POI-Excel导入与导出

• maven配置

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.6</version>
        </dependency>

• 导入

public void excelImport(MultipartFile file) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
        //获取sheet
        HSSFSheet bigTypeSheet = workbook.getSheet("bigType");
        //遍历除标题外的行内容
        for (int i = 1; i <= bigTypeSheet.getLastRowNum(); i++) {
            HSSFRow row = bigTypeSheet.getRow(i);
            //第一列
            String firstColumn = row.getCell(0).getStringCellValue();
            //第二列
            String secondColumn = row.getCell(1).getStringCellValue();
        }
    }

• 导出

    public void excelExport(HttpServletResponse response) {
        //excel文件名
        String timeStr = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String fileName = "djcx" + timeStr + ".xls";
        //excel标题
        String[] title = {"违禁物", "旅客", "车次"};
        //sheet名
        String sheetName = "djcx";
        //组织内容
        String[][] content = new String[2][title.length];
        for (int i = 0; i < 2; i++) {
            content[i] = new String[title.length];
            content[i][0] = "利器";
            content[i][1] = "张三";
            content[i][2] = "G101";
        }
        //创建HSSFWorkbook
        HSSFWorkbook wb = ExcelUtil.createHSSFWorkbook(sheetName, title, content, null);
        //响应到客户端
        try {
            OutputStream os = response.getOutputStream();
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

public static HSSFWorkbook createHSSFWorkbook(String sheetName, String[] title, String[][] content, HSSFWorkbook wb) {
        // 第一步，创建HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，建sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，创建标题
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            row.createCell(i).setCellValue(title[i]);
        }
        //第四步，创建内容
        for (int i = 0; i < content.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < content[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(content[i][j]);
            }
        }
        return wb;
    }