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;
}
}