Is there a way of writing large datatable in an excel file more efficiently or faster?

Question

I'm quite new in handling huge data sets and I'm using C# for this. Now, the data that I'm handling (which is a CSV) has a column of 19 and row of 9,831. When it comes to writing the data into an existing excel file the program take 6 minutes to accomplish its task. I'm looking for suggestions or tips that can reduce the time rendering into seconds. So here's my class or code for writing it to an excel file:

using System;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

namespace Project
{
    class WriteCsv
    {
        public WriteCsv(DataTable dt)
        {
            //sets the existing excel file to be written
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open(@"path to excel file");
            Microsoft.Office.Interop.Excel.Worksheet x = excel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;

            //selects a specific worksheet to written on
            x = (Excel.Worksheet)sheet.Sheets[2];

            int rowCount = 1; 
            int dataColumns = dt.Columns.Count;

            //this is where the writing starts
            foreach (DataRow dr in dt.Rows)
            {
                int columnCount = 0;
                while (columnCount < dataColumns)
                {
                    x.Cells[rowCount, columnCount + 1] = dr[columnCount];
                    columnCount++;
                }
                Console.WriteLine("=====================ROW COMPLETED " + rowCount + "========================");
                rowCount++;

            }
            sheet.Close(true, Type.Missing, Type.Missing);
            excel.Quit();
        }
    }
}

@BaileyKocin OP is looping through each field in their `datarow` and writing it to the corresponding cell. Cell by painful cell. — JNevill, Nov 18 '18 at 12:32
It's for putting the data in specific cell of the excel file and for indicating what data should be written in that cell for the data row. — R. Suarez, Nov 18 '18 at 12:34
@BaileyKocin Perhaps that's the help that OP is looking for? — JNevill, Nov 18 '18 at 12:37
Well, honestly I'm just a newbie so I go for the helpful codes that I can understand. So, any suggestion or changes that I can make? — R. Suarez, Nov 18 '18 at 12:42

score 1 · Accepted Answer · answered Nov 18 '18 at 12:54

I've dealt with this a few ways in the past. Especially when consuming a DataReader from a SQL source which is always a few hops, skips, and jumps from playing nice and fast with Excel.

Excel really likes 2-dim arrays though. What I've done with the DataTable in the past is converted it to a 2-dim array and then just dump that array into the spreadsheet all at once. You are still iterating through every row/column in the DataTable, but C# is fast about that.

        string[,] data = new string[dt.Rows.Count, dt.Columns.Count];
        int i = 0;
        foreach (DataRow row in dt.Rows)
        {
            int j = 0;
            foreach (DataColumn col in dt.Columns)
            {
                data[i,j++] = row[col].ToString();
            }
            i++;
        }

        //dump the whole array to the range
        x.Value = data

There's some other crafty ways of doing this by writing to a file with comma delimited rows (although I would use tab to make it more excel friendly), then opening the file, but that seems even more cumbersome. Check out some interesting answers here

You may also have some luck converting that datatable to an array using Linq, although I haven't tried yet.

Perhaps something like:

x.Value = dt.AsEnumerable().Select(row => row.ItemArray).ToArray()

I'm not convinced that's 100%, but it may be a step in the right direction.

Is there a way of writing large datatable in an excel file more efficiently or faster?

1 Answers1