Read XLSX and get all Attributes from table to process them further

Tolst

New member
Joined
Apr 6, 2020
Messages
1
Programming Experience
Beginner
Hello,
first im a beginner in programming.
Im looking for a solution, for reading a XLSX and getting some important values, which i need to process them further.
My code can read at a spezial point, that i need.
it start at the Table beginn with "BG".
i need alle values under "BG" and other points like "ID" later.
But i dont really know how i can do that.

C#:
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\xyz\Downloads\TestSettings_Vorgaben.xlsx");
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

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

            var startRow = FindHeaderOfTable(xlRange, "BG");
            for (int i = startRow; i <= rowCount; i++)
            {
                for (int j = 1; j <= colCount; j++)
                {

                    if (j == 1)
                        Console.Write("\r\n");


                    if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                        Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");

                }
            }

            Console.ReadLine();
        }

        private static int FindHeaderOfTable(Range xlRange, string headerMark)
        {
            int startRow = -1;
            int rowCount = xlRange.Rows.Count;
            for (int i = 1; i <= rowCount; i++)
            {
                if (xlRange.Cells[i, 1]?.Value2?.ToString() == headerMark)
                {
                    startRow = i + 1;
                    break;
                }
            }

            return startRow;
        }

i think i need a class that looking like this?

C#:
  public class InputData
        {

            [DataMember(Name = "BG")]
            public int Size { get; set; }
            [DataMember(Name = "ID")]
            public int ID { get; set; }
  }

sorry for my bad english..
 
Last edited:
Assuming that each sheet in your workbook represents a table of data (e.g. you don't have multiple tables stored in a single sheet), then you can open an spreadsheet using the Microsoft OLEDB provider, and perform queries against it like a database. You wouldn't have to do what you are currently doing which is examining each cell to try to get the data.
 

Latest posts

Back
Top Bottom