Summary |  Admin |  Home Page |  Files |  Javadoc |  FAQ | 

JExcel API Frequently Asked Questions
From the JExcel API group located at
How do I use JXL?
Assuming your class is named MyClass with no package:
	javac -classpath jxl.jar
	java -classpath jxl.jar;. MyClass
	javac -classpath jxl.jar
	java -classpath jxl.jar:. MyClass
	Right click on your Project, Select Java Build Path, open Libraries tab,
	Add External JARs..., select jxl.jar
Can JExcelApi read and write Excel 2003 or Excel XP files?
How can I copy a worksheet in one workbook to a new worksheet in another workbook?
This can be done, but requires a little work. Firstly, you have to copy it cell (within a couple of nested for loops). For each cell you need to invoke the copyTo() method, which will produce a deep copy. However the format is only shallow copied, so you will need to get the cell format and use the copy constructor of that, and then call setCellFormat on the cell you have just copied. Then add the duplicate cell to the new spreadsheet

The code might look as follows:
  for (int i = 0 ; i < numrows ; i++)
    for (int j = 0 ; j < numcols ; j++)
      readCell = sheet.getCell(i, j);
      newCell = readCell.copyTo(i, j);
      readFormat = readCell.getCellFormat();
      newFormat = new WritableCellFormat(readFormat);
Answered By: Andy
How do I add a newline or carriage-return to a Label cell?
Use "\012" or "\n". Also make sure to turn on cell wrapping with WritableCellFormat.setWrap(true) or the newline will show up as a square (unknown character).
How do I force Excel to open inside Internet Explorer instead of a separate window?

Most people assume that setting the HTTP Content-Type to "application/" and/or using an extension of ".xls" for downloaded workbooks is enough. For most recent default installations of Office, Windows, and IE, it is enough. However, for some older installations of Office, Windows, and IE, many of us have seen that Excel still opens in a separate window (outside of IE).

The other thing which must be done is to edit the advanced MIME type settings on the Windows computers which will be hitting your web site. In other words, your customers must do this next step: there is nothing you can do from your response stream (or anywhere else on your web site) to enforce this advanced MIME setting.

The place where the MIME setting is changed varies from Windows version to version. I will give the directions for Windows 2000.

  1. Open File Explorer
  2. Goto the Tools/Folder Options menu
  3. Open the File Types tab
  4. Select the XLS (or XLT if using templates) registered file type and click "Advanced" button.
  5. Make sure the "Browse in same window" checbox is CHECKED.
  6. Click OK and Close.

That's it.

I remember older versions of Windows and/or IE made these settings available in IE, not File Explorer.

Make sure you still set the Content-Type of response streams to "application/" and use an extension of ".xls" for downloaded files.

Thanks go out to Dermot Balson and the microsoft.public.excel.programming newsgroup.

Answered By: Eric H. Jung
How do I get rid of OutOfMemory exceptions?
The JVM places an upper limit on the amount of memory available to the current process in order to prevent runaway processes gobbling system resources and making the machine grind to a halt. When reading or writing large spreadsheets, the JVM may require more memory than has been allocated to the JVM by default - this normally manifests itself as a java.lang.OutOfMemory exception. By default, each workbook allocates 5MB when created and allocates more memory in 5MB increments. These values can be changed by using class WorkbookSettings. Often the OutOfMemory exception can be removed by lowering these values (note that performance may suffer as a result). Alternatively, you can you can allocate more memory to the JVM using the -Xms and -Xmx options; e.g., to allocate an initial heap size of 10 MB, with 100 MB as the upper bound, you should start the JVM like this: java -Xms10m -Xmx100m MyClass. In order to allocate more memory in this manner to servlets/JSPs, consult the help documentation for the Web Application Server. Oftentimes, as is the case with Tomcat and WebLogic, a simple change to a startup script is all that's necessary.
How do I read or write a chart to/from a spreadsheet?
Charts that already exist in a spreadsheet can be copied to another spreadsheet. New charts can't yet be inserted into a spreadsheet, but by using a chart in an pre-existing spreadsheet as a template, you can create new spreadsheets that use that template chart but plug-in new data values at runtime. See message #1401 at
How do I read or write images to/from a spreadsheet?
Adam Caldwell has written a patch that allows the insertion of BMPs. The patch for JExcelApi version 2.3.11 is at Patches for other versions are also available. Version 2.4 of JExcelApi introduced native support for images. You can insert PNG files into a spreadsheet using sheet.addImage(). Adam has no plans on updating his BMP patch to later versions of JexcelAPI.
I create a very large Excel file using JExcelApi. If I open the file in Excel and save it without making any changes, the files shrinks to about half its original size. Why?
Excel performs optimizations to reduce file size when possible, but JExcelApi does not. Here some examples:
  1. JExcelApi writes all strings in Unicode, whereas Excel will save strings as single-byte ASCII encoding when it can. This can halve the size of all stored strings.
  2. If many cells use the same formula, Excel will only write this formula to the underlying file once, whereas JExcelApi writes the formula once for each cell.
  3. If you write many blank cells sequentially, Excel groups these into a single MULBLANK record when saving, whereas JExcelApi writes individual records for each cell.
I get the exception "reference to Number is ambiguous; both class jxl.write.Number in package jxl.write and class Java.lang.Number in package java.lang match
You can either import jxl.write.Number at the top of the source file, or fully-qualify each reference to Number with jxl.write.Number in the source code.
I'm getting the error message 'too many different cell formats'
You need to reuse old formats rather than create a new one for each cell. For example, use code like this:
  WritableFont boldRedFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD); 	
  WritableCellFormat boldRed = new WritableCellFormat(boldRedFont);
  for (int row = 0; row<100; row++)
    sheet.addCell(new Label(0,row,"Row "+row,boldRed);
instead of
  WritableFont boldRedFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD); 	
  for (int row = 0; row<100; row++)
    sheet.addCell(new Label(0,row,"Row "+row,new WritableCellFormat(boldRedFont));
also, it's important that you Do Not declare your cell formats as static. As a cell format is added to a sheet, it gets assigned an internal index number. If you have two threads (such as in a Web environment) writing two different spreadsheets, the format numbers will get confused and the resulting sheet could either be corrupted or incorrect.
Why do dates appear one day less that is stored in Excel?
This is because java automatically adjusts for your local timezone information. As Excel dates are not concerned with timezone information, any hours java subtracts for timezone adjustments make it look as if the date has been adjusted by a whole day, instead of a couple of hours. This is explained in more detail on Andy's Page.
How do I set the width of Columns and the height of Rows?
See the javadoc for: WritableSheet.setColumnView() and WritableSheet.setRowView()
How do I do the equivilent of Excel's "Format/Column/Auto Fit Selection"?
There is no API function to do this for you. You'll need to write code that scans the cells in each column, calculates the maximum length, and then calls setColumnView() accordingly. This will get you close to what Excel does but not exactly. Since most fonts have variable width characters, to get the exact same value, you would need to use FontMetrics to calculate the maximum width of each string in the column. No one has posted code on how to do this yet. Feel free to post code to the Yahoo! group or send it directly to the FAQ author's listed at the bottom of this page.
How do I do the equivilent of Excel's "Format/Row/Auto Fit Selection"?
There is no API function to do this for you, however you can simulate it under most conditions. If you do not explicitly set a row's size, Excel will re-size it on load if any of the cells in the row are set to auto-wrap (format.setWrap(true)). Excel will then resize the row to be large enough to handle the biggest cell of data. The only exception to this rule I've found is when the auto-wrap format is set on a merged cell.
How do I read an Excel File uploaded through a Web browser to a Servlet?
Get Jakarata Commons-Fileupload
Create in input form like this:
	<form name="frm" action="/servlet/MyServlet" method="post" enctype="multipart/form-data">
	<input type="file" name="fl_upload">
	<input type="submit" value="Upload">
	<input type="reset" value="cancel">
in your servlet, include code like this:
		DiskFileUpload fu = new DiskFileUpload();
		List fileList = fu.parseRequest(request);
		InputStream uploadedFileStream = null;
		String uploadedFileName = null; // name of file on user's computer
		for (Iterator i = fileList.iterator(); i.hasNext(); )
			FileItem fi = (FileItem);
			if (fi.isFormField())
				String key = fi.getFieldName();
				String val = fi.getString();
				System.out.println("Form parameter " + key + "=" + val);
			} else
				if (fi.getSize() < 1)
					throw new Exception("No file was uplaoded");
				uploadedFileName = fi.getName();
				uploadedFileStream = fi.getInputStream();

		Workbook workbook = Workbook.getWorkbook(uploadedFileStream);
How do I output an Excel file from a Servlet?
Like this:
	import javax.servlet.ServletException;
	import javax.servlet.http.HttpServlet;
	import javax.servlet.http.HttpServletRequest;
	import javax.servlet.http.HttpServletResponse;
	import jxl.Workbook;
	import jxl.write.Label;
	import jxl.write.WritableSheet;
	import jxl.write.WritableWorkbook;
	public class Sample extends HttpServlet
	 public void doGet(HttpServletRequest request, HttpServletResponse response)
	 throws ServletException, IOException
	  OutputStream out = null;
	   response.setHeader("Content-Disposition", "attachment; filename=sampleName.xls");
	   WritableWorkbook w = Workbook.createWorkbook(response.getOutputStream());
	   WritableSheet s = w.createSheet("Demo", 0);
	   s.addCell(new Label(0, 0, "Hello World"));
	  } catch (Exception e)
	   throw new ServletException("Exception in Excel Sample Servlet", e);
	  } finally
	   if (out != null)

I need to insert an arbitrary image. How do I do this?
JExcelAPI requires a PNG file. If you are using Java 1.4.1 or later, you could convert your image to PNG on the fly using code like this:
	import java.awt.image.BufferedImage;
	import javax.imageio.ImageIO;
	private static final double CELL_DEFAULT_HEIGHT = 17;
	private static final double CELL_DEFAULT_WIDTH = 64;
	File imageFile = new File(GIF_OR_JPG_IMAGE_FILE);
	BufferedImage input =;
	ByteArrayOutputStream baos = new ByteArrayOutputStream();
	ImageIO.write(input, "PNG", baos);
	sheet.addImage(new WritableImage(1,1,input.getWidth() / CELL_DEFAULT_WIDTH,
		input.getHeight() / CELL_DEFAULT_HEIGHT,baos.toByteArray()));
If you know you have a PNG file, but don't know the size of it, you can load it into a BufferedImage and call getWidth()/getHeight().
If you want to avoid the 1.4.1 dependancy, there's a public domain Java class (ImageInfo) that can be used to find the width and height of the image.
Answered By: Adam Caldwell & Ganesh Venkataraman
Does JXL support feature XXXXXX?
Maybe. JXL supports some things on copy that it doesn't support via the WritableSheet interface. For example, you can copy macros from a template to an output file, but you can't create a macro.

To figure out if JExcelAPI supports feature XXXXXX on a copy, perform a simple test:

  1. Create a spreadsheet using feature XXXXXX
  2. Run the command: java -jar jxl.jar -rw TestFile.xls CopiedFile.xls
  3. Open up CopiedFile.xls in Excel and see if feature XXXXXX is still present
If the answer is "No", then you have two options:
  1. Try to figure out how to implement it yourself in the source code
  2. Create an activation macro in your template that do feature XXXXXX for you
There are a number of features where the answer is known to be "No":
  • Pivot Tables
  • Dropdown Lists
  • Rich Text in cells
  • Set repeating rows
  • Password Protection
FAQ Authors: Eric H. Jung (, Adam Caldwell (