Click here to Skip to main content
15,892,768 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Hi,

I m working on below sample code found from google :)

C#
using (SpreadsheetDocument xl = SpreadsheetDocument.Create("LargeFile.xlsx", SpreadsheetDocumentType.Workbook))
{
    List<OpenXmlAttribute> oxa;
    OpenXmlWriter oxw;

    xl.AddWorkbookPart();
    WorksheetPart wsp = xl.WorkbookPart.AddNewPart<WorksheetPart>();

    oxw = OpenXmlWriter.Create(wsp);
    oxw.WriteStartElement(new Worksheet());
    oxw.WriteStartElement(new SheetData());

    for (int i = 1; i <= 50000; ++i)
    {
        oxa = new List<OpenXmlAttribute>();
        // this is the row index
        oxa.Add(new OpenXmlAttribute("r", null, i.ToString()));

        oxw.WriteStartElement(new Row(), oxa);

        for (int j = 1; j <= 100; ++j)
        {
            oxa = new List<OpenXmlAttribute>();
            // this is the data type ("t"), with CellValues.String ("str")
            oxa.Add(new OpenXmlAttribute("t", null, "str"));

            // it's suggested you also have the cell reference, but
            // you'll have to calculate the correct cell reference yourself.
            // Here's an example:
            //oxa.Add(new OpenXmlAttribute("r", null, "A1"));

            oxw.WriteStartElement(new Cell(), oxa);

            oxw.WriteElement(new CellValue(string.Format("R{0}C{1}", i, j)));

            // this is for Cell
            oxw.WriteEndElement();
        }

        // this is for Row
        oxw.WriteEndElement();
    }

    // this is for SheetData
    oxw.WriteEndElement();
    // this is for Worksheet
    oxw.WriteEndElement();
    oxw.Close();

    oxw = OpenXmlWriter.Create(xl.WorkbookPart);
    oxw.WriteStartElement(new Workbook());
    oxw.WriteStartElement(new Sheets());

    // you can use object initialisers like this only when the properties
    // are actual properties. SDK classes sometimes have property-like properties
    // but are actually classes. For example, the Cell class has the CellValue
    // "property" but is actually a child class internally.
    // If the properties correspond to actual XML attributes, then you're fine.
    oxw.WriteElement(new Sheet()
    {
        Name = "Sheet1",
        SheetId = 1,
        Id = xl.WorkbookPart.GetIdOfPart(wsp)
    });

    // this is for Sheets
    oxw.WriteEndElement();
    // this is for Workbook
    oxw.WriteEndElement();
    oxw.Close();

    xl.Close();
}



But unable to modify on basis of my datatable row and column requirement.
Can some one modify this piece of code to accept the datatable row and column.

What I have tried:

i tried modify but not working
Posted
Updated 21-Nov-16 2:29am
Comments
[no name] 21-Nov-16 8:28am    
"Can some one modify this piece of code", yes someone can, YOU can. Even if we were to be a site were you could get your work done for free (it isn't), we would have no idea at all what your "datatable row and column requirement" are or what "but not working" means.
Nawab Ahmad 21-Nov-16 8:39am    
datatable table1 is table
table.row[0] is the row
Karthik_Mahalingam 21-Nov-16 22:57pm    
Always use  Reply  button, to post Comments/query to the user, so that the user gets notified and responds to your text.

1 solution

You can export the datatable to an XML file using DataTable.WriteXml(filename), and then load the XML in Excel. You don't need a third party library to do that.

EDIT=====================

Regarding number of rows:

You can breakup the databtable into smaller chunks by utilizing a loop that contains the DataTable.ImportRow method.
 
Share this answer
 
v4
Comments
Nawab Ahmad 21-Nov-16 8:38am    
Yes, I can but i m trying to export 1 million record
#realJSOP 21-Nov-16 9:01am    
That doesn't explain why you can't use it. Excel supports 1,048,576 rows by 16,384 columns per worksheet. If your dataset has more rows than that, you're gonna have to break it up into more manageable chunks.
Nawab Ahmad 21-Nov-16 11:12am    
Easy to say...But that not my requirement.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900