none
Using R1C1 style References with openxml SDK 2.5 SpreadsheetML RRS feed

  • Question

  • I am successfully importing a datatable into a spreadsheet.  The next request was to format that data as a table for filtering purposes.  The code provided by reflector for the table is very static with concrete column declarations etc. I am going to write my own part class to do this formatting but with variable column I need to be able to appropriately set the range of the table.

    For instance Reflector produces (on a sample workbook) this for the table declaration:

    Table table1 = new Table() { Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = "A1:C4", TotalsRowShown = false };

    While the above works perfectly fine, it doesn't account for the variable width and length of the exported Datatable. I need to be able to set the Reference to a Range covering all of the columns and rows in the datatable imported.

    My Preference would be to use R1C1 Notation for the reference, but from my limited testing this is not working. Converting from R1C1 to "regular" notation would work too if there is a helper method somewhere in the SDK.

    For Reference I have tried:

    Table table1 = new Table() { Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = "R1C1:R4C3", TotalsRowShown = false };

    this is obviously a trivial example exported spreadsheets may have up to 65000 rows with up to 30 columns. Although more rows usually means fewer columns.

    The potential number of rows (which is outside of my control) is what makes the table important for filtering.

    P.S. I am doing this server side, so no interop.

    Many Thanks,

    Bill

    Monday, February 18, 2013 6:14 PM

Answers

  • As always I am under a time crunch so I ripped off these helper routines for my workbookHelper Class (Code after the sign off).  While 99.99% of the time my start row and column will be 1,1 I created it so that it could define a range from any start point to any end point.  Not sure what would happen if you reversed start and end information, but I can live with that for now.  While I would still prefer to use some baked in methods (hopefully with better error checking) this will at least allow me to move forward and I thought others might find it useful.  If you have a better way please let  me know.

    While not ideal it allows me to write my code like this (Still in progress) which seems like it will suite my needs

    public static void AddTableDefinitionPart(WorksheetPart part)
            {
                TableDefinitionPart tableDefinitionPart1 = part.AddNewPart<TableDefinitionPart>("rId1"); //
                GenerateTableDefinitionPart1Content(tableDefinitionPart1);
            }
    
            // Generates content of tableDefinitionPart1.
            private static void GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1)
            {
                //information to be passed in or set via property. Hardcode for initial testing
                int startRow = 5;
                int startColumn = 4;
                int endRow = 8;
                int endColumn = 6;
                string tableName = "TestTable";
                string displayName = "TestTable";
                bool showTotalsRow = false;
                string tableStyleName = "TableStyleMedium2";
                bool tableStyleShowFirstColumn = false;
                bool tableStyleShowLastColumn = false;
                bool tableStyleShowRowStripes = true;
                bool tableStyleShowColumnStripes = false;
    
                //Internal Routine Machinations
                string tableRange = GetExcelA1Range(startRow, startColumn, endRow, endColumn);
                Table table1 = new Table() { Id = (UInt32Value)1U, Name = tableName, DisplayName = displayName, Reference = tableRange, TotalsRowShown = showTotalsRow };
                AutoFilter autoFilter1 = new AutoFilter() { Reference = tableRange };
    
                //Wrap column details in a for each loop to generate the appropriate columns:
                TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)3U };
                TableColumn tableColumn1 = new TableColumn() { Id = (UInt32Value)1U, Name = "Make" };
                TableColumn tableColumn2 = new TableColumn() { Id = (UInt32Value)2U, Name = "Miles" };
                TableColumn tableColumn3 = new TableColumn() { Id = (UInt32Value)3U, Name = "Cost", DataFormatId = (UInt32Value)0U };
    
                tableColumns1.Append(tableColumn1);
                tableColumns1.Append(tableColumn2);
                tableColumns1.Append(tableColumn3);
    
                //Apply Table Style information
                TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = tableStyleName, ShowFirstColumn = tableStyleShowFirstColumn, ShowLastColumn = tableStyleShowLastColumn, ShowRowStripes = tableStyleShowRowStripes, ShowColumnStripes = tableStyleShowColumnStripes };
    
                //Append Table Details
                table1.Append(autoFilter1);
                table1.Append(tableColumns1);
                table1.Append(tableStyleInfo1);
                
                //Apply Table to Definition Part.
                tableDefinitionPart1.Table = table1;
            }

    Thanks,

    Bill

    using System;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Linq;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    using System.Collections.Generic;
    using System.Text;
    
    namespace ConvertCellsToTable
    {
        class Program
        {
            private static string GetExcelA1Range(int startRow, int startColumn, int endRow, int endColumn)
            {            
                return GetExcelColumnA1Reference(startColumn) + startRow.ToString() + ":" + GetExcelColumnA1Reference(endColumn) + endRow.ToString();
            }
    
            private static string GetExcelColumnA1Reference(int columnNumber)
            {
                int dividend = columnNumber;
                string columnName = String.Empty;
                int modulo;
                
                while (dividend > 0)
                {
                    modulo = (dividend - 1) % 26;
                    columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                    dividend = (int)((dividend - modulo) / 26);
                }
    
                return columnName;
            }
    
            static void Main(string[] args)
            {
                int startRow;
                int startColumn;
                int endRow;
                int endColumn;
    
                bool isNum = int.TryParse(args[0], out startRow);
                isNum = int.TryParse(args[1], out startColumn);
                isNum = int.TryParse(args[2], out endRow);
                isNum = int.TryParse(args[3], out endColumn);
         
    
                Console.WriteLine("RangeFormatted for coordinates [{0},{1}], [{2},{3}]", startRow, startColumn, endRow, endColumn);
                Console.WriteLine("{0}", GetExcelA1Range(startRow, startColumn, endRow, endColumn));
                Console.WriteLine("Press Enter to continue");
                Console.ReadLine();
            }
        }
    }

    Monday, February 18, 2013 8:59 PM

All replies

  • As always I am under a time crunch so I ripped off these helper routines for my workbookHelper Class (Code after the sign off).  While 99.99% of the time my start row and column will be 1,1 I created it so that it could define a range from any start point to any end point.  Not sure what would happen if you reversed start and end information, but I can live with that for now.  While I would still prefer to use some baked in methods (hopefully with better error checking) this will at least allow me to move forward and I thought others might find it useful.  If you have a better way please let  me know.

    While not ideal it allows me to write my code like this (Still in progress) which seems like it will suite my needs

    public static void AddTableDefinitionPart(WorksheetPart part)
            {
                TableDefinitionPart tableDefinitionPart1 = part.AddNewPart<TableDefinitionPart>("rId1"); //
                GenerateTableDefinitionPart1Content(tableDefinitionPart1);
            }
    
            // Generates content of tableDefinitionPart1.
            private static void GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1)
            {
                //information to be passed in or set via property. Hardcode for initial testing
                int startRow = 5;
                int startColumn = 4;
                int endRow = 8;
                int endColumn = 6;
                string tableName = "TestTable";
                string displayName = "TestTable";
                bool showTotalsRow = false;
                string tableStyleName = "TableStyleMedium2";
                bool tableStyleShowFirstColumn = false;
                bool tableStyleShowLastColumn = false;
                bool tableStyleShowRowStripes = true;
                bool tableStyleShowColumnStripes = false;
    
                //Internal Routine Machinations
                string tableRange = GetExcelA1Range(startRow, startColumn, endRow, endColumn);
                Table table1 = new Table() { Id = (UInt32Value)1U, Name = tableName, DisplayName = displayName, Reference = tableRange, TotalsRowShown = showTotalsRow };
                AutoFilter autoFilter1 = new AutoFilter() { Reference = tableRange };
    
                //Wrap column details in a for each loop to generate the appropriate columns:
                TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)3U };
                TableColumn tableColumn1 = new TableColumn() { Id = (UInt32Value)1U, Name = "Make" };
                TableColumn tableColumn2 = new TableColumn() { Id = (UInt32Value)2U, Name = "Miles" };
                TableColumn tableColumn3 = new TableColumn() { Id = (UInt32Value)3U, Name = "Cost", DataFormatId = (UInt32Value)0U };
    
                tableColumns1.Append(tableColumn1);
                tableColumns1.Append(tableColumn2);
                tableColumns1.Append(tableColumn3);
    
                //Apply Table Style information
                TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = tableStyleName, ShowFirstColumn = tableStyleShowFirstColumn, ShowLastColumn = tableStyleShowLastColumn, ShowRowStripes = tableStyleShowRowStripes, ShowColumnStripes = tableStyleShowColumnStripes };
    
                //Append Table Details
                table1.Append(autoFilter1);
                table1.Append(tableColumns1);
                table1.Append(tableStyleInfo1);
                
                //Apply Table to Definition Part.
                tableDefinitionPart1.Table = table1;
            }

    Thanks,

    Bill

    using System;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Linq;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    using System.Collections.Generic;
    using System.Text;
    
    namespace ConvertCellsToTable
    {
        class Program
        {
            private static string GetExcelA1Range(int startRow, int startColumn, int endRow, int endColumn)
            {            
                return GetExcelColumnA1Reference(startColumn) + startRow.ToString() + ":" + GetExcelColumnA1Reference(endColumn) + endRow.ToString();
            }
    
            private static string GetExcelColumnA1Reference(int columnNumber)
            {
                int dividend = columnNumber;
                string columnName = String.Empty;
                int modulo;
                
                while (dividend > 0)
                {
                    modulo = (dividend - 1) % 26;
                    columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                    dividend = (int)((dividend - modulo) / 26);
                }
    
                return columnName;
            }
    
            static void Main(string[] args)
            {
                int startRow;
                int startColumn;
                int endRow;
                int endColumn;
    
                bool isNum = int.TryParse(args[0], out startRow);
                isNum = int.TryParse(args[1], out startColumn);
                isNum = int.TryParse(args[2], out endRow);
                isNum = int.TryParse(args[3], out endColumn);
         
    
                Console.WriteLine("RangeFormatted for coordinates [{0},{1}], [{2},{3}]", startRow, startColumn, endRow, endColumn);
                Console.WriteLine("{0}", GetExcelA1Range(startRow, startColumn, endRow, endColumn));
                Console.WriteLine("Press Enter to continue");
                Console.ReadLine();
            }
        }
    }

    Monday, February 18, 2013 8:59 PM
  • Hi Bill,

    Thank you for sharing your solution which might be very helpful to other community members.

    Have a nice day.


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 20, 2013 1:04 PM
    Moderator