如何在导出到 Excel 时使空数据空白

Question

我的 datagridview 中有一个列日期，我希望它在 Excel 中将其显示为 12 小时，因为它是 24 小时(示例：16/06/19 7：00 PM(。这个问题很好，我解决了，但真正的问题是如果我的数据为空，它会像这样显示(16/06/19 12：00 AM(。它总是有时间值 12：00 AM，我只想显示日期，就像这样 (16/06/19 (。我需要一个条件，但我不知道怎么做。

这是我导出到 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;
}

Answer 1

你想要

12小时制使用小hh，你想要24小时制使用大写HH

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

使用格式字符串中的tt生成 AM/PM 指示符。

Answer 2

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 = "*";
        }
    }
}