Fixed bugs related to : -cloning/removing sheets -AbstractEscherHolderRecord serialization Added features: -Excel's Data validation mechanism . For this DV and DVAL records was created and two utility clkasses for easily manipulations . -methods for grouping/outlining rows and columns -methods for hidding ( nomrla and strong hide) sheets -methiod for hidding rows
Created attachment 10701 [details] Patch file cvs diff
Created attachment 10702 [details] Additions ( DV, DVAL records , utility classes for Data validation mechanism)
Just some additional notes from someone in the field: I have applied his patches to my own copy of the 2.5 final source. There was one compiler error: compile-main: [javac] Compiling 383 source files to /devel/jakarta/poi/build/classes [javac] /devel/jakarta/poi/src/java/org/apache/poi/hssf/util/HSSFCellRangeAddress.java:162: unreachable statement [javac] return 2 + this.field_addr_number*8;; [javac] ^ [javac] Note: Some input files use or override a deprecated API. [javac] Note: Recompile with -deprecation for details. [javac] 1 error Once you remove the extraneous semicolon, it compiles fine. For me the new version successfully reads, alters and writes back out Excel 2000 files with validation set to: 1) a list (ie, yes,no,maybe) 2) a range of cells (ie, $H1:$H4) 3) a named external reference (ie, =dropDown) I suspect this patch will also fix bug# 27929 and bug# 28136
Looks cool, i'll get on to this as soon as i can (which cant be immdtly) unless someone picks this up ..
There is a bug in org.apache.poi.hssf.model.Sheet.collapseColumnLevel that means calls to org.apache.poi.hssf.model.Sheet.createOutlineColumn with collapsed = true, won't collapes if any column width's are set before calling createOutlineColumn.
bug in changes to org.apache.poi.hssf.model.FormulaParser.Ident() line 299-303 causes FormulaParser Exception if function param list contains a trailing ";" ie cell.setCellFormula("HYPERLINK(\"http://google.com\",\"Google\";)"); throws java.lang.RuntimeException: Cannot Parse, sorry : Integer Expected at org.apache.poi.hssf.model.FormulaParser.Abort(FormulaParser.java:118) at org.apache.poi.hssf.model.FormulaParser.Expected (FormulaParser.java:125) at org.apache.poi.hssf.model.FormulaParser.GetNum (FormulaParser.java:240) at org.apache.poi.hssf.model.FormulaParser.Factor (FormulaParser.java:489) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:558) at org.apache.poi.hssf.model.FormulaParser.Expression (FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Arguments (FormulaParser.java:463) at org.apache.poi.hssf.model.FormulaParser.function (FormulaParser.java:325) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:266) at org.apache.poi.hssf.model.FormulaParser.Factor (FormulaParser.java:484) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:558) at org.apache.poi.hssf.model.FormulaParser.Expression (FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:710) at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula (HSSFCell.java:678) at nos.common.batch.RiemnsReport.getRiemnsXLS(RiemnsReport.java:335) at nos.common.batch.RiemnsReport.getReportZip(RiemnsReport.java:445) at nos.common.batch.RiemnsReport.main(RiemnsReport.java:494) this works fine cell.setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")");
Hrrm... just saw this thing. I've actually recently finished implementing column and row outlining (not having seen this patch). Oh well. The other stuff is still useful.
How to integrate this code with my code where i am trying to manipulate with Excel file? It is throwing some errors.
Which version should I apply this patch to? I've tried with 2.5.1 final and several 3.0a with the same results: I can successfully write a file, but when I read it gets stuck in the Workbook constructor, in this loop: while (recOffset < records.size()) { Sheet sheet = Sheet.createSheet(records, sheetNum++, recOffset ); recOffset = sheet.getEofLoc()+1; sheet.convertLabelRecords( workbook); // convert all LabelRecord records to LabelSSTRecord HSSFSheet hsheet = new HSSFSheet(workbook, sheet); sheets.add(hsheet); // workbook.setSheetName(sheets.size() -1, "Sheet"+sheets.size()); } recOffset won't increase, leading to an infinite loop. Is this patch being merged in the svn? I've seen no comment about that... (In reply to comment #8) > How to integrate this code with my code where i am trying to manipulate with > Excel file? > It is throwing some errors. (In reply to comment #3) > Just some additional notes from someone in the field: I have applied his > patches to my own copy of the 2.5 final source. There was one compiler error: > compile-main: > [javac] Compiling 383 source files to /devel/jakarta/poi/build/classes > [javac] > /devel/jakarta/poi/src/java/org/apache/poi/hssf/util/HSSFCellRangeAddress.java:162: > unreachable statement > [javac] return 2 + this.field_addr_number*8;; > [javac] ^ > [javac] Note: Some input files use or override a deprecated API. > [javac] Note: Recompile with -deprecation for details. > [javac] 1 error > > Once you remove the extraneous semicolon, it compiles fine. > > For me the new version successfully reads, alters and writes back out Excel 2000 > files with validation set to: > 1) a list (ie, yes,no,maybe) > 2) a range of cells (ie, $H1:$H4) > 3) a named external reference (ie, =dropDown) > > I suspect this patch will also fix bug# 27929 and bug# 28136 > >
I applied with 3.0 alpha3 to support reading data validation and stucked with the following compilation errors: --------------- "DVRecord.java": org.apache.poi.hssf.record.DVRecord should be declared abstract; it does not define fillFields (org.apache.poi.hssf.record.RecordInputStream) in org.apache.poi.hssf.record.Record at line 38, column 1 "DVRecord.java": cannot resolve symbol: constructor Record (short,short,byte[]) in class org.apache.poi.hssf.record.Record at line 137, column 9 "DVRecord.java": cannot resolve symbol: constructor Record (short,short,byte [],int)in class org.apache.poi.hssf.record.Record at line 150, column 9 "DVRecord.java": createPtg(org.apache.poi.hssf.record.RecordInputStream) in org.apache.poi.hssf.record.formula.Ptg cannot be applied to (byte[],int) at line 198, column 26 "DVRecord.java": createPtg(org.apache.poi.hssf.record.RecordInputStream) in org.apache.poi.hssf.record.formula.Ptg cannot be applied to (byte[],int) at line 214, column 26 "DVRecord.java": cannot resolve symbol: method getFromUnicode (byte[],int,int) in class org.apache.poi.util.StringUtil at line 550, column 48 "DVRecord.java": cannot resolve symbol: method putUncompressedUnicode (java.lang.String,byte[],int)in class org.apache.poi.util.StringUtil at line 579, column 28 ... more
Data Validation code now committed (needed some tweaking to get it to apply to trunk). Only took about 4 years....! I believe all the code from this patch has now either been committed, or isn't needed due to the functionality having been implemented in other ways. Feel free to re-open if we've missed something.