How to Create an excel document using OpenXML SDK?

Hello Odessa_Technologies,

Take a look here:

Creating Spreadsheet document using OpenXML SDK
http://openxmldeveloper.org/forums/thread/1816.aspx



Regards,

Rimvis

hey thanks for the reply. I have seen this link before.

but the problem is they load the workbook and a sheet in the code.

but i didn't understand that how they are getting that workbook and Excel sheet.

XmlDocument xwb = new XmlDocument();
xwb.Load(@"..\..\workbook.xml");

Do i need to save a copy of the physical workbook and sheet in any location??

But from coding stand point that's not done.

I'll appreciate if you can explain it in details.

Thanks,
darshan

Sorry, maybe not the best example. have you seen this:

Creating an Excel Workbook with the Open XML SDK 2.0
http://blog.stuartwhiteford.com/?p=49

which .net Framework are u using ? 2.0 or 3.5  ?

FW 3.5

Hi Mardau,

i have seen your work. But in DataSetToWorkbook class you are inserting the data by reading the cell for each rows.
Just a query if i have more than 65000 rows what will happened to the performance??

Why i'm asking this question is that in some scenario i need to export more than 1,50,000 records into excel. I tried with row cell method but we didn't get good result.

Your advice is highly required.

thanks,
Darshan

I'm using these "extentions" for data import from excell to my app,
and the performace is really nice. You ask why ? because of the LINQ lazy loading.
which accesses the data just when it's needed. to be more specific its "yield return" feature
which fixes the performance :)

hey Mardau,

i got another issues. When i have more than 65K records in the dataset. Ideally after 65k records it should create another sheet in the same workbook. But currently it's putting in the same sheet.

Is there anything that you can help me out regarding this issue??

Thanks,

Darshan

you can use Page`ing.
maybe You can Use Linq function .Take(65K)

linq take(65K)
it takes the first 65K elements specified in the parameter.

Do you think this is applicable in dataset as well. I'm finding difficulty to understand.

sorry, I don't know the exact situation. Thought it might help

let me help you to understand the technical requirement.

I need to export data from dataset to excel. Now Dataset can hold approximately more than 2,00,000 records. Now i need to generate an excel workbook with multiple sheets. No sheet should contain more than 65,000 records.

I think this will help you to understand the situation.

I believe something like this should work

var d = new DataSet();

 var result = d.Tables[0].AsEnumerable().Skip(0).Take(5);

you would just need to figure out the table index in your dataset

Hi Mardau,

The http://yogesh.jagotagroup.com/blog/page/Excel-Xml-Library.aspx  link that you have suggested those dll's can not be used in our project. Do you have any sample example with source code to create an excel file that can contain min of 65k records.

Thnaks,
Darshan

Hi,

unfortunately i don't.
by the way - why can't you use the library ?

thanks a lot