将大量数据从数据表导出到Excel [英] Export large amount of data from datatable to Excel

问题描述

您好。

我想将数据从Oracle表导出到Excel。

I want to export data from Oracle table to Excel.

excel应该有多张表格，并且应该 提示用户保存excel文件。我尝试了下面的代码，但代码太慢了。我不能使用任何第三方工具。

The excel should have multiple sheets and should prompt the user to save the excel file. I tried the below code , but the code is too slow. I cannot use any third party tool.

有没有其他方法可以更快地将数据传递给excel？

Is there any other way to pass data to excel in a faster way?

Excel.Application oXL = new Excel.Application();
Excel.Workbook oWB = oXL.Workbooks.Add(missing);
Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
             
using (OracleConnection con = new OracleConnection(oradb))
                {
    con.Open();

 test2(oSheet, "select * from table1", "Names", con);
                  
Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing) as Excel.Worksheet;
  test2(oSheet2, "select * from table2", "Address", con);

Excel.Worksheet oSheet3 = oWB.Sheets.Add(missing, missing, 2, missing) as Excel.Worksheet;
test2(oSheet3, "select * from table3", "All Users", con);

}

 public static void test2(Excel.Worksheet oSheet, string sql, string name, OracleConnection con)
        {
 OracleDataAdapter da = new OracleDataAdapter(sql, con);
 DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt == null || dt.Columns.Count == 0)
            {
            }
            else
            {
 oSheet.Name = name;
                
 for (var i = 0; i < dt.Columns.Count; i++)
 {
  oSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
 }
  for (var i = 0; i < dt.Rows.Count; i++)
  {
 for (var j = 0; j < dt.Columns.Count; j++)
   {
 oSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
    }
   }
  }
 }

还有其他更好的方法可以将大量数据导出到Excel吗？如何保存文件？

Is there any other better way to export large amount of data to excel ? How to save the file ?

谢谢

推荐答案

您好，

使用Visual Studio中的NuGet包管理器安装 SpreadSheetLight ，这是一个免费的库。在图书馆主页上有一个帮助文件，如果您愿意，因为这个库的功能相当大，但如果您需要的所有
都需要导入DataTable对象，那么您需要的只是下面的内容。

Install SpreadSheetLight, a free library using NuGet Package manager in Visual Studio. On the libraries home page there is a help file if you so desire as this libraries is rather large in functionality but if all you need is to import DataTable objects all you need is below.

将DataTable写入Excel的代码。

Code which writes a DataTable to Excel.

/// <summary>
/// Import a DataTable into a .xlsx file to a specific sheet starting at a specific cell address
/// </summary>
/// <param name="pFileName">Path and file name</param>
/// <param name="pSheetName">Sheet name to import data into</param>
/// <param name="pStartReference">cell reference to start import e.g. A1</param>
/// <param name="pDataTable">DataTabe to import from</param>
/// <param name="pIncludeHeaders">true to include column name, false to exclude column names</param>
public void ImportDataTable(string pFileName, string pSheetName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders  = true)
{
	using (SLDocument doc = new SLDocument(pFileName, pSheetName))
	{
		doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);
		doc.Save();
	}
}

这篇关于将大量数据从数据表导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！