How to change not only the style but the cell format with C# using NPOI?

Asked

Viewed 179 times

2

when I file in Excel using the library NPOI (https://github.com/tonyqus/npoi/tree/master/examples/xssf) I can change the style of the cell to moneyFormat with the following code, however, when the user opens Excel the cells are with the formatting of "GENERAL" and in order for it to be able to, for example, add up all transactions by selecting cells, it needs to tighten F2 (edit cell) and press Enter, only then the cell changes format. Does anyone know how to solve this? Follow the code:

HSSFFont boldFont = (HSSFFont)workBook.CreateFont();  
HSSFCellStyle cellStyle = (HSSFCellStyle)workBook.CreateCellStyle();  

ICellStyle moneyFormat = workBook.CreateCellStyle();  
ICellStyle percentFormat = workBook.CreateCellStyle();  
ICellStyle qtdFormat = workBook.CreateCellStyle();  

//Define máscaras  
IDataFormat format = workBook.CreateDataFormat();  
moneyFormat.DataFormat = format.GetFormat("R$#,##0.00");  
percentFormat.DataFormat = format.GetFormat("0.00");  
qtdFormat.DataFormat = format.GetFormat("0");  

//Linhas  
foreach (var linha in mvtFinanceiroList)  
{  
    row = (HSSFRow)sheet.CreateRow(index);  
    row.CreateCell(0).SetCellValue(linha.tb_ban_banco.ban_c_descricao); //Cria 1ª coluna da 1ª linha  

    int lastRow1 = row.LastCellNum;  
    row.CreateCell(lastRow1).SetCellValue(linha.mvt_c_descricao); //Cria 2ª coluna da 1ª linha  

    int lastRow2 = row.LastCellNum;  
    row.CreateCell(lastRow2).SetCellValue(string.Format("{0:C}", linha.mvt_n_valor)); //Cria 3ª coluna da 1ª linha  
    row.GetCell(lastRow2).CellStyle = moneyFormat; //Aplica máscara de Grana  

    int lastRow3 = row.LastCellNum;  
    row.CreateCell(lastRow3).SetCellValue(linha.mvt_d_dataMovimento.ToString("dd/MM/yyyy"));  
    row.GetCell(lastRow3).CellStyle = cellStyleCenter; //Posiciona no centro  

    index++;  
}
c# excel npoi

  • would not be the SetDataFormat() in the CellStyle?

  • Leandro Angelo, it didn’t work. I tried several variations of Setdataformat(), but without effect.

  • The closest I came was seeing this tutorial, but it also doesn’t work properly for real value. link

1 answer

1

I ended up being able to solve it. It was simpler than it looked. To get it right in Excel the correct thing is to convert to Double instead of decimal ()

HSSFFont boldFont = (HSSFFont)workBook.CreateFont();  
HSSFCellStyle cellStyle = (HSSFCellStyle)workBook.CreateCellStyle();  

ICellStyle moneyFormat = workBook.CreateCellStyle();  
ICellStyle percentFormat = workBook.CreateCellStyle();  
ICellStyle qtdFormat = workBook.CreateCellStyle();  

//Define máscaras  
IDataFormat format = workBook.CreateDataFormat();  
moneyFormat.DataFormat = format.GetFormat("R$#,##0.00");  
percentFormat.DataFormat = format.GetFormat("0.00");  
qtdFormat.DataFormat = format.GetFormat("0");  

//Linhas  
foreach (var linha in mvtFinanceiroList)  
{  
    row = (HSSFRow)sheet.CreateRow(index);  
    row.CreateCell(0).SetCellValue(linha.tb_ban_banco.ban_c_descricao); //Cria 1ª coluna da 1ª linha  

    int lastRow1 = row.LastCellNum;  
    row.CreateCell(lastRow1).SetCellValue(linha.mvt_c_descricao); //Cria 2ª coluna da 1ª linha  

    int lastRow2 = row.LastCellNum;  
    row.CreateCell(lastRow2).SetCellValue(Convert.ToDouble(linha.mvt_n_valor)); //Cria 3ª coluna da 1ª linha  
    row.GetCell(lastRow2).CellStyle = moneyFormat; //Aplica máscara de Grana  

    int lastRow3 = row.LastCellNum;  
    row.CreateCell(lastRow3).SetCellValue(linha.mvt_d_dataMovimento.ToString("dd/MM/yyyy"));  
    row.GetCell(lastRow3).CellStyle = cellStyleCenter; //Posiciona no centro  

    index++;  
}

Browser other questions tagged c# excel npoi

You are not signed in. Login or sign up in order to post.