开发学院数据库MSSQL Server DataSet导出到Excel比较完整的解决方案(二)--服... 阅读

DataSet导出到Excel比较完整的解决方案(二)--服务器端生成文件(downmoon) (上)

 2009-01-16 10:19:13 来源:WEB开发网   
核心提示:在前一篇文章中,介绍了DataSet导出到Excel时客户端生成文件的几种思路,DataSet导出到Excel比较完整的解决方案(二)--服务器端生成文件(downmoon) (上),接着往下说,服务器端生成文件,用户直接下载,应该格式是可以保证的!于是直接调用Excel的API生成

在前一篇文章中,介绍了DataSet导出到Excel时客户端生成文件的几种思路,接着往下说,服务器端生成文件,用户直接下载,应该格式是可以保证的!

于是直接调用Excel的API生成。代码如下:

DataSetToLocalExcel

public static void DataSetToLocalExcel(DataSet dataSet, string outputPath, bool deleteOldFile)
    {
      if (deleteOldFile)
      {
        if (System.IO.File.Exists(outputPath)) { System.IO.File.Delete(outputPath); }
      }
      // Create the Excel Application object
      ApplicationClass excelApp = new ApplicationClass();
  
      // Create a new Excel Workbook
      Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
  
      int sheetIndex = 0;
  
      // Copy each DataTable
      foreach (System.Data.DataTable dt in dataSet.Tables)
      {
  
        // Copy the DataTable to an object array
        object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
  
        // Copy the column names to the first row of the object array
        for (int col = 0; col < dt.Columns.Count; col++)
        {
          rawData[0, col] = dt.Columns[col].ColumnName;
        }
  
        // Copy the values to the object array
        for (int col = 0; col < dt.Columns.Count; col++)
        {
          for (int row = 0; row < dt.Rows.Count; row++)
          {
            rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
          }
        }
  
        // Calculate the final column letter
        string finalColLetter = string.Empty;
        string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        int colCharsetLen = colCharset.Length;
  
        if (dt.Columns.Count > colCharsetLen)
        {
          finalColLetter = colCharset.Substring(
            (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
        }
  
        finalColLetter += colCharset.Substring(
            (dt.Columns.Count - 1) % colCharsetLen, 1);
  
        // Create a new Sheet
        Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
          excelWorkbook.Sheets.get_Item(++sheetIndex),
          Type.Missing, 1, XlSheetType.xlWorksheet);
  
        excelSheet.Name = dt.TableName;
  
        // Fast data export to Excel
        string excelRange = string.Format("A1:{0}{1}",
          finalColLetter, dt.Rows.Count + 1);
  
        excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
  
        // Mark the first row as BOLD
        ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
      }
      //excelApp.Application.AlertBeforeOverwriting = false;
      excelApp.Application.DisplayAlerts = false;
      // Save and Close the Workbook
      excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
      excelWorkbook.Close(true, Type.Missing, Type.Missing);
      excelWorkbook = null;
  
      // Release the Application object
      excelApp.Quit();
      excelApp = null;
  
      // Collect the unreferenced objects
      GC.Collect();
      GC.WaitForPendingFinalizers();
  
    }

1 2 3  下一页

Tags:DataSet 导出 Excel

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
更多精彩
    赞助商链接

    热点阅读
      焦点图片
        最新推荐
          精彩阅读