poi 删除列_数据回显、删除以及excel导出

数据回显

当点击某个按钮跳转某个页面之前，发送请求到后台进行数据查询，最后将查询结果返回给前端页面，前端页面获取数据，最后呈现出来的效果是就回显的效果。

示例代码：

前端代码：

Controller

Service

删除功能，举例，当一个部门需要将信息删除，需要满足该部门下没有员工信息，没有子部门信息，这样才能将部门信息删除，否则进行提示无法删除。在EasyUI中也有类似的，比如树，要删除树中的文件夹需要先判断该文件夹中是否存有子文件等..

Service

    //删除
    @Override
    public Result deleteDeptByDeptId(String[] deptIds) throws Exception {
        List<String> deptName = new ArrayList<>();
        for (int i = 0; i < deptIds.length; i++) {
            String deptId = deptIds[i];
            //判断是否有子部门
            if (isHaveChildDept(deptId)) {
                DeptP deptP = deptPMapper.selectByPrimaryKey(deptId);
                deptName.add(deptP.getDeptName());
                continue;
            }
            //有员工的部门不能删除
            if (isHaveUser(deptId)) {
                DeptP deptP = deptPMapper.selectByPrimaryKey(deptId);
                deptName.add(deptP.getDeptName());
                continue;
            }
            deptPMapper.deleteByPrimaryKey(deptId);
        }
        if (deptName.size() > 0) {
            return new Result(400, null, deptName);
        }

        return new Result(200, "删除成功", null);
    }

    //判断该部门是否有员工
    private boolean isHaveUser(String deptId) {
        UserPExample userPExample = new UserPExample();
        UserPExample.Criteria criteria = userPExample.createCriteria();
        criteria.andDeptIdEqualTo(deptId);
        List<UserP> userPList = userPMapper.selectByExample(userPExample);
        if (userPList != null && userPList.size() > 0) {
            return true;
        }
        return false;
    }

    //判断该部门是否有子部门
    private boolean isHaveChildDept(String deptId) {
        DeptPExample deptPExample = new DeptPExample();
        DeptPExample.Criteria criteria = deptPExample.createCriteria();
        criteria.andParentIdEqualTo(deptId);
        List<DeptP> deptPList = deptPMapper.selectByExample(deptPExample);
        if (deptPList != null && deptPList.size() > 0) {
            return true;
        }
        return false;
    }
}

excel表格导出

依赖

    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
    </dependencies>

ExportExcelUtil

public class ExportExcelUtil {
	private CellStyle cs;
	/**
	 * 描述：根据文件路径获取项目中的文件
	 * 
	 * @param path
	 *            文件路径
	 * @param filePath
	 * @return
	 * @throws Exception
	 */
	public File getExcelTplFile(String path, String filePath) throws Exception {
		String classDir = null;
		String fileBaseDir = null;
		File file = null;
		file = new File(path+filePath);
		if (!file.exists()) {
			throw new Exception("模板文件不存在！");
		}
		return file;
	}
	/**
	 * 创建工作簿
	 * @param file
	 * @return
	 * @throws Exception
	 */
	public Workbook getWorkbook(File file) throws Exception {
		FileInputStream fis = new FileInputStream(file);
		Workbook wb = new ImportExcelUtil().getWorkbook(fis, file.getName()); // 获取工作薄
		return wb;
	}

	/**
	 * 创建Sheet
	 * @param wb
	 * @param sheetName
	 * @return
	 * @throws Exception
	 */
	public Sheet getSheet(Workbook wb, String sheetName) throws Exception {
		cs = setSimpleCellStyle(wb); // Excel单元格样式
		Sheet sheet = wb.getSheet(sheetName);
		return sheet;
	}

	/**
	 * 创建Row
	 * @param sheet
	 * @return
	 * @throws Exception
	 */
	public Row createRow(Sheet sheet) throws Exception {
		// 循环插入数据
		int lastRow = sheet.getLastRowNum() + 1; // 插入数据的数据ROW
		Row row = sheet.createRow(lastRow);
		return row;
	}
	/**
	 * 创建Cell
	 * @param row
	 * @param CellNum
	 * @return
	 * @throws Exception
	 */
	public Cell createCell(Row row, int CellNum) throws Exception {
		Cell cell = row.createCell(CellNum);
		cell.setCellStyle(cs);
		return cell;
	}

	/**
	 * 描述：设置简单的Cell样式
	 * 
	 * @return
	 */
	public CellStyle setSimpleCellStyle(Workbook wb) {
		CellStyle cs = wb.createCellStyle();

		cs.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
		cs.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
		cs.setBorderTop(CellStyle.BORDER_THIN);// 上边框
		cs.setBorderRight(CellStyle.BORDER_THIN);// 右边框

		cs.setAlignment(CellStyle.ALIGN_CENTER); // 居中

		return cs;
	}

}

ImportExcelUtil

public class ImportExcelUtil {
	
	private final static String excel2003L =".xls";    //2003- 版本的excel
	private final static String excel2007U =".xlsx";   //2007+ 版本的excel
	
	/**
	 * 描述：获取IO流中的数据，组装成List<List<Object>>对象
	 * @param in,fileName
	 * @return
	 * @throws IOException 
	 */
	public  List<List<Object>> getListByExcel(InputStream in,String fileName) throws Exception{
		List<List<Object>> list = null;
		
		//创建Excel工作薄
		Workbook work = this.getWorkbook(in,fileName);
		if(null == work){
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		
		list = new ArrayList<List<Object>>();
		//遍历Excel中所有的sheet
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if(sheet==null){continue;}
			
			//遍历当前sheet中的所有行
			for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++) {
				row = sheet.getRow(j);
				if(row==null||row.getFirstCellNum()==j){continue;}
				
				//遍历所有的列
				List<Object> li = new ArrayList<Object>();
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					li.add(this.getCellValue(cell));
				}
				list.add(li);
			}
		}
		return list;
	}
	
	/**
	 * 描述：根据文件后缀，自适应上传文件的版本 
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if(excel2003L.equals(fileType)){
			wb = new HSSFWorkbook(inStr);  //2003-
		}else if(excel2007U.equals(fileType)){
			wb = new XSSFWorkbook(inStr);  //2007+
		}else{
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 * @param cell
	 * @return
	 */
	public  Object getCellValue(Cell cell){
		Object value = null;
		DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
		
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if("General".equals(cell.getCellStyle().getDataFormatString())){
				value = df.format(cell.getNumericCellValue());
			}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
				value = sdf.format(cell.getDateCellValue());
			}else{
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}
}

方法

public void export(PageBean pageBean, Model model,
                       HttpServletRequest request, HttpServletResponse response)throws Exception{
        //1、查询要导出的数据
        PageBean pb = deptService.listDeptOfPage(pageBean);
        List<DeptVo> datas = (List<DeptVo>) pb.getDatas();

        //2、将数据写入到excel表格中
            //创建导出工具类对象
        ExportExcelUtil excelUtil = new ExportExcelUtil();
        String realPath = request.getSession().getServletContext().getRealPath("/");
        String filePath = "/tpl/dept_export.xlsx";
            //模板
        File exceTpFile = excelUtil.getExcelTplFile(realPath,filePath);
            //工作簿
        Workbook workbook = excelUtil.getWorkbook(exceTpFile);
            //sheet
        Sheet sheet = excelUtil.getSheet(workbook, "部门信息");
        for(int i = 0; i<datas.size();i++){
            DeptVo deptVo = datas.get(i);
            //行数
            Row row = excelUtil.createRow(sheet);
            Cell cell0 = excelUtil.createCell(row,0);
            cell0.setCellValue(deptVo.getDeptNo());

            Cell cell1 = excelUtil.createCell(row,1);
            cell1.setCellValue(deptVo.getParentDeptName());

            Cell cell2 = excelUtil.createCell(row,2);
            cell2.setCellValue(deptVo.getDeptName());

            Cell cell3 = excelUtil.createCell(row,3);
            cell3.setCellValue(deptVo.getState()==1?"启用":"停用");
        }
        //3、把写好的excel发送到客户端
        String fileName="dept_list.xlsx";
        response.setContentType("application/ms-excel");
        response.setHeader("Content-disposition", "attachment;filename="+fileName);
        ServletOutputStream ouputStream = response.getOutputStream();
        workbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }