java import and export excel operations (jxl)

Author：Eve Cole Update Time：2009-12-28 16:59:50

java import and export excel operations (jxl)
Java interprets Excel data (use of jxl.jar package)
Keywords: java excel jxl.jar

jxl.jar package download address:
http://www.andykhan.com/jexcelapi/
Real download address:
http://www.andykhan.com/jexcelapi/download.html

Its characteristics are described on the website as follows:
● Supports all versions of Excel 95-2000 ● Generates Excel 2000 standard format ● Supports font, number, and date operations ● Able to modify cell attributes ● Supports images and charts It should be said that the above functions can roughly meet our needs. The most important thing is that this API is pure Java and does not depend on the Windows system. Even if it is running under Linux, it can still process Excel files correctly. It should also be noted that this set of APIs has very limited support for graphics and charts, and only recognizes the PNG format.
To build the environment, unpack the downloaded file to get jxl.jar, put it into the classpath, and the installation is complete.

Basic operations

1. Create the file to generate an Excel file named "Test Data.xls", in which the first worksheet is named "First Page". The general effect is as follows:

Java code
/*
* Created on Dec 30, 2007
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package JExcelTest.standard;

import java.io.*;
import jxl.*;
import jxl.write.*;

/**
* @author Ken
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class CreateXLS {

public static void main(String[] args) {
try {
//open file.
WritableWorkbook book = Workbook.createWorkbook(new File("d:/Test.xls"));

//create Sheet named "Sheet_1". 0 means this is 1st page.
WritableSheet sheet = book.createSheet("Sheet_1", 0);

//define cell column and row in Label Constructor, and cell content write "test".
//cell is 1st-Column,1st-Row. value is "test".
Label label = new Label(0, 0, "test");
//add defined cell above to sheet instance.
sheet.addCell(label);

//create cell using add numeric. WARN:necessarily use integrated package-path, otherwise will be throws path-error.
//cell is 2nd-Column, 1st-Row. value is 789.123.
jxl.write.Number number = new jxl.write.Number(1, 0, 789.123);
//add defined cell above to sheet instance.
sheet.addCell(number);

//add defined all cells above to case.
book.write();
//close file case.
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
After Java is compiled and executed, an Excel file will be generated at the current location.

2. Read the file. Take the Excel file we just created as an example to perform a simple reading operation. The program code is as follows:

3. Modify files You can use jExcelAPI to modify existing Excel files. When modifying Excel files, except for the different ways of opening the file, the other operations are the same as creating Excel. The following example adds a worksheet to the Excel file we have generated:
Modify the Excel class and add a worksheet
Java code
/*
* Created on Dec 30, 2007
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package JExcelTest.standard;

import java.io.*;
import jxl.*;
import jxl.write.*;

/**
* @author Ken
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class UpdateXLS {

public static void main(String[] args) {
try {
//get file.
Workbook wb = Workbook.getWorkbook(new File("d:/Test.xls"));
//open a copy file(new file), then write content with same content with Test.xls.
WritableWorkbook book =
Workbook.createWorkbook(new File("d:/Test.xls"), wb);
//add a Sheet.
WritableSheet sheet = book.createSheet("Sheet_2", 1);
sheet.addCell(new Label(0, 0, "test2"));
book.write();
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

Advanced operations

1. Data formatting in Excel does not involve complex data types. It can handle strings, numbers and dates relatively well and is sufficient for general applications.
String formatting String formatting involves elements such as font, thickness, font size, etc. These functions are mainly responsible for the WritableFont and WritableCellFormat classes. Suppose we use the following statement when generating a cell containing a string. For convenience of description, we number each line of command:
WritableFont font1= new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD);
or
//Set the font format to a format supported by excel
WritableFont font3=new WritableFont(WritableFont.createFont("楷体_GB2312"),12,WritableFont.NO_BOLD );
① WritableCellFormat format1=new WritableCellFormat(font1);
② Label label=new Label(0,0,”data 4 test”,format1)
③ Among them
I. The string format is specified: the font is TIMES, the font size is 16, and it is displayed in bold. WritableFont has a very rich set of constructors for use in different situations. There is a detailed list in the java-doc of jExcelAPI, which will not be listed here.
II. The code above uses the WritableCellFormat class. This class is very important. Through it, various attributes of the cell can be specified. There will be more descriptions in the subsequent cell formatting.
III. The constructor of the Label class is used to specify the format that the string is given. In the WritableCellFormat class, another very important method is to specify the alignment of the data. For example, for our example above, you can specify:
//Specify the horizontal alignment as centered
format1.setAlignment(jxl.format.Alignment.CENTRE);
//Specify vertical alignment as centered
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//Set automatic line wrapping
format1.setWrap(true);

2. Cell operations
A very important part of Excel is the operation of cells, such as row height, column width, cell merging, etc. Fortunately, jExcelAPI provides these supports. These operations are relatively simple, and only the relevant APIs are introduced below.
1. Merge cells
WritableSheet.mergeCells(int m,int n,int p,int q);
The function is to merge all cells from (m,n) to (p,q), for example:
WritableSheet sheet=book.createSheet("First Page",0);
//Merge all cells from the first row of the first column to the first row of the sixth column
sheet.mergeCells(0,0,5,0);
Mergers can be either horizontal or vertical. Merged cells cannot be merged again, otherwise an exception will be triggered.
2. Row height and column width
WritableSheet.setRowView(int i,int height);
The function is to specify the height of the i+1th row, for example:
//Set the height of the first row to 200
sheet.setRowView(0,200);
WritableSheet.setColumnView(int i,int width);
Its function is to specify the width of column i+1, for example:
//Set the width of the first column to 30
sheet.setColumnView(0,30);
3. Operation pictures
Java code
public static void write()throws Exception{
WritableWorkbook wwb=Workbook.createWorkbook(new File("c:/1.xls"));
WritableSheet ws=wwb.createSheet("Test Sheet 1",0);
File file=new File("C:\jbproject\PVS\WebRoot\weekhit\1109496996281.png");
WritableImage image=new WritableImage(1, 4, 6, 18,file);
ws.addImage(image);
wwb.write();
wwb.close();
}
It is very simple and the same as the way to insert cells, but it has more parameters. The WritableImage class inherits Draw. The above is just one of its construction methods. Needless to say, the last parameter is of type double. , followed by x, y, width, height. Note that the width and height here are not the width and height of the picture, but the number of units that the picture occupies. Because it inherits Draw, its type must be double. I haven’t looked into the details of how it’s implemented :) Because I’m in a rush, I’ll complete the functions first, and I’ll have time to study the rest later. I will continue to write down my experience in using it in the future.

read:
When reading, the idea is like this. First use an input stream (InputStream) to get the Excel file, then use the Workbook in jxl to get the workbook, use Sheet to get the worksheet from the workbook, and use Cell to get a certain point in the worksheet. cell.
InputStream->Workbook->Sheet->Cell, you will get the cells in the excel file

Java code
String path="c:\excel.xls";//Excel file URL
InputStream is = new FileInputStream(path);//Write to FileInputStream
jxl.Workbook wb = Workbook.getWorkbook(is); //Get the workbook
jxl.Sheet st = wb.getSheet(0);//Get the first worksheet in the workbook
Cell cell=st.getCell(0,0);//Get the first cell of the worksheet, which is A1
String content=cell.getContents();//getContents() converts the characters in Cell into strings
wb.close();//Close the workbook
is.close();//Close the input stream
String path="c:\excel.xls";//Excel file URL
InputStream is = new FileInputStream(path);//Write to FileInputStream
jxl.Workbook wb = Workbook.getWorkbook(is); //Get the workbook
jxl.Sheet st = wb.getSheet(0);//Get the first worksheet in the workbook
Cell cell=st.getCell(0,0);//Get the first cell of the worksheet, which is A1
String content=cell.getContents();//getContents() converts the characters in Cell into strings
wb.close();//Close the workbook
is.close();//Close the input stream. We can get any cell through Sheet's getCell(x,y) method, and x, y corresponds to the coordinates in excel.
For example, A1 corresponds to (0,0), A2 corresponds to (0,1), and D3 corresponds to (3,2). The coordinates in Excel start from A,1, but in jxl they all start from 0.
You can also get the number of rows and columns through Sheet's getRows() and getColumns() methods, and use them for loop control to output all the contents in a sheet.

Write:
To write content into Excel, you mainly use the classes in the jxl.write package.
The idea is this:
OutputStream<-WritableWorkbook<-WritableSheet<-Label
The Label here represents the location and content of the Cell written to the Sheet.

Java code
OutputStream os=new FileOutputStream("c:\test.xls");//Output Excel file URL
WritableWorkbook wwb = Workbook.createWorkbook(os);//Create a writable workbook
WritableSheet ws = wwb.createSheet("sheet1", 0);//Create a writable worksheet
Label labelCF=new Label(0, 0, "hello");//Create writing location and content
ws.addCell(labelCF);//Write Label into sheet
Label's constructor Label(int x, int y, String aString) xy means xy when reading, and aString is the written content.
WritableFont wf = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false);//Set the writing font
WritableCellFormat wcfF = new WritableCellFormat(wf);//Set CellFormat
Label labelCF=new Label(0, 0, "hello");//Create writing location, content and format
Another constructor of Label, Label(int c, int r, String cont, CellFormat st), can format the written content, set the font and other attributes.
You can write now
wwb.write();
Close after writing
wwb.close();
Also close the output stream
os.close;

OK, as long as you combine reading and writing, you can read data in N Excel and write it into the new Excel table you want, which is quite convenient.

The following is an example of a program:

Java code
sql = "select * from tablename";
rs = stmt.executeQuery(sql);

//Create a new Excel file
String filePath=request.getRealPath("aaa.xls");
File myFilePath=new File(filePath);
if(!myFilePath.exists())
myFilePath.createNewFile();
FileWriter resultFile=new FileWriter(myFilePath);
PrintWriter myFile=new PrintWriter(resultFile);
resultFile.close();

//Use JXL to add content to the newly created file
OutputStream outf = new FileOutputStream(filePath);
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(outf);
jxl.write.WritableSheet ws = wwb.createSheet("sheettest", 0);

int i=0;
int j=0;

for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
ws.addCell(new Label(k,0,rs.getMetaData().getColumnName(k+1)));
}

while(rs.next()){
out.println(rs.getMetaData().getColumnCount());

for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
ws.addCell(new Label(k,j+i+1,rs.getString(k+1)));
}

i++;
}
wwb.write();
wwb.close();
}catch(Exception e){e.printStackTrace();}
finally{

rs.close();
conn.close();
}

response.sendRedirect("aaa.xls");

This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/yangf1984/archive/2009/12/28/5088933.aspx