NPOI导出Excel生成多个sheet

╰+攻爆jí腚メ 2021-11-10 14:44 559阅读 0赞

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using NPOI.SS.UserModel;
    using NPOI.XSSF.UserModel;
    using System.IO;
    using System.Reflection;
    
    namespace WebApplication1.Controllers
    {
        public class Info
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
        public class TestController : Controller
        {
            // GET: Test
            public ActionResult Index()
            {
                List<Info> list = new List<Info>();
    
                for (int i = 0; i < 10; i++)
                {
                    Info info = new Info();
                    info.Id = i + 1;
                    info.Name = "张三" + (i + 1);
                    list.Add(info);
                }
                Dictionary<string, string> columnInfo = new Dictionary<string, string>();
                columnInfo.Add("Id","序号");
                columnInfo.Add("Name", "姓名");
                byte[] btyBytes=null;
                var a = ExportExcelTest<Info>(list, @"F://111.xlsx", ref btyBytes, columnInfo, 2);
    
                return View();
            }
    
            #region  NPOI大数据量多个sheet导出
    
            /// <summary>
            /// 大数据量多个sheet导出
            /// </summary>
            /// <typeparam name="T">数据源实体类</typeparam>
            /// <param name="objList">数据源</param>
            /// <param name="fileName">文件名称</param>
            /// <param name="btyBytes">导出数据流</param>
            /// <param name="columnInfo">显示列对应数据字典</param>
            /// <param name="listCount">每个sheet包含数据条数</param>
            /// <returns></returns>
            public static bool ExportExcelTest<T>(List<T> objList, string fileName, ref byte[] btyBytes,
    Dictionary<string, string> columnInfo = null, int listCount = 10000)
            {
                bool bResult = false;
                //在内存中生成一个Excel文件：
                XSSFWorkbook book = new XSSFWorkbook();
                if (objList != null && objList.Count > 0)
                {
                    double sheetCount = Math.Ceiling((double)objList.Count / listCount);
                    for (int i = 0; i < sheetCount; i++)
                    {
                        ISheet sheet = null;
                        sheet = book.CreateSheet("sheet" + i);
                        sheet.DefaultRowHeight = 20 * 10;
                        List<T> list = new List<T>();
                        list = objList.Skip<T>(listCount * i).Take<T>(listCount).ToList();
    
                        int rowIndex = 0;
                        int StartColIndex = 0;
                        int colIndex = StartColIndex;
    
                        //创建表头样式
                        ICellStyle style = book.CreateCellStyle();
                        style.Alignment = HorizontalAlignment.CENTER;
                        style.WrapText = true;
                        IFont font = book.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
                        font.FontName = "简体中文";
                        style.SetFont(font);//HEAD 样式
    
                        Type myType = null;
                        myType = objList[0].GetType();
                        //根据反射从传递进来的属性名信息得到要显示的属性
                        List<PropertyInfo> myPro = new List<PropertyInfo>();
                        PropertyInfo[] properties = myType.GetProperties();
    
                        #region 定义表头
                        int m = 0;
                        if (columnInfo != null)
                        {
                            var rowheader = sheet.CreateRow(0);
                            rowheader.Height = rowheader.Height = 20 * 20;
                            foreach (string cName in columnInfo.Keys)
                            {
                                PropertyInfo p = myType.GetProperty(cName);
                                if (p != null)
                                {
                                    myPro.Add(p);
                                    rowheader.CreateCell(m).SetCellValue(columnInfo[cName]);
                                    m++;
                                }
                            }
                        }
                        #endregion
                        #region 定义表体并赋值
                        //如果没有找到可用的属性则结束
                        if (myPro.Count == 0) { return bResult; }
                        foreach (T obj in list)
                        {
                            int n = 0;
                            if (sheet != null)
                            {
                                rowIndex++;
                                var sheetrow = sheet.CreateRow(rowIndex);
                                sheetrow.Height = sheetrow.Height = 20 * 20;
                                foreach (PropertyInfo p in myPro)
                                {
                                    dynamic val = p.GetValue(obj, null) ?? "";
                                    string valtype = val.GetType().ToString();
                                    if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -1)
                                    {
                                        val = Convert.ToDouble(val);
                                    }
                                    else if (valtype.ToLower().IndexOf("datetime", StringComparison.Ordinal) > -1)
                                    {
                                        val = val.ToString("yyyy-MM-dd HH:mm:ss");
                                        if (val.Equals("0001-01-01 00:00:00"))
                                        {
                                            val = "";
                                        }
                                    }
                                    sheetrow.CreateCell(n).SetCellValue(val);
                                    n++;
                                }
                            }
    
                        }
                        #endregion
                    }
                }
                else
                {
                    //在工作薄中建立工作表
                    XSSFSheet sheet = book.CreateSheet() as XSSFSheet;
                    sheet.SetColumnWidth(0, 30 * 256);
                    if (sheet != null) sheet.CreateRow(0).CreateCell(0).SetCellValue("暂无数据！");
                }
    
              var  fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                book.Write(fs);
                //try
                //{
                //    HttpResponse rs = System.Web.HttpContext.Current.Response;
                //    rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                //    rs.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                //    rs.ContentType = "application/vnd.ms-excel";
                //    //application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
    
                //    using (MemoryStream ms = new MemoryStream())
                //    {
                //        book.Write(ms);          
                //        btyBytes = ms.GetBuffer();
                //        rs.AddHeader("Content-Length", btyBytes.Length.ToString());
                //        rs.BinaryWrite(ms.GetBuffer());
                //        ms.Flush();
                //    }
                //}
                //catch (SystemException ex)
                //{
                //    throw ex;
                //}
                //catch (ApplicationException ex)
                //{
                //    throw ex;
                //}
                return bResult;
            }
    
    
            #endregion
        }
    }

![1415349-20190613162444969-1839187959.png][]

转载于:https://www.cnblogs.com/LiChen19951127/p/11017169.html

[1415349-20190613162444969-1839187959.png]: /images/20211109/28b640897bef49cc86b1edb84f3af1d1.png