POI 解析excel
【03与07版本excel区别:】
一、后缀不同:前者.xls,后者.xslx
二、前者行数有最大行数限制(最大65536行),后者无行数限制;
一、POI解析excel(读):
1.1 简单方式:
①加载文件路径、获取流;(new FileInputStream(filePath),再把流放入到工作簿里,此时inputStream对象就读取到工作簿里的内容了)
②创建工作簿;(new HSSFWorkbook(inputStream)、new XSSFWorkbook(inputStream):03版是HSSFWorkbook、07版是XSSFWorkbook)
③得到表;(workbook.getSheetAt(num))
④得到行;(sheet.getRow(num))
⑤得到列;(row.getCell(num))
@Test
public void testRead07() throws IOException {
FileInputStream inputStream = new FileInputStream(PATH + "拉拉观众统计表071.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Row row1 = sheet.getRow(1);
Cell cell = row.getCell(0);
System.out.println(row1.getCell(1).getStringCellValue());
inputStream.close();
}
1.2 循环行列读取:
①加载文件路径、获取流;(new FileInputStream(filePath),再把流放入到工作簿里,此时inputStream对象就读取到工作簿里的内容了)
②创建工作簿;(new HSSFWorkbook(inputStream)、new XSSFWorkbook(inputStream):03版是HSSFWorkbook、07版是XSSFWorkbook)
③得到表;(workbook.getSheetAt(num))
④得到总行数进行循环;(sheet.getPhysicalNumberOfRows()获取总行数,进行总行数的for循环)
⑤循环每一行获取列;(sheet.getRow(0).getPhysicalNumberOfCells()获取第一行总列数,进行每一行(即单元格)的for循环)
@Test
public void testCellType() throws IOException {
FileInputStream inputStream = new FileInputStream(PATH + "明细表.xls");
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null) {
//读取列
int cellCount = sheet.getRow(0).getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");//打印出:第几行-第几列
Cell cell = rowData.getCell(cellNum);
//匹配列的数据类型
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING: //字符串
System.out.print("【String】");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: //布尔
System.out.print("【BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK: //空
System.out.print("【BLANK】");
break;
case HSSFCell.CELL_TYPE_NUMERIC: //数字:分为日期/普通数字
System.out.print("【NUMERIC】");
if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
System.out.print("【日期】");
//日期转化
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
//如果不是日期格式,防止数字过长(使用科学计数法显示直接转化成字符串输出)
System.out.print("【转换成字符串输出】");//普通数字:转换成字符串输出
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR: //错误
System.out.print("【数据类型错误】");
break;
}
System.out.println(cellValue);
}
}
}
}
inputStream.close();
}
二、POI解析excel(写):
2.1 简单方式:
①创建一个工作簿;(new HSSFWorkbook()、new XSSFWorkbook())
②创建一个工作表;(workbook.createSheet(传入要创建的某一个sheet名))
③创建一个行;(sheet.createRow(num),num=0创建第一行)
④创建一个单元格,赋值;(row1.createCell(num),cell12.setCellValue(222))
⑤加载文件路径创建输出流;(new FileOutputStream(文件路径,包括要创建的excel文件的自定义文件名称))
⑥通过工作簿将输出流写出;( workbook.write(fos))
⑦关闭流;(fos.close())
@Test
public void testWrite03() throws IOException {
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("拉拉观众统计表");//sheet名
//创建第一行:为第1行第1列、第1行第2列单元格赋值
Row row1 = sheet.createRow(0);
Cell cell11 = row1.createCell(0);
cell11.setCellValue("今日的新增观众");//往单元格中填入数据
Cell cell12 = row1.createCell(1);
cell12.setCellValue(222);
//创建第二行:为第1行第1列、第1行第2列单元格赋值
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");//使用它的格式化工具类创建时间,比java官方的Util.Date好用
cell22.setCellValue(time);
FileOutputStream fos = new FileOutputStream(PATH + "拉拉观众统计表03.xls");
workbook.write(fos);
fos.close();
System.out.println("拉拉观众统计表03 生成完毕");
}
2.2 循环行列读取:
①创建一个工作簿;(new HSSFWorkbook()、new XSSFWorkbook())
②创建一个工作表;(workbook.createSheet(传入要创建的某一个sheet名))
(定义一个最大行数值:进行循环行、循环列的赋值;)
③循环创建行;(sheet.createRow(rowNum),rowNum从0开始,小于自定义最大行数值)
④循环每一行,创建每个单元格,赋值;(row1.createCell(cellNum),cell12.setCellValue(cellNum))
⑤加载文件路径创建输出流;(new FileOutputStream(文件路径,包括要创建的excel文件的自定义文件名称))
⑥工作簿将输出流写出;( workbook.write(fos))
⑦关闭流;(fos.close())
@Test
public void testWrite03BigData() throws IOException {
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 65536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");
workbook.write(outputStream);
outputStream.close();
}