Writing Dataset to Excel increase performance

I am writing dataset data to excel by reading from dataset and writing to excel as DirectCast(ws.Cells(row, column), Range).Value2 = item

It is writing, But I have few formatting like borders for each cell and i am applyin开发者_运维知识库g as DirectCast(ws.Cells(row, col), Range).Borders(XlBordersIndex.xlEdgeLeft).Weight = 2

This works faster for 100 records and if i increase the records it takes lot of time.

Problem : In real time data there may be 2 lakhs records. Then how to increase the

performance to write fast that time.

Thank you Ramesh.T.

First, you probably want to fill in your data with an ADODB recordset. That is the fastest way to fill in data on an Excel sheet that I have found.

Example: ws.Range("A2").CopyFromRecordset(rs)

Get all the columns you need into the recordset and put it all in at once.

If you start on cell A2, then you can fill your headers in afterwards.

You can do your formatting all at once on a whole range of cells, after you have put the data in:

Example: ws.UsedRange.Cells.Borders(xlBordersIndex.xlEdgeLeft).Weight = 2

Try applying the border effects after you finish writing all the lines.

What I mean is apply the border effect on a range of cells instead of on a cell by cell basis.

If you are a .NET developer, another approach might be to take advantage of the open xml extensions used by office (assuming you are using a version of office that supports this -2007 or later I think)

Here's a simplistic view of the approach.

Basically the document file is a zip file that you can open up and manipulate, many of the files in the zip are XML - in fact most times you dont even need office installed to generate a document! You basically build xml file and put it in the zip file.

Beth Massi has several articles on this (http://blogs.msdn.com/b/bethmassi , and even did a few screen casts on .NET Rocks (http://www.dotnetrocks.com) showing the technique.

You may want to check it out.