C＃将文本文件保存到Excel

I have a text file that is comma delimited. some numbers are representing Codes or Credit Card numbers and they should show in the Excel as TEXT and should not be treated as numbers (left Zero is important )

我有一个逗号分隔的文本文件。有些数字代表代码或信用卡号，它们应该在Excel中显示为TEXT，不应该被视为数字（左边是重要的）

here is my code:

这是我的代码：

m_objBooks.OpenText(
                    Filename: _fileInfo.FullName,
                    Origin: Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                    StartRow: 1,
                    DataType: Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited,
                    TextQualifier: Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
                    ConsecutiveDelimiter: false,
                    Tab: false,
                    Semicolon: false,
                    Comma: true,
                    Space: false,
                    Other: false,
                    OtherChar: m_objOpt,
                    FieldInfo: m_objOpt,
                    TextVisualLayout: m_objOpt,
                    DecimalSeparator: m_objOpt,
                    ThousandsSeparator: m_objOpt,
                    TrailingMinusNumbers: m_objOpt,
                    Local: m_objOpt
                    );

                m_objBook = m_objExcel.ActiveWorkbook;

                // Save the text file in the typical workbook format and quit Excel.
                m_objBook.SaveAs(excelFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt,
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,
                    m_objOpt, m_objOpt);
                m_objBook.Close(false, m_objOpt, m_objOpt);
                m_objExcel.Quit();

Although the Code and Credit Card fields has the Double Quotes around them, the generated excel file still treats them as numbers by removing the Left Zero and stuff like that. why?

虽然代码和信用卡字段周围有双引号，但生成的excel文件仍然通过删除Left Zero和类似的东西将它们视为数字。为什么？

2 个解决方案

#1

I already fight with this problem over a month. I found there is many problem using this method.

我已经在一个月内解决了这个问题。我发现使用这种方法存在很多问题。

Finally i got a great example in the CodeProject website.

最后，我在CodeProject网站上得到了一个很好的例子。

Please look at this below example.

请看下面的例子。

A Very Easy to Use Excel XML Import-Export Library

一个非常容易使用的Excel XML导入 - 导出库

I hope it helpfull.

我希望它有用。

#2

I managed to solve this problem by passing the correct FieldInfo object.

我设法通过传递正确的FieldInfo对象来解决这个问题。

in my example, I passed a Missing object to the FieldInfo parameter.

在我的例子中，我将一个Missing对象传递给FieldInfo参数。

instead of that, I created an array object and passed it to the FieldInfo parameter.

而不是那样，我创建了一个数组对象并将其传递给FieldInfo参数。

Here is a sample on how to create the FieldInfo object:

以下是有关如何创建FieldInfo对象的示例：

int[,] _fieldInfo = new int[8,2]  // 8 = number of columns , 2 = number of properties (column number and column type (General = 1 , Text = 2 .. check: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.opentext.aspx )

//fill the array:
_fieldInfo[0,0] = 1; //column number
_fieldInfo[0,1] = 2; //column type (2 = text)

_fieldInfo[1,0] = 2;
_fieldInfo[1,1] = 2;

_fieldInfo[2,0] = 3;
_fieldInfo[2,1] = 2;

after that, I just pass the _fieldInfo to the method and all is done. Of course, you can use this technique to create the _fieldInfo dynamically using loops.

之后，我只是将_fieldInfo传递给方法，一切都完成了。当然，您可以使用此技术使用循环动态创建_fieldInfo。

#1