I need read Excel File xlsx data in ASP.Net using C# and ClosedXml library
Using ClosedXml library, the Excel file data can be read from the InputStream property and displayed on Web Page using GridView in ASP.Net using C#
The uploaded file is saved in txt format to be imported into a database.
My problem is that when the cells of the xlsx file are empty all the information moves one column to the left, causing in the txt file the skip initial sorting from xlsx file.
My code below
protected void btnUploadxlsx_Click(object sender, ImageClickEventArgs e)
{
string filePath = Server.MapPath("~/public/ExcelFiles/") +
Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(filePath);
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
DataTable dt = new DataTable();
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells())
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
sourceFile = Path.GetFileName(filePath);
targetFile = Server.MapPath("~/public/ExcelFiles/target_" +
DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt");
wrtr = new StreamWriter(targetFile);
for (int x = 0; x < dt.Rows.Count; x++)
{
string rowString = "";
for (int y = 0; y < dt.Columns.Count; y++)
{
rowString += dt.Rows[x][y].ToString().Replace(System.Environment.NewLine, " ").Replace("\r", "").Replace("\n", "") + ",";
}
wrtr.WriteLine(rowString);
}
wrtr.Close();
wrtr.Dispose();
}
}