C # Excel problem in column

Question

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

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

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

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

Guest   0   12/10/2010 15:46    66082

However, if I ran the C # code, I get.

Guest   0   10/12/2010 15:46    66082

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

Here is my c # code

((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);

I even recorded a 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))

The only thing I could not try was FieldInfo. Don't know how to convert it to C #. Not much help from google. I suspect this is xlGeneralFormat.

Any ideas? Thank.

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

Ok, I realized that the problem is not related to FieldInfo, it works fine even if I omit it.

The problem is this.

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

. , , . .

. , .

-, FileName , "saveFileDialog1.FileName". , , . Type.Missing @ "C:\lalala.xls", .

, -.- , . DateTime Excel Serial DateTime. .

Answer 1

, ( Excel VBA .NET, Webb Saunders), FieldInfo ", ". , , , .

MSDN :

http://msdn.microsoft.com/en-us/library/04b02wh9%28v=VS.80%29.aspx

, : (, ). - . - XlColumnDataType. :

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlcolumndatatype.aspx

. , xlDMYFormat xlMDYFormat ?

, (-) ( ), . faffing , .

Answer 2

, , 3- . , , :

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);

Answer 3

It requests an array, and you need to send an array.

Array fieldInfoArray = new int[,] { { 1, 1 },  { 2, 1 }, { 3, 3 }, { 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,
 FieldInfoArray, Type.Missing,
 Type.Missing, Type.Missing);

Optionally, you can use only those columns that you want to change the format of, the rest will remain in "general". The first is the column number, the second is the format.

Array fieldInfoArray = new int[,] { { 3, 3 } };