package com.xxx.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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 org.springframework.web.multipart.MultipartFile;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
* Created with IntelliJ IDEA.
*
* @Auther: zck
* @Date: 2020/08/22/12:55
* @Description: 解析excel文件工具类
*/
public class PoiUtils {
private static final String xls = "xls";
private static final String xlsx = "xlsx";
private static final String DATE_FORMAT = "yyyy/MM/dd";
/**
* 读取excel文件 解析后返回list
*
* @param file
* @return
*/
public static List<String[]> readExcel(MultipartFile file) throws IOException {
// 1.校验上传的文件是否是excel格式的文件
checkFile(file);
// 2.获得工作簿
Workbook workbook = getWorkBook(file);
// 3.创建返回值对象 把excel表格中的每一行的值作为一个数组,所有行作为一个集合返回
List<String[]> returnList = new ArrayList<>();
if (workbook != null) {
//4.获得工作薄(Workbook)中工作表(Sheet)的个数
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
// 5.获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if (sheet == null) {
// 如果当前行的值为null 继续下次循环
continue;
}
// 6.获得当前工作表的开始行
int firstRowNum = sheet.getFirstRowNum();
// 7.获取当前工作表的结束航
int lastRowNum = sheet.getLastRowNum();
// 循环除了第一行的所有行
for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
// 8.获取当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 9.获取当前行的开始列
short firstCellNum = row.getFirstCellNum();
// 10.获取当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
// 11.循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
returnList.add(cells);
}
}
workbook.close();
}
return returnList;
}
/**
* 获取列的值
*
* @param cell
* @return
*/
private static String getCellValue(Cell cell) {
// 1.定义变量接收最后的值
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 2.如果当前单元格内的内容为日期格式的数据 需要特殊处理
String dataFormatString = cell.getCellStyle().getDataFormatString();
if (dataFormatString.equals("m/d/yy")) {
cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
return cellValue;
}
// 3.数字需要当成字符串来读 否则1会被当成1.0来读
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
// 4。switch判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
/**
* 获取工作簿
*
* @param file
* @return
*/
private static Workbook getWorkBook(MultipartFile file) {
// 1.获取文件名
String filename = file.getOriginalFilename();
// 2.创建WorkBook 工作簿对象 代表整个excel文件对象
Workbook workbook = null;
try {
// 3.创建excel的文件流
InputStream inputStream = file.getInputStream();
// 4.根据不同的问价后缀创建不同的WorkBook工作簿对象 xls/xlsx
if (filename.endsWith(xls)) {
workbook = new HSSFWorkbook(inputStream);
} else if (filename.endsWith(xlsx)) {
workbook = new XSSFWorkbook(inputStream);
}
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
/**
* 检查文件是否合法
*
* @param file
*/
private static void checkFile(MultipartFile file) throws IOException {
// 1.判断文件是否存在
if (null == file) {
throw new FileNotFoundException("文件不存在!");
}
// 2.获得文件名
String filename = file.getOriginalFilename();
// 3.判断文件是否是excel文件
if (!filename.endsWith(xls) && !filename.endsWith(xlsx)) {
throw new IOException(filename + "不是excel文件!");
}
}
}
谢谢