package com.mzl.flower.utils;
|
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.util.LocaleUtil;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import java.io.*;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
|
/**
|
* excel读写工具类 */
|
public class POIUtil {
|
private final static String xls = "xls";
|
private final static String xlsx = "xlsx";
|
|
public static SheetContent readExcel(MultipartFile file, int sheetNum) throws IOException{
|
//检查文件
|
checkFile(file);
|
//获得Workbook工作薄对象
|
Workbook workbook = getWorkBook(file);
|
|
return readExcel(workbook, sheetNum);
|
}
|
|
public static SheetContent readExcel(File file, int sheetNum) throws IOException{
|
Workbook workbook = getWorkBook(file);
|
|
return readExcel(workbook, sheetNum);
|
}
|
|
public static SheetContent readExcel(InputStream is, String fileName, int sheetNum) throws IOException{
|
Workbook workbook = getWorkBook(is, fileName);
|
|
return readExcel(workbook, sheetNum);
|
}
|
|
public static SheetContent readExcel(Workbook workbook, int sheetNum) throws IOException{
|
SheetContent sheetContent = null;
|
|
//获得当前sheet工作表
|
Sheet sheet = workbook.getSheetAt(sheetNum);
|
if(sheet == null){
|
return sheetContent;
|
}
|
String sheetName = sheet.getSheetName();
|
List<String[]> list = new ArrayList<>();
|
//获得当前sheet的开始行
|
int firstRowNum = sheet.getFirstRowNum();
|
//获得当前sheet的结束行
|
int lastRowNum = sheet.getLastRowNum();
|
//循环除了第一行的所有行
|
for(int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++){
|
//获得当前行
|
Row row = sheet.getRow(rowNum);
|
if(row == null){
|
continue;
|
}
|
//获得当前行的开始列
|
int firstCellNum = row.getFirstCellNum();
|
if(firstCellNum == -1){
|
list.add(new String[0]);
|
continue;
|
}
|
//获得当前行的列数
|
int lastCellNum = row.getPhysicalNumberOfCells();
|
String[] cells = new String[row.getPhysicalNumberOfCells()];
|
//循环当前行
|
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
|
Cell cell = row.getCell(cellNum);
|
cells[cellNum] = getCellValue(cell);
|
}
|
list.add(cells);
|
}
|
|
sheetContent = new SheetContent(sheetName, list);
|
|
return sheetContent;
|
}
|
|
public static List<SheetContent> readExcel(Workbook workbook) throws IOException{
|
//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
|
List<SheetContent> list = new ArrayList<>();
|
if(workbook != null){
|
for(int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++){
|
list.add(readExcel(workbook, sheetNum));
|
}
|
}
|
return list;
|
}
|
|
public static void checkFile(MultipartFile file) throws IOException{
|
//判断文件是否存在
|
if(null == file){
|
throw new FileNotFoundException("文件不存在！");
|
}
|
//获得文件名
|
String fileName = file.getOriginalFilename();
|
//判断文件是否是excel文件
|
if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
|
throw new IOException(fileName + "不是excel文件");
|
}
|
}
|
|
public static Workbook getWorkBook(MultipartFile file) {
|
//获得文件名
|
String fileName = file.getOriginalFilename();
|
//创建Workbook工作薄对象，表示整个excel
|
Workbook workbook = null;
|
try {
|
workbook = getWorkBook(file.getInputStream(), fileName);
|
} catch (IOException e) {
|
}
|
return workbook;
|
}
|
|
public static Workbook getXSSFWorkbook(InputStream is) throws IOException {
|
return new XSSFWorkbook(is);
|
}
|
|
public static Workbook getHSSFWorkbook(InputStream is) throws IOException {
|
return new HSSFWorkbook(is);
|
}
|
|
public static Workbook getWorkBook(File file) {
|
String fileName = file.getName();
|
//创建Workbook工作薄对象，表示整个excel
|
Workbook workbook = null;
|
try {
|
workbook = getWorkBook(new FileInputStream(file), fileName);
|
} catch (IOException e) {
|
}
|
|
return workbook;
|
}
|
|
public static Workbook getWorkBook(InputStream is, String fileName) {
|
Workbook workbook = null;
|
try {
|
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
|
if(fileName.endsWith(xls)){
|
//2003
|
workbook = getHSSFWorkbook(is);
|
}else if(fileName.endsWith(xlsx)){
|
//2007
|
workbook = getXSSFWorkbook(is);
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
return workbook;
|
}
|
|
public static String getCellValue(Cell cell){
|
String cellValue = "";
|
if(cell == null){
|
return cellValue;
|
}
|
int tt = cell.getCellStyle().getDataFormat();
|
//判断数据的类型
|
switch (cell.getCellType()){
|
case NUMERIC: //数字
|
short format = cell.getCellStyle().getDataFormat();
|
SimpleDateFormat sdf = null;
|
if (format == 57) { // 日期
|
sdf = new SimpleDateFormat("yyyy年MM月");
|
}
|
if(sdf != null){
|
try {
|
double value = cell.getNumericCellValue();
|
Date date = DateUtil.getJavaDate(value);
|
cellValue = sdf.format(date);
|
} catch (Exception e) {
|
}
|
}else{
|
cell.setCellType(CellType.STRING);
|
cellValue = String.valueOf(cell.getStringCellValue());
|
}
|
break;
|
case STRING: //字符串
|
cellValue = String.valueOf(cell.getStringCellValue());
|
break;
|
case BOOLEAN: //Boolean
|
cellValue = String.valueOf(cell.getBooleanCellValue());
|
break;
|
case FORMULA: //公式
|
//cellValue = String.valueOf(cell.getCellFormula());
|
try {
|
cellValue = String.valueOf(cell.getNumericCellValue());
|
} catch (IllegalStateException e) {
|
}
|
if(cellValue.length() == 0){
|
try {
|
cellValue = String.valueOf(cell.getStringCellValue());
|
} catch (IllegalStateException e) {
|
}
|
}
|
break;
|
case BLANK: //空值
|
cellValue = "";
|
break;
|
case ERROR: //故障
|
cellValue = "非法字符";
|
break;
|
default:
|
cellValue = "未知类型";
|
break;
|
}
|
return cellValue;
|
}
|
|
public static class SheetContent{
|
private String sheetName;
|
private List<String[]> data;
|
public SheetContent(String sheetName, List<String[]> data){
|
this.sheetName = sheetName;
|
this.data = data;
|
}
|
|
public String getSheetName(){
|
return sheetName;
|
}
|
|
public List<String[]> getData(){
|
return data;
|
}
|
}
|
}
|
