每一個Table生成一個sheet頁:
/// <summary>
/// 将DataSet裡所有資料導入Excel.
/// 需要添加COM: Microsoft Excel Object Library.
/// using Excel;
/// </summary>
/// <param name="filePath"></param>
/// <param name="ds"></param>
public static void ExportToExcel( string filePath, DataSet ds)
{
object oMissing = System.Reflection.Missing.Value;
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
try
{
// 打開Excel檔案。
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing);
Excel.Worksheet xlWorksheet;
// 循環所有DataTable
for ( int i = 0 ; i < ds.Tables.Count; i ++ )
{
// 添加入一個新的Sheet頁。
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing, 1 ,oMissing);
// 以TableName作為新加的Sheet頁名。
xlWorksheet.Name = ds.Tables[i].TableName;
// 取出這個DataTable中的所有值,暫存于stringBuffer中。
string stringBuffer = "" ;
for ( int j = 0 ; j < ds.Tables[i].Rows.Count; j ++ )
{
for ( int k = 0 ; k < ds.Tables[i].Columns.Count; k ++ )
{
stringBuffer += ds.Tables[i].Rows[j][k].ToString();
if ( k < ds.Tables[i].Columns.Count - 1 )
stringBuffer += " " ;
}
stringBuffer += " " ;
}
// 利用系統剪切闆
System.Windows.Forms.Clipboard.SetDataObject( "" );
// 将stringBuffer放入剪切闆。
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
// 選中這個sheet頁中的第一個單元格
((Excel.Range)xlWorksheet.Cells[ 1 , 1 ]).Select();
// 粘貼!
xlWorksheet.Paste(oMissing,oMissing);
// 清空系統剪切闆。
System.Windows.Forms.Clipboard.SetDataObject( "" );
}
// 儲存并關閉這個工作簿。
xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null ;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// 釋放...
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null ;
GC.Collect();
}
}
将多個DataTable導出到一個excel的同一個Sheet裡:
public static void ExportToExcel( string filePath, DataSet ds)
{
object oMissing = System.Reflection.Missing.Value;
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
// xlApp.Application.Workbooks.Add(true);
try
{
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);
Excel.Worksheet xlSheet;
xlSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(oMissing,oMissing, 1 ,oMissing);
xlSheet.Name = ds.DataSetName;
int ExcelRowNumber = 1 ; // 記錄資料集裡表所在的行
for ( int i = 0 ;i < ds.Tables.Count;i ++ )
{
string strBuffer = "" ;
int tableRowAcount = 0 ; // 記錄單個表的行數
for ( int j = 0 ;j < ds.Tables[i].Rows.Count;j ++ )
{
for ( int k = 0 ;k < ds.Tables[i].Columns.Count;k ++ )
{
strBuffer += ds.Tables[i].Rows[j][k].ToString();
if (k < ds.Tables[i].Columns.Count - 1 )
strBuffer += " " ;
}
strBuffer += " " ;
tableRowAcount ++ ;
}
System.Windows.Forms.Clipboard.SetDataObject( "" );
System.Windows.Forms.Clipboard.SetDataObject(strBuffer);
((Excel.Range)xlSheet.Cells[ExcelRowNumber, 1 ]).Select();
xlSheet.Paste(oMissing,oMissing);
System.Windows.Forms.Clipboard.SetDataObject( "" );
ExcelRowNumber += tableRowAcount;
ExcelRowNumber ++ ;
} // end for
xlWorkBook.Close(Excel.XlSaveAction.xlSaveChanges,oMissing,oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
xlWorkBook = null ;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
xlApp.Quit();
xlApp = null ;
GC.Collect();
}
}
以上方法都是打開一個已有的excel,下面是如果有個同名的excel就提示是否覆寫,如果沒有就建立一個:
Excel.ApplicationClass my = new Excel.ApplicationClass();
my.Visible = false ;
object objMissing = System.Reflection.Missing.Value;
Excel.Workbook mybook = (Excel.Workbook)my.Workbooks.Add( 1 );
Excel.Worksheet xlWorksheet;
for ( int i = 0 ; i < ds.Tables.Count; i ++ )
{
xlWorksheet = (Excel.Worksheet)mybook.Worksheets.Add(objMissing, objMissing, 1 , objMissing);
xlWorksheet.Name = ds.Tables[i].TableName;
for ( int j = 0 ; j < ds.Tables[i].Rows.Count; j ++ )
{
for ( int k = 0 ; k < ds.Tables[i].Columns.Count; k ++ )
{
xlWorksheet.Cells[ 1 , k + 1 ] = ds.Tables[i].Columns[k].Caption;
xlWorksheet.Cells[j + 2 , k + 1 ] = ds.Tables[i].Rows[j][k];
}
}
}
try
{
mybook.SaveAs( " e:/aa.xls " , objMissing, objMissing, objMissing, objMissing, objMissing, Excel.XlSaveAsAccessMode.xlShared, objMissing, objMissing, objMissing, objMissing, objMissing);
mybook = null ;
my.Quit();
my = null ;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// 用法和前面的大同小異,是以并未注釋...
以上方法都可通過自己的實際需要進行修改,達到舉一反三的目的.
若覺得還可以,就頂下...謝謝...嘿嘿....