3

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

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

Guest   0   12/10/2010 15:46    66082

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

Guest   0   10/12/2010 15:46    66082

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

Here's 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 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))

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.

Any Ideas? Thanks.

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.

The problem lies with this.

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.

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.

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.

user607455
  • 479
  • 5
  • 18

3 Answers3

1

The book I have (Programming Excel with VBA and .NET, Webb & Saunders) simply describes FieldInfo as "An array that describes the data types of fields in the text". So it does appear teh solution should be there, but the macro does not include any date formatting.

The MSDN ref docs are more useful:

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

Yes it is an array: actually an array of value pairs (hence the array of array in your macro). The first value is the column number. The second value is XlColumnDataType constant. The definitions are here:

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

Note the list of date formats. Looks like you need a xlDMYFormat or xlMDYFormat value for the third column?

If you know which column(s) contain the date (as the above assumes), then you could also explicitly set the format afterwards. Saves the faffing around with arrays although it does turn it into a two stage process.

winwaed
  • 7,645
  • 6
  • 36
  • 81
  • Any example on how I would put it in as a parameter? IIRC, I just put it in as ,XlColumnDataType.xlGeneralFormat , Type.Missing, and the whole Text to Column failed to work. – user607455 Feb 09 '11 at 14:31
  • 1
    This MSDN KB article shows you how to pass arrays to Excel: http://support.microsoft.com/kb/302096 . This example fills multiple cells, but you should be able to crib how to pass an array across the COM interop interface. MalibuCusser's int[][] looks to be along the right lines. – winwaed Feb 09 '11 at 15:13
  • And here's another sample using int[,] = new object [r,c] : http://stackoverflow.com/questions/536636/write-array-to-excel-range – winwaed Feb 09 '11 at 15:15
1

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);
MalibuCusser
  • 124
  • 1
  • 8
  • I changed column 2 and 3 to some Date Time format and I got this "FieldInfo :=Array(Array(1, 1), Array(2, 4), Array(3, 7), Array(4, 1))" How would I even change this to put into C#? – user607455 Feb 09 '11 at 14:45
  • Try passing in this in the position for the FieldInfo parameter (11th position I believe): 'int[][] fieldInfoArray = { new int[] { 1, 1 }, new int[] { 2, 4 }, new int[] { 3, 7 }, new int[] { 4, 1 } };'. – MalibuCusser Feb 09 '11 at 14:57
  • "Exception System.Runtime.InteropServices.SafeArrayTypeMismatchException was thrown in debuggee: Specified array was not of the expected type." – user607455 Feb 10 '11 at 01:04
  • I think you'll have to declare the array separately and cast it to and object, since TextToColumns requires objects for each parameter. – MalibuCusser Feb 11 '11 at 15:56
0

It ask for an array and you have 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);

Optional you can use just the columns you want to change the format, the other ones will stay in "general". The first is the column number, the second the format.

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