none
Keep original cell format while working with template... RRS feed

  • Question

  • hi, working with OpenXMLSDK 2.0 to populate excel file from existing template.

    The template contains a Table and the table contains a few columns that is shown below:

     

     

    The table contains a [Total Row] to display summary on some columns and some columns of this table are also formatted by the user. The format can also be modified in the template whenever required. I am trying to fill this table with records, but the problem is the format of the cells do not retain. Below is an image of how the file should look like after I fill the data in:

     

     

    Is there anyone who can help me on this?

    Thanks,

    Jahedur Rahman

    Tuesday, August 16, 2011 7:07 AM

All replies

  • Hi Jahedur,

    I have spent much time reseaching on your problem.I can insert rows to the template, but it is difficult to change the related reference in the package and I haven't figured out how to remove the wrong information. Below is my code snippet:

    using System.Collections.Generic;
    using System.Linq;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
     
    namespace OpenXmlFillTable
    {
        class Program
        {
            static void Main(string[] args)
            {
                File.Copy("FillTable.xlsx""copy.xlsx",true);
                using (SpreadsheetDocument myDocument = SpreadsheetDocument.Open("copy.xlsx"true))
                {
                   Sheet sheet = myDocument.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();
                   if (null != sheet)
                   {
                       WorksheetPart worksheetPart = myDocument.WorkbookPart.GetPartById(sheet.Id) as WorksheetPart;
     
                       // get table in Sheet1
                       if (worksheetPart.TableDefinitionParts.Count() != 0)
                       {
                           for (int i = 1; i < 6; i++)
                           {
                               var lastRow = worksheetPart.Worksheet.Descendants<Row>().LastOrDefault();
                               SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                               uint rowIndex = lastRow.RowIndex.Value;
     
                                
                               Row newRow = CreateContentRow(rowIndex);
                               CreateRow(lastRow, sheetData, newRow);
                              
                               // change calculation reference
                               CalculationCell[]  calCellsList =
                                   myDocument.WorkbookPart.CalculationChainPart.CalculationChain.Descendants<CalculationCell>().
                                   Where(r => r.SheetId.Value == 1).ToArray();
                               foreach (CalculationCell calCell in calCellsList)
                               {
                                   var cellReference = calCell.CellReference.Value;
                                   calCell.CellReference = cellReference.ToString()[0] + (rowIndex + 1).ToString();
                               }
                           }
                       }
                   }
                }
            }
     
            public static string[] headerColumns = new string[] { "A""B""C","D","E","F","G" };
     
     
            private static Row CreateRow(Row refRow, SheetData sheetData, Row newRow)
            {
                uint rowIndex = refRow.RowIndex.Value;
                uint newRowIndex;
     
                //var theNewRow = (Row)refRow.Clone();
                var theNewRow = newRow;
                //theNewRow.RowIndex = rowIndex;
     
                // move the last row
                IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);
                foreach (Row row in rows)
                {
                    newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
     
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        string cellReference = cell.CellReference.Value;
                        cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                    }
     
                    row.RowIndex = new UInt32Value(newRowIndex);
                }
     
                // insert the new row before the last row
                sheetData.InsertBefore(theNewRow, refRow);
     
                return theNewRow;
            }
     
            private static Row CreateContentRow(uint rowIndex)
            {
                //Create the new row.
                Row r = new Row();
                r.RowIndex = rowIndex;
     
                //Create the cells that contain the data.
                for (int i = 0; i < 7; i++)
                {
                    Cell c = new Cell();
                    c.CellReference = headerColumns[i] + rowIndex;
                    CellValue v = new CellValue();
                    v.Text = (i+1).ToString();
                   // c.DataType = CellValues.Number;
                    c.AppendChild(v);
                    r.AppendChild(c);
                }
     
                return r;
            }
     
        }
    }
    

    And please see my screen shot:

     

    See whether this can help you go further about your problem and I will do further research about it. Please just feel free to follow up after you have tested.

    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.


    Monday, August 22, 2011 2:58 AM
  • Thanks a lot Bruce for your effort. I will try this and let you know the status soon.

    -Jahedur Rahman


    Tuesday, August 23, 2011 7:43 AM
  • Bruce, I have tried your code with a template having a table in the first sheet started at the first row. But it seems that your code pulls down the table header at the bottom of the newly inserted rows and the table gets new headers like [1,2,3,4,5,6,7]. Moreover the table is also not getting expanded to wrap all the rows in.

    After filling in the records, you can see the table reference using below code that needs to be changed though:

     

    worksheetPart.TableDefinitionParts.First().Table.Reference
    

    I am also getting the same error you mentioned in your post.

    Thanks for your effort...

    Jahedur Rahman




    Wednesday, August 24, 2011 4:57 AM
  • Hi Jahedur Rahman,

    I still can't fix the program correctly,it is really difficult and time confusing to adjust the table definition.  I think it better to generate the Excel file from scratch not to base on the template, when generating the file, you can fill data row by row and finally generate the table definition:

      // Generates content of tableDefinitionPart1.
            private static void GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1)
            {
                Table table1 = new Table() { Id = (UInt32Value)1U, Name = "表1", DisplayName = "表1", Reference = "A1:G4", TotalsRowCount = (UInt32Value)1U };
                AutoFilter autoFilter1 = new AutoFilter() { Reference = "A1:G3" };
     
                TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)7U };
                TableColumn tableColumn1 = new TableColumn() { Id = (UInt32Value)1U, Name = "ID", TotalsRowLabel = "汇总" };
                TableColumn tableColumn2 = new TableColumn() { Id = (UInt32Value)2U, Name = "Name" };
                TableColumn tableColumn3 = new TableColumn() { Id = (UInt32Value)3U, Name = "ExsitingAssets", TotalsRowFunction = TotalsRowFunctionValues.Sum, DataFormatId = (UInt32Value)1U, TotalsRowDifferentialFormattingId = (UInt32Value)0U };
                TableColumn tableColumn4 = new TableColumn() { Id = (UInt32Value)4U, Name = "TotalRevenueSharing", TotalsRowFunction = TotalsRowFunctionValues.Sum };
                TableColumn tableColumn5 = new TableColumn() { Id = (UInt32Value)5U, Name = "AnnualDeposits", TotalsRowFunction = TotalsRowFunctionValues.Sum };
                TableColumn tableColumn6 = new TableColumn() { Id = (UInt32Value)6U, Name = "Participants", TotalsRowFunction = TotalsRowFunctionValues.Sum };
                TableColumn tableColumn7 = new TableColumn() { Id = (UInt32Value)7U, Name = "PlanYearEnd" };
     
                tableColumns1.Append(tableColumn1);
                tableColumns1.Append(tableColumn2);
                tableColumns1.Append(tableColumn3);
                tableColumns1.Append(tableColumn4);
                tableColumns1.Append(tableColumn5);
                tableColumns1.Append(tableColumn6);
                tableColumns1.Append(tableColumn7);
                TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
     
                table1.Append(autoFilter1);
                table1.Append(tableColumns1);
                table1.Append(tableStyleInfo1);
     
                tableDefinitionPart1.Table = table1;
            }
    

    You can obtain the reflected code from the Openxml productivity tool and make some changes to it, for example, add your own data.

    Hope this can help you.

    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.


    Thursday, August 25, 2011 8:44 AM
  • Thanks a lot Bruce for giving such time and effort on this.

    I could try to start creating the files from scratch instead using templates, but the problem is the users can create/add templates as per their needs and can associate it with a process along with a query in an application. The templates contain pivot tables as per the users design/requirement that connect to the table in the first sheet.

    Since the pivot tables are predefined and specific to a single template, I cannot build a tool to create the pivot tables on the fly for all templates to accmodate users requirements. And creating even simple pivot tables on the fly using OpenXML for me I guess is going to be a nightmare!

    But I have got another API (ClosedXML) in CodePlex at http://closedxml.codeplex.com/. I have started with this and found its preety helpful though still having some issues with formatting columns and memory usage. I am working with this API to overcome the issues.

    Thanks again for your kind attention...

    Jahedur Rahman


    Friday, August 26, 2011 4:49 AM