Open XML：使用列索引删除整个 Excel 列

使用 Open XML 删除 Excel 列的方法

csharp
string filePath = "path_to_excel_file.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
{
    // 加载 Excel 文件的 XML 内容
    WorkbookPart workbookPart = document.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
    SheetData sheetData = worksheetPart.Worksheet.Elements().First();
    // ...
}

csharp
int columnIndex = 2; // 要删除的列索引，这里以第二列为例
foreach (Row row in sheetData.Elements())
{
    Cell cell = row.Elements().FirstOrDefault(c => GetColumnIndex(c.CellReference.Value) == columnIndex);
    if (cell != null)
    {
        // 删除单元格数据
        cell.Remove();
    }
}

csharp
worksheetPart.Worksheet.Save();
workbookPart.Workbook.Save();
document.Close();

csharp
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Linq;
namespace ExcelColumnDeletion
{
    class Program
    {
        static void Main(string[] args)
        {
            string filePath = "path_to_excel_file.xlsx";
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
            {
                // 加载 Excel 文件的 XML 内容
                WorkbookPart workbookPart = document.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                SheetData sheetData = worksheetPart.Worksheet.Elements().First();
                int columnIndex = 2; // 要删除的列索引，这里以第二列为例
                foreach (Row row in sheetData.Elements())
                {
                    Cell cell = row.Elements().FirstOrDefault(c => GetColumnIndex(c.CellReference.Value) == columnIndex);
                    if (cell != null)
                    {
                        // 删除单元格数据
                        cell.Remove();
                    }
                }
                worksheetPart.Worksheet.Save();
                workbookPart.Workbook.Save();
                document.Close();
            }
        }
        // 获取单元格的列索引
        static int GetColumnIndex(string cellReference)
        {
            string columnName = new string(cellReference.Where(c => char.IsLetter(c)).ToArray());
            int columnIndex = 0;
            int factor = 1;
            for (int i = columnName.Length - 1; i >= 0; i--)
            {
                columnIndex += factor * (columnName[i] - 'A' + 1);
                factor *= 26;
            }
            return columnIndex;
        }
    }
}