Hi,
I am using the ClosedXML.Excel object in the C# application.
Using ClosedXML.Excel I need show the preview of an excel XLSX file uploaded in the GridView.
I can't because the csv file is generated empty.
The problem is the source excel file as it contains no-standard columns and rows.
If the no-standard columns and rows are deleted on the XLSX file, the preview works correctly.
I need to delete these no-standard columns and rows.
Please see image below.
Any suggestion?
No-standard columns on the XLSX excel file
using System.Data;
using System.IO;
using ClosedXML.Excel;
protected void Upload(object sender, EventArgs e)
{
if (!Directory.Exists(Server.MapPath("~/public/Files/")))
{
Directory.CreateDirectory(Server.MapPath("~/public/Files/"));
}
string filePath = Server.MapPath("~/public/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(filePath);
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
DataTable dt = new DataTable();
Guid id = Guid.NewGuid();
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++;
}
}
}
GridView1.Caption = "<b>Preview «" + Path.GetFileName(filePath) + "»</b><br /><br />";
GridView1.DataSource = dt;
GridView1.DataBind();
string sourceFile = Path.GetFileName(filePath);
string worksheetName = "table";
string targetFile = Server.MapPath("~/public/Files/target_" + DateTime.Now.ToString("yyyyMMdd") + ".csv");
StreamWriter 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() + ",";
}
wrtr.WriteLine(rowString);
}
wrtr.Close();
wrtr.Dispose();
}
}
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" runat="server" OnClick="Upload" />
<hr />
<asp:GridView runat="server" ID="GridView1"></asp:GridView>