java Apache POI 如何使用条件格式更改数据格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13699263/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Apache POI How to change dataformat with conditional formatting
提问by JBerta93
I'm an user of Apache POI and i make excel file (.xlsx) and i need to use the conditional formatting on a cell. My problem is this : i've a formula, if this formula returns a value that is between 0 and 1 i want show the result with one decimal at the contrary i want to show the result with any decimal. It is possible use the conditional formatting? If yes, how i can do it?
我是 Apache POI 的用户，我制作了 excel 文件 (.xlsx)，我需要在单元格上使用条件格式。我的问题是：我有一个公式，如果这个公式返回一个介于 0 和 1 之间的值，我想用一位小数显示结果，相反我想用任何小数显示结果。可以使用条件格式吗？如果是，我该怎么做？
回答by Filipe Bernardes
Cell new_cell = row.createCell(index_column_found);
XSSFCellStyle cs = wBook.createCellStyle();
XSSFDataFormat df = wBook.createDataFormat();
cs.setDataFormat(df.getFormat(cell_custom_format));
new_cell.setCellStyle(cs);
This work very well with POI 3.12
这与 POI 3.12 配合得很好
回答by Sunil Gulabani
Here is the code that will satisfy your requirement:
这是满足您要求的代码：
FileOutputStream out = new FileOutputStream("dateFormat.xls");
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfworkbook.createSheet("new sheet");
HSSFCellStyle cs = hssfworkbook.createCellStyle();
HSSFDataFormat df = hssfworkbook.createDataFormat();
cs.setDataFormat(df.getFormat("#,##0.0"));
for(int i=0 ;i <100 ; i++ )
{
double value = new Random(i).nextGaussian();
HSSFRow row = sheet.createRow((short) i);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue(value);
if(value>=0 && value<=1)
cell.setCellStyle(cs);
}
hssfworkbook.write(out);
out.close();
回答by swamy
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
by using this dataformat we can assighn value as string to the cell.
通过使用这种数据格式，我们可以将值作为字符串分配给单元格。
回答by Sunil Gulabani
Use DataFormatter for format your cell values.
使用 DataFormatter 设置单元格值的格式。
Check Example: http://www.roseindia.net/java/poi/setdataformat.shtml
检查示例：http: //www.roseindia.net/java/poi/setdataformat.shtml
API Link: http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html
API 链接：http: //poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html
This might help you.
这可能对你有帮助。
回答by SkateScout
Hi there is no official way but i got it running with an small hack:
嗨，没有官方方法，但我通过一个小技巧让它运行：
static void setNumberFormat(final XSSFConditionalFormattingRule r,final XSSFSheet s, final String format) {
final int id = s.getWorkbook().createDataFormat().getFormat(format);
try {
final Method m = XSSFConditionalFormattingRule.class.getDeclaredMethod("getDxf", boolean.class);
m.setAccessible(true);
final org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDxf dxf = (CTDxf)m.invoke(r, true);
final org.openxmlformats.schemas.spreadsheetml.x2006.main.CTNumFmt nf = dxf.addNewNumFmt();
nf.setNumFmtId(id);
nf.setFormatCode(format);
dxf.setNumFmt(nf);
} catch(final Throwable t) {
t.printStackTrace();
}
}
Usage:
用法：
private static final String HUMAN1_BYTES = "[<1000]#0\" B\";[<1000000]#0,\" KB\";#0,,\" MB\"";
private static final String HUMAN2_BYTES = "[<1000000000000]#0,,,\" GB\";[<1000000000000000]#0,,,,\" TB\";#0,,,,,\" PB\"";
public static void addConditionalFormattingRule(final XSSFSheet sheet, final CellRangeAddress[] regions, final byte op, final long limit, final String format) {
final XSSFSheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
final XSSFConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(op, Long.toString(limit));
setNumberFormat(rule, sheet, format);
sheetCF.addConditionalFormatting(regions, rule);
}
public static void setHumanSizeColumns(final XSSFSheet sheet, final char... cols) {
if(cols == null) return;
final CellRangeAddress[] ranges = new CellRangeAddress[cols.length];
for(int i=0;i<cols.length;++i) ranges[i] = CellRangeAddress.valueOf("$"+cols[i]+"1:$"+cols[i]+"1048576");
addConditionalFormattingRule(sheet, ranges, ComparisonOperator.LT, 1000000000, HUMAN1_BYTES);
addConditionalFormattingRule(sheet, ranges, ComparisonOperator.GE, 1000000000, HUMAN2_BYTES);
}
