C＃Excel文本到列问题 [英] C# Excel Text to Column Problem

问题描述

我叫在C＃中的文本列功能，但它并没有原来我想它会的方式。

Hi I've called the text to column function in C# but it didn't turned out the way I wanted it to be.

我在这个数据。我的工作表的第一个单元格

I have this data in the first cell of my worksheet.

Guest;0;12/10/2010 03:46:34 PM;66082

如果我从Excel手动运行文本列，我得到。

If I run Text to Column manually from Excel, I get.

Guest   0   12/10/2010 15:46    66082

但是，如果我通过我的C＃代码跑了，我得到。

However if I ran through my C# code, I get.

Guest   0   10/12/2010 15:46    66082

日期格式已经从DD / MM / YYYY为MM / DD / YYYY切换

The Date format have switched from "DD/MM/YYYY" to "MM/DD/YYYY"

下面是我的C＃代码

((Range)ws.Cells[1,1]).EntireColumn.TextToColumns(
Type.Missing, Excel.XlTextParsingType.xlDelimited,
 Excel.XlTextQualifier.xlTextQualifierNone, Type.Missing,
 Type.Missing, true, Type.Missing,
 Type.Missing, Type.Missing,
 Type.Missing, Type.Missing,
 Type.Missing, Type.Missing);

我甚至记录从Excel宏。

I even recorded the Macro from Excel.

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))

这是我不能尝试的唯一的事出将字段信息。不知道如何将其转换为C＃。从谷歌没有多大帮助的。我有点怀疑它是xlGeneralFormat。

The only thing that I couldn't try out would be FieldInfo. Had no idea how to convert it to C#. Not much help from Google either. I'm kinda suspecting it is the xlGeneralFormat.

任何想法？谢谢你。

object fieldInfo = new int[4,2] {{1,1},{2,1},{3,1},{4,1}};

好吧，我意识到问题不在于与字段信息，它运行良好，即使我忽略它出。

Ok I realised that the problem does not lies with the FieldInfo, it runs fine even if I omit it out.

问题在于这一点。

wb.Close(true, saveFileDialog1.FileName, Type.Missing);

我保存到另一个文件。发现原文件为正确的格式，我想。但新保存的文件是格式错误。

I saved it into another file. And found that the original file was in the correct format I wanted. But the newly saved file was in the wrong format.

我注释掉的密切行，手动保存它。旧文件仍处于分隔的形式，而新文件仍然是格式错误。

I commented out the close line, and saved it manually. The old file is still in delimited form while the new file is still in the wrong format.

显然目标文件名只有工作，如果它是saveFileDialog1.FileName。换句话说，它的工作原理，只有当它是相同的文件名。我试图Type.Missing和@C：\lalala.xls，并没有文字列

Apparently object FileName only works if it's "saveFileDialog1.FileName". In other words it works only if it's the same file name. I tried Type.Missing and @"C:\lalala.xls" and it didn't text to column.

我刚刚实现文本到列的格式没有按'T甚至工作-.-
好​​吧，我放弃了。我要去的DateTime转换到Excel序列日期时间。
然而一个有趣的问题。

I just realise the format in Text to column doesn't even work -.- Ok I give up. I'm going to convert DateTime to Excel Serial DateTime. An interesting problem nonetheless.

推荐答案

重新录制宏，并在设置列的格式问题3步向导给你array.Using这些值的正确的价值观，我认为电话应该是这样的：

Re-recording your macro and setting the format of the column in question in the 3 step of the wizard gives you the correct values of the array.Using those values, I think the call should look like this:

int[][] fieldInfoArray = { new int[] { 1, 1 }, new int[] { 2, 1 }, new int[] { 3, 3 }, new int[] { 4, 1 } };    
((Range)ws.Cells[1,1]).EntireColumn.TextToColumns(
     Type.Missing, Excel.XlTextParsingType.xlDelimited,
     Excel.XlTextQualifier.xlTextQualifierNone, Type.Missing,
     Type.Missing, true, Type.Missing,
     Type.Missing, Type.Missing,
     Type.Missing,
     (object)fieldInfoArray,
     Type.Missing, Type.Missing);

这篇关于C＃Excel文本到列问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！