NPOI读取示例

淩亂°似流年 2021-07-04 13:41 545阅读 0赞

## 通过流生成 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; }
        }
    }