本文介绍了为了解决大量表格汇总问题而开发的一款C#软件。该软件可以自动汇总结构相同的Excel表格，保留原格式，简化了辅导员等用户的工作。文章提供了操作类的简要介绍，并分享了代码实现。
事实上，这是一个很有需求的一个小软件，例如辅导员老师需要整理的表格一般都是按班级分类好的，通常都需要对这些表格进行汇总，数量一多，手动显然就很麻烦，于是在老师的建议下，写了这个小软件，也当作自己练手的一个小项目。
软件截图：
待汇总表格：
汇总结果：
操作类介绍：C#操作Excel总结
（感谢原博主的总结）
由于只考虑相同结构的表格汇总，故我们不需要针对每一列进行判断，可以直接采用去掉表头后使用copy方法，这种方法对比复制数据后粘贴的好处是：能保留原表格的格式（包括行宽列宽，加粗等等）
代码：
string P_str_Name = "";//存储遍历到的Excel文件名
List<string> P_list_SheetNames = new List<string>();//实例化泛型集合对象，用来存储工作表名称
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(missing);
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
excel.Visible = false;
excel.DisplayAlerts = false;
progressBar1.Value++;
for (int i = 0; i < listBox1.Items.Count; i++)//遍历所有选择的Excel文件名
{
P_str_Name = listBox1.Items[i].ToString();//记录遍历到的Excel文件名
Microsoft.Office.Interop.Excel.Workbook Tempworkbook = excel.Application.Workbooks.Open(P_str_Name, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
P_list_SheetNames = GetSheetName(P_str_Name);//获取Excel文件中的所有工作表名
for (int j = 0; j < P_list_SheetNames.Count; j++)//遍历所有工作表
{// [1] "Sheet1 _FilterDatabase
if (P_list_SheetNames[j].Length>15&&P_list_SheetNames[j].Substring(P_list_SheetNames[j].Length - 15, 15) == "_FilterDatabase")
continue;
//指定要复制的工作表
Microsoft.Office.Interop.Excel.Worksheet TempWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)Tempworkbook.Sheets[P_list_SheetNames[j]];//创建新工作表
if (TempWorksheet.UsedRange.Rows.Count == 1)
continue;
if (newWorksheet.UsedRange.Rows.Count==1)
{
if (TempWorksheet.UsedRange.Rows.Count < Convert.ToInt32(comboBox1.Text))
{
MessageBox.Show("选择表头行数超过实际表格行数", "警告", MessageBoxButtons.OK);
return;
}
TempWorksheet.Copy(missing, newWorksheet);
newWorksheet = workbook.Sheets[2];
}
else
{
for (int ib = 0; ib < Convert.ToInt32(comboBox1.Text);ib++ )
for (int ia = 0; ia < newWorksheet.UsedRange.Columns.Count; ia++)
{
if (newWorksheet.Cells[ib+1, ia + 1].Text != TempWorksheet.Cells[ib+1, ia + 1].Text)
{
MessageBox.Show("表" + P_str_Name + "的第" + (j + 1).ToString() + "张工作表" + P_list_SheetNames[j] + "的表头错误，与第一张表不同，请修改！", "警告", MessageBoxButtons.OK);
return;
}
}
Excel.Range b1 = (Microsoft.Office.Interop.Excel.Range)TempWorksheet.Cells[Convert.ToInt32(comboBox1.Text)+1, 1];
Excel.Range b2 = (Microsoft.Office.Interop.Excel.Range)TempWorksheet.Cells[newWorksheet.UsedRange.Rows.Count, newWorksheet.UsedRange.Columns.Count];
Excel.Range rng1 = (Microsoft.Office.Interop.Excel.Range)TempWorksheet.get_Range(b1, b2);
Excel.Range b3 = (Microsoft.Office.Interop.Excel.Range)newWorksheet.Cells[newWorksheet.UsedRange.Rows.Count+1, 1];
rng1.Copy(b3);
}
}
Tempworkbook.Close(false, missing, missing);//关闭临时工作簿
progressBar1.Value++;
}
workbook.SaveAs(quanju.w_path, missing, null, null, false, false, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
workbook.Close(false, missing, missing);//关闭目标工作簿
MessageBox.Show("已经将所有选择的Excel工作表汇总到了一个Excel工作表中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
CloseProcess("EXCEL");//关闭所有Excel进程
CloseProcess("WPS");可能有不完善的地方，望指正！
对比于上面这一种，我们可能还会遇到不完全匹配的表格汇总，就是可能有些表格表头不完全一致，个人考虑过这一点并实现了，由于过程中会对每一列的列名进行匹配后复制，效率很低下，就淘汰了（被老师批速度太慢不实用，刷掉。。。。）
就简单放下代码吧
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(missing);
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
//string[,] newdata = new string[1000, 100];//用于存放新表
excel.Visible = false;
excel.DisplayAlerts = false;
int new_count = 0;//newdata的行的数量
for (int i = 0; i < listBox1.Items.Count; i++)//遍历所有选择的Excel文件名
{
P_str_Name = P_str_Names[i];//记录遍历到的Excel文件名
//指定要复制的工作簿
Microsoft.Office.Interop.Excel.Workbook Tempworkbook = excel.Application.Workbooks.Open(P_str_Name, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
P_list_SheetNames = GetSheetName(P_str_Name);//获取Excel文件中的所有工作表名
for (int j = 0; j < P_list_SheetNames.Count; j++)//遍历所有工作表
{
//指定要复制的工作表
Microsoft.Office.Interop.Excel.Worksheet TempWorksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)Tempworkbook.Sheets[P_list_SheetNames[j]];//创建新工作表
Microsoft.Office.Interop.Excel.Workbook workbook1 = excel.Application.Workbooks.Add(missing);
Microsoft.Office.Interop.Excel.Worksheet TempWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook1.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
for (int a = 1; a < TempWorksheet1.UsedRange.Rows.Count+1; a++)
{
for (int b = 1; b < TempWorksheet1.UsedRange.Columns.Count+1; b++)
{
TempWorksheet.Cells[a, b] = TempWorksheet1.Cells[a, b];
}
}
if (TempWorksheet.UsedRange.Rows.Count == 1)
continue;
for (int a = 0; a < TempWorksheet.UsedRange.Rows.Count; a++)
{
for (int b = 0; b < TempWorksheet.UsedRange.Columns.Count; b++)
{
if (TempWorksheet.Cells[a + 1, b + 1].Text == "" && a == 0 && b != 0)
{
TempWorksheet.Cells[a + 1, b + 1] = TempWorksheet.Cells[a + 1, b].Text+"(1)";
}
}
}
if (quanju.rr == 1)
{
for (int ib = 0; ib < TempWorksheet.UsedRange.Rows.Count; ib++)
{
for (int ia = 0; ia < TempWorksheet.UsedRange.Columns.Count; ia++)
{
newWorksheet.Cells[ib + 1, ia + 1] = TempWorksheet.Cells[ib+1, ia+1];
}
quanju.rr++;
}
new_count = TempWorksheet.UsedRange.Columns.Count;
quanju.rr--;
break;
}
else
{
for (int ia = 0; ia < TempWorksheet.UsedRange.Columns.Count; ia++)
{
for (int ib = 0; ib < new_count; ib++)
{
int row_count = quanju.rr;
if (TempWorksheet.Cells[1, ia+1].Text == newWorksheet.Cells[1, ib+1].Text)//有相同值则对应赋给该列
{
for (int ic = 1; ic < TempWorksheet.UsedRange.Rows.Count; ic++)
{
//newdata[row_count++, ib] = exceldata[ic, ia].ToString();
newWorksheet.Cells[++row_count, ib + 1] = TempWorksheet.Cells[ic + 1, ia + 1];
}
break;
}
if (ib == new_count - 1)//无相同值，则在newdata中新增一列
{
// newdata[0, new_count++] = exceldata[0, ia].ToString();
newWorksheet.Cells[1, ++new_count] = TempWorksheet.Cells[1, ia + 1];
for (int ic = 1; ic < TempWorksheet.UsedRange.Rows.Count; ic++)
{
// newdata[row_count++, new_count - 1] = exceldata[ic, ia].ToString();
newWorksheet.Cells[++row_count, new_count] = TempWorksheet.Cells[ic + 1, ia + 1];
}
}
}
}
quanju.rr = quanju.rr + TempWorksheet.UsedRange.Rows.Count - 1;
}
}
Tempworkbook.Close(false, missing, missing);//关闭临时工作簿
progressBar1.Value++;
}
Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)newWorksheet.Cells[1, 1];
Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)newWorksheet.Cells[newWorksheet.UsedRange.Rows.Count, newWorksheet.UsedRange.Columns.Count];
Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)newWorksheet.get_Range(c3, c2);
rng.WrapText = true;
//newWorksheet.Cells.Select();
newWorksheet.Cells.Columns.AutoFit();
workbook.SaveAs(quanju.w_path, missing, null, null, false, false, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
workbook.Close(false, missing, missing);//关闭目标工作簿
MessageBox.Show("已经将所有选择的Excel工作表汇总到了一个Excel工作表中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
CloseProcess("EXCEL");//关闭所有Excel进程
CloseProcess("WPS");
扫一扫
726
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
