每一个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();
}
}
/// 将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();
}
}
{
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);
}
// 用法和前面的大同小异,所以并未注释...
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);
}
// 用法和前面的大同小异,所以并未注释...
以上方法都可通过自己的实际需要进行修改,达到举一反三的目的.
若觉得还可以,就顶下...谢谢...嘿嘿....