CODEWITHSUNDEEP
c#office-interop
Kimberly Cruz
Kimberly Cruz

Reputation: 17

C#-how to blank the null data while exporting excel

if i export the time is null in the datagridview then when i export it the time null is become 12:00 i dont know how to condition it in exporting excel file.

this is my code:

            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: 444

Answers (1)

Cleptus
Cleptus

Reputation: 3541

Just add a check and write into the excel cell only if there is data to write.

for (int j = 0; j < dataGridViewIn.Columns.Count; j++)
{
    if (dataGridViewIn.Rows[i].Cells[j].Value != null)
    {
        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";
    }
}

Upvotes: 2

Related Questions