I am exporting Sql data to Excel. The code I am using currently is :
DataTable dt = new DataTable();
// Create sql connection string
string conString = "Data Source=DELL\\SQLSERVER1;Trusted_Connection=True;DATABASE=Zelen;CONNECTION RESET=FALSE";
SqlConnection sqlCon = new SqlConnection(conString);
sqlCon.Open();
SqlDataAdapter da = new SqlDataAdapter("select LocalSKU,ItemName, QOH,Price,Discontinued,CAST(Barcode As varchar(25)) As Barcode,Integer2,Integer3,ISNULL(SalePrice,0.0000)AS SalePrice,SaleOn,ISNULL(Price2,0.0000)AS Price2 from dbo.Inventory", sqlCon);
System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
da.Fill(dtMainSQLData);
DataColumnCollection dcCollection = dtMainSQLData.Columns;
// Export Data into EXCEL Sheet
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
int i = 1;
int j = 1;
int s = 1;
//header row
foreach (DataColumn col in dtMainSQLData.Columns)
{
ExcelApp.Cells[i, j] = col.ColumnName;
j++;
ExcelApp.Rows.AutoFit();
ExcelApp.Columns.AutoFit();
}
i++;
//data rows
foreach (DataRow row in dtMainSQLData.Rows)
{
for (int k = 1; k < dtMainSQLData.Columns.Count + 1; k++)
{
ExcelApp.Cells[i, k] = "'" + row[k - 1].ToString();
}
i++;
s++;
Console.Write(s);
Console.Write("\n\r");
ExcelApp.Columns.AutoFit();
ExcelApp.Rows.AutoFit();
}
var b = Environment.CurrentDirectory + @"\Sheet1.xlsx";
ExcelApp.ActiveWorkbook.SaveCopyAs(b);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
Console.WriteLine(".xlsx file Exported succssessfully.");
Takes are 70000 rows in my sql database. I am running this script in Console application. It takes more then an hour to export it to excel file.
How can I use this to export it faster?
Examples would be appreciated.
Option 1:
See this answer. Use a library called ClosedXML to write the data to Excel.
Option 2:
Get a range big enough for all of the data and set the value equal to a 2 dimensional range. This works very fast without another referencing another library. I tried with 70000 records.
// Get an excel instance
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Get a workbook
Workbook wb = excel.Workbooks.Add();
// Get a worksheet
Worksheet ws = wb.Worksheets.Add();
ws.Name = "Test Export";
// Add column names to the first row
int col = 1;
foreach (DataColumn c in table.Columns) {
ws.Cells[1, col] = c.ColumnName;
col++;
}
// Create a 2D array with the data from the table
int i = 0;
string[,] data = new string[table.Rows.Count, table.Columns.Count];
foreach (DataRow row in table.Rows) {
int j = 0;
foreach (DataColumn c in table.Columns) {
data[i,j] = row[c].ToString();
j++;
}
i++;
}
// Set the range value to the 2D array
ws.Range[ws.Cells[2, 1], ws.Cells[table.Rows.Count + 1, table.Columns.Count]].value = data;
// Auto fit columns and rows, show excel, save.. etc
excel.Columns.AutoFit();
excel.Rows.AutoFit();
excel.Visible = true;
Edit: This version exported a million records on my machine it takes about a minute. This example uses Excel interop and breaks the rows in to chunks of 100,000.
// Start a stopwatch to time the process
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
// Check if there are rows to process
if (table != null && table.Rows.Count > 0) {
// Determine the number of chunks
int chunkSize = 100000;
double chunkCountD = (double)table.Rows.Count / (double)chunkSize;
int chunkCount = table.Rows.Count / chunkSize;
chunkCount = chunkCountD > chunkCount ? chunkCount + 1 : chunkCount;
// Instantiate excel
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Get a workbook
Workbook wb = excel.Workbooks.Add();
// Get a worksheet
Worksheet ws = wb.Worksheets.Add();
ws.Name = "Test Export";
// Add column names to excel
int col = 1;
foreach (DataColumn c in table.Columns) {
ws.Cells[1, col] = c.ColumnName;
col++;
}
// Build 2D array
int i = 0;
string[,] data = new string[table.Rows.Count, table.Columns.Count];
foreach (DataRow row in table.Rows) {
int j = 0;
foreach (DataColumn c in table.Columns) {
data[i, j] = row[c].ToString();
j++;
}
i++;
}
int processed = 0;
int data2DLength = data.GetLength(1);
for (int chunk = 1; chunk <= chunkCount; chunk++) {
if (table.Rows.Count - processed < chunkSize) chunkSize = table.Rows.Count - processed;
string[,] chunkData = new string[chunkSize, data2DLength];
int l = 0;
for (int k = processed; k < chunkSize + processed; k++) {
for (int m = 0; m < data2DLength; m++) {
chunkData[l,m] = table.Rows[k][m].ToString();
}
l++;
}
// Set the range value to the chunk 2d array
ws.Range[ws.Cells[2 + processed, 1], ws.Cells[processed + chunkSize + 1, data2DLength]].value = chunkData;
processed += chunkSize;
}
// Auto fit columns and rows, show excel, save.. etc
excel.Columns.AutoFit();
excel.Rows.AutoFit();
excel.Visible = true;
}
// Stop the stopwatch and display the seconds elapsed
sw.Stop();
MessageBox.Show(sw.Elapsed.TotalSeconds.ToString());