java 实现excel批量导入导出功能

目录

导出

导入

---

导出

ExportExcelUtil（导出工具类）

/**
 * 导出Excel工具类 admin 2018/10/11
 * title 表名
 * headersName 使用数组封装excel列名
 * headersId 使用数组封装对应列名需要展示的列的属性名 （二者需要一一对应，顺序不能打乱）
 * dtoList 对象结果集
 * */
public class ExportExcelUtil<T> {
    @SuppressWarnings("deprecation")
    public void exportExcel(String title, String[] headersName,
                            String[] headersId, List<T> dtoList, HttpServletResponse response, String name)
            throws Exception {
        Map<Integer, String> headersNameMap = new HashMap<>();
        int key = 0;
        for (int i = 0; i < headersName.length; i++) {
            if (!headersName[i].equals(null)) {
                headersNameMap.put(key, headersName[i]);
                key++;
            }
        }
        Map<Integer, String> titleFieldMap = new HashMap<>();
        int value = 0;
        for (int i = 0; i < headersId.length; i++) {
            if (!headersId[i].equals(null)) {
                titleFieldMap.put(value, headersId[i]);
                value++;
            }
        }
        //创建Excel工作薄对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建Excel工作表对象
        HSSFSheet sheet = wb.createSheet(title);
//        设置列宽
        sheet.setDefaultColumnWidth((short) 15);
        //创建单元格样式
        HSSFCellStyle style = wb.createCellStyle();
        //创建Excel工作表的行
        HSSFRow row = sheet.createRow(0);
        System.out.println(HSSFWorkbook.class.getProtectionDomain().getCodeSource().getLocation());
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFCell cell;
        // 拿到表格所有标题的value的集合
        Collection<String> c = headersNameMap.values();
        Iterator<String> it = c.iterator();// 表格标题的迭代器
        // 根据选择的字段生成表头
        int size = 0;
        while (it.hasNext()) {
            System.out.println(it);
            String s = it.toString();
            cell = row.createCell(size);
            cell.setCellValue(it.next().toString());
            cell.setCellStyle(style);
            size++;
        }
        Collection<String> zdC = titleFieldMap.values();
        Iterator<T> labIt = new ArrayList<T>().iterator();
        if (dtoList != null) {
            labIt = dtoList.iterator();
        }
        int zdRow = 0;
        while (labIt.hasNext()) {// 记录的迭代器，遍历总记录
            int zdCell = 0;
            zdRow++;
            row = sheet.createRow(zdRow);
            T l = (T) labIt.next();
            List<Field> fields = new ArrayList<>();
            Class<?> tempClass = l.getClass();
            while (tempClass != null) {
                fields.addAll(Arrays.asList(tempClass.getDeclaredFields()));
                tempClass = tempClass.getSuperclass();
            }
            Iterator<String> zdIt = zdC.iterator();
            while (zdIt.hasNext()) {// 遍历要导出的字段集合
                String next = zdIt.next();
                for (short i = 0; i < fields.size(); i++) {// 遍历属性，比对
                    Field field = fields.get(i);
                    String fieldName = field.getName();// 属性名
                    if (next.equals(fieldName)) {
                        String getMethodName = "get"
                                + fieldName.substring(0, 1).toUpperCase()
                                + fieldName.substring(1);// 拿到属性的get方法
                        try {
                            Class<?> cls = l.getClass();
                            Method getMethod = cls.getMethod(getMethodName,
                                    new Class[] {});
                            Object val = getMethod.invoke(l, new Object[] {});
                            String textVal = null;
                            if (val != null) {
                                // 如果是double类型则只保留两位小说
                                if (field.getGenericType().toString()
                                        .equals("double")
                                        || field.getGenericType().toString()
                                        .equals("Double")) {
                                    DecimalFormat df = new DecimalFormat(
                                            "#0.00");
                                    String format = df.format(val);
                                    textVal = String.valueOf(format);
                                } else {
                                    textVal = String.valueOf(val);// 转化成String
                                }
                            } else {
                                textVal = null;
                            }
                            row.createCell( zdCell)
                                    .setCellValue(textVal);
                            zdCell++;
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
        OutputStream out = response.getOutputStream();
        try {
            response.setContentType("application/x-download");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment;fileName="
                    + new String((name).getBytes("gbk"), "iso8859-1")
                    + new SimpleDateFormat("yyyy-MM-dd").format(new Date())
                    + ".xls");
            wb.write(out);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

导出excel的controller  （使用数组封装对应列名需要展示的列的属性名 （二者需要一一对应，顺序不能打乱））

    @ApiOperation("一体化门户数据统计导出")
    @PostMapping("/getIntegratedTwoMapNewExport")
    @OperLog(message = "一体化门户数据统计导出",operation = OperationType.QUERY)
    public void getIntegratedTwoMapNewExport(@RequestBody DataStatisticsParams params , HttpServletResponse response)throws Exception{
        JSONObject resultObj=dataStatisticsService.getIntegratedTwoMapNew(params);
        List<MapExportEntity> list = castMapToBean((List<Map<String,Object>>)resultObj.get("xzqh"), MapExportEntity.class);
        ExportExcelUtil<MapExportEntity> exportExcelUtil = new ExportExcelUtil<>();
        String[] headersName = {"名称","数量","sq数","同比率"};
        String[] headersField = {"name","num","sqcount","amplitude"};
        exportExcelUtil.exportExcel("态势感知", headersName, headersField, list, response,"态势感知");

    }

导入

      读取excel表格数据内容转为list  注意： 实体类的顺序要和excel表格表头的顺序一致 如果实体类的参数比表格表头参数数量多 可以给 abandonFields赋值多的参数数量 来保证excel表头和实体一一对应；

	/**
	 * 读取Excel表数据转为List
	 */
	public List readExcel(MultipartFile file) {
		List resultList = null;
		int abandonFields = 13;
		try {
			String filename = file.getOriginalFilename();
			String postfix = filename.substring(filename.lastIndexOf(".") + 1);
			//根据Excel文件后缀读取数据
			if (postfix.equals("xls")) {
				Map xlsExcelMap = ExcelUtil.readXLSXExcel(file.getInputStream(), StudDogBo.class, abandonFields);
				resultList = (List) xlsExcelMap.get("dataList");
			} else {
				Map xlsxExcelMap = ExcelUtil.readXLSXExcel(file.getInputStream(), StudDogBo.class, abandonFields);
				resultList = (List) xlsxExcelMap.get("dataList");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return resultList;
	}

导入工具类 （我这里示范写的Excel2007版本，03版本修改一下即可）

• HSSF：Excel97-2003版本，扩展名为.xls。一个sheet最大行数65536，最大列数256。
• XSSF：Excel2007版本开始，扩展名为.xlsx。一个sheet最大行数1048576，最大列数16384。

    public static Map readXLSXExcel(InputStream inputStream, Class obj, int abandonFields) {
        Map resultMap = new HashMap();
        List dataList = new ArrayList();
        try {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
            int totalRowNums = sheet.getPhysicalNumberOfRows();
            //从第二行开始读（第一行是表头）
            for (int i = 1; i < totalRowNums ; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    totalRowNums++;
                    continue;
                }
                Object instance = obj.newInstance();
                Field[] fields = obj.getDeclaredFields();
                for (int j = 0; j < abandonFields ; j++) {
                    Field field = fields[j];
                    field.setAccessible(true);
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    String cellValue = getValue(cell);
                    field.set(instance, cellValue);
                }
                dataList.add(instance);
                resultMap.put("dataList", dataList);
                resultMap.put("totalRowNums", totalRowNums);
            }
        } catch (Exception e) {
            throw new SecureException("读取Excel文件异常请检查");
        }
        return resultMap;
    }


	//获取单元格数据
	public static String getValue(Cell cell) {
		// 空白或空
		CellType cellType = cell.getCellTypeEnum();
		if (cell == null || cellType == CellType._NONE
			|| cellType == CellType.BLANK) {
			return "";
		}
		// 布尔值 CELL_TYPE_BOOLEAN
		if (cellType == CellType.BOOLEAN) {
			return cell.getBooleanCellValue() + "";
		}
		//  数字 类型
		if (cellType == CellType.NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				return df.format(date);
			}
			cell.setCellType(CellType.STRING);
			String val = cell.getStringCellValue() + "";
			val = val.toUpperCase();
			if (val.contains("E")) {
				val = val.split("E")[0].replace(".", "");
			}
			return val;
		}
		//  公式 CELL_TYPE_FORMULA
		if (cellType == CellType.FORMULA) {
			return cell.getCellFormula();
		}
		// String类型
		if (cellType == CellType.STRING) {
			String val = cell.getStringCellValue();
			if (val == null || val.trim().length() == 0) {
				return "";
			}
			return val.trim();
		}

		//  错误 CELL_TYPE_ERROR
		if (cellType == CellType.ERROR) {
			return "错误";
		}

		return "";
	}

总结

文章其实还存在个小问题，就是日期信息的导入导出不准确，需要拿到数据的时候时间格式问题需要调整

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date parse = sdf.parse(birthDate);
String format = sdf.format(parse);