I am using the Excel - Set to try to fill in an Excel sheet using numeric values from previous SQL output tasks. When the value is saved in the Excel sheet it is only saving as text instead of a numeric or decimal.

I tried to modify the column definitions but that appears to only work for the import file and not a task output.
I tried to modify the value using the MATH add function to convert string to double (which works in the value) but not saving to Excel.

Any help on this would be greatly appreciated as the Excel is not usable without having to convert all the fields once they have been written by the user.

Thanks

Can you show how you format the columns? It should look like this to get numbers:




thomas

The following Source Value:
{MATH(Add|Double|{TASK(781e1f9d-8808-4239-9c02-a357e4c49647,StdOut)}|0|#0.00)}
With the column format set to:
Order 1: Format: #,##0.00


Returns an error below so I assumed column format was only from a file import based on your help doc.
Exception in Task: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
at System.Text.StringBuilder.Insert(Int32 index, Char value)
at OfficeOpenXml.Style.XmlAccess.ExcelNumberFormatXml.ExcelFormatTranslator.ToNetFormat(String ExcelFormat, Boolean forColWidth)
at OfficeOpenXml.Style.XmlAccess.ExcelNumberFormatXml.ExcelFormatTranslator..ctor(String format, Int32 numFmtID)
at OfficeOpenXml.Style.XmlAccess.ExcelNumberFormatXml.get_FormatTranslator()
at OfficeOpenXml.ExcelRangeBase.GetFormattedText(Boolean forWidthCalc)
at OfficeOpenXml.ExcelRangeBase.AutoFitColumns(Double MinimumWidth, Double MaximumWidth)
at OBOKIIHBDBPMCFALPGECCIFHCDJPEDKODDGK.AKDCNCMOBHEIABBNNBCLPPIJHKEKCHBCJGDP.LDPIMKIFMGEICBNMCKEAMGPKPGOGDLFCEHPF(String , String ) in C:\sourcefiles\code\VisualCronService\Jobs\TaskProcesses\Office\Excel\clsProcessTaskExcelSetCell.vb:line 425
at OBOKIIHBDBPMCFALPGECCIFHCDJPEDKODDGK.AKDCNCMOBHEIABBNNBCLPPIJHKEKCHBCJGDP.Run() in C:\sourcefiles\code\VisualCronService\Jobs\TaskProcesses\Office\Excel\clsProcessTaskExcelSetCell.vb:line 36

Thanks,
Curtis

Can you show how you format the columns? It should look like this to get numbers:




thomas

Hi Curtis

I find it a bit hard to follow what is going on in your script, so you may have to add some more screenshots. You have an index out of range exception, so your problem may not be related to formatting at all. What is the output of {TASK(781e1f9d-8808-4239-9c02-a357e4c49647,StdOut)} ?

thomas