先来说说什么是POI
POI
写操作
操作03版的 **.xls (最多有65536行)
HSSFWorkbook
@Test//65536
public void testWrite03() throws IOException {
//创建一个工作簿
Workbook workbook = new HSSFWorkbook();
//创建一个工作表
Sheet sheet = workbook.createSheet("03工作统计表");
//创建一行
Row row0 = sheet.createRow(0);
//创建一个单元格
Cell cell00 = row0.createCell(0);
cell00.setCellValue("今天的观众");
Cell cell01 = row0.createCell(1);
cell01.setCellValue("123233");
Row row1 = sheet.createRow(1);
Cell cell10 = row1.createCell(0);
cell10.setCellValue("时间");
Cell cell11 = row1.createCell(1);
String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell11.setCellValue(s);
//生成一张表
FileOutputStream fileOutputStream = new FileOutputStream(Path + "/03工作统计表.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("over");
}
- 效果图
操作07版的 **.xlsx
XSSFWorkbook
@Test
public void testWrite07() throws IOException {
//创建一个工作簿
Workbook workbook = new XSSFWorkbook();
//创建一个工作表
Sheet sheet = workbook.createSheet("07工作统计表");
//创建一行
Row row0 = sheet.createRow(0);
//创建一个单元格
Cell cell00 = row0.createCell(0);
cell00.setCellValue("今天的观众");
Cell cell01 = row0.createCell(1);
cell01.setCellValue(13333);
Row row1 = sheet.createRow(1);
Cell cell10 = row1.createCell(0);
cell10.setCellValue("时间");
Cell cell11 = row1.createCell(1);
String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell11.setCellValue(s);
//生成一张表
FileOutputStream fileOutputStream = new FileOutputStream(Path + "/07工作统计表.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("over");
}
大数据写入
SXSSFWorkbook
@Test
public void bigWrite07S() throws IOException {
Random random = new Random();
long start = DateTime.now().getMillis();
//创建工作簿
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("03big");
for (int rowNum = 0; rowNum < 100000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 20; cellNum++) {
int i = random.nextInt(100);
Cell cell = row.createCell(cellNum);
cell.setCellValue(i);
}
}
FileOutputStream fileOutputStream = new FileOutputStream(Path + "/07Sbig工作簿.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
((SXSSFWorkbook)workbook).dispose();
long end = DateTime.now().getMillis();
System.out.println("over");
System.out.println((double)(end-start)/1000);
}
读操作
读取数据类型多种
@Test //类型
public void testCellType() throws IOException {
//获取文件流
FileInputStream fileInputStream = new FileInputStream("E:\\JavaProgram\\IdeaJava\\ks_order\\POI_EasyExcel\\07不同数据类型的读取.xlsx");
//创建一个工作簿
Workbook workbook = new XSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
Row rowTitle = sheet.getRow(0);
if (rowTitle!=null){
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int rowNum = 0; rowNum < cellCount; rowNum++) {
Cell cell = rowTitle.getCell(rowNum);
if (cell!=null){
int cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.print(cellValue+" | ");
}
}
}
//读取表的内容
int rows = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rows; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row!=null){
int cells = row.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cells; cellNum++) {
System.out.print("("+rowNum+","+cellNum+")-->");
Cell cell = row.getCell(cellNum);
String cellValue = "";
//匹配该cell的类型
if (cell!=null){
int cellType = cell.getCellType();
switch (cellType){
case Cell.CELL_TYPE_STRING:
System.out.print("[string]:");
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print("[BOOLEAN]:");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
System.out.print("[BLANK]:");
break;
case Cell.CELL_TYPE_NUMERIC: //日期,数字
System.out.print("[NUMERIC]:");
if (HSSFDateUtil.isCellDateFormatted(cell)){
System.out.print("[Date]:");
Date dateCellValue = cell.getDateCellValue();
String s = new DateTime(dateCellValue).toString("yyyy/MM/dd HH:mm:ss");
cellValue = s;
}else{
System.out.print("[Number]:");
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = cell.getStringCellValue();
}
break;
case Cell.CELL_TYPE_ERROR:
System.out.print("[ERROR]:");
break;
}
System.out.println(cellValue);
}
}
}
}
fileInputStream.close();
}
效果
计算(公式)
==XSSFFormulaEvaluator ==
@Test //计算
public void testEven() throws IOException {
//获取文件流
FileInputStream fileInputStream = new FileInputStream("E:\\JavaProgram\\IdeaJava\\ks_order\\POI_EasyExcel\\07.xlsx");
//创建一个工作簿
Workbook workbook = new XSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(4);
Cell cell = row.getCell(0);
//得到计算公式
XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook)workbook);
//输出单元格的内容
int cellType = cell.getCellType();
switch (cellType){
case Cell.CELL_TYPE_FORMULA:
String formula = cell.getCellFormula();
System.out.println(formula);
//计算
CellValue evaluate = formulaEvaluator.evaluate(cell);
String s = evaluate.formatAsString();
System.out.println(s);
break;
}
}
EasyExcel
写操作
https://www.yuque.com/easyexcel/doc/write