正在从Excel中读取单元格

本文关键字：读取 单元格 Excel | 更新日期: 2023-09-27 18:27:37

我在阅读c#中的exel时遇到了一些问题。我有这个代码，我读取从A到X的每个单元格。

            int i = 1;
            int number;
            System.Array myvalues; string[] strArray;
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "X" + i.ToString());
            while(range.Count!=0)
            {
                    i++;
                    range = worksheet.get_Range("A" + i.ToString(), "X" + i.ToString());
                    myvalues = (System.Array)range.Cells.Value;
                    strArray = ConvertToStringArray(myvalues);
                    number = Convert.ToInt32(strArray[0]);
            }

我的问题是：如何根据"数字"值读取excel中接下来的4*"数字"行？

例如：

         A B C D E F G H I J
         a a a a a 1 a a a a

F的单元格值是1，所以我想读（G H i J）如果F的单元格值是2，我想读取（G H i J K L M N）

         A B C D E F G H I J K L M N
         a a a a a 2 a a a a a a a a

F的单元格值3:

        A B C D E F G H I J K L M N O P Q R
        a a a a a 3 a a a a a a a a a a a a

正在从Excel中读取单元格

代码：

int i = 1;
do
{
   i++;
   var range = worksheet.get_Range("A" + i.ToString(), "X" + i.ToString());
   if (range.Count != 0) 
   {
      var myvalues = (System.Array)range.Cells.Value;
      var strArray = ConvertToStringArray(myvalues);
      var number = Convert.ToInt32(strArray[0]);
      MyMethodReadRage(worksheet, number);
   }
} while(range.Count!=0);

读取范围的方法：

void MyMethodReadRage(Microsoft.Office.Interop.Excel.Worksheet worksheet, int numberRows)
{
   var range = worksheet.get_Range("A" + numberRows, "X" + (numberRows + 4));
   /*code*/
}

编辑

读取列"F"的值，将该值乘以4，并将前几列的数量相加（最多为"F"）。然后使用这个（未经验证的）函数来获取字母。

    /// <summary>
    /// http://www.freevbcode.com/ShowCode.asp?ID=4303
    /// </summary>
    private string ColumnLetter(int ColumnNumber)
    {
        if (ColumnNumber > 26)
        {
            return string.Format("{0}{1}", (char)(Convert.ToInt32((ColumnNumber - 1) / 26) + 64), (char)(((ColumnNumber - 1) % 26) + 65));
        }
        else
        {
            return string.Format("{0}", (char)(ColumnNumber + 64));
        }
    }

编辑II

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplicationExcelCeldas
{
    class Program
    { 
        private const int NumberPositionColumnF = 6;
        static void Main(string[] args)
        {
            Microsoft.Office.Interop.Excel.Worksheet worksheet = new Microsoft.Office.Interop.Excel.Worksheet();
            var i = 0;
            var test = new TestExcel();
            do
            {
                i++;
                var rangeF = worksheet.get_Range(string.Format("F{0}" , i));
                if (rangeF.Count == 0)
                {
                    break;
                }
                var values = test.GetCellsStringArray(rangeF);
                if (values.Count() > 0) 
                {
                    int number = 0;
                    if (int.TryParse(values[0], out number))
                    {
                        var rangeAll = worksheet.get_Range(
                            string.Format("A{0}" , i), 
                            string.Format("{0}{1}", test.ColumnLetter(Program.NumberPositionColumnF + (number * 4)), i));

                        /*
                         your code for work with rangeAll
                        */
                    }
                }
            } while (true);
        }
    }
    class TestExcel
    {
        internal string[] GetCellsStringArray(Microsoft.Office.Interop.Excel.Range range)
        {
            var myvalues = (System.Array)range.Cells.Value;
            return this.ConvertToStringArray(myvalues);
        }
        internal string[] ConvertToStringArray(System.Array values)
        {
            string[] theArray = new string[values.Length];
            for (int i = 1; i <= values.Length; i++)
            {
                if (values.GetValue(1, i) == null)
                    theArray[i - 1] = "";
                else
                    theArray[i - 1] = (string)values.GetValue(1, i).ToString();
            }
            return theArray;
        }
        internal string ColumnLetter(int columnNumber)
        {
            if (columnNumber > 26)
            {
                return string.Format("{0}{1}", (char)(Convert.ToInt32((columnNumber - 1) / 26) + 64), (char)(((columnNumber - 1) % 26) + 65));
            }
            else
            {
                return string.Format("{0}", (char)(columnNumber + 64));
            }
        }
    }
}

我不知道为什么要执行逐行读取。读取4行的范围，然后解析二维数组中的值，可能会更好吗？

看看这篇文章：使用Office互操作程序集更快地读取MS Excel

嗨，我刚刚创建了这段代码，它从第一列开始遍历所有行，你可以随心所欲地更改它（最新的Microsoft Lib互操作DLL.NET原生库），它工作得很好，希望它能帮助你摆脱

        int sheetIndex = 1; //PLEASE NOTE THIS LIB WORKS WITH NON-ZERO BASED INDEX
        string excelFilePath = "your_path/your_excel_file.xls";
        List<string> yourList = new List<string>();
        Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Open(excelFilePath);            
        Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[sheetIndex];
        Microsoft.Office.Interop.Excel.Range rangeSelection = worksheet.Columns[1];
        foreach (Microsoft.Office.Interop.Excel.Range row in rangeSelection.Rows)
        {
            Microsoft.Office.Interop.Excel.Range cell = (Microsoft.Office.Interop.Excel.Range)row.Cells[1, 1];
            if (cell.Value2 != null)
                list.Add(cell.Value2.ToString());
        }
相关文章：