Dear Team,
My Requirement is -
1. I have to export Datatable to .xlsx file.
2. For all Date Columns in Datatable, the excel cell should be of "Date" Format instead of Text or General.
This is because, the user can filter data according to date as required.
3. For all Time Columns in Datatable, the excel cell should be of "Time" Format instead of Text or General.
I have tried using the following, but I am not proper result.
Please guide.
My method -
---------------
private void ExportToXLSX(DataTable table, string s)
{
try
{
DataTable dtdatetimenew = new DataTable();
string currdate = DateTime.Now.ToString("dd-MMM-yyyy");
string fname = currdate + "_" + s;
if (table != null)
{
wb.Worksheets.Add(table, "DumpAndCount");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}
catch (Exception err) { err.Message.ToString(); }
}
{
try
{
DataTable dtdatetimenew = new DataTable();
string currdate = DateTime.Now.ToString("dd-MMM-yyyy");
string fname = currdate + "_" + s;
if (table != null)
{
using (XLWorkbook wb = new XLWorkbook())
{
DataColumnCollection columns = table.Columns;
{
DataColumnCollection columns = table.Columns;
foreach (DataColumn dc in table.Columns)
{
if (dc.ColumnName.Contains("Date"))
{
{
if (dc.ColumnName.Contains("Date"))
{
wb.Style.NumberFormat.Format = "mm/dd/yyyy";
}
if (dc.ColumnName.Contains("Time"))
{
wb.Style.NumberFormat.Format = "hh:mm:ss";
}
}
}
if (dc.ColumnName.Contains("Time"))
{
wb.Style.NumberFormat.Format = "hh:mm:ss";
}
}
wb.Worksheets.Add(table, "DumpAndCount");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename='" + fname + "'.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
catch (Exception err) { err.Message.ToString(); }
}
Leon DPosted Mar 19, 2019, 10:27 PM
Riddhi ValechaPosted Mar 19, 2019, 5:20 AM
Dear Leon D,
I tried using Spire.XLS dll.
{
try
{
DataTable dtdatetimenew = new DataTable();
string currdate = DateTime.Now.ToString("dd-MMM-yyyy");
string fname = currdate + "_" + s;
{
Worksheet sheet = wb.Worksheets[0];
sheet.InsertDataTable(table, true, 1, 1);
DataColumnCollection columns = table.Columns;
for(int i = 0; i< columns.Count ; i++)
{
if(columns[i].ToString().Contains("Date"))
{
sheet.Columns[i].NumberFormat = "dd-MMM-yyyy";
}
if (columns[i].ToString().Contains("Time"))
{
sheet.Columns[i].NumberFormat = "hh:mm:ss";
}
}
wb.SaveToHttpResponse(fname + ".xls", Response, HttpContentType.Excel2007);
}
catch (Exception err) { err.Message.ToString(); }
}
Leon DPosted Mar 18, 2019, 8:38 PM