在C＃中将数据表导出为ex​​cel

问题描述

我的数据超过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();

         }

推荐答案

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