npoi导出excel合并单元格

需要引用NPOI.dll程序集和Ionic.Zip.dll程序集

string[] headerRowName = { "序号", "地市", "镇街", "企业名称", "监控类型", "企业联网负责人", "企业联网负责人手机号",
"环保督办人", "环保督办人联系电话", "所属重点行业", "是否原国控企业",
"监测类型", "产污工艺名称", "排放口安装视频监控数", "安装工控监控数",
"排放口名称", "监控点位名称", "是否安装设备", "是否暂缓安装", "暂缓备注",
"安装期限", "是否与环保部门联网传输", "联网期限", "联网传输方式", "监控污染物", "执行标准"};

IWorkbook book = RenderToExcel1(dt2, headerRowName);
ISheet sheet = book.GetSheet("Sheet1");

ICellStyle style = book.CreateCellStyle();
//style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;

Merge1(sheet, style);

MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Flush();
ms.Position = 0;
RenderToBrowser(ms, HttpContext.Current, FileName);

 

//第一个方法

/// <summary>
/// 将数据写入工作簿中
/// </summary>
/// <param name="table">数据</param>
/// <param name="headerRowName">对应table数剧中的列名称的数组</param>

public static IWorkbook RenderToExcel1(DataTable table, string[] headerRowName)
{
//MemoryStream ms = new MemoryStream();

using (table)
{
IWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
ISheet sheet = workbook.CreateSheet("Sheet1");//创建工作表
//创建第一行
IRow headerRow = sheet.CreateRow(0);

//设置第一行的名称
for (int i = 0; i < headerRowName.Length; i++)
{
string value = headerRowName[i];
headerRow.CreateCell(i).SetCellValue(value);//If Caption not set, returns the ColumnName value
}
// handling value.
int rowIndex = 1;

string sourceName1 = table.Rows[0]["SourceName"].ToString();

foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
//添加序号
//dataRow.CreateCell(0).SetCellValue(rowIndex);
//添加table中的数据
foreach (DataColumn column in table.Columns)
{
int column_Ordinal = column.Ordinal;
string value = row[column].ToString();
dataRow.CreateCell(column_Ordinal).SetCellValue(value);
}
rowIndex++;
}

//列宽自适应，只对英文和数字有效
for (int i = 0; i <= table.Rows.Count; i++)
{
sheet.AutoSizeColumn(i);
}

//workbook.Write(ms);
//ms.Flush();
//ms.Position = 0;
return workbook;
}
//return ms;
}

//第二个方法(合并单元格)

public void Merge1(ISheet sheet, ICellStyle style)
{
int num = 1;
string FirstSource = null;
int dept = 0;
for (int rowID = 0; rowID <= sheet.LastRowNum + 1; rowID++)
{
IRow row = null;
string nowSource = null;
if (rowID <= sheet.LastRowNum)
{
row = sheet.GetRow(rowID);

//获取用来判断是否合并的列的值
nowSource = row.GetCell(3).ToString();
}

//如果当前那个用来判断是否合并的列的值与上一列的值相同就不合并,继续看下一行

if (nowSource != FirstSource)
{
if (FirstSource != null)
{
//序号
//if (rowID - dept != 0)
// sheet.GetRow(rowID - dept).GetCell(0).SetCellValue(num++);

//如果深度超过1，进行合并企业信息列
if (dept > 1)
{
for (int gi = 0; gi < 15; gi++)
{
IRow iRow = sheet.GetRow(rowID - dept);
ICell iCell = iRow.GetCell(gi);
iCell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(rowID - dept, rowID - 1, gi, gi));
}

//合并企业信息后，检索排放口列,排放口信息从13列开始
string FirstCheck = null;
int cDept = 1;
for (int cRowID = rowID - dept; cRowID <= rowID; cRowID++)
{
IRow crow = null;
string nowCheck = null;
if (cRowID != rowID)
{
crow = sheet.GetRow(cRowID);
nowCheck = crow.GetCell(16).ToString();
}

if (nowCheck != FirstCheck)
{
if (FirstCheck != null && cDept > 1)
{
//合并排放口
for (int gi = 16; gi <= 23; gi++)
{
IRow iRow = sheet.GetRow(cRowID - cDept);
ICell iCell = iRow.GetCell(gi);
iCell.CellStyle = style;
//sheet.GetRow(cRowID - cDept).GetCell(gi).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(cRowID - cDept, cRowID - 1, gi, gi));
}
}

FirstCheck = nowCheck;
cDept = 1;
}
else
{
cDept++;
}
}
}
}

FirstSource = nowSource;
dept = 1;
}
else
{
dept++;
}
}
}

 

//第三个方法

static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
{
if (context.Request.Browser.Browser == "IE")
fileName = HttpUtility.UrlEncode(fileName);
context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
context.Response.BinaryWrite(ms.ToArray());
}