Test class, which is used to create ConditionalFormula.xls

ConditionalFormulaTest.java (text/plain), 6.04 KB, created by Jochen Wiedmann on 2011-05-03 12:05:24 UTC

(hide)

Description: Test class, which is used to create ConditionalFormula.xls

Filename:

MIME Type:

Creator: Jochen Wiedmann

Created: 2011-05-03 12:05:24 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 */ } } > } > } >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]