元哥的筆記

最近在做一些開發需求的時候順便重構現有功能與職責，順便記錄一下npoi的用法

先install安裝npoi套件

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

  //規劃member類別
  class Member
  {
     public string Id { get; set; }
     public string Name { get; set; }
     public string Address { get; set; }
  }

        public IActionResult ExporttoExcel()
        {
            List<Member> members = new List<Member>();
            for (int i = 0; i < 9; i++)
                members.Add(new Member { Id = "A00" + i, Name = "黑影大俠" + i, Address = "台北市信義區XXX街110號" + i + "樓" });
          
            //建立Excel
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //建立活頁簿
            ISheet sheet = hssfworkbook.CreateSheet("sheet"); //建立sheet

            //設定樣式
            ICellStyle headerStyle = hssfworkbook.CreateCellStyle();
            IFont headerfont = hssfworkbook.CreateFont();
            headerStyle.Alignment = HorizontalAlignment.Center; //水平置中
            headerStyle.VerticalAlignment = VerticalAlignment.Center; //垂直置中
            headerfont.FontName = "微軟正黑體";
            headerfont.FontHeightInPoints = 20;
            headerfont.Boldweight = (short)FontBoldWeight.Bold;
            headerStyle.SetFont(headerfont);
            //新增標題列
            sheet.CreateRow(0); //需先用CreateRow建立
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 2)); //合併1~2列及A~C欄儲存格
            sheet.GetRow(0).CreateCell(0).SetCellValue("會員清單");
            sheet.GetRow(0).GetCell(0).CellStyle = headerStyle; //套用樣式
            List<string> strList = new List<string> { "編號","名稱","聯絡地址" };
            for (int i = 0; i < strList.Count; i++)
            {
                if (i == 0)
                    sheet.CreateRow(1).CreateCell(i).SetCellValue(strList[i]);
                else
                    sheet.GetRow(1).CreateCell(i).SetCellValue(strList[i]);
            }

            //填入資料
            int rowIndex = 2;
            foreach (var member in members)
            {
                sheet.CreateRow(rowIndex).CreateCell(0).SetCellValue(member.Id);
                sheet.SetColumnWidth(0, 3 * 2 * 256);  //寬度調整
                sheet.GetRow(rowIndex).CreateCell(1).SetCellValue(member.Name);
                sheet.SetColumnWidth(1, 6 * 2 * 256);  //寬度調整
                sheet.GetRow(rowIndex).CreateCell(2).SetCellValue(member.Address);
                sheet.SetColumnWidth(2,50* 2 * 256);   //寬度調整
                rowIndex++; 
            }
            var excelDatas = new MemoryStream();
            hssfworkbook.Write(excelDatas);
            return File(excelDatas.ToArray(), "application/vnd.ms-excel", string.Format($"會員清單.xls"));
        }

在View中加入打Action按鈕

   <input type="button" onclick="location.href='@Url.Action("ExporttoExcel", "Home")'" value="匯出測試" />

點了就會可以進行匯出了​

元哥的筆記