read multiple excel sheets into C#

Question

Excel 2003 = literature.xls
Sheets: LineCards, Data, Brochures, and Tips

    using System;

using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.OleDb;

public partial class literature : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { LoadGrid(0); } protected void grd_PageIndexChanging(object sender, GridViewPageEventArgs e) { LoadGrid(e.NewPageIndex); }

void LoadGrid(int LineCards)
{
    String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("literature\\literature.xls") + ";" +
"Extended Properties=Excel 8.0;";

    // Create connection object by using the preceding connection string.
    OleDbConnection objConn = new OleDbConnection(sConnectionString);

    // Open connection with the database.
    objConn.Open();

    // The code to follow uses a SQL SELECT command to display the data from the worksheet.

    // Create new OleDbCommand to return data from worksheet.
    OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [LineCards$]", objConn);

    // Create new OleDbDataAdapter that is used to build a DataSet
    // based on the preceding SQL SELECT statement.
    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

    // Pass the Select command to the adapter.
    objAdapter1.SelectCommand = objCmdSelect;

    // Create new DataSet to hold information from the worksheet.
    DataSet objDataset1 = new DataSet();

    // Fill the DataSet with the information from the worksheet.
    objAdapter1.Fill(objDataset1, "XLData");

    // Bind data to DataGrid control.
    grd.DataSource = objDataset1.Tables[0].DefaultView;
    grd.PageIndex = LineCards;
    grd.DataBind();

    // Clean up objects.
    objConn.Close();
}

}for your assistance.

Answer 1

You're going to need to perform this code once for each file. Essentially, just create a function to take care of it, and just pass the function your connectionString (or whatever element of it changes). This assumes that all of the files you are querying have the correct data you are looking for.

Here is an example of the function, and how to call it.

//I don't know what values LineCards is supposed to be, so I am just passing 5, 6, and 7.
//Put these calls where your LoadGrid() call is currently.

assignExcelSheetToGrid (Server.MapPath("literature\\literature.xls"), grd, 5);
assignExcelSheetToGrid (Server.MapPath("literature\\literature2.xls"), grd2, 6);
assignExcelSheetToGrid (Server.MapPath("literature\\literature3.xls"), grd3, 7);

And the function.... I assume your first function worked, so I am just re-using your code. If your original function worked for one grid, this code should work for any number.

function assignExcelSheetToGrid(string thePath, YOURGRIDTYPE theGrid, int LineCards){
    ///This replaces LoadGrid function
    //Make sure you change YOURGRIDTYPE (Above) to the type of grid you are passing

    String theConnString= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + thePath + ";Extended Properties=Excel 8.0;";

    OleDbConnection objConn = new OleDbConnection(theConnString);
    objConn.Open();

    OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [LineCards$]", objConn);
    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

    objAdapter1.SelectCommand = objCmdSelect;
    DataSet objDataset1 = new DataSet();
    objAdapter1.Fill(objDataset1, "XLData");

    theGrid.DataSource = objDataset1.Tables[0].DefaultView;
    theGrid.PageIndex = LineCards;
    theGrid.DataBind();

    objConn.Close();
}

UPDATE

function now takes the file path instead of the entire connection string.