Apache POI中的数字和单元格格式 [英] Number and cell Formatting in apache poi

问题描述

我正在使用apache poi创建Excel工作表.我有-337499.939437217之类的数字，我想要显示它在excel中而不四舍五入.同样，单元格格式应为数字(对于某些列)和货币(对于某些列).

I am creating excel sheet using apache poi. I have numbers like - 337499.939437217, which I want to show as it is in excel without rounding off. Also the cell format should be number (for some columns) and currency (for some columns).

请建议我应使用哪种BuiltinFormats实现此目的.

Please suggest which BuiltinFormats should I use to achieve this.

非常感谢您的帮助.

推荐答案

首先，您需要了解如何使用

At first you need to know how to use DataFormats. Then you need to know the guidelines for customizing a number format.

对于将以通用数字格式显示的数字-337499.939437217，您可以使用格式#.###############. #表示仅在需要时显示的数字(最后一位十进制数字不是前导零和/或不为零)-请参阅准则.因此，整个格式意味着，如果需要，最多可以显示15个十进制数字，但只能显示所需的数量.

For your number -337499.939437217 which will be displayed rounded with general number format, you could use format #.###############. The # means a digit which will be displayed only if needed (is not leading zero and/or is not zero as last decimal digit) - see guidelines. So the whole format means show up to 15 decimal digits if needed but only as much as needed.

对于货币，您实际上应该使用内置的数字格式表示货币.因此，货币符号取决于Excel的区域设置.以下 BuiltinFormats 可用于apache poi .使用内置数字格式，您只需要十六进制格式的数字.

For currency you should really using a built in number format for currency. So the currency symbol depends on the locale settings of Excel. The following BuiltinFormats are usable with apache poi. Using a built in number format you need only the hexadecimal format numbers.

示例:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreateNumberFormats {

 public static void main(String[] args) throws Exception {
  Workbook wb = new XSSFWorkbook();

  Sheet sheet = wb.createSheet("format sheet");
  CellStyle style;
  DataFormat format = wb.createDataFormat();
  Row row;
  Cell cell;
  short rowNum = 0;
  short colNum = 0;

  row = sheet.createRow(rowNum++);
  cell = row.createCell(colNum);
  cell.setCellValue(-337499.939437217); // general format

  style = wb.createCellStyle();
  style.setDataFormat(format.getFormat("#.###############")); // custom number format
  row = sheet.createRow(rowNum++);
  cell = row.createCell(colNum);
  cell.setCellValue(-337499.939437217);
  cell.setCellStyle(style);
  row = sheet.createRow(rowNum++);
  cell = row.createCell(colNum);
  cell.setCellValue(123.456789012345);
  cell.setCellStyle(style);
  row = sheet.createRow(rowNum++);
  cell = row.createCell(colNum);
  cell.setCellValue(123456789.012345);
  cell.setCellStyle(style);

  style = wb.createCellStyle();
  style.setDataFormat((short)0x7); // builtin currency format
  row = sheet.createRow(rowNum++);
  cell = row.createCell(colNum);
  cell.setCellValue(-1234.5678);
  cell.setCellStyle(style);

  sheet.autoSizeColumn(0);

  FileOutputStream fileOut = new FileOutputStream("CreateNumberFormats.xlsx");
  wb.write(fileOut);
  fileOut.close();
  wb.close();
 }
}

这篇关于Apache POI中的数字和单元格格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！