Copy DataTable into Excel Very Quickly With NoColumnHeader using SpreadSheetGear

Question

i am having a table that contains 30,000 Rows and around 50 Columns, i have to copy all this data into Excel Sheet (.xlsm) without Column Header, but my current approach is taking quite long time.Here is my code, can anyone please tell me how to improve it to populate data into excel quickly or any other approach

    string Sqlcmd;
    Sqlcmd = "select Number ,Name1, Name 2 from MyTempData";
    SqlCommand command = new SqlCommand(Sqlcmd, con);
    SqlDataAdapter a = new SqlDataAdapter(command);
    DataTable datatable = new DataTable();
    a.Fill(datatable);
    con.Close();
    string filename = "E:\\shared\\TotalInformation.xlsm";
    SpreadsheetGear.IWorkbook Myworkbook = SpreadsheetGear.Factory.GetWorkbook(filename);
    SpreadsheetGear.IWorksheet partnerdataSheet = Myworkbook.Worksheets["Total Information"];
    SpreadsheetGear.IRange partnerNumberCheckRange = Myworkbook.Names["rngCopyRow"].RefersToRange;
     partnerNumberCheckRange.CopyFromDataTable(datatable, SetDataFlags.InsertCells | SetDataFlags.NoColumnHeaders);    
     Myworkbook.SaveAs("D:\\Both.xlsm", SpreadsheetGear.FileFormat.OpenXMLWorkbookMacroEnabled);

Would be better in [CodeReview.SE]? – Soner Gönül Nov 12 '14 at 07:22 — Soner Gönül, Nov 12 '14 at 07:22

score 1 · Answer 1 · answered Nov 12 '14 at 19:23

I have had a similar experience with large Excel files. I haven't quantified this in terms of the number of cells in the spreadsheet but in terms of file size (roughly) < 1 MB file size = a few seconds, 5 - 10 MB = 10 - 15 seconds, 15 - 20 MB = 30 - 60 seconds.

SpreadhsheetGear will have an answer to this but from what I can see the time taken relates to the time to load the spreadsheet file as an instance rather than the specific read / write time for particular actions. In your case, given the size of the table, it could well be the time to load in the data.

My suggestion is that you test various table / file sizes to determine the relationship between size and speed for your particular data type.

The alternative approaches are to output the table as a csv file using a csv read/write component, or as pure XML using an XML file writer. Both of these formats can be read into a standard Excel file.

True but I am not aware of a standard feature in Excel to import JSON. There is however a discussion on this option here: http://stackoverflow.com/questions/8044423/json-import-to-excel — Xcheque, Nov 13 '14 at 20:41

Copy DataTable into Excel Very Quickly With NoColumnHeader using SpreadSheetGear

1 Answers1