C# NPOI导出Excel横向纵向显示

小咪咪 2022-11-13 13:24 252阅读 0赞

/// <summary>
            /// DataTable导出Excel(纵向导出)
            /// </summary>
            /// <param name="dataTable">数据源</param>
            /// <param name="filePath">保存的路径</param>
            /// <param name="documentname">表名</param>
            public static void Excel2(this DataTable dataTable, string filePath, string documentname)
            {
                string sheetName = "Sheet1";
                if (dataTable == null || dataTable.Rows.Count == 0)
                    throw new Exception("No data to export");
    
                ISheet sheet = null;
                IWorkbook workbook = null;
    
                try
                {
                    if (Directory.Exists(filePath) == false)
                    {
                        Directory.CreateDirectory(filePath);
                    }
                    string filedocmentname = "\\File Card.xls";//文件名 using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))
                    {
                        workbook = new HSSFWorkbook();
                        if (string.IsNullOrEmpty(sheetName))
                            sheetName = "Sheet1";//工作簿
                        sheet = workbook.CreateSheet(sheetName);
                        IRow row = sheet.CreateRow(0);
                        IFont font = workbook.CreateFont();
                        font.FontName = "Arial";//字体样式
                        font.Boldweight = (short)FontBoldWeight.Bold;
                        ICellStyle headerStyle2 = workbook.CreateCellStyle();
                        headerStyle2.VerticalAlignment = VerticalAlignment.Center; //垂直居中
                        headerStyle2.WrapText = true;//自动换行
                        font.FontHeightInPoints = 9;//字体 for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            ICellStyle header= workbook.CreateCellStyle();
                            header.VerticalAlignment = VerticalAlignment.Center; //垂直居中
                            header.WrapText = true;//自动换行
                            var row1_ = sheet.CreateRow(i);//创建行
                            var cellName = row1_.CreateCell(0);//列名
                            cellName.SetCellValue(dataTable.Columns[i].ColumnName.ToString().Replace("<br />", "\n"));
                            IFont font2 = workbook.CreateFont();
                            font2.Boldweight = (short)FontBoldWeight.Bold;
                            header.SetFont(font);
                            cellName.CellStyle = header;
    　　　　　　　　　　　　　　 //填充内容 for (int j = 0; j < dataTable.Rows.Count; j++)
                            {
                                var cell1_ = row1_.CreateCell(j + 1);
                                cell1_.SetCellValue(dataTable.Rows[j][i].ToString().Replace("<br />", "\n"));
                                cell1_.CellStyle = headerStyle2; //把样式赋给单元格
                            }
                        }
                        //设置列宽
                        sheet.SetColumnWidth(0, 10 * 256+200);//列宽为10
    //设置行高
     //row.Height = 30 * 20;//行高为30
                        workbook.Write(fs); //写入到excel
                    }
                }
                finally
                {
                    if (workbook != null)
                        workbook.Clear();
                }
            }

/// <summary>
            /// DataTable导出Excel(横向导出)
            /// </summary>
            /// <param name="dataTable">数据源</param>
            /// <param name="filePath">保存的路径</param>
            /// <param name="documentname">表名</param>
            public static void Excel(this DataTable dataTable, string filePath, string documentname)
            {
        
    　　　　　　　//datatable第一列增加序号
                dataTable= AddSeriNumToDataTable(dataTable);
                string sheetName = "Sheet1";
                if (dataTable == null || dataTable.Rows.Count == 0)
                    throw new Exception("No data to export");
    
                ISheet sheet = null;
                IWorkbook workbook = null;
    
                try
                {
                    if (Directory.Exists(filePath) == false)
                    {
                        Directory.CreateDirectory(filePath);
                    }
                    string filedocmentname = "\\List.xls";//文件名                 using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))
                    {
                        workbook = new HSSFWorkbook();
                        if (string.IsNullOrEmpty(sheetName))
                            sheetName = "Sheet1";
                        sheet = workbook.CreateSheet(sheetName);
                        IRow row = sheet.CreateRow(0);
                        IFont font = workbook.CreateFont();
                        font.FontName = "Arial";//字体样式
                        ICellStyle headerStyle2 = workbook.CreateCellStyle();
                        headerStyle2.VerticalAlignment = VerticalAlignment.Center; //垂直居中
                        headerStyle2.WrapText = true;//自动换行
                        font.FontHeightInPoints = 9;//字体 //填充表头
                        for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                        {
                            ICellStyle headerStyle = workbook.CreateCellStyle(); 
                            headerStyle.FillForegroundColor = IndexedColors.PaleBlue.Index;//首行填充颜色
                            headerStyle.FillPattern = FillPattern.SolidForeground;
                            headerStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 
                            headerStyle.WrapText = true;//自动换行
                            headerStyle.BorderTop = BorderStyle.Thin;//上边框
                            headerStyle.BorderBottom = BorderStyle.Thin;//下边框
                            headerStyle.BorderLeft = BorderStyle.Thin;//左边框
                            headerStyle.BorderRight = BorderStyle.Thin;//右边框 var cell = row.CreateCell(columnIndex);
                            cell.CellStyle = headerStyle;
                            cell.SetCellValue(dataTable.Columns[columnIndex].ColumnName.Replace("<br />", "\n")); //设置列名
                            row.HeightInPoints= 35;
                        }
                        //填充内容
                        for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
                        {
                            row = sheet.CreateRow(rowIndex + 1);
                            for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                            {
                                row.CreateCell(columnIndex).SetCellValue(Convert.ToString(dataTable.Rows[rowIndex][columnIndex]));
                                ICell cellData = row.CreateCell(columnIndex);
                                cellData.SetCellValue(dataTable.Rows[rowIndex][columnIndex].ToString().Replace("<br />", "\n"));
                                cellData.CellStyle = headerStyle2; //把样式赋给单元格
                            }
                        }
                        sheet.CreateFreezePane(0, 1, 0, 1); //首行冻结
                        workbook.Write(fs); //写入到excel
                    }
                }
                finally
                {
                    if (workbook != null)
                        workbook.Clear();
                }
            }

/// <summary>
            /// 在DataTable中添加一序号列，编号从1依次递增
            /// </summary>
            /// <param >DataTable</param>
            /// <returns></returns>
            public static DataTable AddSeriNumToDataTable(DataTable dt)
            {
                //需要返回的值
                DataTable dtNew;
                if (dt.Columns.IndexOf("序号") >= 0)
                {
                    dtNew = dt;
                }
                else //添加一序号列,并且在第一列
                {
                    int rowLength = dt.Rows.Count;
                    int colLength = dt.Columns.Count;
                    DataRow[] newRows = new DataRow[rowLength];
                    dtNew = new DataTable();
                    //在第一列添加“序号”列
                    dtNew.Columns.Add("序号");
                    for (int i = 0; i < colLength; i++)
                    {
                        dtNew.Columns.Add(dt.Columns[i].ColumnName);
                        //复制dt中的数据
                        for (int j = 0; j < rowLength; j++)
                        {
                            if (newRows[j] == null)
                                newRows[j] = dtNew.NewRow();
                            //将其他数据填充到第二列之后，因为第一列为新增的序号列
                            newRows[j][i + 1] = dt.Rows[j][i];
                        }
                    }
                    foreach (DataRow row in newRows)
                    {
                        dtNew.Rows.Add(row);
                    }
                }
                //对序号列填充，从1递增
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dtNew.Rows[i]["序号"] = i + 1;
                }
                return dtNew;
            }

/// <summary>
            /// Datatable导出Excel(无任何样式)
            /// </summary>
            /// <param >DataTable</param>
            /// <returns></returns>
            public static void ExportExcel(DataTable dataTable, string filePath, string documentname)
            {
                if (dataTable != null && dataTable.Rows.Count > 0)
                {
                    HSSFWorkbook excelBook = new HSSFWorkbook();
                    ISheet sheet1 = excelBook.CreateSheet("Export Data");
                    IRow row1 = sheet1.CreateRow(0);
    
                    //填充表头
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        row1.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
                    }
    
                    //填充内容
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        IRow rowTemp = sheet1.CreateRow(i + 1);
                        for (int j = 0; j < dataTable.Columns.Count; j++)
                        {
                            rowTemp.CreateCell(j).SetCellValue(string.Format("{0}", dataTable.Rows[i][j]));
                        }
                    }
                    //保存
                    MemoryStream ms = new MemoryStream();
                    excelBook.Write(ms);
                    if (Directory.Exists(filePath) == false)
                    {
                        Directory.CreateDirectory(filePath);
                    }
                    string filedocmentname = "\\List.xls";
                    using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    ms.Close();
                    ms.Dispose();
                }
            }

**纵向导出Excel效果图**

![2067140-20200616103306778-267944556.png][]

**横向导出Excel效果图**

![2067140-20200616103514669-1522159547.png][]

[2067140-20200616103306778-267944556.png]: /images/20221022/a610aec2f07f4cb0a85c88fb225864f3.png
[2067140-20200616103514669-1522159547.png]: https://img2020.cnblogs.com/blog/2067140/202006/2067140-20200616103514669-1522159547.png