Posts Tagged as "Read xls file using C#"

Microsoft.Office.Interop.Excel.Application excelApp;

Microsoft.Office.Interop.Excel.Workbook workbook;

Microsoft.Office.Interop.Excel.Worksheet worksheet;

Microsoft.Office.Interop.Excel.Range range;

excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

// Opening Excel file

workbook = excelApp.Workbooks.Open(txtPath.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);

range = worksheet.UsedRange;

int column = 0;

int row = 0;            

System.Data.DataTable dt = new System.Data.DataTable();

dt.Columns.Add("CustomerID");

dt.Columns.Add("CompanyName");

dt.Columns.Add("ContactName");

for (row = 2; row <= range.Rows.Count; row++)

{

    DataRow dr = dt.NewRow();                               

    for (column = 1; column <= range.Columns.Count; column++)

    {

        dr[column - 1] = (range.Cells[row, column] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();

    }

    dt.Rows.Add(dr);                

}

workbook.Close(true, null, null);

excelApp.Quit();

// Binding to DataGridView

dataGridView1.DataSource = dt;

using System.Data;

using System.Data.OleDb;

protected void Page_Load(object sender, EventArgs e)

{

    string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", Server.MapPath("Customers.xlsx"));

    string query = String.Format("select * from [{0}$]", "Sheet1");

    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);

    DataSet dataSet = new DataSet();

    dataAdapter.Fill(dataSet);

    GridView1.DataSource = dataSet.Tables[0];

    GridView1.DataBind();

}

string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", txtPath.Text);

string query = String.Format("select * from [{0}$]", "Sheet1");

OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);

DataSet dataSet = new DataSet();

dataAdapter.Fill(dataSet);

dataGridView1.DataSource = dataSet.Tables[0];