一个EXCEL文件中包含了N个sheet,我要用代码将其拆分为N个EXCEL,每个EXCEL一个sheet。
大家有什么办法吗?
先谢谢大家了。
string excelpath = @"D:\xxx\xxx.xls"; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); //app.Visible = true; object missing = Type.Missing; Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(excelpath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); try { if (workBook != null) { for (int i = 1; i < workBook.Sheets.Count; i++) { Microsoft.Office.Interop.Excel.Worksheet _wSheets = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; Microsoft.Office.Interop.Excel.Workbook newBook = app.Workbooks.Add(missing); Worksheet mySheet = newBook.Sheets[1] as Worksheet; try { string filename = @"D:\temp\" + _wSheets.Name + ".xls"; if (File.Exists(filename)) { File.Delete(filename); } //mySheet.Name = _wSheets.Name; _wSheets.Copy(mySheet, missing); newBook.SaveAs(filename, missing , missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, missing , missing, missing, missing, missing); } catch (Exception ex) { } finally { _wSheets = null; mySheet = null; newBook.Close(); } } } } catch (Exception ex) { } finally { //newBook.Close(); workBook.Close(); app.Quit(); app = null; }
问题搞定了,领导解决的,代码如上,希望可以帮到后来人。
先分别读出每个Sheet,再独立生成新的Excel文件就可以了。
不能改变里面的样式啊,亲。
用VBA解决的话,5~20分钟。
可以用C#调用VBA吗?怎么弄?
@whwfwfy: 用C#的话就建议使用NPOI之类第三方类库。
用VBA的话,直接打开EXCEL,按下ALT+F11,就可以写代码了,简单易学。
有编程基础的人,不懂VBA,现学现用。
http://www.c-sharpcorner.com/Blogs/5291/
为什么excel都喜欢用微软哦菲斯组件,如果你是服务器上,你也要先装哦菲斯吗