none
Export data to excel file

    Question

  • I have a lot of records, for example 1 million.
    I have to export them into excel file. First I insert them into dataset and second, I create and save excel file. It works fine with small files. But for large files, I get out of memory exception.
    What is the most optimal way to transfer the data into excel file with c#?

    DataSet exportData = new DataSet();
    using (SqlConnection conn = new SqlConnection("context connection=true")){
     SqlCommand getOutput = new SqlCommand();
     getOutput.CommandText = procName.ToString(); ;
     getOutput.CommandType = CommandType.StoredProcedure;
     getOutput.CommandTimeout = 300;
     getOutput.Connection = conn;
    
     conn.Open();
     SqlDataAdapter da = new SqlDataAdapter(getOutput);
     da.Fill(exportData);
     conn.Close();
     da.Dispose();
    
    //I use closedXML library for excel
     XLWorkbook xlWb = new XLWorkbook();
     xlWb.Worksheets.Add(exportData);
    
    
     xlWb.SaveAs(filePath.ToString());
     releaseObject(xlWb);
    
     exportData.Clear();
     exportData = null;
    }
     public static void releaseObject(object obj){
      try{
         System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
          obj = null;
      }catch{
          obj = null;
      }finally{
           GC.Collect();
      }
     }
    

    Friday, January 25, 2013 11:01 AM

Answers

  • The simplest way to export data to be used by excel is to export it to a comma separated values file, CSV file.

    Basically you just write each record as a line, where each value is surrounded by quotes and delimited by a comma. The quotes are not always needed, but I have found it to be a bit more reliable to always include them. If there is a quote in the value, it can be encoded by making it two quotes.

    The file will look something like this.

    "1","some","values"
    "3","more","data"

    This can be imported into excel.

    Friday, January 25, 2013 11:55 AM
  • Chris, what are you talking about? 255 rows? It's 255 columns and the new versions of Excel (2007 and later) have 1,048,576 rows.  As I know, a CSV file has an unlimited number of rows, just like a Text file.  

    Simon, just export your data to a CSV file, or a Text file.  You can open both files with Excel, but if the number of rows exceeds 1,048,576, you will only see the first 1,048,576 rows.  Again, if you open the file as a CSV, you will see ALL rows, no matter how many there are.

    Check out this link for some ideas:

    http://www.mikesdotnetting.com/Article/58/Exporting-data-to-a-CSV-tab-delimited-or-other-text-format

    If you want some ideas of how you can export your data to Excel, see this:

    http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm


    Ryan Shuell

    Friday, January 25, 2013 4:25 PM
  • private void ExportToExcel(string fileName, DataGridView dgv) //Exports the given dataGridView to Excel with the given fileName
            {
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
    
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                int i = 0;
                int j = 0;
    
                DataTable dtExcelTable = GetDataTableForExcel(dgv);
    
                for (i = 0; i < dtExcelTable.Columns.Count; i++)
                {
                    xlWorkSheet.Cells[1, i + 1] = dtExcelTable.Columns[i].ColumnName;
                }
    
                for (i = 0; i < dtExcelTable.Rows.Count; i++)
                {
                    for (j = 0; j < dtExcelTable.Columns.Count; j++)
                    {
                        xlWorkSheet.Cells[i + 2, j + 1] = dtExcelTable.Rows[i][j];
                    }
                }
    
                try
                {
                    xlWorkBook.SaveAs(fileName + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
    
                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);
    
                    MessageBox.Show("Excel dosyanız C:\\" + fileName + ".xls uzantısında yaratıldı.");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Bir sorun oluştu, tekrar deneyiniz. Hata: " + ex.Message);
                }
    
            }

    Friday, January 25, 2013 4:54 PM

All replies

  • The simplest way to export data to be used by excel is to export it to a comma separated values file, CSV file.

    Basically you just write each record as a line, where each value is surrounded by quotes and delimited by a comma. The quotes are not always needed, but I have found it to be a bit more reliable to always include them. If there is a quote in the value, it can be encoded by making it two quotes.

    The file will look something like this.

    "1","some","values"
    "3","more","data"

    This can be imported into excel.

    Friday, January 25, 2013 11:55 AM
  • Afaik Excel Sheets had a limit of 65536 lines and 255 rows. No idea if that is still valid for XLSX.

    So I would agree with using CSV or maybe an XML format for output. (usually the best way of workign with excel (sheets) in correlation with databases is to not use Excel Sheets at all and live happily without them).

    Friday, January 25, 2013 1:05 PM
  • Chris, what are you talking about? 255 rows? It's 255 columns and the new versions of Excel (2007 and later) have 1,048,576 rows.  As I know, a CSV file has an unlimited number of rows, just like a Text file.  

    Simon, just export your data to a CSV file, or a Text file.  You can open both files with Excel, but if the number of rows exceeds 1,048,576, you will only see the first 1,048,576 rows.  Again, if you open the file as a CSV, you will see ALL rows, no matter how many there are.

    Check out this link for some ideas:

    http://www.mikesdotnetting.com/Article/58/Exporting-data-to-a-CSV-tab-delimited-or-other-text-format

    If you want some ideas of how you can export your data to Excel, see this:

    http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm


    Ryan Shuell

    Friday, January 25, 2013 4:25 PM
  • Chris, what are you talking about? 255 rows? It's 255 columns and the new versions of Excel (2007 and later) have 1,048,576 rows.  As I know, a CSV file has an unlimited number of rows, just like a Text file. 

    Well the op said something of Row counts in the million. So I pointed out that this propably exceeds the limits of a Excel Sheet (.xls) file. Sorry that I mixed up the names for the two things. Please leave my head where it belongs.

    Friday, January 25, 2013 4:33 PM
  • private void ExportToExcel(string fileName, DataGridView dgv) //Exports the given dataGridView to Excel with the given fileName
            {
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
    
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                int i = 0;
                int j = 0;
    
                DataTable dtExcelTable = GetDataTableForExcel(dgv);
    
                for (i = 0; i < dtExcelTable.Columns.Count; i++)
                {
                    xlWorkSheet.Cells[1, i + 1] = dtExcelTable.Columns[i].ColumnName;
                }
    
                for (i = 0; i < dtExcelTable.Rows.Count; i++)
                {
                    for (j = 0; j < dtExcelTable.Columns.Count; j++)
                    {
                        xlWorkSheet.Cells[i + 2, j + 1] = dtExcelTable.Rows[i][j];
                    }
                }
    
                try
                {
                    xlWorkBook.SaveAs(fileName + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
    
                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);
    
                    MessageBox.Show("Excel dosyanız C:\\" + fileName + ".xls uzantısında yaratıldı.");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Bir sorun oluştu, tekrar deneyiniz. Hata: " + ex.Message);
                }
    
            }

    Friday, January 25, 2013 4:54 PM
  • Sorry for the strong tone, Chris.  I'll bet you came here from a SQL Server background, or maybe a C# background.  I came here from an Excel background; I outgrew that and started learning SQL Server and C#.  We met right here, from totally different ends of the spectrum.  These discussion groups are great, aren't they!!  

    Ryan Shuell

    Friday, January 25, 2013 9:14 PM