blocks|key|150926|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|150927|+...when我需要读取excel文件，唯一不正确的日期是用户更改的日期|blockquote|150928|150929|150930|因此，当您阅读修改后的excel表格时，修改后的日期是数字，而未更改的值是日期格式中的字符串？|150931|您可以通过DateTime.FromOADate获取DateTime|offset|length|style|CODE|150932|long+dateNum+=+long.Parse(worksheet.Cells[row,+column].Value.ToString());
DateTime+result+=+DateTime.FromOADate(dateNum);|code-block|syntax|javascript|150933|使用您的样本号：|150934|Console.Write(DateTime.FromOADate(41640));+//+->++01/01/2014+|150935|entityMap^0|0|0|0|0|0|5|J|Q|8|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|Y|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|Z|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|10|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|11|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|12|7|@]|8|@]|9|$]]|$1|H|3|I|4|5|6|13|7|@$J|14|K|15|L|M]|$J|16|K|17|L|M]]|8|@]|9|$]]|$1|N|3|O|4|P|6|18|7|@]|8|@]|9|$Q|R]]|$1|S|3|T|4|5|6|19|7|@]|8|@]|9|$]]|$1|U|3|V|4|P|6|1A|7|@]|8|@]|9|$Q|R]]|$1|W|3|-4|4|5|6|1B|7|@]|8|@]|9|$]]]|X|$]]

<blockquote>
 ...when I need to read that excel file, the only dates that are
 incorrect are the ones the user has changed
</blockquote>

So when you read the modified excel-sheet, the modified dates are numbers whereas the unchanged values are strings in your date-format? 

You could get the <code>DateTime</code> via <code>DateTime.FromOADate</code>:

<pre><code>long dateNum = long.Parse(worksheet.Cells[row, column].Value.ToString());
DateTime result = DateTime.FromOADate(dateNum);
</code></pre>

With your sample-number:

<pre><code>Console.Write(DateTime.FromOADate(41640)); // -&gt; 01/01/2014 
</code></pre>

blocks|key|153718|text|您还可以更改“NumberFormatLocal”属性。这对我很有效。如果在使用EPPLUS修改Excel文件之前对其进行格式化。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|153719|下面的代码基本示例在典型的excel文件中设置A列的格式。|153720|Sub+ChangeExcelColumnFormat()

Dim+ExcelApp+As+Excel.Application
Dim+ExcelWB+As+Excel.Workbook
Dim+ExcelWS+As+Excel.Worksheet
Dim+formatRange+As+Excel.Range

Dim+strFile+As+String+=+"C:\Test.xlsx"
Dim+strSheetname+As+String+=+"Sheet1"


ExcelApp+=+New+Excel.Application
ExcelWB+=+ExcelApp.Workbooks.Open(strFile)

++++strColSelect+=+"A:A"
++++strFormat+=+"dd/mm/yyyy"

++++formatRange+=+ExcelWS.Range(strColSelect)
++++formatRange.NumberFormatLocal+=+strFormat

ExcelWB.Save()
ExcelWB.Close()
ExcelApp.Quit()

ExcelWS+=+Nothing
ExcelWB+=+Nothing
ExcelApp+=+Nothing|code-block|syntax|javascript|153721|结束子对象|153722|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can also change the 'NumberFormatLocal' property. This worked for me. If you format the Excel file before improting it using EPPLUS. 

The following basic example of code formats column A in a typical excel file. 

<pre><code>Sub ChangeExcelColumnFormat()

Dim ExcelApp As Excel.Application
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet
Dim formatRange As Excel.Range

Dim strFile As String = "C:\Test.xlsx"
Dim strSheetname As String = "Sheet1"


ExcelApp = New Excel.Application
ExcelWB = ExcelApp.Workbooks.Open(strFile)

 strColSelect = "A:A"
 strFormat = "dd/mm/yyyy"

 formatRange = ExcelWS.Range(strColSelect)
 formatRange.NumberFormatLocal = strFormat

ExcelWB.Save()
ExcelWB.Close()
ExcelApp.Quit()

ExcelWS = Nothing
ExcelWB = Nothing
ExcelApp = Nothing
</code></pre>

End Sub

blocks|key|152390|text|今天，当我试图从一些ASP.NET+DataTables生成一些Excel文档时，我偶然发现了这个问题:我在字符串方面没有问题，但在数字类型(整型、双精度型、小数型)和DataTables方面遇到了一些问题，它们被格式化为字符串或数字表示(OADate)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|152391|以下是我最终设法实现的解决方案：|152392|if+(dc.DataType+==+typeof(DateTime))
{
++++if+(!r.IsNull(dc))
++++{
++++++++ws.SetValue(row,+col,+(DateTime)r[dc]);
++++++++//+Change+the+following+line+if+you+need+a+different+DateTime+format
++++++++var+dtFormat+=+"dd/MM/yyyy";
++++++++ws.Cells[row,+col].Style.Numberformat.Format+=+dtFormat;
++++}
++++else+ws.SetValue(row,+col,+null);
}|code-block|syntax|javascript|152393|显然，诀窍在于将值设置为DateTime，然后相应地配置适当的Style.Numberformat.Format。|offset|length|style|CODE|152394|我发布了完整的代码示例(使用EPPlus将DataTable转换为Excel文件)+in+this+post+on+my+blog。|152395|entityMap|0|LINK|mutability|MUTABLE|url|https://www.ryadel.com/en/asp-net-epplus-ms-excel-data-type-format-column-value-numeric-number-date-time-cell-column/^0|0|0|0|C|8|V|P|0|16|N|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|11|8|@$K|12|L|13|M|N]|$K|14|L|15|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|16|8|@]|9|@$K|17|L|18|1|19]]|A|$]]|$1|Q|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

I stumbled upon this issue today when trying to generate some Excel documents from some ASP.NET DataTables: I had no problem with strings, but ran into few issues with numeric types (int, doubles, decimals) and DataTables, which were formatted as string or as numeric representations (OADate).

Here's the solution I eventually managed to pull off:

<pre><code>if (dc.DataType == typeof(DateTime))
{
 if (!r.IsNull(dc))
 {
 ws.SetValue(row, col, (DateTime)r[dc]);
 // Change the following line if you need a different DateTime format
 var dtFormat = "dd/MM/yyyy";
 ws.Cells[row, col].Style.Numberformat.Format = dtFormat;
 }
 else ws.SetValue(row, col, null);
}
</code></pre>

Apparently, the trick was to set the value as <code>DateTime</code> and then configure the proper <code>Style.Numberformat.Format</code>accordingly.

I published the full code sample (DataTable to Excel file with EPPlus) <a href="https://www.ryadel.com/en/asp-net-epplus-ms-excel-data-type-format-column-value-numeric-number-date-time-cell-column/" rel="nofollow noreferrer">in this post on my blog</a>.

blocks|key|2947052|text|您可以检查单元格格式是否为日期格式，然后将其解析为日期|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2947053|var+cell+=+worksheet.Cells[row,+col];
++value+=+cell.Value.ToString();
++if+(cell.Style.Numberformat.Format+==+"[$-409]d\\-mmm\\-yy;@")
++{
+string+inputString+=+DateTime.FromOADate(long.Parse(value.ToString())).ToString("dd-MMM-yyyy");
+
+}|code-block|syntax|javascript|2947054|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you could check if the cell format is in date format,
then parse it to date
<pre><code>var cell = worksheet.Cells[row, col];
 value = cell.Value.ToString();
 if (cell.Style.Numberformat.Format == &quot;[$-409]d\\-mmm\\-yy;@&quot;)
 {
 string inputString = DateTime.FromOADate(long.Parse(value.ToString())).ToString(&quot;dd-MMM-yyyy&quot;);
 
 }
</code></pre>

blocks|key|153753|text|您应该尝试使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|153754|++string+dateFromExcel+=+workSheet.Cells[row,+col].Text.ToString();
++DateTime+localdt;
++if+(DateTime.TryParse(dateFromExcel,+out+localdt))
++{
++++dateFromExcel+=+localdt.ToString("MM/dd/yyyy");
++};|code-block|syntax|javascript|153755|该值以常规格式读取值，而文本以应用的格式从excel中读取值。|153756|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You should try using
<pre><code> string dateFromExcel = workSheet.Cells[row, col].Text.ToString();
 DateTime localdt;
 if (DateTime.TryParse(dateFromExcel, out localdt))
 {
 dateFromExcel = localdt.ToString(&quot;MM/dd/yyyy&quot;);
 };
</code></pre>
the Value reads the value in the general formatting while Text reads the value as it is from the excel with applied formatting.

So i'm using EPPlus to read and write excel documents.

Workflow

<ul>
<li>User generates populated excel document</li>
<li>Opens document and adds a row</li>
<li>Uploaded and read</li>
</ul>

The dates that are generated when I create the document using EPPlus show correctly when I'm reading the value back but the row the user changes the date one or adds is showing as an INT value not something I can use as a real date. 

When I enter the date 1/01/2014 and write it, the output when I open the file up shows 41640

I'm reading it as follows

<pre><code>sheet.Cells[i, "AE".ConvertExcelColumnIndex()].Value != null
 ? sheet.Cells[i, "AE".ConvertExcelColumnIndex()].Value.ToString().Trim()
 : string.Empty
</code></pre>

Update

When exporting the file I have added the following

<pre><code>DateTime testDate;

if (DateTime.TryParse(split[i], out testDate))
{
 sheet.Cells[row, i + 1].Style.Numberformat.Format = "MM/dd/yyyy";
 sheet.Cells[row, i + 1].Value = testDate.ToString("MM/dd/yyyy");
}
</code></pre>

Also when reading the value back I have tried

<pre><code>sheet.Cells[i, "AE".ConvertExcelColumnIndex()].Style.Numberformat.Format = "MM/dd/yyy";
</code></pre>

I still get an INT back

Excel Date column returning INT using EPPlus

所以我使用EPPlus来读写excel文档。工作流程用户生成填充的excel文档并打开文档，并添加一行Uploaded read我使用EPPlus创建文档时生成的日期在我读回值时显示正确，但是用户更改日期或添加的行显示为int值，而不是我可以用作实际日期的值。当我输入date 1/01/2014并写入它时，打开文件时的...

问Excel日期列使用EPPlus返回INT
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel日期列使用EPPlus返回INTEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel日期列使用EPPlus返回INT
EN