CODEWITHSUNDEEP
c#
Kimberly Cruz
Kimberly Cruz

Reputation: 17

How to make blank the null data while exporting to Excel

I have a column date in my datagridview, and I want it to display it in Excel to 12 hours, because it is 24 hours (sample: 16/06/19 7:00 PM). This problem is fine, I solved it, but the real problem is if I have data is null, it displays like this (16/06/19 12:00 AM). It always has time value 12:00 AM, I just want to display only the date, like this (16/06/19 ). I need a condition for this, but I don't know how.

This is my code for exporting to excel:

Microsoft.Office.Interop.Excel._Application app = new 
Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = 
app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Exported from gridview";

for (int i = 1; i < dataGridViewIn.Columns.Count + 1; i++)
{
    worksheet.Cells[1, i] = dataGridViewIn.Columns[i - 1].HeaderText;
}

for (int i = 0; i < dataGridViewIn.Rows.Count - 1; i++)
{
    (worksheet.Rows[i + 2 + ":" + i + 2, System.Reflection.Missing.Value] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
    for (int j = 0; j < dataGridViewIn.Columns.Count; j++)
    {
        worksheet.Cells[i + 2, j + 1] = dataGridViewIn.Rows[i].Cells[j].Value.ToString();
        worksheet.Cells[i + 2, 3].NumberFormat = " m/d/yy h:mm AM/PM";
    }
}
      
var saveFileDialoge = new SaveFileDialog();
saveFileDialoge.FileName = "TimeIn";
saveFileDialoge.DefaultExt = ".xlsx";
if (saveFileDialoge.ShowDialog() == DialogResult.OK)
{
    workbook.SaveAs(saveFileDialoge.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    app.Visible = true;
}

Upvotes: 0

Views: 610

Answers (2)

Ali
Ali

Reputation: 1

Foreach (datagridview rw in this.datagridview1.rows){
    For (int i=1 ; i<rw.cells.count ; i++){
        if (rw.cells[i]. value == null){
            rw.cells[i]. value = "*";
        }
    }
}

Upvotes: 0

Syafiqur_
Syafiqur_

Reputation: 587

You want 12-hour clock use small hh , You want the 24-hour clock use capital HH 

if (dateTime == null)
{
    DateTime date=Covert.ToDateTime( lastdate.ToString("dd/MM/yyyy hh:mm:ss.fff",CultureInfo.InvariantCulture));
}

use tt in the format string to produce the AM/PM designator.

Upvotes: 1

Related Questions