I'm trying to export millions of data from different data table to single excel file with multiple sheets. I was able to achieve this for thousands of records but my approach seems not efficient for the millions of data export.
Is there any alternate way to achieve the same for millions of records export to single excel file ? (I checked so many forums but all seem to follow the same approach of what I did in the below code)
Below is my code for exporting data from different data table to multiple sheets in a single excel file.
I know the code is little big but trust me it has so many parts listed which i didnt mention here. My point is to show the technique i used to push data into excel file.
Let me know if anyone have some better idea on this one. Thanks in advance.
private void technicalvalidation_DoWork(object sender, DoWorkEventArgs e)
{
if (MessageBox.Show("This process will take more than 10 minutes. You cannot use the tool utill processing finishes.\n\nDo you want to continue?", "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
int countdataset;
int loopcount = 1;
String Date = DateTime.Now.ToString("MM.dd.yyy_HH.mm.ss");
DataSet ds = new DataSet();
backgroundWorker.ReportProgress(1);
//StoredProcedure Execution for the validation engine
string connectionString = ConfigurationManager.ConnectionStrings["VEG_proj_Tool.Properties.Settings.VEG_ToolprojConnectionString"].ConnectionString;
string commandText = "proj.ValidationEngine-TechnicalValidation";
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 600;
try
{
conn.Open();
SDA.SelectCommand = cmd;
SDA.Fill(ds);
ds.Tables[0].TableName = "DataseriesNotinDatavalues";
//ds.Tables[1].TableName = "DVDataseriesNotinDataseries";
ds.Tables[1].TableName = "DS-IDvalidation";
ds.Tables[2].TableName = "DV-IDvalidation";
ds.Tables[3].TableName = "CountryCount";
ds.Tables[4].TableName = "CountryList";
ds.Tables[5].TableName = "StateCount";
ds.Tables[6].TableName = "StateList";
ds.Tables[7].TableName = "GeoDuplicates";
countdataset = ds.Tables.Count;
}
catch (SqlException ex)
{
backgroundWorker.ReportProgress(0);
MessageBox.Show(ex.Message + "Data processing unsuccessful, please contact technical team.");
saveAction(ex.Message);
}
finally
{
conn.Close();
}
if (!Directory.Exists("C:\\proj Tool\\Validation Results proj")) // name of the error file and location
{
Directory.CreateDirectory("C:\\proj Tool\\Validation Results proj");
}
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
backgroundWorker.ReportProgress(0);
MessageBox.Show("Excel is not properly installed!!");
return;
}
try
{
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkBook.SaveAs("C:\\proj Tool\\Validation Results proj\\TechnicalValidation" + "_" + Date + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
string filePath = @"C:\\proj Tool\\Validation Results proj\\TechnicalValidation" + "_" + Date + ".xls";
xlWorkBook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//It supperesses the Microsoft Compatibility message
xlWorkBook.CheckCompatibility = false;
//For sheet1------------------------------>
Excel.Worksheet excelWorkSheet = xlWorkBook.Sheets.Add();
excelWorkSheet.Name = "DataSeries Validation";
foreach (DataTable table in ds.Tables)
{
if (loopcount > 3)
{
break;
}
if (table.TableName == "DataseriesNotinDatavalues")
{
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[3, i] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet.get_Range("A3");
formatRange.EntireRow.Font.Bold = true;
}
}
if (table.TableName == "DS-IDvalidation")
{
int li = 6;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[3, li] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet.get_Range("A3");
formatRange.EntireRow.Font.Bold = true;
li = li + 1;
}
}
if (table.TableName == "DV-IDvalidation")
{
int ki = 11;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[3, ki] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet.get_Range("A3");
formatRange.EntireRow.Font.Bold = true;
ki = ki + 1;
}
}
if (table.Rows.Count == 0)
{
if (table.TableName == "DataseriesNotinDatavalues")
{
excelWorkSheet.Cells[5, 2].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet.get_Range("B5", "D5").Merge();
excelWorkSheet.Cells[5, 2] = excelWorkSheet.get_Range("B5", "D5");
excelWorkSheet.Cells[5, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelWorkSheet.Cells[5, 2] = "No mismatch found in Datavalues";
}
if (table.TableName == "DS-IDvalidation")
{
excelWorkSheet.Cells[5, 7].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet.get_Range("G5", "I5").Merge();
excelWorkSheet.Cells[5, 7] = excelWorkSheet.get_Range("G5", "I5");
excelWorkSheet.Cells[5, 7].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelWorkSheet.Cells[5, 7] = "No IDmismatch found in Dataseries";
}
if (table.TableName == "DV-IDvalidation")
{
excelWorkSheet.Cells[5, 12].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet.get_Range("L5", "N5").Merge();
excelWorkSheet.Cells[5, 12] = excelWorkSheet.get_Range("L5", "N5");
excelWorkSheet.Cells[5, 12].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelWorkSheet.Cells[5, 12] = "No IDmismatch found in Datavalues";
}
}
if (table.TableName == "DataseriesNotinDatavalues")
{
int l = 4;
excelWorkSheet.get_Range("A2", "C2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet.Cells[2, 1] = "Data Series not in Data Values";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[l, k + 1] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet.Cells[l, k + 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
}
l = l + 1;
}
}
if (table.TableName == "DS-IDvalidation")
{
int l = 4;
int la = 6;
excelWorkSheet.get_Range("F2", "H2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet.Cells[2, 6] = "Validate each ID in data series to match its definition in the LU tables";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[l, la] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet.Cells[l, la].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RosyBrown);
la = la + 1;
}
l = l + 1;
la = 6;
}
}
if (table.TableName == "DV-IDvalidation")
{
int l = 4;
int lb = 11;
excelWorkSheet.get_Range("K2", "M2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet.Cells[2, 11] = "Validate each ID in data values to match its definition in the LU tables";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[l, lb] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet.Cells[l, lb].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightCyan);
lb = lb + 1;
}
l = l + 1;
lb = 11;
}
}
loopcount = loopcount + 1;
}
//For sheet2------------------------------>
Excel.Worksheet excelWorkSheet1 = xlWorkBook.Sheets.Add();
excelWorkSheet1.Name = "Geo Validation";
loopcount = 1;
foreach (DataTable table in ds.Tables)
{
if (loopcount > 3)
{
if (loopcount > 8)
{
break;
}
if (table.TableName == "CountryCount")
{
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet1.Cells[3, i] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet1.get_Range("A3");
formatRange.EntireRow.Font.Bold = true;
}
}
if (table.TableName == "CountryList")
{
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet1.Cells[4, i] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet1.get_Range("A4");
formatRange.EntireRow.Font.Bold = true;
}
}
if (table.TableName == "StateCount")
{
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet1.Cells[3, 3] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet1.get_Range("A3");
formatRange.EntireRow.Font.Bold = true;
}
}
if (table.TableName == "StateList")
{
int ji = 3;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet1.Cells[4, ji] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet1.get_Range("A4");
formatRange.EntireRow.Font.Bold = true;
ji = ji + 1;
}
}
if (table.TableName == "GeoDuplicates")
{
int jh = 5;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet1.Cells[4, jh] = table.Columns[i - 1].ColumnName;
Excel.Range formatRange;
formatRange = excelWorkSheet1.get_Range("A4");
formatRange.EntireRow.Font.Bold = true;
jh = jh + 1;
}
}
if (table.Rows.Count == 0)
{
if (table.TableName == "CountryList")
{
excelWorkSheet1.Cells[6, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet1.Cells[6, 1] = "No Countries are availabel";
}
if (table.TableName == "StateList")
{
excelWorkSheet1.Cells[6, 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet1.Cells[6, 3] = "No states are availabel";
}
if (table.TableName == "GeoDuplicates")
{
excelWorkSheet1.Cells[6, 5].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelWorkSheet1.Cells[6, 5] = "No geoduplicates are availabel";
}
}
if (table.TableName == "CountryList")
{
int l = 5;
excelWorkSheet1.get_Range("A2", "C2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet1.Cells[2, 1] = "Geograpy Country Validation";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet1.Cells[l, k + 1] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet1.Cells[l, k + 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
}
l = l + 1;
}
}
if (table.TableName == "CountryCount")
{
int l = 3;
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet1.Cells[l, 2] = table.Rows[j].ItemArray[k].ToString();
}
l = l + 1;
}
}
if (table.TableName == "StateCount")
{
int l = 3;
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet1.Cells[l, 4] = table.Rows[j].ItemArray[k].ToString();
}
l = l + 1;
}
}
if (table.TableName == "StateList")
{
int lj = 5;
excelWorkSheet1.get_Range("C2", "E2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet1.Cells[2, 3] = "Geography State Validation";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet1.Cells[lj, 3] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet1.Cells[lj, 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RosyBrown);
}
lj = lj + 1;
}
}
if (table.TableName == "GeoDuplicates")
{
int lk = 5;
excelWorkSheet1.get_Range("E2", "H2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet1.Cells[2, 5] = "Geograpy Dimension Duplicates";
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet1.Cells[lk, 5] = table.Rows[j].ItemArray[k].ToString();
excelWorkSheet1.Cells[lk, 5].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Linen);
}
lk = lk + 1;
}
}
}
loopcount = loopcount + 1;
}
xlWorkBook.Sheets["Sheet1"].Delete();
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch (Exception ex)
{
backgroundWorker.ReportProgress(0);
MessageBox.Show(" There was a problem in creating excel file. Please contact the techincal team " + ex.Message);
return;
}
}
backgroundWorker.ReportProgress(2);
}
}
