POI
1.基本功能
- HSSF 操作Excel 03,最大支持65536行,xls。
- XSSF 操作Excel OOXML 07版,支持无线行数,xlsx。
- HWPF 操作Word。
- HSLF 操作PowerPoint。
- HDGF 操作Visio。
2.POI和EasyExcel
- POI会存在内查溢出的问题,而easyExcel可以有效的避免这个问题。
- POI操作Excel是将初级加载到内存,所以会有内存溢出的问题,而easyExcel是一行一行的操作数据。
- EasyExcel Github源码地址,https://github.com/alibaba/easyexcel。
- EasyExcel文档地址,https://www.yuque.com/easyexcel/doc/easyexcel。
3.HSSFWorkbook,03,Write
- HSSFWorkbook只能下65536行数据,但是操作时间比XSSFWorkbook更快。
/**
* 创建一个 03版本的 excel,并写数据。
* 后缀 xls。
* 03使用 HSSFWorkbook
* 07使用 XSSFWorkbook
* 07可以有无线行,SXSSFWorkbook是XSSFWorkbook的升级版。
* 当写入的行数超过65536时,出现异常
* Exception in thread "main" java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
* @param path
*/
public static void writeExcel03(String path) throws Exception {
// 1 创建一个工作簿
Workbook workbook = new HSSFWorkbook();
// 2 创建一个工作表
Sheet sheet = workbook.createSheet();
// 3 创建一行
Row row1 = sheet.createRow(0);
// 4 创建一列,并写入值
Cell cell1 = row1.createCell(0);
cell1.setCellValue("用户名");
Cell cell2 = row1.createCell(1);
cell2.setCellValue("密码");
Row row2 = sheet.createRow(1);
Cell c1 = row2.createCell(0);
c1.setCellValue("tom");
Cell c2 = row2.createCell(1);
c2.setCellValue("123");
FileOutputStream fileOutputStream = new FileOutputStream(new File(path + "03.xls"));
// Excel输出
workbook.write(fileOutputStream);
fileOutputStream.close();
}
4.XSSFWorkbook,07,Write
- XSSFWorkbook可以写入无限行的数据,但是操作数据比HSSFWorkbook需要的时间更长。
/**
* 创建一个 07版本的 excel,并写数据。
* 后缀 xlsx。
* 03使用 HSSFWorkbook
* 07使用 XSSFWorkbook
* 07可以有无限行,SXSSFWorkbook是XSSFWorkbook的升级版。
* @param path
*/
public static void writeExcel07(String path) throws Exception {
// 1 创建一个工作簿
Workbook workbook = new XSSFWorkbook();
// 2 创建一个工作表
Sheet sheet = workbook.createSheet();
// 3 创建一行
Row row1 = sheet.createRow(0);
// 4 创建一列,并写入值
Cell cell1 = row1.createCell(0);
cell1.setCellValue("用户名");
Cell cell2 = row1.createCell(1);
cell2.setCellValue("密码");
Row row2 = sheet.createRow(1);
Cell c1 = row2.createCell(0);
c1.setCellValue("tom");
Cell c2 = row2.createCell(1);
c2.setCellValue("123");
FileOutputStream fileOutputStream = new FileOutputStream(new File(path + "07.xlsx"));
// Excel输出
workbook.write(fileOutputStream);
fileOutputStream.close();
}
5.SXSSFWorkbook,XSSFWorkbook的升级
/**
* 使用XSSFWorkbook的升级 SXSSFWorkbook来更快的写入数据
* 需要3秒
* @param path
* @throws Exception
*/
public static void writeExcel07Test02(String path) throws Exception {
long start = System.currentTimeMillis();
// 1 创建一个工作簿
SXSSFWorkbook workbook = new SXSSFWorkbook();
// 2 创建一个工作表
Sheet sheet = workbook.createSheet();
for (int i = 0;i < 65536;i++) {
Row row = sheet.createRow(i);
for (int j = 0;j < 10;j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j);
}
}
FileOutputStream fileOutputStream = new FileOutputStream(new File(path + "07.xlsx"));
// Excel输出
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((end - start) / 1000);
}
6.从Excel读取数据
/**
* 从Excel中读取数据
*/
public static void readExcel03(String p) throws Exception {
FileInputStream inputStream = new FileInputStream(p + "03.xls");
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
HSSFSheet sheet = workbook.getSheetAt(0);
String value;
int rowCount = sheet.getPhysicalNumberOfRows();
for (int i = 0;i < rowCount;i++) {
HSSFRow row = sheet.getRow(i);
int cellCount = row.getPhysicalNumberOfCells();
for (int j = 0;j < cellCount;j++) {
HSSFCell cell = row.getCell(j);
CellType cellType = cell.getCellType();
switch (cellType) {
case STRING:
value = cell.getStringCellValue();
System.out.println("字符串 - " + value);
break;
case NUMERIC:
// 日期类型
if (DateUtil.isCellDateFormatted(cell)) {
value = new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd");
System.out.println("日期 - " + value);
}else {
double d = cell.getNumericCellValue();
System.out.println("数字类型 - " + d);
}
break;
case BLANK:
System.out.println("空 - " + cell.getStringCellValue());
break;
case BOOLEAN:
System.out.println("布尔 - " + cell.getBooleanCellValue());
break;
case ERROR:
System.out.println("错误 - " + cell.getErrorCellValue());
break;
case _NONE:
System.out.println("none - " + cell.getStringCellValue());
case FORMULA:
System.out.println("formula");
default:
System.out.println("不知道的类型");
}
}
}
inputStream.close();
}
7.当Excel单元格式公式的时候,进行相关计算
/**
* 计算Excel中的公式
* @param p
* @throws Exception
*/
public static void readExcel07(String p) throws Exception {
FileInputStream inputStream = new FileInputStream(p + "07.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(3);
XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);
switch (cell.getCellType()) {
case FORMULA:
String value = cell.getCellFormula();
System.out.println(value);
CellValue cellValue = evaluator.evaluate(cell);
System.out.println("公式计算后的值 = " + cellValue.formatAsString());
break;
}
}
8.EasyExcel简单使用
- EasyExcel读写本地数据。
@Slf4j
public class EasyExcelUtils {
/**
* 读取数据
*/
public static <T> List<T> readData(InputStream is, Class<T> type) {
return EasyExcelFactory.read(is).head(type).sheet().doReadSync();
}
/**
* 读取数据,并且将每行的数据输出。
*/
public static <T> List<T> readDataAndOut(InputStream is, Class<T> type) {
ReadListener<User> readListener = new ReadListener<>() {
@Override
public void invoke(User data, AnalysisContext context) {
log.info("data is {}", data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("read end");
}
};
return EasyExcelFactory.read(is, readListener).head(type).sheet().doReadSync();
}
public static <T> void writeData(OutputStream os, List<T> data, Class<T> type) {
EasyExcelFactory.write(os).head(type).sheet().doWrite(data);
}
}
/**
* EasyExcel本地读写数据
*/
public class EasyExcelTest {
public static void main(String[] args) throws FileNotFoundException {
test03();
}
public static void test03() throws FileNotFoundException {
FileOutputStream os = new FileOutputStream("F:\\数据\\2.xls");
List<User> users = new ArrayList<>();
users.add(new User("tom", "1234", 1, "01", new Date()));
users.add(new User("alice", "1234", 2, "01", new Date()));
users.add(new User("bob", "1234", 3, "01", new Date()));
EasyExcelUtils.writeData(os, users, User.class);
}
public static void test02() throws FileNotFoundException {
FileInputStream inputStream = new FileInputStream("F:\\数据\\2.xls");
List<User> users = EasyExcelUtils.readDataAndOut(inputStream, User.class);
System.out.println(users);
}
public static void test01() throws FileNotFoundException {
FileInputStream inputStream = new FileInputStream("F:\\数据\\1.xls");
List<User> users = EasyExcelUtils.readData(inputStream, User.class);
System.out.println(users);
}
}
- Web中读写数据。
@Slf4j
@RestController
public class UserController {
@PostMapping("/read/data")
public List<User> readData(@RequestPart("file") MultipartFile file) throws IOException {
List<User> users = EasyExcelUtils.readData(file.getInputStream(), User.class);
log.info("data is {}", users);
return users;
}
@GetMapping("/download/data")
public void downloadData(HttpServletResponse response) throws IOException {
List<User> users = new ArrayList<>();
users.add(new User("tom", "1234", 1, "01", new Date()));
users.add(new User("alice", "1234", 2, "01", new Date()));
users.add(new User("bob", "1234", 3, "01", new Date()));
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = UUID.fastUUID().toString() + ExcelTypeEnum.XLS.getValue();
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
EasyExcelUtils.writeData(response.getOutputStream(), users, User.class);
}
}