Fill string Array to Excel

[Mschauder]

Hey guys, I want to fill my array into a .xls or .xlsx file... momentary I fill it with 2 loops from my grid. I read something about the ".Range" property from Excel, but how can I handle it? :confused: or is there a possibility to import 120.000 rows & 20 cols much faster? hope you can help me! thanks previously

modified on Friday, March 19, 2010 7:46 AM

[Dave Kreskowiak]

Depending on which version of Excel you're using, you can easily run the machine out of RAM doing this. Also, there are versions of Excel that cannot hold 120,000 rows in a single sheet. You have to populate each cell with its appropriate value from your dataset. There is no "bulk" way to do it. Your only other option, instead of filling in a Worksheet object directly, is to write your data out to a .CSV file and open that in Excel.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008
But no longer in 2009...

[Mschauder]

Hey >> Also, there are versions of Excel that cannot hold 120,000 rows in a single sheet. Office Excel 2007 can hold 1.048.576 rows. >> Your only other option, instead of filling in a Worksheet object directly, is to write your data out to a .CSV file and open that in Excel. Should have a .xls or .xlsx format and should can autosize cols and edit backcolor from the header. >> You have to populate each cell with its appropriate value from your dataset. There must be an another way... ...fill it with

for (int i = 0; i < UsingGrid.Cols; i++)
{
for (int j = 0; j < UsingGrid.Rows; j++)
{
worksheet.Cells[j + 1, i + 1] = ConvertToExcelFormat(Convert.ToString(UsingGrid[j, i]));
}
}

costs amount of time (up to 10 minutes)...

modified on Monday, March 22, 2010 3:56 AM

[Dave Kreskowiak]

Mschauder wrote:

Office Excel 2007 can hold 1.048.576 rows.

Which not everyone has... But then again, you haven't said anything about your client base, so I'm, assuming you have to support more than a "corporate standard load".

Mschauder wrote:

Should have a .xls or .xlsx format and should can autosize cols and edit backcolor from the header

You cannot write an .XLS file without using the Excel object model or a third party control, filing in all the cells, formatting them, then saving it. An .XLSX file is just an XML file, so there's a bit of work there to get the data in the correct format surrounded by the required XML Excel wants to see. Not every Excel can open an .XLSX file either. Again, it depends on your clients...

Mschauder wrote:

There must be an another way...

You've got your options. There is no "bulk insert" option for Excel.

Mschauder wrote:

//Application.DoEvents();

I wish DoEvents was never allowed to be created...

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008
But no longer in 2009...

[Dan Mos]

Cheack out this article or this one. It is fast kind of bulk. Another totally diferent option is to use OLEDB.

[Lost User]

Hi, you could do it faster with this Excel .NET component. It uses its own parsing engine written purely in managed environment, so no cross platform invocations, no COM wrappers, no Excel Automation. Here is a sample Excel C# code how to export DataSet to Excel within just one for loop:

// Create new ExcelFile.
var ef = new ExcelFile();

// Imports all the tables from DataSet to new file.
foreach (DataTable dataTable in dataSet.Tables)
{
// Add new worksheet to the file.
var ws = ef.Worksheets.Add(dataTable.TableName);

// Insert the data from DataTable to the worksheet starting at cell "A1".
ws.InsertDataTable(dataTable, "A1", true);

}

// Save the file to XLS format.
ef.SaveXls("DataSet.xls");