public ActionResult DownloadProjectStatistics(string json)
{
var result = ProjectBusiness.ListProjectStatistics(json);
List<ProjectStatisticsModel> psList = new List<ProjectStatisticsModel>();
if (result.StatusCode == System.Net.HttpStatusCode.OK)
{
var pagModel = JsonConvert.DeserializeObject<PaginationModel>(result.Data.ToString());
psList = JsonConvert.DeserializeObject<List<ProjectStatisticsModel>>(pagModel.Results.ToString());
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
HSSFRow headerTitle = sheet.CreateRow(0) as HSSFRow;
HSSFRow headerHeader = sheet.CreateRow(1) as HSSFRow;
headerTitle.Height = 30 * 20;
headerHeader.Height = 16 * 20;
ICellStyle headStyle = GetHeadStyle(workbook);
ICellStyle headerStyle = GetHeaderStyle(workbook);
ICellStyle contentStyle = GetContentStyle(workbook);
//标题
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15));
headerTitle.CreateCell(0).SetCellValue("账目记录");
headerTitle.GetCell(0).CellStyle = headStyle;
#region list表头
var headerList = new List<string>() { "项目名称", "开发商", "供应商", "质保期(月)","合同总金额(¥)", "采购总金额(¥)", "已出库金额(¥)", "项目批次", "合同应收总金额(¥)", "采购应付总金额(¥)", "已开票金额(¥)", "已回款金额(¥)", "已到账金额(¥)", "已回票金额(¥)", "待结算金额(¥)", "已结算金额(¥)" };
var i = 0;
headerList.ForEach(item => {
sheet.AddMergedRegion(new CellRangeAddress(1, 1, i, i));
headerHeader.CreateCell(i).SetCellValue(item);
headerHeader.GetCell(i).CellStyle = headerStyle;
i++;
});
#endregion
#region 内容数据
if (psList != null && psList.Count() > 0)
{
int rowIndex = 2;
psList.ForEach(item =>
{
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
var row = 0;
if (item.BatchList != null && item.BatchList.Count > 0)
{
if (item.BatchList.Count == 1)
row = item.BatchList[0].StageList.Count - 1;
else
row = item.BatchList.Count - 1;
}
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + row, 0, 0));
dataRow.CreateCell(0).SetCellValue(item.Name);
dataRow.GetCell(0).CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + row, 1, 1));
dataRow.CreateCell(1).SetCellValue(item.DeveloperName);
dataRow.GetCell(1).CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + row, 2, 2));
dataRow.CreateCell(2).SetCellValue(item.SupplierNameStr);
dataRow.GetCell(2).CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + row, 3, 3));
dataRow.CreateCell(3).SetCellValue(item.Warranty.HasValue?Convert.ToInt32(item.Warranty.Value) :0);
dataRow.GetCell(3).CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + row, 4, 4));
dataRow.CreateCell(4).SetCellValue(item.ContractAmount.HasValue ? item.ContractAmount.Value.ToString() : "0");
dataRow.GetCell(4).CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + row, 5, 5));
dataRow.CreateCell(5).SetCellValue(item.PurchaseAmount.HasValue ? item.PurchaseAmount.Value.ToString() : "0");
dataRow.GetCell(5).CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + row, 6, 6));
dataRow.CreateCell(6).SetCellValue(item.DeliveryAmount.HasValue ? item.DeliveryAmount.Value.ToString() : "0");
dataRow.GetCell(6).CellStyle = headerStyle;
//"项目批次", "合同应收总金额(¥)", "采购应付总金额(¥)", "已开票金额(¥)", "已回款金额(¥)", "已到账金额(¥)", "已回票金额(¥)", "待结算金额(¥)", "已结算金额(¥)"
//无批次,无阶段
if (item.IsBatch.HasValue && !item.IsBatch.Value && item.IsStage.HasValue && !item.IsStage.Value)
{
dataRow.CreateCell(7).SetCellValue("-");
dataRow.GetCell(7).CellStyle = headerStyle;
dataRow.CreateCell(8).SetCellValue(item.ContractRceivableAmount.HasValue? item.ContractRceivableAmount.Value.ToString(): "0");
dataRow.GetCell(8).CellStyle = headerStyle;
dataRow.CreateCell(9).SetCellValue(item.PurchasePaymentAmount.HasValue ? item.PurchasePaymentAmount.Value.ToString() : "0");
dataRow.GetCell(9).CellStyle = headerStyle;
dataRow.CreateCell(10).SetCellValue(item.BillingAmount.HasValue ? item.BillingAmount.Value.ToString() : "0");
dataRow.GetCell(10).CellStyle = headerStyle;
dataRow.CreateCell(11).SetCellValue(item.ReceiveAmount.HasValue ? item.ReceiveAmount.Value.ToString() : "0");
dataRow.GetCell(11).CellStyle = headerStyle;
dataRow.CreateCell(12).SetCellValue(item.ArrivalAmount.HasValue ? item.ArrivalAmount.Value.ToString() : "0");
dataRow.GetCell(12).CellStyle = headerStyle;
dataRow.CreateCell(13).SetCellValue(item.BillAmount.HasValue ? item.BillAmount.Value.ToString() : "0");
dataRow.GetCell(13).CellStyle = headerStyle;
dataRow.CreateCell(14).SetCellValue(item.WaitSettlementAmount.HasValue ? item.WaitSettlementAmount.Value.ToString() : "0");
dataRow.GetCell(14).CellStyle = headerStyle;
dataRow.CreateCell(15).SetCellValue(item.SettlementAmount.HasValue ? item.SettlementAmount.Value.ToString() : "0");
dataRow.GetCell(15).CellStyle = headerStyle;
}
else {
if (item.BatchList != null && item.BatchList.Count > 0)
{
if (item.BatchList.Count == 1)
{
var j = rowIndex;
item.BatchList[0].StageList.ForEach(b =>
{
if (j != rowIndex)
dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
dataRow.CreateCell(7).SetCellValue("-");
dataRow.GetCell(7).CellStyle = contentStyle;
dataRow.CreateCell(8).SetCellValue(b.ContractAmount.HasValue ? b.ContractAmount.Value.ToString() : "0");
dataRow.GetCell(8).CellStyle = contentStyle;
dataRow.CreateCell(9).SetCellValue(b.PurchaseAmount.HasValue ? b.PurchaseAmount.Value.ToString() : "0");
dataRow.GetCell(9).CellStyle = contentStyle;
dataRow.CreateCell(10).SetCellValue(b.BillingAmount.HasValue ? b.BillingAmount.Value.ToString() : "0");
dataRow.GetCell(10).CellStyle = contentStyle;
dataRow.CreateCell(11).SetCellValue(b.ReceiveAmount.HasValue ? b.ReceiveAmount.Value.ToString() : "0");
dataRow.GetCell(11).CellStyle = contentStyle;
dataRow.CreateCell(12).SetCellValue(b.ArrivalAmount.HasValue ? b.ArrivalAmount.Value.ToString() : "0");
dataRow.GetCell(12).CellStyle = contentStyle;
dataRow.CreateCell(13).SetCellValue(b.BillAmount.HasValue ? b.BillAmount.Value.ToString() : "0");
dataRow.GetCell(13).CellStyle = contentStyle;
dataRow.CreateCell(14).SetCellValue(b.WaitSettlementAmount.HasValue ? b.WaitSettlementAmount.Value.ToString() : "0");
dataRow.GetCell(14).CellStyle = contentStyle;
dataRow.CreateCell(15).SetCellValue(b.SettlementAmount.HasValue ? b.SettlementAmount.Value.ToString() : "0");
dataRow.GetCell(15).CellStyle = contentStyle;
rowIndex++;
});
}
else
{
var j = rowIndex;
item.BatchList.ForEach(b =>
{
if (j != rowIndex)
dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
dataRow.CreateCell(7).SetCellValue(b.PaymentBatchName);
dataRow.GetCell(7).CellStyle = contentStyle;
dataRow.CreateCell(8).SetCellValue(b.ContractAmount.HasValue ? b.ContractAmount.Value.ToString() : "0");
dataRow.GetCell(8).CellStyle = contentStyle;
dataRow.CreateCell(9).SetCellValue(b.PurchaseAmount.HasValue ? b.PurchaseAmount.Value.ToString() : "0");
dataRow.GetCell(9).CellStyle = contentStyle;
dataRow.CreateCell(10).SetCellValue(b.BillingAmount.HasValue ? b.BillingAmount.Value.ToString() : "0");
dataRow.GetCell(10).CellStyle = contentStyle;
dataRow.CreateCell(11).SetCellValue(b.ReceiveAmount.HasValue ? b.ReceiveAmount.Value.ToString() : "0");
dataRow.GetCell(11).CellStyle = contentStyle;
dataRow.CreateCell(12).SetCellValue(b.ArrivalAmount.HasValue ? b.ArrivalAmount.Value.ToString() : "0");
dataRow.GetCell(12).CellStyle = contentStyle;
dataRow.CreateCell(13).SetCellValue(b.BillAmount.HasValue ? b.BillAmount.Value.ToString() : "0");
dataRow.GetCell(13).CellStyle = contentStyle;
dataRow.CreateCell(14).SetCellValue(b.WaitSettlementAmount.HasValue ? b.WaitSettlementAmount.Value.ToString() : "0");
dataRow.GetCell(14).CellStyle = contentStyle;
dataRow.CreateCell(15).SetCellValue(b.SettlementAmount.HasValue ? b.SettlementAmount.Value.ToString() : "0");
dataRow.GetCell(15).CellStyle = contentStyle;
rowIndex++;
});
}
}
else
{
rowIndex++;
}
}
});
}
#endregion
var fileName = "账目记录.xls";
FileStream filecreate = new FileStream(GetUrl(fileName), FileMode.Create, FileAccess.ReadWrite);
workbook.Write(filecreate);
filecreate.Close();
FileInfo file = new FileInfo(GetUrl(fileName));
var fs = file.OpenRead();
return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
}
public string GetUrl(string fileName)
{
string url = @"C:\Project\EP\Excel";
if (!Directory.Exists(url))
{
Directory.CreateDirectory(url);
}
return url + fileName;
}