introduction of Microsoft.Office.Interop.Excel.dll
Format: header underlined, cell content centered
1 usingSystem;2 usingSystem.Data;3 usingSystem.Data.SqlClient;4 usingSystem.Web.UI; 5 6 Public Static voidDatasettoexcel (DataTable DataTable,stringSaveFile)7 { 8 Microsoft.Office.Interop.Excel.Application Excel; 9 Ten Microsoft.office.interop.excel._workbook Workbook; One A Microsoft.office.interop.excel._worksheet Worksheet; - - ObjectMisvalue =System.Reflection.Missing.Value; the -Excel =NewMicrosoft.Office.Interop.Excel.Application (); - -WorkBook =Excel. Workbooks.Add (Misvalue); + -WorkSheet =(Microsoft.office.interop.excel._worksheet) Workbook.activesheet; + A intRowIndex =1; at - intColindex =0; - - //Get title - foreach(DataColumn Colinchdatatable.columns) - { incolindex++; - toExcel. cells[1, Colindex] =Col. ColumnName; +Microsoft.Office.Interop.Excel.Range Range = worksheet.range[worksheet.cells[1, colindex],worksheet.cells[1, Colindex]]; -Range. Font.underline =true; the } * $ //get the data in the tablePanax Notoginseng foreach(DataRow rowinchdatatable.rows) - { therowindex++; + AColindex =0; the + foreach(DataColumn Colinchdatatable.columns) - { $colindex++; $ -Excel. Cells[rowindex, Colindex] = - the Row[col. ColumnName]. ToString (). Trim (); - WuyiMicrosoft.Office.Interop.Excel.Range Range =Worksheet.range[excel. Cells[rowindex, Colindex], Excel. Cells[rowindex, Colindex]]; theRange. HorizontalAlignment =Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; - } Wu } - AboutExcel. Visible =false; $Excel. DisplayAlerts =false; - - if(WorkSheet! =NULL) - { A Workbook.saveas (SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misvalue,< /c7> + the Misvalue, Misvalue, Misvalue, microsoft.office.interop.excel.xlsaveasaccessmode.xlexclusive,< /c10> - $ Misvalue, Misvalue, Misvalue, Misvalue, misvalue); the } the the theDataTable =NULL; - inWorkbook.close (true, Misvalue, Misvalue); the the Excel. Quit (); About the the Releaseobject (WorkSheet); the + Releaseobject (WorkBook); - the Releaseobject (Excel); Bayi the } the - Private Static voidReleaseobject (Objectobj) - { the Try the { the System.Runtime.InteropServices.Marshal.ReleaseComObject (obj); theobj =NULL; - } the Catch the { theobj =NULL; 94 } the finally the { the GC. Collect (); 98 } About}
Call Funxction Example:
Datasettoexcel (ds. tables[0@ "D:\ZhaoNick\Desktop\test.xls");
This time need to do a windowservices, the main function is to extract data from local DB into the FTP Excel file, after extracting the data successfully into the dataset, when imported to Excel to the title underline and the content Set center, found the report " Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: "Object" does not contain the definition of "get_range " Such a mistake, It was later discovered that there was a problem with the. NET version or the wording. But in the end the pit was settled.
C # DataSet data imported into Excel fix-. NET FrameWork more than 4.0