首先，通过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);
            IR