NPOI操作Excel文件

首先，通过NuGet添加NPOI. 

NPOI依赖SharpZipLib，通过NuGet添加SharpZipLib.

然后添加NPOI.

 

添加后项目的引用列表如下：

 

把DataTable转换成Excel文件。

代码如下： 

        public static MemoryStream RenderDataTableToExcel(DataTable table)
        {
            MemoryStream ms = new MemoryStream();

            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(table.TableName);

            for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in table.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(table.Rows[rowIndex][column].ToString());
                }
            }

            workbook.Write(ms);
            ms.Close();

            return ms;
        }

转换Excel文件内容如下:

 

Excel文件添加表头

代码： 

        public static MemoryStream RenderDataTableToExcelWithHeader(DataTable table)
        {
            MemoryStream ms = new MemoryStream();

            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(table.TableName);
            IRow headerRow = sheet.CreateRow(0);
            foreach (DataColumn column in table.Columns)
            {
                headerRow.CreateCell(column.Ordinal).SetCellValue(string.Format("    {0}    ", column.Caption));
            }

            for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
            {
                IRow dataRow = sheet.CreateRow(rowIndex+1);
                foreach (DataColumn column in table.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(table.Rows[rowIndex][column].ToString());
                }
            }

            workbook.Write(ms);
            ms.Close();

            return ms;
        }

转换Excel文件内容如下:

 

添加Excel文件添加表头样式

 代码： 

public static MemoryStream RenderDataTableToExcelWithHeaderRowStyle(DataTable table)
        {
            MemoryStream ms = new MemoryStream();

            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(table.TableName);
            IRow headerRow = sheet.CreateRow(0);

            ICellStyle headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
            headStyle.FillPattern = FillPattern.SolidForeground;
            IFont font = workbook.CreateFont();
            font.FontName = "Microsoft Yahei";
            font.FontHeightInPoints = 11;
            font.IsBold = true;
            font.Color = HSSFColor.White.Index;
            headStyle.SetFont(font);
            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;

            foreach (DataColumn column in table.Columns)
            {
                ICell cell = headerRow.CreateCell(column.Ordinal);
                cell.SetCellValue(string.Format("    {0}    ", column.Caption));
                cell.CellStyle = headStyle;
            }

            for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
            {
                IRow dataRow = sheet.CreateRow(rowIndex + 1);
                foreach (DataColumn column in table.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(table.Rows[rowIndex][column].ToString());
                }
            }

            workbook.Write(ms);
            ms.Close();

            return ms;
        }

转换Excel文件内容如下:

 

添加Excel文件添加数据行样式

  代码： 

public static MemoryStream RenderDataTableToExcelWithDataRowStyle(DataTable table)
        {
            MemoryStream ms = new MemoryStream();

            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(table.TableName);
            IRow headerRow = sheet.CreateRow(0);

            ICellStyle headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
            headStyle.FillPattern = FillPattern.SolidForeground;
            IFont font = workbook.CreateFont();
            font.FontName = "Microsoft Yahei";
            font.FontHeightInPoints = 11;
            font.IsBold = true;
            font.Color = HSSFColor.White.Index;
            headStyle.SetFont(font);
            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;

            ICellStyle dataRowEvenStyle = workbook.CreateCellStyle();
            dataRowEvenStyle.Alignment = HorizontalAlignment.Center;
            dataRowEvenStyle.FillForegroundColor = HSSFColor.LightOrange.Index;
            dataRowEvenStyle.FillPattern = FillPattern.SolidForeground;
            IFont dataRowEvenFont = workbook.CreateFont();
            dataRowEvenFont.FontName = "Microsoft Yahei";
            dataRowEvenFont.FontHeightInPoints = 11;
            dataRowEvenFont.Color = HSSFColor.Blue.Index;
            dataRowEvenStyle.SetFont(dataRowEvenFont);
            dataRowEvenStyle.BorderBottom = BorderStyle.Thin;
            dataRowEvenStyle.BorderRight = BorderStyle.Thin;
            dataRowEvenStyle.BorderLeft = BorderStyle.Thin;

            ICellStyle dataRowOddStyle = workbook.CreateCellStyle();
            dataRowOddStyle.Alignment = HorizontalAlignment.Center;
            dataRowOddStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            dataRowOddStyle.FillPattern = FillPattern.SolidForeground;
            IFont dataRowOddFont = workbook.CreateFont();
            dataRowOddFont.FontName = "Microsoft Yahei";
            dataRowOddFont.FontHeightInPoints = 11;
            dataRowOddFont.Color = HSSFColor.Black.Index;
            dataRowOddStyle.SetFont(dataRowOddFont);
            dataRowOddStyle.BorderBottom = BorderStyle.Thin;
            dataRowOddStyle.BorderRight = BorderStyle.Thin;
            dataRowOddStyle.BorderLeft = BorderStyle.Thin;

            foreach (DataColumn column in table.Columns)
            {
                ICell cell = headerRow.CreateCell(column.Ordinal);
                cell.SetCellValue(string.Format("    {0}    ", column.Caption));
                cell.CellStyle = headStyle;
            }

            for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
            {
                IRow dataRow = sheet.CreateRow(rowIndex + 1);
                foreach (DataColumn column in table.Columns)
                {
                    ICell cell = dataRow.CreateCell(column.Ordinal);
                    cell.SetCellValue(table.Rows[rowIndex][column].ToString());
                    if (rowIndex % 2 == 0)
                    {
                        cell.CellStyle = dataRowEvenStyle;
                    }
                    else
                    {
                        cell.CellStyle = dataRowOddStyle;
                    }
                }
            }

            workbook.Write(ms);
            ms.Close();

            return ms;
        }

转换Excel文件内容如下:

Excel文件合并单元格

代码： 

public static MemoryStream RenderDataTableToExcelMergedRegion(DataTable table)
        {
            MemoryStream ms = new MemoryStream();

            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(table.TableName);
            IRow headerRow = sheet.CreateRow(0);

            ICellStyle headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
            headStyle.FillPattern = FillPattern.SolidForeground;
            IFont font = workbook.CreateFont();
            font.FontName = "Microsoft Yahei";
            font.FontHeightInPoints = 11;
            font.IsBold = true;
            font.Color = HSSFColor.White.Index;
            headStyle.SetFont(font);
            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;

            ICellStyle dataRowEvenStyle = workbook.CreateCellStyle();
            dataRowEvenStyle.Alignment = HorizontalAlignment.Center;
            dataRowEvenStyle.FillForegroundColor = HSSFColor.LightOrange.Index;
            dataRowEvenStyle.FillPattern = FillPattern.SolidForeground;
            IFont dataRowEvenFont = workbook.CreateFont();
            dataRowEvenFont.FontName = "Microsoft Yahei";
            dataRowEvenFont.FontHeightInPoints = 11;
            dataRowEvenFont.Color = HSSFColor.Blue.Index;
            dataRowEvenStyle.SetFont(dataRowEvenFont);
            dataRowEvenStyle.BorderBottom = BorderStyle.Thin;
            dataRowEvenStyle.BorderRight = BorderStyle.Thin;
            dataRowEvenStyle.BorderLeft = BorderStyle.Thin;

            ICellStyle dataRowOddStyle = workbook.CreateCellStyle();
            dataRowOddStyle.Alignment = HorizontalAlignment.Center;
            dataRowOddStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            dataRowOddStyle.FillPattern = FillPattern.SolidForeground;
            IFont dataRowOddFont = workbook.CreateFont();
            dataRowOddFont.FontName = "Microsoft Yahei";
            dataRowOddFont.FontHeightInPoints = 11;
            dataRowOddFont.Color = HSSFColor.Black.Index;
            dataRowOddStyle.SetFont(dataRowOddFont);
            dataRowOddStyle.BorderBottom = BorderStyle.Thin;
            dataRowOddStyle.BorderRight = BorderStyle.Thin;
            dataRowOddStyle.BorderLeft = BorderStyle.Thin;

            foreach (DataColumn column in table.Columns)
            {
                ICell cell = headerRow.CreateCell(column.Ordinal);
                cell.SetCellValue(string.Format("    {0}    ", column.Caption));
                cell.CellStyle = headStyle;
            }

            for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
            {
                IRow dataRow = sheet.CreateRow(rowIndex + 1);
                foreach (DataColumn column in table.Columns)
                {
                    ICell cell = dataRow.CreateCell(column.Ordinal);
                    cell.SetCellValue(table.Rows[rowIndex][column].ToString());
                    if (rowIndex % 2 == 0)
                    {
                        cell.CellStyle = dataRowEvenStyle;
                    }
                    else
                    {
                        cell.CellStyle = dataRowOddStyle;
                    }
                }
            }

            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(3, 4, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(5, 6, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(7, 8, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(9, 10, 0, 0));

            workbook.Write(ms);
            ms.Close();

            return ms;
        }

转换Excel文件内容如下: