POIUtil Excel导入 POI 3.7-1
package com.hxzy.xazb.util;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
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.Date;
import java.util.List;
/**
-
Created by Sunshine on 2020/8/11.
*/
public class POIUtil {
// 扩展名
public final static String XLS = “xls”;
public final static String XLSX = “xlsx”;/**
-
- 读取excel文件
-
@param excelFile excel文件
-
@param startRow 读取数据的起始行, 行号从0开始
-
@return
-
@throws IOException
*/
public static List<List> readExcelFile(MultipartFile excelFile) throws Exception {
// 检查文件
String fileName = checkFile(excelFile);List<List> lists = null;
if(fileName.endsWith(XLSX)){
lists = readXlsx(excelFile.getInputStream());
}else if(fileName.endsWith(XLS)){
lists = readXls(excelFile.getInputStream());
}
return lists;
}
/**
- 读取xlsx文档列
- /
private static String getXSSFCellValue(XSSFCell cell)throws Exception
{
String cellvalue = “”;
if (cell!=null) {
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_BOOLEAN:
cellvalue = String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);//注意月份是MM
cellvalue = simpleDateFormat.format(date);
}else{
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
case XSSFCell.CELL_TYPE_STRING:
cellvalue = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
break;
case XSSFCell.CELL_TYPE_ERROR:
break;
case XSSFCell.CELL_TYPE_FORMULA:
break;
}
}
return cellvalue;
}
/* - 读取xls文档列
- */
private static String getHSSFCellValue(HSSFCell cell)throws Exception
{
String cellvalue = “”;
if(cell!=null){
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_BOOLEAN:
cellvalue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);//注意月份是MM
cellvalue = simpleDateFormat.format(date);
}else{
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING:
cellvalue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
case HSSFCell.CELL_TYPE_FORMULA:
break;
}
}
return cellvalue;
}
/**
-
xlsx文档
-
@return 错误提示类型 0-正常 1-存在不能为空的列 2-格式不对 3-空文件
-
*/
private static List<List> readXlsx(InputStream inputstream) throws Exception {
XSSFWorkbook hssfworkbook = new XSSFWorkbook(inputstream);
XSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
List<List> list = new ArrayList<>();
if (hssfsheet != null) {
int totalrows = hssfsheet.getPhysicalNumberOfRows();// --获取sheet总行数
if (totalrows > 1)// 除了标题外，必须有数据
{
for (int i = 1; i < totalrows; i++) {
List rowList = new ArrayList<>();
XSSFRow hssfrow = hssfsheet.getRow(i);
// 获取每一行的总列数
int cellNum = hssfrow.getPhysicalNumberOfCells();if (cellNum==0){ continue; } for(int j = 1; j < cellNum; j++){ XSSFCell cell = hssfrow.getCell(j); String xssfCellValue = getXSSFCellValue(cell); rowList.add(xssfCellValue); } list.add(rowList); } return list; } else{ return null; }}
return null;
}
/**
-
xls文档
-
@return 错误提示类型 0-正常 1-存在不能为空的列 2-格式不对 3-空文件
-
*/
private static List<List> readXls(InputStream inputstream) throws Exception {
HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
List<List> list = new ArrayList<>();
if (hssfsheet != null) {
int totalrows = hssfsheet.getPhysicalNumberOfRows();// --获取sheet总行数
if (totalrows > 1)// 除了标题外，必须有数据
{
for (int i = 1; i < totalrows; i++) {
List rowList = new ArrayList<>();
HSSFRow hssfrow = hssfsheet.getRow(i);
// 获取每一行的总列数
int cellNum = hssfrow.getPhysicalNumberOfCells();if (cellNum==0){ continue; } for(int j = 1; j < cellNum; j++){ HSSFCell cell = hssfrow.getCell(j); String xssfCellValue = getHSSFCellValue(cell); rowList.add(xssfCellValue); } list.add(rowList); } return list; }else{ return null; }}
return null;
}
/**
- 获得工作簿对象
- @param excelFile excel文件
- @return 工作簿对象
*/
public static Workbook getWorkBook(MultipartFile excelFile) {
// 获得文件名
String fileName = excelFile.getOriginalFilename();
// 创建Workbook工作簿对象，表示整个excel
Workbook workbook = null;
try {
// 获得excel文件的io流
InputStream is = excelFile.getInputStream();
// 根据文件后缀名不同(xls和xlsx)获得不同的workbook实现类对象
if (fileName.endsWith(XLS)) {
// 2003版本
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith(XLSX)) {
// 2007版本
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
/**
- 检查文件
- @param excelFile excel文件
- @throws IOException
*/
public static String checkFile(MultipartFile excelFile) throws IOException {
//判断文件是否存在
if (null == excelFile) {
throw new FileNotFoundException(“文件不存在”);
}
//获得文件名
String fileName = excelFile.getOriginalFilename();
//判断文件是否是excel文件
if (!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
throw new IOException(fileName + “不是excel文件”);
}
return fileName;
}
}
-
遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/youjianbo_han_87/article/details/7393916