我这边的思路是这样的:
1.将DataSet转换成二维数组,然后粘贴到Excel中
2.对生成的Excel数据进行操作【现在需要对其中重复数据进行合并】现在遇到一个问题就是:就是合并成功之后,我很难再获取正确的坐标,导致值为空,程序报错,有谁能提供更好的循环合并Excel的方法代码如下:
//阻止Excel合并单元格消息弹出
sheet.Application.DisplayAlerts = false; int rows = data.Length / cols, MergeRows = 1, EndNext = data.Length / cols + 1;
string beginValue = null, NextValue = null;
//////判断已经合并单元格
bool mergeTrue = false;
//将数据粘贴到Excel表格中
sheet.get_Range(sheet.Cells[rowIndex, colIndex], sheet.Cells[rowIndex + rows - 1, colIndex + cols - 1]).Value2 = data; //循环合并Excel单元格
for (int i = 2; i <= rows+1; i++)
{
if (mergeTrue)
{
i--;
mergeTrue = false;
}
for (int j = 1; j <= cols; j++)
{
beginValue = sheet.get_Range(sheet.Cells[i, 6], sheet.Cells[i, 6]).Value2.ToString();
NextValue = sheet.get_Range(sheet.Cells[i + MergeRows, 6], sheet.Cells[i + MergeRows, 6]).Value2.ToString();
if (beginValue == NextValue)
{
if (sheet.get_Range(sheet.Cells[i, j], sheet.Cells[i, j]).Value2.ToString() == sheet.get_Range(sheet.Cells[i + MergeRows, j], sheet.Cells[i + MergeRows, j]).Value2.ToString())
{
sheet.get_Range(sheet.Cells[i, j], sheet.Cells[i + MergeRows, j]).MergeCells = true;
sheet.get_Range(sheet.Cells[i, j], sheet.Cells[i + MergeRows, j]).set_Value(Type.Missing, sheet.get_Range(sheet.Cells[i, j], sheet.Cells[i, j]).Value2.ToString());
mergeTrue = true;
}
}
else
{
i = i + MergeRows;//开始行数判断
rows = rows + MergeRows;//行数值随i进行变化
}
}
if (mergeTrue)
{
rows--;
}
1.将DataSet转换成二维数组,然后粘贴到Excel中
2.对生成的Excel数据进行操作【现在需要对其中重复数据进行合并】现在遇到一个问题就是:就是合并成功之后,我很难再获取正确的坐标,导致值为空,程序报错,有谁能提供更好的循环合并Excel的方法代码如下:
//阻止Excel合并单元格消息弹出
sheet.Application.DisplayAlerts = false; int rows = data.Length / cols, MergeRows = 1, EndNext = data.Length / cols + 1;
string beginValue = null, NextValue = null;
//////判断已经合并单元格
bool mergeTrue = false;
//将数据粘贴到Excel表格中
sheet.get_Range(sheet.Cells[rowIndex, colIndex], sheet.Cells[rowIndex + rows - 1, colIndex + cols - 1]).Value2 = data; //循环合并Excel单元格
for (int i = 2; i <= rows+1; i++)
{
if (mergeTrue)
{
i--;
mergeTrue = false;
}
for (int j = 1; j <= cols; j++)
{
beginValue = sheet.get_Range(sheet.Cells[i, 6], sheet.Cells[i, 6]).Value2.ToString();
NextValue = sheet.get_Range(sheet.Cells[i + MergeRows, 6], sheet.Cells[i + MergeRows, 6]).Value2.ToString();
if (beginValue == NextValue)
{
if (sheet.get_Range(sheet.Cells[i, j], sheet.Cells[i, j]).Value2.ToString() == sheet.get_Range(sheet.Cells[i + MergeRows, j], sheet.Cells[i + MergeRows, j]).Value2.ToString())
{
sheet.get_Range(sheet.Cells[i, j], sheet.Cells[i + MergeRows, j]).MergeCells = true;
sheet.get_Range(sheet.Cells[i, j], sheet.Cells[i + MergeRows, j]).set_Value(Type.Missing, sheet.get_Range(sheet.Cells[i, j], sheet.Cells[i, j]).Value2.ToString());
mergeTrue = true;
}
}
else
{
i = i + MergeRows;//开始行数判断
rows = rows + MergeRows;//行数值随i进行变化
}
}
if (mergeTrue)
{
rows--;
}
然后alt+F11 就可以看到你录制的代码了。虽然是VBA。但能看出逻辑。
IWorksheet worksheet = workbook.Worksheets[0];
//Creates a DataTable object.
DataTable dt = new DataTable();
//添加列该DataTable对象中。
dt.Columns.Add("Weekday");
dt.Columns.Add("Aniseed Syrup", typeof(int));
dt.Columns.Add("Carnarvon Tigers", typeof(int));
dt.Columns.Add("Tofu", typeof(int)); //添加行该DataTable对象中。
dt.Rows.Add(new object[] { "Mon", 13, 38, 18 });
dt.Rows.Add(new object[] { "Tue", 12, 36, 17 });
dt.Rows.Add(new object[] { "Wed", 14, 39, 16 });
dt.Rows.Add(new object[] { "Thu", 12, 37, 17 });
dt.Rows.Add(new object[] { "Fri", 13, 36, 18 });
worksheet.ImportDataTable(dt, true, "A1");//插入datatable
worksheet.Cells.CreateRange("A1:D1").Merge();//合并A1:D1区域 workbook.SaveAs(@"c:\book1.xls", FileFormat.Excel97To2003);原文: http://www.cnblogs.com/happyfish78/