Fastest way of exporting data from C# indexer(containing x,y values) to excel

Question

What is the fastest way of exporting data from C# indexer to excel?

Exporting cell values using for loop as below, is too slow.

Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            var ws = (Worksheet)wb.Worksheets[1];    
for (int y = 0; y < Y; y++)
                {
                    for (int x = 0; x < X; x++)
                    {
                        ws.Cells[y + 1, x + 1].Value2 = (double)Pixels[y, x];
                    }
                }

In above code, Pixels[y,x] is an indexer.

Exporting it to a range as in @Pilgerstorfer Franz answer Writing a large 2d array to Excel is faster.

I couldn't convert indexer to array (2d array or jagged array) or make below work with indexer.

var writeRange = ws.Range[startCell, endCell];
    writeRange.Value = myArray;

@barrick I am getting the file from a camera not a database & I have to read pixel values in excel. — user2330678, Aug 20 '14 at 23:31
They are alternative ways of writing to Excel rather than using the PIAs. Which version of Excel are you writing to, or is it unknown? — dyson, Aug 20 '14 at 23:35
does this have to be in Excel. images are huge and exporting the data to a csv then just opening it like normal would be a lot faster — RadioSpace, Aug 20 '14 at 23:55
@barrack I am testing on Excel 2010 but it would be Excel 2013 in real-time. — user2330678, Aug 21 '14 at 00:16

score 0 · Answer 1 · answered Aug 21 '14 at 00:36

0

CSV it is however I could not test this as I don't have access to your X,Y and Pixels

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace ConsoleApplication1
{
class Program
{
    static void Main(string[] args)
    {
        using (FileStream fs = File.Create("imagedata.csv"))//or whatever here. I would use a save file dialog
        using(StreamWriter sw = new StreamWriter(fs))
        {
            sw.Write("column headers");// if so desired 

            for (int y = 0; y < Y; y++)
            {

                for (int x = 0; x < X; x++)
                {
                    if(x == x-1)//last element in row
                    {sw.Write( (double)Pixels[y, x] );}
                    else
                    {sw.Write( (double)Pixels[y, x] + "\t" );} // use a tab delimmiter to avoid collision with commas and decimals Excel can handle this just fine
                }

                sw.WriteLine(); // newline is next row
            }  
        }


    }
}
}

answered Aug 21 '14 at 00:36

RadioSpace

953
7
15

Is it possible to export to CSV without using for loops? Like with Excel it would be worksheet.Range[startCell, endCell]; with arrays. – user2330678 Aug 21 '14 at 00:41
anything would have to loop even foreach and linQ loop and calls to `.Count()`. for loops are not slow the Excel Api is. the only way to speed things up would be to use SharpDX or the like but I am not sure how to output text from a graphics card. with images you are looping millions of times and that is just slow by nature. what kind of resolution are you talking here? – RadioSpace Aug 21 '14 at 00:47
So maybe excel would be better. I want to try something like ws.Range[startCell, endCell].Value2= Pixel[y,x]; where Pixel[y,x] is an indexer. – user2330678 Aug 21 '14 at 01:04
use `System.Diagnostics.Stopwatch` to see where all your time is going. if Excel is fast. I am setup to develop for Excel and own Excel 2013 perhaps you could throw a solution up on github I could tinker with to try and speed things up. but you area working with images on the CPU this is never fast even for small images – RadioSpace Aug 21 '14 at 01:10
I have posted the code below. I didn't use System.Diagnostics.Stopwatch but it was notably faster than cell-by-cell export to excel. Now, export took less than 1 minute for 640*480 cells. And I don't have a github account. If you need more code, let me know. I will try .netfiddle. BTW please try System.Diagnostics.Stopwatch & do let me know the results. – user2330678 Aug 21 '14 at 17:55

score 0 · Answer 2 · answered Aug 21 '14 at 08:38

The question is whether you really need to insert data directly into an Excel worksheet using automation, or you can create a document and then get Excel to load it.

In the first case you will have (at some level) a piece of VBA automation code that refers to worksheets, rows and cells, much like the sample you provided. In my experience driving Excel using VBA at the cell level, or even at some slightly higher level, is seriously slow. It is not a practical solution.

The alternative is to create an Excel document and then ask Excel to load it. The document can be a CSV file, or it can be an actual Excel spreadsheet created by a third party library. In either case the document can be written fast, and it only takes a few lines of VBA code to load the document into Excel. The result is fast, and highly practical.

If you want the second, just search for "excel library c#". There are also a few on NuGet. I won't recommend one, because it depends on your needs.

score 0 · Answer 3 · answered Aug 21 '14 at 17:51

I ended up copying the indexer into a 2D array & range export to excel.

var startCell = ws.Cells[1, 1];
        int row = Y, col = X;

        var endCell = ws.Cells[row, col];

        // access range by Property and cells indicating start and end 

        var writeRange = ws.Range[startCell, endCell];
        var _pixels = new double[Y, X];

        for (int y = 0; y < Y; y++)
        {

            for (int x = 0; x < X; x++)
            {

                _pixels[y, x] = (double)Pixels[y, x];

            }

        }

        writeRange.Value = _pixels;

Fastest way of exporting data from C# indexer(containing x,y values) to excel

3 Answers3