/// <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")); }