抄的，自己看，别问我。下面代码从DataSet和excel的导入导出，具体从数据库到DataSet应该会把！引用COM里面的Microsoft.Excel 11.0 1 public class ImportExportToExcel 2 { 3 private string strConn ; 4 5 private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog(); 6 private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 7 8 public ImportExportToExcel() 9 { 10 // 11 // TODO: 在此处添加构造函数逻辑 12 // 13 this.openFileDlg.DefaultExt = "xls"; 14 this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls"; 15 16 this.saveFileDlg.DefaultExt="xls"; 17 this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls"; 18 19 } 20 21 从Excel文件导入到DataSet#region 从Excel文件导入到DataSet 22 // /// 23 // /// 从Excel导入文件 24 // /// 25 // /// Excel文件名 26 // /// 返回DataSet 27 // public DataSet ImportFromExcel(string strExcelFileName) 28 // { 29 // return doImport(strExcelFileName); 30 // } 31 /**//// 32 /// 从选择的Excel文件导入 33 /// 34 /// DataSet 35 public DataSet ImportFromExcel() 36 { 37 DataSet ds=new DataSet(); 38 if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 39 ds=doImport(openFileDlg.FileName); 40 return ds; 41 } 42 /**//// 43 /// 从指定的Excel文件导入 44 /// 45 /// Excel文件名 46 /// 47 public DataSet ImportFromExcel(string strFileName) 48 { 49 DataSet ds=new DataSet(); 50 ds=doImport(strFileName); 51 return ds; 52 } 53 /**//// 54 /// 执行导入 55 /// 56 /// 文件名 57 /// DataSet 58 private DataSet doImport(string strFileName) 59 { 60 if (strFileName=="") return null; 61 62 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + 63 "Data Source=" + strFileName + ";" + 64 "Extended Properties=Excel 8.0;"; 65 OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn); 66 67 DataSet ExcelDs = new DataSet(); 68 try 69 { 70 ExcelDA.Fill(ExcelDs, "ExcelInfo"); 71 72 } 73 catch(Exception err) 74 { 75 System.Console.WriteLine( err.ToString() ); 76 } 77 return ExcelDs; 78 79 80 81 } 82 #endregion 83 84 从DataSet到出到Excel#region 从DataSet到出到Excel 85 /**//// 86 /// 导出指定的Excel文件 87 /// 88 /// 要导出的DataSet 89 /// 要导出的Excel文件名 90 public void ExportToExcel(DataSet ds,string strExcelFileName) 91 { 92 if (ds.Tables.Count==0 || strExcelFileName=="") return; 93 doExport(ds,strExcelFileName); 94 95 96 } 97 /**//// 98 /// 导出用户选择的Excel文件 99 /// 100 /// DataSet 101 public void ExportToExcel(DataSet ds) 102 { 103 if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 104 doExport(ds,saveFileDlg.FileName); 105 106 } 107 /**//// 108 /// 执行导出 109 /// 110 /// 要导出的DataSet 111 /// 要导出的文件名 112 private void doExport(DataSet ds,string strExcelFileName) 113 { 114 115 Excel.Application excel= new Excel.Application(); 116 117 // Excel.Workbook obj=new Excel.WorkbookClass(); 118 // obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null); 119 120 int rowIndex=1; 121 int colIndex=0; 122 123 excel.Application.Workbooks.Add(true); 124 125 126 System.Data.DataTable table=ds.Tables[0] ; 127 foreach(DataColumn col in table.Columns) 128 { 129 colIndex++; 130 excel.Cells[1,colIndex]=col.ColumnName; 131 } 132 133 foreach(DataRow row in table.Rows) 134 { 135 rowIndex++; 136 colIndex=0; 137 foreach(DataColumn col in table.Columns) 138 { 139 colIndex++; 140 excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString(); 141 } 142 } 143 excel.Visible=false; 144 excel.Sheets[0] = "sss"; 145 excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null); 146 147 148 //wkbNew.SaveAs strBookName 149 150 151 //excel.Save(strExcelFileName); 152 excel.Quit(); 153 excel=null; 154 155 GC.Collect();//垃圾回收 156 } 157 #endregion 0
