openxls-develop — OpenXLS Development Discussion

Re: [openxls-develop] Fwd: Crash reading simple XLS file

[Philip N. <prn...@us...>]

Nick Pratt wrote:
> Anyone see why the following test case crashes?

A bug?

I tried this in Octave (OSS Matlab lookalike) where one can 
interactively process Java methods, and I get the same results; for 
*any* columnHandle.

(Hmmm.... traffic is low on this list; my own question from two weeks 
ago hasn't received an answer yet.....)

Philip

> Test Excel file available here:
> https://dl.dropboxusercontent.com/u/107816727/Test.xls
>
> public class OpenXLSTest
> {
> @Test
> public void testCreateWorksheetAndSetValue() throws Exception
> {
> try( InputStream inp = OpenXLSTest.class.getResourceAsStream(
> "/excel-templates/Test.xls" ) )
> {
> WorkBookHandle wbh = new WorkBookHandle( inp );
> WorkSheetHandle worksheetHandle = wbh.getWorkSheet( "Test" );
>
> CellHandle cellHandle = worksheetHandle.getCell( "B4" );
> cellHandle.setVal( "Test" );
>
> ColHandle colHandle = worksheetHandle.getCol( 0 );
>
> // Why does this trigger an ArrayIndexOutOfBoundsException ?
> colHandle.getCells();
> }
> }
> }
>
> Stack:
>
> java.lang.ArrayIndexOutOfBoundsException: 0
> at com.extentech.formats.XLS.Mulblank.getIxfe(Mulblank.java:401)
> at com.extentech.ExtenXLS.CellHandle.setMulblank(CellHandle.java:201)
> at com.extentech.ExtenXLS.CellHandle.<init>(CellHandle.java:224)
> at com.extentech.ExtenXLS.ColHandle.getCells(ColHandle.java:375)
> at com.test.OpenXLSTest.testCreateWorksheetAndSetValue(OpenXLSTest.java:32)
>
>
>
>
>
> ------------------------------------------------------------------------------
> CenturyLink Cloud: The Leader in Enterprise Cloud Services.
> Learn Why More Businesses Are Choosing CenturyLink Cloud For
> Critical Workloads, Development Environments & Everything In Between.
> Get a Quote or Start a Free Trial Today.
> http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
>
>
>
> _______________________________________________
> openxls-develop mailing list
> ope...@li...
> https://lists.sourceforge.net/lists/listinfo/openxls-develop
>

[openxls-develop] Fwd: Crash reading simple XLS file

[Nick P. <nb...@gm...>]

Anyone see why the following test case crashes?

Test Excel file available here:
https://dl.dropboxusercontent.com/u/107816727/Test.xls

public class OpenXLSTest
{
@Test
 public void testCreateWorksheetAndSetValue() throws Exception
{
 try( InputStream inp = OpenXLSTest.class.getResourceAsStream(
"/excel-templates/Test.xls" ) )
{
 WorkBookHandle wbh = new WorkBookHandle( inp );
WorkSheetHandle worksheetHandle = wbh.getWorkSheet( "Test" );

CellHandle cellHandle = worksheetHandle.getCell( "B4" );
 cellHandle.setVal( "Test" );

ColHandle colHandle = worksheetHandle.getCol( 0 );

// Why does this trigger an ArrayIndexOutOfBoundsException ?
 colHandle.getCells();
}
 }
}

Stack:

java.lang.ArrayIndexOutOfBoundsException: 0
 at com.extentech.formats.XLS.Mulblank.getIxfe(Mulblank.java:401)
at com.extentech.ExtenXLS.CellHandle.setMulblank(CellHandle.java:201)
 at com.extentech.ExtenXLS.CellHandle.<init>(CellHandle.java:224)
at com.extentech.ExtenXLS.ColHandle.getCells(ColHandle.java:375)
 at com.test.OpenXLSTest.testCreateWorksheetAndSetValue(OpenXLSTest.java:32)

Re: [openxls-develop] OpenXLS cannot read its own empty OOXML file

[Philip N. <prn...@us...>]

Philip Nienhuis wrote:
> Subject says all.
>
> Steps to reproduce:
>
> - Create an OOXML along the lines of (Octave/Matlab script language):
> wb = javaObject ("com.extentech.ExtenXLS.WorkBookHandle", true);
>
> - Remove sheets #1 and #2 (keep Sheet #0). Note: all these sheets are
> empty.
> Optionally rename Sheet1 to "EmptySheet"
>
> - Write file to disk (using Octave/Matlab):
>    xlsout = javaObject ("java.io.FileOutputStream", "io-test.xlsx");
>    xls.workbook.write (xlsout);
>    xlsout.close ();
>    xls.workbook.close ();
>
> Excel 2013 and LibreOffice read this file just fine.
> Apache POI apparently parses it OK but spots an empty cell B4.
> Inspecting it using 7-zip yields nothing special (admittedly I'm no
> OOXML guru). However there is indeed a cell B4 in the only worksheet:
>
> (Expanded sheet 1 "EmptySheet":)
>
> <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
> <worksheet
> xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
> xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension
> ref="A1:B5"/>
>    <sheetViews>
>      <sheetView tabSelected="1" workbookViewId="0"></sheetView>
>    </sheetViews>
>    <sheetFormatPr defaultColWidth="8.0" defaultRowHeight="12.75"/>
>    <sheetData>
>      <row r="4">
>        <c r="B4" s="15"></c>
>      </row>
>    </sheetData>
> </worksheet>

FTR, I see that cell B4 is actually valid as the "empty" file was made 
along the lines of this Octave/Matlab call:

xlswrite ('io_test.xls', {''}, 'EmptySheet', 'b4')

where {''} is an empty string.

Philip

[openxls-develop] OpenXLS cannot read its own empty OOXML file

[Philip N. <prn...@us...>]

Subject says all.

Steps to reproduce:

- Create an OOXML along the lines of (Octave/Matlab script language):
wb = javaObject ("com.extentech.ExtenXLS.WorkBookHandle", true);

- Remove sheets #1 and #2 (keep Sheet #0). Note: all these sheets are empty.
Optionally rename Sheet1 to "EmptySheet"

- Write file to disk (using Octave/Matlab):
   xlsout = javaObject ("java.io.FileOutputStream", "io-test.xlsx");
   xls.workbook.write (xlsout);
   xlsout.close ();
   xls.workbook.close ();

Excel 2013 and LibreOffice read this file just fine.
Apache POI apparently parses it OK but spots an empty cell B4.
Inspecting it using 7-zip yields nothing special (admittedly I'm no 
OOXML guru). However there is indeed a cell B4 in the only worksheet:

(Expanded sheet 1 "EmptySheet":)

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet 
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" 
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension 
ref="A1:B5"/>
   <sheetViews>
     <sheetView tabSelected="1" workbookViewId="0"></sheetView>
   </sheetViews>
   <sheetFormatPr defaultColWidth="8.0" defaultRowHeight="12.75"/>
   <sheetData>
     <row r="4">
       <c r="B4" s="15"></c>
     </row>
   </sheetData>
</worksheet>


Upon trying to read the file in Octave using OpenXLS, I get:

2013-12-30 21:20:02:0015 ERROR: XML Exception in OOXMLAdapter.parseRels. 
Input file is
  out of spec.: org.xmlpull.v1.XmlPullParserException: caused by: 
org.xmlpull.v1.XmlPul
lParserException: resource not found: 
/META-INF/services/org.xmlpull.v1.XmlPullParserF
actory make sure that parser implementing XmlPull API is available

and applying OpenXLS' <WorkBookHandle>.getStats method gives (from 
Octave prompt):

debug> wb.getStats
ans = -------------------------------------------
ExtenXLS Version:     11.0-${timestamp}-${scmVersi
Excel Version:        518
-------------------------------------------------
Statistics for: 
C:\Home\Philip\LOCALS~1\Temp\extentech\WBP2809737568889784884.tmp
Number of Worksheets: 0
Number of Cells:      0
Number of Formulas:   0
Number of Charts:     0
Number of Fonts:      4
Number of Formats:    8
Number of Xfs:        1
-------------------------------------------------

Clearly 'Number of Worksheets' and 'Number of Cells' are wrong, but that 
can be due to the previously occurring parse error.

Offending .xlsx file attached.

Best wishes for 2014!

Philip

[openxls-develop] java.lang.NoClassDefFoundError: org/json/JSONException - hidden dependency

[Philip N. <prn...@us...>]

Hi,

(Introducing myself...)
I'm developing spreadsheet I/O support for Octave (see www.octave.org 
and http://octave.sourceforge.net/io/overview.html). Octave is an OSS 
Matlab alternative.
Until now I've implemented quite a few "interfaces" for Octave's 
spreadsheet I/O, based on a.o., ActiveX, and as regards Java-based: 
Apache POI, JExcelAPI, UNO (=LibreOffice behind-the-scenes), and ODF 
Tookit and jOpenDocument for ODS.

OpenXLS is also one of the implemented interfaces (since a few years), 
and used to be the fastest Java-based one at least for reading.

One issue is that I've never managed to get write support properly 
implemented from Octave. That is, until this weekend, when I played 
around with OpenXLS v.10 and then at last discovered that apart from 
OpenXLS.jar another jar is required; i.e.:

- gwt-servlet-deps.jar

from the GWT kit, as that implements the org.JSON.JSONException class 
referred to in OpenXLS.jar in the (WorkSheetHandle) add method, required 
to add cells to a worksheet.

1. AFAICS, that dependency is mentioned nowhere in the docs or on your 
site (hint!)

2. Could OpenXLS be changed so that it doesn't need this dependency, please?
IMO a download of 108 MB (for GWT) + unzipping & extracting just one 
.jar file is a bit undue for a dependency on just one external class. I 
think many Octave users would feel the same.

Thanks,

Philip

Re: [openxls-develop] Insert Cell

[Nick P. <nb...@gm...>]

I figured this out, so if its of any use to anyone here's my wrapper
moveCell method:

Basic operation:
1. Find the target cell
2. Obtain a list of cells that would be affected by the move
3. Move the target cell to the new destination (important to move the
target cell before updating the formula references)
4. For each affected cell, update the formula text and trigger a formula
recalc.

This is working fine for me.

Regards

Nick

Cell - This is my wrapper interface around CellHandle.
ExcelAPIException - this is my unchecked API Exception class
'workSheet' - this is an instance of OpenXLS WorkSheetHandle

@Override
public void moveCell( Cell cell, String dest )
 {
int rowIdx = cell.getRowIdx();
 int colIdx = cell.getColIdx();

try
 {
CellHandle cellHandle = workSheet.getCell( rowIdx, colIdx );

String srcXRef = cell.toExcelRef();
log.debug( "Moving cell: " + srcXRef + " to " + dest + ". Contents: " +
cell.getActualCellContentsAsString() + ", " + cell.getStringValue());

List<CellHandle> affectedCells = cellHandle.calculateAffectedCells();
 log.debug( "affectedCells = " + affectedCells );

 workSheet.moveCell( cellHandle, dest );

for( CellHandle affectedCell : affectedCells )
 {
if( affectedCell.isFormula() )
 {
FormulaHandle formulaHandle = affectedCell.getFormulaHandle();
 log.debug( "Updating formula in Cell: " + affectedCell.getCellAddress() +
". Formula was: " + formulaHandle.toString() );
 formulaHandle.changeFormulaLocation( srcXRef, dest );
log.debug( "Formula now: " + formulaHandle.toString() );
 formulaHandle.calculate();
}
 else
{
log.warn("Don't know what to do with Cell as its not a Formula!" );
 }
}

 log.debug( "New cell info: " + cellHandle.getCellAddress()+ ". Contents: "
+ cell.getActualCellContentsAsString() + ", " + cell.getStringValue());
 }
catch( Exception e )
 {
throw new ExcelAPIException( e );
 }
}



On Thu, Dec 5, 2013 at 9:20 PM, Nick Pratt <nb...@gm...> wrote:

> I see insertCol and insertRow functionality, but not a single cell insert.
>  I've taken a row, shifted all cells over by one that are affected
> (shifting from the end of the row towards the insertion point), Im
> struggling to update the formula references correctly.
>
> I only see a way to shift formula refs for the entire column or row.  Is
> there another way to update all formulas that reference a moved cell?
>
> workSheetHandle.moveCell( cellHandle, dest ); doesnt seem to update the
> formula refs - so Im trying to manually trigger an update.
>
>
> Nick
>

[openxls-develop] Insert Cell

[Nick P. <nb...@gm...>]

I see insertCol and insertRow functionality, but not a single cell insert.
 I've taken a row, shifted all cells over by one that are affected
(shifting from the end of the row towards the insertion point), Im
struggling to update the formula references correctly.

I only see a way to shift formula refs for the entire column or row.  Is
there another way to update all formulas that reference a moved cell?

workSheetHandle.moveCell( cellHandle, dest ); doesnt seem to update the
formula refs - so Im trying to manually trigger an update.


Nick

Re: [openxls-develop] TextBoxes

[Kaia C. <ka...@in...>]

Hi Nick,

Unfortunately, OpenXLS is no longer a supported product.  However, there is existing machinery to create text boxes, see MSODrawing.createCommentBox and MSODrawing.getTextBoxPrototype

Best,
Kaia Cornell
Infoteria America Corporation
408.758.6510 x 101

ExtenXLS | Handbook 

This communication is for informational purposes only. The contents of the transmission are confidential and are intended solely for the use of the individual or entity to who they are addressed. If you have received this email in error, please notify the sender by return email and delete this message from your system. Infoteria America Corporation, its subsidiaries and affiliates do not guarantee this communication to be accurate, nor to be free of viruses and accept no liability for any damage caused thereof. 

 

From: Nick Pratt 
Sent: Sunday, December 01, 2013 5:34 PM
To: ope...@li... 
Subject: [openxls-develop] TextBoxes

Does OpenXLS support reading from / writing to TextBoxes drawn on a WorkSheet?  I see some references to the MSFT drawing objects, but not a clear way to access them on a sheet. 

Regards

Nick


--------------------------------------------------------------------------------
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT 
organizations don't have a clear picture of how application performance 
affects their revenue. With AppDynamics, you get 100% visibility into your 
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk 


--------------------------------------------------------------------------------
_______________________________________________
openxls-develop mailing list
ope...@li...
https://lists.sourceforge.net/lists/listinfo/openxls-develop

[openxls-develop] TextBoxes

[Nick P. <nb...@gm...>]

Does OpenXLS support reading from / writing to TextBoxes drawn on a
WorkSheet?  I see some references to the MSFT drawing objects, but not a
clear way to access them on a sheet.

Regards

Nick