C#导出excel表格自动合并单元格

m0_37842403 2017-04-13 04:43:59
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;
}
...全文
1276 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
黑娃 2017-06-05
  • 打赏
  • 举报
回复
将第一行第一列和第一行第二列合并成一个 ws.Range[ws.Cells[1, 1], ws.Cells[1, 2]].Merge(); 你只能这样去合并你想合并的单元格们
m0_37842403 2017-04-13
  • 打赏
  • 举报
回复

16,550

社区成员

发帖
与我相关
我的任务
社区描述
VB技术相关讨论,主要为经典vb,即VB6.0
社区管理员
  • VB.NET
  • 水哥阿乐
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧