CODEWITHSUNDEEP
.netopenxmlopenxml-sdk
Diane
Diane

Reputation: 428

How to insert columns to Excel Worksheet with OpenXML

I'm provided with an Excel workbook (xlsx) to generate reports. There is a requirment that i have to insert columns in some cases. Here is the code i'm trying to do. It runs fine but does not insert the new column in the WorkSheet: How can i accomplish this?

using (SpreadsheetDocument document = SpreadsheetDocument.Open(outputPath, true)){
    Sheet sheet1 = document.WorkbookPart.Workbook.Descendants<Sheet>().Single( s => s.Name == "Balance" );

    Worksheet workSheet1 = ( (WorksheetPart)document.WorkbookPart.GetPartById( sheet2.Id ) ).Worksheet;

    Columns cs = workSheet1.GetFirstChild<Columns>();                          
    Column c = new Column()
    {
        Min = (UInt32Value)1U,
        Max = (UInt32Value)1U,
        Width = 44.33203125D,
        CustomWidth = true
    };
    cs.Append( c );
}

Upvotes: 3

Views: 5920

Answers (1)

shunty
shunty

Reputation: 3768

What your code does is adjust the dimensions of the first column. Not insert a new one. From my understanding of OpenXML you will need to go through the rows collection and adjust the cell reference of every single Cell.

This will involve iterating through the rows collection then for each row iterate through the cells collection. Get the cell reference for each cell which will be in the form "B1", parse it, increment the column part to produce eg "C1". This will still leave you with the issue of what to do about updating formula references. Good luck with that! :-)

Three alternatives I can think of:

1) Try something like ClosedXML - I've never used this so don't know how well it would work.

2) Write an Excel add-in using VSTO rather than OpenXML. But this would seem to defeat the object of OpenXML in the first place.

3) Think laterally. Create a new sheet in the workbook, add the info for you first column as required and for each cell in the original "Balance" sheet create a new cell in the new sheet containing a formula along the lines of "Balance!A1".
This would be my preferred approach as it leaves the source sheet intact in its original format and your reports can read from the new sheet into which you could also add any other calculated columns you want.
Creating the new sheet gives you much more flexibility.

You can add a formula cell similar to:

string sourcecell = ...; // read this from the cell reference of the original cell
string newcell = ...;  // parse the original cell ref and increment the column

row.Append(new Cell(
    new CellFormula() { 
        FormulaType = CellFormulaValues.Normal, 
        Text = string.Format("Balance!({0})", sourcecell)
    },
    new CellValue("0.00")
    ) { CellReference = newcell, StyleIndex = 0 }
);

I found these 2 links helpful when we were doing something similar recently:
Creating an OpenXML spreadsheet from scratch
Styling an OpenXML spreadsheet

Upvotes: 3

Related Questions