//宋吉峰使用EXCEL变量定义开始 public Microsoft.Office.Interop.Excel.Application excelApp = null; public Microsoft.Office.Interop.Excel.Workbook book = null; public Microsoft.Office.Interop.Excel.Application app = null; public Microsoft.Office.Interop.Excel.Workbook workBook = null; public Microsoft.Office.Interop.Excel.Worksheet sheet = null; public Microsoft.Office.Interop.Excel.Worksheet workSheet = null; public Microsoft.Office.Interop.Excel.Range range = null; public String Input_StrArr = ""; //宋吉峰使用EXCEL变量定义结束
public ExcelHelper() { }
/// <summary> /// 创建一个Excel对象 /// </summary> public void Create() { App = new Application() { Visible = false }; Wbs = App.Workbooks; Wb = Wbs.Add(Missing); }
/// <summary> /// //打开一个Excel文件 /// </summary> /// <param name="fileName"></param> public void Open(string fileName) { App = new Application(); Wbs = App.Workbooks; Wb = Wbs.Add(fileName); FileName = fileName; } public void CopyWorkSheet(int srcSheetIndex, int aimSheetIndex, string newSheetName,string filename) { app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; //若为true，删除瞬间可以看见 office excel界面 //打开excel文件
workBook = app.Workbooks.Add(filename);
//得到WorkSheet对象 workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
if (srcSheetIndex > workBook.Sheets.Count || aimSheetIndex > workBook.Sheets.Count) {
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！"); }
try { Microsoft.Office.Interop.Excel.Worksheet srcSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(srcSheetIndex); Microsoft.Office.Interop.Excel.Worksheet aimSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(aimSheetIndex);
srcSheet.Copy(Missing, aimSheet);
//重命名 workSheet = (Microsoft.Office.Interop.Excel.Worksheet)aimSheet.Next; //获取新拷贝的工作表 workSheet.Name = newSheetName; string str = ""; string s = System.DateTime.Now.Date.Year.ToString() + "-" + System.DateTime.Now.Date.Month.ToString() + "-" + System.DateTime.Now.Date.Day.ToString() + "-" + System.DateTime.Now.Date.Hour.ToString() + "-" + System.DateTime.Now.Date.Minute.ToString() + "-" + System.DateTime.Now.Second.ToString() + "-" + System.DateTime.Now.Millisecond.ToString(); s = s + ".xls"; str = filename.ToString().ToLower().Replace(".xls", s); //保存编辑 // SaveAs(); workBook.SaveAs(str, XlFileFormat.xlWorkbookNormal, Missing, Missing, Missing, Missing, XlSaveAsAccessMode.xlNoChange, Missing, Missing, Missing, Missing, Missing); //关闭book workBook.Close(Missing, Missing, Missing); //退出excel application，可以将前面的excelApp.Visible = false改为excelApp.Visible = true看看; app.Workbooks.Close(); app.Quit();
} catch (Exception e) {
throw e; } } public void CopyWorkSheets(int srcSheetIndex, int aimSheetIndex, string newSheetName, string filename) { app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; //若为true，删除瞬间可以看见 office excel界面 //打开excel文件
workBook = app.Workbooks.Add(filename);
//得到WorkSheet对象 workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
if (srcSheetIndex > workBook.Sheets.Count || aimSheetIndex > workBook.Sheets.Count) {
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！"); }
for (int j = 1; j < workSheet.UsedRange.Rows.Count;j++ ) { for (int i = 1; i < workSheet.UsedRange.Columns.Count; i++) { Microsoft.Office.Interop.Excel.Range range = workSheet.get_Range(workSheet.Cells[j, i], workSheet.Cells[j, i]);
if (range.Text.ToString() == "$$") { ((Range) workSheet.Cells[j, i]).Value2 = (i.ToString() + "==" + j.ToString());
} } }
string str = ""; string s = System.DateTime.Now.Date.Year.ToString() + "-" + System.DateTime.Now.Date.Month.ToString() + "-" + System.DateTime.Now.Date.Day.ToString() + "-" + System.DateTime.Now.Date.Hour.ToString() + "-" + System.DateTime.Now.Date.Minute.ToString() + "-" + System.DateTime.Now.Second.ToString() + "-" + System.DateTime.Now.Millisecond.ToString(); s = s + ".xls"; str = filename.ToString().ToLower().Replace(".xls", s); //保存编辑 // SaveAs(); workBook.SaveAs(str, XlFileFormat.xlWorkbookNormal, Missing, Missing, Missing, Missing, XlSaveAsAccessMode.xlNoChange, Missing, Missing, Missing, Missing, Missing); //关闭book workBook.Close(Missing, Missing, Missing); //退出excel application，可以将前面的excelApp.Visible = false改为excelApp.Visible = true看看; app.Workbooks.Close(); app.Quit();
}