NPOI讀取示例

通過流生成 excel

using Microsoft.AspNetCore.Mvc;

using NPOI.HSSF.Record.Aggregates;
using NPOI.HSSF.UserModel;
using NPOI.XWPF.UserModel;

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace NpoiDemo.Common
{
    using S = String;
    public class ZJDelivery:CustomReportBase
    {
        public static FileContentResult GetCDFPickDetail(List<CDFPickDetail> handover)
        {
            var hssfworkbook = new HSSFWorkbook();

            var sheet1 = hssfworkbook.CreateSheet("Sheet1");
            var font = hssfworkbook.CreateFont();
            font.FontName = "Times New Roman";
            font.FontHeightInPoints = 11;
            var style = hssfworkbook.CreateCellStyle();
            style.SetFont(font);

            var font2 = hssfworkbook.CreateFont();
            font2.FontName = "Times New Roman";
            font2.FontHeightInPoints = 36;

            #region   {第一行}
            var s1Row1Style = hssfworkbook.CreateCellStyle();

            var r1 = sheet1.CreateRow(0);
            r1.HeightInPoints = 46.5f;
            var r1c1Style = hssfworkbook.CreateCellStyle();
            r1c1Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            r1c1Style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            r1c1Style.SetFont(font2);

            var r1c1 = r1.CreateCell(0);
            r1c1.SetCellValue("VIP 理 貨 單");
            r1c1.CellStyle = r1c1Style;
            Enumerable.Range(1, 19).ToList().ForEach(x =>
            {
                r1.CreateCell(x).SetCellValue("");
            });

            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 18));

            #endregion

            #region{第二行}
            var r2 = sheet1.CreateRow(1);
            var r2c1 = r2.CreateCell(0);

            r2c1.SetCellValue($"Date：{DateTime.Now.ToString("dd-MM-yyyy")}");
            r2c1.CellStyle = style;
            Enumerable.Range(1, 19).ToList().ForEach(x =>
            {
                r2.CreateCell(x).SetCellValue("");
            });
            #endregion
            #region   {第三行-空白行}
            var r3 = sheet1.CreateRow(2);
            Enumerable.Range(0, 19).ToList().ForEach(x =>
            {
                r3.CreateCell(x).SetCellValue("");
            });
            #endregion

            var fontCotent = hssfworkbook.CreateFont();
            font.FontName = "Times New Roman";
            font.FontHeightInPoints = 11;
            var contentStyle = hssfworkbook.CreateCellStyle();
            contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            contentStyle.SetFont(font);
            setBorderStyle(contentStyle,NPOI.SS.UserModel.BorderStyle.Medium);

            #region   {第四行，列名}
            var r4 = sheet1.CreateRow(3);
            var r4Col = new List<S>
            {
                "VIP",
                "ItemID",
                "UPC",
                "C Code",
                "Product",
                "QTY",
                "Inventory",
                "Location"
            };
            Enumerable.Range(0, r4Col.Count).ToList().ForEach(x =>
            {
                r4.CreateCell(x).SetCellValue(r4Col[x]);
                r4.Cells[x].CellStyle = contentStyle;

            });
            var r4c9 = r4.CreateCell(r4Col.Count);
            var r4c9Style = hssfworkbook.CreateCellStyle();
            r4c9.CellStyle = contentStyle;
            r4c9.SetCellValue("Cantons Detail");
            Enumerable.Range(0, 8).ToList().ForEach(x =>
            {
                r4.CreateCell(x + 9).SetCellValue("");
                r4.Cells[x + 9].CellStyle = contentStyle;
            });
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 8, 15));

            var r4Col2 = new List<S>
            {
                "Lot",
                "Cantons",
                "Remark",
            };
            Enumerable.Range(0, 3).ToList().ForEach(x =>
            {
                r4.CreateCell(x + 16).SetCellValue(r4Col2[x]);
                r4.Cells[x + 16].CellStyle = contentStyle;
            });
            #endregion


            //資料填充
            var rIndex = 4;
            var calcIndex = 5;
            handover.ForEach(x =>
            {
                var r = sheet1.CreateRow(rIndex++);
                r.CreateCell(0).SetCellValue("");//vip 後面需要進行合併
                r.CreateCell(1).SetCellValue(x.ItemID);
                r.CreateCell(2).SetCellValue(x.UPC);
                r.CreateCell(3).SetCellValue(x.CCode);
                r.CreateCell(4).SetCellValue(x.Product);
                r.CreateCell(5).SetCellValue(x.QTY);
                r.CreateCell(6).SetCellValue("");//Inventory
                r.CreateCell(7).SetCellValue("");//location
                r.CreateCell(8).SetCellValue("");//input qty
                r.CreateCell(9).SetCellValue("pcs");
                r.CreateCell(10).SetCellValue("");//input qty
                r.CreateCell(11).SetCellValue("ctn");
                r.CreateCell(12).SetCellValue("");//input qty
                r.CreateCell(13).SetCellValue("pcs");
                r.CreateCell(14).SetCellValue("");//input qty
                r.CreateCell(15).SetCellValue("ctn");
                r.CreateCell(16).SetCellValue("");//lot
                var m = r.CreateCell(17);
                m.SetCellValue("");//Cantons
                m.SetCellFormula($"SUM(K{calcIndex},O{calcIndex})");
                r.CreateCell(18).SetCellValue("");//Remark
                Enumerable.Range(0, 19).ToList().ForEach(y =>
                {
                    r.Cells[y].CellStyle = contentStyle;
                });
                calcIndex++;
            });

            Enumerable.Range(0, 2).ToList().ForEach(x=> {
                var r = sheet1.CreateRow(rIndex++);
                Enumerable.Range(0, 19).ToList().ForEach(y=>{
                    r.CreateCell(y).SetCellValue("");
                });
            });
            //vip列合併
            sheet1.GetRow(4).Cells[0].SetCellValue(handover.First().VIP);
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, handover.Count+3, 0, 0));

            //理貨人
            var rlast = sheet1.CreateRow(rIndex);
            var rlastc1 = rlast.CreateCell(0);
            rlastc1.SetCellValue("理貨人員簽名：");
            rlastc1.CellStyle = style;
            //自適應列寬
            Enumerable.Range(0, 19).ToList().ForEach(x =>
            {
                sheet1.AutoSizeColumn(x);
            });
            //固定寬度設定
            Enumerable.Range(0, 8).ToList().ForEach(x =>
            {
                sheet1.SetColumnWidth(x+8, 5 * 256);
            });
            var bytes = new byte[] { };
            using (var ms = new MemoryStream())
            {
                hssfworkbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                bytes = ms.GetBuffer();
            }
            return new FileContentResult(bytes, "application/vnd.ms-excel")
            {
                FileDownloadName = $"VIP揀貨明細表{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls"
            };
        }

    }

    public class CDFPickDetail
    {
        public S VIP { get; set; }
        public S ItemID { get; set; }
        public S UPC { get; set; }
        public S CCode { get; set; }
        public S Product { get; set; }
        public S QTY { get; set; }
        public S Inventory { get; set; }
        public S Location { get; set; }
        public S CantonsDetail { get; set; }
        public S Lot { get; set; }
        public S Cantons { get; set; }
        public S Remark { get; set; }
    }
}