java file 解析为list_JAVA 读取excel文件成List<Entity>

packagecom.fsinfo.common.utils;importcom.fsinfo.modules.enterprise.entity.EnterpriseRecordEntity;importorg.apache.commons.io.FileUtils;importorg.apache.poi.hssf.usermodel.HSSFDataFormat;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importorg.springframework.web.multipart.MultipartFile;importjava.io.File;importjava.io.FileInputStream;importjava.io.IOException;importjava.io.InputStream;importjava.util.Date;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.List;/*** @Author leijiaxuan

* @Email 897953910@qq.com

* @Date 9:50 2020/7/22

**/

public classExcelInsert {//private static Logger logger = Logger.getLogger(ExcelReader.class.getName());//日志打印类

private static final String XLS = "xls";private static final String XLSX = "xlsx";/*** 根据文件后缀名类型获取对应的工作簿对象

*@paraminputStream 读取文件的输入流

*@paramfileType 文件后缀名类型(xls或xlsx)

*@return包含文件数据的工作簿对象

*@throwsIOException*/

public static Workbook getWorkbook(InputStream inputStream, String fileType) throwsIOException {

Workbook workbook= null;if(fileType.equalsIgnoreCase(XLS)) {

workbook= newHSSFWorkbook(inputStream);

}else if(fileType.equalsIgnoreCase(XLSX)) {

workbook= newXSSFWorkbook(inputStream);

}returnworkbook;

}/*** 读取Excel文件内容

*@parammyfile 要读取的Excel文件流

*@return读取结果列表，读取失败时返回null*/

public static ListreadExcel(MultipartFile myfile) {

Workbook workbook= null;

FileInputStream inputStream= null;try{//获取Excel后缀名

String fileName=myfile.getOriginalFilename();

String fileType= fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());// //获取Excel文件//File excelFile = new File(fileName);//if (!excelFile.exists()) {

logger.warning("指定的Excel文件不存在！");//return null;//}

File file= newFile(myfile.getOriginalFilename());

FileUtils.copyInputStreamToFile(myfile.getInputStream(), file);//获取Excel工作簿

inputStream = newFileInputStream(file);

workbook=getWorkbook(inputStream, fileType);//会在本地产生临时文件，用完后需要删除

if(file.exists()) {

file.delete();

}//读取excel中的数据

List resultDataList =parseExcel(workbook);returnresultDataList;

}catch(Exception e) {//logger.warning("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());

return null;

}finally{try{if (null !=workbook) {

workbook.close();

}if (null !=inputStream) {

inputStream.close();

}

}catch(Exception e) {//logger.warning("关闭数据流出错！错误信息：" + e.getMessage());

return null;

}

}

}/*** 解析Excel数据

*@paramworkbook Excel工作簿对象

*@return解析结果*/

private static ListparseExcel(Workbook workbook) {

List resultDataList = new ArrayList<>();//解析sheet

for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {

Sheet sheet=workbook.getSheetAt(sheetNum);//校验sheet是否合法

if (sheet == null) {continue;

}//获取第一行数据

int firstRowNum =sheet.getFirstRowNum();

Row firstRow=sheet.getRow(firstRowNum);if (null ==firstRow) {//logger.warning("解析Excel失败，在第一行没有读取到任何数据！");

}//解析每一行的数据，构造数据对象

int rowStart = firstRowNum + 1;int rowEnd =sheet.getPhysicalNumberOfRows();for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {

Row row=sheet.getRow(rowNum);if (null ==row) {continue;

}

EnterpriseRecordEntity resultData=convertRowToData(row);if (null ==resultData) {//logger.warning("第 " + row.getRowNum() + "行数据不合法，已忽略！");

continue;

}

resultDataList.add(resultData);

}

}returnresultDataList;

}/*** 将单元格内容转换为字符串

*@paramcell

*@return

*/

private staticString convertCellValueToString(Cell cell) {if(cell==null){return null;

}

String returnValue= null;switch(cell.getCellType()) {case 0: //数字

if (HSSFDateUtil.isCellDateFormatted(cell)) {//处理日期格式、时间格式

SimpleDateFormat sdf = null;if (cell.getCellStyle().getDataFormat() ==HSSFDataFormat

.getBuiltinFormat("h:mm")) {

sdf= new SimpleDateFormat("HH:mm");

}else {//日期

sdf = new SimpleDateFormat("yyyy-MM-dd");

}

Date date=cell.getDateCellValue();

returnValue=sdf.format(date);

}else if (cell.getCellStyle().getDataFormat() == 58) {//处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");double value =cell.getNumericCellValue();

Date date=org.apache.poi.ss.usermodel.DateUtil

.getJavaDate(value);

returnValue=sdf.format(date);

}else{double value =cell.getNumericCellValue();

CellStyle style=cell.getCellStyle();

DecimalFormat format= newDecimalFormat();

String temp=style.getDataFormatString();//单元格设置成常规

if (temp.equals("General")) {

format.applyPattern("#");

}

returnValue=format.format(value);

}break;case 1: //字符串

returnValue =cell.getStringCellValue();break;case 4: //布尔

Boolean booleanValue =cell.getBooleanCellValue();

returnValue=booleanValue.toString();break;case 2: //空值

break;case 7: //公式

returnValue =cell.getCellFormula();break;case 64: //故障

break;default:break;

}returnreturnValue;

}/*** 提取每一行中需要的数据，构造成为一个结果数据对象

*

* 当该行中有单元格的数据为空或不合法时，忽略该行的数据

*

*@paramrow 行数据

*@return解析后的行数据对象，行数据错误时返回null*/

private staticEnterpriseRecordEntity convertRowToData(Row row) {

EnterpriseRecordEntity resultData= newEnterpriseRecordEntity();

Cell cell;int cellNum = 0;//获取记录日期

cell = row.getCell(cellNum++);

String recorddate=convertCellValueToString(cell);

resultData.setRecorddate(java.sql.Date.valueOf(recorddate));//获取企业名称

cell = row.getCell(cellNum++);

String enterpriseuuid=convertCellValueToString(cell);

resultData.setEnterpriseuuid(enterpriseuuid);//获取企业反映问题

cell = row.getCell(cellNum++);

String recordcontext=convertCellValueToString(cell);

resultData.setRecordcontext(recordcontext);//获取解决情况

cell = row.getCell(cellNum++);

String handlinfo=convertCellValueToString(cell);

resultData.setHandlinfo(handlinfo);//获取备注

cell = row.getCell(cellNum++);

String remarks=convertCellValueToString(cell);

resultData.setRemarks(remarks);returnresultData;

}

}