如何跳过某个范围内的空白列？

Question

我使用 C# 和 Interop.Excel.Range 返回工作表的使用的列范围，并将使用的列和行写入数据网格视图。某些列返回空白/空。

Microsoft.Office.Interop.Excel.Range excelRange = wWorksheet.UsedRange;

TabPage wTabPage = new TabPage(wWorksheet.Name.ToString());
DataGridView wDGV = new DataGridView();
wDGV.Dock = DockStyle.Fill;
wTabPage.Controls.Add(wDGV);
Sheets_TabControl.TabPages.Add(wTabPage);

DataTable dt = new DataTable();
DataRow wNewRow = null;

for (int i = 0; i < excelRange.Columns.Count; i++)
{
    dt.Columns.Add(new DataColumn(i.ToString(), typeof(string)));
}

string wValue = string.Empty;
Microsoft.Office.Interop.Excel.Range wRange = null;

for (int wRowIndex = 1; wRowIndex <= skipRows; wRowIndex++)
{
    wNewRow = dt.NewRow();

    foreach (DataColumn wColumn in dt.Columns)
    {
        wRange = excelRange.Cells[wRowIndex, wColumn.Ordinal + 1];

        if (wRange != null)
        {
            if (wRange.Value2 != null)
            {
                wValue = wRange.Value2.ToString();

                if (!string.IsNullOrEmpty(wValue))
                {
                    wNewRow.SetField(wColumn, wValue);
                }
            }

        }
    }

    dt.Rows.Add(wNewRow)
}

wDGV.DataSource = dt;

在写入包含数据的列时，是否有办法跳过或忽略任何空白列？

感谢您的帮助！

Answer 1

关键是使用 VBA 工作表函数 CountA，该函数返回一个值，指示给定范围内有多少个单元格有数据。基于此，您可以决定是否在 DataTable 中创建列。

由于表列和 Excel 范围列现在不同步，因此您需要使用在代码中设置的列名称作为相应 Excel 范围列的索引。

因此，重新调整代码可以得到：

Microsoft.Office.Interop.Excel.Range excelRange = wWorksheet.UsedRange;

TabPage wTabPage = new TabPage(wWorksheet.Name.ToString());
DataGridView wDGV = new DataGridView();
wDGV.Dock = DockStyle.Fill;
wTabPage.Controls.Add(wDGV);
Sheets_TabControl.TabPages.Add(wTabPage);

DataTable dt = new DataTable();
DataRow wNewRow = null;

// New code to create DataTable columns

for (int i = 1; i <= excelRange.Columns.Count; i++)
{
    // If the current column of cells does not contain any data, then don't add a column to the datatable

    if (xlSpreadsheet.App.WorksheetFunction.CountA(excelRange.Cells[Missing.Value, i]) != 0)
    {
        dt.Columns.Add(new DataColumn(i.ToString(), typeof(string)));
    }
}

string wValue = string.Empty;
Microsoft.Office.Interop.Excel.Range wRange = null;

for (int wRowIndex = 1; wRowIndex <= excelRange.Rows.Count; wRowIndex++)
{
    wNewRow = dt.NewRow();

    foreach (DataColumn wColumn in dt.Columns)
    {
        // Parse the column number we stored earlier as the column name

        int colNumber = 0;

        if (int.TryParse(wColumn.ColumnName, out colNumber))
        {
            // We use the parsed column number to index the column in the Excel range

            wRange = excelRange.Cells[wRowIndex, colNumber];

            if (wRange != null)
            {
                if (wRange.Value2 != null)
                {
                    wValue = wRange.Value2.ToString();

                    if (!string.IsNullOrEmpty(wValue))
                    {
                        wNewRow.SetField(wColumn, wValue);
                    }
                }
            }
        }
    }

    dt.Rows.Add(wNewRow)
}

wDGV.DataSource = dt;