EXCEL报表导入
第一次博客生涯
前言
我们往往会碰到这样的需求,批量导入数据,我们可以通过导入EXCEL数据来实现
一、读取EXCEL文件,并且写入到临时文件中
private File getFilePath(UploadFile uploadFile) {
File file = uploadFile.getFile();
String path = System.getProperty("java.io.tmpdir");
File newFile = new File(path, uploadFile.getSimpleName() + "." + uploadFile.getType());
InputStream in = null;
OutputStream out = null;
try {
in = new NetilerFileInputStream(file);
out = new FileOutputStream(newFile);
IOUtil.write(in, out);
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtil.close(in);
IOUtil.close(out);
}
return newFile;
}
二、读取临时文件,将数据写入到准备好的VIEW对象中
/**
* 获取excal内容
*
* @param filePath
* @param k
*/
private ExcelView getExcel(String filePath, int k) {
List<HashMap<String, String>> resultList = new ArrayList<HashMap<String, String>>();
Workbook wb = null;
Sheet sheet = null;
Row row = null;
wb = readExcel(filePath);
int colnum = 0;
int rownum = 0;
if (wb != null) {
// 获取sheet
sheet = wb.getSheetAt(k);
// 获取最大行数
rownum = sheet.getPhysicalNumberOfRows();
// 获取第一行
row = sheet.getRow(0);
if (row == null) {
return null;
}
if(rownum>1) {
// 获取最大列数
colnum = row.getPhysicalNumberOfCells();
for (int i = 0; i < rownum; i++) {
HashMap<String, String> map = new HashMap<String, String>();
row = sheet.getRow(i);
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
String ip = String.valueOf(row.getCell(0).getStringCellValue());
String contact = String.valueOf(row.getCell(1).getStringCellValue());
String seat = String.valueOf(row.getCell(2).getStringCellValue());
if(!ip.toLowerCase().equals("ip")) {
map.put("ip", ip);
map.put("contact", contact);
map.put("seat", seat);
resultList.add(map);
}
}
}
}
return new ExcelView(resultList, rownum, colnum);
}
// 读取excel
private Workbook readExcel(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(new File(filePath));
if (".xls".equals(extString)) {
return wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return wb = new XSSFWorkbook(is);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
三、将VIEW对象中的数据写入到数据库中,然后删除临时文件
@Override
public String uploadFile(UploadFile uploadFile) {
if(!uploadFile.getType().equals("xls")&&!uploadFile.getType().equals("xlsx")) {
return "只支持xls跟xlsx格式的文件";
}
// 读取xls和xlsx文件
File newFile = getFilePath(uploadFile);
// 循环读取sheet
for (int k = 0; k < 1; k++) {
// 读取excel
ExcelView excel = null;
try {
excel = getExcel(newFile.getPath(), k);
} catch (Exception e) {
return "读取EXCEL失败,请检查格式是否正确";
}
if(excel==null) {
return "读取EXCEL失败,请检查格式是否正确";
}
List<HashMap<String, String>> resultList = excel.getResultList();
if(resultList!=null&&resultList.size()>0) {
for (HashMap<String, String> hashMap : resultList) {
IpContact ipContact = new IpContact();
ipContact.setIp(hashMap.get("ip"));
ipContact.setContact(hashMap.get("contact"));
ipContact.setSeat(hashMap.get("seat"));
save(ipContact);
}
}
}
newFile.delete();
return "success";
}
总结
end