NPOI导出excel（符合条件的背景颜色变色）
后台
public ActionResult FAExport()
{
var sql = @"SELECT * FROM t_EquipMasterFA A WHERE 1=1";
var data = bm.FillTable(sql);
string ExcelFileName = Server.MapPath("~/temp/") + Guid.NewGuid().ToString() + ".xlsx";
EMMS.Core.Helper.ExcelHelper.FAmasterTableToExcelForXLSX(data, ExcelFileName);
return ExportExcel(ExcelFileName, "Master");
}
public ActionResult ExportExcel(string path, string downloadname)
{
var fi = new System.IO.FileInfo(downloadname);
if (fi.Extension == "")
downloadname += ".xlsx";
JeffSoft.Logger.Error("ExportExcel path"+ path);
return Json(new { Success = true, path = path, contenttype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", downloadname = downloadname }, JsonRequestBehavior.AllowGet);
}
PS：操作 2003 版本需要添加 NPOI.dll 的引用，操作 2007 版本需要添加 NPOI.OOXML.dll 的引用。
ExcelHelper.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;
namespace EMMS.Core.Helper
{
public class ExcelHelper
{
#region Excel2003
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLS(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLS(DataTable dt, string file)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Test");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
hssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
/// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(HSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
#region Excel2007
public static List<List<String>> ExcelToListXLSX(string file)
{
List<List<String>> list = new List<List<String>>();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(0);
//数据
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
List<String> dataList = new List<String>();
for (int j = 0; j < row.LastCellNum; j++)
{
object obj = GetValueTypeForXLSX(row.GetCell(j) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dataList.Add("");
}
else
{
dataList.Add(obj.ToString());
}
}
list.Add(dataList);
}
}
}
return list;
}
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xlsx)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLSX(string file, int headerRowNum)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(headerRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
int ii = 0;
try
{
for (int i = headerRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
ICell cell = row.GetCell(j);
dr[j] = GetValueTypeForXLSX(cell as XSSFCell);
}
else
{
dr[j] = "";
}
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
ii++;
}
}
catch (Exception e)
{
throw new Exception(string.Format("读取文件{0}出错，第{1}行，原因{2}", file, ii, e.Message));
//System.Diagnostics.Debug.WriteLine(e.Message);
}
}
return dt;
}
public static MemoryStream ExportToExcel(DataTable dt, string header)
{
IWorkbook workbook = new XSSFWorkbook();//2007
ISheet sheet = workbook.CreateSheet("sheet0");
//设置大标题行
int rowCount = 0;
//设置全局列宽和行高
sheet.DefaultColumnWidth = 14; //全局列宽
sheet.DefaultRowHeightInPoints = 15; //全局行高
//设置标题行数据
int a = 0;
IRow row1 = sheet.CreateRow(rowCount); //创建报表表头标题列
string[] columnHeaders = header.Split(new char[] { ',' });//按逗号拆分标题
for (int k = 0; k < columnHeaders.Length; k++)
{ //将传递过来的字符串表头进行拆分到Excel
string columnName = columnHeaders[k];
ICell cell = row1.CreateCell(a);
cell.SetCellValue(columnName);
a++;
}
//填写ds数据进excel
for (int i = 0; i < dt.Rows.Count; i++) //写行数据
{
IRow row2 = sheet.CreateRow(i + rowCount + 1);
int b = 0;
for (int j = 0; j < dt.Columns.Count; j++)
{
string dgvValue = string.Empty;
dgvValue = dt.Rows[i][j].ToString();
ICell cell = row2.CreateCell(b);
cell.SetCellValue(dgvValue);
b++;
}
}
MemoryStream memoryStream = new MemoryStream();
workbook.Write(memoryStream);
byte[] bytes = memoryStream.ToArray();
// 设置当前流的位置为流的开始
return new MemoryStream(bytes);
}
public static MemoryStream ExportToExcel(DataTable dt)
{
IWorkbook workbook = new XSSFWorkbook();//2007
ISheet sheet = workbook.CreateSheet("sheet0");
//设置大标题行
int rowCount = 0;
//设置全局列宽和行高
sheet.DefaultColumnWidth = 14; //全局列宽
sheet.DefaultRowHeightInPoints = 15; //全局行高
//设置标题行数据
int a = 0;
IRow row1 = sheet.CreateRow(rowCount); //创建报表表头标题列
foreach (DataColumn dtColums in dt.Columns)
{ //将传递过来的字符串表头进行拆分到Excel
string columnName = dtColums.ColumnName;
ICell cell = row1.CreateCell(a);
cell.SetCellValue(columnName);
a++;
}
//填写ds数据进excel
for (int i = 0; i < dt.Rows.Count; i++) //写行数据
{
IRow row2 = sheet.CreateRow(i + rowCount + 1);
int b = 0;
for (int j = 0; j < dt.Columns.Count; j++)
{
string dgvValue = string.Empty;
dgvValue = dt.Rows[i][j].ToString();
ICell cell = row2.CreateCell(b);
cell.SetCellValue(dgvValue);
b++;
}
}
MemoryStream memoryStream = new MemoryStream();
workbook.Write(memoryStream);
byte[] bytes = memoryStream.ToArray();
// 设置当前流的位置为流的开始
return new MemoryStream(bytes);
}
public static MemoryStream InsertPic(string file, string imagePath, int col = 0, int row = 0)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
byte[] bytes1 = System.IO.File.ReadAllBytes(imagePath);
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(0);
int pictureIdx = xssfworkbook.AddPicture(bytes1, XSSFWorkbook.PICTURE_TYPE_PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
//add a picture
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 0, col, row, 1, 3);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
MemoryStream memoryStream = new MemoryStream();
xssfworkbook.Write(memoryStream);
byte[] bytes = memoryStream.ToArray();
// 设置当前流的位置为流的开始
return new MemoryStream(bytes);
}
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLSX(DataTable dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Sheet1");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void FAmasterTableToExcelForXLSX(DataTable dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Sheet1");
ICellStyle cellStyle = xssfworkbook.CreateCellStyle();
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = 26;
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
private static ICellStyle CreateTQReportSimpleBorderStyle(IWorkbook wb)
{
ICellStyle style = wb.CreateCellStyle();
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
return style;
}
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return "";
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
short format = cell.CellStyle.DataFormat;
if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20 || DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
/**
if (DateUtil.IsValidExcelDate(cell.NumericCellValue) || DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}*/
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
string v = "";
switch (cell.CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = cell.StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
v = strFORMULA.ToString();
}
else
{
v = "";
}
break;
case CellType.Numeric:
v = Convert.ToString(cell.NumericCellValue);
break;
case CellType.Boolean:
v = Convert.ToString(cell.BooleanCellValue);
break;
case CellType.Error:
v = NPOI.SS.Formula.Eval.ErrorEval.GetText(cell.ErrorCellValue);
break;
default:
v = cell.CellFormula;
break;
}
return v;
default:
return cell.StringCellValue;
}
}
#endregion
/// <summary>
/// 将datatable导出为excel
/// 图片默认显示在excel 第二行最后一列
/// </summary>
/// <param name="table">数据源</param>
/// <param name="excelInfo">Tuple<excel列名,datatable列名,excel列宽度></param>
/// <param name="sheetName">工作簿名称</param>
/// <param name="picBytes">导出图片字节流</param>
/// <param name="mergedRegion">合并单元格信息：null不合并单元格</param>
/// <returns></returns>
public static MemoryStream ExportToExcel2007(DataTable table, List<Tuple<string, string, int>> excelInfo,
string sheetName, byte[] picBytes, List<CellRangeAddress> mergedRegion)
{
MemoryStream ms = new MemoryStream();
try
{
using (table)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
for (int i = 0; i < excelInfo.Count; i++)
{
sheet.SetColumnWidth(i, excelInfo[i].Item3 * 256);
}
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < excelInfo.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(excelInfo[i].Item1);
}
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < excelInfo.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(row[excelInfo[i].Item2].ToString());
}
rowIndex++;
}
//合并单元格
if (mergedRegion != null && mergedRegion.Count > 0)
{
foreach (CellRangeAddress cellRangeAddress in mergedRegion)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(cellRangeAddress);
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
//将新的样式赋给单元格
var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn);
cell.CellStyle = style;
}
}
//插入图片
if (picBytes != null && picBytes.Length > 0)
{
var row1 = 2;
var col1 = excelInfo.Count + 1;
/* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
int pictureIdx = workbook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.PNG); //添加图片
/* Create the drawing container */
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
/* Create an anchor point */
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 240, col1, row1, col1 + 1, row1 + 1);
/* Invoke createPicture and pass the anchor point and ID */
XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx);
/* Call resize method, which resizes the image */
picture.Resize();
picBytes = null;
}
workbook.Write(ms);
}
}
catch (Exception ex)
{
ms = null;
}
return ms;
}
/// <summary>
/// 将datatable导出为excel
/// 图片默认显示在excel 第二行最后一列
/// </summary>
/// <param name="table">数据源</param>
/// <param name="excelInfo">Tuple<excel列名,datatable列名,excel列宽度></param>
/// <param name="sheetName">工作簿名称</param>
/// <param name="picBytes">导出图片字节流</param>
/// <param name="mergedRegion">合并单元格信息：null不合并单元格</param>
/// <returns></returns>
public static MemoryStream ExportToExcel97(DataTable table, List<Tuple<string, string, int>> excelInfo, string sheetName, byte[] picBytes, List<CellRangeAddress> mergedRegion)
{
MemoryStream ms = new MemoryStream();
try
{
using (table)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
for (int i = 0; i < excelInfo.Count; i++)
{
sheet.SetColumnWidth(i, excelInfo[i].Item3 * 256);
}
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < excelInfo.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(excelInfo[i].Item1);
}
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < excelInfo.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(row[excelInfo[i].Item2].ToString());
}
rowIndex++;
}
//合并单元格
if (mergedRegion != null && mergedRegion.Count > 0)
{
foreach (CellRangeAddress cellRangeAddress in mergedRegion)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(cellRangeAddress);
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
//将新的样式赋给单元格
var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn);
cell.CellStyle = style;
}
}
//插入图片
if (picBytes != null && picBytes.Length > 0)
{
var row1 = 2;
var col1 = excelInfo.Count + 1;
int pictureIdx = workbook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.PNG); //添加图片
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 240, col1, row1, col1 + 1, row1 + 1);
//图片位置，图片左上角为（col, row）
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize(); //用图片原始大小来显示
picBytes = null;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
}
catch (Exception ex)
{
ms = null;
}
return ms;
}
}
}
浙公网安备 33010602011771号