none
How to insert value in to particular cell in excel document which is created from template RRS feed

  • Question

  • Hi Team,

    I need some help on inserting values in excel file using openxml.

    I have tried with standard code which Microsoft giving to insert values in to excel. But if i am writing large files about 3000 rows and 50 columns it is taking more than 20 min.

    I have found a workaround to insert excel values using OpenXMLWriter. 

    But it is not working with the template approach. It is working only if i create new sheetdata and workseet.

    And is there any way to insert value into particular cells using OpenXMLWriter.

    The code i am using is

     WorkbookPart workbookpart = null;
                WorksheetPart worksheetpart = null;
                Worksheet worksheet = null;
                SheetData sheetdata = null;
                OpenXmlWriter writer = null;
                Cell cell = null;
                Row row = null;

               

                SpreadsheetDocumentObj.CompressionOption = CompressionOption.SuperFast;

                
                workbookpart = SpreadsheetDocumentObj.WorkbookPart;
                Sheet sheet = workbookpart.Workbook.Descendants<Sheet>().Where(

                   (s) => s.Name == "Sheet1").FirstOrDefault();

                worksheetpart = (WorksheetPart)(workbookpart.GetPartById(sheet.Id));
                worksheet = worksheetpart.Worksheet;
                sheetdata = worksheet.GetFirstChild<SheetData>();

                writer = OpenXmlWriter.Create(worksheetpart);
                writer.WriteStartElement(new Worksheet(worksheet.OuterXml));
                writer.WriteStartElement(new SheetData(sheetdata.OuterXml));

               


                for (int i = 1; i < 35000; i++)
                {
                    row = new Row();
                    
                    for (int j = 0; j < 100; j++)
                    {

                        cell = new Cell();
                        cell.SetAttribute(new OpenXmlAttribute("", "t", "", "inlineStr"));
                        cell.InlineString = new InlineString { Text = new Text { Text = "Hi" } };
                        row.Append(cell);

                       
                    }

                    writer.WriteElement(row);
                    
                }


                writer.WriteEndElement();
                writer.WriteEndElement();
                writer.Close();
                workbookpart.Workbook.Save();
                SpreadsheetDocumentObj.Close();

    Please help me out of this. Any help is appreciated.

    Thanks & Regards,

    Programmer Kadapa


    Programmer Kadapa

    Saturday, December 28, 2013 1:04 PM

Answers

  • Hi,

    >>I have tried with standard code which Microsoft giving to insert values in to excel. But if i am writing large files about 3000 rows and 50 columns it is taking more than 20 min.<<

    I think it will not take too much time to insert values into an Excel spreadsheet with 3000 rows and 50 columns by Open XML SDK.

    Would you mind sharing your original code without using OpenXMLWriter to help us to reproduce your issue?

    >>is there any way to insert value into particular cells using OpenXMLWriter.<<

    As you said, OpenXMLWriter can only operate a new Worksheet instead of an existing document. So I'm afraid you cannot insert values into particular cells of existing spreadsheet using OpenXMLWriter.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, December 31, 2013 11:54 AM
    Moderator

All replies

  • Hi Kadapa

    You need to provide more information. How do you identify the cells to which data must be written? How many cells would this be? Are we talking about entire "tables" or data, or individual cells?


    Cindy Meister, VSTO/Word MVP, my blog

    Saturday, December 28, 2013 4:02 PM
    Moderator
  • Hi Cindy Meister,

    Thanks for the response.

    I am filling excel from a data table which contains the cell and row information. But first i am not even able to add any cell into the excel file which is created from a template. 

    Thanks & Regards

    Programer Kadapa


    Programmer Kadapa

    Saturday, December 28, 2013 9:25 PM
  • Hi,

    >>I have tried with standard code which Microsoft giving to insert values in to excel. But if i am writing large files about 3000 rows and 50 columns it is taking more than 20 min.<<

    I think it will not take too much time to insert values into an Excel spreadsheet with 3000 rows and 50 columns by Open XML SDK.

    Would you mind sharing your original code without using OpenXMLWriter to help us to reproduce your issue?

    >>is there any way to insert value into particular cells using OpenXMLWriter.<<

    As you said, OpenXMLWriter can only operate a new Worksheet instead of an existing document. So I'm afraid you cannot insert values into particular cells of existing spreadsheet using OpenXMLWriter.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, December 31, 2013 11:54 AM
    Moderator