Reading Excel File

Manjunatha Srinivasappa

c# code Browse excel file from folder and clicking on the button it should get details of specified column to the listbox in windows forms?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Garry Gibson12y ago

Hi Manjunatha here is another way that you can try:

// Open excel file which was selected from "openDialog" control.
var file = ExcelFile.Load(this.openDialog.FileName);
// Select required worksheet.
var sheet = file.Worksheets.ActiveWorksheet;
 
// Retrieve required cells.
var requiredCells = sheet.Cells.GetSubrangeRelative(
                        0,
                        ExcelColumnCollection.ColumnNameToIndex(this.txtColumnName.Text),
                        1,
                        sheet.Rows.Count - 1);
 
// Show cells data in "listBox" control.
foreach (var cell in requiredCells)
    if (cell != null &&
        cell.Value != null &&
        !string.IsNullOrEmpty(cell.Value.ToString()))
        this.listBox.Items.Add(cell.GetFormattedValue());

The code uses a .NET component which works on excel workbooks and as you can see you have a direct approach how to retrieve the required values. Note that you can use the same approach for reading both XLS and XLSX files (older and newer excels) in .NET without applying any changes to code.

Jignesh Trivedi13y ago
hi,

Please refer below link it might help you.
http://www.c-sharpcorner.com/uploadfile/mgold/howtooepnandreadanexcelspreadsheetinalistviewin.net11282005034134am/howtooepnandreadanexcelspreadsheetinalistviewin.net.aspx
http://www.dotnetspider.com/resources/5791-Open-Read-File-Win-Forms.aspx
Donald Green13y ago
Hello,
You can use some 3rd party components to read Excel data. For example in this code i used Elerium Excel .Net reader(it is not free) to read second column from first worksheet and display it values at Listbox:

ExcelWorkbook wb2 = null;
wb2 = ExcelWorkbook.ReadXLSX(@"C:/temp/test.xlsx");
if (wb2 != null)
{
ExcelWorksheet ws = wb2.Worksheets[0];
for (int i = 0; i < ws.Rows.Count; i++)
{
if (!ws.Rows[i].Cells[1].IsEmpty)
{
ListItem li = new ListItem();
li.Text = ws.Rows[i].Cells[1].ValueWithFormat();
ListBox1.Items.Add(li);
}
}
}
Arul Manivannan13y ago
Hi Manjunatha, Providing solutions to this task is like spoonfeeding. Just analyse and break it into small tasks. Assuming that you have placed the appropriate controls on the winforms like openfiledialogbox, listbox etc.

1. Fetch the excel data and populate it into datatable.
2. remove the unwanted columns from the datatable.
3. assign this datatable as datasource to that listbox. For fetching data from excel file. Please see http://www.dotnetfunda.com/codes/code972-read-a-excel-file-with-sheet-wise-into-dataset-using-csharp.aspx

Arul Manivannnan