You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by snelligan <sn...@illinois.edu> on 2011/06/20 15:55:23 UTC

Problem creating an .xlsx file using POI and XSSF

When i try to create an .xlsx file using POI/XSSF it creates an .xlsx file,
but when excel tries to open it, I get the following Error:
"Excel found unreadable content in 'dkdkd.xlsx' do you want to recover the
contents of this workbook?  If you trust the source of this workbook, click
Yes."


When I click Yes I get a pop up which states "Repaired Records: Worksheet
properties from /xl/workbook.xml part (Workbook).

Log returns the following:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error031280_01.xml</logFileName><summary>Errors
were detected in file
'C:\WINDOWS\TEMP\snelliga-1.xlsx'</summary><repairedRecords
summary="Following is a list of repairs:"><repairedRecord>Repaired Records:
Worksheet properties from /xl/workbook.xml part
(Workbook)</repairedRecord></repairedRecords></recoveryLog>

Sorry if this is a duplicate, but looking through the mailing list, there
were several with this problem, but I could not find a solution.

I am using the following Jar files:
dom4j-1.6.1.jar
jdom.jar
log4j-1.2.9.jar
poi-3.7-20101029.jar
poi-examples-3.7-20101029.jar
poi-ooxml-3.7-20101029.jar
poi-ooxml-schemas-3.7-20101029.jar
poi-scratchpad-3.7-20101029.jar
xmlbeans-2.3.0.jar


This is the first time I have tried to program for POI.  The previous
programmer used HSSF in the application, but because of office 2010 I am
trying to update to XSSF.

Any help or direction would be appreciated.

-Steve Nelligan









--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4506461.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem creating an .xlsx file using POI and XSSF

Posted by Nick Burch <ni...@alfresco.com>.
On Fri, 22 Jul 2011, kempler wrote:
> -xl/_rels/workbook.xml.rels.xml - the rescued document has two additional
> targets defined: calcChain.xml and theme1.xml. Both files have styles.xml
> defined as a Target.

I wouldn't have thought that these two would be needed. Are there any 
references to them, perhaps from the styles or sheet data? (It'd likely be 
by r:id, look up the id of the relation then check for that)

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem creating an .xlsx file using POI and XSSF

Posted by kempler <ke...@hotmail.com>.
With 3.8 beta-3 I am getting the same result. I also started having a new
problem when I switched to 3.8, about sheet.createFreezePane method, but
since it is not totally related, I am planning to ask about it in a
different thread. I commented out the lines that use this method and then
tried again and had the same problem.

After reading the explanation, I unzipped the two files. Here is a summary
of the differences:

-app.xml -completely different
-core.xml - the rescued document has cp:lastModified and dctems:modified
tags in addition to the original document
-xl/_rels/workbook.xml.rels.xml - the rescued document has two additional
targets defined: calcChain.xml and theme1.xml. Both files have styles.xml
defined as a Target.
-the rescued document has a theme folder which contains an xml file named
theme1.xml. This folder does not exist in the original file.
- styles.xml files are different.
-the rescued file has calcChain.xml which doesn't exist in the original
file.

Is this information helpful? We want to start using the new version because
of the memory leak fix.

Thank you.


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4623356.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem creating an .xlsx file using POI and XSSF

Posted by Nick Burch <ni...@alfresco.com>.
On Thu, 21 Jul 2011, kempler wrote:
> The Excel generation code is exactly the same. Our excel file has .xslx 
> extension. I tried with .xls and it's not working either. We are using 
> two Maven dependencies; poi 3.7 and poi-ooxml 3.7.

You might want to try with 3.8 beta 3 to see if it helps


> This file gets created once. Can you, please, explain what are the 
> before and after xlsx files?

The file as generated by POI, and the version as saved by excel after it's 
"fixed" things

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem creating an .xlsx file using POI and XSSF

Posted by kempler <ke...@hotmail.com>.
Thanks for the quick answer.

The Excel generation code is exactly the same. Our excel file has .xslx
extension. I tried with .xls and it's not working either. We are using two
Maven dependencies; poi 3.7 and poi-ooxml 3.7.

The first time I try to generate the spread sheet I get this:

org.apache.poi.POIXMLException: org.apache.poi.POIXMLException:
java.lang.reflect.InvocationTargetException
	at
org.apache.poi.POIXMLDocumentPart.createRelationship(POIXMLDocumentPart.java:244)
	at
org.apache.poi.POIXMLDocumentPart.createRelationship(POIXMLDocumentPart.java:213)
	at
org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:271)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:169)
...

The second time, the spread sheet is generated. However, when I try to open
to open it, I have the following message:

Repaired Records: Font from /xl/styles.xml part (Styles)

And all the styles are absent in the spread sheet, though it opens fine.

This file is created on the fly and saved in a temporary folder.

The log says

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error030240_02.xml</logFileName>
<summary>
Errors were detected in file
'C:\Users\...\Content.IE5\RZOV8ZX4\excelFile.xlsx'
</summary>
<repairedRecords summary="Following is a list of repairs:">
<repairedRecord>
Repaired Records: Font from /xl/styles.xml part (Styles)</repairedRecord>
</repairedRecords>
</recoveryLog>

This file gets created once. Can you, please, explain what are the before
and after xlsx files?

Your help is appreciated. We upgraded to 3.7 since a memory leak issue was
fixed with version 3.6.

Regards.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4621129.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem creating an .xlsx file using POI and XSSF

Posted by Nick Burch <ni...@alfresco.com>.
On Thu, 21 Jul 2011, kempler wrote:
> None of the sheet names is longer than 31 characters.

If you're not using long sheet names, then it's likely to be a different 
problem. What does excel report in the error log? And what gets changed? 
(You may need to unzip the before and after .xlsx files and diff the xml 
contents to find that out)

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem creating an .xlsx file using POI and XSSF

Posted by kempler <ke...@hotmail.com>.
I have exactly the same issue. I have been reading through the threads, but I
don't understand what the real solution to the original problem is. We just
upgraded to apache poi 3.7. Before, we were using 3.5-beta5 and we did not
have any problems with the styles. After upgrading to 3.7, we started having
the problem. 

Our code begins like:

Workbook workbook = new XSSFWorkbook();

On the workbook instance, setSheetName() is never called.

The tabs are added to the workbook as

workbook.createSheet(...);

None of the sheet names is longer than 31 characters.

Still, the resulting Excel spread sheet has no style on it. The fonts are of
size 1.

Any help?





--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4620837.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem creating an .xlsx file using POI and XSSF

Posted by Yegor Kozlov <ye...@dinom.ru>.
Fixed in r1138819

I fixed workbook.createSheetName(sheetName) to silently truncate the input
argument if it is longer than 31 character.

The idea to throw IllegalArgumentException is not very good. My concern is
existing usages of POI in production. With version of POI prior to 3.8 existing
code swallowed long sheet names without any exceptions. If we decided to throw
IllegalArgumentException then these system would start to fail which is not
good.

Truncating the input argument to 31 character seems a reasonable workaround.

Yegor


On Wed, Jun 22, 2011 at 4:04 PM, Yegor Kozlov <ye...@dinom.ru> wrote:
> 2007 works for me, but the behavior may depend on Excel update number
> or service pack.
>
> OK, I'm convinced to revert my changes. The fix is coming soon. I
> created a new Bugzilla ticket to track progress:
>
> https://issues.apache.org/bugzilla/show_bug.cgi?id=51415
>
> Yegor
>
> On Wed, Jun 22, 2011 at 3:09 PM, snelligan <sn...@illinois.edu> wrote:
>> Attached is a problem which shows the 31 character problem.
>>
>>
>>
>> We are running Excel 2007 and having the problem.
>>
>>
>>
>> -Steve Nelligan
>>
>>
>>
>>
>>
>> Steven M. Nelligan
>>
>>
>>
>> From: Yegor Kozlov-4 [via Apache POI]
>> [mailto:ml-node+4513283-449273712-221743@n5.nabble.com]
>> Sent: Wednesday, June 22, 2011 3:11 AM
>> To: Nelligan, Steve M (Facilities & Services)
>> Subject: Re: Problem creating an .xlsx file using POI and XSSF
>>
>>
>>
>> On Tue, Jun 21, 2011 at 9:50 PM, Jon Svede <[hidden email]> wrote:
>>> The javdoc spells out that limitation but I agree with you, I think it
>> should
>>> throw an exception.
>>>
>> Not exactly, the rule is more complex.
>>
>> Non Excel clients such as OpenOffice allow opening / editing of
>> worksheets with names longer than 31 characters.
>>
>> Some time back I loosened the 31 characters constraint to support
>> that. That seemed to be OK for Excel 2003, Excel 2007 and OpenOffice.
>> They all were able to open workbooks with long sheet names. Now I'm
>> testing in Excel 2010 and it fails to open .xlsx.
>>
>> The test synopsis is below:
>>
>> XLS format:
>>
>> [OK] Excel 2003 can open .xls files  with long sheet names without
>> error or warning. The sheet tab shows the full name.
>> [OK] Excel 2007 can open .xls files  with long sheet names without
>> error or warning. The sheet name is silently truncated to 31
>> characters.
>> [OK] Excel 2010 can open .xls files  with long sheet names without
>> error or warning. The sheet name is silently truncated to 31
>> characters.
>>
>> XLSX format:
>>
>> [OK] Excel 2007 can open .xls files  with long sheet names without
>> error or warning. The sheet name is silently truncated to 31
>> characters.
>> [FAILURE] Excel 2010 failes to open .xlsx files  with sheet names
>> longer than 31 characters.
>>
>> I'm inclined to revert my previous change and always enforce the 31
>> characters rule. The question is what to do if the sheetName argument
>> is long? Throw IllegalArgumentException or silently truncate to 31
>> chars?
>>
>> Yegor
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>>
>>
>> ________________________________
>>
>> If you reply to this email, your message will be added to the discussion
>> below:
>>
>> http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
>> ing-POI-and-XSSF-tp4506461p4513283.html
>>
>> To unsubscribe from Problem creating an .xlsx file using POI and XSSF,
>> click here
>> <http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
>> unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD
>> Y0NjF8MjYyNDY3NjE4> .
>>
>>
>>
>> --
>> View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4513669.html
>> Sent from the POI - User mailing list archive at Nabble.com.
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem creating an .xlsx file using POI and XSSF

Posted by Yegor Kozlov <ye...@dinom.ru>.
2007 works for me, but the behavior may depend on Excel update number
or service pack.

OK, I'm convinced to revert my changes. The fix is coming soon. I
created a new Bugzilla ticket to track progress:

https://issues.apache.org/bugzilla/show_bug.cgi?id=51415

Yegor

On Wed, Jun 22, 2011 at 3:09 PM, snelligan <sn...@illinois.edu> wrote:
> Attached is a problem which shows the 31 character problem.
>
>
>
> We are running Excel 2007 and having the problem.
>
>
>
> -Steve Nelligan
>
>
>
>
>
> Steven M. Nelligan
>
>
>
> From: Yegor Kozlov-4 [via Apache POI]
> [mailto:ml-node+4513283-449273712-221743@n5.nabble.com]
> Sent: Wednesday, June 22, 2011 3:11 AM
> To: Nelligan, Steve M (Facilities & Services)
> Subject: Re: Problem creating an .xlsx file using POI and XSSF
>
>
>
> On Tue, Jun 21, 2011 at 9:50 PM, Jon Svede <[hidden email]> wrote:
>> The javdoc spells out that limitation but I agree with you, I think it
> should
>> throw an exception.
>>
> Not exactly, the rule is more complex.
>
> Non Excel clients such as OpenOffice allow opening / editing of
> worksheets with names longer than 31 characters.
>
> Some time back I loosened the 31 characters constraint to support
> that. That seemed to be OK for Excel 2003, Excel 2007 and OpenOffice.
> They all were able to open workbooks with long sheet names. Now I'm
> testing in Excel 2010 and it fails to open .xlsx.
>
> The test synopsis is below:
>
> XLS format:
>
> [OK] Excel 2003 can open .xls files  with long sheet names without
> error or warning. The sheet tab shows the full name.
> [OK] Excel 2007 can open .xls files  with long sheet names without
> error or warning. The sheet name is silently truncated to 31
> characters.
> [OK] Excel 2010 can open .xls files  with long sheet names without
> error or warning. The sheet name is silently truncated to 31
> characters.
>
> XLSX format:
>
> [OK] Excel 2007 can open .xls files  with long sheet names without
> error or warning. The sheet name is silently truncated to 31
> characters.
> [FAILURE] Excel 2010 failes to open .xlsx files  with sheet names
> longer than 31 characters.
>
> I'm inclined to revert my previous change and always enforce the 31
> characters rule. The question is what to do if the sheetName argument
> is long? Throw IllegalArgumentException or silently truncate to 31
> chars?
>
> Yegor
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
>
>
> ________________________________
>
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
> ing-POI-and-XSSF-tp4506461p4513283.html
>
> To unsubscribe from Problem creating an .xlsx file using POI and XSSF,
> click here
> <http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
> unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD
> Y0NjF8MjYyNDY3NjE4> .
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4513669.html
> Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


RE: Problem creating an .xlsx file using POI and XSSF

Posted by snelligan <sn...@illinois.edu>.
Attached is a problem which shows the 31 character problem.

 

We are running Excel 2007 and having the problem.

 

-Steve Nelligan

 

 

Steven M. Nelligan

 

From: Yegor Kozlov-4 [via Apache POI]
[mailto:ml-node+4513283-449273712-221743@n5.nabble.com] 
Sent: Wednesday, June 22, 2011 3:11 AM
To: Nelligan, Steve M (Facilities & Services)
Subject: Re: Problem creating an .xlsx file using POI and XSSF

 

On Tue, Jun 21, 2011 at 9:50 PM, Jon Svede <[hidden email]> wrote: 
> The javdoc spells out that limitation but I agree with you, I think it
should 
> throw an exception. 
> 
Not exactly, the rule is more complex. 

Non Excel clients such as OpenOffice allow opening / editing of 
worksheets with names longer than 31 characters. 

Some time back I loosened the 31 characters constraint to support 
that. That seemed to be OK for Excel 2003, Excel 2007 and OpenOffice. 
They all were able to open workbooks with long sheet names. Now I'm 
testing in Excel 2010 and it fails to open .xlsx. 

The test synopsis is below: 

XLS format: 

[OK] Excel 2003 can open .xls files  with long sheet names without 
error or warning. The sheet tab shows the full name. 
[OK] Excel 2007 can open .xls files  with long sheet names without 
error or warning. The sheet name is silently truncated to 31 
characters. 
[OK] Excel 2010 can open .xls files  with long sheet names without 
error or warning. The sheet name is silently truncated to 31 
characters. 

XLSX format: 

[OK] Excel 2007 can open .xls files  with long sheet names without 
error or warning. The sheet name is silently truncated to 31 
characters. 
[FAILURE] Excel 2010 failes to open .xlsx files  with sheet names 
longer than 31 characters. 

I'm inclined to revert my previous change and always enforce the 31 
characters rule. The question is what to do if the sheetName argument 
is long? Throw IllegalArgumentException or silently truncate to 31 
chars? 

Yegor 

--------------------------------------------------------------------- 
To unsubscribe, e-mail: [hidden email] 
For additional commands, e-mail: [hidden email] 




________________________________

If you reply to this email, your message will be added to the discussion
below:

http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4513283.html 

To unsubscribe from Problem creating an .xlsx file using POI and XSSF, 
click here
<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD
Y0NjF8MjYyNDY3NjE4> . 



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4513669.html
Sent from the POI - User mailing list archive at Nabble.com.

Re: Problem creating an .xlsx file using POI and XSSF

Posted by Yegor Kozlov <ye...@dinom.ru>.
On Tue, Jun 21, 2011 at 9:50 PM, Jon Svede <js...@yahoo.com> wrote:
> The javdoc spells out that limitation but I agree with you, I think it should
> throw an exception.
>
Not exactly, the rule is more complex.

Non Excel clients such as OpenOffice allow opening / editing of
worksheets with names longer than 31 characters.

Some time back I loosened the 31 characters constraint to support
that. That seemed to be OK for Excel 2003, Excel 2007 and OpenOffice.
They all were able to open workbooks with long sheet names. Now I'm
testing in Excel 2010 and it fails to open .xlsx.

The test synopsis is below:

XLS format:

[OK] Excel 2003 can open .xls files  with long sheet names without
error or warning. The sheet tab shows the full name.
[OK] Excel 2007 can open .xls files  with long sheet names without
error or warning. The sheet name is silently truncated to 31
characters.
[OK] Excel 2010 can open .xls files  with long sheet names without
error or warning. The sheet name is silently truncated to 31
characters.

XLSX format:

[OK] Excel 2007 can open .xls files  with long sheet names without
error or warning. The sheet name is silently truncated to 31
characters.
[FAILURE] Excel 2010 failes to open .xlsx files  with sheet names
longer than 31 characters.

I'm inclined to revert my previous change and always enforce the 31
characters rule. The question is what to do if the sheetName argument
is long? Throw IllegalArgumentException or silently truncate to 31
chars?

Yegor

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Problem creating an .xlsx file using POI and XSSF

Posted by Jon Svede <js...@yahoo.com>.
The javdoc spells out that limitation but I agree with you, I think it should 
throw an exception.

Unless someone else answers with a reason why this isn't a good idea, you can 
open a bugzilla on this.

Jon





________________________________
From: snelligan <sn...@illinois.edu>
To: user@poi.apache.org
Sent: Tue, June 21, 2011 8:43:34 AM
Subject: RE: Problem creating an .xlsx file using POI and XSSF







See attached program.



Found that the sheet name cannot be more than 31 characters



Not sure, but should createSheet(<sheetName>) throw an exception if the
length of <sheetName> is greater than 31 Characters?







Steven M. Nelligan



From: Jon Svede [via Apache POI]
[mailto:ml-node+4507655-1226400264-221743@n5.nabble.com] 
Sent: Monday, June 20, 2011 2:31 PM
To: Nelligan, Steve M (Facilities & Services)
Subject: Re: Problem creating an .xlsx file using POI and XSSF



Can you distill that down to something that discreetly reproduces the
problem?  
I tried copying and pasting that snippet into a class to run but it's
not a 
complete example. 

Thanks, 

Jon 






________________________________ 
From: snelligan <[hidden email]> 
To: [hidden email] 
Sent: Mon, June 20, 2011 8:04:22 AM 
Subject: RE: Problem creating an .xlsx file using POI and XSSF 

It is threaded through the application, but this is the main classs 



package model; 



import dbcp.TransactionManager; 

import java.text.Format; 

import java.util.*; 

import java.sql.SQLException; 

import org.apache.poi.ss.usermodel.*; 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; 

import org.apache.poi.xssf.usermodel.XSSFRow; 

import org.apache.poi.xssf.usermodel.XSSFCell; 

import org.apache.poi.xssf.usermodel.XSSFSheet; 

import org.apache.poi.xssf.usermodel.XSSFCellStyle; 

import org.jdom.Document; 

import org.jdom.Element; 

import org.jdom.input.DOMBuilder; 

import java.io.*; 

import view.HtmlMappable; 

import javax.servlet.http.HttpSession; 



.. 



    public synchronized String toExcel(final String pathPlusFile) throws

DataException { 



        // create a new workBook 

        final XSSFWorkbook book = new XSSFWorkbook(); 

        // create a new sheet w/i above workBook 

        final XSSFSheet sheet = book.createSheet(this.recordName); 

        // create a helper 

        CreationHelper createHelper = book.getCreationHelper(); 

        // declare a row object reference 

        XSSFRow row; 

        // declare a cell object reference 

        XSSFCell cell; 

        // create Fonts 

        boldFont = book.createFont(); 

        boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 

        // create cell Styles 

        dateStyle = book.createCellStyle(); 


dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/y

y")); 

        timeStyle = book.createCellStyle(); 


timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/y

y h:mm")); 

        headerStyle = book.createCellStyle(); 

        headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); 

        headerStyle.setFont(boldFont); 



        Record firstRecord; 

        // If no records, cannot export 

        if (this.records.size() == 0) throw new DataException("Record 
Set Contains No Rows"); 



        // Get reference to first record in set for creating column 
heads and indexes 

        firstRecord = (Record)this.records.get(0); 



        // Array to contain column indexes and display order to export 
to excel 

        final int[] excelColumnIndexes = new 
int[firstRecord.columns.length]; 

        int excelColumnCount = 0; 



        // No columns specified - implicit - include all columns in xml 
defined order 

        if (this.excelColumnNames.size()== 0) { 

            for (int i = 0; i < firstRecord.columns.length; i++) { 

                excelColumnIndexes[excelColumnCount] = i; 

                excelColumnCount++; 

            } 

        } 

        // Build explicit ordered array of column indexes if specified 
by setExcelColumnNames() method 

        else { 

            final Iterator i = this.excelColumnNames.iterator(); 

            while (i.hasNext()) { 

                final String columnName = (String)i.next(); 

                try { 

                    excelColumnIndexes[excelColumnCount] = 
firstRecord.getColumnIndexByName(columnName); 

                    excelColumnCount++; 

                } 

                catch (Exception e) {} 

            } 

        } 



        // Create header row and column heading cells 

        row = sheet.createRow((short)0); 

        for (int i = 0; i < excelColumnCount; i++) { 

            cell = row.createCell((short)i); 

            final Column column = 
firstRecord.columns[excelColumnIndexes[i]]; 

            cell.setCellValue(column.getColumnDefinition().getPrompt());


            cell.setCellStyle(headerStyle); 

        } 



        // Write specified filtered / sorted columns and records to 
sheet 

        int sheetRow = 0; 

        final Iterator i = this.getFilteredSortedRecords().iterator(); 

        while (i.hasNext()) { 

            final Record record = (Record)i.next(); 

            sheetRow++; 

            row = sheet.createRow((short)sheetRow); 

            for (int idx = 0; idx < excelColumnCount; idx++) { 

                cell = row.createCell((short)idx); 

                final Column column = 
record.columns[excelColumnIndexes[idx]]; 

                column.setExcelValue(cell); 

            } 

        } 



        // Write file 

        try { 

            final FileOutputStream out = new 
FileOutputStream(pathPlusFile); 

            book.write(out); 

            out.close(); 

        } 

        catch (Exception e) { 

            e.printStackTrace(); 

            throw new DataException(e.getMessage()); 

        } 

        return pathPlusFile; 

    } 



... 







Steven M. Nelligan 



From: Jon Svede [via Apache POI] 
[mailto:[hidden email]] 
Sent: Monday, June 20, 2011 8:58 AM 
To: Nelligan, Steve M (Facilities & Services) 
Subject: Re: Problem creating an .xlsx file using POI and XSSF 



Can you send the snippet of code that shows how you are creating/saving 
the 
workbook? 

Thanks, 

Jon 





________________________________ 
From: snelligan <[hidden email]> 
To: [hidden email] 
Sent: Mon, June 20, 2011 7:55:23 AM 
Subject: Problem creating an .xlsx file using POI and XSSF 

When i try to create an .xlsx file using POI/XSSF it creates an .xlsx 
file, 
but when excel tries to open it, I get the following Error: 
"Excel found unreadable content in 'dkdkd.xlsx' do you want to recover 
the 
contents of this workbook?  If you trust the source of this workbook, 
click 
Yes." 


When I click Yes I get a pop up which states "Repaired Records: 
Worksheet 
properties from /xl/workbook.xml part (Workbook). 

Log returns the following: 
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> 
<recoveryLog 
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFi

leName>error031280_01.xml</logFileName><summary>Errors 

were detected in file 
'C:\WINDOWS\TEMP\snelliga-1.xlsx'</summary><repairedRecords 
summary="Following is a list of repairs:"><repairedRecord>Repaired 
Records: 
Worksheet properties from /xl/workbook.xml part 
(Workbook)</repairedRecord></repairedRecords></recoveryLog> 

Sorry if this is a duplicate, but looking through the mailing list, 
there 
were several with this problem, but I could not find a solution. 

I am using the following Jar files: 
dom4j-1.6.1.jar 
jdom.jar 
log4j-1.2.9.jar 
poi-3.7-20101029.jar 
poi-examples-3.7-20101029.jar 
poi-ooxml-3.7-20101029.jar 
poi-ooxml-schemas-3.7-20101029.jar 
poi-scratchpad-3.7-20101029.jar 
xmlbeans-2.3.0.jar 


This is the first time I have tried to program for POI.  The previous 
programmer used HSSF in the application, but because of office 2010 I am


trying to update to XSSF. 

Any help or direction would be appreciated. 

-Steve Nelligan 









-- 
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506461.html 

Sent from the POI - User mailing list archive at Nabble.com. 

--------------------------------------------------------------------- 
To unsubscribe, e-mail: [hidden email] 
For additional commands, e-mail: [hidden email] 

________________________________ 

If you reply to this email, your message will be added to the discussion

below: 

http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506477.html 

To unsubscribe from Problem creating an .xlsx file using POI and XSSF, 
click here 
<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD

Y0NjF8MjYyNDY3NjE4> . 



-- 
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506501.html

Sent from the POI - User mailing list archive at Nabble.com. 

________________________________

If you reply to this email, your message will be added to the discussion
below:

http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4507655.html 

To unsubscribe from Problem creating an .xlsx file using POI and XSSF, 
click here
<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD
Y0NjF8MjYyNDY3NjE4> . 



--
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4510848.html

Sent from the POI - User mailing list archive at Nabble.com.

RE: Problem creating an .xlsx file using POI and XSSF

Posted by snelligan <sn...@illinois.edu>.
 

 

 

See attached program.

 

Found that the sheet name cannot be more than 31 characters

 

Not sure, but should createSheet(<sheetName>) throw an exception if the
length of <sheetName> is greater than 31 Characters?

 

 

 

Steven M. Nelligan

 

From: Jon Svede [via Apache POI]
[mailto:ml-node+4507655-1226400264-221743@n5.nabble.com] 
Sent: Monday, June 20, 2011 2:31 PM
To: Nelligan, Steve M (Facilities & Services)
Subject: Re: Problem creating an .xlsx file using POI and XSSF

 

Can you distill that down to something that discreetly reproduces the
problem?   
I tried copying and pasting that snippet into a class to run but it's
not a 
complete example. 

Thanks, 

Jon 






________________________________ 
From: snelligan <[hidden email]> 
To: [hidden email] 
Sent: Mon, June 20, 2011 8:04:22 AM 
Subject: RE: Problem creating an .xlsx file using POI and XSSF 

It is threaded through the application, but this is the main classs 



package model; 



import dbcp.TransactionManager; 

import java.text.Format; 

import java.util.*; 

import java.sql.SQLException; 

import org.apache.poi.ss.usermodel.*; 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; 

import org.apache.poi.xssf.usermodel.XSSFRow; 

import org.apache.poi.xssf.usermodel.XSSFCell; 

import org.apache.poi.xssf.usermodel.XSSFSheet; 

import org.apache.poi.xssf.usermodel.XSSFCellStyle; 

import org.jdom.Document; 

import org.jdom.Element; 

import org.jdom.input.DOMBuilder; 

import java.io.*; 

import view.HtmlMappable; 

import javax.servlet.http.HttpSession; 



.. 



    public synchronized String toExcel(final String pathPlusFile) throws

DataException { 



        // create a new workBook 

        final XSSFWorkbook book = new XSSFWorkbook(); 

        // create a new sheet w/i above workBook 

        final XSSFSheet sheet = book.createSheet(this.recordName); 

        // create a helper 

        CreationHelper createHelper = book.getCreationHelper(); 

        // declare a row object reference 

        XSSFRow row; 

        // declare a cell object reference 

        XSSFCell cell; 

        // create Fonts 

        boldFont = book.createFont(); 

        boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 

        // create cell Styles 

        dateStyle = book.createCellStyle(); 


dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/y

y")); 

        timeStyle = book.createCellStyle(); 


timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/y

y h:mm")); 

        headerStyle = book.createCellStyle(); 

        headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); 

        headerStyle.setFont(boldFont); 



        Record firstRecord; 

        // If no records, cannot export 

        if (this.records.size() == 0) throw new DataException("Record 
Set Contains No Rows"); 



        // Get reference to first record in set for creating column 
heads and indexes 

        firstRecord = (Record)this.records.get(0); 



        // Array to contain column indexes and display order to export 
to excel 

        final int[] excelColumnIndexes = new 
int[firstRecord.columns.length]; 

        int excelColumnCount = 0; 



        // No columns specified - implicit - include all columns in xml 
defined order 

        if (this.excelColumnNames.size()== 0) { 

            for (int i = 0; i < firstRecord.columns.length; i++) { 

                excelColumnIndexes[excelColumnCount] = i; 

                excelColumnCount++; 

            } 

        } 

        // Build explicit ordered array of column indexes if specified 
by setExcelColumnNames() method 

        else { 

            final Iterator i = this.excelColumnNames.iterator(); 

            while (i.hasNext()) { 

                final String columnName = (String)i.next(); 

                try { 

                    excelColumnIndexes[excelColumnCount] = 
firstRecord.getColumnIndexByName(columnName); 

                    excelColumnCount++; 

                } 

                catch (Exception e) {} 

            } 

        } 



        // Create header row and column heading cells 

        row = sheet.createRow((short)0); 

        for (int i = 0; i < excelColumnCount; i++) { 

            cell = row.createCell((short)i); 

            final Column column = 
firstRecord.columns[excelColumnIndexes[i]]; 

            cell.setCellValue(column.getColumnDefinition().getPrompt());


            cell.setCellStyle(headerStyle); 

        } 



        // Write specified filtered / sorted columns and records to 
sheet 

        int sheetRow = 0; 

        final Iterator i = this.getFilteredSortedRecords().iterator(); 

        while (i.hasNext()) { 

            final Record record = (Record)i.next(); 

            sheetRow++; 

            row = sheet.createRow((short)sheetRow); 

            for (int idx = 0; idx < excelColumnCount; idx++) { 

                cell = row.createCell((short)idx); 

                final Column column = 
record.columns[excelColumnIndexes[idx]]; 

                column.setExcelValue(cell); 

            } 

        } 



        // Write file 

        try { 

            final FileOutputStream out = new 
FileOutputStream(pathPlusFile); 

            book.write(out); 

            out.close(); 

        } 

        catch (Exception e) { 

            e.printStackTrace(); 

            throw new DataException(e.getMessage()); 

        } 

        return pathPlusFile; 

    } 



... 







Steven M. Nelligan 



From: Jon Svede [via Apache POI] 
[mailto:[hidden email]] 
Sent: Monday, June 20, 2011 8:58 AM 
To: Nelligan, Steve M (Facilities & Services) 
Subject: Re: Problem creating an .xlsx file using POI and XSSF 



Can you send the snippet of code that shows how you are creating/saving 
the 
workbook? 

Thanks, 

Jon 





________________________________ 
From: snelligan <[hidden email]> 
To: [hidden email] 
Sent: Mon, June 20, 2011 7:55:23 AM 
Subject: Problem creating an .xlsx file using POI and XSSF 

When i try to create an .xlsx file using POI/XSSF it creates an .xlsx 
file, 
but when excel tries to open it, I get the following Error: 
"Excel found unreadable content in 'dkdkd.xlsx' do you want to recover 
the 
contents of this workbook?  If you trust the source of this workbook, 
click 
Yes." 


When I click Yes I get a pop up which states "Repaired Records: 
Worksheet 
properties from /xl/workbook.xml part (Workbook). 

Log returns the following: 
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> 
<recoveryLog 
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFi

leName>error031280_01.xml</logFileName><summary>Errors 

were detected in file 
'C:\WINDOWS\TEMP\snelliga-1.xlsx'</summary><repairedRecords 
summary="Following is a list of repairs:"><repairedRecord>Repaired 
Records: 
Worksheet properties from /xl/workbook.xml part 
(Workbook)</repairedRecord></repairedRecords></recoveryLog> 

Sorry if this is a duplicate, but looking through the mailing list, 
there 
were several with this problem, but I could not find a solution. 

I am using the following Jar files: 
dom4j-1.6.1.jar 
jdom.jar 
log4j-1.2.9.jar 
poi-3.7-20101029.jar 
poi-examples-3.7-20101029.jar 
poi-ooxml-3.7-20101029.jar 
poi-ooxml-schemas-3.7-20101029.jar 
poi-scratchpad-3.7-20101029.jar 
xmlbeans-2.3.0.jar 


This is the first time I have tried to program for POI.  The previous 
programmer used HSSF in the application, but because of office 2010 I am


trying to update to XSSF. 

Any help or direction would be appreciated. 

-Steve Nelligan 









-- 
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506461.html 

Sent from the POI - User mailing list archive at Nabble.com. 

--------------------------------------------------------------------- 
To unsubscribe, e-mail: [hidden email] 
For additional commands, e-mail: [hidden email] 

________________________________ 

If you reply to this email, your message will be added to the discussion

below: 

http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506477.html 

To unsubscribe from Problem creating an .xlsx file using POI and XSSF, 
click here 
<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD

Y0NjF8MjYyNDY3NjE4> . 



-- 
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506501.html

Sent from the POI - User mailing list archive at Nabble.com. 

________________________________

If you reply to this email, your message will be added to the discussion
below:

http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4507655.html 

To unsubscribe from Problem creating an .xlsx file using POI and XSSF, 
click here
<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD
Y0NjF8MjYyNDY3NjE4> . 



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4510848.html
Sent from the POI - User mailing list archive at Nabble.com.

RE: Problem creating an .xlsx file using POI and XSSF

Posted by snelligan <sn...@illinois.edu>.
 

I will try.

 

 

-Steve.

Steven M. Nelligan

 

From: Jon Svede [via Apache POI]
[mailto:ml-node+4507655-1226400264-221743@n5.nabble.com] 
Sent: Monday, June 20, 2011 2:31 PM
To: Nelligan, Steve M (Facilities & Services)
Subject: Re: Problem creating an .xlsx file using POI and XSSF

 

Can you distill that down to something that discreetly reproduces the
problem?   
I tried copying and pasting that snippet into a class to run but it's
not a 
complete example. 

Thanks, 

Jon 






________________________________ 
From: snelligan <[hidden email]> 
To: [hidden email] 
Sent: Mon, June 20, 2011 8:04:22 AM 
Subject: RE: Problem creating an .xlsx file using POI and XSSF 

It is threaded through the application, but this is the main classs 



package model; 



import dbcp.TransactionManager; 

import java.text.Format; 

import java.util.*; 

import java.sql.SQLException; 

import org.apache.poi.ss.usermodel.*; 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; 

import org.apache.poi.xssf.usermodel.XSSFRow; 

import org.apache.poi.xssf.usermodel.XSSFCell; 

import org.apache.poi.xssf.usermodel.XSSFSheet; 

import org.apache.poi.xssf.usermodel.XSSFCellStyle; 

import org.jdom.Document; 

import org.jdom.Element; 

import org.jdom.input.DOMBuilder; 

import java.io.*; 

import view.HtmlMappable; 

import javax.servlet.http.HttpSession; 



.. 



    public synchronized String toExcel(final String pathPlusFile) throws

DataException { 



        // create a new workBook 

        final XSSFWorkbook book = new XSSFWorkbook(); 

        // create a new sheet w/i above workBook 

        final XSSFSheet sheet = book.createSheet(this.recordName); 

        // create a helper 

        CreationHelper createHelper = book.getCreationHelper(); 

        // declare a row object reference 

        XSSFRow row; 

        // declare a cell object reference 

        XSSFCell cell; 

        // create Fonts 

        boldFont = book.createFont(); 

        boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 

        // create cell Styles 

        dateStyle = book.createCellStyle(); 


dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/y

y")); 

        timeStyle = book.createCellStyle(); 


timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/y

y h:mm")); 

        headerStyle = book.createCellStyle(); 

        headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); 

        headerStyle.setFont(boldFont); 



        Record firstRecord; 

        // If no records, cannot export 

        if (this.records.size() == 0) throw new DataException("Record 
Set Contains No Rows"); 



        // Get reference to first record in set for creating column 
heads and indexes 

        firstRecord = (Record)this.records.get(0); 



        // Array to contain column indexes and display order to export 
to excel 

        final int[] excelColumnIndexes = new 
int[firstRecord.columns.length]; 

        int excelColumnCount = 0; 



        // No columns specified - implicit - include all columns in xml 
defined order 

        if (this.excelColumnNames.size()== 0) { 

            for (int i = 0; i < firstRecord.columns.length; i++) { 

                excelColumnIndexes[excelColumnCount] = i; 

                excelColumnCount++; 

            } 

        } 

        // Build explicit ordered array of column indexes if specified 
by setExcelColumnNames() method 

        else { 

            final Iterator i = this.excelColumnNames.iterator(); 

            while (i.hasNext()) { 

                final String columnName = (String)i.next(); 

                try { 

                    excelColumnIndexes[excelColumnCount] = 
firstRecord.getColumnIndexByName(columnName); 

                    excelColumnCount++; 

                } 

                catch (Exception e) {} 

            } 

        } 



        // Create header row and column heading cells 

        row = sheet.createRow((short)0); 

        for (int i = 0; i < excelColumnCount; i++) { 

            cell = row.createCell((short)i); 

            final Column column = 
firstRecord.columns[excelColumnIndexes[i]]; 

            cell.setCellValue(column.getColumnDefinition().getPrompt());


            cell.setCellStyle(headerStyle); 

        } 



        // Write specified filtered / sorted columns and records to 
sheet 

        int sheetRow = 0; 

        final Iterator i = this.getFilteredSortedRecords().iterator(); 

        while (i.hasNext()) { 

            final Record record = (Record)i.next(); 

            sheetRow++; 

            row = sheet.createRow((short)sheetRow); 

            for (int idx = 0; idx < excelColumnCount; idx++) { 

                cell = row.createCell((short)idx); 

                final Column column = 
record.columns[excelColumnIndexes[idx]]; 

                column.setExcelValue(cell); 

            } 

        } 



        // Write file 

        try { 

            final FileOutputStream out = new 
FileOutputStream(pathPlusFile); 

            book.write(out); 

            out.close(); 

        } 

        catch (Exception e) { 

            e.printStackTrace(); 

            throw new DataException(e.getMessage()); 

        } 

        return pathPlusFile; 

    } 



... 







Steven M. Nelligan 



From: Jon Svede [via Apache POI] 
[mailto:[hidden email]] 
Sent: Monday, June 20, 2011 8:58 AM 
To: Nelligan, Steve M (Facilities & Services) 
Subject: Re: Problem creating an .xlsx file using POI and XSSF 



Can you send the snippet of code that shows how you are creating/saving 
the 
workbook? 

Thanks, 

Jon 





________________________________ 
From: snelligan <[hidden email]> 
To: [hidden email] 
Sent: Mon, June 20, 2011 7:55:23 AM 
Subject: Problem creating an .xlsx file using POI and XSSF 

When i try to create an .xlsx file using POI/XSSF it creates an .xlsx 
file, 
but when excel tries to open it, I get the following Error: 
"Excel found unreadable content in 'dkdkd.xlsx' do you want to recover 
the 
contents of this workbook?  If you trust the source of this workbook, 
click 
Yes." 


When I click Yes I get a pop up which states "Repaired Records: 
Worksheet 
properties from /xl/workbook.xml part (Workbook). 

Log returns the following: 
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> 
<recoveryLog 
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFi

leName>error031280_01.xml</logFileName><summary>Errors 

were detected in file 
'C:\WINDOWS\TEMP\snelliga-1.xlsx'</summary><repairedRecords 
summary="Following is a list of repairs:"><repairedRecord>Repaired 
Records: 
Worksheet properties from /xl/workbook.xml part 
(Workbook)</repairedRecord></repairedRecords></recoveryLog> 

Sorry if this is a duplicate, but looking through the mailing list, 
there 
were several with this problem, but I could not find a solution. 

I am using the following Jar files: 
dom4j-1.6.1.jar 
jdom.jar 
log4j-1.2.9.jar 
poi-3.7-20101029.jar 
poi-examples-3.7-20101029.jar 
poi-ooxml-3.7-20101029.jar 
poi-ooxml-schemas-3.7-20101029.jar 
poi-scratchpad-3.7-20101029.jar 
xmlbeans-2.3.0.jar 


This is the first time I have tried to program for POI.  The previous 
programmer used HSSF in the application, but because of office 2010 I am


trying to update to XSSF. 

Any help or direction would be appreciated. 

-Steve Nelligan 









-- 
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506461.html 

Sent from the POI - User mailing list archive at Nabble.com. 

--------------------------------------------------------------------- 
To unsubscribe, e-mail: [hidden email] 
For additional commands, e-mail: [hidden email] 

________________________________ 

If you reply to this email, your message will be added to the discussion

below: 

http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506477.html 

To unsubscribe from Problem creating an .xlsx file using POI and XSSF, 
click here 
<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD

Y0NjF8MjYyNDY3NjE4> . 



-- 
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506501.html

Sent from the POI - User mailing list archive at Nabble.com. 

________________________________

If you reply to this email, your message will be added to the discussion
below:

http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4507655.html 

To unsubscribe from Problem creating an .xlsx file using POI and XSSF,
click here
<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD
Y0NjF8MjYyNDY3NjE4> . 



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4507673.html
Sent from the POI - User mailing list archive at Nabble.com.

Re: Problem creating an .xlsx file using POI and XSSF

Posted by Jon Svede <js...@yahoo.com>.
Can you distill that down to something that discreetly reproduces the problem?  
I tried copying and pasting that snippet into a class to run but it's not a 
complete example.

Thanks,

Jon






________________________________
From: snelligan <sn...@illinois.edu>
To: user@poi.apache.org
Sent: Mon, June 20, 2011 8:04:22 AM
Subject: RE: Problem creating an .xlsx file using POI and XSSF

It is threaded through the application, but this is the main classs



package model;



import dbcp.TransactionManager;

import java.text.Format;

import java.util.*;

import java.sql.SQLException;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.jdom.Document;

import org.jdom.Element;

import org.jdom.input.DOMBuilder;

import java.io.*;

import view.HtmlMappable;

import javax.servlet.http.HttpSession;



..



    public synchronized String toExcel(final String pathPlusFile) throws
DataException {



        // create a new workBook

        final XSSFWorkbook book = new XSSFWorkbook();

        // create a new sheet w/i above workBook

        final XSSFSheet sheet = book.createSheet(this.recordName);

        // create a helper

        CreationHelper createHelper = book.getCreationHelper();

        // declare a row object reference

        XSSFRow row;

        // declare a cell object reference

        XSSFCell cell;

        // create Fonts

        boldFont = book.createFont();

        boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // create cell Styles

        dateStyle = book.createCellStyle();


dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/y
y"));

        timeStyle = book.createCellStyle();


timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/y
y h:mm"));

        headerStyle = book.createCellStyle();

        headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

        headerStyle.setFont(boldFont);



        Record firstRecord;

        // If no records, cannot export

        if (this.records.size() == 0) throw new DataException("Record
Set Contains No Rows");



        // Get reference to first record in set for creating column
heads and indexes

        firstRecord = (Record)this.records.get(0);



        // Array to contain column indexes and display order to export
to excel

        final int[] excelColumnIndexes = new
int[firstRecord.columns.length];

        int excelColumnCount = 0;



        // No columns specified - implicit - include all columns in xml
defined order

        if (this.excelColumnNames.size()== 0) {

            for (int i = 0; i < firstRecord.columns.length; i++) {

                excelColumnIndexes[excelColumnCount] = i;

                excelColumnCount++;

            }

        }

        // Build explicit ordered array of column indexes if specified
by setExcelColumnNames() method

        else {

            final Iterator i = this.excelColumnNames.iterator();

            while (i.hasNext()) {

                final String columnName = (String)i.next();

                try {

                    excelColumnIndexes[excelColumnCount] =
firstRecord.getColumnIndexByName(columnName);

                    excelColumnCount++;

                }

                catch (Exception e) {}

            }

        }



        // Create header row and column heading cells

        row = sheet.createRow((short)0);

        for (int i = 0; i < excelColumnCount; i++) {

            cell = row.createCell((short)i);

            final Column column =
firstRecord.columns[excelColumnIndexes[i]];

            cell.setCellValue(column.getColumnDefinition().getPrompt());

            cell.setCellStyle(headerStyle);

        }



        // Write specified filtered / sorted columns and records to
sheet

        int sheetRow = 0;

        final Iterator i = this.getFilteredSortedRecords().iterator();

        while (i.hasNext()) {

            final Record record = (Record)i.next();

            sheetRow++;

            row = sheet.createRow((short)sheetRow);

            for (int idx = 0; idx < excelColumnCount; idx++) {

                cell = row.createCell((short)idx);

                final Column column =
record.columns[excelColumnIndexes[idx]];

                column.setExcelValue(cell);

            }

        }



        // Write file

        try {

            final FileOutputStream out = new
FileOutputStream(pathPlusFile);

            book.write(out);

            out.close();

        }

        catch (Exception e) {

            e.printStackTrace();

            throw new DataException(e.getMessage());

        }

        return pathPlusFile;

    }



...







Steven M. Nelligan



From: Jon Svede [via Apache POI]
[mailto:ml-node+4506477-1900045859-221743@n5.nabble.com] 
Sent: Monday, June 20, 2011 8:58 AM
To: Nelligan, Steve M (Facilities & Services)
Subject: Re: Problem creating an .xlsx file using POI and XSSF



Can you send the snippet of code that shows how you are creating/saving
the 
workbook? 

Thanks, 

Jon 





________________________________ 
From: snelligan <[hidden email]> 
To: [hidden email] 
Sent: Mon, June 20, 2011 7:55:23 AM 
Subject: Problem creating an .xlsx file using POI and XSSF 

When i try to create an .xlsx file using POI/XSSF it creates an .xlsx
file, 
but when excel tries to open it, I get the following Error: 
"Excel found unreadable content in 'dkdkd.xlsx' do you want to recover
the 
contents of this workbook?  If you trust the source of this workbook,
click 
Yes." 


When I click Yes I get a pop up which states "Repaired Records:
Worksheet 
properties from /xl/workbook.xml part (Workbook). 

Log returns the following: 
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> 
<recoveryLog 
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFi
leName>error031280_01.xml</logFileName><summary>Errors 

were detected in file 
'C:\WINDOWS\TEMP\snelliga-1.xlsx'</summary><repairedRecords 
summary="Following is a list of repairs:"><repairedRecord>Repaired
Records: 
Worksheet properties from /xl/workbook.xml part 
(Workbook)</repairedRecord></repairedRecords></recoveryLog> 

Sorry if this is a duplicate, but looking through the mailing list,
there 
were several with this problem, but I could not find a solution. 

I am using the following Jar files: 
dom4j-1.6.1.jar 
jdom.jar 
log4j-1.2.9.jar 
poi-3.7-20101029.jar 
poi-examples-3.7-20101029.jar 
poi-ooxml-3.7-20101029.jar 
poi-ooxml-schemas-3.7-20101029.jar 
poi-scratchpad-3.7-20101029.jar 
xmlbeans-2.3.0.jar 


This is the first time I have tried to program for POI.  The previous 
programmer used HSSF in the application, but because of office 2010 I am

trying to update to XSSF. 

Any help or direction would be appreciated. 

-Steve Nelligan 









-- 
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506461.html

Sent from the POI - User mailing list archive at Nabble.com. 

--------------------------------------------------------------------- 
To unsubscribe, e-mail: [hidden email] 
For additional commands, e-mail: [hidden email] 

________________________________

If you reply to this email, your message will be added to the discussion
below:

http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506477.html 

To unsubscribe from Problem creating an .xlsx file using POI and XSSF,
click here
<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD
Y0NjF8MjYyNDY3NjE4> . 



--
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4506501.html

Sent from the POI - User mailing list archive at Nabble.com.

RE: Problem creating an .xlsx file using POI and XSSF

Posted by snelligan <sn...@illinois.edu>.
It is threaded through the application, but this is the main classs

 

package model;

 

import dbcp.TransactionManager;

import java.text.Format;

import java.util.*;

import java.sql.SQLException;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.jdom.Document;

import org.jdom.Element;

import org.jdom.input.DOMBuilder;

import java.io.*;

import view.HtmlMappable;

import javax.servlet.http.HttpSession;

 

..

 

    public synchronized String toExcel(final String pathPlusFile) throws
DataException {

 

        // create a new workBook

        final XSSFWorkbook book = new XSSFWorkbook();

        // create a new sheet w/i above workBook

        final XSSFSheet sheet = book.createSheet(this.recordName);

        // create a helper

        CreationHelper createHelper = book.getCreationHelper();

        // declare a row object reference

        XSSFRow row;

        // declare a cell object reference

        XSSFCell cell;

        // create Fonts

        boldFont = book.createFont();

        boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // create cell Styles

        dateStyle = book.createCellStyle();

 
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/y
y"));

        timeStyle = book.createCellStyle();

 
timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/y
y h:mm"));

        headerStyle = book.createCellStyle();

        headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

        headerStyle.setFont(boldFont);

 

        Record firstRecord;

        // If no records, cannot export

        if (this.records.size() == 0) throw new DataException("Record
Set Contains No Rows");

 

        // Get reference to first record in set for creating column
heads and indexes

        firstRecord = (Record)this.records.get(0);

 

        // Array to contain column indexes and display order to export
to excel

        final int[] excelColumnIndexes = new
int[firstRecord.columns.length];

        int excelColumnCount = 0;

 

        // No columns specified - implicit - include all columns in xml
defined order

        if (this.excelColumnNames.size()== 0) {

            for (int i = 0; i < firstRecord.columns.length; i++) {

                excelColumnIndexes[excelColumnCount] = i;

                excelColumnCount++;

            }

        }

        // Build explicit ordered array of column indexes if specified
by setExcelColumnNames() method

        else {

            final Iterator i = this.excelColumnNames.iterator();

            while (i.hasNext()) {

                final String columnName = (String)i.next();

                try {

                    excelColumnIndexes[excelColumnCount] =
firstRecord.getColumnIndexByName(columnName);

                    excelColumnCount++;

                }

                catch (Exception e) {}

            }

        }

 

        // Create header row and column heading cells

        row = sheet.createRow((short)0);

        for (int i = 0; i < excelColumnCount; i++) {

            cell = row.createCell((short)i);

            final Column column =
firstRecord.columns[excelColumnIndexes[i]];

            cell.setCellValue(column.getColumnDefinition().getPrompt());

            cell.setCellStyle(headerStyle);

        }

 

        // Write specified filtered / sorted columns and records to
sheet

        int sheetRow = 0;

        final Iterator i = this.getFilteredSortedRecords().iterator();

        while (i.hasNext()) {

            final Record record = (Record)i.next();

            sheetRow++;

            row = sheet.createRow((short)sheetRow);

            for (int idx = 0; idx < excelColumnCount; idx++) {

                cell = row.createCell((short)idx);

                final Column column =
record.columns[excelColumnIndexes[idx]];

                column.setExcelValue(cell);

            }

        }

 

        // Write file

        try {

            final FileOutputStream out = new
FileOutputStream(pathPlusFile);

            book.write(out);

            out.close();

        }

        catch (Exception e) {

            e.printStackTrace();

            throw new DataException(e.getMessage());

        }

        return pathPlusFile;

    }

 

...

 

 

 

Steven M. Nelligan

 

From: Jon Svede [via Apache POI]
[mailto:ml-node+4506477-1900045859-221743@n5.nabble.com] 
Sent: Monday, June 20, 2011 8:58 AM
To: Nelligan, Steve M (Facilities & Services)
Subject: Re: Problem creating an .xlsx file using POI and XSSF

 

Can you send the snippet of code that shows how you are creating/saving
the 
workbook? 

Thanks, 

Jon 





________________________________ 
From: snelligan <[hidden email]> 
To: [hidden email] 
Sent: Mon, June 20, 2011 7:55:23 AM 
Subject: Problem creating an .xlsx file using POI and XSSF 

When i try to create an .xlsx file using POI/XSSF it creates an .xlsx
file, 
but when excel tries to open it, I get the following Error: 
"Excel found unreadable content in 'dkdkd.xlsx' do you want to recover
the 
contents of this workbook?  If you trust the source of this workbook,
click 
Yes." 


When I click Yes I get a pop up which states "Repaired Records:
Worksheet 
properties from /xl/workbook.xml part (Workbook). 

Log returns the following: 
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> 
<recoveryLog 
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFi
leName>error031280_01.xml</logFileName><summary>Errors 

were detected in file 
'C:\WINDOWS\TEMP\snelliga-1.xlsx'</summary><repairedRecords 
summary="Following is a list of repairs:"><repairedRecord>Repaired
Records: 
Worksheet properties from /xl/workbook.xml part 
(Workbook)</repairedRecord></repairedRecords></recoveryLog> 

Sorry if this is a duplicate, but looking through the mailing list,
there 
were several with this problem, but I could not find a solution. 

I am using the following Jar files: 
dom4j-1.6.1.jar 
jdom.jar 
log4j-1.2.9.jar 
poi-3.7-20101029.jar 
poi-examples-3.7-20101029.jar 
poi-ooxml-3.7-20101029.jar 
poi-ooxml-schemas-3.7-20101029.jar 
poi-scratchpad-3.7-20101029.jar 
xmlbeans-2.3.0.jar 


This is the first time I have tried to program for POI.  The previous 
programmer used HSSF in the application, but because of office 2010 I am

trying to update to XSSF. 

Any help or direction would be appreciated. 

-Steve Nelligan 









-- 
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506461.html

Sent from the POI - User mailing list archive at Nabble.com. 

--------------------------------------------------------------------- 
To unsubscribe, e-mail: [hidden email] 
For additional commands, e-mail: [hidden email] 

________________________________

If you reply to this email, your message will be added to the discussion
below:

http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-us
ing-POI-and-XSSF-tp4506461p4506477.html 

To unsubscribe from Problem creating an .xlsx file using POI and XSSF,
click here
<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=
unsubscribe_by_code&node=4506461&code=c25lbGxpZ2FAaWxsaW5vaXMuZWR1fDQ1MD
Y0NjF8MjYyNDY3NjE4> . 



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4506501.html
Sent from the POI - User mailing list archive at Nabble.com.

Re: Problem creating an .xlsx file using POI and XSSF

Posted by Jon Svede <js...@yahoo.com>.
Can you send the snippet of code that shows how you are creating/saving the 
workbook?

Thanks,

Jon





________________________________
From: snelligan <sn...@illinois.edu>
To: user@poi.apache.org
Sent: Mon, June 20, 2011 7:55:23 AM
Subject: Problem creating an .xlsx file using POI and XSSF

When i try to create an .xlsx file using POI/XSSF it creates an .xlsx file,
but when excel tries to open it, I get the following Error:
"Excel found unreadable content in 'dkdkd.xlsx' do you want to recover the
contents of this workbook?  If you trust the source of this workbook, click
Yes."


When I click Yes I get a pop up which states "Repaired Records: Worksheet
properties from /xl/workbook.xml part (Workbook).

Log returns the following:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error031280_01.xml</logFileName><summary>Errors

were detected in file
'C:\WINDOWS\TEMP\snelliga-1.xlsx'</summary><repairedRecords
summary="Following is a list of repairs:"><repairedRecord>Repaired Records:
Worksheet properties from /xl/workbook.xml part
(Workbook)</repairedRecord></repairedRecords></recoveryLog>

Sorry if this is a duplicate, but looking through the mailing list, there
were several with this problem, but I could not find a solution.

I am using the following Jar files:
dom4j-1.6.1.jar
jdom.jar
log4j-1.2.9.jar
poi-3.7-20101029.jar
poi-examples-3.7-20101029.jar
poi-ooxml-3.7-20101029.jar
poi-ooxml-schemas-3.7-20101029.jar
poi-scratchpad-3.7-20101029.jar
xmlbeans-2.3.0.jar


This is the first time I have tried to program for POI.  The previous
programmer used HSSF in the application, but because of office 2010 I am
trying to update to XSSF.

Any help or direction would be appreciated.

-Steve Nelligan









--
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-creating-an-xlsx-file-using-POI-and-XSSF-tp4506461p4506461.html

Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org