• DataSet保存成Excel(DataSet中的每个Table保存为一个Sheet)


    这个方法可以把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);
    }
    }

    }

    }

  • 相关阅读:
    HDU 1247
    [转载]亲密接触VC6.0编译器
    [转载]你该学什么程序语言
    ACE学习2009116
    新东方英语学习二
    电脑族吃什么比较好
    爱默生生活的准则
    成大事必备9种能力9种手段9种心态
    [转载]句柄和指针
    关于WM_CREATE消息
  • 原文地址:https://www.cnblogs.com/qizh/p/3586241.html
Copyright © 2020-2023  润新知