none
Export large amount of data from datatable to Excel RRS feed

  • Question

  • Hi.

    I want to export data from Oracle table to Excel.

    The excel should have multiple sheets and should prompt the user to save the excel file. I tried the below code , but the code is too slow. I cannot use any third party tool.

    Is there any other way to pass data to excel in a faster way?

    Excel.Application oXL = new Excel.Application();
    Excel.Workbook oWB = oXL.Workbooks.Add(missing);
    Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
                 
    using (OracleConnection con = new OracleConnection(oradb))
                    {
        con.Open();
    
     test2(oSheet, "select * from table1", "Names", con);
                      
    Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing) as Excel.Worksheet;
      test2(oSheet2, "select * from table2", "Address", con);
    
    Excel.Worksheet oSheet3 = oWB.Sheets.Add(missing, missing, 2, missing) as Excel.Worksheet;
    test2(oSheet3, "select * from table3", "All Users", con);
    
    }
    
     public static void test2(Excel.Worksheet oSheet, string sql, string name, OracleConnection con)
            {
     OracleDataAdapter da = new OracleDataAdapter(sql, con);
     DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt == null || dt.Columns.Count == 0)
                {
                }
                else
                {
     oSheet.Name = name;
                    
     for (var i = 0; i < dt.Columns.Count; i++)
     {
      oSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
     }
      for (var i = 0; i < dt.Rows.Count; i++)
      {
     for (var j = 0; j < dt.Columns.Count; j++)
       {
     oSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
        }
       }
      }
     }

    Is there any other better way to export large amount of data to excel ? How to save the file ?

    Thanks

    Thursday, June 6, 2019 9:42 AM

All replies

  • Hello,

    Install SpreadSheetLight, a free library using NuGet Package manager in Visual Studio. On the libraries home page there is a help file if you so desire as this libraries is rather large in functionality but if all you need is to import DataTable objects all you need is below.

    Code which writes a DataTable to Excel.

    /// <summary>
    /// Import a DataTable into a .xlsx file to a specific sheet starting at a specific cell address
    /// </summary>
    /// <param name="pFileName">Path and file name</param>
    /// <param name="pSheetName">Sheet name to import data into</param>
    /// <param name="pStartReference">cell reference to start import e.g. A1</param>
    /// <param name="pDataTable">DataTabe to import from</param>
    /// <param name="pIncludeHeaders">true to include column name, false to exclude column names</param>
    public void ImportDataTable(string pFileName, string pSheetName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders  = true)
    {
    	using (SLDocument doc = new SLDocument(pFileName, pSheetName))
    	{
    		doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);
    		doc.Save();
    	}
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, June 6, 2019 10:05 AM
    Moderator
  • Hi Venkatzeus,

    Thank you for posting here.

    Based on your description, you want to export data form datatable to excel.

    You could use closedxml, then I make a code for your reference.

    Code:

    [Obsolete]
            static void Main(string[] args)
            {
                string sql1 = "select * from table1";
                string sql2 = "select * from table2";
                string sql3 = "select * from table3";
                string filename = @"D:\test.xlsx";
                var workbook = new XLWorkbook();
                DataTable table1 = returntable(oradb, sql1);
                DataTable table2 = returntable(oradb, sql2);
                DataTable table3 = returntable(oradb, sql3);
    
                workbook.Worksheets.Add(table1, "sheet1");
                workbook.Worksheets.Add(table2, "sheet2");
                workbook.Worksheets.Add(table3, "sheet3");
    
                workbook.SaveAs(filename);
    
    
    
    
            }
    
            [Obsolete]
            static DataTable returntable(string oradb,string sql)
            {
                using (OracleConnection con = new OracleConnection(oradb))
                {
                    con.Open();
                    OracleDataAdapter da = new OracleDataAdapter(sql, con);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }
            }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 7, 2019 1:56 AM
    Moderator
  •  I cannot use any third party tool.

    Is there any other way to pass data to excel in a faster way?

    Unfortunately the previous answers are both using third party tools, which is precisely what you don't want to do.

    The reason why your code is slow is that you are passing the values of the cells one by one, so each of these cell assignments involves a new COM call into Excel. The time for the calls add up, so the whole lot is quite slow.

    One way to speed it up is to store the values for a whole block of cells into an array and then send the array to Excel in a single call. This is much faster than passing the elements one by one in a loop. Sorry, I don't have any example on how to do this, but it shouldn't be too difficult to find examples around the 'net.

    Alternatively, you could give up COM automation and use an alternative way to access the Excel file. One way is to use the OleDb driver for Excel, which treats the spreadsheet as a database table. You can do Inserts to send whole rows into the table, and this is faster than the COM option. Alternatively, you could use OpenXml to create the xlsx file. This does require a library, but it is not "tihird-party", it is provided by Microsoft. The learning curve for OpenXml is steep, but once you learn how to do it, it can write large amounts of data quite fast.

    Friday, June 7, 2019 4:01 PM
    Moderator
  • I agree with Alberto. Here is the way I do it:

    public ExcelCell PutArrayValuesStartingHere(object[,] values, Worksheet sheet) { ExcelCell lastCell = this; if (values.Length > 0) { lastCell = this.Shift(values.GetLength(0) - 1, values.GetLength(1) - 1); sheet.get_Range(this.CellLocation, lastCell.CellLocation).set_Value(XlRangeValueDataType.xlRangeValueDefault, Excel.Utilities.CleanValues(values)); } return lastCell; }

    where Excel.Utilities.CleanValues keeps data types Excel knows about as they are converts other things (most a class I defined which Excel balks at) into strings

    this is much faster than putting in values a single cell at a time.

    Ethan


    Ethan Strauss

    Friday, June 7, 2019 9:34 PM
  • Hi

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,
    Jack



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 18, 2019 7:22 AM
    Moderator
  • Hi.

    Thanks for the reply. But the issue is not resolved.

    I am bringing in lot of data to be pushed to excel, and getting out of Memory exception.

    I currently have 30+ data tables each containing 10000 rows, 255 columns each. The requirement is to save this data from each data table as separate sheet within the same excel.

    I have tried with Open XML DOM model, ClosedXML, string writer. But all giving the same issue.

    Thanks

    Thursday, July 11, 2019 7:02 AM
  • Hi Venkatzeus,

    Thanks for the feedback.

    I want to know what error you get when you used the method that I provided. If you provide the exception information, we will solve your problem better.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 11, 2019 7:10 AM
    Moderator
  • Hi Venkatzeus,

    Thanks for the feedback.

    I want to confirm if you look at the method that I provided again. If not, please try it. If so, please tell me what error you get.

    The following code works for me.

    [Obsolete]
            static void Main(string[] args)
            {
                string sql1 = "select * from table1";
                string sql2 = "select * from table2";
                string sql3 = "select * from table3";
                string filename = @"D:\test.xlsx";
                var workbook = new XLWorkbook();
                DataTable table1 = returntable(oradb, sql1);
                DataTable table2 = returntable(oradb, sql2);
                DataTable table3 = returntable(oradb, sql3);
    
                workbook.Worksheets.Add(table1, "sheet1");
                workbook.Worksheets.Add(table2, "sheet2");
                workbook.Worksheets.Add(table3, "sheet3");
    
                workbook.SaveAs(filename);
    
    
    
    
            }
    
            [Obsolete]
            static DataTable returntable(string oradb,string sql)
            {
                using (OracleConnection con = new OracleConnection(oradb))
                {
                    con.Open();
                    OracleDataAdapter da = new OracleDataAdapter(sql, con);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }
            }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, July 11, 2019 8:10 AM
    Moderator
  • Hi Jack.

    I had tried your code , but still getting out of memory exception. Please find the below code, as per your suggestion:

    XLWorkbook wb = new XLWorkbook();
    DataSet ds = new DataSet();
    string destpath = @"C:\test.xlsx";
    
    try
      {
        ds = GetDatafromDatabase();
    
        int TableCount = ds.Tables.Count;
        for (int i = 0; i < TableCount; i++)
        {
           DataTable dt = ds.Tables[i];
           wb.Worksheets.Add(dt);
           dt.Dispose(); 
        }
    ds.Dispose();
    wb.SaveAs(destpath);
    }
     catch (Exception ex)
     {
       throw ex;
     }

    The dataTable is of 10,000 rows, 255 columns each.

    Thanks

    Tuesday, July 16, 2019 12:46 PM
  • Hi.

    Any help on this ?

    Thanks

    Wednesday, July 24, 2019 3:18 AM
  • Hi Venkatzeus,

    Thanks for the feedback.

    Since this thread has been here for a long time, I suggest that you could create a new thread in c# forum.

    Besides, I remembered that I could solve your problem successfully by using my code.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 24, 2019 7:35 AM
    Moderator
  • Hi Jack.

    I had tried the same code as you had provided. But was still getting the same error.

    I have opened a new thread here - https://social.msdn.microsoft.com/Forums/en-US/8c07ddad-38b5-493a-9a77-f460bec8db2a/populate-large-data-to-execl?forum=csharpgeneral

    This thread post can be closed. Thanks for the help

    Friday, July 26, 2019 10:03 AM