jfinal poi

2019独角兽企业重金招聘Python工程师标准>>>

最近项目采用jfinal的项目要对一些excel进行操作，经过考虑采用jfinal+poi,在一些学习，使用后总结并分享一些代码片段。

导入excel========================================

    protected Workbook workbook =null;
    protected File file=null;

    public Object readExcel(UploadFile uploadFile) throws Exception{
        String path = uploadFile.getUploadPath() + "/" + System.currentTimeMillis() + uploadFile.getFileName();
        file = new File(path);
        uploadFile.getFile().renameTo(file);

        //文件是否存在
        this.fileCheck(path);

        //返回workbook
        workbook=this.getWorkbook(path);

        if (validateData()){
          //解析excel读取数据，并返回List<Record>
        List<Record> list = new ArrayList<>();
        String itemName =null;
        Date startTime = null;
        
        sheet = workbook.getSheetAt(0);
        int rows = sheet.getLastRowNum();//从0到最后一行非空row
        for (int i = 3; i <= rows; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                int cells = row.getLastCellNum();//行中，从0到最后一个非空cell
               
                for (int j = 0; j < cells; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {// 需验证单元格里面的数据,""也会返回null
                        switch (j) {
                            case 0:
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                itemName= cell.getStringCellValue().trim();
                                
                                break;
                            case 1:
                            	if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)){//日期也是数值类型
                                    startTime = cell.getDateCellValue();
                                }else{
                                    throw new Exception("请输入正确的日期！");
                                }
                                break;
                           
                            default:
                                break;
                        }
                    }
                Record r = new Record();
                r.set("itemName", itemName);
                r.set("startTime", startTime);
                list.add(r);

                itemName=null;
                startTime=null;
                }

            workbook.close();
            file.delete();
            return list;
        }
        return null;
    };

    
   
    /**
     *  文件常规检查
     * @param filePath
     * @throws FileNotFoundException
     * @throws FileFormatException
     */
    protected void fileCheck(String filePath) throws FileNotFoundException, FileFormatException {
        File file = new File(filePath);
        if (!file.exists()) {
            throw new FileNotFoundException("传入的文件不存在：" + filePath);
        }

        //03 ,07
        if (!(filePath.endsWith(".xls") || filePath.endsWith(".xlsx"))) {
            throw new FileFormatException("传入的文件不是excel");
        }
    }

    /**
     * 03或07excel
     *
     */
    protected Workbook getWorkbook(String filePath) throws IOException {
        Workbook workbook = null;
        InputStream is = new FileInputStream(filePath);
        if (filePath.endsWith(".xls")) {
            workbook = new HSSFWorkbook(is);
        } else if (filePath.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(is);
        }
        return workbook;
    }

导出excel数据===========================================

protected HSSFWorkbook workbook = new HSSFWorkbook();
	protected HSSFSheet sheet=workbook.createSheet();;
	protected HSSFRow row;
	protected HSSFCell cell;
	protected HSSFCellStyle style ;
	protected HSSFFont font ;
	
	protected List<Record> list;
	protected String[] headersId;
	protected String[] headersName;
	protected String title;
	protected String fileName;


//以流的形式直接输出excel到客户端，在controller调用此方法后再次调用 renderNull()此方法即可。
//网上有的是生成excel file 再用renderFile()的方法输出到客户端，但我没有采用这种方法。（注意：这种应该在文件输出后将文件删除）
public void writeExcel(List<Record> list,String title,String[] headersId,String[] headersName,String fileName,HttpServletResponse response) throws Exception{
		this.list=list;
		this.title=title;
		this.fileName=fileName;
		this.headersId=headersId;
		this.headersName=headersName;
		
		this.genTitle();
		this.genList();
		this.genFooter();
		
		OutputStream outputStream=response.getOutputStream();
		try {
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode(fileName, "utf-8")+".xls");
			workbook.write(outputStream);
			outputStream.flush();
		}catch (Exception e) {
			throw new Exception("导出失败！");
		}finally {
			outputStream.close();
			workbook.close();
		}
		
		
	}
	
	
	//生成title及标题行
	public void genTitle(){
		row = sheet.createRow(0);
		//题头
		sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, headersName.length-1));
		cell = row.createCell(0);
		cell.setCellStyle(genTitleStyle());
		cell.setCellValue(title);
		
		rowindex+=3;
	}
	
	
	//生成list数据
	public void genList(){
		//表格标题行
		row = sheet.createRow(rowindex);
		for(int i=0;i<headersName.length;i++){
			cell = row.createCell(i);
			cell.setCellStyle(genH2Style());
			cell.setCellValue(headersName[i]);
		}
		
		Record r=null;
		for(int i=0;i<list.size();i++){
			rowindex++;
			row = sheet.createRow(rowindex);
			r = list.get(i);
			//每行数据
			for(int j =0;j<headersId.length;j++){
				cell = row.createCell(j);
				if(r.get(headersId[j]) instanceof Date)
					cell.setCellValue(DateUtil.dateToStr((Date)r.get(headersId[j]), "yyyy-MM-dd"));
				else if(r.get(headersId[j]) instanceof String)
					cell.setCellValue((String)r.get(headersId[j])==null?"":(String)r.get(headersId[j]));
				else if(r.get(headersId[j]) instanceof Number)
					cell.setCellValue(r.get(headersId[j])==null?"":String.valueOf(r.get(headersId[j])));

			}
		}
		rowindex++;
	}

 

 

 

 

转载于:https://my.oschina.net/WWWW23223/blog/725664