none
OfficeOpenXml 数字格式 RRS feed

  • 问题

  • 请问各路大神,我在设置 OfficeOpenXml 数字格式的时候,设置了千分位和小数点, 如:

    sheet.Cells[2, 12, 2, 12].Style.Numberformat.Format = "#,##0.000";

    这样的话,比如说数字 175665.234可以显示位  175,665.234 ,
    但是整数 5874也会更着显示位 5,874.000  
    我想问问,如何不要显示整数后面的.000  , 我的意思是,如果为整数就不要出现末尾的三个小数位了。

    请问format里面怎么写?谢谢 
    2021年4月20日 1:58

答案

  • 你好,

    鉴于你是要对整数和小数设置不同的数字格式,我建议你可以判断当前的值是整数还是小数来设置不同的数字格式。

    你可以看看下面的代码:

     FileInfo existingFile = new FileInfo("D:\\1.xlsx");
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                int number;
                decimal smallnumer;
                using (ExcelPackage package = new ExcelPackage(existingFile))
                {
                    //get the first worksheet in the workbook
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                    int colCount = worksheet.Dimension.End.Column;  //get Column Count
                    int rowCount = worksheet.Dimension.End.Row;     //get row count
                    for (int row = 1; row <= rowCount; row++)
                    {
                        for (int col = 1; col <= colCount; col++)
                        {
                           if(int.TryParse(worksheet.Cells[row,col].Value.ToString(),out number))
                            {
                                worksheet.Cells[row, col].Style.Numberformat.Format = "#,##";
                            }
                            else
                            {
                                if (decimal.TryParse(worksheet.Cells[row, col].Value.ToString(), out smallnumer))
                                {
                                    worksheet.Cells[row, col].Style.Numberformat.Format = "#,##0.000";
                                }
                            }
                        }
                    }
    
                    package.Save();
                }

    最终的实现结果像这样:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2021年4月20日 8:21
    版主