excel 简单的导出和导入-xlsx

until

public class ReadExcel {
    private final static String DATE_FORMAT = "yyyy/MM/dd";

    /**
     * @param rowIndex 合并的行
     * */
    public static List<String[]> readXlsx(Workbook workbook,int rowIndex){
        List<String[]> lists = new ArrayList<>();
        Sheet sheetAt = workbook.getSheetAt(0);
        //获取总行数
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i =rowIndex;i<=lastRowNum;i++){
            Row row = sheetAt.getRow(i);
            String[] s=new String[row.getLastCellNum()];
            for (int j=0;j<row.getLastCellNum();j++){
                Cell cell = row.getCell(j);
                s[j]=getCellValue(cell);
            }
            lists.add(s);
        }
        return lists;
    }
    //判断文件的类型
    public static Workbook getworkBook(String fileName, InputStream inputStream) throws IOException {
        Workbook workbook = null;
        if (fileName.endsWith(".xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileName.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }
    //将cell的数据转化为对应的数据
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //如果当前单元格内容为日期类型，需要特殊处理
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        if(dataFormatString.equals("m/d/yy")){
            cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * @param title 文件的标题
     *  @param titles excel的标题
     * @param  s 导出的数据
     * @param rowIndex 合并的行
     * @param colIndex 合并的列
    * */
    public static Workbook writeExcel(String[] titles, String title, List<String[]> s, int rowIndex, int colIndex) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 10); //字体大小
        font.setFontName("新宋体");
        font.setColor(HSSFColor.BLUE.index); //颜色
        font.setBoldweight((short) 0.8);

        // 2.生成样式对象
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFont(font); // 调用字体样式对象
        //设置标题
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue(title);
        //设置样式
        cell.setCellStyle(style);
        //合并行列
        sheet.addMergedRegion(new CellRangeAddress(0, rowIndex, 0, colIndex));

        //创建title
        Row row1 = sheet.createRow(rowIndex+1);
        for (int i = 0;i<titles.length;i++){
            row1.createCell(i).setCellValue(titles[i]);
        }
        for (int m = 0 ;m<s.size();m++){
            Row rowData = sheet.createRow(rowIndex+2+m);
            String[] strings = s.get(m);
            for (int n = 0;n<strings.length;n++){
                rowData.createCell(n).setCellValue(strings[n]);
            }
        }
        return workbook;

    }

    /**
     * 下载文件
     * */
    public static void downLoad(HttpServletResponse response,Workbook workbook) throws Exception {
        //文件下载名称
        String tt="tt.xlsx";
        //设置ContentType
        response.setContentType("application/octet-stream");
        //设置文件名称
        response.setHeader("Content-Disposition", "attachment;filename=" +new String(tt.getBytes("GBK"), "ISO-8859-1") );
//        response.setContentLength(new FileInputStream(source).available());

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();

    }

    /**
     * 获取对象的属性
     * */
    public static <T>  HashMap<String, Method>  getMethod(T t)  {
        Class aClass = t.getClass();
        Field[] fields = t.getClass().getDeclaredFields();
        HashMap<String, Method> map = new HashMap<>();
        try{
            for(int i = 0; i <fields.length;i++){
                Field field = fields[i];
                if(field.getName().equals("id")){
                    continue;
                }
                PropertyDescriptor pd = new PropertyDescriptor(field.getName(), aClass);
                Method getMethod = pd.getReadMethod();//获得get方法
                map.put(field.getName(),getMethod);
//                getMethod.invoke(t);//此处为执行该Object对象的get方法
                Method setMethod = pd.getWriteMethod();//获得set方法
                //setMethod.invoke(obj,"参数");//此处为执行该Object对象的set方法
            }
        }catch (Exception e){
            System.out.println("获取对象属性失败");
        }
        return map;

    }
}

controller

@Controller
public class TestttController {

    @Autowired
    private StudentService studentService;

    @RequestMapping("/test")
    @ResponseBody
    public String t(MultipartFile file, HttpServletResponse response) {
        studentService.exportData(response);
        return "测试成功";
    }

    @RequestMapping("/export")
    @ResponseBody
    public String export(MultipartFile file) {
        studentService.exportExcel(file);
        return "测试成功";
    }
}

service

@Service
public class StudentServiceImpl implements StudentService {
    @Autowired
    private StudentMapper studentMapper;


    @Override
    public List<Student> selectAll(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        List<Student> students = studentMapper.selectList(wrapper);
        return students;
    }

    @Override
    public void exportExcel(MultipartFile file) {
        String originalFilename = file.getOriginalFilename();
        try{
            Workbook workbook = ReadExcel.getworkBook(file.getOriginalFilename(), file.getInputStream());
            List<String[]> strings = ReadExcel.readXlsx(workbook, 3);
            for (String[] s:strings) {
                Student student = new Student();
                student.setName(s[0]);
                student.setAge(s[1]);
                student.setHeight(s[2]);
                student.setWeight(s[3]);
                student.setId(Long.valueOf(IdUntils.getID()));
                studentMapper.insert(student);
            }
        }catch (Exception e){
            System.out.println("读取异常"+e);
        }

    }

    @Override
    public void exportData(HttpServletResponse response) {
        String[] titles={"姓名","年龄","身高","体重"};
        String title="导出";
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        List<Student> students = studentMapper.selectList(wrapper);
        List<String[]> strings=new ArrayList<>();
        for (Student s:students) {
            String[] sts={s.getName(),s.getAge(),s.getHeight(),s.getWeight()};
            strings.add(sts);
        }
        Workbook workbook = ReadExcel.writeExcel(titles, title, strings, 1, 3);
        try{
            ReadExcel.downLoad(response,workbook);
        }catch (Exception e){
            System.out.println("导出失败");
        }

    }
}