0

I have a table in an excel sheet with two columns and about 400 rows. Very simple. All I want is to now put that data into a scrollable list on a form in winforms.

I've been at this for awhile and have become frustrated. Everything I find is showing me how to add an item to a listview one by one, I assume there's an easier way.

This list is static, it will never need to change. How would I do this?

Logan Ness
  • 15
  • 3
  • Read the Excel worksheet into a `DataTable` and then set that `DataTable` as the `DataGridView` `DataSource`. – Loathing Jul 13 '21 at 17:07
  • If you want to use OleDb to read data from Excel WorkSheets and generate DataTables, see the notes and sample code [here](https://stackoverflow.com/a/54352568/7444103) and [here](https://stackoverflow.com/a/55240079/7444103). – Jimi Jul 13 '21 at 17:50
  • I don't really want to generate the list at runtime. I just want the data to be in the list. Does that make sense? I don't want it to open up a worksheet, read the data, and put it into a listview every time. The data never changes. If there weren't 400 items I'd do it manually on the designer – Logan Ness Jul 13 '21 at 18:22

2 Answers2

1

Holding data in source code is generally not a good idea. I think, there is no such thing as 'never changes'. I understand you don't want to implement the Excel-Stuff, but you may put the data in a Json, whatever... and use it as resource. Anyway, you can use Excel functions to prepare your data for copy-paste.

Excel Code gen Sorry for the german Excel, I think the english name of the function would be 'Concatenate'

then you copy the cells and paste it in your Code (assuming your variable is called 'list' and it's instantinated already...)

enter image description here

enter image description here

don't forget about the extra parenthesis in the excel function for strings!

dba
  • 1,159
  • 1
  • 14
  • 22
  • Oh my gosh you’re an absolute genius. Can’t believe I didn’t think of writing the code in excel and then copy pasting. Wow – Logan Ness Jul 14 '21 at 20:53
0

Try this:

Type ExcelType = Type.GetTypeFromProgID("Excel.Application");
        dynamic Excel = Activator.CreateInstance(ExcelType);
        int Index = 1;
        Excel.WorkBooks.Open("Your workbooks file location here...");

        do
        {
            listBox1.Items.Add(Excel.Range("A" + Index).Value);
            Index += 1;
        } while (Excel.Range("A" + Index).Value != null);
        Excel.Quit();
        Excel = null;
  • This will have to open the workbook every time the program runs right? Then read the data and write it at run time. Is there any way to, for lack of a better term, copy and paste the data from the file into a listview? Or import it as a data source? – Logan Ness Jul 13 '21 at 18:23