分享一个JAVA操作Excel的工具类,非常方便,先上代码
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class ExcelTool {
private final static String EXCEL2003 = "xls";
private final static String EXCEL2007 = "xlsx";
public static boolean writeExcel(List<List<String>> datas, List<String> header, String folder, String fileName) {
if (datas == null || datas.isEmpty() || header == null || header.isEmpty() || StringUtils.isEmpty(folder)
|| StringUtils.isEmpty(fileName)) {
return false;
}
// 判断路径是否正确
File file = new File(folder);
if (!file.isDirectory()) {
return false;
}
// 设置文件后缀
if (!fileName.endsWith(".xlsx")) {
fileName = fileName + ".xlsx";
}
// 第一步,创建一个workbook对应一个excel文件
Workbook workbook = new XSSFWorkbook();
// 第二部,在workbook中创建一个sheet对应excel中的sheet
Sheet sheet = workbook.createSheet("sheet1");
// 第三部,在sheet表中添加表头第0行,老版本的poi对sheet的行列有限制
Row row = sheet.createRow(0);
// 第四步,创建单元格,设置表头
for (int i = 0; i < header.size(); i++) {
row.createCell(i).setCellValue(header.get(i));
}
// 第五步,写入实体数据,实际应用中这些数据从数据库得到,对象封装数据,集合包对象。对象的属性值对应表的每行的值
for (int i = 0; i < datas.size(); i++) {
Row row1 = sheet.createRow(i + 1);
List<String> data = datas.get(i);
// 创建单元格设值
for (int j = 0; j < data.size(); j++) {
row1.createCell(j).setCellValue(data.get(j));
}
}
// 将文件保存到指定的位置
try {
if (!file.exists()) {
file.mkdirs();
}
FileOutputStream fos = new FileOutputStream(folder + "\\" + fileName);
workbook.write(fos);
fos.close();
return true;
} catch (IOException e) {
e.printStackTrace();
}
return false;
}
public static List<List<String>> read(String path) throws IOException {
List<List<String>> list = new ArrayList<>();
//1.读取Excel文档对象
Workbook workbook = null;
if (path.endsWith(EXCEL2007)) {
workbook = new XSSFWorkbook(new FileInputStream(path));
}
if (path.endsWith(EXCEL2003)) {
workbook = new HSSFWorkbook(new FileInputStream(path));
}
//2.获取要解析的表格(第一个表格)
Sheet sheet = workbook.getSheetAt(0);
//获得最后一行的行号
int lastRowNum = sheet.getLastRowNum();
//遍历每一行
for (int i = 0; i <= lastRowNum; i++) {
List<String> list1 = new ArrayList<>();
//3.获得要解析的行
Row row = sheet.getRow(i);
//4.获得每个单元格中的内容(String)
short lastCellNum = row.getLastCellNum();
for (short j = 0; j < lastCellNum; j++) {
row.getCell(j).setCellType(CellType.STRING);
String stringCellValue0 = row.getCell(j).getStringCellValue();
list1.add(stringCellValue0);
}
list.add(list1);
}
return list;
}
}
用到的jar包是apache的poi、poi-ooxml和lang3.StringUtils,自行下载或者在POM中导入,分別是写出和读取两个方法。
附上写出使用案例:
public static void main(String[] args) {
List<List<String>> excel=new ArrayList<>();
List<String> li1 = Arrays.asList("1", "2", "3");
List<String> li2 = Arrays.asList("4", "5", "6");
List<String> li3 = Arrays.asList("7", "8", "9");
excel.add(li1);
excel.add(li2);
excel.add(li3);
List<String> header = Arrays.asList("表头1", "表头2", "表头3");
boolean b = ExcelTool.writeExcel(excel, header, "D:\\", "表格");
}
返回true表示成功,表格内容如下:
表头1 | 表头2 | 表头3 |
1 | 2 | 3 |
4 | 5 | 6 |
7 | 8 | 9 |
读取的方法很简单,直接返回List<List<String>>集合。