none
Fill an Excel Table using Open XML SDK 2.0 RRS feed

  • Question

  • Hello experts,

    I have just started using Open XML SDK 2.0 to write data in Excel (.xlsx) file. I am trying to fill with data into an existing file that contains an Excel Table (Table1) in the first sheet and a Pivot Table in a separate sheet (Sheet2) using the Table (Table1) from the first Sheet (Sheet1).

    The template is created manually using Microsoft Office Excel 2007 application. The Table in the first sheet and the pivot table in the second sheet are also created using this. The Table (Table1) contains only one row for the column headers. My application should insert records in the table using Open XML SDK 2,0 and stretch the table to the number of rows inserted.

    When I insert the records from second row in the first sheet, the table does not get stretched to the total number of rows. So the pivot table is also not showing any records when I refresh due to this, although the first sheet contains all the rows.

    Can anyone please help me writing the correct code to insert records in the main table in the first sheet that will allow the pivot table refresh properly?

    Thanks in advance...

    -Jahedur Rahman

    Saturday, August 6, 2011 11:20 AM

All replies

  • Please let me know if anyone needs further clarification....

    I need to get this solved urgently!

    -Jahedur Rahman

    Monday, August 8, 2011 6:09 AM
  • Dear Jahedur Rahman,

    Please see these articles, they contains the code about inserting values to the Excel cells, you can try to modify them and apply to your project:

    How to: Insert Text into a Cell in a Spreadsheet Document

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

    Office Open XML Formats: Inserting Values into Excel 2007 Cells

    http://msdn.microsoft.com/en-us/library/bb508943(office.12).aspx

    OpenXML Spreadsheet, how to copy and insert specified rows

    http://products.e-dbxml.net/openxml-spreadsheet/post/openxml-spreadsheet-copy-inser

    SpreadsheetML Made Easy Using C#

    http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/12/21/7937.aspx

    Hope these help.

    Regards,


    Be happy.
    Thursday, August 11, 2011 6:51 AM
  • Thanks Mike for your response and for providing the links. These are helpful, but at this moment I am trying to insert rows/cells in an existing Excel Table.

    Below is the code I am trying to do this...

     

    using (SpreadsheetDocument excel = SpreadsheetDocument.Open(this.fileName.Text, true))
       {
        WorkbookPart workbookPart = excel.WorkbookPart;
    
        WorksheetPart worksheetPart = GetFirstWorksheetPart(excel);
        SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
    
        DataTable dtData = GetData();
        
        for (int i = 0; i < dtData.Rows.Count; i++)
        {
         sheetData.AppendChild(CreateContentRow(dtData.Rows[i], i + 2));
        }
        
        
        if (worksheetPart.TableDefinitionParts.Count() == 0)
        {
         string reference = "A1:" + getColumnName(dtData.Columns.Count) + (dtData.Rows.Count + 1).ToString();
         TableDefinitionPart tdf = worksheetPart.AddNewPart<TableDefinitionPart>();
         tdf.Table = new Table();
         tdf.Table.TableStyleInfo = new TableStyleInfo();
         tdf.Table.TableStyleInfo.Name = "TableStyleMedium9";
         tdf.Table.Reference = reference;
    
        }
        else//if the table already exists
        {
         string reference = "A1:" + getColumnName(dtData.Columns.Count) + (dtData.Rows.Count + 1).ToString();
         TableDefinitionPart tdf = worksheetPart.TableDefinitionParts.First();
         tdf.Table.Reference = reference;
         tdf.Table.HeaderRowBorderFormatId = null;
         tdf.Table.HeaderRowFormatId = null;
         tdf.Table.TotalsRowShown = false;
         tdf.Table.InsertRow = null;
         tdf.Table.AutoFilter.Reference = reference;
         tdf.Table.Save();
         
        }
        excel.WorkbookPart.Workbook.Save();
        excel.Close();
       }
      }
    

    I am inserting rows from the second row in the spreadsheet. The Table wraps cells from the first row. After inserting all records in the worksheet, I have modified the table definition information to hold all the rows (including newly insterted) in it.

     

    But when I open the spread sheet I get an error stating.. "Excel found unreadable content in 'filename.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."

    Can you please help me on this?

    -Jahedur Rahman

     

     


    Thursday, August 11, 2011 9:19 AM
  • Hi Jahedur Rahman,

    After reading your post, I coded the following code snippet which I think may help you. In template.xlsx file, I define the table with three columns with their names in sheet1 and a pivot table in sheet2.

    I try to get the table in the sheet1, then use LINQ to SQL to get the data and append it to the table:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
     
     
    namespace OpenXmlExcelFillTablePivotTable
    {
        class Program
        {
            static void Main(string[] args)
            {
                File.Copy("template.xlsx""generated.xlsx"true);
                //Open the copied template workbook. 
                using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open("generated.xlsx"true))
                {
     
     
                    // Access the main Workbook part, which contains all references.
                    WorkbookPart workbookPart = myWorkbook.WorkbookPart;
                    // get sheet by name
                    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();
                    
                    if (sheet != null)
                    {
                        // get worksheetpart by sheet id
                        WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;
                        // get table in Sheet1
                        if (worksheetPart.TableDefinitionParts.Count() != 0)
                        {
                            // The SheetData object will contain all the data.
                            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
     
                            //Connect to database.
                            DataClasses1DataContext db = new DataClasses1DataContext();
     
                            //The data starts at row 2. 
                            int index = 2;
     
                            //Select all rows from SalesTerritory table.
                            var territoryQuery = from t in db.SalesTerritories select t;
     
                            //For each row in the database, add a row to they spreadsheet.
                            foreach (var item in territoryQuery)
                            {
                                string territoryName = item.Name;
                                decimal salesLastYear = Math.Round(item.SalesLastYear, 2);
                                decimal salesThisYear = Math.Round(item.SalesYTD, 2);
                                //Add a new row.
                                Row contentRow = CreateContentRow(index, territoryName, salesLastYear, salesThisYear);
                                index++;
                                //Append new row to sheet data.
                                sheetData.AppendChild(contentRow);
                            }
                        }
                    }
                }
            }
     
            public static string[] headerColumns = new string[] { "A""B""C" };
     
            private static Row CreateContentRow(int index, string territory, decimal salesLastYear, decimal salesThisYear)
            {
                //Create the new row.
                Row r = new Row();
                r.RowIndex = (UInt32)index;
                //First cell is a text cell, so create it and append it.
                Cell firstCell = CreateTextCell(headerColumns[0], territory, index);
                r.AppendChild(firstCell);
                //Create the cells that contain the data.
                for (int i = 1; i < headerColumns.Length; i++)
                {
                    Cell c = new Cell();
                    c.CellReference = headerColumns[i] + index;
                    CellValue v = new CellValue();
                    if (i == 1)
                        v.Text = salesLastYear.ToString();
                    else
                        v.Text = salesThisYear.ToString();
                    c.AppendChild(v);
                    r.AppendChild(c);
                }
                return r;
            }
     
            public static Cell CreateTextCell(string header, string text, int index)
            {
                //Create a new inline string cell.
                Cell c = new Cell();
                c.DataType = CellValues.InlineString;
                c.CellReference = header + index;
                //Add text to the text cell.
                InlineString inlineString = new InlineString();
                Text t = new Text();
                t.Text = text;
                inlineString.AppendChild(t);
                c.AppendChild(inlineString);
                return c;
            }
     
        }
    }
    

    With the help of the template file, I can get generate.xlsx file and the pivot table can show in the sheet2 from the data in sheet1 after refreshing data:

    And here is the download url for the project named OpenXmlExcelFillTablePivotTable :

     https://skydrive.live.com/#!/?cid=c7d080162e80a07a&sc=documents&id=C7D080162E80A07A%21115

    , you can download the database from this site and add to your project:

    http://msftdbprodsamples.codeplex.com/releases/view/4004

    Hope these can help you and feel free to follow up after you have tried. Wish you a nice day.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Friday, August 12, 2011 3:57 AM
  • Thanks a lot Bruce for your effort.

    But I found that in your sample template, the pivot table refers three columns (A, B, C) from the first sheet instead refering to the table from it. Here the Table in the first sheet (Sheet1) is not expanding upon insertion of new rows in the sheet. Because the rows from SalesTerritory are inserted into the Sheet1 instead of inserting into the Table.

    The main problem here is that, the tables in my templates contain the [Total Row] at the end of the rows that displays the summary (total, count...etc.) of some columns. If I add this total row of the table selecting the check box from the ribbon [Design\Table Style Options\Total Row], it adds the total row at position 3 in the worksheet. Because the table was not expanded to the end of the inserted rows int the sheet.

    Below is the image of Sheet1 after adding the total Row...

    And below is the image of the pivot table after adding to total row in the first sheet...

    Here the Total is also added as a data row in the pivot table.

    The solution of this issue could be to use table reference in the Pivot table instead using sheet reference.

    Please take a look at it and please let me know if this can be resolved, because if we use table reference, the table needs to be expanded to the end of the inserted rows programmatically.

    -Jahedur Rahman

    Friday, August 12, 2011 5:24 AM
  • I too am having this same issue.  Interstingly my code is EXACTLY the same as what Bruce proposed above.  I have a template with a sheet that contains a named table that I need to insert items into.  My items are inserted almost as expected, but the table range is not changed.  There must be a way to add the rows to a "named" table in Excel and not just to the SheetData...I just haven't found it yet.
    Tuesday, November 8, 2011 3:54 PM
  • Easy way to fill excel table, using Spire.XLS.

      Workbook workbook = new Workbook();
     
    //Initialize worksheet
    Worksheet sheet = workbook.Worksheets[0];
    sheet.InsertDataTable((DataTable)this.dataGrid1.DataSource,true,2,1,-1,-1);
    Monday, November 14, 2011 6:33 AM
  • Indeed tables in Open XML are special.

    To get a first sense of it you may unzip your Excel Workbook (change extension from .xlsx to .zip, then unzip) and take a look at the xml files stored in /xl/tables folder.

    An article here : http://ankushbhatia.wordpress.com/2011/06/13/how-to-delete-a-column-in-excel-using-open-xml-sdk/ uses the tables to DELETE a column, so while the use case is not exactly the same, it has a nice introduction to Excel tables representation in Open XML, and the rest of it helps in the understanding.

    In short, tables are more complex than sheets, they also rely on /xl/tables folder and on sharedStrings.xml file.

    Monday, December 5, 2011 2:11 PM
  • Found the solution to the issue...might be helpful to someone...

    Create a template with lets say 3 columns of table. Fill the data in. Then modify the table definition by using the below code.

    string reference = string.Format("A1:{0}{1}", lastColumn, lastExcelRow);
    TableDefinitionPart tdf = workSheetPart.TableDefinitionParts.First();
    tdf.Table.Reference = reference;
    tdf.Table.AutoFilter.Reference = reference;

    Thats all is needed.
    Note : there is no need of tdf.Table.Save();

    If you give that excel was opening with errors.

    Wednesday, May 30, 2012 11:07 AM
  • HI,

    Can you please provide code for changing the style/ applying style to excel.

    I want to apply table style Medium4 to my excel.

    Help will be appreciated.

    TIA.

    Regards:

    GaurravS

    Tuesday, October 15, 2013 10:56 AM
  • Hi Bruce,

    Actually am trying to populate data into existing excel sheet table from data table using openXML in c#, can we have reference code.

    Thanks in advance

    Friday, December 14, 2018 1:14 PM