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