如何用EPPlus移除Excel空白列及清除格式并处理中间空列

Got it, let's fix this up. Your current code handles trailing empty columns by limiting formatting cleanup to columns with headers, but we need to go a step further: identify and remove all middle columns that have no header (and are blank), then clear formatting across all remaining rows/columns.

Key Approach #

First, we need to:

1. Identify valid columns: Columns where the header (first row) has non-empty text, or columns that contain data even if the header is missing (per your requirement, we target columns with no header AND are fully blank for removal).
2. Remove invalid columns (middle + trailing) — critical to delete columns from right to left to avoid index shifting issues.
3. Clear formatting for all remaining cells in the worksheet.

Updated EPPlus Extension Class #

Here's the revised code that handles both middle blank columns and formatting cleanup:

using Microsoft.Extensions.Logging;
using OfficeOpenXml;
using System.Collections.Generic;
using System.Linq;

public static class EpPlusExtension
{
    private static ILogger _logger;

    // Set logger if needed (you can inject this or initialize it elsewhere)
    public static void SetLogger(ILogger logger)
    {
        _logger = logger;
    }

    // Get indices of columns that are valid (have header OR contain data)
    private static List<int> GetValidColumnIndices(this ExcelWorksheet sheet)
    {
        var validColumns = new List<int>();
        int startCol = sheet.Dimension.Start.Column;
        int endCol = sheet.Dimension.End.Column;
        int startRow = sheet.Dimension.Start.Row;
        int endRow = sheet.Dimension.End.Row;

        for (int col = startCol; col <= endCol; col++)
        {
            // Check if header cell has non-empty text
            var headerCell = sheet.Cells[startRow, col];
            bool hasHeader = !string.IsNullOrWhiteSpace(headerCell.Text);

            // If no header, check if any cell in the column has data
            bool hasData = false;
            if (!hasHeader)
            {
                for (int row = startRow + 1; row <= endRow; row++)
                {
                    var cell = sheet.Cells[row, col];
                    if (cell.Value != null && !string.IsNullOrWhiteSpace(cell.Text))
                    {
                        hasData = true;
                        break;
                    }
                }
            }

            // Keep the column if it has a header or contains data
            if (hasHeader || hasData)
            {
                validColumns.Add(col);
            }
        }

        return validColumns;
    }

    // Remove all blank columns (middle + trailing) with no header/data
    public static ExcelWorksheet RemoveBlankColumns(this ExcelWorksheet worksheet)
    {
        try
        {
            var validColumns = worksheet.GetValidColumnIndices();
            int startCol = worksheet.Dimension.Start.Column;
            int endCol = worksheet.Dimension.End.Column;

            // Delete columns from right to left to avoid index shifting errors
            for (int col = endCol; col >= startCol; col--)
            {
                if (!validColumns.Contains(col))
                {
                    worksheet.DeleteColumn(col);
                }
            }

            return worksheet;
        }
        catch (Exception ex)
        {
            _logger?.LogError($"Error removing blank columns: {ex.Message}");
            return worksheet;
        }
    }

    // Clear formatting for all remaining cells (including empty ones)
    public static ExcelWorksheet RemoveCellFormatter(this ExcelWorksheet worksheet)
    {
        try
        {
            if (worksheet.Dimension == null)
                return worksheet;

            int startRow = worksheet.Dimension.Start.Row;
            int endRow = worksheet.Dimension.End.Row;
            int startCol = worksheet.Dimension.Start.Column;
            int endCol = worksheet.Dimension.End.Column;

            for (int i = startRow; i <= endRow; i++)
            {
                for (int j = startCol; j <= endCol; j++)
                {
                    var cell = worksheet.Cells[i, j];
                    var cellValue = cell.Value;
                    
                    // Clear all formatting and reassign value if it exists
                    cell.Clear();
                    if (cellValue != null)
                    {
                        cell.Value = cellValue;
                    }
                }
            }

            return worksheet;
        }
        catch (Exception ex)
        {
            _logger?.LogError($"Error clearing cell formatting: {ex.Message}");
            return worksheet;
        }
    }

    // Convenience method: Run both cleanup steps in sequence
    public static ExcelWorksheet CleanupWorksheet(this ExcelWorksheet worksheet)
    {
        return worksheet.RemoveBlankColumns().RemoveCellFormatter();
    }
}

How to Use #

Call the combined method to handle everything in one go:

// Example usage with an ExcelPackage
using (var package = new ExcelPackage(new FileInfo("input-file.xlsx")))
{
    var worksheet = package.Workbook.Worksheets.First();
    worksheet.CleanupWorksheet();
    package.SaveAs(new FileInfo("cleaned-output.xlsx"));
}

Key Improvements #

• GetValidColumnIndices: Ensures we only keep columns that serve a purpose (have a header or contain data).
• RemoveBlankColumns: Deletes columns from right to left to avoid breaking index references during deletion.
• RemoveCellFormatter: Updated to clear formatting for empty cells too, ensuring a consistent clean state.
• CleanupWorksheet: A one-call method to run both operations in the correct order (remove columns first, then clear formatting).

内容的提问来源于stack exchange，提问作者Asif Iqbal