/// <summary>
/// 导出凭证清单
/// </summary>
/// <param name="dataList"></param>
/// <returns></returns>
private static Stream ExportExpenseReimbursementByData(List <ApiExpenseReimbursementEntity> list)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
// 设置字体
IFont headfont = workbook.CreateFont();
headfont.FontName = "微软雅黑";
// 建表头
IRow row = sheet.CreateRow(0);
row.CreateCell(0).CellStyle.SetFont(headfont);
row.CreateCell(0).SetCellValue("公司代码");
row.CreateCell(1).SetCellValue("报销单编号");
row.CreateCell(2).SetCellValue("凭证日期");
row.CreateCell(3).SetCellValue("过账日期");
row.CreateCell(4).SetCellValue("凭证抬头文本");
row.CreateCell(5).SetCellValue("页数");
row.CreateCell(6).SetCellValue("项目类型");
row.CreateCell(7).SetCellValue("WBS编号");
row.CreateCell(8).SetCellValue("科目");
row.CreateCell(9).SetCellValue("金额");
row.CreateCell(10).SetCellValue("成本中心");
row.CreateCell(11).SetCellValue("行项目文本");
row.CreateCell(12).SetCellValue("内部订单编号");
for (var i = 0; i < list.Count; i++)
{
ApiExpenseReimbursementEntity model = list[i];
IRow rowItem = sheet.CreateRow(i + 1);
rowItem.CreateCell(0).SetCellValue(model.BUKRS);
rowItem.CreateCell(1).SetCellValue(model.XBLNR);
rowItem.CreateCell(2).SetCellValue(model.BLDAT);
rowItem.CreateCell(3).SetCellValue(model.BUDAT);
rowItem.CreateCell(4).SetCellValue(model.BKTXT);
rowItem.CreateCell(5).SetCellValue(model.NUMPG.ToString());
rowItem.CreateCell(6).SetCellValue(model.PROTYP);
rowItem.CreateCell(7).SetCellValue(model.POSID);
rowItem.CreateCell(8).SetCellValue(model.HKONT);
rowItem.CreateCell(9).SetCellValue(model.DMBTR.ToString());
rowItem.CreateCell(10).SetCellValue(model.KOSTL);
rowItem.CreateCell(11).SetCellValue(model.SGTXT);
rowItem.CreateCell(12).SetCellValue(model.AUFNR);
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
/// <summary>
/// 导出凭证清单
/// </summary>
/// <param name="searchValue"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="status"></param>
/// <returns></returns>
public FileResult ExportTripReimbursementByCondition(string searchValue, DateTime?startTime, DateTime?endTime, string status)
{
List <ApiExpenseReimbursementEntity> list = new List <ApiExpenseReimbursementEntity>();
int total = 0;
var dataList = GetSearchTripReimbursementList(inn, Userinfo.Roles, out total, null, startTime, endTime, searchValue, status, Userinfo.UserName);
if (dataList != null)
{
foreach (var item in dataList)
{
if (string.IsNullOrEmpty(item.status))
{
item.status = "End";
}
if (item.status == "Expense Accountant Creation" || item.status == "End")
{
List <ApiExpenseReimbursementEntity> datas = TripReimbursementBll.SendTripReimbursementCreation(inn, item.id);
if (datas != null)
{
List <ApiExpenseReimbursementEntity> newApiEntity = new List <ApiExpenseReimbursementEntity>();
foreach (var data in datas)
{
if (data.b_TaxRate > 0)
{
int rateInt = int.Parse((data.b_TaxRate.Value * double.Parse("100")).ToString());
TaxCodeTypeList?obj = EnumDescription.GetEnumByValue <TaxCodeTypeList>(rateInt);
string textDescription = EnumDescription.GetFieldText(obj);
//根据描述获取对应的科目
string subject = TaxCodeConfigureBll.GeTaxCodeConfigureByText(textDescription);
data.DMBTR = data.b_TaxFreeAmount;
ApiExpenseReimbursementEntity entity = new ApiExpenseReimbursementEntity();
entity.BUKRS = data.BUKRS;
entity.XBLNR = data.XBLNR;
entity.BLDAT = data.BLDAT;
entity.BUDAT = data.BUDAT;
entity.BKTXT = data.BKTXT;
entity.NUMPG = data.NUMPG;
entity.PROTYP = data.PROTYP;
entity.POSID = data.POSID;
entity.HKONT = subject;
entity.DMBTR = data.b_Tax;
entity.KOSTL = "";
entity.SGTXT = data.SGTXT;
entity.AUFNR = data.AUFNR;
newApiEntity.Add(data);
newApiEntity.Add(entity);
}
else
{
newApiEntity.Add(data);
}
}
//计算合计金额
ApiExpenseReimbursementEntity totalEntity = new ApiExpenseReimbursementEntity();
totalEntity.BUKRS = newApiEntity.First().BUKRS;
totalEntity.XBLNR = newApiEntity.First().XBLNR;
totalEntity.BLDAT = newApiEntity.First().BLDAT;
totalEntity.BUDAT = newApiEntity.First().BUDAT;
totalEntity.BKTXT = newApiEntity.First().b_StaffNo;
totalEntity.HKONT = "2241999999";
totalEntity.DMBTR = newApiEntity.Select(x => x.DMBTR).Sum();
newApiEntity.Add(totalEntity);
list.AddRange(newApiEntity);
}
}
}
}
Stream ms = ExportTripReimbursementByData(list);
return(File(ms, "application/vnd.ms-excel", "凭证清单" + ".xls"));
}