Excel List> per each row

Question

I have a small program where you can select some database tables and create a excel file with all values for each table and thats my solution to create the excel file.

foreach (var selectedDatabase in this.lstSourceDatabaseTables.SelectedItems)
{
    //creates a new worksheet foreach selected table
    foreach (TableRetrieverItem databaseTable in tableItems.FindAll(e => e.TableName.Equals(selectedDatabase)))
    {
        _xlWorksheet = (Excel.Worksheet) xlApp.Worksheets.Add();
        _xlWorksheet.Name = databaseTable.TableName.Length > 31 ? databaseTable.TableName.Substring(0, 31): databaseTable.TableName;

        _xlWorksheet.Cells[1, 1] = string.Format("{0}.{1}", databaseTable.TableOwner,databaseTable.TableName);
        ColumnRetriever retrieveColumn = new ColumnRetriever(SourceConnectionString);
        IEnumerable<ColumnRetrieverItem> dbColumns = retrieveColumn.RetrieveColumns(databaseTable.TableName);
        var results = retrieveColumn.GetValues(databaseTable.TableName);
        int i = 1;

        (result is a result.Item3 is a List<List<string>> which contains all values from a table and for each row is a new list inserted)
        for (int j = 0; j < results.Item3.Count(); j++)
        {
            int tmp = 1;
            foreach (var value in results.Item3[j])
            {
                _xlWorksheet.Cells[j + 3, tmp] = value;
                tmp++;
            }
        }
    }
}

It works but when you have a table with 5.000 or more values it will take such a long time.

Does someone maybe know a better solution to add the List List string per row than my for foreach solution ?

Are you able to use EPPlus? That library is fast and you can load a DataTable using using (ExcelPackage pck = new ExcelPackage(newFile)) { ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts"); ws.Cells["A1"].LoadFromDataTable(dataTable, true); pck.Save(); } — Mike Miller, Sep 09 '16 at 09:06
Totally agree with @MikeMiller [EPPlus](https://www.nuget.org/packages/EPPlus/) is better way to go in this situation. — Michael, Sep 09 '16 at 09:15
Add the moment i'm not able to use EPPlus but maybe I can take a look to this project — kb_, Sep 09 '16 at 09:15
How about pre-compute all data in memory and then update the excel cells in one go? [Also see this answer](http://stackoverflow.com/questions/536636/write-array-to-excel-range) — grek40, Sep 09 '16 at 09:19
Do you need to write an application to do this? Have you considered using a database-level technology such as DTS in SQL? — Daniel Minnaar, Sep 09 '16 at 09:19

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

I utilize the GetExcelColumnName function in my code sample to convert from column count to the excel column name.

The whole idea is, that it's very slow to write excel cells one by one. So instead precompute the whole table of values and then assign the result in a single operation. In order to assign values to a two dimensional range, use a two dimensional array of values:

var rows = results.Item3.Count;
var cols = results.Item3.Max(x => x.Count);
object[,] values = new object[rows, cols];
// TODO: initialize values from results content
// get the appropriate range
Range range = w.Range["A3", GetExcelColumnName(cols) + (rows + 2)];
// assign all values at once
range.Value = values;

Maybe you need to change some details about the used index ranges - can't test my code right now.

edited May 23 '17 at 12:15

Community

1
1

answered Sep 09 '16 at 09:47

grek40

13,113
1
24
50

short question to your solution is it possible to do the same for the numberformat ? It works nice but when i add the NumberFormat its slow ( xlWorksheet.Cells[j + 3, tmpNbr].NumberFormat = ExcelColumnTypes.ConvertToExcelTypes(results.Item2[tmpNbr - 1]);) – kb_ Oct 04 '16 at 12:55
1

@kb_ would have to try that... why don't you just try it yourself? – grek40 Oct 04 '16 at 12:56
I can make a new question, I've no idea how it should work – kb_ Oct 04 '16 at 12:57
1

@kb_ it should work exactly the same way as with the `Value` property. Just create an object array of the same size as the range that you want to fill, then initialize the format values and call `range.NumberFormat = formatValueArray;` – grek40 Oct 04 '16 at 13:12
I've tryed it, it work, but when the table has more than 200.000 entrys it will fail but thats another problem i think :/ – kb_ Oct 04 '16 at 13:23

score 1 · Answer 2 · answered Sep 09 '16 at 09:48

As I see, youd didn't do profiling. I recomend to do profiling first (for example dotTrace) and see what parts of your code actualy causing performance issues. In my practice there is rare cases (almost no such cases) when code executes slower than database requests, even if code is realy awfull in algorithmic terms.

First, I recomend to fill up your excel not by columns, but by rows. If your table has many columns this will cause multiple round trips to database - it is great impact to performance.

Second, write to excel in batches - by rows. Think of excel files as mini-databases, with same 'batch is faster than one by one' principles.

Excel List> per each row

2 Answers2