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).

请建议我应该使用哪些内置格式来实现这一点.

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 可用于 阿帕奇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屋！