C# DataSet导出Excel

//多个DataSet导出Excel文件
public static void DataSetToExcel(DataSet p_ds,string strSavePath)
{
    int l_intSheetIndex=1;//多个DataSet导出多个Excel，针对Excel文件中的第一个Sheet
    //实例化Excel对象
    Microsoft.Office.Interop.Excel.Application app=new Microsoft.Office.Interop.Excel.Application();
    //根据Excel对象新建Excel文件
    Microsoft.Office.Interop.Excel.Workbook book=app.Workbooks.Add(true);
    try
    {
        foreach(DataTable dt in p_ds.Tables)
        {
            if(dt!=null && dt.Rows.Count>0)
        {
        object missing=System.Reflection.Missing.Value;
        Microsoft.Office.Interop.Excel.Worksheet sheet;
        //判断Excel文件是否存在Sheet
        //1.不存在就新建Sheet
        //2.存在就开启对应Sheet
        if(book.Worksheets.Count < l_intSheetIndex)
        {
           //新建Sheet
           sheet=(Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets.Add(Type.Missing,book.Worksheets[book.Worksheets.Count],1,Type.Missing);
        }
        //开启对应Sheet
        sheet=(Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets.get_Item(l_intSheetIndex);
        //设置Sheet名
        sheet.Name=dt.TableName;

        //取得区域  --取得区域的方式 1.设置整列 2.设置使用的区域
        //整列
        //Range column = ((Range)worksheet.Cells[1, 1]).EntireColumn;（选取A列；方法：先选取A1单元格，然后选取A1单元格所在的这一整列。）
        //使用的区域
        Microsoft.Office.Interop.Excel.Range l_range=sheet.Range[sheet.Cells[1,1],sheet.Cells[dt.Rows.Count+1,dt.Columns.Count]];
        //设置区域格式“@”是文本格式
        l_range.NumberFormatLocal="@";
        int i=0;
        foreach(DataColumn dc in dt.Columns)
        {
            sheet.Cells[1,i+1]=dc.ColumnName;
            i++;
        }
        int j=0;
        foreach(DataRow dr in dt.Rows)
        {
            int k=0;
            foreach(DataColumn dc in dt.Columns)
            {
            sheet.Cells[j+2,k+1]=dr[k].ToString();
            k++;
            }
        }
        book.Saveed=true;
        }
        l_intSheetIndex++;
        }
    ((Microsoft.Office.Interop.Excel.WorkSheet)book.Worksheets.get_Item(1)).Select();
    book.SaveAs(strSavePath);
    book.Close(Type.Missing,Type.Missing,Type.Missing);
    }
    catch(Exception ex)
    {
    throw ex;
    }
    finally
    {
    app.Quit();
    IntPtr t=new IntPtr(app.Hwnd);
    int d=0;
    GetWindowThreadProcessId(t,out d);
    System.Diagenostics.Process p=System.Diagnostics.Process.GetProcessById(d);
    p.Kill();
    }
}

[DllImport("User32.dll",CharSet=CharSet.Auto)]
Public static extern int GetWindowThreadProcessId(IntPtr hwnd,out int ID);

 

转载于:https://www.cnblogs.com/WarBlog/p/8073997.html