ASF Bugzilla – Attachment 26950 Details for
Bug 51089
Conditional formula is constant over cells, although it shouldn't be
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
Remember
[x]
|
Forgot Password
Login:
[x]
File, which has been created by POI using the Java class
ConditionalFormulaTest.java (text/plain), 6.04 KB, created by
Jochen Wiedmann
on 2011-05-03 12:03:04 UTC
(
hide
)
Description:
File, which has been created by POI using the Java class
Filename:
MIME Type:
Creator:
Jochen Wiedmann
Created:
2011-05-03 12:03:04 UTC
Size:
6.04 KB
patch
obsolete
>package net.sf.csutils.poi; > >import java.io.ByteArrayInputStream; >import java.io.ByteArrayOutputStream; >import java.io.File; >import java.io.FileOutputStream; >import java.io.OutputStream; >import java.lang.reflect.Field; >import java.util.List; > >import org.apache.poi.hssf.model.InternalWorkbook; >import org.apache.poi.hssf.record.CFRuleRecord; >import org.apache.poi.hssf.record.ExternSheetRecord; >import org.apache.poi.hssf.record.RecalcIdRecord; >import org.apache.poi.hssf.record.Record; >import org.apache.poi.hssf.record.RecordInputStream; >import org.apache.poi.hssf.record.StandardRecord; >import org.apache.poi.hssf.record.aggregates.CFRecordsAggregate; >import org.apache.poi.hssf.record.cf.PatternFormatting; >import org.apache.poi.hssf.usermodel.HSSFCell; >import org.apache.poi.hssf.usermodel.HSSFConditionalFormatting; >import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule; >import org.apache.poi.hssf.usermodel.HSSFPatternFormatting; >import org.apache.poi.hssf.usermodel.HSSFRow; >import org.apache.poi.hssf.usermodel.HSSFSheet; >import org.apache.poi.hssf.usermodel.HSSFSheetConditionalFormatting; >import org.apache.poi.hssf.usermodel.HSSFWorkbook; >import org.apache.poi.hssf.util.CellReference; >import org.apache.poi.ss.util.CellRangeAddress; >import org.apache.poi.util.LittleEndianOutput; >import org.apache.poi.util.LittleEndianOutputStream; > > >public class ConditionalFormulaTest { > private static <M> M getFieldValue(Object pObject, String pFieldName) throws Exception { > final Field field = pObject.getClass().getDeclaredField(pFieldName); > field.setAccessible(true); > @SuppressWarnings("unchecked") > final M m = (M) field.get(pObject); > return m; > } > > private static void setFieldValue(Object pObject, String pFieldName, Object pValue) throws Exception { > final Field field = pObject.getClass().getDeclaredField(pFieldName); > field.setAccessible(true); > field.set(pObject, pValue); > } > > private static void createCell(HSSFSheet pSheet, String pCellRef, double pValue) { > final CellReference ref = new CellReference(pCellRef); > HSSFRow row = pSheet.getRow(ref.getRow()); > if (row == null) { > row = pSheet.createRow(ref.getRow()); > } > HSSFCell cell = row.getCell((int) ref.getCol()); > if (cell == null) { > cell = row.createCell((int) ref.getCol()); > } > cell.setCellValue(pValue); > } > > private static HSSFConditionalFormattingRule createRule(HSSFSheet pSheet, int pRow) throws Exception { > final HSSFSheetConditionalFormatting conditionalFormatting = pSheet.getSheetConditionalFormatting(); > final HSSFConditionalFormattingRule cf = conditionalFormatting.createConditionalFormattingRule("$B1<$C1"); > final HSSFPatternFormatting patternFormatting = cf.createPatternFormatting(); > patternFormatting.setFillBackgroundColor((short) 12); > patternFormatting.setFillForegroundColor((short) 0); > patternFormatting.setFillPattern(PatternFormatting.SOLID_FOREGROUND); > final CFRuleRecord cfRuleRecord = getFieldValue(cf, "cfRuleRecord"); > cfRuleRecord.setPatternStyleModified(false); > return cf; > } > > private static Record newRecalcIdRecord() throws Exception { > final ByteArrayOutputStream baos = new ByteArrayOutputStream(); > final LittleEndianOutputStream leos = new LittleEndianOutputStream(baos); > leos.writeShort(RecalcIdRecord.sid); > leos.writeShort(RecalcIdRecord.sid); > leos.writeShort(0); > leos.writeInt(Integer.valueOf("00013880", 16)); > final byte[] buffer = baos.toByteArray(); > final ByteArrayInputStream bis = new ByteArrayInputStream(buffer); > final RecordInputStream ris = new RecordInputStream(bis); > setFieldValue(ris, "_currentDataLength", buffer.length); > return new RecalcIdRecord(ris); > } > > public static void main(String[] pArgs) throws Exception { > final HSSFWorkbook workbook = new HSSFWorkbook(); > final InternalWorkbook internalWorkbook = getFieldValue(workbook, "workbook"); > final List<Record> records = internalWorkbook.getRecords(); > boolean found = false; > for (int i = 0; i < records.size(); i++) { > if (records.get(i).getSid() == ExternSheetRecord.sid) { > found = true; > records.add(i+1, newRecalcIdRecord()); > } > } > if (!found) { > records.add(newRecalcIdRecord()); > } > final HSSFSheet sheet = workbook.createSheet("Conditional Formula Test"); > createCell(sheet, "B3", 1.0); > createCell(sheet, "C3", 2.0); > createCell(sheet, "B4", 1.0); > createCell(sheet, "C4", 1.0); > createCell(sheet, "B5", 1.5); > createCell(sheet, "C5", 1.0); > createCell(sheet, "B6", 1.0); > createCell(sheet, "C6", 1.1); > final CellRangeAddress[] cellRangeAddresses = new CellRangeAddress[]{ > CellRangeAddress.valueOf("D3:D6") > }; > final HSSFConditionalFormattingRule[] rules = new HSSFConditionalFormattingRule[]{ > createRule(sheet, 3), > }; > final HSSFSheetConditionalFormatting sheetConditionalFormatting = sheet.getSheetConditionalFormatting(); > sheetConditionalFormatting.addConditionalFormatting(cellRangeAddresses, rules); > final HSSFConditionalFormatting conditionalFormatting = sheetConditionalFormatting.getConditionalFormattingAt(0); > final CFRecordsAggregate cfRecordsAggregate = getFieldValue(conditionalFormatting, "cfAggregate"); > cfRecordsAggregate.getHeader().setNeedRecalculation(true); > OutputStream os = new FileOutputStream(new File("C:/workspace/csutils/cs-utils-poi/target/ConditionalFormulaTest.xls")); > try { > workbook.write(os); > os.close(); > os = null; > } finally { > if (os != null) { try { os.close(); } catch (Throwable t) { /* Ignore me */ } } > } > } >}
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Raw
Actions:
View
Attachments on
bug 51089
:
26910
|
26911
|
26912
|
26913
|
26914
|
26915
|
26919
|
26920
|
26921
|
26950
|
26951
|
26952
|
26953