ssm下的Excel文件处理

ssm下的Excel文件处理

3. 拓展：Excel处理

3.1 编写Excel处理的工具类：

import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 处理Excel的工具类
 */
public class ExcelUtil {

    //这是Excel的表头和键的映射关系
    private static final Map<String ,String> map = new HashMap<>();

    /**
     * 获取Excel的映射关系
     * @return
     */
    public static Map getExcelMap(){
        return map;
    }

    /**
     * 通过对象集合导出一个Excel表
     * @param fileName 导出之后的文件名
     * @param sizeOfSheet  一个Sheet存放的最大信息条数
     * @param dataList  集合数据
     * @param os  写入的流
     * @param <T>  集合的泛型
     */
    public static <T>void exportExcel(String fileName,
                                      int sizeOfSheet,
                                      List<T> dataList, 
                                      OutputStream os) {
        if (map.size()==0){
            System.out.println("没有设置映射关系");
            return;
        }
        //判断文件名是否复合Excel文件命名
        boolean xls = fileName.endsWith("xls");
        try {
            //利用工作簿工厂创建一个工作簿
            Workbook workbook = WorkbookFactory.create(xls);
            //根据数据条数计算出需要创建sheet数量
            int numberOfSheets;
            if(dataList.size() % sizeOfSheet == 0){
                numberOfSheets = dataList.size() / sizeOfSheet; //计算sheet数量
            } else {
                numberOfSheets = dataList.size() / sizeOfSheet + 1;//计算sheet数量
            }
            //通过循环在工作簿中创建sheet表，并且
            for (int number = 0;number<numberOfSheets;number++){
                //首先创建sheet(设置sheet的名称为sheet1~sheetN)
                Sheet sheet = workbook.createSheet("sheet"+number);
                //在sheet中创建第0行。
                Row row = sheet.createRow(0);
                //这个Map就是用来记录列号对应的属性名
                Map<Integer,String> columnsMap = new HashMap<>();
                int index = 0; //这个index的值走到最后就是最大的列数
                //在0行放置列的属性名
                for(String title: map.keySet()){
                    columnsMap.put(index, map.get(title));
                    Cell cell = row.createCell(index++);
                    cell.setCellValue(title);
                }
                //设置第一行开始写数据
                int rowIndex = 1;
                //计算当前sheet存储的数据在集合中的开始位置
                int start = number * sizeOfSheet;
                //计算当前sheet存储的数据在集合中的结束位置
                int end = (number + 1) * sizeOfSheet;

                if(end > dataList.size()) //校验最大值，不能超过集合的大小
                    end = dataList.size();

                for(int k=start; k<end; k++){
                    Row dataRow = sheet.createRow(rowIndex++);
                    T t = dataList.get(k);
                    Class<?> clazz = t.getClass();
                    for(int i=0; i<index; i++){//这个循环的目的就是为了保持与表头的列数量一致
                        Cell cell = dataRow.createCell(i); //创建数据行的单元格
                        String prop = columnsMap.get(i); //取到该列使用的属性名
                        String methodName = "get" + prop.substring(0,1).toUpperCase() + prop.substring(1);
                        Method method = clazz.getMethod(methodName);
                        Object value = method.invoke(t);
                        cell.setCellValue(value.toString());
                    }
                }
            }
            workbook.write(os);
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 通过Excel表获取到对象的集合
     * @param is  读取Excel表的流
     * @param clazz 集合对象的字节码文件对象
     * @param <T> 集合的泛型
     * @return
     */
    public static <T> List<T> importExcel(InputStream is, Class<T> clazz){
        if (map.size()==0){
            System.out.println("没有设置映射关系");
            return null;
        }
        List<T> dataList = new ArrayList<>();
        try {
            //针对给定的excel文件创建一个对应的工作簿对象，这个工作簿对象中就已经包含有数据了
            Workbook workbook = WorkbookFactory.create(is);
            //不知道在哪个sheet中存放数据，因此需要一个一个的sheet查看
            int numberOfSheets = workbook.getNumberOfSheets(); //获取sheet数量
            for(int i=0; i<numberOfSheets; i++){
                Sheet sheet = workbook.getSheetAt(i); //获取给定位置的sheet
                int rows = sheet.getPhysicalNumberOfRows();//获取实际使用的行数
                if(rows <= 1) continue; //只有1行或者1行都没有 表示没有数据，直接跳过该sheet
                Row titleRow = sheet.getRow(0); //第一行表示标题行
                int cells = titleRow.getPhysicalNumberOfCells();//获取标题行实际使用的列数
                Map<Integer, String> columnsMap = new HashMap<>(); //存储单元格下标与标题
                for(int j=0; j<cells; j++){
                    Cell cell = titleRow.getCell(j); //获取给定位置的单元格
                    String title = cell.getStringCellValue(); //获取单元格内容，这里是获取字符串形式的内容
                    columnsMap.put(j, title);
                }
                //开始解析数据行
                for(int row=1; row<rows; row++){
                    T t = clazz.newInstance();
                    Row dataRow = sheet.getRow(row); //获取给定位置的数据行
                    for(int n=0; n<cells; n++){//循环行内的单元格
                        Cell cell = dataRow.getCell(n); //获取给定的位置的单元格
                        Object cellValue = null; //单元格存储内容
                        switch (cell.getCellType()){//对每一种单元格类型做出处理
                            case STRING:
                                cellValue = cell.getStringCellValue(); break;
                            case BOOLEAN:
                                cellValue = cell.getBooleanCellValue(); break;
                            case NUMERIC: //数字
                                //POI提供的工具类DateUtil可以用来判断一个单元格的类型是否是日期格式
                                if(DateUtil.isCellDateFormatted(cell)){
                                    cellValue = cell.getDateCellValue();
                                } else {
                                    cellValue = cell.getNumericCellValue();
                                }
                                break;
                            case BLANK: //空白
                                cellValue = "";
                                break;
                        }
                        String title = columnsMap.get(n); //获取该列的标题
                        String prop = map.get(title); //获取标题对应的属性
                        Field field = clazz.getDeclaredField(prop);//得到类中定义的属性字段
                        Class<?> fieldType = field.getType(); //获取字段类型
                        field.setAccessible(true);
                        field.set(t, getObject(cellValue, fieldType));
                    }
                    dataList.add(t);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataList;
    }

    /**
     * 判断
     * @param cellValue
     * @param fieldType
     * @return
     * @throws ParseException
     */
    private static Object getObject(Object cellValue, Class<?> fieldType) throws ParseException {
        if(cellValue == null) return null;
        if(fieldType == String.class){
            cellValue = cellValue.toString();
        } else if(fieldType == int.class || fieldType == Integer.class){
            cellValue = (int)Double.parseDouble(cellValue.toString());
        } else if(fieldType == short.class || fieldType == Short.class){
            cellValue = (short)Double.parseDouble(cellValue.toString());
        } else if(fieldType == byte.class || fieldType == Byte.class){
            cellValue = (byte)Double.parseDouble(cellValue.toString());
        } else if(fieldType == long.class || fieldType == Long.class){
            cellValue = (long)Double.parseDouble(cellValue.toString());
        } else if(fieldType == float.class || fieldType == Float.class){
            cellValue = Float.parseFloat(cellValue.toString());
        } else if(fieldType == double.class || fieldType == Double.class){
            cellValue = Double.parseDouble(cellValue.toString());
        } else if(fieldType == boolean.class || fieldType == Boolean.class){
            cellValue = Boolean.parseBoolean(cellValue.toString());
        } else if(fieldType == char.class || fieldType == Character.class){
            cellValue = cellValue.toString().charAt(0);
        } else if(fieldType == Date.class){
            cellValue = new SimpleDateFormat("yyyy/MM/dd").parse(cellValue.toString());
        } else if(fieldType == java.sql.Date.class){
            Date date = new SimpleDateFormat("yyyy/MM/dd").parse(cellValue.toString());
            cellValue = new java.sql.Date(date.getTime());
        } else if(fieldType == Timestamp.class){
            Date date = new SimpleDateFormat("yyyy/MM/dd").parse(cellValue.toString());
            cellValue = new Timestamp(date.getTime());
        }
        return cellValue;
    }
}

3.2 测试方法：

@Test
public void test() throws SQLException {
    //1. 必须先设置对象和Excel表的映射关系，key为Excel表中的表头，value为对应属性名。
    Map excelMap = ExcelUtil.getExcelMap();
    excelMap.put("小区名","name");
    excelMap.put("小区地址","address");
    excelMap.put("小区面积","area");
    excelMap.put("小区绿化","green");
    excelMap.put("小区物业","property");
    
    //2.1 对集合生成Excel表进行测试
    //2.1.1 获取需要生成Excel表的集合（这是通过持久层方法从数据库中获取的小区对象信息的集合）
    List<Community> list = communityDao.showCommunities(new CommunityCondition(null,null,1,10));
    //2.1.2 将调用方法传递参数
    try {
        ExcelUtil.exportExcel("D:\\Excel\\1.xls",10,list,new FileOutputStream("D:\\Excel\\1.xls"));
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    
    //2.2 对Excel表生成集合对象进行测试
    try {
        //调用方法返回一个对应泛型的集合
        List<Community> communities = ExcelUtil.importExcel(new FileInputStream("D:\\Excel\\1.xls"), Community.class);
        communities.forEach(System.out::println);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
}

3.3 在ssm中的运用

3.3.1 示例：下载小区信息

点击下载小区信息，实现小区信息从数据库中读取并写入Excel文件。

注：下面的代码最好写在service层中，返回一个data数据即可，这里只是简单演示

@GetMapping("/downloadExcel")
private ResponseEntity<byte[]> downloadExcel(@RequestHeader("User-Agent") String userAgent) throws IOException {
    List<CommunityModel> list = communityService.getCommunities("小区");
    Map excelMap = ExcelUtil.getExcelMap();
    excelMap.put("ID","id");
    excelMap.put("小区名","name");
    //这里可以从配置文件中读取，也可以自己定义。
    String fileName = "D:\\Excel\\2.xls";
    
    File downloadFile = new File(fileName);
    FileOutputStream fileOutputStream = new FileOutputStream(downloadFile);
    ExcelUtil.exportExcel(fileName,20,list,fileOutputStream);

    byte[] data = FileUtils.readFileToByteArray(downloadFile);
    //解决ie下载时文件名乱码的问题
    userAgent = userAgent.toUpperCase();
    //判断是否是ie浏览器
    if (userAgent.contains("MSIE") || userAgent.contains("TRIDENT") || userAgent.contains("EDGE")) {
        //IE下载文件名乱码可以直接通过URL编码解决
        fileName = URLEncoder.encode(fileName, "UTF-8"); //StandardCharsets.UTF_8.name();
    }else{
        //其他浏览器需要使用转码进行解决
        fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
    }
    HttpHeaders headers = new HttpHeaders();
    headers.add("Content-Type", MediaType.APPLICATION_OCTET_STREAM_VALUE);
    headers.add("Content-Disposition", "attachment; filename=" + fileName);
    return new ResponseEntity<>(data, headers, HttpStatus.OK);

}

3.3.2 示例：导入小区信息

controller层：

@PostMapping("/import")
public int importCommunities(@RequestPart(name = "file") MultipartFile file){
    Map excelMap = ExcelUtil.getExcelMap();
    excelMap.put("小区名","name");
    excelMap.put("小区地址","address");
    excelMap.put("小区面积","area");
    excelMap.put("小区绿化","green");
    excelMap.put("小区物业","property");
    try {
        InputStream inputStream = file.getInputStream();
        List<Community> communities = ExcelUtil.importExcel(inputStream, Community.class);
        int result = communityService.importCommunity(communities);
        return result;
    } catch (IOException e) {
        e.printStackTrace();
    }
    return -1;
}

service层：

@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ, rollbackFor = Exception.class)
@Override
public int importCommunity(List<Community> communities) {//思考，集合过大，怎么操作？
    //假设一次导入1000条
    int times = communities.size() % 1000 == 0 ? communities.size() / 1000 : communities.size() / 1000 + 1;
    int result = 0;
    for(int i=0; i<times; i++){
        int start = i * 1000;
        int end = (i + 1) * 1000;
        if(end > communities.size())
            end = communities.size();
        List<Community> subList = communities.subList(start, end);
        result += communityMapper.importCommunity(subList);
    }
    return result == communities.size() ? 1 : 0;
}