如何从c＃中的数据集或数据表中导出excel？

Question

我想在不使用Gridview的情况下将数据从数据集或数据表导出到Excel文件。

Answer 1

获取更多的方式：9 Solutions to Export Data to Excel for ASP.NET

我有这样的代码，但是这需要包括Excel的COM组件

在您的项目会为你做任务添加的Microsoft.Office.Interop.Excel.dll参考。

using Excel = Microsoft.Office.Interop.Excel; 
public static bool ExportDataTableToExcel(DataTable dt, string filepath) 
    { 

    Excel.Application oXL; 
    Excel.Workbook oWB; 
    Excel.Worksheet oSheet; 
    Excel.Range oRange; 

    try 
    { 
     // Start Excel and get Application object. 
     oXL = new Excel.Application(); 

     // Set some properties 
     oXL.Visible = true; 
     oXL.DisplayAlerts = false; 

     // Get a new workbook. 
     oWB = oXL.Workbooks.Add(Missing.Value); 

     // Get the Active sheet 
     oSheet = (Excel.Worksheet)oWB.ActiveSheet; 
     oSheet.Name = "Data"; 

     int rowCount = 1; 
     foreach (DataRow dr in dt.Rows) 
     { 
      rowCount += 1; 
      for (int i = 1; i < dt.Columns.Count + 1; i++) 
      { 
       // Add the header the first time through 
       if (rowCount == 2) 
       { 
        oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName; 
       } 
       oSheet.Cells[rowCount, i] = dr[i - 1].ToString(); 
      } 
     } 

     // Resize the columns 
     oRange = oSheet.get_Range(oSheet.Cells[1, 1], 
         oSheet.Cells[rowCount, dt.Columns.Count]); 
     oRange.EntireColumn.AutoFit(); 

     // Save the sheet and close 
     oSheet = null; 
     oRange = null; 
     oWB.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookNormal, 
      Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
      Excel.XlSaveAsAccessMode.xlExclusive, 
      Missing.Value, Missing.Value, Missing.Value, 
      Missing.Value, Missing.Value); 
     oWB.Close(Missing.Value, Missing.Value, Missing.Value); 
     oWB = null; 
     oXL.Quit(); 
    } 
    catch 
    { 
     throw; 
    } 
    finally 
    { 
     // Clean up 
     // NOTE: When in release mode, this does the trick 
     GC.WaitForPendingFinalizers(); 
     GC.Collect(); 
     GC.WaitForPendingFinalizers(); 
     GC.Collect(); 
    } 

    return true; 
} 

Answer 2

可以使用

using Microsoft.Office.Interop.Excel; 

与Excel文档 “原生” 的工作......

用C＃创建一个Excel文档 http://www.codeproject.com/Articles/20228/Using-C-to-Create-an-Excel-Document

但最常见的报告生成器/设计师可以轻松导出到excel 也检查报告服务如果您正在使用SQL SERVER

Answer 3

我会建议EPPlus - 这种解决方案并不需要COM互操作也不DLL和速度非常快。完美适合网络场景。

http://epplus.codeplex.com/

http://nuget.org/packages/EPPlus

private void DumpExcel(DataTable tbl) 
{ 
    using (ExcelPackage pck = new ExcelPackage()) 
    { 
      //Create the worksheet 
      ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo"); 

      //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 
      ws.Cells["A1"].LoadFromDataTable(tbl, true); 

      //Format the header for column 1-3 
      using (ExcelRange rng = ws.Cells["A1:C1"]) 
      { 
       rng.Style.Font.Bold = true; 
       rng.Style.Fill.PatternType = ExcelFillStyle.Solid;      //Set Pattern for the background to Solid 
       rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue 
       rng.Style.Font.Color.SetColor(Color.White); 
      } 

      //Example how to Format Column 1 as numeric 
      using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1]) 
      { 
        col.Style.Numberformat.Format = "#,##0.00"; 
        col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; 
       } 

       //Write it back to the client 
       Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; 
       Response.AddHeader("content-disposition", "attachment; filename=ExcelDemo.xlsx"); 
       Response.BinaryWrite(pck.GetAsByteArray()); 
      } 
     } 
    } 
} 

Answer 4

万一别人使用的选择的解决方案，并运行到“对象不包含get_Range定义”异常。我在这里找到了解决方案：Worksheet get_Range throws exception。我希望这会节省你的时间。