在日常工作中,我们经常会需要将一个表格的数据拆分为多个表格以满足工作需要。如果只需要拆分成一两个那还好,手动筛选后新建复制就好了,但是一旦需要拆分的表格多达3个以上的时候,这样简单重复拆分表格的工作就会显得特别费力费时,这时候我们就需要一键操作!
本文正是基于此需求,写了一个可以一键将原始表格根据列内容拆分成多个表格的小工具!输入基于该列拆分的列号,选择需要拆分的文件,即可拆分将原始表格拆分为多个表格!拆分后的表格名称正是以列的内容来命名的!标题有合并单元格也可以实现!下面是实现代码:
int c = Convert.ToInt32(textBox2.Text);
Microsoft.Office.Interop.Excel.Application excel = new ApplicationClass();
excel.ScreenUpdating = false; //停止工作表刷新
excel.DisplayAlerts = false;
Workbook workbook2 = excel.Workbooks.Open(filename[0]);
Worksheet sheet2 = (Worksheet)workbook2.ActiveSheet;
Dictionary<String, Range> dic = new Dictionary<String, Range>();
Range rng = sheet2.UsedRange;
int lColumn = rng.Columns.Count;//获得最大列数
int lRow = rng.Rows.Count;//获得最大行数
int b = Convert.ToInt32(TileLine);
b = b + 1;
for (int i = b; i < lRow+1; i++)
{
string h = sheet2.Range[sheet2.Cells[i, c], sheet2.Cells[i,c]].Text.ToString();
if (dic.ContainsKey(h))
{
dic[h] = excel.Union(dic[h], sheet2.Range[sheet2.Cells[i, 1], sheet2.Cells[i, lColumn]]);
}
else
{
dic.Add(h, sheet2.Range[sheet2.Cells[i, 1], sheet2.Cells[i, lColumn]]);
}
}
for (int i = 0; i < dic.Count; i++)
{
Workbook workbooknew = excel.Workbooks.Add(Type.Missing);
Worksheet sheetnew = (Worksheet)workbooknew.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Range range;
range = (Range)sheetnew.Cells[sheetnew.Range["A65536", "A65536"].End[XlDirection.xlUp].Row, 1];
sheet2.Range[sheet2.Cells[1, 1], sheet2.Cells[b, lColumn]].Copy(range);
range = (Range)sheetnew.Cells[sheetnew.Range["A65536", "A65536"].End[XlDirection.xlUp].Row, 1];
Dictionary<String, Range>.KeyCollection key = dic.Keys;
dic[key.ElementAt(i)].Copy(range);
workbooknew.SaveAs(workbook2.Path + "\\" + key.ElementAt(i) + ".xls");
workbooknew.Close();
}
workbook2.Close();
excel.Quit();
PublicMethod.Kill(excel);//关闭excel进程
MessageBox.Show("success");
实现:
1.初始表格
2.执行。输入表格的行数及拆分的列号
3.拆分后