Using this API, non-Windows operating systems can also process Excel data tables through pure Java applications. Because it is written in Java, we can call the API through JSP and Servlet in the web application to access the Excel data table.
Copy the code code as follows:
package com.yonyou.test;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class JxlTable {
private final static JxlTable jxlTable = new JxlTable();
public static JxlTable getInstance() {
return jxlTable;
}
public JxlTable() {
}
public boolean createTable(String header, String[] body, String filePath) {
boolean createFlag = true;
WritableWorkbook book;
try {
//Generate excel file based on path
book = Workbook.createWorkbook(new File(filePath));
//Create a sheet named "Table"
WritableSheet sheet = book.createSheet("Table", 0);
//Set NO column width
sheet.setColumnView(1, 5);
//Remove the grid lines in the entire sheet
sheet.getSettings().setShowGridLines(false);
Label tempLabel = null;
// Header output
String[] headerArr = header.split(",");
int headerLen = headerArr.length;
// Loop to write header content
for (int i = 0; i < headerLen; i++) {
tempLabel = new Label(1 + i, 1, headerArr[i],
getHeaderCellStyle());
sheet.addCell(tempLabel);
}
// table body output
int bodyLen = body.length;
// Loop to write table body content
for (int j = 0; j < bodyLen; j++) {
String[] bodyTempArr = body[j].split(",");
for (int k = 0; k < bodyTempArr.length; k++) {
WritableCellFormat tempCellFormat = null;
tempCellFormat = getBodyCellStyle();
if (tempCellFormat != null) {
if (k == 0 || k == (bodyTempArr.length - 1)) {
tempCellFormat.setAlignment(Alignment.CENTRE);
}
}
tempLabel = new Label(1 + k, 2 + j, bodyTempArr[k],
tempCellFormat);
sheet.addCell(tempLabel);
}
}
book.write();
book.close();
} catch (IOException e) {
createFlag = false;
System.out.println("EXCEL creation failed!");
e.printStackTrace();
} catch (RowsExceededException e) {
createFlag = false;
System.out.println("EXCEL unit setting creation failed!");
e.printStackTrace();
} catch (WriteException e) {
createFlag = false;
System.out.println("EXCEL writing failed!");
e.printStackTrace();
}
return createFlag;
}
public WritableCellFormat getHeaderCellStyle() {
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);
WritableCellFormat headerFormat = new WritableCellFormat(
NumberFormats.TEXT);
try {
//Add font settings
headerFormat.setFont(font);
//Set the cell background color: the header is yellow
headerFormat.setBackground(Colour.YELLOW);
//Set header table border style
//The entire table line is thick and black
headerFormat.setBorder(Border.ALL, BorderLineStyle.THICK,
Color.BLACK);
//The header content is displayed horizontally in the center
headerFormat.setAlignment(Alignment.CENTRE);
} catch (WriteException e) {
System.out.println("Header cell style setting failed!");
}
return headerFormat;
}
public WritableCellFormat getBodyCellStyle() {
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
WritableCellFormat bodyFormat = new WritableCellFormat(font);
try {
//Set the cell background color: the table body is white
bodyFormat.setBackground(Colour.WHITE);
//Set header table border style
//The entire table line is thin and black
bodyFormat
.setBorder(Border.ALL, BorderLineStyle.THIN, Color.BLACK);
} catch (WriteException e) {
System.out.println("Table body cell style setting failed!");
}
return bodyFormat;
}
public static void main(String[] args) {
String header = "NO, name, gender, age";
String[] body = new String[4];
body[0] = "1,Ouyang Feng, male, 68";
body[1] = "2,Huang Yaoshi,Male,67";
body[2] = "3,Hong Qigong,Male,70";
body[3] = "4,Guo Jing,Male,32";
String filePath = "e:/test.xls";
JxlTable testJxl = JxlTable.getInstance();
boolean flag = testJxl.createTable(header, body, filePath);
if (flag) {
System.out.println("Table created successfully!!");
}
}
}
Step by step explanation:
Read excel file
To read an excel file, you should first create a wordbook:
Copy the code code as follows:
Workbook wb=Workbook.getWorkbook(File file);
Workbook wb=Workbook.getWorkbook(InputStream is);
Then you can get its worksheet :
Copy the code code as follows:
Sheet[] sheets=wb.getSheets(); //Get all worksheets
Sheet sheet=wb.getSheet(0); //Indicates getting the first worksheet.
Finally, you can get the values of certain cells in a worksheet:
Copy the code code as follows:
Cell cell=sheet.getCell(0,0); //Get the data of the first column and the first row. The first parameter is the column
String value=cell.getContents(); //Get the value of the cell in string form
String type=cell.getType(); //Get the data type of the cell.
Close the workbook workflow :
When you finish processing the Excel spreadsheet data, be sure to use the close() method to close the previously created object to release the memory space occupied in the process of reading the data table. This is especially important when reading a large amount of data. . Refer to the following code snippet:
Copy the code code as follows:
wb.close();
Note: You can only close the excel after completing the operation. After closing, calling Sheet s=wb.getSheet(0) will be null.
Introduction to commonly used methods :
Methods provided by Workbook class
1. int getNumberOfSheets()
Get the number of worksheets in the workbook, example:
Copy the code code as follows:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
int sheets = rwb.getNumberOfSheets();
2. Sheet[] getSheets()
Returns an array of Sheet objects in the Workbook, example:
Copy the code code as follows:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
Sheet[] sheets = rwb.getSheets();
Methods provided by the Sheet interface
1. String getName()
Get the name of the Sheet, example:
Copy the code code as follows:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
String sheetName = rs.getName();
2. int getColumns()
Get the total number of columns contained in the Sheet table, example:
Copy the code code as follows:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
int rsColumns = rs.getColumns();
3. Cell[] getColumn(int column)
Get all the cells in a certain column and return an array of cell objects. Example:
Copy the code code as follows:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
Cell[] cell = rs.getColumn(0);
4. int getRows()
Get the total number of rows contained in the Sheet table, example:
Copy the code code as follows:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
int rsRows = rs.getRows();
5. Cell[] getRow(int row)