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;
        }
    }
}