Formatting and overriding the default cell format of date & time while writing it in an Excel Sheet

Question

We are capturing the entire table data inside the $("#dvtablerptTDetails")[0].innerHTML containing all the data and the same will be saved in the session and later converted to string & sent to Response.Write() that would later create an Excel file containing data of filetype xlsx

Here the requirement is to override the default cell format when writing the data, especially for the date & time format of an individual cell.

By default, when saved and viewed,
Cell Format will be in dd/mm/yyyy hh:mm
But Expected Cell Format should be in dd-MMM-yyyy HH:mm:ss

So tried to modify data prefixing and suffixing with Dot(.) or At the rate(@). But it worked printing as per our requirement. But unable to escape special characters. Since it writes exactly as it is.

Below here is the partial snippets

DateTimeUtility objDTU = new DateTimeUtility();
RegisteredDate = objDTU.GetActualDateTime((DateTime)(dsTestcener.Rows[i]["TestApprovedDate"])).ToString("dd-MMM-yyyy HH:mm:ss");

public DateTime GetActualDateTime(DateTime utcDateTime)
{
    DateTime actualDateTime;
    if (HttpContext.Current.Session["UserTimeZone"] != null)
    {
        actualDateTime = utcDateTime.AddHours(Double.Parse(HttpContext.Current.Session["UserTimeZone"].ToString(), CultureInfo.InvariantCulture));
    }
    else
    {
        actualDateTime = utcDateTime;
    }
    return actualDateTime;

}

Tried to modify the above data assigned to RegisteredDate object

objDTU.GetActualDateTime((DateTime)(dsTestcener.Rows[i]["TestApprovedDate"]))
.ToString("dd-MMM-yyyy HH:mm:ss", CultureInfo.InvariantCulture);

Actual Data(html) : 28-Feb-2020 15:14:38
Actual Data(xsl) : 28/02/2020 15:14
Expected Data(xsl): 28-Feb-2020 15:14:38

objDTU.GetActualDateTime((DateTime)(dsTestcener.Rows[i]["TestApprovedDate"]))
.ToString("dd-MMM-yyyy HH:mm:ss.fff", CultureInfo.InvariantCulture);

Actual Data(html) : 28-Feb-2020 15:14:38.313
Actual Data(xsl) : 14:38.
Expected Data(xsl): 28-Feb-2020 15:14:38.313

objDTU.GetActualDateTime((DateTime)(dsTestcener.Rows[i]["TestApprovedDate"]))
.ToString("dd-MMM-yyyy HH:mm:ss");

Actual Data(html) : 28-Feb-2020 15:14:38
Actual Data(xsl) : 28/02/2020 15:14:38
Expected Data(xsl): 28-Feb-2020 15:14:38

The below coding snippets shows how does the html data get captured & traversed to Response.Write()

function ExportAnalysis() {
    try {
        var batchDetails = $("#displayDetails").html();
        var message = JSON.stringify({ BatchDetails: batchDetails });
        CallAjaxJSON(message, '<%=Request.ApplicationPath%>' + "/MES/TestCentre.aspx/PersistBatchDetails", OnExportBatchSuccess, OnFailure);
        var url = '<%=Context.Request.ApplicationPath%>' + '/MES/ExportToExcel.aspx?name=Analysis Response';
        window.open(url);

    } catch (e) {
    }
    return false;
}

[System.Web.Services.WebMethod()]
public static string PersistBatchDetails(object BatchDetails)
{
    try
    {
        HttpContext.Current.Session["DashboardBatchInfo"] = BatchDetails;
    }
    catch (Exception ex){}
    return "S001";
}

protected void Page_Load(object sender, EventArgs e)
{
    string fileName = "Excel";
    if (!String.IsNullOrEmpty(Request.QueryString["name"]))
        fileName = Request.QueryString["name"];
    Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
    Response.Charset = "";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = "application/vnd.ms-excel";
    string BatchDetails = Convert.ToString(Session["DashboardBatchInfo"]);
    Response.Write(BatchDetails);
    Response.End();
    Session["DashboardBatchInfo"] = null;
}

How should we need to achieve in writing the expected data in the respective format?

Use [EPPlus](https://github.com/JanKallman/EPPlus) and read [this](https://stackoverflow.com/questions/40209636/epplus-number-format/40214134#40214134). It looks like you are just exporting a html string as xls — VDWWD, Mar 20 '20 at 08:47

score 0 · Answer 1 · answered Mar 31 '20 at 17:53

Finally found the solution:

[System.Web.Services.WebMethod()]
public static string PersistBatchDetails(object BatchDetails)
{
    try
    {
        string pattern = @"\d{4}\-\d{2}\-\d{2}\s\d{2}\:\d{2}\:\d{2}|\d{2}\-[a-zA-Z]{3}\-\d{4}\s\d{2}\:\d{2}\:\d{2}";
        Regex r = new Regex(pattern);
        var res = r.Replace(BatchDetails.ToString(), new MatchEvaluator(ConvertDateFormat));
        HttpContext.Current.Session["DashboardBatchInfo"] = res;

    }
    catch (Exception ex)
    {
        BasePage objBasePage = new BasePage();
        objBasePage.Log.LogError("Page: TestActivation, Method: GetBatchesList " + ex.Message, ex);
        objBasePage = null;
        return "S002";
    }
    return "S001";
}

public static string ConvertDateFormat(Match m)
{
    var mydate = DateTime.Parse(m.Value);
    return string.Format("=\"{0}\"", mydate.ToString("yyyy-MM-dd hh:mm:ss"));
}

Formatting and overriding the default cell format of date & time while writing it in an Excel Sheet

1 Answers1