[Excel Package] Formatting Date and Currency data

Forge Component
(5)
Published on 2018-08-02 by Carlos Alfaro
5 votes
Published on 2018-08-02 by Carlos Alfaro

Hi


Is there any option for format the date and decimal values to currency?

Christoff le Roux wrote:

Hi


Is there any option for format the date and decimal values to currency?

Hi Christoff le Roux,


Can you please check this post and try this solution?

https://www.outsystems.com/forums/discussion/44599/cell-writebyindex-with-celltype-datetime-writes-a-number/


Best Regards,

FV


Hi, the suggested solution does not solve my problem.. I need to be able to specify the currency display format and number of decimal places.

Hello Christoff,

I also tried this and the current implementation of the Excel package does not support this. When you use the write cell action with custom it writes your number as text: (sorry for the Dutch screenshot of Excel, but is says number stored as text. 



With this number stored as text you can do calculations is Excel, but it not fool proof and you need to do your formatting in you cellvalue asign. But i some cases this could be good enough. 


The only other alternative is adjusting the Excel extension. In the extension there the following function: 

public void MssCell_WriteByIndexWithFormat(object ssWorksheet, int ssRow, int ssColumn, string ssCellValue, string ssCellType, RCCellFormatRecord ssCellFormat)
        {
            ExcelWorksheet ws;
            ws = (ExcelWorksheet)ssWorksheet;
            //ws.SetValue(ssRow, ssColumn, ssCellValue);
           
            switch (ssCellType.ToLower())
            {
                case "integer": ws.SetValue(ssRow, ssColumn, Convert.ToInt32(ssCellValue)); break;
                case "datetime": ws.SetValue(ssRow, ssColumn, Convert.ToDateTime(ssCellValue)); break;
                case "decimal": ws.SetValue(ssRow, ssColumn, Convert.ToDecimal(ssCellValue)); break;
                case "boolean": ws.SetValue(ssRow, ssColumn, Convert.ToBoolean(ssCellValue)); break;
                default: ws.SetValue(ssRow, ssColumn, ssCellValue); break;
            }
            ApplyFormatToRange(ws.Cells[ssRow, ssColumn], ssCellFormat);
        } // MssCell_WriteByIndexWithFormat


You could add a case like :                 

case "currency": ws.Cells[ssRow, ssColumn].Style.Numberformat.Format = "$###,###,##0.00"; break; 


or 

case "currency": ws.Cells[ssRow, ssColumn].Style.Numberformat.Format = "$###.###.##0,00"; break; 

Depending on you decimal sign


I have not tested this, and I also don't know what happens you you set the decimal sign different that you excel language settings. But if it is important enough you try and test different configurations.