Hi,
I facing problem to read excel sheet using oledb reader, first column not returning by reader and showing at last column head F14 and column has been empty.
but when i open excel sheet and double click on header row border for auto adjust and auto re size save excel and again read then all columns returning perfectly.
Excel sheet which I try to read that generates using php application and after download that excel we put on my application to read data from excel but above issue come.
I already did lots of R&D even i give width in excel sheet while generating excel using web application. My code is like this
private bool Import_To_Grid(string FilePath, string Extension)
{
try
{
string conStr = "";
switch (Extension)
{
case ".xls":
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
.ConnectionString;
break;
case ".xlsx":
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
.ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
connExcel.Open();
Exceldt = new DataTable();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(Exceldt);
connExcel.Close();
dgv_showexcel.DataSource = Exceldt;
BindDataToCmbClass();
cmb_userclass.SelectedIndex = 0;
return true;
}
catch (Exception ex) { MessageBox.Show("Its Error" + " " + ex.ToString()); return false; }
}
config
<add name="Excel03ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';" />
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';" />
[EDIT]
Error:
"No value given for one or more required parameters."
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ImageMetaDataApplier.Home.Import_To_Grid(String FilePath, String Extension) in h:\Meharwan Singh\Desktop Application\ImageMetaCreator\ImageMetaDataApplier\home.cs:line 71
[/EDIT]