换工作一周了，之前一段时间在家休息，所以并没有记录博客，而这一住也很忙，今天记录一点简单一些的东西吧，关于excel的。
刚来就开始干活了，刚好在工作中有excl导入导出的功能需求，以前也做过，这次在这里记录一下吧，下次就不重复写了。
这是上传excel加解析 xls和xlsx是新旧两种excel
核心步骤：
//获取文件流
InputStream is = file.getInputStream();
//将文件流转换为workbook，这是专门针对excel的工具类
if (hz.toLowerCase().equals(".xls")) {
wb = new HSSFWorkbook(is);// Excel 2003
} else {
wb = new XSSFWorkbook(is);// Excel 2007
}
}
//获得sheet
sheet = wb.getSheetAt(0);
//获得某行
Row row = sheet.getRow(i)
//获得某行的某列
row.getCell(i) /**
* 导入excel
* @param file
* @return
* @throws Exception
*/
public FileDTO importExcel(MultipartFile file) throws IOException ,RuntimeException {
Workbook wb = null;
Sheet sheet = null;
fileName = file.getOriginalFilename();
InputStream is = file.getInputStream();
// 判断Excel版本
logger.info("进入了上传方法,上传的文件名称是：" + fileName.toLowerCase());
// 比较后缀
String hz = fileName.substring(fileName.lastIndexOf("."),
fileName.length());
if (hz.toLowerCase().equals(".xls")) {
wb = new HSSFWorkbook(is);// Excel 2003
} else {
wb = new XSSFWorkbook(is);// Excel 2007
}
sheet = wb.getSheetAt(0);
int rowCount=sheet.getLastRowNum();
logger.info("导入表中有{}条数据",rowCount );
FileDTO handle=null;
try {
handle = this.excel2List(sheet)!=null ? this.excel2List(sheet):new FileDTO();
handle.setFilaName(fileName);
handle.setRowCount(rowCount);
} catch (RuntimeException e) {
throw e;
}
return handle;
}
protected FileDTO excel2List(Sheet sheet) {
FileDTO result =new FileDTO();
//返回的结果
List<RefundTmpImport> list = new ArrayList<>();
//错误信息
Set<String> setErrMes=new HashSet<>();
RefundTmpImport refund = null;
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
try {
Row row = sheet.getRow(i);
refund = new RefundTmpImport();
// 判空
if (row == null) {
this.throwRuntimeException(i, null);
}
refund.setOrderNumber(row.getCell(0) != null ? row.getCell(0)
.toString() : null);
//验证金额
refund.setRefundAmt((Double)this.checkType(row.getCell(1), i, 2, DataType.D_DOUBLE4));
//验证日期
refund.setRefundTime(this.checkDateAndCell2object(row.getCell(2), i, 3, DateFormat.F_DATE));
list.add(refund);
} catch (ParseException e) {
e.printStackTrace();
} catch (RuntimeException e) {
setErrMes.add(e.getMessage());
e.printStackTrace();
}
}
result.setErrorMes(setErrMes);
result.setData(list);
return result;
}导出这边只到xlsx
/**
* 导出excel
* @throws IOException
*/
public void exportExcel(HttpServletRequest request, HttpServletResponse response ,List list) {
HttpSession session = request.getSession();
session.setAttribute("state", null);
// 生成提示信息，
response.setContentType("application/vnd.ms-excel");
OutputStream fOut = null;
try {
// 进行转码，使其支持中文文件名
//TODO 修改导出名称
String codedFileName = java.net.URLEncoder.encode("test", "UTF-8");
response.setHeader("content-disposition", "attachment;filename="
+ codedFileName + ".xlsx");
// 产生工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook();
// 产生工作表对象
XSSFSheet sheet = workbook.createSheet();
// TODO 做导出的实现类
fOut = response.getOutputStream();
this.list2Excel(list, sheet,fOut,workbook);
} catch (IOException e) {
//jsonView.setFlag(0);
logger.error(e.getMessage(), e);
} finally {
try {
fOut.flush();
fOut.close();
} catch (IOException e) {
//jsonView.setFlag(0);
logger.error(e.getMessage(), e);
}
session.setAttribute("state", "open");
}
System.out.println("文件生成...");
}
//转出对象
protected void list2Excel(List list, XSSFSheet sheet,OutputStream fOut,XSSFWorkbook workbook ) throws IOException {
int i = 0;
for (Object o : list) {
ExportExcelDTO dto = (ExportExcelDTO) o;
Row row = sheet.createRow((int) i);// 创建一行
Cell cell0 = row.createCell((int) 0);// 创建一列
cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
cell0.setCellValue(dto.getOrderNumber());
i++;
}
workbook.write(fOut);
}