• 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;
            }
    View Code

    转换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;
            }
    View Code

    转换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;
            }
    View Code

    转换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;
            }
    View Code

    转换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;
            }
    View Code

    转换Excel文件内容如下:

  • 相关阅读:
    linux 软件各文件安装位置
    c dup 函数
    c sigaction信号处理
    vtun 信号处理
    vtun 虚拟网卡的读写非阻塞研究
    vtun 守护进程详解
    vtun fork函数
    vtun 中的__io_canceled变量和相关函数
    android 之 AIDL
    android 显示电池电量
  • 原文地址:https://www.cnblogs.com/ucos/p/6781475.html
Copyright © 2020-2023  润新知