9 Replies - 30683 Views - Last Post: 20 April 2012 - 12:45 AM Rate Topic: -----

#1 fullyunknown  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 23-March 12

Quicker way to export Data Table to Excel

Posted 15 April 2012 - 04:29 PM

I found some code through googling that allows me to export a data table, that was populated through a query of a couple access tables. However, the code basically opens/creates a excel file and populates each cell one at a time with the data from the data-table. Well with 56K+ lines it takes forever to export. Is there anything else that can be done to speed this up?
I only ask cause of the speed difference in doing it this way and doing it directly in the access program itself. For example, if you go into the access database and run the query, and then export the results with excel, it does it in about 10 seconds compared to the 5+ mins it takes with the code below:
            DataTable dt = new DataTable();
            dt = dsMain.Tables["Bills"];

            Excel.Application excel = new Excel.Application();
            Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
            // Add column headings...
            int iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                excel.Cells[1, iCol] = c.ColumnName;
            }
            // for each row of data...
            int iRow = 0;
            foreach (DataRow r in dt.Rows)
            {
                iRow++;
                // add each row's cell data...
                iCol = 0;
                foreach (DataColumn c in dt.Columns)
                {
                    iCol++;
                    excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                }
            }
            // Global missing reference for objects we are not defining...
            object missing = System.Reflection.Missing.Value;
            // If wanting to Save the workbook...
            workbook.SaveAs("MyExcelWorkBook.xlsx",
                Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
                false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                missing, missing, missing, missing, missing);
            // If wanting to make Excel visible and activate the worksheet...
            excel.Visible = true;
            Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
            ((Excel._Worksheet)worksheet).Activate();
            // If wanting excel to shutdown...
            
            ((Excel._Application)excel).Quit();
            MessageBox.Show("Completed!", "Export");



Is This A Good Question/Topic? 0
  • +

Replies To: Quicker way to export Data Table to Excel

#2 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1252
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: Quicker way to export Data Table to Excel

Posted 15 April 2012 - 04:42 PM

Unless you really really need an Excel file, just export a CSV file and you can open that in Excel easy peasy.

You still have the normal sorting, etc functions that Excels brings to the table on a CSV file.

As an example, here's how I build CSV report for a web application. Fine tune it for your desktop app if necessary:

            EfProductoRepository repo = new EfProductoRepository();
            var productos = repo.BuscarProductos();

            StringBuilder csvExport = new StringBuilder();
            //csvExport.AppendLine(Resources.CSVHeader);
            csvExport.AppendLine("Codigo,Categoria,Nombre,Titulo,PrecioA,PrecioB,PrecioC,Fecha de Creacion,Estado");

            foreach (var producto in productos)
            {
                string fech = "";
                if (producto.FechaDeCreacion.HasValue)
                    fech = producto.FechaDeCreacion.Value.ToString();
                else
                    fech = DateTime.Now.ToString();

                csvExport.AppendLine(string.Format(
                    "\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\", \"{8}\"",
                    producto.ProductoId,
                    producto.Categoria.Nombre,
                    producto.Nombre,
                    producto.Titulo,
                    producto.PrecioCatUno,
                    producto.PrecioCatDos,
                    producto.PrecioCatTres,
                    fech,
                    producto.Estado));
            }

            byte[] data = new ASCIIEncoding().GetBytes(csvExport.ToString());

            HttpContext.Response.Clear();
            HttpContext.Response.ContentType = "APPLICATION/OCTET-STREAM";
            HttpContext.Response.AppendHeader("Content-Disposition", "attachment; filename=Reporte-Productos.csv");
            HttpContext.Response.OutputStream.Write(data, 0, data.Length);
            HttpContext.Response.End();

            return View();

This post has been edited by Sergio Tapia: 15 April 2012 - 04:44 PM

Was This Post Helpful? 1
  • +
  • -

#3 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Quicker way to export Data Table to Excel

Posted 15 April 2012 - 05:15 PM

Well, populating cell by cell is a very slow process. I had the same problem and I found out a very easy and elegant solution to this problem:here. All you have to do is put your data into a bidimensional array and assign it to a range in excel file. Details in the link.
Was This Post Helpful? 0
  • +
  • -

#4 fullyunknown  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 23-March 12

Re: Quicker way to export Data Table to Excel

Posted 15 April 2012 - 07:53 PM

Thank you. I went with the CSV export way. Just now having issue with trying to figure out how to make it export the Memo field through this method. As the memo field being greater then 255 characters is causing issues with the export, in the sense that data after memo field no longer falls in line with the column headers anymore.
Was This Post Helpful? 0
  • +
  • -

#5 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1252
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: Quicker way to export Data Table to Excel

Posted 15 April 2012 - 08:07 PM

I don't think it has to do with the size of the "data" but more with your data having a ',' somewhere in there. :)

Use a breakpoint and triple-check that the "Memo" field doesn't have a comma in there somewhere.

If I am correct, let us know and we'll see what we can cook up!
Was This Post Helpful? 0
  • +
  • -

#6 fullyunknown  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 23-March 12

Re: Quicker way to export Data Table to Excel

Posted 15 April 2012 - 08:25 PM

Ahhhh, ok. Did not think of that. Well looked into the export file and compared it to the table at the points where it was messing up the export. It does look like some of the issue is the fact that the data does have a comma in it. However, i have examples where there is no comma in the memo field and durning export as CSV, once it got to memo field(rich text), it printed some of it in the proper column then it jumped down to rows and printed the rest of data. Once done moved on to the next row in table. Example, of data below..(i assuming the rich text </div> is what is causing the issue also, but confusing is the second </div> does not force the next set of data to another line)



EXCEL FILE
--------------
Row 1) <div>File Name: <font face=Calibri>D09DMD2MPDW002</font></div>

Row 3) <div><font face=Calibri>some more data typed here. then even more. and finally the last bit of data.</font></div> (the rest of the data for this row gets entered)
Row 4) insert new row from table on this row

Hopefully that made some kind of sense. :dontgetit:
Was This Post Helpful? 0
  • +
  • -

#7 fullyunknown  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 23-March 12

Re: Quicker way to export Data Table to Excel

Posted 16 April 2012 - 09:51 AM

Ok, it just dawned on me why data is moving to new rows. forgot about the carriage returns stored in the memo field also. guess i just need to figure out a way to strip those and then strip the extra commas out of the memo field when trying to export.
Was This Post Helpful? 0
  • +
  • -

#8 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1252
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: Quicker way to export Data Table to Excel

Posted 16 April 2012 - 10:42 AM

I suggest creating a helper method:

public string SanitizeInput(string input)
{
    List<string> invalidItems = new List<string>()
                                    {
                                        ",",
                                        "/r" //add more as needed.
                                    } 
    
    foreach (invalidItem in invalidItems)
    {
        input = input.Replace(invalidItem, ""); //Remove the bad char. Or do something else.
    }
}



Then just call the method:

SanitizeInput(memoField);

Was This Post Helpful? 1
  • +
  • -

#9 fullyunknown  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 23-March 12

Re: Quicker way to export Data Table to Excel

Posted 16 April 2012 - 11:27 AM

View PostSergio Tapia, on 16 April 2012 - 01:42 PM, said:

I suggest creating a helper method:

public string SanitizeInput(string input)
{
    List<string> invalidItems = new List<string>()
                                    {
                                        ",",
                                        "/r" //add more as needed.
                                    } 
    
    foreach (invalidItem in invalidItems)
    {
        input = input.Replace(invalidItem, ""); //Remove the bad char. Or do something else.
    }
}



Then just call the method:

SanitizeInput(memoField);



THANK YOU. Great idea. Just for anyone though that finds this form and needs help for same issue, i had to make two changes to the code above. Code below and commented.

public string SanitizeInput(string input)
{
    List<string> invalidItems = new List<string>()
                                    {
                                        ",",
                                        "/r",
                                        System.Environment.NewLine 
                                    }; // had to add the ; here
    
    foreach (string invalidItem in invalidItems) // had to add "string" in front of invalidItem
    {
        input = input.Replace(invalidItem, ""); 
    }
}

Was This Post Helpful? 0
  • +
  • -

#10 doc_guru  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-March 12

Re: Quicker way to export Data Table to Excel

Posted 20 April 2012 - 12:45 AM

View Postfullyunknown, on 15 April 2012 - 04:29 PM, said:

I found some code through googling that allows me to export a data table, that was populated through a query of a couple access tables. However, the code basically opens/creates a excel file and populates each cell one at a time with the data from the data-table. Well with 56K+ lines it takes forever to export. Is there anything else that can be done to speed this up?
I only ask cause of the speed difference in doing it this way and doing it directly in the access program itself. For example, if you go into the access database and run the query, and then export the results with excel, it does it in about 10 seconds compared to the 5+ mins it takes with the code below:
            DataTable dt = new DataTable();
            dt = dsMain.Tables["Bills"];

            Excel.Application excel = new Excel.Application();
            Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
            // Add column headings...
            int iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                excel.Cells[1, iCol] = c.ColumnName;
            }
            // for each row of data...
            int iRow = 0;
            foreach (DataRow r in dt.Rows)
            {
                iRow++;
                // add each row's cell data...
                iCol = 0;
                foreach (DataColumn c in dt.Columns)
                {
                    iCol++;
                    excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                }
            }
            // Global missing reference for objects we are not defining...
            object missing = System.Reflection.Missing.Value;
            // If wanting to Save the workbook...
            workbook.SaveAs("MyExcelWorkBook.xlsx",
                Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
                false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                missing, missing, missing, missing, missing);
            // If wanting to make Excel visible and activate the worksheet...
            excel.Visible = true;
            Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
            ((Excel._Worksheet)worksheet).Activate();
            // If wanting excel to shutdown...
            
            ((Excel._Application)excel).Quit();
            MessageBox.Show("Completed!", "Export");



Hey I find an artiles on Google Blogspot. It says Best Solution to Export Datatable to Excel have a check.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1