C# web 利用NPOI進行生成excel

最新推荐文章于 2022-06-07 11:30:00 发布

也就会一点点

最新推荐文章于 2022-06-07 11:30:00 发布

阅读量340

点赞数

分类专栏： 2018编程笔记

本文链接：https://blog.csdn.net/qq_37986863/article/details/89277458

版权

2018编程笔记专栏收录该内容

11 篇文章 2 订阅

订阅专栏

本文介绍了如何在C# Web项目中利用NPOI库生成Excel文件。首先，需要引用NPOI.dll并引入相关命名空间。然后，可以将查询结果如DataTable转换为数据流。对于一对多的关系，可以处理复杂的数据结构。最后，将数据流返回并输出到网页。

摘要由CSDN通过智能技术生成

第一步：引用NPOI.dll，命名空間引用

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

第二步：傳入查詢的到的

@1單純的以行導出的格式DataTable，作為參數，得到數據流

         /// <summary>
        /// 数据导出到Excel
        /// </summary>
        /// <param name="dts">数据表集合</param>
        /// <returns>输出流</returns>
        public static MemoryStream ExcelOut(List<DataTable> dts)
        {
          
            HSSFWorkbook wb = new HSSFWorkbook();
            for (int i = 0; i < dts.Count; i++)
            {
                if (dts[i].Columns.Count == 0)
                {
                    continue;
                }
                HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(dts[i].TableName);
                sheet.DefaultColumnWidth = 15;
                sheet.DefaultRowHeight = 22 * 20;
                HSSFRow row = (HSSFRow)sheet.CreateRow(0);
                HSSFCellStyle cellStyle = (HSSFCellStyle)wb.CreateCellStyle();
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;

                cellStyle.BorderTop = BorderStyle.Medium;
                cellStyle.BorderRight = BorderStyle.Medium;
                cellStyle.BorderBottom = BorderStyle.Medium;
                cellStyle.BorderLeft = BorderStyle.Medium;
                HSSFFont font = (HSSFFont)wb.CreateFont();
                font.IsBold = true;
                cellStyle.SetFont(font);
                for (int j = 0; j < dts[i].Columns.Count; j++)
                {
                    HSSFCell cell = (HSSFCell)row.CreateCell(j);
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(excelKey.ContainsKey(dts[i].Columns[j].ToString()) ? excelKey[dts[i].Columns[j].ToString()] : dts[i].Columns[j].ToString());

                    cell.CellStyle = cellStyle;
                }
                HSSFCellStyle cellStyle2 = (HSSFCellStyle)wb.CreateCellStyle();
                cellStyle2.VerticalAlignment = VerticalAlignment.Center;
                cellStyle2.BorderTop = BorderStyle.Thin;
                cellStyle2.BorderRight = BorderStyle.Thin;
                cellStyle2.BorderBottom = BorderStyle.Thin;
                cellStyle2.BorderLeft = BorderStyle.Thin;
                for (int k = 0; k < dts[i].Rows.Count; k++)
                {
                    HSSFRow row2 = (HSSFRow)sheet.CreateRow(k + 1);
                    object[] values = dts[i].Rows[k].ItemArray;
                    for (int z = 0; z < values.Length; z++)
                    {
                        HSSFCell cell = (HSSFCell)row2.CreateCell(z);
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue(values[z].ToString());
                        cell.CellStyle = cellStyle2;
                    }
                }
            }
            MemoryStream ms = new MemoryStream();
            wb.Write(ms);
            return ms;
        }

@2：一對多的關係，如：

       /// <summary>
        /// 复杂数据导出到Excel
        /// </summary>
        /// <param name="dts">数据表集合</param>
        /// <returns>输出流</returns>
        public static MemoryStream ExcelOut2(List<DataTable> dts)
        {
            
            #region 生成列
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("001");
            for (int i = 0; i < dts.Count; i++)
            {
                if (dts[i].Columns.Count == 0)
                {
                    continue;
                }
                sheet.DefaultColumnWidth = 15;
                sheet.DefaultRowHeight = 22 * 20;
                HSSFRow row = (HSSFRow)sheet.CreateRow(0);
                HSSFCellStyle cellStyle = (HSSFCellStyle)wb.CreateCellStyle();
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;

                cellStyle.BorderTop = BorderStyle.Medium;
                cellStyle.BorderRight = BorderStyle.Medium;
                cellStyle.BorderBottom = BorderStyle.Medium;
                cellStyle.BorderLeft = BorderStyle.Medium;
                HSSFFont font = (HSSFFont)wb.CreateFont();
                font.IsBold = true;
                cellStyle.SetFont(font);
                for (int j = 0; j < dts[i].Columns.Count; j++)
                {
                    HSSFCell cell = (HSSFCell)row.CreateCell(j);
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(excelKey.ContainsKey(dts[i].Columns[j].ToString()) ? excelKey[dts[i].Columns[j].ToString()] : dts[i].Columns[j].ToString());

                    cell.CellStyle = cellStyle;
                }
                HSSFCellStyle cellStyle2 = (HSSFCellStyle)wb.CreateCellStyle();
                cellStyle2.VerticalAlignment = VerticalAlignment.Center;
                cellStyle2.BorderTop = BorderStyle.Thin;
                cellStyle2.BorderRight = BorderStyle.Thin;
                cellStyle2.BorderBottom = BorderStyle.Thin;
                cellStyle2.BorderLeft = BorderStyle.Thin;
                for (int k = 0; k < dts[i].Rows.Count; k++)
                {
                    HSSFRow row2 = (HSSFRow)sheet.CreateRow(k + 1);
                    object[] values = dts[i].Rows[k].ItemArray;
                    for (int z = 0; z < values.Length; z++)
                    {
                        HSSFCell cell = (HSSFCell)row2.CreateCell(z);
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue(values[z].ToString());
                        cell.CellStyle = cellStyle2;
                    }
                }
            }
            #endregion

            #region 合并行
            DataTable dt = new DataTable();
            dt = dts[0];
           //合并行
            for(int i = 1; i < dt.Rows.Count + 1; i++)
            {
                string value = sheet.GetRow(i).GetCell(0).StringCellValue;
                int end = i;
                //找到结束为止
                for(int j = i + 1; j < dt.Rows.Count + 1; j++)
                {
                    string value1= sheet.GetRow(j).GetCell(0).StringCellValue;
                    if (value != value1)
                    {
                        end = j - 1;
                        break;
                    }
                    else if(value==value1 && j == dt.Rows.Count)
                    {
                        end = j;
                        break;
                    }
                }
                sheet.AddMergedRegion(new CellRangeAddress(i, end, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(i, end, 1, 1));
                i = end;
            }                           
            #endregion           
            MemoryStream ms = new MemoryStream();
            wb.Write(ms);
            return ms;
        }
    }

第三步：把數據流返回你調用的方法，并輸出頁面。

  // 设置编码和附件格式
  HttpContext.Response.ContentType = "application/vnd.ms-excel";
  HttpContext.Response.ContentEncoding = Encoding.UTF8;
  HttpContext.Response.Charset = "";
  HttpContext.Response.AppendHeader("Content-Disposition",
 "attachment;filename=" + HttpUtility.UrlEncode(pathName + ".xls", Encoding.UTF8));
  HttpContext.Response.BinaryWrite(ms.GetBuffer());
  HttpContext.Response.End();