1,前提
这里我已经将excel的文件信息传入数据表中,表格内容以字节流数据存储
datacontent字段就是我存储的数据内容,
fileNameOrg则是文件信息
2,思路
1,获取表格文件信息
2,读取文件数据内容
3,判断文件类型是否为表格
4,读取第一个工作页sheet
5, 读取标题栏
6,读取内容
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;
public HashMap<String, Object> importExcel(@RequestParam HashMap<String, String> param) {
try {
//获取表格文件名
InputStream is = null;
String filename = fileEntity.getFileNameOrg();
//获取后缀名称
try {
is =new ByteArrayInputStream(fileEntity.getDatacontent());
//判断是否是表格文件,是,获取工作簿
Workbook wb = null;
if (filename.contains("xlsx")) {
wb = new XSSFWorkbook(is);
} else if (filename.contains("xlsx")) {
wb = new HSSFWorkbook(is);
} else {
HashMap<String, Object> resultMap = new HashMap<>();
resultMap.put("success", false);
resultMap.put("msg", "数据导入失败!");
return resultMap;
}
//读取第一个工作页sheet
Sheet sheet = wb.getSheetAt(0);
//列头
Row hender = sheet.getRow(0);
//标题行获取,根据索引一一对应
HashMap<Integer, String> fieldnamemap = new HashMap<>();
for (Cell cell : hender) {
fieldnamemap.put(cell.getColumnIndex(), cell.getStringCellValue());
}
//表格行数据
for (Row row : sheet) {
if(StringUtil.isEmptyOrLength0(row)){
break;
}
//非标题行数据读取
if (row.getRowNum()!=0){
for (Cell cell : row) {
//当前单元格的标题
String name = fieldnamemap.get(cell.getColumnIndex());
//字符串型处理
String value1=cell.getStringCellValue();
//数值型处理
double value2=cell.getNumericCellValue();
//时间型处理
int intnum=Integer.parseInt(cell.toString().split("\\.")[0]) ;
double demnum=Double.parseDouble("0."+cell.toString().split("\\.")[1]);
Date dateTime =getTime(getDate(intnum), demnum);
String time=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dateTime);
//日期型处理
int intnum=Integer.parseInt(cell.toString().split("\\.")[0]) ;
Date dateTime =getTime(getDate(intnum), 0);
String date= new SimpleDateFormat("yyyy-MM-dd").format(dateTime);
// 略。。。。。。。。。。
} catch (Exception e) {
logger.error("系统错误!" + e.getMessage(), e);
HashMap<String, Object> resultMap = new HashMap<>();
resultMap.put("success", false);
resultMap.put("msg", "数据导入失败!");
return resultMap;
} finally {
try {
if (is != null) is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
HashMap<String, Object> resultMap = new HashMap<>();
resultMap.put("success", true);
resultMap.put("msg", "数据导入成功!");
return resultMap;
} catch (Exception ex) {
ex.printStackTrace();
HashMap<String, Object> resultMap = new HashMap<>();
resultMap.put("success", false);
resultMap.put("msg", "系统错误,请稍候再试." + ex.getMessage().replace("\r", "").replace("\n", ""));
return resultMap;
}
}
//时间和日期处理的方法
public Date getDate(int days) {
Calendar c = Calendar.getInstance();
c.set(1900, 0, 1);
c.add(Calendar.DATE, days - 2);
return c.getTime();
}
public Date getTime(Date date, double ditNumber) {
Calendar c = Calendar.getInstance();
int mills = (int) (Math.round(ditNumber * 24 * 3600));
int hour = mills / 3600;
int minute = (mills - hour * 3600) / 60;
int second = mills - hour * 3600 - minute * 60;
c.setTime(date);
c.set(Calendar.HOUR_OF_DAY, hour);
c.set(Calendar.MINUTE, minute);
c.set(Calendar.SECOND, second);
return c.getTime();
}