none
Using Open XML SDK Code Snippets RRS feed

  • Question

  • I am having trouble using the reflect code for the whole excel document I created as a report sheet. I get a stack overflow error when trying to create that report sheet programmatically using the create package code.

     private void button1_Click(object sender, EventArgs e)

            {

                //This is a main method used to call the generate class that generates excel documents

                GeneratedClass gc = new GeneratedClass();

                gc.CreatePackage("MAR.xlsx");

     

    Really all I need to do though is have the computer copy and paste the one table I get when I export to excel from Lightswitch into the template report file. Right now I am using =cell (contents) references either within the same file or to another file to automatically transfer the data. The problem with that though is that the exported excel file has to have the user know to save the file with the correct name to the correct place so the lines of the table are copied to a sheet on the report sheet so I can use the data in the way it is formatted and displayed on in the report sheet file in excel.

    I have been looking at code and found one of the snippets called XLGetCellForWriting formt the 2008 Open XML SDK that may help. I want to use it to write to the cells of the report sheet creating a table on a separate worksheet without having to write the whole file over again every time. Then just have one instance of the template file in a generic file directory like C:/Folder for Program/Excel file. I can then after the code snippet write a process start statement for that file location

                //This opens the excel file from the current directory
                Process.Start("MAR.xlsx");

     

    Below is the code snippet but I am unsure how to use it there seems to be some XML code and some C# code here but I don’t know what I will need to add to my button handler to write to a specific cell and how to specify it use the table of data in my database to write to those cells. I am assuming I will have to write this snippet once for every cell and I know that I will only need so many rows of the database because after a certain number of rows it is unlikely that there will be more items in this case medications per patient would not exceed a number very high. Can you help me with an example of how to use this code to write to a cell one item from a database like the tables generated in Lightswitch?

    <?xml version="1.0" encoding="utf-8"?>
    <CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
      <CodeSnippet Format="1.0.0">
        <Header>
          <SnippetTypes>
            <SnippetType>Expansion</SnippetType>
          </SnippetTypes>
          <Title>Excel: Get cell for writing</Title>
          <Author>Ken Getz</Author>
          <Description>Given a spreadsheet document, a sheet name and an address, return a reference to a cell ready to accept a value. Create the cell if necessary.</Description>
          <HelpUrl>
          </HelpUrl>
          <Shortcut>XLGetCellForWriting</Shortcut>
        </Header>
        <Snippet>
          <References>
            <Reference>
              <Assembly>DocumentFormat.OpenXml</Assembly>
            </Reference>
          </References>
          <Imports>
             <Import>
               <Namespace>System</Namespace>
             </Import>
             <Import>
               <Namespace>System.Linq</Namespace>
             </Import>
             <Import>
               <Namespace>DocumentFormat.OpenXml.Spreadsheet</Namespace>
             </Import>
             <Import>
               <Namespace>System.Text.RegularExpressions</Namespace>
             </Import>
             <Import>
               <Namespace>DocumentFormat.OpenXml.Packaging</Namespace>
             </Import>
          </Imports>
          <Code Language="csharp"><![CDATA[
    //using System;
    //using System.Linq;
    //using DocumentFormat.OpenXml.Spreadsheet;
    //using System.Text.RegularExpressions;
    //using DocumentFormat.OpenXml.Packaging;
     
    // Given a reference to an Excel SpreadsheetDocument, the name of a sheet,
    // and a cell address, return a reference to the cell. Throw an ArgumentException
    // if the sheet doesn't exist. If the cell doesn't exist, create it.
    public  Cell XLGetCellForWriting(SpreadsheetDocument document, string sheetName, string address)
    {
     
      WorkbookPart wbPart = document.WorkbookPart;
     
      // Find the sheet with the supplied name, and then use that Sheet object
      // to retrieve a reference to the appropriate worksheet.
      Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
        Where(s => s.Name == sheetName).FirstOrDefault();
     
      if (theSheet == null)
      {
        throw new ArgumentException("sheetName");
      }
     
      // Retrieve a reference to the worksheet part, and then use its Worksheet property to get 
      // a reference to the cell whose address matches the address you've supplied:
      WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
      Worksheet ws = wsPart.Worksheet;
      Cell theCell = ws.Descendants<Cell>().
        Where(c => c.CellReference == address).FirstOrDefault();
     
      // If the cell doesn't exist, create it:
      if (theCell == null)
      {
        theCell = InsertCellInWorksheet(ws, address);
      }
      return theCell;
    }
    // Given a Worksheet and an address (like "AZ254"), either return a cell reference, or 
    // create the cell reference and return it.
    private  Cell InsertCellInWorksheet(Worksheet ws, string addressName)
    {
     
      // Use regular expressions to get the row number and column name.
      // If the parameter wasn't well formed, this code
      // will fail:
      Regex rx = new Regex("^(?<col>\\D+)(?<row>\\d+)");
      Match m = rx.Match(addressName);
      uint rowNumber = uint.Parse(m.Result("${row}"));
      string colName = m.Result("${col}");
     
      SheetData sheetData = ws.GetFirstChild<SheetData>();
      string cellReference = (colName + rowNumber.ToString());
      Cell theCell = null;
     
      // If the worksheet does not contain a row with the specified row index, insert one.
      var theRow = sheetData.Elements<Row>().
        Where(r => r.RowIndex.Value == rowNumber).FirstOrDefault();
     
      if (theRow == null)
      {
        theRow = new Row();
        theRow.RowIndex = rowNumber;
        sheetData.Append(theRow);
      }
     
      // If the cell you need already exists, return it.
      // If there is not a cell with the specified column name, insert one.  
      Cell refCell = theRow.Elements<Cell>().
        Where(c => c.CellReference.Value == cellReference).FirstOrDefault();
      if (refCell != null)
      {
        theCell = refCell;
      }
      else
      {
        // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
        foreach (Cell cell in theRow.Elements<Cell>())
        {
          if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
          {
            refCell = cell;
            break;
          }
        }
     
        theCell = new Cell();
        theCell.CellReference = cellReference;
     
        theRow.InsertBefore(theCell, refCell);
      }
      return theCell;
    }
    ]]></Code>
        </Snippet>
      </CodeSnippet>
    </CodeSnippets>

     

    Saturday, March 1, 2014 1:17 AM

Answers

  • Hi,

    According to your code and description, you want to insert text into a cell using OpenXML SDK, There are two functions in your code sample, these are used to create cells nodes in Excel worksheet, but the value of the cell is saved in sheetData. sheetData represents the cell table and contains one or more Row elements. A row contains one or more Cell elements. Each cell contains a CellValue element that represents the value of the cell.

    I added InsertTextEx and InsertSharedStringItem method in your code, it can insert value in specified cell.

    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Text.RegularExpressions;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    
    public void InsertTextEx(string docName, string text)
            {
                // Open the document for editing.
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                {
                    // Get the SharedStringTablePart. If it does not exist, create a new one.
                    SharedStringTablePart shareStringPart;
                    if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    {
                        shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    }
                    else
                    {
                        shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    }
                    // Insert the text into the SharedStringTablePart.
                    int index = InsertSharedStringItem(text, shareStringPart);
    
                    // Insert a new worksheet.
                    WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
    
                    // Insert cell A1 into the new worksheet.
                    Cell cell = XLGetCellForWriting(spreadSheet, "Sheet1", "A1");
    
                    // Set the value of cell A1.
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    
                    // Save the new worksheet.
                    worksheetPart.Worksheet.Save();
                }
            }
    private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
            {
                // If the part does not contain a SharedStringTable, create one.
                if (shareStringPart.SharedStringTable == null)
                {
                    shareStringPart.SharedStringTable = new SharedStringTable();
                }
                int i = 0;
                // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
                foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == text)
                    {
                        return i;
                    }
    
                    i++;
                }
                // The text does not exist in the part. Create the SharedStringItem and return its index.
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                shareStringPart.SharedStringTable.Save();
                return i;
            }
    

    Please refer to following link to get more information:

    #How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    http://msdn.microsoft.com/en-us/library/office/cc861607.aspx

    Regards,


    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.

    • Marked as answer by reigh7 Monday, March 24, 2014 12:24 AM
    Monday, March 3, 2014 9:50 AM
    Moderator

All replies

  • Hi,

    According to your code and description, you want to insert text into a cell using OpenXML SDK, There are two functions in your code sample, these are used to create cells nodes in Excel worksheet, but the value of the cell is saved in sheetData. sheetData represents the cell table and contains one or more Row elements. A row contains one or more Cell elements. Each cell contains a CellValue element that represents the value of the cell.

    I added InsertTextEx and InsertSharedStringItem method in your code, it can insert value in specified cell.

    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Text.RegularExpressions;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    
    public void InsertTextEx(string docName, string text)
            {
                // Open the document for editing.
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                {
                    // Get the SharedStringTablePart. If it does not exist, create a new one.
                    SharedStringTablePart shareStringPart;
                    if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    {
                        shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    }
                    else
                    {
                        shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    }
                    // Insert the text into the SharedStringTablePart.
                    int index = InsertSharedStringItem(text, shareStringPart);
    
                    // Insert a new worksheet.
                    WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
    
                    // Insert cell A1 into the new worksheet.
                    Cell cell = XLGetCellForWriting(spreadSheet, "Sheet1", "A1");
    
                    // Set the value of cell A1.
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    
                    // Save the new worksheet.
                    worksheetPart.Worksheet.Save();
                }
            }
    private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
            {
                // If the part does not contain a SharedStringTable, create one.
                if (shareStringPart.SharedStringTable == null)
                {
                    shareStringPart.SharedStringTable = new SharedStringTable();
                }
                int i = 0;
                // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
                foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == text)
                    {
                        return i;
                    }
    
                    i++;
                }
                // The text does not exist in the part. Create the SharedStringItem and return its index.
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                shareStringPart.SharedStringTable.Save();
                return i;
            }
    

    Please refer to following link to get more information:

    #How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    http://msdn.microsoft.com/en-us/library/office/cc861607.aspx

    Regards,


    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.

    • Marked as answer by reigh7 Monday, March 24, 2014 12:24 AM
    Monday, March 3, 2014 9:50 AM
    Moderator
  • I have several questions first how do Identify the "replacement parameters" such as docName that I will need to fill in and what is the syntax for a docName file.

    Second I want to create a more secure document package and was reading that the open method can contain the Boolean parameter "false" so that changes to the document would not be saved. Does the below quote mean that I can leave have a document used as a template with a blank worksheet used to insert the table in the file location stay in that files location. Then leave it up to the user to choose to save that file with a new name and/or location after the tables cells have been inserted onto the blank worksheet and process.start has displayed the modified table. Additionally can I create a secure file storage location for documents to be saved to by the user and an unsecured file location for the template document to be stored so the calls can be inserted with this code. Ideally I would like these file locations to be added as part of the application install process.

    "To create the class instance from the document that you call one of the Open() overload methods. Several are provided, each with a different signature. The sample code in this topic uses the Open(String, Boolean) method with a signature that requires two parameters. The first parameter takes a full path string that represents the document that you want to open. The second parameter is either true or false and represents whether you want the file to be opened for editing. Any changes that you make to the document will not be saved if this parameter is false."

    Third in the section on creating a worksheet part it refers to the of concatenating...

    string sheetName = "Sheet" + sheetId;

    but I will want to be using an existing worksheet that I have already tied =Cell (contents, "") function to from another sheet.

    "The code adds a new WorksheetPart object to the WorkbookPart object by using the AddNewPart method. It then adds a new Worksheet object to the WorksheetPart object, and gets a unique ID for the new worksheet by selecting the maximum SheetId object used within the spreadsheet document and adding one to create the new sheet ID. It gives the worksheet a name by concatenating the word "Sheet" with the sheet ID. It then appends the new Sheet object to the Sheets collection."

    Lastly can I repeat the code block part

                    // Set the value of cell A1.
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

    repeatedly within the same code block to define cells "A1" through "AU40" to reduce the saze of the code block?

    Monday, March 10, 2014 1:44 AM
  • Hi,

    Firstly, you need pass document path as docName argument in this method, like below:

    InsertTextEx(@"D:\1.xlsx", "InsertText")

    >>I want to create a more secure document package and was reading that the open method can contain the Boolean parameter "false" so that changes to the document would not be saved.

    You could add try…catch block to catch exception in the save () method.

    >> I will want to be using an existing worksheet that I have already tied =Cell (contents, "") function to from another sheet.

    Yes, this sample code shows how to use the classes in the Open XML SDK 2.5 for Office to insert text into a cell in a new worksheet in a spreadsheet document programmatically, but you just need modify   sheetName as your existing worksheet name to achieve goal.

    Lastly, >> Lastly can I repeat the code block part

    Your InsertCellInWorksheet method contain a regular expression to analysis the string to cell, if you want to fill cell which locating the “AU” column and the 40th row, you should modify “A1” as “AU40”.

    Did you want to set cells value from A1 to AU40?

    If so, I suggest you changing the InsertCellInWorksheet method to pass column index and row index, and then using a “for” loop to run a statement or a block of statements repeatedly.

    Regards,


    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.

    Wednesday, March 12, 2014 7:15 AM
    Moderator
  • Hi,

    Did you want to set cells value from A1 to AU40?

    If so, I suggest you changing the InsertCellInWorksheet method to pass column index and row index, and then using a “for” loop to run a statement or a block of statements repeatedly.

    Regards,


    The values from A1 to AU40 will theoretically be all different completing a whole table from the database data of one person. There will be would be some repetition but I will just not pass the column associated with the persons name for instance more than once even though it is repeated on the original table. other values such as medication name will be different for each row of the column it are associated with.
    Friday, March 14, 2014 2:32 AM