// 引用
using Excel = Microsoft.Office.Interop.Excel;
// 创建新的Excel文件
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Add();
// 删除原有分页,只保留1个分页:每次删除最后那个分页
var sheetsCount = xlWorkBook.Sheets.Count;
for (var i = 0; i < sheetsCount - 1; i++)
{
var sheetsCountNew = xlWorkBook.Sheets.Count;
Excel.Worksheet sheetDel = (Excel.Worksheet)xlWorkBook.Sheets[sheetsCountNew];
xlApp.DisplayAlerts = false;
sheetDel.Delete();
}
// 第一个分页:获取原有分页,并重命名
var sheet1 = xlWorkBook.Sheets.Count;
Excel.Worksheet ws1 = (Excel.Worksheet)xlWorkBook.Sheets[sheet1];
//Microsoft.Office.Interop.Excel.Worksheet ws1 = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.Add();
ws1.Name = "数据";
// 第二个分页:置于第一个分页后面
Microsoft.Office.Interop.Excel.Worksheet ws2 = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.Add(Missing.Value, ws1, Missing.Value, Missing.Value);
ws2.Name = "柏拉图1";
// 第三个分页:置于第二个分页后面
Microsoft.Office.Interop.Excel.Worksheet ws3 = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.Add(Missing.Value, ws2, Missing.Value, Missing.Value);
ws3.Name = "柏拉图2";
// 保存Excel文件 + 关闭Excel文件
xlWorkBook.SaveAs(exlName);
xlWorkBook.Close();
xlApp.Quit();
richTextBox1.AppendText("\n输出结果路径: " + exlName + "\n");
案例如上.
解释如下:
1. 获取Excel表格分页数量
var sheetsCount = xlWorkBook.Sheets.Count;
2. 已知Sheet总数量,每次循环删除新的最后一个分页(sheetsCountNew)
PS:不能全部删除,要至少保留一个Sheet
for (var i = 0; i < sheetsCount - 1; i++)
{
var sheetsCountNew = xlWorkBook.Sheets.Count;
Excel.Worksheet sheetDel = (Excel.Worksheet)xlWorkBook.Sheets[sheetsCountNew];
xlApp.DisplayAlerts = false;
sheetDel.Delete();
}
3. 这时只剩1个Sheet,获取该Sheet,并重命名
var sheet1 = xlWorkBook.Sheets.Count;
Excel.Worksheet ws1 = (Excel.Worksheet)xlWorkBook.Sheets[sheet1];
ws1.Name = "数据";
4. 新建Sheet:无输入参数,表示新建Sheet置于第一个分页前面
Microsoft.Office.Interop.Excel.Worksheet ws1 = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.Add();
ws1.Name = "数据";
5. 在指定位置插入新Sheet
Microsoft.Office.Interop.Excel.Worksheet ws2 = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.Add(参数1, 参数2, 参数3, 参数4);
参数1:before,指定Sheet对象,新建的工作表将置于此Sheet前面.
参数2:after,指定Sheet对象,新建的工作表将置于此Sheet后面.
参数3:count,要新建的Sheet的数目。默认值为 1.
参数4:type,指定Sheet类型.
例子:新建sheet置于最后一个分页之后
// 获取Sheet数量
var sheets = xlWorkBook.Sheets.Count;
// 获取最后一个Sheet
Excel.Worksheet ws1 = (Excel.Worksheet)xlWorkBook.Sheets[sheets];
// 设定参数2为最后一个Sheet对象,表示将新建sheet置于最后一个sheet后面
Microsoft.Office.Interop.Excel.Worksheet ws2 = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.Add(Missing.Value, ws1 , Missing.Value, Missing.Value);
ws2.Name = "数据";
6. 获取sheet对象(已知sheet名称)
// 第1个分页:已知sheet名称("Sheet1")
Excel.Worksheet ws1 = xlWorkBook.Worksheets["Sheet1"];
ws1.Name = "数据";
// 第2个分页:已知sheet名称("Sheet2")
Excel.Worksheet ws2 = xlWorkBook.Worksheets["Sheet2"];
ws2.Name = "柏拉图1";
// 第3个分页:已知sheet名称("Sheet3")
Excel.Worksheet ws3 = xlWorkBook.Worksheets["Sheet3"];
ws3.Name = "柏拉图2";