poi实现Excel文件导入、导出

2017-10-23 阅读量
poi 是什么呢

Apache POI 简介是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office（Excel、WORD、PowerPoint、Visio等）格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“可怜的模糊实现”。
与我而言

使用于某项技术，来完成特定业务是最重要的。看下图最终要实现的功能：
考查、考试，在对应的中期（期中）考核形式、期末考试形式相应位置处画 √
必修课补考形式唯一必填。
难点在于期中考试形式、期末考试形式、必修课补考形式（考试形式字典）是动态可增减的。
考务：考试形式登记
归纳总结Excel工具类


public static Map<String,String>  getExcelHeaderString(String filePath) {
//        You'd use HSSF if you needed to read or write an Excel file using Java (XLS). 
//        You'd use XSSF if you need to read or write an OOXML Excel file using Java (XLSX).         
        boolean isE2007 = false;    //判断是否是excel2007格式

        if(filePath.endsWith("xlsx")) isE2007 = true;
        //得到表头字段
        Map<String, String> excelHeaderMap = new LinkedHashMap<String, String>();
        InputStream input = null;
        Workbook wb = null;
        try {
            //建立输入流
            input = new FileInputStream(filePath);
            //根据文件格式(2003或者2007)来初始化
            if(isE2007) wb = new XSSFWorkbook(input);
            else wb = new HSSFWorkbook(input);

            Sheet sheet = wb.getSheetAt(0);     //获得第一个表单
            Row row = sheet.getRow(0);
            int columnNum = row.getLastCellNum();

            for (short i = 0; i < columnNum; i++) {
                Cell cell = row.getCell(i);
                String HeaderString = cell.getRichStringCellValue().getString().trim();
                excelHeaderMap.put(HeaderString,HeaderString);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            try {
                if(null != wb){
                    input.close();
                    wb.close();
                }
            } catch (IOException e) {
            }
        }
        return excelHeaderMap;
    }


    public static List<Map<String,Object>> getBeanList(String filePath) throws IOException{
        return getBeanList(filePath, null);
    }
    /**
     * 解析excel 数据，每一行存为一个map， key为每列首行对应的文字
     */
    public static List<Map<String,Object>> getBeanList(String filePath, Map<String, String> matchingResultMap) throws IOException{
        List<Map<String,Object>> beanList = new ArrayList<Map<String,Object>>();

        boolean isE2007 = false;    //判断是否是excel2007格式

        if(filePath.endsWith("xlsx")) isE2007 = true;
        InputStream input = null;
        Workbook wb = null;
        try {
            //建立输入流
            input = new FileInputStream(filePath);
            //根据文件格式(2003或者2007)来初始化
            if(isE2007) wb = new XSSFWorkbook(input);
            else wb = new HSSFWorkbook(input);

            Sheet sheet = wb.getSheetAt(0);
            Map<Short, String> excelHeaderMap = new HashMap<Short, String>();
            int firstRowNum = sheet.getFirstRowNum();
            Row row = sheet.getRow(firstRowNum);
            int columnNum = row.getLastCellNum();
            for (short i = 0; i < columnNum; i++) {
                Cell cell = row.getCell(i);
                String HeaderString = cell.getRichStringCellValue().getString().trim();
                excelHeaderMap.put(i,HeaderString);
            }
            int rowNum = sheet.getPhysicalNumberOfRows();// 获取第0个sheet的行的数量(1...n)

            for (int i = firstRowNum+1; i < rowNum; i++) {
                Row xRow = sheet.getRow(i);
                Map<String,Object> beanMap = new LinkedHashMap<String, Object>();
                for(short j=0; j < columnNum; j++ ){
                    String headerKey = null;
                    if ( matchingResultMap == null
                            || matchingResultMap.get(excelHeaderMap.get(j)) == null){
                        headerKey = excelHeaderMap.get(j);
                    } else {
                        matchingResultMap.get(excelHeaderMap.get(j));
                    }
                    String headerValue = null;
                    if(null != xRow.getCell(j)){
                        headerValue = getCellContent(xRow.getCell(j)).trim();
                        if (beanMap.get(headerKey) == null){
                            beanMap.put(headerKey, headerValue);
                        }
                    }else{
                        beanMap.put(headerKey, "");
                    }
                }
                if(beanMap.containsKey(null)){
                    beanMap.remove(null);
                }
                beanMap.put("row_num", i + 1);//excel中的行号
                beanList.add(beanMap);
            }
        }catch(IOException e){

        }finally{
            input.close();
            wb.close();
        }
        return beanList;
    }

    /**
     * 根据 二维数组（数据）在指定路径下生成文件
     * 
     * @param fileFullPathName 绝对文件路径（包含文件名）
     * @param fieldNames 表头字段数组
     * @param data 二维数组（数据）Object[行][列]
     * @return boolean (标识是否成功生成文件)
     * @author zhangjinkui
     */
    public static boolean writeFile(String fileFullPathName,String[] fieldNames,Object[][] data) {

        boolean flag = false;
        HSSFWorkbook wb = new HSSFWorkbook();
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        try {
            // 创建字体
            Font font = wb.createFont();
            // 创建列样式
            CellStyle cellStyle = wb.createCellStyle();

            Sheet sheet = wb.createSheet();
            Row row = sheet.createRow(0);
            int colLength = fieldNames.length;
            for (int i = 0; i < colLength; i++) {
                row.setHeightInPoints(25);
                sheet.setColumnWidth(i, 256*16);
                ExcelUtils.createCell(row, i, fieldNames[i],createHeadCell(wb));
            }
            int line = 0;
            for (line = 0; line < data.length; line++) {
                Row rowtemp = sheet.createRow(line +1);
                for (int col = 0; col < colLength; col++) {
                    createCell2(rowtemp, col,data[line][col],createCellStyle(font,cellStyle));
                }
            }
            wb.write(baos);
            File file = new File(fileFullPathName);
            FileUtils.writeByteArrayToFile(file, baos.toByteArray());    
            flag = true;
        } catch (Exception e) {
            flag = false;
        } finally {
                try {
                    baos.close();
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                } 
        }
        return flag;
    }

    /**
     * 得到字节流之后，可直接写入客户端，也可写入到指定文件
     * 
     * @param sheetName sheet页名称
     * @param fieldNames 表头字段数组
     * @param data 二维数组（数据）Object[行][列]
     * @return  返回字节数组
     * @throws IOException 
     * @author zhangjinkui
     */
    public static byte[] writeExcel(String sheetName,String[] fieldNames,Object[][] data) throws IOException{
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HSSFWorkbook wb = new HSSFWorkbook();
        try {
            // 创建字体
            Font font = wb.createFont();
            // 创建列样式
            CellStyle cellStyle = wb.createCellStyle();
            Sheet sheet = null;
            if(StringUtils.isBlank(sheetName)){
                sheet = wb.createSheet("");
            }else{
                sheet =  wb.createSheet(sheetName);
            }
            Row row = sheet.createRow(0);
            int colLength = fieldNames.length;
            for (int i = 0; i < colLength; i++) {
                row.setHeightInPoints(25);
                sheet.setColumnWidth(i, 256*16);
                ExcelUtils.createCell(row, i, fieldNames[i], createHeadCell(wb));
            }
            int line = 0;
            for (line = 0; line < data.length; line++) {
                Row rowtemp = sheet.createRow(line +1);
                for (int col = 0; col < colLength; col++) {
                    createCell2(rowtemp, col,data[line][col],createCellStyle(font,cellStyle));
                }
            }
            wb.write(baos);
        } catch (Exception e) {
        } finally {
                try {
                    baos.close();
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                } 
        }
        return baos.toByteArray();
    }

    /**
     * 合并单元格 行
     * @param sheet    
     * @param columnNum 从第几列开始（0-based）
     * @param rowStart          从第几行开始（0-based）
     * @param rowspan          跨多少行
     */
    public static void mergedRegionRow(Sheet sheet, int columnNum, int rowStart, int rowspan){
         sheet.addMergedRegion(new CellRangeAddress(
                 rowStart, //first row (0-based)
                 rowStart + rowspan-1, //last row  (0-based)
                 columnNum, //first column (0-based)
                 columnNum  //last column  (0-based)
         ));
    }

    /**
     * 合并单元格 列
     * @param sheet
     * @param rowNum 第几行 （0-based）
     * @param colStart    从第几列开始（0-based）
     * @param colspan    跨多少列
     */
    public static void mergedRegionColumn(Sheet sheet, int rowNum, int colStart, int colspan){
         sheet.addMergedRegion(new CellRangeAddress(
                 rowNum, //first row (0-based)
                 rowNum, //last row  (0-based)
                 colStart, //first column (0-based)
                 colStart + colspan - 1 //last column  (0-based)
         ));
    }

    /**
     * 设置生成Cell单元格 
     * @param row
     * @param column
     * @param cellValue
     * @param cellStyle
     */
    public static void createCell(Row row, Integer column, String cellValue, CellStyle cellStyle) {
        Cell cell = row.createCell(column);
        if(StringUtils.isEmpty(cellValue)) cellValue = "";
        cell.setCellValue(cellValue);
        // 统一将单元格格式设置成字符串类型
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyle);
    }

    public static void createCell2(Row row, Integer column, Object cellValue, CellStyle cellStyle) {
        Cell cell = row.createCell(column);
        if(StringUtils.isEmpty(String.valueOf(cellValue))) cellValue = "";

        try {
            if (isNumeric(String.valueOf(cellValue))){
                double num = Double.valueOf(String.valueOf(cellValue));
                cell.setCellValue(num);
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            } else {
                cell.setCellValue(String.valueOf(cellValue));
                cell.setCellType(Cell.CELL_TYPE_STRING);
            }
        }catch(Exception e){
            cell.setCellValue(String.valueOf(cellValue));
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }

        cell.setCellStyle(cellStyle);
    }

     private static boolean isNumeric(String value){
             if (StringUtils.isBlank(value) ){
                 return false;
             }
             //http://blog.jobbole.com/96052/ 
             //非负浮点数：^\d+(\.\d+)?$ 或 ^[1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0$ 
//            String reg = "^\\d+(\\.\\d+)?$";
            String reg = "^(0|([1-9]\\d{0,6}))(\\.\\d+)?$";//避免长数字出现科学计数法
            return Pattern.compile(reg).matcher(value).find();
        }

    /**
     * 设置生成Cell单元格 
     * @param row
     * @param column
     * @param cellValue
     */
    public static void createCell(Row row, Integer column, String cellValue) {
        Cell cell = row.createCell(column);
        if(StringUtils.isEmpty(cellValue)) cellValue = "";
        cell.setCellValue(cellValue);
        // 统一将单元格格式设置成字符串类型
        cell.setCellType(Cell.CELL_TYPE_STRING);
    }

    /**
     * 设置生成Cell单元格 
     * @param row
     * @param column
     * @param cellValue
     */
    public static void createCell(Row row, Integer column, Object cellValue) {
        Cell cell = row.createCell(column);
        if(StringUtils.isEmpty(String.valueOf(cellValue))) cellValue = "";
        cell.setCellValue(String.valueOf(cellValue));
        // 统一将单元格格式设置成字符串类型
        cell.setCellType(Cell.CELL_TYPE_STRING);
    }

    /**
     * 获得excel列的值，以字符串格式返回
     * 避免过大数字 以出现科学计数法形式出现
     * @param cell
     * @return
     */
    public static String getCellContent(Cell cell){
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return String.valueOf(cell.getRichStringCellValue().toString().trim());
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return String.valueOf(cell.getDateCellValue());
            } else {
                DecimalFormat df = new DecimalFormat("0");
                return  df.format(cell.getNumericCellValue());
            }
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_FORMULA:
            String strCell = null;
            try {  
                   strCell = String.valueOf(cell.getStringCellValue().trim());  
            } catch (IllegalStateException e) {  
                   strCell = String.valueOf(cell.getNumericCellValue());  
            }  
            return strCell;
        default:
            return String.valueOf(cell.getRichStringCellValue().getString().trim());
        }
    }

    /**
     * 正文 默认字体样式，setWrapText=true（自动换行）
     * @return
     */
    public static CellStyle createCellStyle(Font font,CellStyle cellStyle) {

        // 设置字体颜色 (黑色)
        font.setColor(HSSFColor.BLACK.index);
        // 设置字体
        font.setFontName("宋体");
        // 设置粗体
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 设置字体大小
        font.setFontHeightInPoints((short)12);
        cellStyle.setWrapText(true);
        // 设置字体
        cellStyle.setFont(font);
        // 设置对齐
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setLocked(false);
        return cellStyle;
    }

    /**
     * 正文 默认字体样式，setWrapText=false（不允许自动换行）
     * @return
     */
    public static CellStyle createCellStyleWrapTextFalse(Font font,CellStyle cellStyle) {

        // 设置字体颜色 (黑色)
        font.setColor(HSSFColor.BLACK.index);
        // 设置字体
        font.setFontName("宋体");
        // 设置粗体
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 设置字体大小
        font.setFontHeightInPoints((short)12);
        cellStyle.setWrapText(false);
        // 设置字体
        cellStyle.setFont(font);
        // 设置对齐
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setLocked(false);
        return cellStyle;
    }

    /**
     * 允许换行
     * @param wb
     * @return
     */
    public static CellStyle createBrCellStyle(HSSFWorkbook wb) {
        // 创建列样式
        CellStyle style = wb.createCellStyle();
        // 创建字体
        Font font = wb.createFont();
        // 设置字体颜色 (黑色)
        font.setColor(HSSFColor.BLACK.index);
        // 设置字体
        font.setFontName("宋体");
        // 设置粗体
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 设置字体大小
        font.setFontHeightInPoints((short)12);
        // 设置不换行
        style.setWrapText(true);
        // 设置字体
        style.setFont(font);
        // 设置对齐
        style.setAlignment(CellStyle.ALIGN_LEFT);
        // 垂直居中
        style.setVerticalAlignment(CellStyle.ALIGN_LEFT);
        style.setLocked(false);

        return style;
    }

    /**
     * 表头默认样式：宋体 12号字体，加粗
     * @return
     */
    public static CellStyle createHeadCell(HSSFWorkbook wb) {
        // 创建字体
        Font font = wb.createFont();
        // 创建列样式
        CellStyle cellStyle = wb.createCellStyle();
        // 设置字体颜色 (黑色)
        font.setColor(HSSFColor.BLACK.index);
        font.setFontName("宋体");
        // 设置粗体
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体大小
        font.setFontHeightInPoints((short)12);
        // 设置不允许自动换行
        cellStyle.setWrapText(false);
        // 设置字体
        cellStyle.setFont(font);
        // 设置对齐
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setLocked(false);

        return cellStyle;
    }

    /**
     * 特殊格式自定义，主要用在表头上
     * @return
     */
    public static  CellStyle createHSSFCellStyle(HSSFWorkbook wb,short boldweight, short fontheight, short alignment) {
        // 创建列样式
        CellStyle style = wb.createCellStyle();
        // 创建字体
        Font font = wb.createFont();
        // 设置字体颜色 (黑色)
        font.setColor(HSSFColor.BLACK.index);
        // 设置字体
        font.setFontName("宋体");
        // 设置粗体
        font.setBoldweight(boldweight);
        // 设置字体大小
        font.setFontHeightInPoints(fontheight);
        // 设置不换行
        style.setWrapText(false);
        // 设置字体
        style.setFont(font);
        // 设置对齐
        style.setAlignment(alignment);
        // 垂直居中
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setLocked(false);

        return style;
    }

    /**
     * 获取合并单元格的值
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return ExcelUtils.getCellContent(fCell);
                }
            }
        }

        return null;
    }

    /**
     * 得到合并区域
     * 
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static CellRangeAddress getMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            int firstColumn = region.getFirstColumn();
            int lastColumn = region.getLastColumn();
            int firstRow = region.getFirstRow();
            int lastRow = region.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return region;
                }
            }
        }
        return null;
    }

    /**
     * 判断合并了行
     * 
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static boolean isMergedRow(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row == firstRow && row == lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * 
     * @param sheet
     *            工作表
     * @param row
     *            行下标
     * @param column
     *            列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
页面部分分为三步：
1、上传文件
导入第一步
2、设置数据更新方式
导入第二步
3、提示（比如说Excel中有重复记录，也需要提示出来，让用户修改后重新导入）
导入第三步