access数据导出到excel

1.构建ExcelData类

public class ExcelData {
        private XSSFSheet sheet;
        private XSSFWorkbook workbook;

        /**
         * 构造函数，初始化excel数据
         *
         * @param filePath excel路径
         * @param i        sheet
         */
        ExcelData(String filePath, Integer i) {
            FileInputStream fileInputStream = null;
            try {
                fileInputStream = new FileInputStream(filePath);
                XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
                //获取sheet
                workbook = sheets;
                sheet = sheets.getSheetAt(i);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        /**
         * 根据行和列的索引获取单元格的数据
         *
         * @param row
         * @param column
         * @return
         */
        public String getExcelDateByIndex(int row, int column) {
            XSSFRow row1 = sheet.getRow(row);
            String cell = row1.getCell(column).toString();
            return cell;
        }

        /**
         * 根据某一列值为“******”的这一行，来获取该行第x列的值
         *
         * @param caseName
         * @param currentColumn 当前单元格列的索引
         * @param targetColumn  目标单元格列的索引
         * @return
         */
        public String getCellByCaseName(String caseName, int currentColumn, int targetColumn) {
            String operateSteps = "";
            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rows; i++) {
                XSSFRow row = sheet.getRow(i);
                String cell = row.getCell(currentColumn).toString();
                if (cell.equals(caseName)) {
                    operateSteps = row.getCell(targetColumn).toString();
                    break;
                }
            }
            return operateSteps;
        }

        //打印excel数据
        public void readExcelData() {
            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rows; i++) {
                //获取列数
                XSSFRow row = sheet.getRow(i);
                int columns = row.getPhysicalNumberOfCells();
                for (int j = 0; j < columns; j++) {
                    String cell = row.getCell(j).toString();
                    System.out.println(cell);
                }
            }
        }
    }

2.导出测试

@Test
    public void test003() throws IOException {
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            String url = "jdbc:ucanaccess://C:/Users/**/Desktop/db1.mdb";
            Connection con = DriverManager.getConnection(url);//没有用户名和密码的时候直接为空
            Statement sta = con.createStatement();

            String filePath = "E:\\12274_14_12_issue_building.xlsx";
            ExcelData sheet1 = new ExcelData(filePath, 0);

            Font font = sheet1.workbook.createFont();
            font.setFontHeightInPoints((short) 12);
            font.setFontName("Calibri");

            XSSFCellStyle oldStyle = sheet1.workbook.createCellStyle();

            XSSFCellStyle style = sheet1.workbook.createCellStyle();
            style.setFont(font);

            Integer totalRowNum = sheet1.sheet.getPhysicalNumberOfRows();

            ResultSet rst = sta.executeQuery("select * from news where k_res like \"%(12)%\" and k_res like \"2021%\"");
            Integer i = 3;
            while (rst.next()) {
                Row row = sheet1.sheet.createRow(totalRowNum);
                //序号
                Cell zero = row.createCell(0);
                zero.setCellStyle(oldStyle);
                zero.setCellValue(i);
                //todo 业务
                i ++;
                totalRowNum ++;
            }
            OutputStream out = new FileOutputStream(filePath);
            sheet1.workbook.write(out);
            System.out.println("==============");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }