• 【C#】【Demo】 创建Excel帮助XSSFWorkbookCreateHelper。//用于浏览器导出Excel


    //用于浏览器导出Excel,即return File(path, "application/vnd.ms-excel", titleName + ".xlsx");

    using NPOI.SS.UserModel;
    using NPOI.SS.Util;
    using NPOI.XSSF.UserModel;
    using System;
    using System.Collections.Generic;
    
    namespace BaiCellsMall.Common
    {
        public class XSSFWorkbookCreateHelper
        {
            //创建Excel文件的对象
            public XSSFWorkbook book;
    
            //添加一个sheet
            public ISheet sheet1;
    
            //默认单元格宽度
            int CellWidth;
    
            //默认单元格高度
            short CellHeight;
    
            Dictionary<int, int> dicWidth;
            Dictionary<int, short> dicHeight;
    
    
            /// <summary>
            /// 
            /// </summary>
            /// <param name="cellWidth">单元格宽</param>
            /// <param name="cellHeight">单元格高</param>
            public XSSFWorkbookCreateHelper(int cellWidth, short cellHeight, VerticalAlignment? center = null)
            {
                CellWidth = cellWidth;
                CellHeight = cellHeight;
    
                dicWidth = new Dictionary<int, int>();
                dicHeight = new Dictionary<int, short>();
    
                //创建Excel文件的对象
                book = new XSSFWorkbook();
                //NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
    
                //添加一个sheet
                sheet1 = book.CreateSheet("Sheet");
    
                //样式
                ICellStyle style = book.CreateCellStyle();//设置垂直居中格式
                style.VerticalAlignment = center == null ? VerticalAlignment.Center : center.Value;// VerticalAlignment.Center;垂直居中       
            }
    
            /// <summary>
            /// 添加单元格
            /// </summary>
            public void AddCell(CellModel model)
            {
                try
                {
    
    
                    int cellWidth = CellWidth;
                    short cellHeight = CellHeight;
    
    
                    if (model.Width != null)
                    {
                        cellWidth = model.Width.Value;
    
                        //第一个宽不被覆盖
                        if (!dicWidth.ContainsKey(model.C))
                        {
                            dicWidth.Add(model.C, model.Width.Value);
                        }
    
                    }
                    else if (dicWidth.ContainsKey(model.C))
                    {
                        cellWidth = dicWidth[model.C];
                    }
    
    
                    if (model.Height != null)
                    {
                        cellHeight = model.Height.Value;
    
                        //第一个不被覆盖
                        if (!dicHeight.ContainsKey(model.R))
                        {
                            dicHeight.Add(model.R, model.Height.Value);
                        }
                    }
                    else if (dicHeight.ContainsKey(model.R))
                    {
                        cellHeight = dicHeight[model.R];
                    }
    
    
                    IRow row = sheet1.GetRow(model.R);
                    if (row == null)
                    {
                        row = sheet1.CreateRow(model.R);
                        row.Height = cellHeight;
                    }
                    var cell = row.GetCell(model.C);
                    if (cell == null)
                    {
                        sheet1.SetColumnWidth(model.C, cellWidth);
                        cell = row.CreateCell(model.C);
                    }
                    cell.SetCellValue(model.Value);
    
                    //合并
                    if (model.REnd > model.R || model.CEnd > model.C)
                    {
                        for (int r = model.R; r <= model.REnd; r++)
                        {
                            var rowr = sheet1.GetRow(r);
                            if (rowr == null)
                            {
                                rowr = sheet1.CreateRow(r);
                                rowr.Height = cellHeight;
                            }
                            for (int c = model.C + 1; c <= model.CEnd; c++)
                            {
                                var cellc = rowr.GetCell(c);
                                if (cellc == null)
                                {
                                    sheet1.SetColumnWidth(c, cellWidth);
                                    cellc = rowr.CreateCell(c);
                                }
                            }
                        }
    
                        // 合并单元格  
                        CellRangeAddress cra = new CellRangeAddress(model.R, model.REnd, model.C, model.CEnd); // 起始行, 终止行, 起始列, 终止列  
                        sheet1.AddMergedRegion(cra);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
        }
    
        /// <summary>
        /// 单元格对象
        /// </summary>
        public class CellModel
        {
            #region 重载构造
            private CellModel() { }
            public CellModel(int r, int c, bool value, int? rend = null, int? cend = null, int? width = null, short? height = null)
            {
                init(r,c,value,rend,cend,width,height);
            }
            public CellModel(int r, int c, string value, int? rend = null, int? cend = null, int? width = null, short? height = null)
            {
                init(r, c, value, rend, cend, width, height);
            }
            public CellModel(int r, int c, IRichTextString value, int? rend = null, int? cend = null, int? width = null, short? height = null)
            {
                init(r, c, value, rend, cend, width, height);
            }
            public CellModel(int r, int c, DateTime value, int? rend = null, int? cend = null, int? width = null, short? height = null)
            {
                init(r, c, value, rend, cend, width, height);
            }
            public CellModel(int r, int c, double value, int? rend = null, int? cend = null, int? width = null, short? height = null)
            {
                init(r, c, value, rend, cend, width, height);
            }
            #endregion
    
            private void init(int r, int c, dynamic value, int? rend = null, int? cend = null, int? width = null, short? height = null) 
            {
                R = r;
                C = c;
                if (value == null) { value = ""; }
                Value = value;
                if (rend == null) { rend = r; }
                if (cend == null) { cend = c; }
                REnd = rend.Value;
                CEnd = cend.Value;
                Width = width;
                Height = height;
            }
    
            internal int R;//行坐标
            internal int C;//列坐标
            internal int REnd;//单元格宽结束坐标,用于占多行(合并单元格)
            internal int CEnd;//单元格列结束坐标,用于占多列(合并单元格)
    
            internal dynamic Value;//单元格值,类型可选:bool、string、IRichTextString、DateTime、double
    
            internal int? Width;
            internal short? Height;
        }
    }
    View Code

    调用例

    [HttpGet]
            public ActionResult ExportExcel(string searchJsonStr, int pageSize, int pageIndex)
            {
                try
                {
    
                    //数据
                    Search_ViewOrderMain search = JsonConvert.DeserializeObject<Search_ViewOrderMain>(searchJsonStr);
                    var dataList = OperateContext.Current.BLLSession.IOrder_MainBLL.GetOrderList(search, pageSize, pageIndex).Data;
                    var states = EnumberHelper.EnumToList<EnumOrderState>();
    
                    //生成工作簿对象
                    int w = 4500;
                    XSSFWorkbookCreateHelper bookHelper = new XSSFWorkbookCreateHelper(w, 400, NPOI.SS.UserModel.VerticalAlignment.Center);
                    int r = 0, c = 0;
    
                    //表头
                    bookHelper.AddCell(new CellModel(r, c++, "Customer Name", 1));//用户名
                    bookHelper.AddCell(new CellModel(r, c++, "Customer Email", 1));
                    bookHelper.AddCell(new CellModel(r, c++, "Customer Phone Number", 1));
                    bookHelper.AddCell(new CellModel(r, c, "Customer Address", cend: c+4));//地址
                    c += 5;
                    bookHelper.AddCell(new CellModel(r, c++, "Invoice Number", 1));
                    bookHelper.AddCell(new CellModel(r, c++, "Invoice Date", 1));
                    bookHelper.AddCell(new CellModel(r, c++, "Invoice Due Date", 1));
                    bookHelper.AddCell(new CellModel(r, c++, "Product Name", 1));
                    bookHelper.AddCell(new CellModel(r, c++, "Product Part Number", 1));//商品零件号
                    bookHelper.AddCell(new CellModel(r, c++, "Quantity", 1));//商品数量
                    bookHelper.AddCell(new CellModel(r, c++, "Unit Price", 1));//单价
                    bookHelper.AddCell(new CellModel(r, c++, "Sales Total", 1));//总销售数量
                    bookHelper.AddCell(new CellModel(r, c++, "Shipping Fee", 1));//运费
                    bookHelper.AddCell(new CellModel(r, c++, "Sales Tax Rate", 1));//税率
                    bookHelper.AddCell(new CellModel(r, c++, "Sales Tax Total", 1));//税费
                    bookHelper.AddCell(new CellModel(r, c++, "Invoice Total", 1));//发票总额
                    bookHelper.AddCell(new CellModel(r, c++, "Payment Status", 1));
                    bookHelper.AddCell(new CellModel(r, c++, "Payment Method", 1));
                    bookHelper.AddCell(new CellModel(r, c++, "Payment Service FEE", 1));//支付服务费
    
                    r++;
                    c = 3;
                    bookHelper.AddCell(new CellModel(r, c++, "street Address"));//街道地址
                    bookHelper.AddCell(new CellModel(r, c++, "city"));
                    bookHelper.AddCell(new CellModel(r, c++, "State"));
                    bookHelper.AddCell(new CellModel(r, c++, "zipcode"));//邮编
                    bookHelper.AddCell(new CellModel(r, c++, "country"));//国家地区
    
                    //表内容
                    var rAdd = 0;//一行订单因为多种商品要多换的行数
                    for (int i = 0; i < dataList.Count; i++)
                    {
                        View_OrderMain item = dataList[i];
                        r++;
                        c = 0;
                        rAdd = item.OrderProduct.Count-1;//多换的行数
    
                        bookHelper.AddCell(new CellModel(r, c++, item.MemberName, r + rAdd));
                        bookHelper.AddCell(new CellModel(r, c++, item.Email, r + rAdd));
                        bookHelper.AddCell(new CellModel(r, c++, item.Mobile, r + rAdd));
    
                        string state = states.Exists(x => x.EnumValue == item.OrderState) ? states.FirstOrDefault(x => x.EnumValue == item.OrderState).Desction : "";
                        bookHelper.AddCell(new CellModel(r, c++, string.IsNullOrWhiteSpace(item.ShippingAddress)? item.ShippingAddress2: item.ShippingAddress, r + rAdd,  w * 3));
    
                        string city = "";
                        if (item.Region.Split(',').Length > 1) 
                        {
                            city = item.Region.Split(',')[item.Region.Split(',').Length-1];
                        }
                        bookHelper.AddCell(new CellModel(r, c++, city, r + rAdd));
    
                        bookHelper.AddCell(new CellModel(r, c++, state, r + rAdd));
                        bookHelper.AddCell(new CellModel(r, c++, item.ZipCode, r + rAdd));
                        bookHelper.AddCell(new CellModel(r, c++, item.Region, r + rAdd,  w * 3));
    
                        bookHelper.AddCell(new CellModel(r, c++, item.InvoiceNumber, r + rAdd));
                        bookHelper.AddCell(new CellModel(r, c++, item.InvoiceDate.HasValue? item.InvoiceDate.Value.ToString("yyyy--MM-dd HH:mm"):"", r + rAdd));
                        bookHelper.AddCell(new CellModel(r, c++, item.InvoiceDueDate.HasValue ? item.InvoiceDueDate.Value.ToString("yyyy--MM-dd HH:mm") : "", r + rAdd));
                        
    
                        //商品
                        int salesTotal = 0;//商品总销售
                        int startC = c;
                        int startR = r;
                        foreach (var product in item.OrderProduct)
                        {
                            c = startC;//恢复列
                            bookHelper.AddCell(new CellModel(r, c++, product.ProductName));
                            bookHelper.AddCell(new CellModel(r, c++, product.PartNumber));
                            bookHelper.AddCell(new CellModel(r, c++, product.SaleNumber));//销售数量
                            bookHelper.AddCell(new CellModel(r, c++, product.Price.ToString()));
    
                            salesTotal += product.SaleNumber;
                            r++;//商品换行
                        }
    
                        r = startR;//恢复行
                        bookHelper.AddCell(new CellModel(r, c++, salesTotal, r + rAdd));
                        bookHelper.AddCell(new CellModel(r, c++, item.Postage.ToString(), r + rAdd));//运费
                        bookHelper.AddCell(new CellModel(r, c++, item.CostRatio.ToString(), r + rAdd));//税率
                        decimal tax = item.CostRatio<=0?0: Math.Round(((item.TotalFee + item.FreeFee) / item.CostRatio),2,MidpointRounding.AwayFromZero);
                        bookHelper.AddCell(new CellModel(r, c++, tax.ToString(), r + rAdd));//税费
                        bookHelper.AddCell(new CellModel(r, c++, item.InvoiceTotal.ToString(), r + rAdd,  w * 3));
    
                        //支付信息
                        bookHelper.AddCell(new CellModel(r, c++, item.PayTime.HasValue? "Payment completed" : "Awaiting payment", r + rAdd));
    
                        string payMethod = "";
                        if (item.CreateType == 0) 
                        {
                            switch (item.PayType)
                            {
    
                                case 1: payMethod = "Card"; break;
                                case 2: payMethod = "Online"; break;
                                case 3: payMethod = "Account Balance"; break;
                                case 4: payMethod = "Insufficient account balance";
                                    payMethod += "
    Account Balance:" + item.PayBalance;
                                    payMethod += "
    Online:" + item.PayCash;
                                    break;
                                default:
                                    break;
                            }
                        }
                        bookHelper.AddCell(new CellModel(r, c++, payMethod, r + rAdd));
    
                        bookHelper.AddCell(new CellModel(r, c++, "" , r + rAdd));//Payment Service FEE
    
                        r += rAdd;//因为商品多换的行数
                    }
                     
                    string path = AppDomain.CurrentDomain.BaseDirectory + "/template/excel";
                    if (!System.IO.Directory.Exists(path))
                    {
                        System.IO.Directory.CreateDirectory(path);
                    }
                    path += "/order_data.xlsx";
                    using (FileStream ms = new FileStream(path, FileMode.Create))
                    {
                        bookHelper.book.Write(ms);
                        string titleName = "OrderData";
                        return File(path, "application/vnd.ms-excel", titleName + ".xlsx");
                    }
                }
                catch (Exception ex)
                {
                    Common.LogHelper.Instance.Error("导出统计数据出错", ex);
                }
    
                return Content("Export failed");
            }
    View Code
                //导出excel详细
                scope.ExportExcel = function () {
                    if (scope.rowCount > 1000) {
                        alert("本次导出数据较多,成功导出后将自动下载,请勿频繁操作!")
                    }
                    if (scope.rowCount > 0) {
                        window.open("ExportExcel?searchJsonStr=" + JSON.stringify(scope.searchModel));
                    }
    
                }
    View Code
  • 相关阅读:
    [转]使用RenderQueueListener针对不同的渲染组改变摄像机的裁剪面
    [转]Calculating Stereo Pairs
    [转]Ogre如何在渲染时切换指定物体的材质技术
    <转>C#操作word
    SQL常用语句二
    SQL高级查询
    C# word打印文档打印不全(数据为完全发送至打印机,程序已经退出)
    access事务插入多条记录
    C#操作word的一些基本方法(word打印,插入文件,插入图片,定位页眉页脚,去掉横线)
    Sqlserver 存储过程中结合事务的代码
  • 原文地址:https://www.cnblogs.com/lanofsky/p/13602334.html
Copyright © 2020-2023  润新知