CODEWITHSUNDEEP
c#excelwinformsexcel-interop
Anonymous Person
Anonymous Person

Reputation: 1538

Adding a column from Excel sheet to WinForms

I am at my wit's end, trying to fix this!

I have two (seemingly related) issues:

Issue 1 - I have an Excel sheet with one worksheet. I have a Browse button in Form1, that browses for an Excel sheet. All this works well. When I click the OK button, it opens up the Form2, which is also correct. In the Form2_Load event, I have the code written to handle the logic I need to perform with the Excel sheet (which is the issue #2). What happens is, when I click Ok on the Browse window, it also opens up the Excel sheet, which I do not want. Here's the code for the whole thing:

Excel.Application xlAppl = new Excel.Application();

xlAppl.Visible = true;

Excel.Workbook myWorkbook = xlAppl.Workbooks.Open(Form1.globalClass.GlobalVar); //Gets the path to the excel sheet

Excel.Worksheet myWorkSheet = (Excel.Worksheet)myWorkbook.Worksheets[1]; 
Excel.Range range = (Excel.Range)myWorkSheet.Columns[1];

if(myWorkSheet != null) //This is the logic for problem#2 so please discard this for now.

I basically do not want to have the Excel sheet opened. Can you see what the issue is?

Issue 2 - After I browse for the Excel sheet, I want to be able to import the first column in the Excel sheet, in a listBox, in Form2. Here's what I've tried:

if(myWorkSheet != null)
{
    int rowCount = myWorkSheet.UsedRange.Rows.Count; //Will count the number of rows of cells with data in the first column

    listBox_CaseNumbers.BeginUpdate();

    for (int i = 1; i <= rowCount; i++)
    {
        try
        {

            //I am not able to get the logic here!

        }

        catch (Exception ex)
        {
             MessageBox.Show(ex.Message + "\n" + ex.StackTrace);
        }
    }
}

Please let me know if you need more information from me.

Upvotes: 0

Views: 267

Answers (2)

Михаил
Михаил

Reputation: 101

Issue 1 Excel sheet will always open. You can show it xlAppl.Visible = true; or not xlAppl.Visible = false; In the end, you need to close the workbook Excel: xlAppl.Quit();

Issue 2 listBox_CaseNumbers.Items.Add(myWorkSheet.Cells[i, indexColumn].Value);

Upvotes: 0

Jonathan Willcock
Jonathan Willcock

Reputation: 5255

The first issue is easy. If you do not want to see the Excel Sheet then don't set xlApp.Visible = true.

But in fact there is a much easier way to do what you want without loading Excel at all. I understand from your question, that all you want to do is to extract data from the Spreadsheet? In which case use OleDbDataAdapter instead, like this:

string xlConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=YourXLFilePathAndName;Extended Properties='Excel 8.0;HDR=No;IMEX=1';";

var xlConn = new OleDbConnection(xlConnStr);
var da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", xlConn);
var xlDT = new DataTable();
da.Fill(xlDT);

You will now have a DataTable containing rows and columns. Assuming your spreadsheet only has data in one column, then the DataTable also has only one column. To get the values simply iterate through the rows.

One thing to note: if your data has a column header, then you need to set HDR=Yes in the connection string.

Upvotes: 1

Related Questions