使用poi 导入导出Excel文件

使用apache poi 可以很方便的进行Excel文件的导入导出操作。

使用poi 导入导出Excel文件

下载Excel文件

//创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//新建工作表
HSSFSheet sheet = null;
sheet = workbook.createSheet("questionTemplate");
//创建行,0表示第一行
HSSFRow row  = sheet.createRow(0);
//创建单元格行号由row确定
HSSFCell cell2 = row.createCell(0);
cell2.setCellValue("题目类型");
cell2.setCellType(CellType.STRING);
sheet.setColumnWidth(0, 6000);

HSSFCell cell5 = row.createCell(1);
cell5.setCellValue("题目内容");
cell5.setCellType(CellType.STRING);
sheet.setColumnWidth(1, 12000);

HSSFCell cell7 = row.createCell(2);
cell7.setCellValue("选项A");
cell7.setCellType(CellType.STRING);
sheet.setColumnWidth(2, 8000);

HSSFCell cell8 = row.createCell(3);
cell8.setCellValue("选项B");
cell8.setCellType(CellType.STRING);
sheet.setColumnWidth(3, 8000);

HSSFCell cell9 = row.createCell(4);
cell9.setCellValue("选项C");
cell9.setCellType(CellType.STRING);
sheet.setColumnWidth(4, 8000);

HSSFCell cell10 = row.createCell(5);
cell10.setCellValue("选项D");
cell10.setCellType(CellType.STRING);
sheet.setColumnWidth(5, 8000);

HSSFCell cell11 = row.createCell(6);
cell11.setCellValue("答案");
cell11.setCellType(CellType.STRING);
sheet.setColumnWidth(6, 8000);

HSSFCell cell12 = row.createCell(7);
cell12.setCellValue("补充说明");
cell12.setCellType(CellType.STRING);
sheet.setColumnWidth(7, 8000);
try {
    workbook.write(fos);
    fos.flush();
} catch (IOException e) {
    logger.error("ExcelUtil downloadTemplate IOException exception" ,e);
} finally {
    if(fos != null){
        try {
            fos.close();
        } catch (IOException e) {
            logger.error("ExcelUtil downloadTemplate FileOutputStream close exception" ,e);
        }
    }

    if(workbook != null){
        try {
            workbook.close();
        } catch (IOException e) {
            logger.error("ExcelUtil downloadTemplate workbook close exception" ,e);
        }
    }
}

/**
 * 下载考试模板
 * @param response
 */
@RequestMapping(value="/downloadTemplate", method= RequestMethod.GET)
public void downloadTemplate(HttpServletResponse response) {
    try {
        //设定输出文件头
        response.setHeader("Content-Disposition", "attachment;filename=" + new String("questionTemplate.xls".getBytes(), "ISO8859-1"));
        // 定义输出类型
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        OutputStream out = response.getOutputStream();
        ExcelUtil.downloadTemplate(out);
    } catch (UnsupportedEncodingException e) {
        logger.error("downloadTemplate controller UnsupportedEncodingException " , e);
    } catch (IOException e) {
        logger.error("downloadTemplate controller IOException " , e);
    }
}

导入Excel文件

/**
 * 导入试题excel
 * @return
 */
@RequestMapping(value="/importQuestion", method= RequestMethod.POST)
@ResponseBody
public Result importQuestion(HttpServletRequest request){
    MultipartFile file = null;
    CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());
    if(multipartResolver.isMultipart(request)){
        MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest)request;
        file = multiRequest.getFileMap().get("file");
    }else {
        return BusinessResultUtil.fail(Constant.ERROR_NOT_FILE_UPLOAD);
    }

     ExcelUtil.importQuestion(file);
     return Result.createSuccessResult();    
}

/**
 * 试题Excel处理
 * @param file
 * @return
 */
public static void importQuestion(MultipartFile file){
    //目前只支持单选，多选
    if(!file.isEmpty()) {
        Workbook workbook = null;
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();

            //由输入流得到工作簿
            if(isExcel2003(file.getOriginalFilename())){
                workbook =  new HSSFWorkbook(inputStream);
            }else if(isExcel2007(file.getOriginalFilename())){
                workbook =  new XSSFWorkbook(inputStream);
            }
            //得到工作表
            Sheet sheet = workbook.getSheetAt(0);
            //判断行数大于1,是因为数据从第二行开始插入
            if(sheet.getPhysicalNumberOfRows() > 1){
                for(int k = 1; k < sheet.getPhysicalNumberOfRows(); k++) {
                    //读取单元格 第 K 行
                      Row rowk = sheet.getRow(k);
                      Cell cell1 = rowk.getCell(0);

                  cell1.setCellType(CellType.STRING);

                String value = cell1.getStringCellValue()

                Cell cell2 = rowk.getCell(1);

Cell cell3 = rowk.getCell(2); Cell cell4 = rowk.getCell(3); Cell cell5 = rowk.getCell(4); Cell cell6 = rowk.getCell(5); } } catch (IOException e) { logger.error("ExcelUtil importUser IOException" ,e); return null; }finally { if(inputStream != null){ try { inputStream.close(); } catch (IOException e) { logger.error("ExcelUtil importUser IOException",e); } } if(workbook != null){ try { workbook.close(); } catch (IOException e) { logger.error("ExcelUtil importUser IOException",e); } } } } return null;}

public static boolean isExcel2007(String filePath) {
    return filePath.matches("^.+\\.(?i)(xlsx)$");
}

public static boolean isExcel2003(String filePath){
    return filePath.matches("^.+\\.(?i)(xls)$");
}

使用poi 导入导出Excel文件

相关推荐