java导入导出Excel文件

首先是导出

	public void getExcelTemplate(HttpServletResponse response) {
		List<Object[]> list = new ArrayList<>();

        String[] colCaption = new String[]{"题型","分类","题目","选项1","选项2","选项3","选项4","正确答案","答案解析"};

        Object[] obj = new Object[9];
        obj[0] = "单选题";
        obj[1] = "原料管理";
        obj[2] = "下列哪种物品是食品添加剂？";
        obj[3] = "豆酱";
        obj[4] = "鱼露";
        obj[5] = "鸡粉";
        obj[6] = "小苏打";
        obj[7] = "D";
        obj[8] = "";
        list.add(obj);

        Object[] obj1 = new Object[9];
        obj1[0] = "多选题";
        obj1[1] = "默认分类";
        obj1[2] = "食品安全事故发生后应采取的措施有？";
        obj1[3] = "送医";
        obj1[4] = "食品留样";
        obj1[5] = "信息上报";
        obj1[6] = "拍照留证";
        obj1[7] = "ABCDE";
        obj1[8] = "";
        list.add(obj1);

        Object[] obj2 = new Object[9];
        obj2[0] = "判断题";
        obj2[1] = "加工制作";
        obj2[2] = "可以使用甲醛泡发海产品。";
        obj2[3] = "正确";
        obj2[4] = "错误";
        obj2[5] = "";
        obj2[6] = "";
        obj2[7] = "B";
        obj2[8] = "甲醛会与海产品中蛋白质发生反应，破坏蛋白质的结构，使其变质，危害人体健康。";
        list.add(obj2);

        ExcelExport excelExport = new ExcelExport("导入模板");
        excelExport.addSheetByArray("导入模板", list, colCaption);
        excelExport.export(response);
   }

接下来是导入，导入需要解析数据，诸多验证需要自己去加

	public Boolean importExcel(MultipartFile file) {

        List<String[]> excelDataAll = ExcelImport.getExcelDataAll(file).get(0);
        Iterator<String[]> iterator = excelDataAll.iterator();
        String[] next1 = iterator.next();
       
        s:  while (iterator.hasNext()) {
           
            String[] next = iterator.next();
            for (int i = 0; i < next.length; i++) {
                switch (i) {
                    case 0:
                        //解析
                        String str = next[i];
                        //执行操作
                        continue;
                    case 1:
                        //解析
                        String str = next[i];
                         //执行操作
                        continue;
                    case 2:
                        //解析
                        String str  = next[i];
                        //执行操作
                        continue;
                    ...
                    default:
                        continue s;
                }
            }
        }
      
        return flag ;
    }

工具类 ExcelExport

public class ExcelExport {
    public static final String EXCEL03_EXTENSION = ".xls";
    public static final String EXCEL07_EXTENSION = ".xls";
    private Workbook workBook;
    private String exportFileName;
    private Log logger;

    public ExcelExport() {
        this("workbook.xlsx");
    }

    public ExcelExport(String exportFileName) {
        this.logger = LogFactory.getLog(ExcelExport.class);
        if (StringUtils.isBlank(exportFileName)) {
            exportFileName = "workbook.xlsx";
        }

        String fileName = exportFileName.toLowerCase();
        if (fileName.endsWith(".xls")) {
            this.workBook = new HSSFWorkbook();
        } else if (exportFileName.endsWith(".xls")) {
            this.workBook = new XSSFWorkbook();
        } else {
            this.workBook = new XSSFWorkbook();
            fileName = fileName + ".xlsx";
        }

        this.exportFileName = fileName;
    }

    public void export(HttpServletResponse response) {
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
        String fileName = null;

        try {
            HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
            String userAgent = request.getHeader("User-Agent");
            if (!userAgent.contains("MSIE") && !userAgent.contains("Trident")) {
                fileName = new String(this.exportFileName.getBytes("UTF-8"), "ISO-8859-1");
            } else {
                fileName = URLEncoder.encode(this.exportFileName, "UTF-8");
            }
        } catch (UnsupportedEncodingException var6) {
            fileName = "export.xls";
        }

        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

        try {
            ServletOutputStream output = response.getOutputStream();
            this.workBook.write(output);
            output.flush();
            output.close();
        } catch (IOException var5) {
            this.logger.error("导出文件出错了", var5);
            throw new CustomException("文件导出错误");
        }
    }

    public static void main(String[] args) {
        ExcelExport ee1 = new ExcelExport();
        List<Object[]> list1 = new ArrayList();
        List<Object> obj = new ArrayList();
        obj.add("370681198002042214");
        obj.add(new Date());
        obj.add(new Timestamp(System.currentTimeMillis()));
        obj.add(1);
        obj.add(11111111111111L);
        obj.add(123456.125F);
        obj.add(1.2345678912345678E8D);
        obj.add(new BigDecimal("123456789123456789.1234"));
        obj.add(true);
        list1.add(obj.toArray());
        String[] header = new String[]{"身份证号", "日期", "时间", "整型", "长整", "浮点", "双精度", "大数", "布尔"};
        ee1.addSheetByArray("测试1", list1, header);

        try {
            OutputStream fis = new FileOutputStream("D:\\test1.xlsx");
            ee1.getWorkbook().write(fis);
        } catch (FileNotFoundException var7) {
            var7.printStackTrace();
        } catch (IOException var8) {
            var8.printStackTrace();
        }

    }

    public void addSheetByArray(String sheetName, List<Object[]> list, String[] colCaption) {
        Sheet sheet;
        if (StringUtils.isBlank(sheetName)) {
            sheet = this.workBook.createSheet();
        } else {
            sheet = this.workBook.createSheet(sheetName);
        }

        this.createCaptionRow(colCaption, sheet);
        int colNum = 0;
        int startRow = 1;

        int cols;
        for(Iterator var7 = list.iterator(); var7.hasNext(); colNum = colNum > cols ? colNum : cols) {
            Object[] obj = (Object[])var7.next();
            Row row = sheet.createRow(startRow++);
            cols = this.createRowData(row, Arrays.asList(obj));
            row.setHeight((short)400);
        }

        this.adjustCellWidth(colNum, startRow, sheet);
    }

    public void addSheetByMap(String sheetName, List<Map<String, Object>> list, String[] colCaption) {
        Sheet sheet;
        if (StringUtils.isBlank(sheetName)) {
            sheet = this.workBook.createSheet();
        } else {
            sheet = this.workBook.createSheet(sheetName);
        }

        this.createCaptionRow(colCaption, sheet);
        int colNum = 0;
        int startRow = 1;

        int cols;
        for(Iterator var7 = list.iterator(); var7.hasNext(); colNum = colNum > cols ? colNum : cols) {
            Map<String, Object> map = (Map)var7.next();
            Row row = sheet.createRow(startRow++);
            cols = this.createRowData(row, map.values());
            row.setHeight((short)400);
        }

        this.adjustCellWidth(colNum, startRow, sheet);
    }

    private boolean isInIgnors(String name, String[] ignores) {
        String[] var3 = ignores;
        int var4 = ignores.length;

        for(int var5 = 0; var5 < var4; ++var5) {
            String ignore = var3[var5];
            if (name.equals(ignore)) {
                return true;
            }
        }

        return false;
    }

    private int createRowData(Row row, Collection<Object> coll) {
        int cellNum = 0;

        for(Iterator var4 = coll.iterator(); var4.hasNext(); ++cellNum) {
            Object obj = var4.next();
            if (obj == null) {
                obj = "";
            }

            String strValue = obj.toString();
            Cell cell = row.createCell(cellNum);
            if (!strValue.startsWith("FORMULA:")) {
                this.setCellValue(cell, obj);
            }
        }

        return cellNum;
    }

    private void setCellValue(Cell cell, Object obj) {
        CreationHelper createHelper = this.workBook.getCreationHelper();
        String strValue = obj.toString();
        if (obj instanceof Timestamp) {
            Date dt = (Timestamp)obj;
            CellStyle cellStyle = this.workBook.createCellStyle();
            cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
            cell.setCellValue(dt);
            cell.setCellStyle(cellStyle);
        } else if (obj instanceof Date) {
            CellStyle cellStyle = this.workBook.createCellStyle();
            cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
            cell.setCellValue((Date)obj);
            cell.setCellStyle(cellStyle);
        } else {
            DecimalFormat df;
            String value;
            if (obj instanceof Integer) {
                cell.setCellType(CellType.NUMERIC);
                df = new DecimalFormat("0");
                value = df.format(obj);
                cell.setCellValue(value);
            } else if (obj instanceof Long) {
                cell.setCellType(CellType.NUMERIC);
                df = new DecimalFormat("0");
                value = df.format(obj);
                cell.setCellValue(value);
            } else if (obj instanceof Float) {
                cell.setCellType(CellType.NUMERIC);
                df = new DecimalFormat("0.0000");
                value = df.format(obj);
                cell.setCellValue(value);
            } else if (obj instanceof Double) {
                cell.setCellType(CellType.NUMERIC);
                df = new DecimalFormat("0.0000");
                value = df.format(obj);
                cell.setCellValue(value);
            } else if (obj instanceof BigDecimal) {
                cell.setCellType(CellType.STRING);
                cell.setCellValue(strValue);
            } else if (obj instanceof String) {
                cell.setCellType(CellType.STRING);
                cell.setCellValue(strValue);
            } else {
                cell.setCellType(CellType.STRING);
                cell.setCellValue(strValue);
            }
        }

    }

    private void createCaptionRow(String[] colCaption, Sheet sheet) {
        Row row = sheet.createRow(0);
        row.setHeight((short)400);
        if (colCaption != null) {
            for(int i = 0; i < colCaption.length; ++i) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(this.getStyle("title"));
                cell.setCellType(CellType.STRING);
                cell.setCellValue(colCaption[i]);
            }

        }
    }

    private void adjustCellWidth(int cols, int rows, Sheet sheet) {
        int[] cellWidth = new int[cols];

        int j;
        for(j = 0; j < cols; ++j) {
            for(int row = 0; row < rows; ++row) {
                Cell cell = sheet.getRow(row).getCell(j);
                String value = this.getCellValue(cell);
                int length = value.getBytes().length;
                if (length > cellWidth[j]) {
                    cellWidth[j] = length;
                }
            }
        }

        for(j = 0; j < cellWidth.length; ++j) {
            if (cellWidth[j] > 254) {
                cellWidth[j] = 254;
            }

            sheet.setColumnWidth(j, cellWidth[j] * 12 > 255 ? 6528 : cellWidth[j] * 12 * 256 / 10);
        }

    }

    private String getCellValue(Cell cell) {
        String value = "";

        try {
            switch(cell.getCellTypeEnum()) {
            case NUMERIC:
                value = cell.getNumericCellValue() + "1111";
                break;
            case STRING:
                value = cell.getStringCellValue();
            }
        } catch (Exception var4) {
        }

        return value;
    }

    private CellStyle getStyle(String type) {
        CellStyle cs = this.workBook.createCellStyle();
        if ("title".equals(type)) {
            Font font = this.workBook.createFont();
            font.setBold(true);
            cs.setAlignment(HorizontalAlignment.CENTER);
            cs.setFillBackgroundColor((short)40);
            cs.setFont(font);
        }

        return cs;
    }

    public Workbook getWorkbook() {
        return this.workBook;
    }

    public void setWorkbook(Workbook workBook) {
        this.workBook = workBook;
    }

    public String getExportFileName() {
        return this.exportFileName;
    }

    public void setExportFileName(String exportFileName) {
        this.exportFileName = exportFileName;
    }
}