//导出Excel
public string PostExportToExcel(int supplierCostID, int serverCostID, string supplierName)
{
IBLL.ICOST_CostTableBLL c_BLL = new BLL.COST_CostTableBLL();
List <Cost_CostTableDetails> supplierData = c_BLL.GetCostFeeDetailList(supplierCostID); //供应商
List <Cost_CostTableDetails> serverData = c_BLL.GetCostFeeDetailList(serverCostID); //客服
List <Cost_CostTableDetails> allData = supplierData.Union(serverData).ToList(); //全部数据
List <ErrorColumn> errorList = new List <ErrorColumn>();
foreach (var item in allData.Where(e => e.Flag != "1")) //循环供应商上传的费用
{
var otherModel = allData.Find(e => (e.Employee_ID == item.Employee_ID && e.PaymentStyle == item.PaymentStyle && e.CityName == item.CityName && e.CreateFrom != item.CreateFrom)); //对应的数据
if (otherModel != null)
{
string empID = otherModel.Employee_ID.ToString();
otherModel.Flag = "1";//标识为已循环
Type t1 = typeof(Cost_CostTableDetails);
PropertyInfo[] propertys1 = t1.GetProperties();
//比较费用
foreach (PropertyInfo pi in propertys1)
{
string name = pi.Name;
if (name != "CreateFrom" && name != "COST_CostTable_ID" && name != "Flag")
{
object serverValue = t1.GetProperty(name).GetValue(otherModel, null) == null ? "" : t1.GetProperty(name).GetValue(otherModel, null);
object supplierValue = t1.GetProperty(name).GetValue(item, null) == null ? "" : t1.GetProperty(name).GetValue(item, null);
if (!serverValue.Equals(supplierValue))
{
ErrorColumn errorModel = new ErrorColumn();
errorModel.ID = empID;
errorModel.Column = name;
errorList.Add(errorModel);
}
}
}
}
else//只存在一方的数据来源
{
Type t1 = typeof(Cost_CostTableDetails);
PropertyInfo[] propertys1 = t1.GetProperties();
foreach (PropertyInfo pi in propertys1)
{
string name = pi.Name;
ErrorColumn errorModel = new ErrorColumn();
errorModel.ID = item.Employee_ID.ToString();
errorModel.Column = name;
errorList.Add(errorModel);
}
}
}
string urlPath = string.Empty;
if (errorList.Count != 0) //费用对比有差异
{
urlPath = ExportToExcel(allData, supplierName, errorList);
}
return(urlPath);
}
public IHttpActionResult GetCompany(int id = 0, int billid = 0) //id为公司ID billid 为费用单ID
{
if (!IsValidation())
{
return(Json(new { code = -1, message = "验证未通过" }));
}
try
{
using (SysEntities db = new SysEntities())
{
if (billid == 0)
{
string res = "{\"Id\":null,\"Remark\":null,\"BillDate\":null,\"Employees\":[{\"IDNumber\":\"123456789012345678\",\"Name\":\"刘腾飞\",\"Style\":\"正常\",\"Remark\":[{\"Name\":\"有问题说明的原因\"}],\"PayType\":\"补缴\",\"PensionCity\":\"杭州\",\"PensionSection\":\"2015-7~2015-8\",\"PensionMonth\":2,\"PensionCompanyWage\":\"5565\",\"PensionPersonWage\":\"3565\",\"PensionCompanyPercentage\":25,\"PensionPersonPercentage\":3,\"MedicalCity\":\"杭州\",\"MedicalSection\":\"2015-7~2015-8\",\"MedicalMonth\":2,\"MedicalCompanyWage\":\"5565\",\"MedicalPersonWage\":\"3565\",\"MedicalCompanyPercentage\":25,\"MedicalPersonPercentage\":3,\"WorkInjuryCity\":\"杭州\",\"WorkInjurySection\":\"2015-7~2015-8\",\"WorkInjuryMonth\":2,\"WorkInjuryCompanyWage\":\"5565\",\"WorkInjuryPersonWage\":\"3565\",\"WorkInjuryCompanyPercentage\":25,\"WorkInjuryPersonPercentage\":3,\"UnemploymentCity\":\"杭州\",\"UnemploymentSection\":\"2015-7~2015-8\",\"UnemploymentMonth\":2,\"UnemploymentCompanyWage\":\"5565\",\"UnemploymentPersonWage\":\"3565\",\"UnemploymentCompanyPercentage\":25,\"UnemploymentPersonPercentage\":3,\"MaternityCity\":\"杭州\",\"MaternitySection\":\"2015-7~2015-8\",\"MaternityMonth\":2,\"MaternityCompanyWage\":\"5565\",\"MaternityPersonWage\":\"3565\",\"MaternityCompanyPercentage\":25,\"MaternityPersonPercentage\":3,\"HousingFundCity\":\"杭州\",\"HousingFundSection\":\"2015-7~2015-8\",\"HousingFundMonth\":2,\"HousingFundCompanyWage\":\"5565\",\"HousingFundPersonWage\":\"3565\",\"HousingFundCompanyPercentage\":25,\"HousingFundPersonPercentage\":3,\"LateFees\":\"3565\",\"DisabledInsuranceFee\":\"3565\",\"HeatingFees\":\"3565\",\"MaterialFees\":\"3565\",\"ServiceFees\":\"3565\"}]}";
return(Json(new { code = 0, message = res }));
}
else
{
IBLL.ICOST_CostTableBLL m_BLL = new BLL.COST_CostTableBLL();
int total = 0;
List <CostFeeModel> queryData2 = m_BLL.GetCostFeeList(null, 1, int.MaxValue, null, null, 0, 0, ref total);
var res = (from a in queryData2
where a.ID == billid
select new
{
Id = a.ID,
Remark = a.Status,
BillDate = a.YearMonth,
Employees = (from s in m_BLL.GetCostFeeDetailList(a.ID)
select new
{
IDNumber = s.CertificateNumber,
Name = s.EmployName,
Style = "",
Remark2 = "",
PayType = ((Common.EmployeeMiddle_PaymentStyle)s.PaymentStyle).ToString(),
PensionCity = s.CityName,
PensionSection = s.YanglaoPaymentInterval,
PensionMonth = s.YanglaoPaymentMonth,
PensionCompanyWage = s.YanglaoCompanyCost,
PensionPersonWage = s.YanglaoPersonCost,
PensionCompanyPercentage = s.YanglaoCompanyRadix,
PensionPersonPercentage = s.YanglaoPersonRatio,
MedicalCity = s.CityName,
MedicalSection = s.YiliaoPaymentInterval,
MedicalMonth = s.YiliaoPaymentMonth,
MedicalCompanyWage = s.YiliaoCompanyCost,
MedicalPersonWage = s.YiliaoPersonCost,
MedicalCompanyPercentage = s.YiliaoCompanyRadix,
MedicalPersonPercentage = s.YiliaoPersonRatio,
WorkInjuryCity = s.CityName,
WorkInjurySection = s.GongshangPaymentInterval,
WorkInjuryMonth = s.GongshangPaymentMonth,
WorkInjuryCompanyWage = s.GongshangCompanyCost,
WorkInjuryPersonWage = s.GongshangPersonCost,
WorkInjuryCompanyPercentage = s.GongshangCompanyRadix,
WorkInjuryPersonPercentage = s.GongshangPersonRatio,
UnemploymentCity = s.CityName,
UnemploymentSection = s.ShiyePaymentInterval,
UnemploymentMonth = s.ShiyePaymentMonth,
UnemploymentCompanyWage = s.ShiyeCompanyCost,
UnemploymentPersonWage = s.ShiyePersonCost,
UnemploymentCompanyPercentage = s.ShiyeCompanyRadix,
UnemploymentPersonPercentage = s.ShiyePersonRatio,
MaternityCity = s.CityName,
MaternitySection = s.ShengyuPaymentInterval,
MaternityMonth = s.ShengyuPaymentMonth,
MaternityCompanyWage = s.ShengyuCompanyCost,
MaternityPersonWage = s.ShengyuPersonCost,
MaternityCompanyPercentage = s.ShengyuCompanyRadix,
MaternityPersonPercentage = s.ShengyuPersonRatio,
HousingFundCity = s.CityName,
HousingFundSection = s.GongjijinPaymentInterval,
HousingFundMonth = s.GongjijinPaymentMonth,
HousingFundCompanyWage = s.GongjijinCompanyCost,
HousingFundPersonWage = s.GongjijinPersonCost,
HousingFundCompanyPercentage = s.GongjijinCompanyRadix,
HousingFundPersonPercentage = s.GongjijinPersonRatio,
OtherCost = s.OtherCost,
OtherInsuranceCost = s.OtherInsuranceCost,
MaterialFees = s.ProductionCost,
ServiceFees = s.ServiceCost,
}).ToList()
}).ToList();
return(Json(new { code = 0, message = res }));
}
};
}
catch (Exception ee)
{
return(Json(new { code = -1, message = ee.ToString() }));
}
}
//导出费用对比Excel表
private string ExportToExcel(List <Cost_CostTableDetails> queryData, string supplierName, List <ErrorColumn> errorList)
{
using (MemoryStream ms = new MemoryStream())
{
queryData = queryData.OrderBy(o => o.EmployName).ThenBy(o => o.CreateFrom).ToList();
IBLL.ICOST_CostTableBLL c_BLL = new BLL.COST_CostTableBLL();
FileStream file = new FileStream(System.Web.HttpContext.Current.Server.MapPath("../../Template/Excel/费用对比导出模板.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
hssfworkbook.SetSheetName(0, "费用明细");
ISheet sheet1 = hssfworkbook.GetSheetAt(0);
InsertRowsdaili2(sheet1, 7, queryData.Count() - 1);
//上色
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thin; //边框
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.Alignment = HorizontalAlignment.Center; //水平居中
IFont cellFont = hssfworkbook.CreateFont();
cellFont.Color = (short)FontColor.Red;
cellStyle.SetFont(cellFont);
for (int i = 0; i < queryData.Count(); i++)
{
List <ErrorColumn> singleEmployee = new List <ErrorColumn>();
string employeeID = queryData[i].Employee_ID.ToString();
singleEmployee = errorList.Where(o => o.ID == employeeID).ToList();
IRow row = sheet1.GetRow(7 + i);
row.GetCell(0).SetCellValue(((Common.CostTable_CreateFrom)queryData[i].CreateFrom).ToString());
row.GetCell(1).SetCellValue(queryData[i].EmployName);
row.GetCell(2).SetCellValue(queryData[i].CertificateNumber); // 身份证号
row.GetCell(3).SetCellValue(queryData[i].SupplierName); // 供应商
string style = ((Common.EmployeeMiddle_PaymentStyle)queryData[i].PaymentStyle).ToString();
row.GetCell(4).SetCellValue(style); // 缴费类型
row.GetCell(5).SetCellValue(queryData[i].CityName); // 缴纳地
#region 社保信息
// 养老
row.GetCell(6).SetCellValue(queryData[i].YanglaoPaymentInterval);
row.GetCell(7).SetCellValue(queryData[i].YanglaoPaymentMonth == null ? 0 : (int)queryData[i].YanglaoPaymentMonth);
row.GetCell(8).SetCellValue(queryData[i].YanglaoCompanyRadix == null ? 0 : (double)queryData[i].YanglaoCompanyRadix);
row.GetCell(9).SetCellValue((double)queryData[i].YanglaoCompanyCost);
row.GetCell(10).SetCellValue((double)queryData[i].YanglaoPersonCost);
// 失业
row.GetCell(12).SetCellValue(queryData[i].ShiyePaymentInterval);
row.GetCell(13).SetCellValue(queryData[i].ShiyePaymentMonth == null ? 0 : (int)queryData[i].ShiyePaymentMonth);
row.GetCell(14).SetCellValue(queryData[i].ShiyeCompanyRadix == null ? 0 : (double)queryData[i].ShiyeCompanyRadix);
row.GetCell(15).SetCellValue((double)queryData[i].ShiyeCompanyCost);
row.GetCell(16).SetCellValue((double)queryData[i].ShiyePersonCost);
// 工伤
row.GetCell(18).SetCellValue(queryData[i].GongshangPaymentInterval);
row.GetCell(19).SetCellValue(queryData[i].GongshangCompanyRadix == null ? 0 : (double)queryData[i].GongshangCompanyRadix);
row.GetCell(20).SetCellValue((double)queryData[i].GongshangCompanyCost);
// 医疗
row.GetCell(21).SetCellValue(queryData[i].YiliaoPaymentInterval);
row.GetCell(22).SetCellValue(queryData[i].YiliaoPaymentMonth == null ? 0 : (int)queryData[i].YiliaoPaymentMonth);
row.GetCell(23).SetCellValue(queryData[i].YiliaoCompanyRadix == null ? 0 : (double)queryData[i].YiliaoCompanyRadix);
row.GetCell(24).SetCellValue((double)queryData[i].YiliaoCompanyCost);
row.GetCell(25).SetCellValue((double)queryData[i].YiliaoPersonCost);
// 大病
row.GetCell(27).SetCellValue((double)queryData[i].DaeCompanyCost);
row.GetCell(28).SetCellValue((double)queryData[i].DaePersonCost);
// 生育
row.GetCell(29).SetCellValue((double)queryData[i].ShengyuCompanyCost);
// 公积金
row.GetCell(30).SetCellValue(queryData[i].GongjijinPaymentInterval);
row.GetCell(31).SetCellValue(queryData[i].GongjijinPaymentMonth == null ? 0 : (int)queryData[i].GongjijinPaymentMonth);
row.GetCell(32).SetCellValue(queryData[i].GongjijinCompanyRadix == null ? 0 : (double)queryData[i].GongjijinCompanyRadix);
row.GetCell(33).SetCellValue((double)queryData[i].GongjijinCompanyCost);
row.GetCell(34).SetCellValue((double)queryData[i].GongjijinPersonCost);
// 补充公积金
row.GetCell(36).SetCellValue((double)queryData[i].GongjijinBCCompanyCost);
row.GetCell(37).SetCellValue((double)queryData[i].GongjijinBCPersonCost);
row.GetCell(38).SetCellValue((double)queryData[i].OtherInsuranceCost); // 其他社保费用
row.GetCell(39).SetCellValue((double)queryData[i].OtherCost); // 其他费用
row.GetCell(43).SetCellValue((double)queryData[i].ProductionCost); // 工本费
row.GetCell(44).SetCellValue((double)queryData[i].ServiceCost); // 服务费
#endregion
// 循环给不一致数据上色标红
List <ExcelVSList> excelList = GetExcelVSList();
for (int k = 0; k < excelList.Count; k++)
{
if (singleEmployee.Select(o => o.Column).ToArray().Contains(excelList[k].Column))
{
row.GetCell(excelList[k].Cell).CellStyle = cellStyle;
}
}
}
IRow LastRow = sheet1.CreateRow(9 + queryData.Count());
LastRow.CreateCell(0);
LastRow.GetCell(0).SetCellValue("");
sheet1.ForceFormulaRecalculation = true;
string fileName = supplierName + "_" + "费用对比差异详情" + ".xls";
string urlPath = "/DataExport/" + fileName; // 文件下载的URL地址，供给前台下载
string filePath = System.Web.HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径
file = new FileStream(filePath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
return(urlPath); // 导出成功
}
}