[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();//新建行
ICell cell = row.CreateCell();//新建列
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 = ;//字体大小
stylecell0.SetFont(fontcell0);
cell.CellStyle = stylecell0; ICell cell1t = row.CreateCell();
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 =;//字体大小
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 = ;//字体大小
style.SetFont(font); for (int p1 = ; p1 < ; p1++)
{
ICell rowJrowCell1 = row.CreateCell(p1);
rowJrowCell1.SetCellValue("");
rowJrowCell1.CellStyle = style;
}
row.Height = * ; //统一样式
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 = ;
styleSame.SetFont(fontSame);
//统一样式 #region 第一行数据
//第一行 第一单元格
IRow row1 = sheet.CreateRow();
ICell cell1 = row1.CreateCell();
cell1.SetCellValue("日期：");
cell1.CellStyle = styleSame;
//
ICell cell2 = row1.CreateCell();
cell2.SetCellValue(settlementDetailModels.createdtime.ToShortDateString());//创建时间
cell2.CellStyle = styleSame;
//
ICell cell3 = row1.CreateCell();
cell3.SetCellValue("请款部门：");
cell3.CellStyle = styleSame;
//
ICell cell4 = row1.CreateCell();
cell4.SetCellValue(settlementDetailModels.facname);
cell4.CellStyle = styleSame;
//
ICell cell5 = row1.CreateCell();
cell5.SetCellValue("申请付款日期：");
cell5.CellStyle = styleSame;
//
ICell cell6 = row1.CreateCell();
cell6.SetCellValue(settlementDetailModels.applytime.ToString());
cell6.CellStyle = styleSame;
//
ICell cell7 = row1.CreateCell();
cell7.SetCellValue("结算总金额：");
cell7.CellStyle = styleSame;
//
ICell cell8 = row1.CreateCell();
cell8.SetCellValue("￥" + settlementDetailModels.totalamount);
cell8.CellStyle = styleSame;
row1.Height = * ;
#endregion 第一行数据 #region 第二行数据
IRow row2 = sheet.CreateRow();
ICell row2Cell1 = row2.CreateCell();
row2Cell1.SetCellValue("摘要：");
row2Cell1.CellStyle = styleSame;
for (int p= ; p < ; 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();
row2Cell2.SetCellValue(settlementDetailModels.note);
row2Cell2.CellStyle = cellstyleNote1;
row2.Height = * ;
#endregion 第二行数据 #region 第三行 数据title
IRow row3 = sheet.CreateRow();
ICell row3Cell1 = row3.CreateCell();
row3Cell1.SetCellValue("费用项目");
row3Cell1.CellStyle = styleSame;
ICell row3Cell2 = row3.CreateCell();
row3Cell2.SetCellValue("金额");
row3Cell2.CellStyle = styleSame;
ICell row3Cell3 = row3.CreateCell();
row3Cell3.SetCellValue("大写金额");
row3Cell3.CellStyle = styleSame;
ICell row3Cell4 = row3.CreateCell();
row3Cell4.SetCellValue("支付方式");
row3Cell4.CellStyle = styleSame;
ICell row3Cell5 = row3.CreateCell();
row3Cell5.SetCellValue("收款方名称");
row3Cell5.CellStyle = styleSame;
ICell row3Cell6 = row3.CreateCell();
row3Cell6.SetCellValue("账号");
row3Cell6.CellStyle = styleSame;
ICell row3Cell7 = row3.CreateCell();
row3Cell7.SetCellValue("开户行");
row3Cell7.CellStyle = styleSame;
ICell row3Cell8 = row3.CreateCell();
row3Cell8.SetCellValue("付款主体");
row3Cell8.CellStyle = styleSame;
row3.Height = * ;
#endregion 第三行 数据title int rowNumber = ;
#region 内容行
foreach (var r in settlementDetailModels.supplys)
{
IRow rowN = sheet.CreateRow(rowNumber); ICell rowNCell1 = rowN.CreateCell();
rowNCell1.SetCellValue(r.project);
rowNCell1.CellStyle = styleSame; ICell rowNCell2 = rowN.CreateCell();
rowNCell2.SetCellValue(r.totalamount.ToString());
rowNCell2.CellStyle = styleSame; ICell rowNCell3 = rowN.CreateCell();
rowNCell3.SetCellValue(DaXie(r.totalamount.ToString()));
rowNCell3.CellStyle = styleSame; ICell rowNCell4 = rowN.CreateCell();
rowNCell4.SetCellValue(r.paytypename);
rowNCell4.CellStyle = styleSame; ICell rowNCell5 = rowN.CreateCell();
rowNCell5.SetCellValue(r.collectusername);
rowNCell5.CellStyle = styleSame; ICell rowNCell6 = rowN.CreateCell();
rowNCell6.SetCellValue(getBankCardNumber(r.collectbankcard));
rowNCell6.CellStyle = styleSame; ICell rowNCell7 = rowN.CreateCell();
rowNCell7.SetCellValue(r.collectbank);
rowNCell7.CellStyle = styleSame; ICell rowNCell8 = rowN.CreateCell();
rowNCell8.SetCellValue(settlementDetailModels.facname);
rowNCell8.CellStyle = styleSame;
rowN.Height = * ; rowNumber++; }
#endregion 内容行 int numberSupply = settlementDetailModels.supplys.Count();
if (numberSupply < )
{
for (int i = ; i < - numberSupply; i++)
{
IRow rowI = sheet.CreateRow(rowNumber);
for(int j = ; j < ; j++)
{ ICell rowJCell1 = rowI.CreateCell(j);
rowJCell1.SetCellValue("");
rowJCell1.CellStyle = styleSame;
}
rowI.Height = * ;
rowNumber++;
}
}
int ammountNumber = rowNumber;
#region 合计行
IRow rowAmount = sheet.CreateRow(rowNumber++);
ICell rowAmountCell1 = rowAmount.CreateCell();
rowAmountCell1.SetCellValue("合计");
rowAmountCell1.CellStyle = style; ICell rowAmountCell2 = rowAmount.CreateCell();
rowAmountCell2.SetCellValue("￥" + settlementDetailModels.totalamount.ToString());
rowAmountCell2.CellStyle = style; ICell rowAmountCell3 = rowAmount.CreateCell(); ICell rowAmountCell4 = rowAmount.CreateCell();
rowAmountCell4.SetCellValue(DaXie(settlementDetailModels.totalamount.ToString()));
rowAmountCell4.CellStyle = style;
for (int j4 = ; j4 < ; j4++)
{ ICell rowJ4Cell1 = rowAmount.CreateCell(j4);
rowJ4Cell1.SetCellValue("");
rowJ4Cell1.CellStyle = styleSame;
}
rowAmount.Height = * ;
#endregion 合计行 int backRow = rowNumber;
IRow rowBack = sheet.CreateRow(rowNumber++);//空白行
for (int j = ; j < ; j++)
{ ICell rowJrowBackCell1 = rowBack.CreateCell(j);
rowJrowBackCell1.SetCellValue("");
rowJrowBackCell1.CellStyle = styleSame;
}
rowBack.Height = * ;
#region 请款人 相关信息
int applyRow = rowNumber;
IRow rowApply = sheet.CreateRow(rowNumber++);
ICell rowApplyCell1 = rowApply.CreateCell();
rowApplyCell1.SetCellValue("请款人：");
rowApplyCell1.CellStyle = styleSame; ICell rowApplyCell2 = rowApply.CreateCell();
rowApplyCell2.SetCellValue("");
rowApplyCell2.CellStyle = styleSame;
ICell rowApplyCell3 = rowApply.CreateCell();
rowApplyCell3.SetCellValue("");
rowApplyCell3.CellStyle = styleSame;
ICell rowApplyCell4 = rowApply.CreateCell();
rowApplyCell4.SetCellValue("部门领导审核：");
rowApplyCell4.CellStyle = styleSame;
ICell rowApplyCell5 = rowApply.CreateCell();
rowApplyCell5.SetCellValue("");
rowApplyCell5.CellStyle = styleSame;
ICell rowApplyCell6 = rowApply.CreateCell();
rowApplyCell6.SetCellValue("");
rowApplyCell6.CellStyle = styleSame;
ICell rowApplyCell7 = rowApply.CreateCell();
rowApplyCell7.SetCellValue("财务审核：");
rowApplyCell7.CellStyle = styleSame;
ICell rowApplyCell8 = rowApply.CreateCell();
rowApplyCell8.SetCellValue("");
rowApplyCell8.CellStyle = styleSame;
rowApply.Height = * ;
#endregion 请款人相关信息 int descriptionTitleNum = rowNumber;
IRow rowDescriptTitle = sheet.CreateRow(rowNumber++);
ICell rowDescriptTitleCell1 = rowDescriptTitle.CreateCell();
rowDescriptTitleCell1.SetCellValue("附件（请附发票，收据等）");
rowDescriptTitleCell1.CellStyle = styleSame;
rowDescriptTitle.Height = * ;
for (int jp =; jp < ; 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();
rowNoteCell.SetCellValue(" 1.需要向公司申请对外支付时候填写此表；\n 2.用途说明填写：费用支出的实际用途，填写大类即可（如：配件及精品、办公用品、人力资源、装修等）；\n 3.务必填写好支付方式：转账（首选）、支票和对方的收款账号及银行信息。");
rowNoteCell.CellStyle = cellstyleNote;
for (int jpy = ; jpy < ; jpy++)
{
ICell rowJrowNoteCell = rowNote.CreateCell(jpy);
rowJrowNoteCell.SetCellValue("");
rowJrowNoteCell.CellStyle = styleSame;
}
rowNote.Height = * ; IRow rowNote1 = sheet.CreateRow(rowNumber++);
ICell rowNote1Cell1 = rowNote1.CreateCell();
rowNote1Cell1.SetCellValue("");
rowNote1Cell1.CellStyle = cellstyleNote;
for (int jps = ; jps < ; jps++)
{
ICell rowJrowNote1Cell = rowNote1.CreateCell(jps);
rowJrowNote1Cell.SetCellValue("");
rowJrowNote1Cell.CellStyle = styleSame;
}
rowNote1.Height = * ; IRow rowNote2 = sheet.CreateRow(rowNumber++);
ICell rowNote2Cell1 = rowNote2.CreateCell();
rowNote2Cell1.SetCellValue("");
rowNote2Cell1.CellStyle = cellstyleNote;
for (int jps1 = ; jps1 < ; jps1++)
{
ICell rowJrowNote2Cell = rowNote2.CreateCell(jps1);
rowJrowNote2Cell.SetCellValue("");
rowJrowNote2Cell.CellStyle = styleSame;
}
rowNote2.Height = * ;
#endregion 备注说明 //需要 合并单元格 或合并行
//CellRangeAddress 四个参数为：起始行 结束行 起始列 结束列
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , )); //合并第一行 从0单元格合并到第8个单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));//合并第3行 从1单元格合并到第8个单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ammountNumber, ammountNumber, , ));//合并合计行的 第2个单元格和第3个单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ammountNumber, ammountNumber, , ));//合并合计行的 第4个单元格和第6个单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(applyRow, applyRow, , ));//合并请款人行 合并第2单元格和第3单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(applyRow, applyRow, , ));//合并请款人行 合并第5单元格 和第6单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(descriptionTitleNum, descriptionTitleNum, , )); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(backRow, backRow, , ));//合并空白行 从单元格 0 到8 个单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowNoteNum, rowNoteNum + , , ));//合并 备注说明行 合并备注3行 即0-7列 //设置列的高度
sheet.SetColumnWidth(, * );//第0列的列宽为18
sheet.SetColumnWidth(, * );
sheet.SetColumnWidth(, * );
sheet.SetColumnWidth(, * );
sheet.SetColumnWidth(, * );
sheet.SetColumnWidth(, * );
sheet.SetColumnWidth(, * );
sheet.SetColumnWidth(, * );
#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(".") != -)
{
money = money.Remove(money.IndexOf("."), );
isPoint = true;
}
for (int i = money.Length; i > ; i--)
{
int MyData = Convert.ToInt16(money[money.Length - i].ToString());//?
M += MyBase[MyData];//?
if (isPoint == true)
{
M += MyScale[i - ];//?
}
else
{
M += MyScale[i + ];//?
}
}
return M;
}
/// <summary>
/// 银行卡号增加空格
/// </summary>
/// <param name="bankCardNumber"></param>
/// <returns></returns>
private string getBankCardNumber(string bankCardNumber)
{
char []st = bankCardNumber.ToCharArray();
string result = "";
for(int i = ; i < st.Length; i++)
{
if (i % == )
{
result += " " + st[i];
}
else
{
result += st[i];
}
}
return result;
}