<< Click here to Goto HomePage

     
     

Thursday, August 07, 2008

How to add data from Excel sheet to a C# program?

I have a coding article for all the geeks out there. I had a strange requirement to access data from a dynamic excel sheet on the fly and use it within my .net program.

Here's how it can be done easily in C#.


string strConn;

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=C:\\Master Report List.xls;" +

"Extended Properties=Excel 8.0;";

//You must use the $ after the object you reference in the spreadsheet

OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Production Reports$]", strConn);

DataSet myDataSet = new DataSet();

myCommand.Fill(myDataSet, "ExcelInfo");

DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;

DataGrid1.DataBind();

2 comments:

  1. Shitu said,

    Can we do this in JAVA???

    on 8/07/2008 5:51 PM


  2. JASON S said,

    Sure Shitu, here's how we do it...


    Steps to read an Excel File in a Java program are as follows:

    * Create a new Excel document reference: HSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));.
    * Refer to the sheet: By default, the first sheet in the Excel document is at reference 0: HSSFSheet sheet = workbook.getSheetAt(0);. A sheet can also be referred to by name.

    Let's assume that the Excel sheet has the default name "Sheet1". It can be referred to as follows:
    HSSFSheet sheet = workbook.getSheet("Sheet1");.

    * Refer to a row: HSSFRow row = sheet.getRow(0);.

    * Refer to a cell in the row: HSSFCell cell = row.getCell((short)0);.

    * Get the values in that cell: cell.getStringCellValue();

    Simple right?

    on 8/08/2008 9:38 AM