在C＃中将数据表导出为ex​​cel [英] Export datatable to excel in C#

问题描述

我的数据超过10万，我需要在excel中导出。因此，当我导出数据时，它需要花费太多时间来导出大数据:(



如果我导出500到1000个记录，它很快就会导出但不会超过。



请帮我快速，快速地输出10万条记录数据



什么我试过了：

I have data more that 1 lakh which i need to export in excel. So when i am exporting data its taking too much time to export for large data :(

and if i export 500 to 1000 records it export soon but not more than that.

Please help me to export data soon and fast for 1 lakh records

What I have tried:

string _path = "C:\\Test.xls";

 DataSet ds = new DataSet();
 ds.Tables.Add(dt);    \\loading datatable "dt" into dataset

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;

                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet.Cells[1, 1] = "Sheet 1 content";

                xlWorkBook.SaveAs(_path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                ExportDataSetToExcel(ds, _path);

 private void ExportDataSetToExcel(DataSet ds, string _filename)
         {
             //Creae an Excel application instance
             Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

             //Create an Excel workbook instance and open it from the predefined location
             Microsoft.Office.Interop.Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(_filename);

             foreach (DataTable table in ds.Tables)
             {
                 //Add a new worksheet to workbook with the Datatable name
                 Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
                 excelWorkSheet.Name = table.TableName;

                 for (int i = 1; i < table.Columns.Count + 1; i++)
                 {
                     excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                 }

                 for (int j = 0; j < table.Rows.Count; j++)
                 {
                    
                     for (int k = 0; k < table.Columns.Count; k++)
                     {
                         excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                     }
                 }
             }

             excelWorkBook.Save();
             excelWorkBook.Close();
             excelApp.Quit();

         }

推荐答案

您好，试试EPPlus库

• EPPlus - 在服务器上创建高级Excel电子表格 - 主页 [ ^ ]
  
  
• EPPlus-在服务器上创建高级Excel电子表格 - 文档 [ ^ ]
  
  
• 使用EPPlus在C＃.Net中创建/读取/编辑高级Excel 2007/2010报告 [ ^ ]
  
  
• Easy Excel与EPPlus交互（第5部分）：导出到Excel [ ^ ]
  
  

Hi, try EPPlus library

• EPPlus-Create advanced Excel spreadsheets on the server - Home[^]
  
• EPPlus-Create advanced Excel spreadsheets on the server - Documentation[^]
  
• Create/Read/Edit Advance Excel 2007/2010 Report in C#.Net using EPPlus[^]
  
• Easy Excel Interaction with EPPlus (Part 5): Export to Excel[^]
  

我同意VR Karthikeyan，EPPlus是一个好的选择。无论如何，您可以查看以下链接：在C＃中操作Excel文档 - 导入/导出数据从DataTable到Excel 或免费导出到ExcelC＃类，使用OpenXML

I agree with VR Karthikeyan, EPPlus is a good choice. Anyway, you can go through and look into below links: Operate excel document in C#--Import/Export Data from DataTable to Excel or A free "Export to Excel" C# class, using OpenXML

这篇关于在C＃中将数据表导出为ex​​cel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！