Export large amount of data from datatable to Excel

[Venkatzeus]

Hi.

I want to export data from Oracle table to 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.

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.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[j];
}
}
}
}

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

Thanks

Continue reading...