Resize Excel Table

Tags: Office, Excel, Open XML, Open XML SDK, C#, .Net

I’m going to show you how you can resize an existing Excel table. Let’s assume you have the following table in excel (A1:C6):


image


And you want to resize the table to a table like this (A1:C10[CN]):


image


Here are the steps that need to be performed:

- Update the SheetDimension to A2:C[N]
- In SheetData duplicate 2nd Row N Times (first row are headers; this is optional)
- Update the TableDefinitionPart

Here’s a sample code that could do that:


        
        int rowsCount = 9; 
        // update table definition 
        // here here I'm assuming you have the worksheetPart already. TableDefinitionPart
        tableDefinitionPart = worksheetPart.TableDefinitionParts.FirstOrDefault(); 
        tableDefinitionPart.UpdateTableDefinitionPart(rowsCount);
        // update content worksheetPart.Worksheet.UpdateRangeWorksheet(rowsCount);
        
    


Here’s the class with the extension methods that do the actual work:


        
         public static class RowExtensions
        {
            // Generates content of tableDefinitionPart. 
            public static void UpdateTableDefinitionPart(this TableDefinitionPart tableDefinitionPart, int rowsCount)
            {
                tableDefinitionPart.Table.Reference = string.Format("A1:C{0}",
                rowsCount + 1);
                tableDefinitionPart.Table.AutoFilter.Reference = string.Format("A1:C{0}", rowsCount + 1);
            }
            
            // Generates content of worksheetPart. 
            public static void UpdateRangeWorksheet(this Worksheet worksheet, int rowsCount)
            {
                worksheet.SheetDimension.Reference
    = string.Format("A1:C{0}", rowsCount + 1); var sheetData = worksheet.GetFirstChild < SheetData > (); const int rowToBeCopiedRowIndex
    = 2; if (sheetData.Elements & lt; Row & gt; ().Any(r = > r.RowIndex == rowToBeCopiedRowIndex)) {
                    Row rowToBeCopied = sheetData.Elements < Row > ().First(r
    = > r.RowIndex == rowToBeCopiedRowIndex); for (int i = 1; i & lt; rowsCount; i++) {
                        var newRow = (Row)rowToBeCopied.CloneNode(true);
                        long newRowIndex = i + rowToBeCopiedRowIndex; newRow.RowIndex = (UInt32Value)newRowIndex; foreach (Cell cell in newRow.Elements & lt; Cell & gt; ())
            { // Update the references for reserved cells. string cellReference = cell.CellReference.Value; cell.CellReference = new
                            StringValue(cellReference.Replace(rowToBeCopied.RowIndex.Value.ToString(), newRowIndex.ToString())); // update formulas if
                            (cell.CellFormula != null) { // update formula addresses string formulaText = cell.CellFormula.Text; var builder = new StringBuilder(formulaText);
                                MatchCollection matchCollection = Regex.Matches(formulaText, "(\\$[A-Z])\\w+"); foreach (string match in matchCollection.OfType & lt; Match & gt; ().Where(m
                                      = > m.Success).Select(m = > m.Value).Distinct()) {
                                    builder.Replace(match, match.Replace(rowToBeCopied.RowIndex.Value.ToString(),
    newRowIndex.ToString()));
                                }
                                cell.CellFormula = new CellFormula(builder.ToString());
                            }
                        }
                        sheetData.Append(newRow);
                    }
                }
            }
        }
        
    


It fixes cells references:


        
            foreach (Cell cell in newRow.Elements<Cell>()) 
            { 
                // Update the references for reserved cells. 
                string cellReference = cell.CellReference.Value; 
                cell.CellReference = new StringValue(cellReference.Replace(rowToBeCopied.RowIndex.Value.ToString(), newRowIndex.ToString())); 
                ... 
            }
        
    


And it fixes simple possible issues in formulas:


        
            foreach (Cell cell in newRow.Elements<Cell>()) 
            { 
                ... 
                // update formulas 
                if (cell.CellFormula != null) { 
                    // update formula addresses
                    string formulaText = cell.CellFormula.Text; 
                    var builder = new StringBuilder(formulaText); 
                    MatchCollection  matchCollection = Regex.Matches(formulaText, "(\\$[A-Z])\\w+"); 
                    foreach (string match in matchCollection.OfType<Match>().Where(m => m.Success).Select( m => m.Value).Distinct()) 
                    { 
                        builder.Replace(match, match.Replace(rowToBeCopied.RowIndex.Value.ToString(), newRowIndex.ToString())); 
                    } 
                    
                    cell.CellFormula = new CellFormula(builder.ToString()); 
                 } 
             }
         
     


Sources:

No Comments

Add a Comment