CONVERTING EXCEL TO DATASET C#

macupryk · January 4th, 2005, 10:07 PM

I NEED TO CONVERT SELECT * FROM SampleSheet$A1:D20
                                       TO INFINITE
IS IT POSSIBLE?

THE FOLLOWING CODE HAS TO BE EXECUTED. IS THERE A WAY AROUND?
PLEASE GET BACK TO ME ASAP. THANKS.

private void Page_Load(object sender, System.EventArgs e)
          {
               // Put user code to initialize the page here
               // Create connection string variable. Modify the "Data Source"
               // parameter as appropriate for your environment.
               String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + Server.MapPath("../ExcelData.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 ExcelData$", 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.
               DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
               DataGrid1.DataBind();

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

Knowledge is to be shared.

cikaPero · March 9th, 2010, 11:22 AM

Hi,

you can import Excel to DataSet easily with GemBox.Spreadsheet C# Excel component.

Here is a code snippet:

Code:

private void Page_Load(object sender, System.EventArgs e)
{
	var ef = new ExcelFile();
	ef.LoadXls(Server.MapPath("../ExcelData.xls"));

	// Initialize DataTable (skip this if you have DataTable definition)
	var dt = new DataTable();
	dt.Columns.Add("name", typeof(string));
	dt.Columns.Add("birth", typeof(DateTime));

	// Extract data to DataTable
	ws.ExtractToDataTable(dt, ws.Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);

	DataGrid1.DataSource = dt.DefaultView;
	DataGrid1.DataBind();
}