数据保存到Excel

//保存到Excel public void SaveDataTableToExcel(System.Data.DataTable excelTable,string filePath) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { //让后台执行设置为不可见 app.Visible = false; //新增加一个工作簿 Workbook wBook = app.Workbooks.Add(true); //如果要打开已有的工作簿，则使用下面的注释语句 // Workbook wBook = app.Workbooks.Open(@"C:/YourPath/YourWorkbook.xls", // missing, missing, missing, missing, missing, missing, missing, // missing, missing, missing, missing, missing,missing, missing); //取得一个工作表 //如果打开了已有的工作簿,也可以这样获取工作表Worksheet wSheet = wBook.ActiveSheet as Worksheet Worksheet wSheet = wBook.Worksheets[1] as Worksheet; if (excelTable.Rows.Count > 0) { int row = 0; row = excelTable.Rows.Count; int col = excelTable.Columns.Count; allsize = row; for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { string str = excelTable.Rows[i][j].ToString(); wSheet.Cells[i + 1, j + 1] = str; } } } int size = excelTable.Columns.Count; for (int i = 0; i < size; i++) { wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName; } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.Save(); //保存excel文件 app.Save(filePath + "//导出excel.xls"); } catch(Exception err) { MessageBox.Show("导入Excel出错！错误原因："+ err.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { //确保Excel进程关闭 app.Quit(); app = null; } }

 

 //向EXCEL一个workbook里多个sheets写入datatable

Excel.Application appExcel = new Excel.Application(); Excel.Workbook workbookData; Excel.Worksheet worksheetData; workbookData = appExcel.Workbooks.Add(Missing.Value); worksheetData = (Excel.Worksheet)workbookData.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);//添加一个sheet worksheetData.Name = "Saved";//sheet命名 你有四个datatable,循环生成四个sheet，分别写入就行了

 

//插入sheet的代码(这里的例子是导入一个dataset)

需要引用的命名空间

using Microsoft.Office.Interop.Excel; using Microsoft.Office.Core;

 

#region EXCEL : ExportToExcel //Exports a DataView to Excel. The following steps are carried out //in order to export the DataView to Excel //Create Excel Objects //Create Column & Row Workbook Cell Rendering Styles //Fill Worksheet With DataView //Add Auto Shapes To Excel Worksheet //Select All Used Cells //Create Headers/Footers //Set Status Finished //Save workbook & Tidy up all objects //@param dv : DataView to use //@param path : The path to save/open the EXCEL file to/from //@param sheetName : The target sheet within the EXCEL file public void ExportToExcel(DataView dv,string path, string sheetName) { try { //Assign Instance Fields this.dv = dv; #region NEW EXCEL DOCUMENT : Create Excel Objects //create new EXCEL application EXL = new Microsoft.Office.Interop.Excel.ApplicationClass(); //index to hold location of the requested sheetName in the workbook sheets //collection int indexOfsheetName; #region FILE EXISTS //Does the file exist for the given path if (File.Exists(path)) { //Yes file exists, so open the file workbook = EXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //get the workbook sheets collection sheets = workbook.Sheets; //set the location of the requested sheetName to -1, need to find where //it is. It may not actually exist indexOfsheetName = -1; //loop through the sheets collection for (int i = 1; i <= sheets.Count; i++) { //get the current worksheet at index (i) worksheet = (Worksheet)sheets.get_Item(i); //is the current worksheet the sheetName that was requested if (worksheet.Name.ToString().Equals(sheetName)) { //yes it is, so store its index indexOfsheetName = i; //Select all cells, and clear the contents Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells; myAllRange.Select(); myAllRange.CurrentRegion.Select(); myAllRange.ClearContents(); } } //At this point it is known that the sheetName that was requested //does not exist within the found file, so create a new sheet within the //sheets collection if (indexOfsheetName == -1) { //Create a new sheet for the requested sheet Worksheet sh = (Worksheet)workbook.Sheets.Add( Type.Missing, (Worksheet)sheets.get_Item(sheets.Count), Type.Missing, Type.Missing); //Change its name to that requested sh.Name = sheetName; } } #endregion #region FILE DOESNT EXIST //No the file DOES NOT exist, so create a new file else { //Add a new workbook to the file workbook = EXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); //get the workbook sheets collection sheets = workbook.Sheets; //get the new sheet worksheet = (Worksheet)sheets.get_Item(1); //Change its name to that requested worksheet.Name = sheetName; } #endregion #region get correct worksheet index for requested sheetName //get the workbook sheets collection sheets = workbook.Sheets; //set the location of the requested sheetName to -1, need to find where //it is. It will definately exist now as it has just been added indexOfsheetName = -1; //loop through the sheets collection for (int i = 1; i <= sheets.Count; i++) { //get the current worksheet at index (i) worksheet = (Worksheet)sheets.get_Item(i); //is the current worksheet the sheetName that was requested if (worksheet.Name.ToString().Equals(sheetName)) { //yes it is, so store its index indexOfsheetName = i; } }