You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by rhyashu <as...@walgreens.com> on 2017/02/02 19:09:33 UTC

Excel has unreadable content Error When creating excel using SXSSF API

I am getting "Excel has unreadable content Error" When creating excel using
SXSSF API.
Below are the jars that i am using:
poi-3.9
poi-ooxml-3.9.jar
poi-ooxml-schemas-3.9.jar
xmlbeans-2.4.0.jar

public class CarveOutReportXLSFileDownload extends AbstractXlsxStreamingView
{

	protected void buildExcelDocument(Map<String, Object> model, Workbook
workbook, HttpServletRequest request,
			HttpServletResponse response) throws WalgreensException {
		
		final String PAGENAME = "report.carveList";
		DashboardSummaryResponseBean responseBean =
(DashboardSummaryResponseBean)model.get("data");
		try {
			String filename = ApplicationConstants.REPORT_CARVE_OUT_FILENAME
					+ DateFormatter.format(
							ApplicationConstants.ADHOC_FILE_DATE, new Date())
					+ ".xlsx";

			String reportRuntime = DateFormatter.format("MM/dd/yyyy hh:mm a",
					new Date());

			workbook = new SXSSFWorkbook(1000);
			DataFormat dataformat = workbook.createDataFormat();
			CellStyle dataStyle = workbook.createCellStyle();
			dataStyle.setDataFormat(dataformat.getFormat("text"));
			dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			dataStyle.setAlignment(CellStyle.ALIGN_CENTER);

			
			carveOutSheet.setColumnWidth(0, 120 * 25);
			carveOutSheet.setColumnWidth(1, 120 * 25);
			carveOutSheet.setColumnWidth(2, 200 * 25);
			carveOutSheet.createFreezePane(0, 9);

			int rowCnt = 9;
			if (responseBean.getMedicaidCarveOutList() != null
					&& responseBean.getMedicaidCarveOutList().size() != 0) {
				for (MedicaidReportingBean bean : responseBean
						.getMedicaidCarveOutList()) {
					row = carveOutSheet.createRow(rowCnt);
					colIdx = 0;
					row.createCell(colIdx).setCellValue(
							new HSSFRichTextString(bean.getBin()));
					row.getCell(colIdx).setCellStyle(dataStyle);
					colIdx++;
					row.createCell(colIdx).setCellValue(
							new HSSFRichTextString(bean.getPcn()));
					row.getCell(colIdx).setCellStyle(dataStyle);
					colIdx++;
					row.createCell(colIdx).setCellValue(
							new HSSFRichTextString(bean.getGroupId()));
					row.getCell(colIdx).setCellStyle(dataStyle);
					colIdx++;
					rowCnt++;
				}
			} else {
				row = carveOutSheet.createRow(rowCnt);
				row.createCell(0).setCellValue(
						new HSSFRichTextString(Language.getText(PAGENAME,
								"noRecordFound")));
				CellRangeAddress cellRangeAddress = new CellRangeAddress(
						rowCnt, rowCnt, 0, 2);
				carveOutSheet.addMergedRegion(cellRangeAddress);

				RegionUtil.setBorderTop(CellStyle.BORDER_NONE,
						cellRangeAddress, carveOutSheet, workbook);
				RegionUtil.setBorderLeft(CellStyle.BORDER_THIN,
						cellRangeAddress, carveOutSheet, workbook);
				RegionUtil.setBorderRight(CellStyle.BORDER_THIN,
						cellRangeAddress, carveOutSheet, workbook);
				RegionUtil.setBorderBottom(CellStyle.BORDER_THIN,
						cellRangeAddress, carveOutSheet, workbook);

			}
		
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("content-disposition", "attachment; filename="
					+ filename);
			OutputStream os = response.getOutputStream();
			workbook.write(os);
			os.flush();
			os.close();
		} catch (Exception ex) {
			ex.printStackTrace();
			//return false;
		}
		logger.debug("in CarveOutReportXLSFileDownload.execute(): END");
		//return true;
	}

	/*@Override
	protected void buildExcelDocument(Map<String, Object> model, Workbook
workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		// TODO Auto-generated method stub
		
	}*/

}




--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Excel-has-unreadable-content-Error-When-creating-excel-using-SXSSF-API-tp5726456.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: Excel has unreadable content Error When creating excel using SXSSF API

Posted by rhyashu <as...@walgreens.com>.
I did try to replicate the scenario on a temp WAS system with JRE 1.7 and i
was able to run the poi-3.11 version to generate XLSX using streaming API
but after excel file was generated i was getting the "unreadble content
found" error, screen shot attached. 
This is the same error that i was getting in JRE 1.6 with older POI jar like
poi-3.9 etc.

Error1.jpg
<http://apache-poi.1045710.n5.nabble.com/file/n5726475/Error1.jpg>  



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Excel-has-unreadable-content-Error-When-creating-excel-using-SXSSF-API-tp5726456p5726475.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: Excel has unreadable content Error When creating excel using SXSSF API

Posted by rhyashu <as...@walgreens.com>.
Hi These are all the dependencies that i shared earlier.If you want i can
share the complete build.gradle file.
I am not getting anything in the logs during build.

Thanks for your help really appreciate it.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Excel-has-unreadable-content-Error-When-creating-excel-using-SXSSF-API-tp5726456p5726473.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: Excel has unreadable content Error When creating excel using SXSSF API

Posted by Javen O'Neal <on...@apache.org>.
Can you attach the log showing all of the subdependencies and versions that
are pulled in by your build script?

On Feb 2, 2017 12:56 PM, "rhyashu" <as...@walgreens.com> wrote:

Thanks, Yes there are other dependencies as well mentioned below:
dependencies {
        // spring dependencies
        compile("org.springframework.boot:spring-boot-
configuration-processor")
        compile("org.springframework.boot:spring-boot-starter")
        compile("org.springframework.boot:spring-boot-starter-aop")
        compile("org.springframework.boot:spring-boot-starter-web") {
                exclude group: 'org.springframework.boot', module:
'spring-boot-starter-tomcat'
        }
        compile("org.springframework.boot:spring-boot-starter-jdbc")
        compile("org.springframework.boot:spring-boot-starter-security")
        compile("org.springframework.boot:spring-boot-starter-mail")
        compile("org.springframework.boot:spring-boot-starter-cache")
        compile("org.springframework.boot:spring-boot-starter-actuator")
        compile("org.springframework.boot:spring-boot-starter-thymeleaf")
        //compile("org.thymeleaf:thymeleaf-spring4:2.1.1.RELEASE")
        // Commons framework jars
        compile("commons-validator:commons-validator:1.5.1")

        // Spring Mobile Framework
        compile("org.springframework.boot:spring-boot-starter-mobile")

        // runtime dependencies
        runtime("org.springframework.boot:spring-boot-starter-thymeleaf")
        runtime("org.springframework.boot:spring-boot-starter-log4j2")

        // javax dependencies
        providedCompile("javax.servlet:javax.servlet-api")

        // apache commons dependencies
        compile("org.apache.commons:commons-lang3")
        compile("commons-beanutils:commons-beanutils")
        compile("commons-fileupload:commons-fileupload")
    compile("org.apache.httpcomponents:httpclient:4.2.3")

        // external dependencies
        compile("org.mybatis:mybatis:3.3.0")
        compile("org.mybatis:mybatis-spring:1.2.3")
        compile("org.apache.poi:poi")
        compile("org.apache.poi:poi-ooxml")
        compile("org.jolokia:jolokia-core")
        //compile("com.itextpdf:itextpdf:2.1.7")


        //local dependencies
        //compile("com.walgreens:accelerate:" + project.ext["accelerate.
version"])
        //compile("oracle:ojdbc:" + project.ext["ojdbc.version"])
        compile files("src/build/lib/accelerate-" +
project.ext["accelerate.version"] + ".jar")
        compile files("src/build/lib/ojdbc" + project.ext["ojdbc.version"] +
".jar")
        compile files("src/build/lib/iText-2.1.7.jar")
        compile files("src/build/lib/javacsv-2.0.jar")
        // itextpdf-5.5.8.jar

compile("com.itextpdf:itextpdf:5.5.8")


        // webjars
        runtime("org.webjars:normalize.css:3.0.2")
        runtime("org.webjars:modernizr:2.8.3")
        runtime("org.webjars:font-awesome:4.5.0")
        runtime("org.webjars:jquery:2.1.4")
        runtime("org.webjars:bootstrap:3.3.6")
        runtime("org.webjars:angularjs:1.4.8")
        runtime("org.webjars:datatables:1.10.11")
        runtime("org.webjars:datatables-tools:2.2.4-1")
        runtime("org.webjars:datatables-fixedheader:2.1.2-1")
        runtime("org.webjars.bower:datatables-fixedcolumns:3.0.4") {
                exclude module: 'jquery'
                exclude module: 'datatables'
        }
        runtime("org.webjars:bootstrap-tokenfield:0.12.1")
        runtime("org.webjars:jquery-ui:1.11.4")
        runtime("org.webjars.bower:moment:2.12.0")
        runtime("org.webjars.bower:jquery.maskedinput:1.4.1")



--
View this message in context: http://apache-poi.1045710.n5.
nabble.com/Excel-has-unreadable-content-Error-When-
creating-excel-using-SXSSF-API-tp5726456p5726464.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: Excel has unreadable content Error When creating excel using SXSSF API

Posted by rhyashu <as...@walgreens.com>.
Thanks, Yes there are other dependencies as well mentioned below:
dependencies {
	// spring dependencies
	compile("org.springframework.boot:spring-boot-configuration-processor")
	compile("org.springframework.boot:spring-boot-starter")
	compile("org.springframework.boot:spring-boot-starter-aop")
	compile("org.springframework.boot:spring-boot-starter-web") {
		exclude group: 'org.springframework.boot', module:
'spring-boot-starter-tomcat'
	}
	compile("org.springframework.boot:spring-boot-starter-jdbc")
	compile("org.springframework.boot:spring-boot-starter-security")
	compile("org.springframework.boot:spring-boot-starter-mail")
	compile("org.springframework.boot:spring-boot-starter-cache")
	compile("org.springframework.boot:spring-boot-starter-actuator")
	compile("org.springframework.boot:spring-boot-starter-thymeleaf")
	//compile("org.thymeleaf:thymeleaf-spring4:2.1.1.RELEASE")
	// Commons framework jars
	compile("commons-validator:commons-validator:1.5.1")
	
	// Spring Mobile Framework
	compile("org.springframework.boot:spring-boot-starter-mobile")
	
	// runtime dependencies
	runtime("org.springframework.boot:spring-boot-starter-thymeleaf")
	runtime("org.springframework.boot:spring-boot-starter-log4j2")
	
	// javax dependencies
	providedCompile("javax.servlet:javax.servlet-api")

	// apache commons dependencies
	compile("org.apache.commons:commons-lang3")
	compile("commons-beanutils:commons-beanutils")
	compile("commons-fileupload:commons-fileupload")
    compile("org.apache.httpcomponents:httpclient:4.2.3")
    
	// external dependencies
	compile("org.mybatis:mybatis:3.3.0")
	compile("org.mybatis:mybatis-spring:1.2.3")
	compile("org.apache.poi:poi")
	compile("org.apache.poi:poi-ooxml")
	compile("org.jolokia:jolokia-core")
	//compile("com.itextpdf:itextpdf:2.1.7")
	
	
	//local dependencies
	//compile("com.walgreens:accelerate:" + project.ext["accelerate.version"])
	//compile("oracle:ojdbc:" + project.ext["ojdbc.version"])
	compile files("src/build/lib/accelerate-" +
project.ext["accelerate.version"] + ".jar")
	compile files("src/build/lib/ojdbc" + project.ext["ojdbc.version"] +
".jar")
	compile files("src/build/lib/iText-2.1.7.jar")
	compile files("src/build/lib/javacsv-2.0.jar")
	// itextpdf-5.5.8.jar

compile("com.itextpdf:itextpdf:5.5.8")
	
	
	// webjars
	runtime("org.webjars:normalize.css:3.0.2")
	runtime("org.webjars:modernizr:2.8.3")
	runtime("org.webjars:font-awesome:4.5.0")
	runtime("org.webjars:jquery:2.1.4")
	runtime("org.webjars:bootstrap:3.3.6")
	runtime("org.webjars:angularjs:1.4.8")
	runtime("org.webjars:datatables:1.10.11")
	runtime("org.webjars:datatables-tools:2.2.4-1")
	runtime("org.webjars:datatables-fixedheader:2.1.2-1")
	runtime("org.webjars.bower:datatables-fixedcolumns:3.0.4") {
		exclude module: 'jquery'
		exclude module: 'datatables'
	}
	runtime("org.webjars:bootstrap-tokenfield:0.12.1")
	runtime("org.webjars:jquery-ui:1.11.4")
	runtime("org.webjars.bower:moment:2.12.0")
	runtime("org.webjars.bower:jquery.maskedinput:1.4.1")



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Excel-has-unreadable-content-Error-When-creating-excel-using-SXSSF-API-tp5726456p5726464.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: Excel has unreadable content Error When creating excel using SXSSF API

Posted by Javen O'Neal <on...@apache.org>.
Does your project have any other dependencies?

What dependencies does WAS bring in?

On Feb 2, 2017 12:17, "rhyashu" <as...@walgreens.com> wrote:

> I was using poi-3.11, poi-ooxml-3.11 and xmlbeans-2.6 versions and i was
> able
> to run the reports fine in my local system but when i tried deploying that
> application in WAS8.5 with JRE 1.6 its started giving me that error.
> Please let me know what all jar i should be including and if there is any
> version mismatch between what i have used.
>
> Thanks a lot for your help.
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.
> nabble.com/Excel-has-unreadable-content-Error-When-
> creating-excel-using-SXSSF-API-tp5726456p5726462.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: Excel has unreadable content Error When creating excel using SXSSF API

Posted by rhyashu <as...@walgreens.com>.
I was using poi-3.11, poi-ooxml-3.11 and xmlbeans-2.6 versions and i was able
to run the reports fine in my local system but when i tried deploying that
application in WAS8.5 with JRE 1.6 its started giving me that error.
Please let me know what all jar i should be including and if there is any
version mismatch between what i have used.

Thanks a lot for your help.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Excel-has-unreadable-content-Error-When-creating-excel-using-SXSSF-API-tp5726456p5726462.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: Excel has unreadable content Error When creating excel using SXSSF API

Posted by Javen O'Neal <on...@apache.org>.
The latest version of POI is built and tested with OpenJDK and Oracle Java
1.6, so there shouldn't be a problem with your JVM.

Make sure you aren't mixing POI versions, which could be brought in as
dependencies for other libraries.
https://poi.apache.org/faq.html

Also make sure you have the right ooxml schemas and xmlbeans dependencies.
https://poi.apache.org/overview.html

On Feb 2, 2017 11:33, "rhyashu" <as...@walgreens.com> wrote:

I tried with POI 3.11 version but with that i am getting some other error. I
researched and found that it has somthing to do with JRE 1.6. Our app is
currently running on WAS 8.5 with JRE 1.6. Thats why it throw below error:
java.lang.NoSuchMethodError:
javax/xml/stream/XMLEventFactory.newFactory()Ljavax/xml/stream/
XMLEventFactory;
at
org.apache.poi.openxml4j.opc.internal.marshallers.
PackagePropertiesMarshaller

And jar used are:
poi-11
poi-ooxml-11

I cant upgrade the JRE as of now. SO i tried using the older versions,
Please let me know if there is any other workaround. Thanks.



--
View this message in context: http://apache-poi.1045710.n5.
nabble.com/Excel-has-unreadable-content-Error-When-
creating-excel-using-SXSSF-API-tp5726456p5726458.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: Excel has unreadable content Error When creating excel using SXSSF API

Posted by rhyashu <as...@walgreens.com>.
I tried with POI 3.11 version but with that i am getting some other error. I
researched and found that it has somthing to do with JRE 1.6. Our app is
currently running on WAS 8.5 with JRE 1.6. Thats why it throw below error:
java.lang.NoSuchMethodError:
javax/xml/stream/XMLEventFactory.newFactory()Ljavax/xml/stream/XMLEventFactory;
at
org.apache.poi.openxml4j.opc.internal.marshallers.PackagePropertiesMarshaller

And jar used are:
poi-11
poi-ooxml-11

I cant upgrade the JRE as of now. SO i tried using the older versions,
Please let me know if there is any other workaround. Thanks.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Excel-has-unreadable-content-Error-When-creating-excel-using-SXSSF-API-tp5726456p5726458.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: Excel has unreadable content Error When creating excel using SXSSF API

Posted by Nick Burch <ap...@gagravarr.org>.
On Thu, 2 Feb 2017, rhyashu wrote:
> I am getting "Excel has unreadable content Error" When creating excel using
> SXSSF API.
> Below are the jars that i am using:
> poi-3.9
> poi-ooxml-3.9.jar
> poi-ooxml-schemas-3.9.jar
> xmlbeans-2.4.0.jar

Why are you using an old version of Apache POI? What happens when you 
upgrade?

Nick

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