NPOI导出EXCEL样式

public void Export(DataRequest<ExportModel> request, DataResponse<dynamic> response)
        {
            
            try
            {
                var order = GetShopModel(request.ObjectData.guid);

                var newFile = Directory.GetCurrentDirectory() + "\\Export\\Order" + order.OrderDate + ".xls";
                string p = System.IO.Path.GetDirectoryName(newFile);
                if (!System.IO.Directory.Exists(p))
                    System.IO.Directory.CreateDirectory(p);

                var ShopGid = order.ShopGid;

                string startTime = order.OrderDate.Split('')[0] + " 00:00:00";
                string endTime = order.OrderDate.Split('')[1] + " 23:59:59";
               
                using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
                {
                    IWorkbook workbook = new XSSFWorkbook();
                    #region 车辆销售订单明细
                    ISheet sheet1 = workbook.CreateSheet("门店销售订单明细");
                    
                    //index代表多少行
                    var rowIndex = 0;
                    //创建表头行
                    IRow row = sheet1.CreateRow(rowIndex);
                    row.HeightInPoints = 30;//行高
         
                   
                    row.CreateCell(0).SetCellValue("门店商铺");
                    row.CreateCell(1).SetCellValue("商铺号码");
                    row.CreateCell(2).SetCellValue("销售单号");
                    row.CreateCell(3).SetCellValue("提车时间");
                   

                    //填充数据
                    List<ExportOrderModel> OrderShops = GetModelExl(ShopGid.Value, startTime, endTime);
                    for (int i = 0; i < OrderShops.Count; i++)
                    {
                        IRow row1 = sheet1.CreateRow(i + 1);
                        ICell cell = row1.CreateCell(0);  //创建第一列

                        #region 标题行设置字体
                        ICellStyle style = workbook.CreateCellStyle();//创建样式对象
                       
                        //设置单元格的样式:水平对齐填充
                        style.Alignment = HorizontalAlignment.Left;
                        style.VerticalAlignment = VerticalAlignment.Center;//垂直居中
                       
                        //自动换行
                        style.WrapText = true;
                        IFont font = workbook.CreateFont(); //创建一个字体样式对象
                        font.FontName = "宋体"; //和excel里面的字体对应
                      
                        font.IsItalic = false; //斜体
                        font.FontHeightInPoints =15 ;//字体大小
                        //font.Boldweight = short.MaxValue;//字体加粗
                        font.Boldweight = (Int16)FontBoldWeight.Bold;//加粗;
                        style.SetFont(font); //将字体样式赋给样式对象
                        //cell.CellStyle = style; //把样式赋给单元格
                        
                        /*修改指定单元格样式 如果要修改行样式则需要将row.Cells.Count循环出来,挨个设置!*/
                        // row.Cells[1].CellStyle = style;
                        for (int k = 0; k < row.Cells.Count; k++)
                        {
                            row.Cells[k].CellStyle = style;
                            //列宽25 //设置列宽
                            sheet1.SetColumnWidth(k, 25 * 256);
                        }

                        #endregion

                        #region  单元格列值属性样式

                        //for (int jj = 0; jj < row1.Cells.Count; jj++)
                        //{
                        //    row1.Cells[jj].CellStyle = style;

                        //}
                        //cell.CellStyle = style;

                       

                        #endregion

                        cell.SetCellValue(OrderShops[i].ShopName);
                        cell = row1.CreateCell(1);//设置单元格的值
                        //设置列值样式
                        //row1.CreateCell(0).CellStyle = style;
                        cell.SetCellValue(order.ShopNo);
                        cell = row1.CreateCell(2);
                        cell.SetCellValue(OrderShops[i].OrderSaleNo);
                        cell = row1.CreateCell(3);
                        cell.SetCellValue(OrderShops[i].PayDateTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
                       
                    }

                    #endregion

                    #endregion

                    #region 车辆销售货明细表格

                    //创建表头
                    ISheet sheet6 = workbook.CreateSheet("车辆销售换货明细表格");
                    IRow row6 = sheet6.CreateRow(0);
                    row6.HeightInPoints = 30;//行高
                    row6.CreateCell(0).SetCellValue("换货单号");
                    row6.CreateCell(1).SetCellValue("新车辆颜色");
                    row6.CreateCell(2).SetCellValue("原车辆型号类型");
                   

                    var table = GetChangeOrder(ShopGid.Value, startTime, endTime);
                    j = 0;
                    foreach (DataRow item in table.Rows)
                    {
                        IRow row1 = sheet6.CreateRow(j + 1);
                        ICell cell = row1.CreateCell(0);

                        #region 标题行设置字体
                        ICellStyle style = workbook.CreateCellStyle();//创建样式对象

                        //设置单元格的样式:水平对齐填充
                        style.Alignment = HorizontalAlignment.Left;
                        style.VerticalAlignment = VerticalAlignment.Center;//垂直居中

                        //自动换行
                        style.WrapText = true;
                        IFont font = workbook.CreateFont(); //创建一个字体样式对象
                        font.FontName = "宋体"; //和excel里面的字体对应

                        font.IsItalic = false; //斜体
                        font.FontHeightInPoints = 15;//字体大小
                        //font.Boldweight = short.MaxValue;//字体加粗
                        font.Boldweight = (Int16)FontBoldWeight.Bold;//加粗;
                        style.SetFont(font); //将字体样式赋给样式对象
                                             //cell.CellStyle = style; //把样式赋给单元格

                        /*修改指定单元格样式 如果要修改行样式则需要将row.Cells.Count循环出来,挨个设置!*/
                        // row.Cells[1].CellStyle = style;
                        for (int k = 0; k < row6.Cells.Count; k++)
                        {
                            row6.Cells[k].CellStyle = style;
                            //列宽25
                            sheet6.SetColumnWidth(k, 25 * 256);
                        }

                        #endregion

                        cell.SetCellValue(DataConvertHelper.GetString(item["ChangeNo"]));
                        cell = row1.CreateCell(1);
                        cell.SetCellValue(DataConvertHelper.GetString(item["OrderColor"]));
                        cell = row1.CreateCell(2);
                        cell.SetCellValue(DataConvertHelper.GetString(item["OrderType"]));
                      
                        j++;
                    }
                    #endregion

                    workbook.Write(fs);
                }
                response.Tag = Common.ExportFile.ExportFlag;
                response.ObjectData = new Common.ExportFile
                {
                    FilePath = newFile,
                    FileName = "订单信息" + 20190101 + ".xls"
                };
            }
            catch (Exception ex)
            {
                
             LogHelper.Debug("异常" + ex.Message, "导出异常", this.GetType().ToString());
             response.ObjectData= ex.Message;

            }
            


        }

 

posted @ 2019-09-04 13:24  蜜雪粮液  阅读(547)  评论(0编辑  收藏  举报