To use this library in your Java program add the lib jxl. See Changing classpath in Eclipse. Create a new Java project called de. Create the de.
|Published (Last):||1 April 2004|
|PDF File Size:||9.83 Mb|
|ePub File Size:||10.35 Mb|
|Price:||Free* [*Free Regsitration Required]|
The information presented in this tutorial is intended as a brief overview as to how JExcelApi may be used to read and write data in Excel format. The tutorial is by no means exhaustive, and if something is not described here, it does not mean that it cannot be done.
The reader is encouraged to examine the API documentation and the sample code particularly Write. Reading Spreadsheets. JExcelApi can read an Excel spreadsheet from a file stored on the local filesystem or from some input stream.
The first step when reading a spreadsheet from a file or input stream is to create a Workbook. The code fragment below illustrates creating a workbook from a file on the local filesystem. File; import java. Date; import jxl. Once you have accessed the workbook, you can use this to access the individual sheets. These are zero indexed - the first sheet being 0, the second sheet being 1, and so on. You can also use the API to retrieve a sheet by name.
Once you have a sheet, you can then start accessing the cells. You can retrieve the cell's contents as a string by using the convenience method getContents. In the example code below, A1 is a text cell, B2 is numerical value and C2 is a date. The contents of these cells may be accessed as follows. The demo programs CSV. However if it is required to access the cell's contents as the exact type ie.
The section of code below illustrates how JExcelApi may be used to retrieve a genuine java double and java. Date object from an Excel spreadsheet. For completeness the label is also cast to it's correct type, although in practice this makes no difference. The example also illustrates how to verify that cell is of the expected type - this can be useful when validating that the spreadsheet has cells in the correct place.
When you have finished processing all the cells, use the close method. This frees up any allocated memory used when reading spreadsheets and is particularly important when reading large. Writing Spreadsheets. This section describes how to write out simple spreadsheet data without any formatting information, such as fonts or decimal places.
Similarly to reading a spreadsheet, the first step is to create a writable workbook using the factory method on the Workbook class. This creates the workbook object. The generated file will be located in the current working directory and will be called "output. The API can also be used to send the workbook directly to an output stream eg. If the HTTP header is set correctly, then this will launch Excel and display the generated spreadsheet. The next stage is to create sheets for the workbook.
Again, this is a factory method, which takes the name of the sheet and the position it will occupy in the workbook. The code fragment below creates a sheet called "First Sheet" at the first position. Now all that remains is to add the cells into the worksheet. This is simply a matter of instantiating cell objects and adding them to the sheet. The following code fragment puts a label in cell A3, and the number 3. There are a couple of points to note here. Firstly, the cell's location in the sheet is specified as part of the constructor information.
Once created, it is not possible to change a cell's location, although the cell's contents may be altered. The other point to note is that the cell's location is specified as column, row. Both are zero indexed integer values - A1 being represented by 0,0 , B1 by 1,0 , A2 by 0,1 and so on. Once you have finished adding sheets and cells to the workbook, you call write on the workbook,.
This final step generates the output file output. If you call close without calling write first, a completely empty file will be generated. Now write out the workbook workbook. Adding Format Information. The previous section illustrates the fundamentals of generating an Excel compatible spreadsheet using the JExcelApi.
However, as it stands Excel will render the data in the default font, and will display the numbers to 3 decimal places. In order to supply formatting information to Excel, we must make use of the overloaded constructor, which takes an additional object containing the cell's formatting information both the font and the style. The code fragment below illustrates creating a label cell for an arial 10 point font. Cell formats objects are shared, so many cells may use the same format object, eg.
This creates another label, with the same format, in cell C1. Because cell formats are shared, it is not possible to change the contents of a cell format object. If this were permitted, then changing the contents of the object could have unforeseen repurcussions on the look of the rest of the workbook. In order to change the way a particular cell is displayed, the API does allow you to assign a new format to an individual cell.
The constructors for the WritableFont object have many overloads. By way of example, the code fragment below creates a label in 16 point Times, bold italic and assigns it to position D1.
Formatting Numbers. Number formatting information may be passed to the cell format object by a similar mechanism to that described for fonts. A variety of predefined number formats are defined statically.
These may be used to format. The above code inserts the value 3. When Excel renders these cells, A5 will display as "3" and B5 will display as "3. It's possible for a user to define their own number formats, by passing in a number format string. The string passed in should be in the same format as that used by the java.
DecimalFormat class. To format a number to display up to five decimal places in cell C5, the following code fragment may be used:. It is, of course, also possible to specify font information as well eg.
Formatting Dates. Dates are handled similarly to numbers, taking in a format compatible with that used by the java. SimpleDateFormat class. In addition, several predefined date formats are specified in the jxl. DateFormat class. As a brief example, the below code fragment illustrates placing the current date and time in cell A7 using a custom format:.
As with numbers, font information may be used to display the date text by using the overloaded constructors on WritableCellFormat. For a more extensive example of writing spreadsheets, the demonstration program Write. In addition to the functionality described above, this program tests out a variety of cell, formatting and font options, as well as displaying cells with different background and foreground colours, shading and boundaries.
Copying and Modifying Spreadsheets. This section describes the scenario where a spreadsheet is read in, it's contents altered in some way and the modified spreadsheet written out. The first stage is to read in the spreadsheet in the normal way:. This creates a readable spreadsheet. To obtain a writable version of this spreadsheet, a copy must be made, as follows:. In order to improve performance, data which relates to output information eg. However, if we need to modify this spreadsheet a handle to the various write interfaces is needed, which can be obtained using the copy method above.
This copies the information that has already been read in as well as performing the additional processing to interpret the fields that are necessary to for writing spreadsheets. The disadvantage of this read-optimized strategy is that we have two spreadsheets held in memory rather than just one, thus doubling the memory requirements. For this reason copying and modifying large spreadsheets can be expensive in terms of processing and memory.
Once we have a writable interface to the workbook, we may retrieve and modify cells. The following code fragment illustrates how to modify the contents of a label cell located in cell B3 in sheet 2 of the workbook.
There is no need to call the add method on the sheet, since the cell is already present on the sheet. The contents of numerical and date cells may be modified in a similar way, by using the setValue and setDate methods respectively. Although cell formats are immutable, the contents of a cell may be displayed differently by assigning a different format object to the cell. The following code fragment illustrates changing the format of numerical cell in position C5 so that the contents will be displayed to five decimal places.
Since the copy of the workbook is an ordinary writable workbook, new cells may be added to the sheet, thus:. As before, once the modifications are complete, the workbook must be written out and closed. Now write out the workbook copy. The demo program contained in the source file ReadWrite. This demo program copies the spreadsheet passed in on the command line; if the spreadsheet to be copied is the example spreadsheet, jxlrwtest.
JExcel API – Reading and Writing Excel file in Java
Now java developers can read Excel spreadsheets, modify them with a convenient and simple API, and write the changes to any output stream e. Any operating system which can run a Java virtual machine i. Because it is Java, the API can be invoked from within a servlet, thus giving access to Excel spreadsheets over internet and intranet web applications. Some Features Reads data from Excel 95, 97, , XP, and workbooks Reads and writes formulas Excel 97 and later only Generates spreadsheets in Excel format Supports font, number and date formatting Supports shading, bordering, and coloring of cells Modifies existing worksheets Is internationalized, enabling processing in almost any locale, country, language, or character encoding formulas are currently only supported in English, French, Spanish, and German, but more can be added if translated Supports copying of charts Supports insertion and copying of images into spreadsheets Supports logging with Jakarta Commons Logging, log4j, JDK 1.
Apache POI - Java Excel APIs
The open source community has created many open source Java projects, which range from charting software to game frameworks to word processors. In this post, I introduce an open source library project for reading and writing Microsoft Excel spreadsheets. Q: I've been asked to extend my company's Java-based spreadsheet software to read and write Excel spreadsheets. Can you recommend an open source Java library that will help me with this task?