C#导出excel表格自动合并单元格
public void GetContractList_Excel()
{
try
{
string proj_id = Request["projid"].ToString();
string user_id = Fyy.DecryptDES(Request.Cookies["EMPSCOOK"]["user_id"]).ToString();
string com_id = Fyy.DecryptDES(Request.Cookies["EMPSCOOK"]["com_id"]).ToString();
string cont_zbType = Request["cont_zbType"];
if (Fyy.IsUniq(Fyy.DecryptDES(Request.Cookies["EMPSCOOK"]["user_id"]).ToString()))
{
List<TM_ContractInfo> list = TM_ContractInfo.GetContractInfoList(user_id, com_id, proj_id, cont_zbType);
var ep = new ExcelPackage();
ExcelWorkbook wb = ep.Workbook;
ExcelWorksheet ws = wb.Worksheets.Add("details");
ws.Cells["A1"].Value = "序号";
ws.Column(1).Width = 5;
ws.Cells["A1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["B1"].Value = "项目名称";
ws.Column(2).Width = 20;
ws.Cells["B1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["C1"].Value = "里程(km)";
ws.Column(3).Width = 15;
ws.Cells["C1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["D1"].Value = "标段名称";
ws.Column(4).Width = 20;
ws.Cells["D1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["E1"].Value = "标段划分";
ws.Column(5).Width = 20;
ws.Cells["E1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["F1"].Value = "中标单位";
ws.Column(6).Width = 20;
ws.Cells["F1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["G1"].Value = "最高现价(万元)";
ws.Column(7).Width = 15;
ws.Cells["G1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["H1"].Value = "合同价(万元)";
ws.Column(8).Width = 15;
ws.Cells["H1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["I1"].Value = "平均(万元/km)";
ws.Column(9).Width = 15;
ws.Cells["I1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["J1"].Value = "资质";
ws.Column(10).Width = 20;
ws.Cells["J1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["K1"].Value = "评标方法";
ws.Column(11).Width = 20;
ws.Cells["K1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["L1"].Value = "业绩";
ws.Column(12).Width = 20;
ws.Cells["L1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["M1"].Value = "备注";
ws.Column(13).Width = 30;
ws.Cells["M1"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ExcelReset("A1:M1", ws);
var i = 2;
string proj_name;
foreach (var finance in list)
{
ws.Cells["A" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["A" + i].Value = finance.cont_no.ToString();
ws.Cells["A" + i].Style.WrapText = true;
proj_name = finance.proj_name.ToString();
ws.Cells["B" + i].Value = finance.proj_name;
ws.Cells["B4:B5"].Merge = true;
ws.Cells["B" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["B" + i].Style.WrapText = true;
ws.Cells["C" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["C" + i].Value = finance.cont_long.ToString();
ws.Cells["C" + i].Style.WrapText = true;
ws.Cells["D" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["D" + i].Value = finance.cont_name;
ws.Cells["D" + i].Style.WrapText = true;
ws.Cells["E" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["E" + i].Value = finance.cont_zbType;
ws.Cells["E" + i].Style.WrapText = true;
ws.Cells["F" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["F" + i].Value = finance.cont_zbCom;
ws.Cells["F" + i].Style.WrapText = true;
ws.Cells["G" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["G" + i].Value = finance.price.ToString();
ws.Cells["G" + i].Style.WrapText = true;
ws.Cells["H" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["H" + i].Value = finance.cont_budget.ToString().Substring(0,finance.cont_budget.ToString().LastIndexOf('.')+4);
ws.Cells["H" + i].Style.WrapText = true;
ws.Cells["I" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["I" + i].Value = finance.cont_avrBudget.ToString();
ws.Cells["I" + i].Style.WrapText = true;
ws.Cells["J" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["J" + i].Value = finance.intelligence;
ws.Cells["J" + i].Style.WrapText = true;
ws.Cells["K" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["K" + i].Value = finance.evaluate;
ws.Cells["K" + i].Style.WrapText = true;
ws.Cells["L" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["L" + i].Value = finance.performance;
ws.Cells["L" + i].Style.WrapText = true;
ws.Cells["M" + i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells["M" + i].Value = finance.cont_memo;
ws.Cells["M" + i].Style.WrapText = true;
ExcelResetValue("A"+i +":M"+i,ws);
i++;
}
//写到客户端(下载)
Response.Clear();
string userAgent = Request.Browser.Browser.ToLower();
var filename = "投资项目进度" + DateTime.Now.ToString("yyyy-MM-dd") + ".xlsx";
if (userAgent != "firefox")
filename = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8);
Response.AddHeader("content-disposition", "attachment; filename=" + filename);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(ep.GetAsByteArray());
Response.Flush();
Response.End();
}
}
catch
{
}
}
private void ExcelReset(string area, ExcelWorksheet ws)
{
// ws.Cells[area].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(0, 0, 128));
// ws.Cells[area].Style.Font.Color.SetColor(System.Drawing.Color.White);
//加粗
//ws.Cells[area].Style.Font.Bold = true;
ws.Cells.Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[area].Style.Font.Size = 10;
ws.Cells.Style.Font.Size = 8;
ws.Cells.AutoFilter = true;
ws.Cells[area].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[area].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Row(1).Height = 20;
}
private void ExcelResetValue(string area, ExcelWorksheet ws)
{
ws.Cells[area].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[area].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}