在开发中,我们难免会遇到一些关于EXCEL、WORD的操作,这是我们可以使用阿帕奇的POI,当然你也可以使用阿里的EasyPOI,看个人爱好,下面我将介绍阿帕奇的POI
了解一下,两个版本,一个是03版本,后缀是.xls,最多65535行,耗时时间短,07版本,后缀是.xlsx,最多无限制行,耗时间较长
首先是进行写表操作,如果表不存在,会自动创建表。
03版本
/**
* @Auther: luo
* @Date: 2020/8/16 14:01
* @Description:
*/
public class Test01 {
public static void main(String[] args) throws IOException {
new Test01().test01();
}
public void test01() throws IOException {
//HSSF 创建03版本的工作簿
Workbook workbook = new HSSFWorkbook();
//创建工作表
Sheet sheet1 = workbook.createSheet("sheet1");
//创建第一行
Row row = sheet1.createRow(0);
//设置第一列
Cell cell = row.createCell(0, CellType.STRING);
cell.setCellValue("str");
//设置第二列
Cell cell1 = row.createCell(1, CellType.STRING);
cell1.setCellValue("str2");
//写出
workbook.write(new FileOutputStream("D:\\IDEA2018\\workplace\\POI\\"+"b.xls"));
}
}
输出试试
07版本
public void test02() throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("teacher");
Row row = sheet.createRow(0);
Cell rowCell = row.createCell(0);
rowCell.setCellValue("name");
Cell rowCell2 = row.createCell(1);
rowCell2.setCellValue("age");
workbook.write(new FileOutputStream("D:\\IDEA2018\\workplace\\POI\\"+"a.xlsx"));
}
读表操作
public void test04()throws IOException{
String path="D:\\IDEA2018\\workplace\\POI\\";
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path + "a.xls");
//读取文件
Workbook workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
//得到有效的单元格数
int cells = row.getPhysicalNumberOfCells();
//输出第一行的标题
for (int i = 0; i < cells; i++){
System.out.print(row.getCell(i)+" ");
}
System.out.println();
//得到有效的行数
int rowCount = sheet.getPhysicalNumberOfRows();
//输出数据
for (int rowNum = 1; rowNum < rowCount; rowNum++){
Row rowData = sheet.getRow(rowNum);
if (rowData != null){
//读取单元格
int cellCount = rowData.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++){
Cell cell = rowData.getCell(cellNum);
if(cell != null){
//匹配单元格的内容数据类型
CellType cellType = cell.getCellType();
String value="";
switch (cellType){
case _NONE:
break;
case BLANK:
break;
case ERROR:
break;
case STRING://字符串类型
value = cell.getStringCellValue();
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: //公式Formula
//拿到计算公式
String cellFormula = cell.getCellFormula();
//System.out.println(cellFormula);
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
CellValue evaluate = formulaEvaluator.evaluate(cell);
value = evaluate.formatAsString();
break;
case NUMERIC://数字Numeric(日期,数字)
if(HSSFDateUtil.isCellDateFormatted(cell)){//是日期
Date date = cell.getDateCellValue();
value=new DateTime(date).toString("yyyy-MM-dd");
}else {
NumberFormat nf = NumberFormat.getInstance();
value = String.valueOf(nf.format(cell.getNumericCellValue())).replace(",", "");
// cell.setCellType(CellType.STRING);
// value=cell.toString();
}
break;
default:
break;
}
System.out.print(value+" ");
}
}
System.out.println();
}
}
fileInputStream.close();
}
"C:\Program Files\Java\jdk1.8.0_144\bin\java.exe" -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:D:\IntelliJ IDEA\IntelliJ IDEA 2018.2.4\lib\idea_rt.jar=56295:D:\IntelliJ IDEA\IntelliJ IDEA 2018.2.4\bin" -Dfile.encoding=UTF-8 -classpath "D:\IntelliJ IDEA\IntelliJ IDEA 2018.2.4\lib\idea_rt.jar;D:\IntelliJ bin\rep\org\apache\commons\commons-compress\1.18\commons-compress-1.18.jar;D:\apache-maven-3.5.4-bin\rep\com\github\virtuald\curvesapi\1.06\curvesapi-1.06.jar;D:\apache-maven-3.5.4-bin\rep\org\apache\poi\ooxml-schemas\1.4\ooxml-schemas-1.4.jar;D:\apache-maven-3.5.4-bin\rep\org\apache\xmlbeans\xmlbeans\3.0.1\xmlbeans-3.0.1.jar;D:\apache-maven-3.5.4-bin\rep\joda-time\joda-time\2.10.5\joda-time-2.10.5.jar" T
name age
罗罗 23
金金 22
Process finished with exit code 0