Dear sir,
Your code with article Read and import excel data to datatable using closed XML in asp. Net with c # is not working in QA server.I have included all dll in bin.
The error coming is system.io.fileformatexception and file contains corrupted data
It is working only for extension. Xlsx file not. Xls file
I have copied each and every thing and there is no problem in my excel file. Please suggest and help me
//Here is my code on button click
protected void ImportExcel(object sender, EventArgs e)
{
//Open the Excel file using ClosedXML.
using (XLWorkbook workBook = new XLWorkbook(FileUpload1.PostedFile.InputStream))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
//Create a new DataTable.
DataTable dt = new DataTable();
//Loop through the Worksheet rows.
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
if (!string.IsNullOrEmpty(cell.Value.ToString()))
{
dt.Columns.Add(cell.Value.ToString());
}
else
{
break;
}
}
firstRow = false;
}
else
{
int i = 0;
DataRow toInsert = dt.NewRow();
foreach (IXLCell cell in row.Cells(1, dt.Columns.Count))
{
try
{
toInsert[i] = cell.Value.ToString();
}
catch (Exception ex)
{
}
i++;
}
dt.Rows.Add(toInsert);
}
}
DataTable dt1 = dt;
string offriddt = "";
string BatchIddt = "";
string offridgv = "";
string BatchIdgv = "";
for (int i = 0; i < dt1.Rows.Count; i++)
{
offriddt = dt1.Rows[i]["OfferId"].ToString();
BatchIddt = dt1.Rows[i]["BatchId"].ToString();
bool result = false;
for (int j = 0; j < gvoffrdtls.Rows.Count; j++)
{
offridgv = (gvoffrdtls.Rows[j].FindControl("hdnoffid") as HiddenField).Value;
BatchIdgv = (gvoffrdtls.Rows[j].FindControl("hdnBatchId") as HiddenField).Value;
if (offriddt == offridgv && BatchIddt == BatchIdgv)
{
((TextBox)gvoffrdtls.Rows[j].FindControl("txtsono")).Text = dt1.Rows[i]["OrderNo"].ToString();
((TextBox)gvoffrdtls.Rows[j].FindControl("txtSoitem")).Text = dt1.Rows[i]["OrderItem"].ToString();
((TextBox)gvoffrdtls.Rows[j].FindControl("txtpcrno")).Text = dt1.Rows[i]["PCRNo"].ToString();
result = true; ;
}
if (result)
break;
}
}
}
}
}
