如何写数据时到Excel做出正确的日期格式 [英] How to make correct date format when writing data to Excel
问题描述
荫使用办公互操作导出一个DataTable到Excel文件。的问题是,Excel不能识别的日期作为这样,而是它显示数字。在另一种情况下,我传递一个字符串,它则识别为一个日期。在这两种情况下,数据被搞砸了。
Iam exporting a DataTable to an Excel-file using office interop. The problem is, that Excel does not recognize dates as such, but instead it displays numbers. In another case I pass a string which it then recognizes as a date. In both cases the data is messed up.
我想这应该是存储在文本格式的单元格的NumberFormat @,但它也不能工作。
I tried NumberFormat @ which is supposed to store the cell in text format, but it didn't work either.
Application app = new Application();
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
app.EnableAnimations = false;
app.EnableAutoComplete = false;
app.EnableSound = false;
app.EnableTipWizard = false;
app.ErrorCheckingOptions.BackgroundChecking = false;
Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws = (Worksheet)wb.Worksheets[1];
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
Range rng = ws.Cells[j+2, i+1]as Range;
rng.Value2 = dt.Rows[j][i].ToString();
rng.NumberFormat = "@";
}
}
wb.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
wb.Close(false, Missing.Value, Missing.Value);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
ws = null;
wb = null;
app = null;
GC.Collect();
为什么我的NumberFormat @工作?不应该将textFormat显示一切一样,我把它?
Why doesn't my NumberFormat @ work? Shouldn't Textformat display everything the same as I put it in?
推荐答案
你有没有尝试格式化整个列作为一个日期列?事情是这样的:
Did you try formatting the entire column as a date column? Something like this:
Range rg = (Excel.Range)worksheetobject.Cells[1,1];
rg.EntireColumn.NumberFormat = "MM/DD/YYYY";
您可以尝试将一个只有滴答字符串表达式之前加载文本之前,其他的事情Excel单元格(如果该事项与否,而是直接输入文本到一个细胞它的工作原理不知道)。
The other thing you could try would be putting a single tick before the string expression before loading the text into the Excel cell (not sure if that matters or not, but it works when typing text directly into a cell).
这篇关于如何写数据时到Excel做出正确的日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!