VB.Net:以文本形式写入Excel工作簿 [英] VB.Net: Writing To Excel Workbook as Text

问题描述

我写了以下函数...

I have written the following function...

Public Function writeAUTLSheet(doc As XDocument, myNameSpaces As Dictionary(Of String, XNamespace), theSheet As Excel.Worksheet)
    'Grab the number of the last row to not overwrite 
    Dim rowNumber As Integer = getLastRow(theSheet)

    For Each myName As XElement In doc.Descendants.Elements(myNameSpaces("ns4") + "autl")
        theSheet.Cells(rowNumber, 1) = doc.Descendants.Elements(myNameSpaces("ns") + "number").Value
        theSheet.Cells(rowNumber, 2) = myName.Descendants(myNameSpaces("ns") + "id").Value
        theSheet.Cells(rowNumber, 3) = myName.Descendants(myNameSpaces("ns") + "name").Value
        rowNumber = rowNumber + 1
    Next

End Function

它按预期运行，但是有些值写为"General"，而另一些值写为"Date".这导致将诸如07-2-3018之类的值转换为7/2/3018.然后，如果我将该单元格更改为文本"或常规"(手动)，它将变为"408525".

It runs as expected, but some values are written as "General" and others are written as "Date". This leads to values such as 07-2-3018 being turned into 7/2/3018. Then if I change that cell to "Text" or "General"(manually) it turns into "408525".

有没有一种方法可以指定我希望将其写为文本来实现07-2-3-18的书写?

Is there a way to specify that I want it to be written as text to achieve 07-2-3-18 being written?

推荐答案

当您放置可能是字符串或日期的数据时，正如您所观察到的那样，Excel确实会处理一些古怪的事情，因为它没有一种了解您提供的数据应该是一种类型还是另一种类型的方法.

When you put data that might be a string, or might be a date, Excel does wacky things with it, as you've observed, because it has no way of knowing whether the data you've provided should be one type or another.

通常，所有可以解释为 Date 类型的东西都是这样解释的.因此， 1/1/2017 将是有效日期，而 13/47/5047 则不能，因此Excel将后者视为字符串文字，将前者视为字符串.日期类型.如果一个值被认为是一个<日期>日期，那么它也是一个<长>数字. 408525 值是 Date 值 7/2/3018 的长数字表示形式.

Usually, anything that can be interpreted as a Date type, is interpreted as such. So, 1/1/2017 would be a valid date but 13/47/5047 cannot be, so Excel will treat the latter as a string literal, the former as a Date type. If a value is considered a Date, then it is also a Long numeric. The 408525 value is the long numeric representation of the Date value 7/2/3018.

您的 Cells 对象(这是 Excel.Range 类型)应该有一个 NumberFormat 属性，但是 NumberFormat不会更改基础的 value ，这是对 value 的解释，这使Excel感到困惑，并使Excel将类似于日期的值表示为Dates.

There should be a NumberFormat property of your Cells object (which is an Excel.Range type), but the NumberFormat doesn't change the underlying value, and it's the interpretation of the value which is confusing Excel and causing it to represent date-like values as Dates.

您始终可以在单元格值前加上撇号，这将迫使Excel解释为字符串，例如:

You can always prepend the cell value with an apostrophe, which will force Excel to interpret as string, e.g.:

theSheet.Cells(rowNumber, 2) = "'" + myName.Descendants(myNameSpaces("ns") + "id").Value

这篇关于VB.Net:以文本形式写入Excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！