How to read excel dynamically data and get combobox values

1
votes

I have to dynamically read about 2000 files in Excel xlsx format, to import data to database using csharp. It´s not tabular data, no head columns at all, the data has to be read dynamically, because the positions of the cells change according to the product data, we have labels cells to find the data. My biggest problem is I can not read the value of combo boxes cause positions, name and quantity of cells/combos will change depending the product, but the labels will remain the same. I tried to export to every single format type but it doesn´t works, the combo selected value after specific cell label dissapears. Any possible solution?

  • Ex. of my spreadsheet:
    • row A1: label|value|null value|label|value|merge cells label|value
    • row A2: label|producttypecombovalue
    • row A3: label|producttypecombovalue
    • row A4: label|producttypecombovalue...
    • row AX..: label|value|label|value|merge cells label|value

note that plus signs is dynamic rows

1
c#.netexcel
I would suggest reading spreadsheet in a DataTable as if it was a tabular data and then filter out what you need from there. - danish
I did not understand the format of the Excel. You can upload a picture? - dovid
lomed I uploaded a pic. and danish: I lose data when I read in a datatable - giuice

1 Answers

0
votes

If the combo boxes are all positioned over the cell to the right of the label then you can filter all of the combos on the sheet by looking at their location (ie. their TopLeftCell property, or Top and Left properties) and using the one which most closely matches the expected position of the one you're looking for.

In VBA (using Forms combos, which from your screenshot are what you're dealing with) - 

Sub tester()
    Dim o
    For Each o In ActiveSheet.OLEObjects
        Debug.Print o.Name, o.Top, o.Left, o.TopLeftCell.Address(), _
                    TypeName(o.Object)
    Next o
End Sub