Excel Interop只读筛选行

Excel Interop read only filtered rows(Excel Interop只读筛选行)

本文介绍了Excel Interop只读筛选行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

原始未筛选表

筛选表

我正在尝试使用Interop.Excel读取.xlsx文件。当我将xlRange变量设置为仅显示筛选出的单元格(可见)时,它似乎有一个奇怪的行为:

Excel.Range xlRange = xlWorksheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing);

调试:

当表未筛选时:

xlRange.Count:15//表中元素总数

rowCount:5//包括标题

筛选表时:

xlRange.Count:9//这是正确的

行数:1//应为3(包括表头)

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(Directory.GetCurrentDirectory() + "\Example.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing);

int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;

//iterate over the rows and columns and print to the console as it appears in the file
//excel is not zero based!!
for (int i = 1; i <= rowCount; i++)
{
    for (int j = 1; j <= colCount; j++)
    {
        //new line
        if (j == 1)
            Console.Write("
");

        //write the value to the console
        if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
            Console.Write(xlRange.Cells[i, j].Value2.ToString() + "	");
    }
}

记住xlRange.Count是9,我应该能够手动访问所有3行,而不考虑rowCount变量,但xlRange似乎是相同的原始非筛选范围:

Console.WriteLine(xlRange.Cells[1, 1]);//Writes ID, Correct
Console.WriteLine(xlRange.Cells[2, 1]);//Writes 1, Should be 2
Console.WriteLine(xlRange.Cells[3, 1]);//Writes 2, Should be 4
Console.WriteLine(xlRange.Cells[4, 1]);//Writes 3, Should not be able to acces this element element at all because xlRange.Count is 9

推荐答案

我怀疑您想要的是迭代.Rows属性,而不是关于行/列的文字。大概是这样的:

foreach (Excel.Range row in xlRange.Rows)
{
    for (int j = 1; j <= colCount; j++)
    {
        //write the value to the console
        if (row.Cells[1, j] != null && row.Cells[1, j].Value2 != null)
            Console.Write(row.Cells[1, j].Value2.ToString() + "	");
    }

    Console.WriteLine();
}

当您在某个范围内指定行、列时,我怀疑它将精确到该行(相对于该范围)。

试一试,让我知道。

这篇关于Excel Interop只读筛选行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:Excel Interop只读筛选行

基础教程推荐