.net webapi 后台导出excel 申请付款单实例

        [HttpGet, AllowAnonymous]
        public void ExportSettlementPrint(string code)
        {
            FinSettlementModel settlementDetailModels = this._finSettlementService.GetFinSettlementByCode(code);//付款单

            #region 导出excel 样式设置
            HSSFWorkbook workbook = new HSSFWorkbook();//建立空白工作簿
            ISheet sheet = workbook.CreateSheet();//在工作簿中，建立空白工作表

            //title 第0行
            IRow row = sheet.CreateRow(0);//新建行
            ICell cell = row.CreateCell(0);//新建列
            cell.SetCellValue(code);
            ICellStyle stylecell0 = workbook.CreateCellStyle();
            stylecell0.Alignment = HorizontalAlignment.Left;//设置单元格样式：水平对齐居中
            stylecell0.VerticalAlignment = VerticalAlignment.Center;
            stylecell0.BorderBottom = BorderStyle.Thin;
            stylecell0.BorderLeft = BorderStyle.Thin;
            //stylecell0.BorderRight = BorderStyle.Thin;
            stylecell0.BorderTop = BorderStyle.Thin;
            IFont fontcell0 = workbook.CreateFont();//字体样式
            fontcell0.Boldweight = short.MaxValue;//字体加粗
            fontcell0.FontHeightInPoints = 10;//字体大小
            stylecell0.SetFont(fontcell0);
            cell.CellStyle = stylecell0;


            ICell cell1t = row.CreateCell(1);
            cell1t.SetCellValue("付款申请单");

            ICellStyle style0 = workbook.CreateCellStyle();
            style0.Alignment = HorizontalAlignment.Center;//设置单元格样式：水平对齐居中
            style0.VerticalAlignment = VerticalAlignment.Center;
            style0.BorderBottom = BorderStyle.Thin;
           // style.BorderLeft = BorderStyle.Thin;
            style0.BorderRight = BorderStyle.Thin;
            style0.BorderTop = BorderStyle.Thin;
            IFont font0 = workbook.CreateFont();//字体样式
            font0.Boldweight = short.MaxValue;//字体加粗
            font0.FontHeightInPoints =10;//字体大小
            style0.SetFont(font0);
            cell1t.CellStyle = style0;

            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;//设置单元格样式：水平对齐居中
            style.VerticalAlignment = VerticalAlignment.Center;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            IFont font = workbook.CreateFont();//字体样式
            font.Boldweight = short.MaxValue;//字体加粗
            font.FontHeightInPoints = 10;//字体大小
            style.SetFont(font);

            for (int p1 = 2; p1 < 8; p1++)
            {
                ICell rowJrowCell1 = row.CreateCell(p1);
                rowJrowCell1.SetCellValue("");
                rowJrowCell1.CellStyle = style;
            }
            row.Height = 30 * 20;

            //统一样式
            ICellStyle styleSame = workbook.CreateCellStyle();
            styleSame.BorderBottom = BorderStyle.Thin;
            styleSame.BorderLeft = BorderStyle.Thin;
            styleSame.BorderRight = BorderStyle.Thin;
            styleSame.BorderTop = BorderStyle.Thin;
            styleSame.Alignment = HorizontalAlignment.Center;
            styleSame.VerticalAlignment = VerticalAlignment.Center;
            IFont fontSame = workbook.CreateFont();
            fontSame.FontHeightInPoints = 10;
            styleSame.SetFont(fontSame);
            //统一样式

            #region 第一行数据
            //第一行 第一单元格
            IRow row1 = sheet.CreateRow(1);
            ICell cell1 = row1.CreateCell(0);
            cell1.SetCellValue("日期：");
            cell1.CellStyle = styleSame;
            // 2
            ICell cell2 = row1.CreateCell(1);
            cell2.SetCellValue(settlementDetailModels.createdtime.ToShortDateString());//创建时间
            cell2.CellStyle = styleSame;
            //3
            ICell cell3 = row1.CreateCell(2);
            cell3.SetCellValue("请款部门：");
            cell3.CellStyle = styleSame;
            //4
            ICell cell4 = row1.CreateCell(3);
            cell4.SetCellValue(settlementDetailModels.facname);
            cell4.CellStyle = styleSame;
            //5
            ICell cell5 = row1.CreateCell(4);
            cell5.SetCellValue("申请付款日期：");
            cell5.CellStyle = styleSame;
            //6
            ICell cell6 = row1.CreateCell(5);
            cell6.SetCellValue(settlementDetailModels.applytime.ToString());
            cell6.CellStyle = styleSame;
            //7
            ICell cell7 = row1.CreateCell(6);
            cell7.SetCellValue("结算总金额：");
            cell7.CellStyle = styleSame;
            //8
            ICell cell8 = row1.CreateCell(7);
            cell8.SetCellValue("￥" + settlementDetailModels.totalamount);
            cell8.CellStyle = styleSame;
            row1.Height = 30 * 20;
            #endregion 第一行数据

            #region 第二行数据
            IRow row2 = sheet.CreateRow(2);
            ICell row2Cell1 = row2.CreateCell(0);
            row2Cell1.SetCellValue("摘要：");
            row2Cell1.CellStyle = styleSame;
            for (int p= 1; p < 8; p++)
            {
                ICell rowJrow2Cell1 = row2.CreateCell(p);
                rowJrow2Cell1.SetCellValue("");
                rowJrow2Cell1.CellStyle = styleSame;
            }

            ICellStyle cellstyleNote1 = workbook.CreateCellStyle();
            cellstyleNote1.BorderBottom = BorderStyle.Thin;
            cellstyleNote1.BorderLeft = BorderStyle.Thin;
            cellstyleNote1.BorderRight = BorderStyle.Thin;
            cellstyleNote1.BorderTop = BorderStyle.Thin;
            cellstyleNote1.Alignment = HorizontalAlignment.Left;
            cellstyleNote1.VerticalAlignment = VerticalAlignment.Center;
            cellstyleNote1.SetFont(fontSame);

            ICell row2Cell2 = row2.CreateCell(1);
            row2Cell2.SetCellValue(settlementDetailModels.note);
            row2Cell2.CellStyle = cellstyleNote1;
            row2.Height = 30 * 20;
            #endregion 第二行数据

            #region 第三行 数据title
            IRow row3 = sheet.CreateRow(3);
            ICell row3Cell1 = row3.CreateCell(0);
            row3Cell1.SetCellValue("费用项目");
            row3Cell1.CellStyle = styleSame;
            ICell row3Cell2 = row3.CreateCell(1);
            row3Cell2.SetCellValue("金额");
            row3Cell2.CellStyle = styleSame;
            ICell row3Cell3 = row3.CreateCell(2);
            row3Cell3.SetCellValue("大写金额");
            row3Cell3.CellStyle = styleSame;
            ICell row3Cell4 = row3.CreateCell(3);
            row3Cell4.SetCellValue("支付方式");
            row3Cell4.CellStyle = styleSame;
            ICell row3Cell5 = row3.CreateCell(4);
            row3Cell5.SetCellValue("收款方名称");
            row3Cell5.CellStyle = styleSame;
            ICell row3Cell6 = row3.CreateCell(5);
            row3Cell6.SetCellValue("账号");
            row3Cell6.CellStyle = styleSame;
            ICell row3Cell7 = row3.CreateCell(6);
            row3Cell7.SetCellValue("开户行");
            row3Cell7.CellStyle = styleSame;
            ICell row3Cell8 = row3.CreateCell(7);
            row3Cell8.SetCellValue("付款主体");
            row3Cell8.CellStyle = styleSame;
            row3.Height = 30 * 20;
            #endregion 第三行 数据title

            int rowNumber = 4;
            #region 内容行
            foreach (var r in settlementDetailModels.supplys)
            {
                IRow rowN = sheet.CreateRow(rowNumber);

                ICell rowNCell1 = rowN.CreateCell(0);
                rowNCell1.SetCellValue(r.project);
                rowNCell1.CellStyle = styleSame;

                ICell rowNCell2 = rowN.CreateCell(1);
                rowNCell2.SetCellValue(r.totalamount.ToString());
                rowNCell2.CellStyle = styleSame;

                ICell rowNCell3 = rowN.CreateCell(2);
                rowNCell3.SetCellValue(DaXie(r.totalamount.ToString()));
                rowNCell3.CellStyle = styleSame;

                ICell rowNCell4 = rowN.CreateCell(3);
                rowNCell4.SetCellValue(r.paytypename);
                rowNCell4.CellStyle = styleSame;

                ICell rowNCell5 = rowN.CreateCell(4);
                rowNCell5.SetCellValue(r.collectusername);
                rowNCell5.CellStyle = styleSame;

                ICell rowNCell6 = rowN.CreateCell(5);
                rowNCell6.SetCellValue(getBankCardNumber(r.collectbankcard));
                rowNCell6.CellStyle = styleSame;

                ICell rowNCell7 = rowN.CreateCell(6);
                rowNCell7.SetCellValue(r.collectbank);
                rowNCell7.CellStyle = styleSame;

                ICell rowNCell8 = rowN.CreateCell(7);
                rowNCell8.SetCellValue(settlementDetailModels.facname);
                rowNCell8.CellStyle = styleSame;
                rowN.Height = 30 * 20;

                rowNumber++;

            }
            #endregion 内容行

            int numberSupply = settlementDetailModels.supplys.Count();
            if (numberSupply < 6)
            {
                for (int i = 0; i < 7 - numberSupply; i++)
                {
                    IRow rowI = sheet.CreateRow(rowNumber);
                    for(int j = 0; j < 8; j++)
                    {
               
                        ICell rowJCell1 = rowI.CreateCell(j);
                        rowJCell1.SetCellValue("");
                        rowJCell1.CellStyle = styleSame;
                    }
                    rowI.Height = 30 * 20;
                    rowNumber++;
                }
            }
            int ammountNumber = rowNumber;
            #region  合计行
            IRow rowAmount = sheet.CreateRow(rowNumber++);
            ICell rowAmountCell1 = rowAmount.CreateCell(0);
            rowAmountCell1.SetCellValue("合计");
            rowAmountCell1.CellStyle = style;

            ICell rowAmountCell2 = rowAmount.CreateCell(1);
            rowAmountCell2.SetCellValue("￥" + settlementDetailModels.totalamount.ToString());
            rowAmountCell2.CellStyle = style;

            ICell rowAmountCell3 = rowAmount.CreateCell(2);

            ICell rowAmountCell4 = rowAmount.CreateCell(3);
            rowAmountCell4.SetCellValue(DaXie(settlementDetailModels.totalamount.ToString()));
            rowAmountCell4.CellStyle = style;
            for (int j4 = 4; j4 < 8; j4++)
            {

                ICell rowJ4Cell1 = rowAmount.CreateCell(j4);
                rowJ4Cell1.SetCellValue("");
                rowJ4Cell1.CellStyle = styleSame;
            }
            rowAmount.Height = 30 * 20;
            #endregion 合计行

            int backRow = rowNumber;
            IRow rowBack = sheet.CreateRow(rowNumber++);//空白行
            for (int j = 0; j < 8; j++)
            {
                
                ICell rowJrowBackCell1 = rowBack.CreateCell(j);
                rowJrowBackCell1.SetCellValue("");
                rowJrowBackCell1.CellStyle = styleSame;
            }
            rowBack.Height = 30 * 20;
            #region 请款人 相关信息
            int applyRow = rowNumber;
            IRow rowApply = sheet.CreateRow(rowNumber++);
            ICell rowApplyCell1 = rowApply.CreateCell(0);
            rowApplyCell1.SetCellValue("请款人：");
            rowApplyCell1.CellStyle = styleSame;

            ICell rowApplyCell2 = rowApply.CreateCell(1);
            rowApplyCell2.SetCellValue("");
            rowApplyCell2.CellStyle = styleSame;
            ICell rowApplyCell3 = rowApply.CreateCell(2);
            rowApplyCell3.SetCellValue("");
            rowApplyCell3.CellStyle = styleSame;
            ICell rowApplyCell4 = rowApply.CreateCell(3);
            rowApplyCell4.SetCellValue("部门领导审核：");
            rowApplyCell4.CellStyle = styleSame;
            ICell rowApplyCell5 = rowApply.CreateCell(4);
            rowApplyCell5.SetCellValue("");
            rowApplyCell5.CellStyle = styleSame;
            ICell rowApplyCell6 = rowApply.CreateCell(5);
            rowApplyCell6.SetCellValue("");
            rowApplyCell6.CellStyle = styleSame;
            ICell rowApplyCell7 = rowApply.CreateCell(6);
            rowApplyCell7.SetCellValue("财务审核：");
            rowApplyCell7.CellStyle = styleSame;
            ICell rowApplyCell8 = rowApply.CreateCell(7);
            rowApplyCell8.SetCellValue("");
            rowApplyCell8.CellStyle = styleSame;
            rowApply.Height = 30 * 20;
            #endregion 请款人相关信息

            int descriptionTitleNum = rowNumber;
            IRow rowDescriptTitle = sheet.CreateRow(rowNumber++);
            ICell rowDescriptTitleCell1 = rowDescriptTitle.CreateCell(0);
            rowDescriptTitleCell1.SetCellValue("附件（请附发票，收据等）");
            rowDescriptTitleCell1.CellStyle = styleSame;
            rowDescriptTitle.Height = 30 * 20;
            for (int jp =1; jp < 8; jp++)
            {
                ICell rowJrowDescriptTitleCell = rowDescriptTitle.CreateCell(jp);
                rowJrowDescriptTitleCell.SetCellValue("");
                rowJrowDescriptTitleCell.CellStyle = styleSame;
            }

            #region 备注说明
            int rowNoteNum = rowNumber;
            //备注行样式
            ICellStyle cellstyleNote = workbook.CreateCellStyle();
            cellstyleNote.BorderBottom = BorderStyle.Thin;
            cellstyleNote.BorderLeft = BorderStyle.Thin;
            cellstyleNote.BorderRight = BorderStyle.Thin;
            cellstyleNote.BorderTop = BorderStyle.Thin;
            cellstyleNote.Alignment = HorizontalAlignment.Left;
            cellstyleNote.VerticalAlignment = VerticalAlignment.Center;
            cellstyleNote.WrapText = true;
            cellstyleNote.SetFont(fontSame);

            IRow rowNote = sheet.CreateRow(rowNumber++);
            ICell rowNoteCell = rowNote.CreateCell(0);
            rowNoteCell.SetCellValue(" 1.需要向公司申请对外支付时候填写此表；
 2.用途说明填写：费用支出的实际用途，填写大类即可（如：配件及精品、办公用品、人力资源、装修等）；
 3.务必填写好支付方式：转账（首选）、支票和对方的收款账号及银行信息。");
            rowNoteCell.CellStyle = cellstyleNote;
            for (int jpy = 1; jpy < 8; jpy++)
            {
                ICell rowJrowNoteCell = rowNote.CreateCell(jpy);
                rowJrowNoteCell.SetCellValue("");
                rowJrowNoteCell.CellStyle = styleSame;
            }
            rowNote.Height = 30 * 20;

            IRow rowNote1 = sheet.CreateRow(rowNumber++);
            ICell rowNote1Cell1 = rowNote1.CreateCell(0);
            rowNote1Cell1.SetCellValue("");
            rowNote1Cell1.CellStyle = cellstyleNote;
            for (int jps = 1; jps < 8; jps++)
            {
                ICell rowJrowNote1Cell = rowNote1.CreateCell(jps);
                rowJrowNote1Cell.SetCellValue("");
                rowJrowNote1Cell.CellStyle = styleSame;
            }
            rowNote1.Height = 30 * 20;

            IRow rowNote2 = sheet.CreateRow(rowNumber++);
            ICell rowNote2Cell1 = rowNote2.CreateCell(0);
            rowNote2Cell1.SetCellValue("");
            rowNote2Cell1.CellStyle = cellstyleNote;
            for (int jps1 = 1; jps1 < 8; jps1++)
            {
                ICell rowJrowNote2Cell = rowNote2.CreateCell(jps1);
                rowJrowNote2Cell.SetCellValue("");
                rowJrowNote2Cell.CellStyle = styleSame;
            }
            rowNote2.Height = 30 * 20;
            #endregion 备注说明

            //需要 合并单元格 或合并行
            //CellRangeAddress 四个参数为：起始行 结束行 起始列 结束列
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 7)); //合并第一行 从0单元格合并到第8个单元格
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 1, 7));//合并第3行 从1单元格合并到第8个单元格
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ammountNumber, ammountNumber, 1, 2));//合并合计行的 第2个单元格和第3个单元格
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ammountNumber, ammountNumber, 3, 5));//合并合计行的 第4个单元格和第6个单元格
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(applyRow, applyRow, 1, 2));//合并请款人行 合并第2单元格和第3单元格
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(applyRow, applyRow, 4, 5));//合并请款人行 合并第5单元格 和第6单元格
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(descriptionTitleNum, descriptionTitleNum, 0, 7));

            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(backRow, backRow, 0, 7));//合并空白行 从单元格 0 到8 个单元格
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowNoteNum, rowNoteNum + 2, 0, 7));//合并 备注说明行  合并备注3行 即0-7列

            //设置列的高度 
            sheet.SetColumnWidth(0, 18 * 256);//第0列的列宽为18
            sheet.SetColumnWidth(1, 16 * 256);
            sheet.SetColumnWidth(2, 22 * 256);
            sheet.SetColumnWidth(3, 18 * 256);
            sheet.SetColumnWidth(4, 22 * 256);
            sheet.SetColumnWidth(5, 30 * 256);
            sheet.SetColumnWidth(6, 22 * 256);
            sheet.SetColumnWidth(7, 16* 256);
            #endregion 导出excel 样式设置


            using (MemoryStream ms = new MemoryStream())
            {

                workbook.Write(ms);
                string fileName = "申请付款单.xlsx";
                #region 附加信息

                //文档摘要信息
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "xx公司申请付款单实例";
                workbook.DocumentSummaryInformation = dsi;
                //
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author = "申请付款单实例"; //填加xls文件作者信息
                si.ApplicationName = "NPOI程序"; //填加xls文件创建程序信息
                si.LastAuthor = "申请付款单实例"; //填加xls文件最后保存者信息
                si.Comments = "申请付款单实例所有"; //填加xls文件作者信息
                si.Title = "申请付款单实例"; //填加xls文件标题信息
                si.Subject = "申请付款单实例";//填加文件主题信息
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;

                #endregion

                string date = DateTime.Now.ToShortDateString();

                //下载报表
                var res = HttpContext.Current.Response;
                res.Clear();
                res.Buffer = true;
                res.Charset = "GBK";
                res.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + date + ".xls");
                res.ContentEncoding = System.Text.Encoding.GetEncoding("GBK");
                res.ContentType = "application/ms-excel;charset=GBK";
                workbook.Write(res.OutputStream);

                res.Flush();
                res.End();
            }
        }

        /// <summary>
        /// 数字金额转换为中文金额
        /// </summary>
        /// <param name="money"></param>
        /// <returns></returns>
        private  string DaXie(string money)
        {
            //将小写金额转换成大写金额
            double MyNumber = Convert.ToDouble(money);
            String[] MyScale = { "分", "角", "元", "拾", "佰", "仟", "万", "拾", "佰", "仟", "亿", "拾", "佰", "仟", "兆", "拾", "佰", "仟" };
            String[] MyBase = { "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖" };
            String M = "";
            bool isPoint = false;
            if (money.IndexOf(".") != -1)
            {
                money = money.Remove(money.IndexOf("."), 1);
                isPoint = true;
            }
            for (int i = money.Length; i > 0; i--)
            {
                int MyData = Convert.ToInt16(money[money.Length - i].ToString());//?
                M += MyBase[MyData];//?
                if (isPoint == true)
                {
                    M += MyScale[i - 1];//?
                }
                else
                {
                    M += MyScale[i + 1];//?
                }
            }
            return M;
        }
        /// <summary>
        /// 银行卡号增加空格
        /// </summary>
        /// <param name="bankCardNumber"></param>
        /// <returns></returns>
        private string getBankCardNumber(string bankCardNumber)
        {
            char []st = bankCardNumber.ToCharArray();
            string result = "";
            for(int i = 0; i < st.Length; i++)
            {
                if (i % 4 == 0)
                {
                    result += " " + st[i];
                }
                else
                {
                    result += st[i];
                }
            }
            return result;
        }