How to filter hidden columns

[FreeVB]

Excuse me, if a column in EXCEL is set to hide, how can I skip this column when reading it?

[andersnm]

Hi,

reader.GetColumnWidth() returns the width of a column in character units. May be 0 if the column is hidden.

[FreeVB]

Thank you. This function can be implemented as expected.

[FreeVB]

hi,andersnm. Is there any good way to achieve the screenshot above? To remove the blank column and EXCEL header on the left

[andersnm]

Hi @FreeVB! The AsDataSet configuration object supports skipping columns using FilterColumn and skipping rows in two different ways using ReadHeaderRow and FilterRow. Please check the documentation at https://github.com/ExcelDataReader/ExcelDataReader for more details

[FreeVB]

The reason for raising this question is that the empty rows and columns on the top and left are not fixed. If they are fixed, you can use the method you mentioned earlier.

[FreeVB]

using var stream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); using var reader = ExcelReaderFactory.CreateReader(stream); // 使用AsDataSet扩展方法从读取器创建数据集 var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration() { UseColumnDataType = false, ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration() { UseHeaderRow = firstRowNamesCheckBox.Checked, FilterColumn = (columnReader, columnIndex) => string.IsNullOrEmpty(reader.GetValue(columnIndex)?.ToString()) && reader.GetColumnWidth(columnIndex) > 0, //>0:不显示隐藏列 ReadHeaderRow = (rowReader) => { rowCount = rowReader.RowCount; for (int i = 0; i < rowCount; i++) { rowCount--; if (rowReader[i] == null || rowReader.GetValue(1).ToString().Length == 0) { rowReader.Read(); } } }, }, }); return dataSet.Tables[0]; Currently, I am doing this, but this is not the result I want: Although the title above was ignored, the empty column on the left did not succeed, and the column name is also incorrect.

[andersnm]

Hi @FreeVB,

Sounds like you need to do some heuristics on the data to determine the actual extents. This is specialized to your situation, unfortunately I cannot provide general programming support.

[FreeVB]

` static int HeaderRowIndex { get; set; } public static DataTable Read(string documentPath) { using (var stream = File.Open(documentPath, FileMode.Open, FileAccess.Read)) using (var reader = ExcelReaderFactory.CreateReader(stream)) { DataSet dataSet = reader.AsDataSet(new ExcelDataSetConfiguration() { UseColumnDataType = true, ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration() { //EmptyColumnNamePrefix = "Column ", UseHeaderRow = true, FilterColumn = (reader, index) => { bool empty = false; string sheet = reader.Name; // Start reading the table from the beginning reader.Reset(); // EDIT: Head over the our current excel sheet while (reader.Name != sheet) if (!reader.NextResult()) break;

                    // Head to the first row with content
                    int rowIndex = 0;
                    while (rowIndex < HeaderRowIndex)
                    {
                        reader.Read();
                        rowIndex++;
                    }

                    while (reader.Read())
                    {
                        // 确定当前列是否为空
                        if (reader[index] == null || string.IsNullOrEmpty(reader[index].ToString()))
                            continue;

                        empty = true;
                        break;
                    }

                    reader.Reset();
                    reader.Read();
                    int rowCount = reader.RowCount;
                    for (int i = 0; i < rowCount; i++)
                    {
                        rowCount--;
                        if (reader[i] == null || reader.GetString(1)?.ToString().Length == 0)
                        {
                            reader.Read();
                        }
                    }
                    return empty;
                }
            }
        });
        return dataSet.Tables[0];
    }        
}

` hi,@andersnm,

The above code solves the problem I raised. However, when reading a column that does not normally have an empty column, it prompts an error. Can you help to see why? thank you!

[appel1]

What error do you get?