Number Format doesn't work

[goskan93]

I create a Table in Excel using Closed XML

            System.Data.DataTable table = AcompanhamentoDePropostaTable();
            XLWorkbook wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Acompanhamento - Cultura");
            ws.FirstCell().InsertTable(table, false);

Problem is that I have columns as string with values like $20000 or R$10000 (currency is changing) and I don't know how to set the currency type to these columns and not lose information about currency.

My code for now

            for(int j = 2; j<= lastrow; j++)
            {
                ws.Cell(j, 09).Style.NumberFormat.Format = ws.Cell(j, 9).Value.ToString() != "" ? ws.Cell(j, 9).Value.ToString().Contains("R$") ? "[$R$-pt-BR]#,##0.00" : "[$$-en-US]#,##0.00" : "" ;
            }

But it does not change the format of values in my column.
Any ideas how to do this? And maybe also there exist the way to not iterate row by row?
Thank You in advance.

[vbjay]

You don't store the currency. You store the number and use formatting.

…

On Fri, Nov 23, 2018, 10:50 AM Natalia ***@***.***> wrote: I create a Table in Excel using Closed XML System.Data.DataTable table = AcompanhamentoDePropostaTable(); XLWorkbook wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Acompanhamento - Cultura"); ws.FirstCell().InsertTable(table, false); Problem is that I have columns as string with values like $20000 or R$10000 (currency is changing) and I don't know how to set the currency type to these columns and not lose information about currency. My code for now for(int j = 2; j<= lastrow; j++) { ws.Cell(j, 09).Style.NumberFormat.Format = ws.Cell(j, 9).Value.ToString() != "" ? ws.Cell(j, 9).Value.ToString().Contains("R$") ? "[$R$-pt-BR]#,##0.00" : "[$$-en-US]#,##0.00" : "" ; } But it does not change the format of values in my column. Any ideas how to do this? And maybe also there exist the way to not iterate row by row? Thank You in advance. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1078>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ADdhsTfaqqsGDDHpztmHwBoX-5OSx3Ysks5uyBlLgaJpZM4Yw239> .

[goskan93]

I dont know if I explained well, but what I want is to now my values look like this R$20000 but like this R$20,000.00, and I don't know where I am making the mistake

[vbjay]

So store the number value in the cell. Only the number. Then open Excel and format it as desired. Then go to custom in number format and use that as the number format.

…

On Fri, Nov 23, 2018, 10:55 AM Natalia ***@***.***> wrote: I dont know if I explained well, but what I want is to now my values look like this R$20000 but like this R$20,000.00 — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#1078 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ADdhsVSbncVTrONYgcg1xMKRGpsz7ubQks5uyBpZgaJpZM4Yw239> .

[goskan93]

I didn't understand your suggestion, but this is what I made:

o the table I created, I add a column with currencyID, and column with only number, without information about currency, this table I added to an Excel, and later in loop I checked row by row what is currency Id and format the cell

ws.Cell(j, i).Style.NumberFormat.Format = ws.Cell(j, i).Value.ToString() != "" ? Convert.ToInt16(ws.Cell(j, (i-1)).Value) == 2 ? " [$R$-pt-BR]#,##0.00" : "[$$-en-US] #,##0.00" : "";}
where ws.Cell(j, i) is cell with value (number) and ws.Cell(j, (i-1)) is cell with currency id

[vbjay]

You are making this too hard. You don't need to export the whole table. There is no need to have a column with currency info.

Use linq to create an ienumerable based off the data table that only has the right values for the columns. Export them to the sheet. Then use linq against the table again and get formats from table indexed by row. Set correct formats of cells, they will still be in same order of the table. Loop and set format.

Or delete currency column after export.