Opening an Excel file

[Sat Nar]

Hi,
response.setContentType("application/vnd.ms-excel") allows the jsp output to be viewed and downloaded in an excel format. In my application, there is a link which downloads an excel file. The excel file is of type MS Office Excel 97-2003. When i click the link it opens the file in Excel 97-2003. Is there any way by which i can make the excel to open in Office 2007?

Thanks

[Ulf Dittmer]

That's a client setting; nothing the web app can do about that. If you right-click a file of that type on your desktop, there's some menu item that lets you specify which program to use to open a particular file type, and there's also an option to make that choice permanent.

[Sat Nar]

Hi,
I am afraid i have not communicated my problem properly. First, i create a new XLS file in my servlet using the below code.

File f = new File("ABC.xls");

Using a DAO i query my DB and stuff the result to a StringBuffer.The below is the next line in the code.

response.setContentType("vnd.application/ms-excel");

Now the XLS created above is of type MS Office 99-2003. But i want to create the XLS of type Office 2007. How do we do this?

Thanks in advance.

[Ulf Dittmer]

As was explained in the other thread where you asked this question -please don't do that again in the future, by the way- that line of code doesn't create a file.

I think the default extension for Excel 2007 files is "xlsx", not "xls" (which is used for the binary Excel files).

[Ulf Dittmer]

If you want to create an Excel file, use Apache POI 3.1 for "xls" files, and POI 3.5 for "xlsx" files.

[Peter Johnson]

Are you asking what mime-type to use for Excel 2007 files? If so, use:

"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

See http://www.bram.us/2007/05/25/office-2007-mime-types-for-iis/

[Sat Nar]

Hi,
Thanks for your replies. I have added the new MIME type. When i click the link, i am getting the error

"Excel cannot open the file because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file"

I am struck here. Kindly help me out.

Thanks.

[Ulf Dittmer]

How are you now creating the file? Are you using POI 3.5?

[Sat Nar]

Hi,
Actually i am trying to open an excel file from a servlet. It works fine when the file format is .xls. But if it is .xlsx it gives the above error. There is a link in the application. When we click the link it calls the servlet. The servlet calls the DAO. The DAO queries the DB and the whole output is stuffed into a StringBuffer and the StringBuffer is written to the ServletOutputStream. I have used

File newFile = new File("ABC.xslx");

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

Kindly help me out.

Thanks.

[Ulf Dittmer]

How are you adding the data to the Excel file? If you're not using the POI library (or something similar), then it simply isn't going to work. The classes in the java.io package are not sufficient to create Excel files.

Once you've created the file, you can then stream it to the browser.

[Sat Nar]

Hi,
POI doesn't support for office 2007 .xslx format. I am stuffing the data into the StringBuffer and sending it to the ServletOutputStream.

response.setHeader("Cache-Control", "cache, must-revalidate");
response.setHeader("Pragma", "public");
response.setHeader("Content-Disposition","inline; filename=" + <file name>);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

response.setBufferSize(strBuffer.capacity());
sosStream.write(strBuffer.toString().getBytes());

sosStream.flush();
sosStream.close();

This is how the excel is created.

But the error occurs when creating .xslx file.

Thanks

[Ulf Dittmer]

POI doesn't support for office 2007 .xslx format.

POI 3.5 does; a beta version is available for download from the POI site.

I am stuffing the data into the StringBuffer and sending it to the ServletOutputStream.

You don't say how you put the data into the StringBuffer, but unless you're using POI whatever you put in there is not an Excel file.

If this was my problem, I'd start by getting POI 3.5, and use that to create a valid Excel 2007 file in a standalone application. Integrating it into the web app only complicates matters until you understand how to use the library.
[ October 07, 2008: Message edited by: Ulf Dittmer ]

[Sat Nar]

The content of the StringBuffer is the output of the query.

The code is like

rs.getString("id");

In the same way all the data is converted to String and stuffed into a StringBuffer.

Kindly let me know if you need additional information.

[Ulf Dittmer]

That confirms my suspicion that you're not using POI. You will need to do that, if you want to accomplish your goal.

[Peter Johnson]

The content of the StringBuffer is the output of the query.

Now you lost me. Is the .xlsx contents being read from a file or from a database? (I assume that 'rs' is a ResultSet.)

If you are reading from a database, why are you using getString()? The contents of an .xlsx file are binary so it should be stored in a blob and accessed as a blob.

Also, are you sure that the contents in the database is a valid .xlsx?

[Paul Clapham]

Originally posted by Peter Johnson:
The contents of an .xlsx file are binary...

I thought that was an XML-based format? In which case it would be a text file.

[Peter Johnson]

That confirms my suspicion that you're not using POI. You will need to do that, if you want to accomplish your goal.

I think that Sar would need to use POI only if he(?) is writing Java code to manipulate the spreadsheet. If he is interacting with the *.xlsx file contents only as a stream of bytes then POI is not required.

But he does have to make sure that none of his data manipulations accidentally convert the byte stream into something else (I recently fought this issue with using Grails to download files).

[Peter Johnson]

I thought that was an XML-based format? In which case it would be a text file.

Actually, it is a series of XML text files all zipped up into a zip file. So the *.xlsx file itself is binary. Try opening it with a text editor and see what you get. Or rename foo.xlsx to foo.zip and opening it with a zip utlity.
[ October 07, 2008: Message edited by: Peter Johnson ]

[Sat Nar]

Hi all,
Thanks for your replies. My requirement is pretty simple. I have query which returns some rows. This result should be displayed in an excel file whose format should be .xlsx. The current way of displaying it as i have described above works well with .xls format. But when i try the same for .xlsx format, i am facing the error.

I think that Sar would need to use POI only if he(?) is writing Java code to manipulate the spreadsheet. If he is interacting with the *.xlsx file contents only as a stream of bytes then POI is not required.

This is perfect. I am not going to manipulate the spreadsheet. Just display the results of the query in the excel file. I have modified the MIME type but it is not solving the problem. When i click the link, the file should open in the browser.

Dear Ranchers, kindly help me out. Its been 2 days and i am still not able to proceed forward.

Thanks

[Ulf Dittmer]

I really don't know what else to tell you, so let me summarize what I've said above. (By the way, I think Peter has misunderstood what you're trying to do, so I don't think what he said really applies here.)

- You can open an XLS file (or, rather, a file Excel 2003 can open - it seems that you didn't actually create an XLS file) in Excel 2007 by changing the file association on the client machine. That has nothing to do with the web app - on the server you'd do everything like you did before for Excel 2003. That change would apply to ALL XLS file, of course, so double-clicking an XLS file would from now on open Excel 2007, not Excel 2003.

- If you want to create an actual XLSX file, then POI 3.5 is the way to go; no way around that.

[Sat Nar]

Hi,
I have created a Workbook using POI 3.5. Below is my code.

File newFile = new File("ABC.xslx");

response.setHeader("Cache-Control", "cache, must-revalidate");
response.setHeader("Pragma", "public");
response.setHeader("Content-Disposition","inline; filename=" + newFile);

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

wb1.write(fos);
wb1.write(sosStream);
sosStream.flush();

But still i am getting the same error. According to mu requirement, i don't need to physically save the file in the hard disk, but i need to open it in the browser when the user clicks the link. Kindly help me out.

Thanks

[Ulf Dittmer]

Without seeing the code that creates the file it's hard to guess what might be going wrong; post it here.

Indeed you don't need to create a file on disk. With POI 3.1 you can create a Workbook in memory, and then stream its contents to an OutputStream (like a ServletOutputStream). I'm not much familiar with POI 3.5, but would assume that it works similarly; look for the examples in the "ss" and "xssf" packages in the source code.
[ October 08, 2008: Message edited by: Ulf Dittmer ]

[Sat Nar]

Hi,
I tried to create a excel through POI 3.5 using a simple java program. I am getting the same error when i try to open it.Below is my code. Kindly let me know the problem.

I am getting the error ""The file you are trying to open .xlsx is in a different format than specified by the file extension. verify the file is not corrupted and is from trusted source before opening the file. Do you want to open the file now?" when opening the .xlsx file.


import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;




public class test {

public static void main(String args[]) throws IOException{
//ExportLobBean exportLobsForm = null;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();

//ArrayList lobList = ExportLobBO.getArrayList();
//int length = lobList.size();
int rowCount=0;
int count=0;
HSSFRow headerRow = sheet.createRow((short) 0);
HSSFRow dataRow = null;


//Creating Column Names
headerRow.createCell((short)0).setCellValue("LOB-ID");
headerRow.createCell((short)1).setCellValue("LOB-NAME");
headerRow.createCell((short)2).setCellValue("WEIGHT");

for(int i=0;i<5;i++){
rowCount++;
sheet.createRow((short)rowCount);
//exportLobsForm = (ExportLobBean)lobList.get(count);
headerRow.createCell((short)0).setCellValue(1);
//LOGGER.info("Lob id "+exportLobsForm.getStrLobId());
headerRow.createCell((short)1).setCellValue(2);
//LOGGER.info("Lob name"+exportLobsForm.getStrLobName());
headerRow.createCell((short)2).setCellValue(3);
//LOGGER.info("Lob weight"+exportLobsForm.getStrLobWeight());
count++;
}
File f = new File("c:\\test.xlsx");
FileOutputStream fos = new FileOutputStream(f);
wb.write(fos);



}


}

[Ulf Dittmer]

The file you are trying to open .xlsx is in a different format than specified by the file extension.

That's correct, since you're creating an XLS file, not an XLSX file. You need to use a class called XSSFWorkbook (I think) instead of HSSFWorkbook. The POI 3.5 download includes a page that explains how to migrate HSSF code (for creating XLS files) to XSSF/SS code (for creating XLSX files).

I'll move the topic to the Other Open Source projects forum, where POI is discussed.
[ October 08, 2008: Message edited by: Ulf Dittmer ]

[Peter Johnson]

(By the way, I think Peter has misunderstood what you're trying to do, so I don't think what he said really applies here.)

Ulf, you are absolutely correct. Based on earlier posts by Sat, especially the example code which contained the statement:

File newFile = new File("ABC.xslx");

I had thought that Sat was attempting to work with an existing xlsx file. Which is why I responded as I did when Sat showed what I thought was the file contents being read from a string out of a database. Not until Sat make this statement:

Just display the results of the query in the excel file.

did I realize that Sat was attempting to construct an XLSX file to be sent to the client.

Moral of the story: it helps to be very clear and precise in exactly what you are doing to get the correct help.

But I have a nagging question. In the old code when an XLS file was transferred, was the data being sent to the client's browser really a valid XLS binary or was the data being transferred CSV (comma-separated-value) text and the mime-type was being used to coax the browser into using Excel to display the data?

[Sat Nar]

Hi,
Thanks for the replies. I have created a workbook(.xlsx). Now i want it to be written to ServletOutputStream.

Below is my code.

File newFile = new File("c:\\TESTMAPPINGDATA.xslx");
FileOutputStream fos = new FileOutputStream(newFile);
XSSFWorkbook xb = generateWorkbook();
xb.write(fos);
sosStream.write(xb);
sosStream.flush();
sosStream.close();

But its saying "The method write(int)in the type OutputStream is not applicable for the arguements XSSFWorkbook"

So how do we write this workbook created through XSSFWorkbook into ServletOutputStream?

Kindly help me out.

Thanks.

[Paul Clapham]

Well, you already have code that writes the workbook to a FileOutputStream. (I don't know why you have that code in your servlet, but that's another question.) The code to write the workbook to the servlet's outputstream would be identical, except you replace the FileOutputStream's variable by the servlet's output stream's variable.

[Sat Nar]

Hi,
Thanks la lot for your replies. I tried creating a .xlsx file using POI 3.5 through a separate java program and it got created properly. Now i tried integrating the code into my Servlet. When executing my application(i.e when the link which creates the excel is clicked), i am getting the below error.

java.lang.NoClassDefFoundError rg/apache/poi/xssf/usermodel/XSSFWorkbook

I have placed the jars related to the POI under WEB-INF/lib.

Do i need to add anything in the Weblogic server classpath?

Kindly let me know the solution.

Thanks

[Sat Nar]

Hi,
I resolved this by placing the POI 3.5 related jars in the Weblogic Classpath in the Weblogic Startup script. Now, i am getting the below error.

java.lang.UnsupportedClassVersionError: org/apache/poi/xssf/usermodel/XSSFWorkbook (Unsupported major.minor version 49.0)


I am using jdk142_08.

Kindly let me know by which jdk POI 3.5 classes have been generated.

Thanks in advance.

[ October 15, 2008: Message edited by: Sat Nar ]
[ October 15, 2008: Message edited by: Sat Nar ]

[Ulf Dittmer]

49.0 is the version created by Java 5, so that's what you need to run it. I'm not sure if POI 3.5 actually needs Java 5 -check the documentation- or if you could just recompile it using Java 1.4, but that's easy to find out.

[venkataramana sat]

HI sat.

I hope you have the sample source code , which will download the xslx file without giving any error message.

Could you please post the code here with the java version and poi version.

Thanks for your time.