excel表格比对的记录

记录一次比对单元格的问题：先把单元格的数据导入到数据库表格里面，然后查询出来去做比较

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>3.14</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-examples</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.10-FINAL</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>

        <!--Apache poi  在word中的表格中插入表格，图片等操作  开始-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>com.deepoove</groupId>
            <artifactId>poi-tl</artifactId>
            <version>1.6.0-beta1</version>
        </dependency>
        <!--结束-->

package com.example.fudemo.exctl;

import com.example.fudemo.mapper.BuipatientinfoDAO;
import com.example.fudemo.mapper.PersonTableMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Member;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;


@RestController
public class Test {

    @Autowired
    BuipatientinfoDAO baseAccessBrandMapper;
    @Autowired

    PersonTableMapper personTableMapper;
    @GetMapping("/excelExport")
    public void test( HttpServletResponse response) throws IOException {
        List<Buipatientinfo> buipatientinfoList = baseAccessBrandMapper.buipatientinfoList();
        List<PersonTable> personTableList = personTableMapper.personTable();
        Map<String, PersonTable> map = new HashMap<>();
        int mapNumber = 0;
        for (PersonTable m1 : personTableList) {
            map.put(m1.getIdentityTypeCode(), m1);
        }
        List<Buipatientinfo>  buipatientinfoList1=new ArrayList<>();
        List<Buipatientinfo>  buipatientinfoList2=new ArrayList<>();
        for (Buipatientinfo m2 : buipatientinfoList) {
                 PersonTable m = map.get(m2.getStudentid());
                if (m != null) {
                    m2.setIslibrary("yes");
                    buipatientinfoList1.add(m2);
                    mapNumber++;
                }else {
                    m2.setIslibrary("no");
                    buipatientinfoList2.add(m2);
                }
        }
        List<Buipatientinfo>  buipatientinfoList3=new ArrayList<>();
        buipatientinfoList3.addAll(buipatientinfoList1);
        buipatientinfoList3.addAll(buipatientinfoList2);
        System.out.println(mapNumber);
        // 表头字段对应的位置（自定义位置）
        Map<String, Integer> position = new HashMap<>();
        // 设置表头字段位置
        position.put("studentid", 0);
        position.put("studentname", 1);
        position.put("traininglevel", 2);
        position.put("trainingmethod", 3);
        position.put("islibrary", 4);
        // 设置表头信息
        Map<String, String> title = new HashMap<>();
        // 设置表头信息
        title.put("studentid", "学生号");
        title.put("studentname", "姓名");
        title.put("traininglevel", "层次");
        title.put("trainingmethod", "方式");
        title.put("islibrary", "是否在库");
        //需要导出的数据
        List<Map<String, Object>> data = new ArrayList<>();
        Map<String, Object> headersMap = null;

        // 遍历模拟的数据填充到headersMap集合
        for (Buipatientinfo singleSampleGeneVo : buipatientinfoList3) {
            headersMap = new HashMap<>();
            headersMap.put("studentid", singleSampleGeneVo.getStudentid());
            headersMap.put("studentname", singleSampleGeneVo.getStudentname());
            headersMap.put("traininglevel", singleSampleGeneVo.getTraininglevel());
            headersMap.put("trainingmethod", singleSampleGeneVo.getTrainingmethod());
            headersMap.put("islibrary", singleSampleGeneVo.getIslibrary());
            // 将headersMap添加到List集合中
            data.add(headersMap);
        }
        DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
        String date = df.format(new Date());
      //获取文件名称
        String excelNameS = "21年" + date+".xls";
        String sheetName ="sheet";
        excelNameS = URLEncoder.encode(excelNameS, "UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + excelNameS);
        response.setContentType("application/x-download");
        exportDataToExcel(title, position, data, sheetName, response.getOutputStream());
    }


    /**
     * 导出列表数据
     *
     * @param title        表头集合
     * @param position     表头字段位置集合
     * @param data         需要导出的数据
     * @param sheetName    导出数据后在excel表格中左下角显示的工作簿名称（注意：不是导出后的文件名）
     * @param outputStream 从controller层通过response获取到的输出流
     */
    public static void exportDataToExcel(Map<String, String> title, Map<String, Integer> position, List<Map<String, Object>> data, String sheetName, OutputStream outputStream) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        try {
            Sheet sheet = workbook.createSheet(sheetName);
            Row header = sheet.createRow(0);
            // 设置表头样式
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 字体样式
            XSSFFont font = ((XSSFWorkbook) workbook).createFont();
            font.setFontName("Arial");
            font.setFontHeightInPoints((short) 14);
            headerStyle.setFont(font);

            int col = 0;
            // 遍历表头map集合
            for (String key : title.keySet()) {
                sheet.setColumnWidth(col, 6000);
                // 设置表格头部
                Cell headerCell = header.createCell(position.get(key));
                headerCell.setCellValue(title.get(key) + "");
                headerCell.setCellStyle(headerStyle);
                col++;
            }
            CellStyle style = workbook.createCellStyle();
            style.setWrapText(true);

            /*
             * 遍历要导出列表的数据data 并与title的key相比较， 确认后插入值
             * 创建列时，根据title的key然后将值插入到对应的列中（position，dataMap，title三个集合的key值是一一对应的）
             */
            if (data != null && data.size() > 0) {
                int r = 0;
                for (Map<String, Object> dataMap : data) {
                    Row row = sheet.createRow(r + 1);
                    for (String dkey : dataMap.keySet()) {
                        for (String key : title.keySet()) {
                            if (key.equals(dkey)) {
                                Cell cell = row.createCell(position.get(key));
                                cell.setCellValue(dataMap.get(dkey) + "");
                                cell.setCellStyle(style);
                                break;
                            }
                        }
                    }
                    r++;
                }
            }
            //使用输出流
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception ex) {
            workbook.close();
        }
    }

//    @PostMapping("/excelExport")
//    public void test(@RequestParam(value="file") MultipartFile file){
//        Map<String,String> res = new HashMap<>();
//        int rowSuccess=0;		//标记成功导入的数据条数
//        List<Buipatientinfo> excelInfo = ReadPatientExcelUtil.getExcelInfo(file);		//调用工具类，传入excel文件，得到数组对象
//        for (Buipatientinfo ec:excelInfo) {
//            baseAccessBrandMapper.insert(ec);
//        }
//    }

}

工具类

package com.example.fudemo.exctl;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;


public class ReadPatientExcelUtil {
    //总行数
    private static int totalRows = 0;
    //总条数
    private static int totalCells = 0;
    //错误信息接收器
    private static String errorMsg;

    /**
     * 读EXCEL文件，获取信息集合
     * @return
     */
    public static List<Buipatientinfo> getExcelInfo(MultipartFile mFile) {
        String fileName = mFile.getOriginalFilename();//获取文件名
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            List<Buipatientinfo> userList = createExcel(mFile.getInputStream(), isExcel2003);
            return userList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 根据excel里面的内容读取客户信息
     * @param is 输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public static List<Buipatientinfo> createExcel(InputStream is, boolean isExcel2003) {
        try{
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            List<Buipatientinfo> userList = readExcelValue(wb);// 读取Excel里面客户的信息
            return userList;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 读取Excel里面客户的信息
     * @param wb
     * @return
     */
    private static List<Buipatientinfo> readExcelValue(Workbook wb) {
        //默认会跳过第一行标题
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<Buipatientinfo> userList = new ArrayList<Buipatientinfo>();
        // 循环Excel行数
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            Buipatientinfo user = new Buipatientinfo();
            // 循环Excel的列
            for (int c = 0; c < totalCells-1; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 0) {           //第一列
                        //如果是纯数字,将单元格类型转为String
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        user.setStudentid(cell.getStringCellValue());//将单元格数据赋值给user
                    }else if (c == 1){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        user.setStudentname(cell.getStringCellValue());
                    }
                    else if (c == 2){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        String stringCellValue = cell.getStringCellValue();
                        user.setTraininglevel(stringCellValue);
                    }
                    else if (c == 3){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        user.setTrainingmethod(cell.getStringCellValue());
                    }
                    else if (c == 4){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        user.setIslibrary(cell.getStringCellValue());
                    }
                }
            }
            // 添加到list
            userList.add(user);
        }
        return userList;
    }
    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }
    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }
    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
}