POI实现Excel导入导出

@Controller
@RequestMapping("/poi")
public class POIController {
    @Resource
    private UserService us;
    @Resource
    private MasterService ms;

    public static HSSFSheet setHSSFValidation(HSSFSheet sheet,
                                              String[] textlist, int firstRow, int endRow, int firstCol,
                                              int endCol) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(data_validation_list);
        return sheet;
    }

    public void getFiledsAndTitles(Field[] fields,String[] titles,HSSFRow titleRow){
        for (int i = 0; i < fields.length - 1; i++) {
            Field declaredField = fields[i];
            DefaultTitle annotation = declaredField.getAnnotation(DefaultTitle.class);
            if (annotation != null) {
                String name = annotation.name();
                titles[i] = name;
            }
        }
        for (int i = 0, a = 0; i < titles.length; i++) {
            String title = titles[i];
            if (title != null) {
                HSSFCell cell = titleRow.createCell(a);
                cell.setCellValue(title);
                a++;
            }
        }
    }

    @RequestMapping("/exportTemplate.do")
    public void exportTemplate(HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建表格
        HSSFSheet userSheet = workbook.createSheet("用户信息");
        //冻结首行
        userSheet.createFreezePane( 0, 1, 0, 1 );

        //设置上师列数据约束
        List<Master> masters = ms.queryAllMasters();
        List<String> masterReligious = new ArrayList<>();
        for (Master master : masters) {
            masterReligious.add(master.getMasterReligious());
        }
        //Object[] objects = masterReligious.toArray();
        //String[] masterNames;
        String[] strings = masterReligious.toArray(new String[masterReligious.size()]);
        userSheet = setHSSFValidation(userSheet, strings, 0, 1200, 6, 6);

        //创建标题行数据
        HSSFRow titleRow = userSheet.createRow(0);
        //设置列宽
        userSheet.setColumnWidth(1, 11 * 256);
        userSheet.setColumnWidth(2, 13 * 256);
        userSheet.setColumnWidth(3, 12 * 256);
        userSheet.setColumnWidth(5, 14 * 256);

        // 设置手机号为文本格式
        CellStyle style3 = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        style3.setDataFormat(format.getFormat("@"));

        for (int i = 1; i < 1200; i++) {
            Row rowID = userSheet.createRow(i);
            Cell cell3 = rowID.createCell((short) 3);
            cell3.setCellStyle(style3);
        }

        Field[] declaredFields = User.class.getDeclaredFields();
        Field[] masterFields = Master.class.getDeclaredFields();
        int userLength = declaredFields.length;
        int masterLength = masterFields.length;
        declaredFields = Arrays.copyOf(declaredFields, userLength + masterLength);
        System.arraycopy(masterFields, 0, declaredFields, userLength, masterLength);

        String[] titles = new String[declaredFields.length - 1];

        getFiledsAndTitles(declaredFields,titles,titleRow);

        workbook.write(new File("d:/用户表导入模板.xls"));
        PrintWriter writer = response.getWriter();
        writer.print("ok");
    }
    @RequestMapping("/importUsers")
    public void importExcel(HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("d:/用户表导入模板.xls")));
        HSSFSheet usersheet = workbook.getSheet("用户信息");
        List<User> list = new ArrayList<>();

        //System.out.println(usersheet.getLastRowNum());
        for (int i = 1; i <usersheet.getLastRowNum(); i++) {

            HSSFRow row = usersheet.getRow(i);
            if(row.getCell(0)==null)break;
            User appUser = new User();
            appUser.setUserRealname(row.getCell(0).getStringCellValue());
            appUser.setUserReligious(row.getCell(1).getStringCellValue());
            appUser.setEmail(row.getCell(2).getStringCellValue());
            appUser.setMobile(row.getCell(3).getStringCellValue());
            appUser.setLocation(row.getCell(4).getStringCellValue());
            Date registerStr = row.getCell(5).getDateCellValue();
            appUser.setRegisterDate(registerStr);
            String masterReligious = row.getCell(6).getStringCellValue();
            String masterId = ms.queryKeyByName(masterReligious);
            appUser.setMasterId(masterId);
            list.add(appUser);
        }
        us.batchAdd(list);
        PrintWriter writer = response.getWriter();
        writer.print("ok");
    }
    @RequestMapping("/exportUsers")
    public void exportUsers(HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建表格
        HSSFSheet userSheet = workbook.createSheet("用户信息");
        //冻结第一行
        userSheet.createFreezePane( 0, 1, 0, 1 );
        //创建标题行数据
        HSSFRow titleRow = userSheet.createRow(0);
        userSheet.setColumnWidth(1,11*256);
        userSheet.setColumnWidth(2,13*256);
        userSheet.setColumnWidth(3,12*256);
        userSheet.setColumnWidth(5,14*256);

        Field[] declaredFields = User.class.getDeclaredFields();
        Field[] masterFields = Master.class.getDeclaredFields();
        int userLength = declaredFields.length;
        int masterLength = masterFields.length;
        declaredFields = Arrays.copyOf(declaredFields,userLength+masterLength);
        System.arraycopy(masterFields, 0, declaredFields, userLength, masterLength);

        String[] titles = new String[declaredFields.length-1];
        getFiledsAndTitles(declaredFields,titles,titleRow);
        List<User> list = us.queryAllUsers();
        DataFormat dataFormat = workbook.createDataFormat();
        short format = dataFormat.getFormat("yyyy年MM月dd日");
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(format);
        for (int i = 0; i < list.size(); i++) {
            HSSFRow row = userSheet.createRow(i+1);
            row.createCell(0).setCellValue(list.get(i).getUserRealname());
            row.createCell(1).setCellValue(list.get(i).getUserReligious());
            row.createCell(2).setCellValue(list.get(i).getEmail());
            row.createCell(3).setCellValue(list.get(i).getMobile());
            row.createCell(4).setCellValue(list.get(i).getLocation());
            HSSFCell cell = row.createCell(5);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(list.get(i).getRegisterDate());
            row.createCell(6).setCellValue(list.get(i).getMaster().getMasterReligious());
        }
        workbook.write(new File("d:/用户表导出数据.xls"));
        PrintWriter writer = response.getWriter();
        writer.print("ok");
    }
}