将List集合转换成Excel文件，并使用NPOI美化Excel（设置首行，内容单元格样式，设置列宽，合并单元格）

将List集合转换成Excel文件，并美化Excel

	/// <summary>
    /// 将List集合转换成Excel文件，并美化Excel
    /// </summary>
    /// <param name="orders">List集合</param>
    /// <param name="fileName">保存路径</param>
    public bool XMLToExcelFile(List<BOrder> orders, string filePath)
    {
        if (filePath == null) { return false; }
        IWorkbook workbook = new HSSFWorkbook();
        try
        {
            ISheet sheet = workbook.CreateSheet("Sheet1");
            sheet.SetColumnWidth(0, 40 * 256);//设置列宽
            sheet.SetColumnWidth(1, 30 * 256);
            sheet.SetColumnWidth(2, 30 * 256);
            sheet.SetColumnWidth(3, 10 * 256);
            sheet.SetColumnWidth(4, 10 * 256);
            sheet.SetColumnWidth(5, 80 * 256);
            #region
            //首行样式
            NPOI.SS.UserModel.IFont fontAll = workbook.CreateFont();
            fontAll.FontName = "Microsoft YaHei";
            fontAll.FontHeight = 10 * 20;
            fontAll.IsBold = true;                
            ICellStyle CellStyle = workbook.CreateCellStyle();
            CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            CellStyle.BottomBorderColor = HSSFColor.Black.Index;
            CellStyle.LeftBorderColor = HSSFColor.Black.Index;
            CellStyle.RightBorderColor = HSSFColor.Black.Index;
            CellStyle.TopBorderColor = HSSFColor.Black.Index;
            CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            CellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            CellStyle.SetFont(fontAll);

            //单元格样式
            NPOI.SS.UserModel.IFont Contentfont = workbook.CreateFont();
            fontAll.FontName = "Microsoft YaHei";
            fontAll.FontHeight = 10 * 20;
            fontAll.IsBold = true;
            ICellStyle Style = workbook.CreateCellStyle();
            Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            Style.BottomBorderColor = HSSFColor.Black.Index;
            Style.LeftBorderColor = HSSFColor.Black.Index;
            Style.RightBorderColor = HSSFColor.Black.Index;
            Style.TopBorderColor = HSSFColor.Black.Index;
            Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            Style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            Style.SetFont(Contentfont);
            #endregion
            for (int i = 0; i < orders.Count; i++)
            { 
                    int typeCount = 0;
                    int typeNum = 0;
                    int total = 0;
                    int rownum = 0;
                    IRow row1 = sheet.CreateRow(0);
                    row1.CreateCell(0).SetCellValue("客户单号");
                    row1.CreateCell(1).SetCellValue("订单号");
                    row1.CreateCell(2).SetCellValue("品牌");
                    row1.CreateCell(3).SetCellValue("名称");
                    row1.CreateCell(4).SetCellValue("价格");
                    row1.CreateCell(5).SetCellValue("备注");
                    //设置首行单元格样式
                    for (int h = 0; h < row1.Cells.Count; h++)
                    {
                        row1.GetCell(h).CellStyle = CellStyle;
                    }
                    //绑定数据
                    for (int r = 0; r < orders.Count; r++)
                    {
                        if (orders[r].OrderType.Equals("B"))
                        {
                            typeCount = 1;
                            typeNum = typeNum + 1;//批量单总数
                            total= total+ orders[r].Name.Count;//获得所有的SPH数量

                            for (int t = 1; t <= typeCount; t++)
                            {
                                for (int o = 0; o < orders[r].SPH.Count; o++)
                                {
                                    rownum = t + rownum;
                                    IRow row = sheet.CreateRow(rownum);
                              		row.CreateCell(0).SetCellValue(orders[r].CustomerOrderNumber);
                                    row.CreateCell(1).SetCellValue(orders[r].Number);
                                    row.CreateCell(2).SetCellValue(orders[r].BrandType);
                                    row.CreateCell(3).SetCellValue(orders[r].Name);
                                    row.CreateCell(4).SetCellValue(orders[r].Price);
                                    row.CreateCell(5).SetCellValue(orders[r].Remark);
       
                                    //设置单元格样式
                                    for (int h = 0; h < row.Cells.Count; h++)
                                    {
                                        row.GetCell(h).CellStyle = Style;
                                    }
                                }
                            }
                        }
                    }
                    //合并单元格
                    for (int q = 1; q < total+1; q++)
                    {
                        string value = sheet.GetRow(q).GetCell(0).StringCellValue;//客户单号
                        string orderNum = sheet.GetRow(q).GetCell(1).StringCellValue;//订单号
                        int end = q;
                        //找到结束为止
                        for (int j = q + 1; j < total+1; j++)
                        {
                            string value1 = sheet.GetRow(j).GetCell(0).StringCellValue;
                            string orderNum1 = sheet.GetRow(j).GetCell(1).StringCellValue;
                            if (value != value1&& orderNum!= orderNum1)
                            {
                                end = j - 1;
                                break;
                            }
                            else if (value == value1 && j == total && orderNum == orderNum1)
                            {
                                end = j;
                                break;
                            }
                        }
                        sheet.AddMergedRegion(new CellRangeAddress(q, end, 0, 0));
                        sheet.AddMergedRegion(new CellRangeAddress(q, end, 1, 1));
                        sheet.AddMergedRegion(new CellRangeAddress(q, end, 2, 2));
                        sheet.AddMergedRegion(new CellRangeAddress(q, end, 5, 5));
                        q = end;
                    }
                }
            }
            //写入文件
            FileStream xlsfile = new FileStream(filePath, FileMode.OpenOrCreate);
            workbook.Write(xlsfile);
            //关闭文件流
            xlsfile.Close();
            workbook.Close();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }