这个方法可以把Dataset中的Table的集合分Sheet导出Excel
public static void EportExcel(DataSet ds, string path)
{
Worksheet worksheetData = null;
Workbook workbookData = null;
bool succeed = false;
if (ds != null)
{
Microsoft.Office.Interop.Excel.Application appExcel = null;
try
{
appExcel = new ApplicationClass();
}
catch (Exception ex)
{
throw ex;
}
if (appExcel != null)
{
try
{
object oMissing = Missing.Value;
workbookData = appExcel.Workbooks.Add(true);
workbookData.Sheets.Add(oMissing,oMissing,ds.Tables.Count,oMissing);
for(int k=0;k<ds.Tables.Count;k++)
{
worksheetData = (Worksheet)workbookData.Sheets[k+1];
worksheetData.Name = ds.Tables[k].TableName;
int rowIndex = 1;
int colIndex = 1;
int colCount = ds.Tables[k].Columns.Count;
int rowCount = ds.Tables[k].Rows.Count;
if(colCount>0&& rowCount>0)
{
//列名的处理
for(int i =0;i<colCount;i++)
{
worksheetData.Cells[rowIndex, colIndex] = ds.Tables[k].Columns[i].ColumnName;
colIndex++;
}
worksheetData.get_Range(worksheetData.Cells[rowIndex, 1], worksheetData.Cells[rowIndex, colCount]).Font.Bold = true;
worksheetData.get_Range(worksheetData.Cells[rowIndex, 1], worksheetData.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
worksheetData.get_Range(worksheetData.Cells[rowIndex, 1], worksheetData.Cells[rowCount + 1, colCount]).Font.Size = 10;
worksheetData.get_Range(worksheetData.Cells[rowIndex, 1], worksheetData.Cells[rowIndex, colCount]).Font.NumberFormartLocal = "@";
rowIndex++;
for(int i=0;i<rowCount;i++)
{
colIndex = 1;
for (int j = 0; j < colCount; j++)
{
worksheetData.Cells[rowIndex, colIndex] = ds.Tables[k].Rows[i][j].ToString();
colIndex++;
}
rowIndex++;
}
worksheetData.Cells.EntireColumn.AutoFit();
workbookData.Saved = true;
}
}
appExcel.DisplayAlerts = false;
path = Path.GetFullPath(path);
workbookData.SaveCopyAs(path);
workbookData.Close(false,null,null);
appExcel.Workbooks.Close();
Marshal.ReleaseComObject(worksheetData);
Marshal.ReleaseComObject(workbookData);
workbookData = null;
succeed = true;
}
catch(Exception ex)
{
succeed = false;
}
finally
{
appExcel.Quit();
Marshal.ReleaseComObject(appExcel);
int generation = System.GC.GetGeneration(appExcel);
appExcel = null;
System.GC.Collect(generation);
}
}
}
}