excel工具类

1、转换成JsonArray

    /**
     * 将excel转换成jsonarray
     * @param book
     * @param sheetIndex
     * @param propertys { "key1", "key2","key3"}
     * @param flag
     * @return
     */
    public static JSONArray excleInGeneric(Workbook book, int sheetIndex, String[] propertys, boolean flag){
        JSONArray ar = new JSONArray();
        try {
            Sheet sheet = book.getSheetAt(sheetIndex);
            for(int i = 2;i <= getAccuracyContextNum(sheet);i++){
                Row row = sheet.getRow(i);
                JSONObject jsobj= new JSONObject();
                for(int j = 1;j<propertys.length + 1;j++){
                    String fieldName = propertys[j-1];
                    //获取excel相应列的内容
                    String value = changeToString(row.getCell(j), flag).trim();

                    jsobj.put(fieldName, value);
                }
                ar.add(jsobj);
            }
        } catch (Exception e) {
            logger.warn("" + e);
        }
        return ar;
    }

    /**
     * 读取单元格内容变为字符串
     * @param cell
     * @param flag 遇到数字时是否需要转为两位小数，true需要，false不需要
     * @return
     * @throws Exception
     */
    public static String changeToString(Cell cell, boolean flag) throws Exception {
        String returnv = "";
        if (cell == null)
            return returnv;
        int type = cell.getCellType();
        DecimalFormat df = new DecimalFormat("#.00"); // 当取数遇到科学计数的时候，转换成整型
        switch (type) {
            case Cell.CELL_TYPE_NUMERIC: {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                if (DateUtil.isCellDateFormatted(cell)) { //判断是否为日期类型
                    Date date = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                    returnv = formater.format(date);
                    break;
                }
                if (flag){
                    returnv = df.format(cell.getNumericCellValue());
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    returnv = cell.getRichStringCellValue().getString();

                }
                break;
            }
            case Cell.CELL_TYPE_STRING:
                returnv = cell.getRichStringCellValue().getString();
                break;
            default:
                break;
        }
        return returnv;
    }

    //获取准确的文件行数
    public static int getAccuracyContextNum(Sheet sheet) {
        // 删除空行
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            // 删除空行
            if (isRowEmpty(row)) {
                int lastRowNum = sheet.getLastRowNum();
                if (i >= 0 && i < lastRowNum) {
                    sheet.shiftRows(i + 1, lastRowNum, -1);// 将行号为i+1一直到行号为lastRowNum的单元格全部上移一行，以便删除i行
                }
                if (i == lastRowNum) {
                    if (row != null) {
                        sheet.removeRow(row);
                    }
                }
                i--;
            }
        }
        return sheet.getLastRowNum();
    }

    /**
     * 判断excel是否是空行
     * @param row
     * @return
     */
    public static boolean isRowEmpty(Row row){
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK){
                return false;
            }else {
                break;
            }
        }
        return true;
    }

2、xlsx导出

    /**
     * excel导出
     * @param response
     * @param list 第一个是表头信息
     * @param sheetName
     * @throws Exception
     */
    public static void outPut(HttpServletResponse response, List list,
                              String sheetName) throws Exception {

        OutputStream os = response.getOutputStream();// 取得输出流
        response.reset();// 清空输出流
        response.setHeader("Content-disposition", "attachment; filename="
                + new String(sheetName.getBytes("GB2312"), "8859_1") + ".xlsx");// 设定输出文件头
        response.setContentType("application/vnd.ms-excel;charset=gb2312");// 定义输出类型
        try {
            // 工作区
            XSSFWorkbook wb = new XSSFWorkbook();

            XSSFCellStyle cellStyle = wb.createCellStyle();

            // 单元格横向纵向对齐 水平居中 垂直居中
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);// 边框黑色

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

            XSSFFont font = wb.createFont();
            font.setFontHeightInPoints((short) 12);
            font.setFontName(" 黑体 ");

            cellStyle.setFont(font);

            // 创建一个sheet
            XSSFSheet sheet = wb.createSheet(sheetName);

            // 生成表头
            XSSFRow rowHead = sheet.createRow(0);
            rowHead.setHeightInPoints((short) 25);
            Object[] headData = (Object[]) list.get(0);
            for (int i = 0, n = headData.length; i < n; i++) {
                // 给这表头赋值
                if (headData[i] != null)
                    rowHead.createCell(i).setCellValue(headData[i].toString());
                else {
                    rowHead.createCell(i).setCellValue("");
                }
            }
            setCellStyle(cellStyle, sheet, rowHead);

            // 生成excel内容体
            for (int i = 1, num = list.size(); i < num; i++) {
                try {
                    Object[] contentData = (Object[]) list.get(i);

                    XSSFRow excelRow = sheet.createRow(i);
                    excelRow.setHeightInPoints((short) 20);
                    for (int j = 0; j < contentData.length; j++) {
                        if (contentData[j] != null) {
                            excelRow.createCell(j).setCellValue(
                                    contentData[j].toString());
                        } else {
                            excelRow.createCell(j).setCellValue("");
                        }
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            // 写文件
            if (os != null)
                wb.write(os);
            // 关闭输出流
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                os.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }

    }

    /**
     * 给表头单元格添加样式
     *
     * @param cellStyle
     *            样式
     * @param sheet
     *            sheet页
     * @param row
     *            行
     */
    public static void setCellStyle(XSSFCellStyle cellStyle, XSSFSheet sheet,
                                    XSSFRow row) {
        for (short i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            cell.setCellStyle(cellStyle);
        }
    }