Looping through each cell in excel sheet and assign NULL value using EPP Plus

Question

I have an application which downloads excel sheet on button click, the data is filled from a ADO.NET data table. Now the problem is, few data columns are NULL in database, they don't print as NULL in excel cell, instead its blank. How do I fill NULL value in those excel cell?

Right now i'm looping through each column to see if the value is of Datetime(Code Below). Can something similar be done to assign NULL values by looping through each row and column cell?

int colWorkSheet1 = 0;
foreach (DataColumn data in dataTable1.Columns)
{
    colWorkSheet1++;
    if (data.DataType == typeof(DateTime))
    {
        worksheet1.Column(colWorkSheet1).Style.Numberformat.Format = "MM/dd/yyyy";// use "MM/dd/yyyy hh:mm:ss AM/PM"; for time and seconds
    }
}

Do you have many columns? Would it be reasonable to check for nulls when inserting data? Or use an helper that would check for datetime and null before inserting the value maybe? — Rafalon

Rafalon Rafalon · Accepted Answer · 2019-02-19T14:19:42

You could have an extension method which would look like this:

public static void InsertSpecial(this ExcelWorkSheet me, int row, int col, object value)
{
    if(value == null)
        me.SetValue(row, col, "NULL");
    else
        me.SetValue(row, col, value);

    if(value is DateTime)
        me.Cells[row, col].Style.Numberformat.Format = "MM/dd/yyyy";

    // add anything you want to check/do here
}

And then call it:

xlWs.InsertSpecial(row, col, value);

instead of:

xlWs.SetValue(row, col, value);

Looping through each cell in excel sheet and assign NULL value using EPP Plus

1 Answers