一个下载excel合并的方法

[-一个大坑]

单号一样的合并，其中有4列按顺序判断是否合并
厂别一样，终点一样才合并，厂别不一样，终点、栈板、纸箱一样也不合并
栈板合并要看厂别、终点是不是都一样，以此类推
因为加了几个判断，导致很多代码好像有重复的
//excel下載合併
public void ExportExcel(DataTable dt, string thName, int rnoColumn, int sqeColunmn, int[] orderMerge, string path)
{
string[] thNameArr = thName.Split(',');
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow dataRow = sheet.CreateRow(0);
Hashtable tdHT = new Hashtable();
Hashtable mergeHT = new Hashtable();

//设置单元格的样式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; //垂直对齐居中
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;

for (int i = 0; i < dt.Columns.Count; i++)
{
ICell icell = dataRow.CreateCell(i);
icell.SetCellValue(thNameArr[i].ToString()); //填充表头
icell.CellStyle = style; //設置樣式
tdHT.Add(i, 0); //初始化Hashtable。用来记录要合并的Excel的行号
mergeHT.Add(i, 0); //初始化Hashtable。用来记录要合并多少行Excel
}

//填充内容,dt從0行開始，excel0行是標題，從1行開始
for (int i = 0; i < dt.Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 1);
if (i != dt.Rows.Count - 1)
{
if (dt.Rows[i][rnoColumn].ToString() == dt.Rows[i + 1][rnoColumn].ToString() && dt.Rows[i][sqeColunmn].ToString() == dt.Rows[i + 1][sqeColunmn].ToString())
{
for (int j = 0; j < dt.Columns.Count; j++)
{
//判斷是不是在按順序排列的數組
if (orderMerge == null || Array.IndexOf(orderMerge, j) == -1)
{
//給表格賦值和設置格式
ICell icell = dataRow.CreateCell(j);
icell.SetCellValue(dt.Rows[i][j].ToString());
icell.CellStyle = style;

if (dt.Rows[i][j].ToString() == dt.Rows[i + 1][j].ToString())
{
int mergeRow = (int)mergeHT[j];
mergeHT.Remove(j);
mergeHT.Add(j, mergeRow + 1); //內容一樣，合併行+1
}
else
{
//內容不一樣，合併單元格，填充內容和設置樣式
int row = (int)tdHT[j] + 1;
int col = (int)mergeHT[j];
sheet.AddMergedRegion(new CellRangeAddress(row, row + col, j, j));

//更新行號和和合併行
tdHT.Remove(j);
tdHT.Add(j, i);
mergeHT.Remove(j);
mergeHT.Add(j, 0);
}
}
else
{
if (dt.Rows[i][j].ToString() == dt.Rows[i + 1][j].ToString())
{
//給表格賦值和設置格式
ICell icell = dataRow.CreateCell(j);
icell.SetCellValue(dt.Rows[i][j].ToString());
icell.CellStyle = style;

int mergeRow = (int)mergeHT[j];
mergeHT.Remove(j);
mergeHT.Add(j, mergeRow + 1); //內容一樣，合併行+1
}
else
{
//按順序對比，遇到不一樣的，數組內後面的不合併
for (int z=j; z <= (int)orderMerge[orderMerge.Length-1]; z++)
{
//給表格賦值和設置格式
ICell icell = dataRow.CreateCell(z);
icell.SetCellValue(dt.Rows[i][z].ToString());
icell.CellStyle = style;

//內容不一樣，合併單元格，填充內容和設置樣式
int row = (int)tdHT[z] + 1;
int col = (int)mergeHT[z];
sheet.AddMergedRegion(new CellRangeAddress(row, row + col, z, z));

//更新行號和和合併行
tdHT.Remove(z);
tdHT.Add(z, i);
mergeHT.Remove(z);
mergeHT.Add(z, 0);
j = z;
}
}

}
}
}
else
{
//單號不一樣，開始合併，并把td的值寫入到excel
for (int j = 0; j < dt.Columns.Count; j++)
{
//給表格賦值和設置格式
ICell icell = dataRow.CreateCell(j);
icell.SetCellValue(dt.Rows[i][j].ToString());
icell.CellStyle = style;

//合併單元格
int row = (int)tdHT[j] + 1;
int col = (int)mergeHT[j];
sheet.AddMergedRegion(new CellRangeAddress(row, row + col, j, j));
}

//单号不同，清空Hashtable，然后新增Hashtable，值为当前行
tdHT.Clear();
mergeHT.Clear();
for (int z = 0; z < dt.Columns.Count; z++)
{
tdHT.Add(z, i);
mergeHT.Add(z, 0);
}
}
}
else
{
//最後一行，開始合併，并把td的值寫入到excel
for (int j = 0; j < dt.Columns.Count; j++)
{
//給表格賦值和設置格式
ICell icell = dataRow.CreateCell(j);
icell.SetCellValue(dt.Rows[i][j].ToString());
icell.CellStyle = style;

//合併單元格
int row = (int)tdHT[j] + 1;
int col = (int)mergeHT[j];
sheet.AddMergedRegion(new CellRangeAddress(row, row + col, j, j));
}
}
}

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

using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
fs.Dispose();
}

}

[Hello World,]

先把数据写到表里，再进行合并，合并功能指定起始行、列，递归调用即可，参考JS版本的

[Hello World,]

发错链接，更新：JS合并相同单元格

[-一个大坑]

UI可以直接根据GridView下载excel，也是合并的

    //下載excel
    protected void BtnDownload_Click(object sender, EventArgs e)
    {
        this.locUI.ClearMessage();
        string excelName = "申请单報表";
        BindIMPDocHead(); //查詢
        this.gridView.Columns[0].Visible = false;
        this.gridView.Attributes.Add("style", "vnd.ms-excel.numberformat:@");
        this.locUI.GridViewDownloadExcel(this.gridView, excelName);
    }

    //重写VerifyRenderingInServerForm方法，阻止系统调用默认的VerifyRenderingInServerForm方法。不然GridView導出excel會報錯
    public override void VerifyRenderingInServerForm(Control control)
    {
        
    }