Web开发中不可避免都会涉及到Excel的操作,如批量导出数据、让前端用户按固定格式批量上传数据等,本文介绍三种Java常用的处理Excel的方法,涵盖格式为xls和xlsx的表格。
JXL
JExcelApi 是一个Java库,提供了对Microsoft Excel文件读、写、修改的操作。
在Java工程中导入jxl库,库的官方下载地址见:JXL库官方下载地址。点击具体的版本,可以看到各种导包的方式。
pom.xml
本文例程采用Maven构建,pom.xml如下:
<?xml version="1.0" encoding="UTF-8"?>
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0
com.victor
jxldemo
1.0
net.sourceforge.jexcelapi
jxl
2.6.12
创建Excel并写入数据
public class JXL{
public static void main(String[] args){
//表头
String[] titles = {"id", "姓名", "性别"};
//新建excel文件并存放在E盘
File file = new File("f:\\jxl_test.xls");
try {
file.createNewFile();
//创建工作簿
WritableWorkbook workbook = Workbook.createWorkbook(file);
//创建sheet,名为sheet1,索引为0
WritableSheet sheet = workbook.createSheet("sheet1", 0);
//写入表头
Label label = null;
for (int i = 0; i < titles.length; i++) {
label = new Label(i, 0, titles[i]); //列,行,内容
sheet.addCell(label);
}
//数据
for (int i = 1; i < 10; i++) {
label = new Label(0, i, "1000" + i);
sheet.addCell(label);
label = new Label(1, i, "路人" + i);
sheet.addCell(label);
label = new Label(2, i, i % 2 == 1 ? "男" : "女");
sheet.addCell(label);
}
workbook.write();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
读取Excel文件内容
try {
//创建工作簿
Workbook workbook = Workbook.getWorkbook(new File("f:\\jxl_test.xls"));
//获取工作表sheet
Sheet sheet = workbook.getSheet(0);
//获取数据
for (int i = 0; i < sheet.getRows(); i++) {
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
System.out.println(cell.getContents() + " ");
}
}
System.out.println();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
POI
POI是Apache提供的、用Java操作Microsoft Documents的类库。
jar包地址:Apache POI
pom.xml
本例程采用Maven构建项目,因为涉及到文件操作还用到了common-io包,完整的pom.xml如下:
<?xml version="1.0" encoding="UTF-8"?>
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0
com.victor
demo
1.0
org.apache.poi
poi
3.16
commons-io
commons-io
2.5
创建Excel文件并写入数据
public static void main(String[] args){
//创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个工作表
HSSFSheet sheet = workbook.createSheet();
//首行
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
//表头
String[] titles = {"id", "姓名", "性别"};
for (int i = 0; i < titles.length; i++) {
cell = row.createCell(i);
cell.setCellValue(titles[i]);
}
//数据
for (int i = 1; i < 10; i++) {
HSSFRow nextRow = sheet.createRow(i);
cell = nextRow.createCell(0);
cell.setCellValue("1000" + i);
cell = nextRow.createCell(1);
cell.setCellValue("路人" + i);
cell = nextRow.createCell(2);
cell.setCellValue(i % 2 == 1 ? "男" : "女");
}
//创建文件并存储数据
File file = new File("/Users/user/Desktop/test.xls");
try {
file.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
流程比较简单,代码里都有注释,就不赘述了,生成的文件如下:
读取Excel文件
public static void main(String[] args){
//引入文件
File file = new File("/Users/user/Desktop/test.xls");
try {
//创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
//获取工作表,可以指定工作表的名,或者按索引进行读取
// HSSFSheet sheet = workbook.getSheet("Sheet0");
HSSFSheet sheet = workbook.getSheetAt(0);
//循环读取数据
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
HSSFRow row = sheet.getRow(i);
for (int j = 0; j < row.getLastCellNum(); j++) {
HSSFCell cell = row.getCell(j);
String value = cell.getStringCellValue();
System.out.print(value + " ");
}
System.out.println();
}
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
XSSF
XSSF是POI的拓展,用于对高版本excel的支持,用POI生成.xlsx打开时会提示文件已经损坏,这时候就要使用XSSF了。
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0
com.victor
demo
1.0
commons-io
commons-io
2.5
org.apache.poi
poi-scratchpad
3.16
dom4j
dom4j
1.6.1
org.apache.poi
poi-excelant
3.16
org.apache.poi
poi-ooxml-schemas
3.16
org.apache.xmlbeans
xmlbeans
2.6.0
创建文件
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
public class XSSFCreateExcel{
public static void main(String[] args){
//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//创建一个工作表
Sheet sheet = workbook.createSheet();
//首行
Row row = sheet.createRow(0);
Cell cell = null;
//表头
String[] titles = {"id", "姓名", "性别"};
for (int i = 0; i < titles.length; i++) {
cell = row.createCell(i);
cell.setCellValue(titles[i]);
}
//数据
for (int i = 1; i < 10; i++) {
Row nextRow = sheet.createRow(i);
cell = nextRow.createCell(0);
cell.setCellValue("1000" + i);
cell = nextRow.createCell(1);
cell.setCellValue("路人" + i);
cell = nextRow.createCell(2);
cell.setCellValue(i % 2 == 1 ? "男" : "女");
}
//创建文件并存储数据
File file = new File("/Users/user/Desktop/highVersion.xlsx");
try {
file.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
横向比较
如果表格版本比较低,都是07之前的xls,那用jxl和poi都ok。但jxl不支持高版本的表格,且自2011年后就停止维护了,官网能下到最新的版本都是7年前的了。所以如果是要处理xlsx格式的文件,建议使用poi的拓展XSSF。