Hide a columns on a .xlsx file that have different sizes

Question

I'm Trying to hide a column on a .xlsx file that already exist from a .asp page using open XML, i found this code that works for hiding columns

    public void HideColumn(string FileName, UInt32Value columnNumber)
    {
      using (SpreadsheetDocument document = SpreadsheetDocument.Open(FileName, true))
      {
        IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Hoja1");
        if (sheets.Count() == 0)
        {
          // The specified worksheet does not exist.
          return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
        Worksheet worksheet = worksheetPart.Worksheet;
        Columns columns1 = GenerateColumns(columnNumber);
        // The column element is behind the SheetFormatProperties element.
        worksheet.InsertAfter(columns1, worksheet.SheetFormatProperties);
        worksheet.Save();
      }
    }

    // Creates an Columns instance and adds its children.
    public Columns GenerateColumns(UInt32Value ColumnIndex)
    {
      Columns columns1 = new Columns();
      Column column1 = new Column() { Min = ColumnIndex, Max = ColumnIndex, Width = 0D, Hidden = true, CustomWidth = true };
      columns1.Append(column1);
      return columns1;
    }

i just call HideColumn(sFile, 1) sending the file name and the column id, but this only works on files were the columns have original size if for some reason i change the column size this doesn't work anymore .

How i can hide columns from a .xlsx file where the columns have different sizes?

An .xlsx file whit columns without sizes looks like this

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet
    xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac"
    xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <dimension ref="A1:E1"/>
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection activeCell="A2" sqref="A2"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr baseColWidth="10" defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    <sheetData>
        <row r="1" spans="1:5" x14ac:dyDescent="0.25">
            <c r="A1">
                <v>1</v>
            </c>
            <c r="B1">
                <v>2</v>
            </c>
            <c r="C1">
                <v>3</v>
            </c>
            <c r="D1">
                <v>4</v>
            </c>
            <c r="E1">
                <v>5</v>
            </c>
        </row>
    </sheetData>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

And .xlsx files with columns with different sizes looks like this

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet
    xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac"
    xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <dimension ref="A1:E1"/>
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection activeCell="E4" sqref="E4"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr baseColWidth="10" defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    <cols>
        <col min="1" max="1" width="17.5703125" customWidth="1"/>
        <col min="2" max="2" width="20" customWidth="1"/>
        <col min="3" max="3" width="20.42578125" customWidth="1"/>
        <col min="4" max="4" width="19" customWidth="1"/>
        <col min="5" max="5" width="18" customWidth="1"/>
    </cols>
    <sheetData>
        <row r="1" spans="1:5" x14ac:dyDescent="0.25">
            <c r="A1">
                <v>1</v>
            </c>
            <c r="B1">
                <v>2</v>
            </c>
            <c r="C1">
                <v>3</v>
            </c>
            <c r="D1">
                <v>4</v>
            </c>
            <c r="E1">
                <v>5</v>
            </c>
        </row>
    </sheetData>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

I think the problem is that the second type of files have this section on them

<cols>
    <col min="1" max="1" width="17.5703125" customWidth="1"/>
    <col min="2" max="2" width="20" customWidth="1"/>
    <col min="3" max="3" width="20.42578125" customWidth="1"/>
    <col min="4" max="4" width="19" customWidth="1"/>
    <col min="5" max="5" width="18" customWidth="1"/>
</cols>

and this section is the one causing the problem

Answer 1

If the columns already exist, loop through them to set/create the hidden attribute:

foreach (Column column in worksheet.Descendants<Column>())
{
    // TODO: filter so that you don't hide every column.
    column.SetAttribute(new OpenXmlAttribute("hidden", "1"));
}