User-479973300 posted
I have upload one excel and convert into datatable using below code.
public DataSet FillExcells()
{
OleDbConnection con;
System.Data.DataTable dt = null;
string conn = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Request.Fullpath + "; Extended Properties='Excel 12.0;HDR=Yes'";
con = new OleDbConnection(conn);
try
{
con.Open();
DataSet ds = new DataSet();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
string getExcelSheetName = dt.Rows[i]["Table_Name"].ToString();
if (getExcelSheetName == "CRF$")
{
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
DataTable ExcelDataSet = new DataTable("SOURCE");
ExcelAdapter.Fill(ExcelDataSet);
SetValueForNull(ExcelDataSet);
//ds.Tables.Add(ExcelDataSet);
for (int j = ExcelDataSet.Rows.Count - 1; j >= 10; j--)
{
if (ExcelDataSet.Rows[j][2].ToString() == "" || ExcelDataSet.Rows[j][2].ToString() == "0")
{
ExcelDataSet.Rows[j].Delete();
}
}
ExcelDataSet.AcceptChanges();
ds.Tables.Add(ExcelDataSet);
}
}
return ds;
}
catch { throw; }
finally { con.Close(); }
}
but the datatable read only 255 characters how to fix it.
i have seen some solutions
Excel 97
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
Excel 2000 and later versions
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
second one regedit setting in my machine how to fix it.
