Excel的解析与下载

解析工具类：

public class ParseExcelUtil {

    /**
     * 解析Excel表格
     * @param file 用户上传的Excel表格文件
     * @return 表格内容集合
     * @author xWang
     * @Date 2020-05-09
     */
    public static List<List<Map<Integer,Object>>> parse(MultipartFile file) throws IOException{
        InputStream fileInputStream = file.getInputStream();
        String fileType = ToolUtil.getFileSuffix(file.getOriginalFilename());
        //Excel表格数据集合
        List<List<Map<Integer,Object>>> lists = new ArrayList<List<Map<Integer,Object>>>();
        Iterator<Sheet> sheets = null;
        try {
            //获取工作薄
            Workbook wb = null;
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(fileInputStream);
                sheets = wb.iterator();
            } else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(fileInputStream);
                sheets = wb.iterator();
            } else {
                return null;
            }
            if(sheets == null){
                throw new GunsException(BizExceptionEnum.DATA_NULL);
            }

            // 遍历excel里每个sheet的数据。
            while (sheets.hasNext()) {
                Sheet sheet = sheets.next();
                List<Map<Integer,Object>> list = getCellValue(sheet);
                lists.add(list);
            }

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fileInputStream != null) fileInputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return lists;
    }

    /**
     * 获取sheet工作表中的内容
     * @param sheet excel表格sheet工作页
     * @return 工作表中数据内容集合
     * @author xWang
     * @Date 2020-05-09
     */
    private static List<Map<Integer,Object>> getCellValue(Sheet sheet){
        ArrayList<Map<Integer,Object>> list = new ArrayList<Map<Integer,Object>>();
        Cell cell=null;
        for (int rowNum = sheet.getFirstRowNum(); rowNum <sheet.getPhysicalNumberOfRows(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (rowNum < 1){
                continue;
            }
            Map<Integer,Object> map = new HashMap<>();
            for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
                cell = row.getCell(j);
                String cellStringVal = getCellStringVal(cell);
                map.put(j,cellStringVal);
            }
            list.add(map);
        }

        return list;
    }

    /**
     * 判断Excel数据类型
     * @param cell 读取de数据
     * @return 对应的文本格式类型
     * @author xWang
     * @Date 2020-5-09
     */
    private static String getCellStringVal(Cell cell) {
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)){
                    //用于转化为日期格式
                    Date d = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                    return formater.format(d);
                }
                return String.valueOf((int)cell.getNumericCellValue());
            case Cell.CELL_TYPE_STRING:
                return cell.getRichStringCellValue().getString();
            case Cell.CELL_TYPE_BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case Cell.CELL_TYPE_FORMULA:
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                try {
                    return formatter.format(cell.getDateCellValue());
                } catch (Exception e){
                    throw new GunsException(BizExceptionEnum.DATA_FORMAT_ERROR);
                }
            case Cell.CELL_TYPE_BLANK:
                return "";
            case Cell.CELL_TYPE_ERROR:
                return String.valueOf(cell.getErrorCellValue());
            default:
                return null;
        }
    }
}

下载工具类

public class ExcelDownLoadUtil {

    static final short borderpx = 1;

    /**
     * 导出excel表格（标准格式）
     * @param head 表头
     * @param body 数据信息
     * @return
     * @author xWang
     */
    public static HSSFWorkbook expExcel(List<String> head, List<List<String>> body) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell= null;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        setBorderStyle(cellStyle, borderpx);
        cellStyle.setFont(setFontStyle(workbook, "黑体", (short) 14));
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置表头颜色
//        HSSFFont redFont = workbook.createFont();
//        redFont.setColor(Font.COLOR_RED);
//        short color = Font.COLOR_RED;
        sheet.createFreezePane(0,1,0,1);
        for (int i = 0; i<head.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(head.get(i));
            cell.setCellStyle(cellStyle);
        }
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        setBorderStyle(cellStyle2, borderpx);
        cellStyle2.setFont(setFontStyle(workbook, "宋体", (short) 12));
        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (int i = 0; i < body.size(); i++) {
            row = sheet.createRow(i + 1);
            List<String> paramList = body.get(i);
            for (int p = 0; p < paramList.size(); p++) {
                cell = row.createCell(p);
                cell.setCellValue(paramList.get(p));
                cell.setCellStyle(cellStyle2);
            }
        }
        for (int i = 0, isize = head.size(); i < isize; i++) {
            sheet.autoSizeColumn(i);
        }
        return workbook;
    }


    /**
     * 文件输出
     * @param workbook 填充好的workbook
     * @author xWang
     */
    public static void outFile(HSSFWorkbook workbook, HttpServletResponse response, String fileName) {
        OutputStream os=null;
        try {
            response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8"));
            os= new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            workbook.write(os);
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            os.flush();
            os.close();
            System.gc();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置字体样式
     * @param workbook 工作簿
     * @param name 字体类型
     * @param height 字体大小
     * @return HSSFFont
     * @author xWang
     */
    private static HSSFFont setFontStyle(HSSFWorkbook workbook, String name, short height) {
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(height);
        font.setFontName(name);
        return font;
    }

    /**
     * 设置单元格样式
     * @param cellStyle 工作簿
     * @param border border样式
     * @author xWang
     */
    private static void setBorderStyle(HSSFCellStyle cellStyle, short border) {
        cellStyle.setBorderBottom(border); // 下边框
        cellStyle.setBorderLeft(border);// 左边框
        cellStyle.setBorderTop(border);// 上边框
        cellStyle.setBorderRight(border);// 右边框
    }

}