DataSet中的数据导出到Excel代码,Asp.Net代码库,德仔网

代码语言
代码分类
【Asp.Net】 DataSet中的数据导出到Excel
作者:Dezai.CN / 发布于2012/10/9/ 694
DataSet中的数据导出到Excel
<div>方法A:</div>
<div>static class ExportingCellByCellMethod
 {</div>
<div> public static void ExportToExcel(DataSet dataSet, string outputPath)
 {
 // Create the Excel Application object
 ApplicationClass excelApp = new ApplicationClass();</div>
<div> // Create a new Excel Workbook
 Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);</div>
<div> int sheetIndex = 0;</div>
<div> // Copy each DataTable as a new Sheet
 foreach (System.Data.DataTable dt in dataSet.Tables)
 {</div>
<div> // Create a new Sheet
 Worksheet excelSheet = (Worksheet) excelWorkbook.Sheets.Add(
 excelWorkbook.Sheets.get_Item(++sheetIndex),
 Type.Missing, 1, XlSheetType.xlWorksheet);</div>
<div> excelSheet.Name = dt.TableName;</div>
<div> // Copy the column names (cell-by-cell)
 for (int col = 0; col < dt.Columns.Count; col++) {
 excelSheet.Cells[1, col + 1] = dt.Columns[col].ColumnName;
 }
<div> ((Range) excelSheet.Rows[1, Type.Missing]).Font.Bold = true;</div>
<div>
 // Copy the values (cell-by-cell)
 for (int col = 0; col < dt.Columns.Count; col++)
 {
 for (int row = 0; row < dt.Rows.Count; row++)
 {
 excelSheet.Cells[row + 2, col + 1] = dt.Rows[row].ItemArray[col];
 }
 }
<div> }
<div> // Save and Close the Workbook
 excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
 Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 excelWorkbook.Close(true, Type.Missing, Type.Missing);
 excelWorkbook = null;</div>
<div> // Release the Application object
 excelApp.Quit();
 excelApp = null;</div>
<div> // Collect the unreferenced objects
 GC.Collect();
 GC.WaitForPendingFinalizers();</div>
<div> }
}
<div></div>
<div></div>
<div>方法B:</div>
<div></div>
<div>public static void ExportToExcel(DataSet dataSet, string outputPath)
 {
 // Create the Excel Application object
 ApplicationClass excelApp = new ApplicationClass();</div>
<div> // Create a new Excel Workbook
 Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);</div>
<div> int sheetIndex = 0;</div>
<div> // Copy each DataTable
 foreach (System.Data.DataTable dt in dataSet.Tables)
 {</div>
<div> // Copy the DataTable to an object array
 object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];</div>
<div> // Copy the column names to the first row of the object array
 for (int col = 0; col < dt.Columns.Count; col++)
 {
 rawData[0, col] = dt.Columns[col].ColumnName;
 }
<div> // Copy the values to the object array
 for (int col = 0; col < dt.Columns.Count; col++)
 {
 for (int row = 0; row < dt.Rows.Count; row++)
 {
 rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
 }
 }
<div> // Calculate the final column letter
 string finalColLetter = string.Empty;
 string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
 int colCharsetLen = colCharset.Length;</div>
<div> if (dt.Columns.Count > colCharsetLen) {
 finalColLetter = colCharset.Substring(
 (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
 }
<div> finalColLetter += colCharset.Substring(
 (dt.Columns.Count - 1) % colCharsetLen, 1);</div>
<div> // Create a new Sheet
 Worksheet excelSheet = (Worksheet) excelWorkbook.Sheets.Add(
 excelWorkbook.Sheets.get_Item(++sheetIndex),
 Type.Missing, 1, XlSheetType.xlWorksheet);</div>
<div> excelSheet.Name = dt.TableName;</div>
<div> // Fast data export to Excel
 string excelRange = string.Format("A1:{0}{1}",
 finalColLetter, dt.Rows.Count + 1);</div>
<div> excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;</div>
<div> // Mark the first row as BOLD
 ((Range) excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
 }
<div> // Save and Close the Workbook
 excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
 Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 excelWorkbook.Close(true, Type.Missing, Type.Missing);
 excelWorkbook = null;</div>
<div> // Release the Application object
 excelApp.Quit();
 excelApp = null;</div>
<div> // Collect the unreferenced objects
 GC.Collect();
 GC.WaitForPendingFinalizers();
}
<div></div>
<div>在使用上述方法是需要引进Microsoft.Office.Interop.Excel,并且将该dll的Embed Interop  Types属性设置为false。</div>
<div></div>
试试其它关键字
　导出到Excel　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)