如何将动态表数据转储到excel sheet springboot中

gzszwxb4  于 2023-05-06  发布在  Spring
关注(0)|答案(5)|浏览(105)

`//如何将动态表数据转储到excel sheet springboot的示例代码//如何将动态表数据转储到excel sheet springboot //如何将动态表数据转储到excel sheet springboot

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
@RequestMapping("/excel")
public class ExcelController {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @GetMapping("/download")
    public ModelAndView downloadExcel(HttpServletResponse response) throws IOException, SQLException {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=table-data.xlsx");
        
        XSSFWorkbook workbook = new XSSFWorkbook();
        
        int sheetNumber = 1;
        int rowNumber = 0;
        int rowPerPage = 10000; // maximum number of rows per sheet
        
        List<String> columnNames = getColumnNames();
        
        XSSFSheet sheet = workbook.createSheet("Sheet " + sheetNumber);
        createHeaderRow(sheet, columnNames);
        
        ResultSet resultSet = getResultSet();
        while (resultSet.next()) {
            if (rowNumber >= rowPerPage) {
                sheetNumber++;
                sheet = workbook.createSheet("Sheet " + sheetNumber);
                createHeaderRow(sheet, columnNames);
                rowNumber = 0;
            }
            
            List<Object> rowValues = getRowValues(resultSet);
            createDataRow(sheet, rowNumber, rowValues);
            
            rowNumber++;
        }
        
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        
        return new ModelAndView("excelView", "excelData", baos.toByteArray());
    }
    
    private List<String> getColumnNames() {
        List<String> columnNames = new ArrayList<>();
        jdbcTemplate.query("SELECT column_name FROM information_schema.columns WHERE table_name = 'my_table'", (rs, rowNum) -> {
            columnNames.add(rs.getString("column_name"));
            return null;
        });
        return columnNames;
    }
    
    private ResultSet getResultSet() throws SQLException {
        return jdbcTemplate.getDataSource().getConnection().createStatement().executeQuery("SELECT * FROM my_table");
    }
    
    private List<Object> getRowValues(ResultSet resultSet) throws SQLException {
        List<Object> rowValues = new ArrayList<>();
        int columnCount = resultSet.getMetaData().getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            rowValues.add(resultSet.getObject(i));
        }
        return rowValues;
    }
    
    private void createHeaderRow(XSSFSheet sheet, List<String> columnNames) {
        int cellNumber = 0;
        for (String columnName : columnNames) {
            sheet.createRow(0).createCell(cellNumber).setCellValue(columnName);
            cellNumber++;
        }
    }
    
    private void createDataRow(XSSFSheet sheet, int rowNumber, List<Object> rowValues) {
        int cellNumber = 0;
        for (Object value : rowValues) {
            sheet.createRow(rowNumber + 1).createCell(cellNumber).setCellValue(value.toString());
            cellNumber++;
        }
    }
} //here

` ``//如何将动态表数据转储到excel sheet springboot的示例代码//如何将动态表数据转储到excel sheet springboot //如何将动态表数据转储到excel sheet springboot

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
@RequestMapping("/excel")
public class ExcelController {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @GetMapping("/download")
    public ModelAndView downloadExcel(HttpServletResponse response) throws IOException, SQLException {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=table-data.xlsx");
        
        XSSFWorkbook workbook = new XSSFWorkbook();
        
        int sheetNumber = 1;
        int rowNumber = 0;
        int rowPerPage = 10000; // maximum number of rows per sheet
        
        List<String> columnNames = getColumnNames();
        
        XSSFSheet sheet = workbook.createSheet("Sheet " + sheetNumber);
        createHeaderRow(sheet, columnNames);
        
        ResultSet resultSet = getResultSet();
        while (resultSet.next()) {
            if (rowNumber >= rowPerPage) {
                sheetNumber++;
                sheet = workbook.createSheet("Sheet " + sheetNumber);
                createHeaderRow(sheet, columnNames);
                rowNumber = 0;
            }
            
            List<Object> rowValues = getRowValues(resultSet);
            createDataRow(sheet, rowNumber, rowValues);
            
            rowNumber++;
        }
        
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        
        return new ModelAndView("excelView", "excelData", baos.toByteArray());
    }
    
    private List<String> getColumnNames() {
        List<String> columnNames = new ArrayList<>();
        jdbcTemplate.query("SELECT column_name FROM information_schema.columns WHERE table_name = 'my_table'", (rs, rowNum) -> {
            columnNames.add(rs.getString("column_name"));
            return null;
        });
        return columnNames;
    }
    
    private ResultSet getResultSet() throws SQLException {
        return jdbcTemplate.getDataSource().getConnection().createStatement().executeQuery("SELECT * FROM my_table");
    }
    
    private List<Object> getRowValues(ResultSet resultSet) throws SQLException {
        List<Object> rowValues = new ArrayList<>();
        int columnCount = resultSet.getMetaData().getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            rowValues.add(resultSet.getObject(i));
        }
        return rowValues;
    }
    
    private void createHeaderRow(XSSFSheet sheet, List<String> columnNames) {
        int cellNumber = 0;
        for (String columnName : columnNames) {
            sheet.createRow(0).createCell(cellNumber).setCellValue(columnName);
            cellNumber++;
        }
    }
    
    private void createDataRow(XSSFSheet sheet, int rowNumber, List<Object> rowValues) {
        int cellNumber = 0;
        for (Object value : rowValues) {
            sheet.createRow(rowNumber + 1).createCell(cellNumber).setCellValue(value.toString());
            cellNumber++;
        }
    }
} //here

``

spring-boot

5条答案

按热度按时间
yuvru6vn

yuvru6vn1#

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
@RequestMapping("/excel")
public class ExcelController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/download")
    public ModelAndView downloadExcel(HttpServletResponse response) throws IOException, SQLException {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=tables-data.xlsx");

        XSSFWorkbook workbook = new XSSFWorkbook();

        List<String> tableNames = getTableNames();
        for (String tableName : tableNames) {
            int rowNumber = 0;
            int rowPerPage = 10000; // maximum number of rows per sheet

            List<String> columnNames = getColumnNames(tableName);

            XSSFSheet sheet = workbook.createSheet(tableName);
            createHeaderRow(sheet, columnNames);

            ResultSet resultSet = getResultSet(tableName);
            while (resultSet.next()) {
                if (rowNumber >= rowPerPage) {
                    sheet = workbook.createSheet(tableName + " cont.");
                    createHeaderRow(sheet, columnNames);
                    rowNumber = 0;
                }

                List<Object> rowValues = getRowValues(resultSet, columnNames);
                createDataRow(sheet, rowNumber, rowValues);

                rowNumber++;
            }
        }

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);

        compare worksheets data
        boolean areEqual = compareWorksheetsData(workbook);

        return new ModelAndView("excelView", "excelData", baos.toByteArray())
                .addObject("worksheetsAreEqual", areEqual);
    }

    private List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        jdbcTemplate.query("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'", (rs, rowNum) -> {
            tableNames.add(rs.getString("table_name"));
            return null;
        });
        return tableNames;
    }

    private List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        jdbcTemplate.query("SELECT column_name FROM information_schema.columns WHERE table_name = ?",
                new Object[] { tableName }, (rs, rowNum) -> {
                    columnNames.add(rs.getString("column_name"));
                    return null;
                });
        return columnNames;
    }

    private ResultSet getResultSet(String tableName) throws SQLException {
        return jdbcTemplate.getDataSource().getConnection().createStatement()
                .executeQuery(String.format("SELECT * FROM %s", tableName));
    }

    private List<Object> getRowValues(ResultSet resultSet, List<String> columnNames) throws SQLException {
        List<Object> rowValues = new ArrayList<>();
        for (String columnName : columnNames) {
            rowValues.add(resultSet.getObject(columnName));
        }
        return rowValues;
    }

    private void createHeaderRow(XSSFSheet sheet, List<String> columnNames) {
        int cellNumber = 0;
        for (String columnName : columnNames) {
            sheet.createRow(0).createCell(cellNumber).setCellValue(columnName);
            cellNumber++;
        }
   
    }
}```
赞(0）分享  回复(0）举报  2023-05-06
4xy9mtcn

4xy9mtcn2#

// Code 1



import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
@RequestMapping("/excel")
public class ExcelController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/download")
    public ModelAndView downloadExcel(HttpServletResponse response) throws IOException, SQLException {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=tables-data.xlsx");

        XSSFWorkbook workbook = new XSSFWorkbook();

        List<String> tableNames = getTableNames();
        for (String tableName : tableNames) {
            int rowNumber = 0;
            int rowPerPage = 10000; // maximum number of rows per sheet

            List<String> columnNames = getColumnNames(tableName);

            XSSFSheet sheet = workbook.createSheet(tableName);
            createHeaderRow(sheet, columnNames);

            ResultSet resultSet = getResultSet(tableName);
            while (resultSet.next()) {
                if (rowNumber >= rowPerPage) {
                    sheet = workbook.createSheet(tableName + " cont.");
                    createHeaderRow(sheet, columnNames);
                    rowNumber = 0;
                }

                List<Object> rowValues = getRowValues(resultSet, columnNames);
                createDataRow(sheet, rowNumber, rowValues);

                rowNumber++;
            }
        }

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);

        // compare worksheets data
        boolean areEqual = compareWorksheetsData(workbook);

        return new ModelAndView("excelView", "excelData", baos.toByteArray())
                .addObject("worksheetsAreEqual", areEqual);
    }

    private List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        jdbcTemplate.query("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'", (rs, rowNum) -> {
            tableNames.add(rs.getString("table_name"));
            return null;
        });
        return tableNames;
    }

    private List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        jdbcTemplate.query("SELECT column_name FROM information_schema.columns WHERE table_name = ?",
                new Object[] { tableName }, (rs, rowNum) -> {
                    columnNames.add(rs.getString("column_name"));
                    return null;
                });
        return columnNames;
    }

    private ResultSet getResultSet(String tableName) throws SQLException {
        return jdbcTemplate.getDataSource().getConnection().createStatement()
                .executeQuery(String.format("SELECT * FROM %s", tableName));
    }

    private List<Object> getRowValues(ResultSet resultSet, List<String> columnNames) throws SQLException {
        List<Object> rowValues = new ArrayList<>();
        for (String columnName : columnNames) {
            rowValues.add(resultSet.getObject(columnName));
        }
        return rowValues;
    }

    private void createHeaderRow(XSSFSheet sheet, List<String> columnNames) {
        int cellNumber = 0;
        for (String columnName : columnNames) {
            sheet.createRow(0).createCell(cellNumber).setCellValue(columnName);
            cellNumber++;
        }
   
}}

// Code 2



@Service
public class DatabaseComparisonService {

    @Autowired
    private JdbcTemplate jdbcTemplate1;

    @Autowired
    private JdbcTemplate jdbcTemplate2;

    public void compareAndExportToExcel() {
        // Retrieve data from first table
        List<MyModelClass> results1 = jdbcTemplate1.query("SELECT * FROM my_table_1", new MyModelClassRowMapper());
        Set<MyModelClass> resultSet1 = new HashSet<>(results1);

        // Retrieve data from second table
        List<MyModelClass> results2 = jdbcTemplate2.query("SELECT * FROM my_table_2", new MyModelClassRowMapper());
        Set<MyModelClass> resultSet2 = new HashSet<>(results2);

        // Find similarities and differences
        Set<MyModelClass> similarities = new HashSet<>(resultSet1);
        similarities.retainAll(resultSet2);

        Set<MyModelClass> differences = new HashSet<>(resultSet1);
        differences.addAll(resultSet2);
        differences.removeAll(similarities);

        // Create Excel workbook and sheet
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Similarities and Differences");

        // Add header row
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Column 1");
        headerRow.createCell(1).setCellValue("Column 2");
        // Add more columns as needed

        // Add data rows for similarities
        int rowNum = 1;
        for (MyModelClass similarity : similarities) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(similarity.getColumn1());
            row.createCell(1).setCellValue(similarity.getColumn2());
            // Add more columns as needed
        }

        // Add empty row to separate similarities and differences
        Row emptyRow = sheet.createRow(rowNum++);

        // Add data rows for differences
        for (MyModelClass difference : differences) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(difference.getColumn1());
            row.createCell(1).setCellValue(difference.getColumn2());
            // Add more columns as needed
        }

        // Export to Excel file
        try {
            FileOutputStream outputStream = new FileOutputStream("similarities_and_differences.xlsx");
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static class MyModelClassRowMapper implements RowMapper<MyModelClass> {
        @Override
        public MyModelClass mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            MyModelClass myModelClass = new MyModelClass();
            myModelClass.setColumn1(resultSet.getString("column1"));
            myModelClass.setColumn2(resultSet.getString("column2"));
            // Set more columns as needed
            return myModelClass;
        }
    }

}

// Code 3

@Service
public class DatabaseComparisonService {

    @Autowired
    private JdbcTemplate jdbcTemplate1;

    @Autowired
    private JdbcTemplate jdbcTemplate2;

    public void compareAndExportToExcel() {
        // Retrieve data from first table
        List<MyModelClass> results1 = jdbcTemplate1.query("SELECT * FROM my_table_1", new MyModelClassRowMapper());
        Set<MyModelClass> resultSet1 = new HashSet<>(results1);

        // Retrieve data from second table
        List<MyModelClass> results2 = jdbcTemplate2.query("SELECT * FROM my_table_2", new MyModelClassRowMapper());
        Set<MyModelClass> resultSet2 = new HashSet<>(results2);

        // Find similarities and differences
        Set<MyModelClass> similarities = new HashSet<>(resultSet1);
        similarities.retainAll(resultSet2);

        Set<MyModelClass> differences = new HashSet<>(resultSet1);
        differences.addAll(resultSet2);
        differences.removeAll(similarities);

        // Create Excel workbook and sheet
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Similarities and Differences");

        // Add header row
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Column 1");
        headerRow.createCell(1).setCellValue("Column 2");
        // Add more columns as needed

        // Add data rows for similarities
        int rowNum = 1;
        for (MyModelClass similarity : similarities) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(similarity.getColumn1());
            row.createCell(1).setCellValue(similarity.getColumn2());
            // Add more columns as needed
        }

        // Add empty row to separate similarities and differences
        Row emptyRow = sheet.createRow(rowNum++);

        // Add data rows for differences
        for (MyModelClass difference : differences) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(difference.getColumn1());
            row.createCell(1).setCellValue(difference.getColumn2());
            // Add more columns as needed
        }

        // Export to Excel file
        try {
            FileOutputStream outputStream = new FileOutputStream("similarities_and_differences.xlsx");
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static class MyModelClassRowMapper implements RowMapper<MyModelClass> {
        @Override
        public MyModelClass mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            MyModelClass myModelClass = new MyModelClass();
            myModelClass.setColumn1(resultSet.getString("column1"));
            myModelClass.setColumn2(resultSet.getString("column2"));
            // Set more columns as needed
            return myModelClass;
        }
    }

}

// Code 4


@Service
public class DatabaseComparisonService {

    @Autowired
    private JdbcTemplate jdbcTemplate1;

    @Autowired
    private JdbcTemplate jdbcTemplate2;

    public void compareAndExportToExcel() {
        // Retrieve data from first table
        List<MyModelClass> results1 = jdbcTemplate1.query("SELECT * FROM my_table_1", new MyModelClassRowMapper());
        Set<MyModelClass> resultSet1 = new HashSet<>(results1);

        // Retrieve data from second table
        List<MyModelClass> results2 = jdbcTemplate2.query("SELECT * FROM my_table_2", new MyModelClassRowMapper());
        Set<MyModelClass> resultSet2 = new HashSet<>(results2);

        // Find similarities and differences
        Set<MyModelClass> similarities = new HashSet<>(resultSet1);
        similarities.retainAll(resultSet2);

        Set<MyModelClass> differences = new HashSet<>(resultSet1);
        differences.addAll(resultSet2);
        differences.removeAll(similarities);

        // Create Excel workbook and sheet
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Similarities and Differences");

        // Add header row
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Column 1");
        headerRow.createCell(1).setCellValue("Column 2");
        // Add more columns as needed

        // Add data rows for similarities
        int rowNum = 1;
        for (MyModelClass similarity : similarities) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(similarity.getColumn1());
            row.createCell(1).setCellValue(similarity.getColumn2());
            // Add more columns as needed
        }

        // Add empty row to separate similarities and differences
        Row emptyRow = sheet.createRow(rowNum++);

        // Add data rows for differences
        for (MyModelClass difference : differences) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(difference.getColumn1());
            row.createCell(1).setCellValue(difference.getColumn2());
            // Add more columns as needed
        }

        // Export to Excel file
        String folderPath = "C:/my-folder";
        String filePath = folderPath + "/similarities_and_differences.xlsx";
        try {
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static class MyModelClassRowMapper implements RowMapper<MyModelClass> {
        @Override
        public MyModelClass mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            MyModelClass myModelClass = new MyModelClass();
            myModelClass.setColumn1(resultSet.getString("column1"));
            myModelClass.setColumn2(resultSet.getString("column2"));
            // Set more columns as needed
            return myModelClass;
        }
    }

}
赞(0）分享  回复(0）举报  2023-05-06
hi3rlvi2

hi3rlvi23#

//Code 1

@Service
public class DatabaseComparisonService {

    @Autowired
    private JdbcTemplate jdbcTemplate1;

    @Autowired
    private JdbcTemplate jdbcTemplate2;

    public void compareAndExportToExcel(String query1, String query2, String[] columnNames, String folderPath, String fileName) {
        // Retrieve data from first table
        List<Map<String, Object>> results1 = jdbcTemplate1.queryForList(query1);
        Set<Map<String, Object>> resultSet1 = new HashSet<>(results1);

        // Retrieve data from second table
        List<Map<String, Object>> results2 = jdbcTemplate2.queryForList(query2);
        Set<Map<String, Object>> resultSet2 = new HashSet<>(results2);

        // Find similarities and differences
        Set<Map<String, Object>> similarities = new HashSet<>(resultSet1);
        similarities.retainAll(resultSet2);

        Set<Map<String, Object>> differences = new HashSet<>(resultSet1);
        differences.addAll(resultSet2);
        differences.removeAll(similarities);

        // Create Excel workbook and sheet
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Similarities and Differences");

        // Add header row
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < columnNames.length; i++) {
            headerRow.createCell(i).setCellValue(columnNames[i]);
        }

        // Add data rows for similarities
        int rowNum = 1;
        for (Map<String, Object> similarity : similarities) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 0; i < columnNames.length; i++) {
                row.createCell(i).setCellValue(String.valueOf(similarity.get(columnNames[i])));
            }
        }

        // Add empty row to separate similarities and differences
        Row emptyRow = sheet.createRow(rowNum++);

        // Add data rows for differences
        for (Map<String, Object> difference : differences) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 0; i < columnNames.length; i++) {
                row.createCell(i).setCellValue(String.valueOf(difference.get(columnNames[i])));
            }
        }

        // Export to Excel file
        String filePath = folderPath + "/" + fileName + ".xlsx";
        try {
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

//Code 2

@Service
public class DatabaseComparisonService {

    @Autowired
    private JdbcTemplate jdbcTemplate1;

    @Autowired
    private JdbcTemplate jdbcTemplate2;

    public void compareAndExportToExcel(String query1, String query2, String[] columnNames, String folderPath, String fileName) {
        // Retrieve data from first table
        List<Map<String, Object>> results1 = jdbcTemplate1.queryForList(query1);
        Set<Map<String, Object>> resultSet1 = new HashSet<>(results1);

        // Retrieve data from second table
        List<Map<String, Object>> results2 = jdbcTemplate2.queryForList(query2);
        Set<Map<String, Object>> resultSet2 = new HashSet<>(results2);

        // Find similarities and differences
        Set<Map<String, Object>> similarities = new HashSet<>(resultSet1);
        similarities.retainAll(resultSet2);

        Set<Map<String, Object>> differences1 = new HashSet<>(resultSet1);
        differences1.removeAll(similarities);

        Set<Map<String, Object>> differences2 = new HashSet<>(resultSet2);
        differences2.removeAll(similarities);

        // Create Excel workbook and sheet
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Similarities and Differences");

        // Add header row
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < columnNames.length; i++) {
            headerRow.createCell(i).setCellValue(columnNames[i]);
        }

        // Add data rows for similarities
        int rowNum = 1;
        for (Map<String, Object> similarity : similarities) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 0; i < columnNames.length; i++) {
                row.createCell(i).setCellValue(String.valueOf(similarity.get(columnNames[i])));
            }
        }

        // Add empty row to separate similarities and differences
        Row emptyRow = sheet.createRow(rowNum++);

        // Add data rows for differences in table 1
        for (Map<String, Object> difference1 : differences1) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 0; i < columnNames.length; i++) {
                row.createCell(i).setCellValue(String.valueOf(difference1.get(columnNames[i])));
            }
        }

        // Add empty row to separate differences in table 1 and table 2
        emptyRow = sheet.createRow(rowNum++);

        // Add data rows for differences in table 2
        for (Map<String, Object> difference2 : differences2) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 0; i < columnNames.length; i++) {
                row.createCell(i).setCellValue(String.valueOf(difference2.get(columnNames[i])));
            }
        }

        // Export to Excel file
        String filePath = folderPath + "/" + fileName + ".xlsx";
        try {
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}


// Code 3

@Service
public class DatabaseComparisonService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void compareAndExportToExcel(String query1, String query2, String[] columnNames,
                                         String folderPath, String fileName) {
        // Retrieve data from first table
        List<Map<String, Object>> results1 = jdbcTemplate.queryForList(query1);
        Set<Map<String, Object>> resultSet1 = new HashSet<>(results1);

        // Retrieve data from second table
        List<Map<String, Object>> results2 = jdbcTemplate.queryForList(query2);
        Set<Map<String, Object>> resultSet2 = new HashSet<>(results2);

        // Find similarities and differences
        Set<Map<String, Object>> similarities = new HashSet<>(resultSet1);
        similarities.retainAll(resultSet2);

        Set<Map<String, Object>> differences = new HashSet<>(resultSet1);
        differences.addAll(resultSet2);
        differences.removeAll(similarities);

        // Create Excel workbook and sheet
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Similarities and Differences");

        // Add header row
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < columnNames.length; i++) {
            headerRow.createCell(i).setCellValue(columnNames[i]);
        }

        // Add data rows for similarities
        int rowNum = 1;
        for (Map<String, Object> similarity : similarities) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 0; i < columnNames.length; i++) {
                row.createCell(i).setCellValue(String.valueOf(similarity.get(columnNames[i])));
            }
        }

        // Add empty row to separate similarities and differences
        Row emptyRow = sheet.createRow(rowNum++);

        // Add data rows for differences
        for (Map<String, Object> difference : differences) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 0; i < columnNames.length; i++) {
                row.createCell(i).setCellValue(String.valueOf(difference.get(columnNames[i])));
            }
        }

        // Export to Excel file
        String filePath = folderPath + "/" + fileName + ".xlsx";
        try {
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}



@Autowired
private DatabaseComparisonService databaseComparisonService;

public void compareTables() {
    String query1 = "SELECT * FROM table1";
    String query2 = "SELECT * FROM table2";
    String[] columnNames = {"id", "name", "description"};

    databaseComparisonService.compareAndExportToExcel(query1, query2, columnNames,
            "/path/to/folder", "comparison_result");
}
赞(0）分享  回复(0）举报  2023-05-06
x3naxklr

x3naxklr4#

// Code 1

@Service
public class DatabaseComparisonService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void compareAndExportToExcel(String query, String[] columnNames1, String[] columnNames2,
                                         String folderPath, String fileName) {
        // Retrieve data from tables
        List<Map<String, Object>> results1 = jdbcTemplate.queryForList(query);
        List<Map<String, Object>> results2 = jdbcTemplate.queryForList(query);

        // Find similarities and differences
        Set<Map<String, Object>> similarities = new HashSet<>(results1);
        similarities.retainAll(results2);

        Set<Map<String, Object>> differences = new HashSet<>(results1);
        differences.addAll(results2);
        differences.removeAll(similarities);

        // Create Excel workbook and sheet
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Similarities and Differences");

        // Add header row for table1
        Row headerRow1 = sheet.createRow(0);
        for (int i = 0; i < columnNames1.length; i++) {
            headerRow1.createCell(i).setCellValue(columnNames1[i]);
        }

        // Add data rows for similarities and differences for table1
        int rowNum = 1;
        for (Map<String, Object> result : differences) {
            if (result.get(columnNames1[0]) != null) {
                Row row = sheet.createRow(rowNum++);
                for (int i = 0; i < columnNames1.length; i++) {
                    row.createCell(i).setCellValue(String.valueOf(result.get(columnNames1[i])));
                }
            }
        }

        // Add empty row to separate similarities and differences
        Row emptyRow = sheet.createRow(rowNum++);

        // Add header row for table2
        Row headerRow2 = sheet.createRow(rowNum++);
        for (int i = 0; i < columnNames2.length; i++) {
            headerRow2.createCell(i).setCellValue(columnNames2[i]);
        }

        // Add data rows for similarities and differences for table2
        for (Map<String, Object> result : differences) {
            if (result.get(columnNames1[0]) == null) {
                Row row = sheet.createRow(rowNum++);
                for (int i = 0; i < columnNames2.length; i++) {
                    row.createCell(i).setCellValue(String.valueOf(result.get(columnNames2[i])));
                }
            }
        }

        // Export to Excel file
        String filePath = folderPath + "/" + fileName + ".xlsx";
        try {
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}


@Autowired
private DatabaseComparisonService databaseComparisonService;

public void compareTables() {
    String query = "SELECT t1.id AS table1_id, t1.name AS table1_name, t1.description AS table1_description, " +
                   "t2.code AS table2_code, t2.title AS table2_title, t2.content AS table2_content " +
                   "FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id";

    String[] columnNames1 = {"table1_id", "table1_name", "table1_description"};
    String[] columnNames2 = {"table2_code", "table2_title", "table2_content"};

    String folderPath = "path/to/folder";
赞(0）分享  回复(0）举报  2023-05-06
mpgws1up

mpgws1up5#

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

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class CompareTablesAndGenerateSummary {

    public void compareTablesAndGenerateSummary(List<Map<String, Object>> table1, List<Map<String, Object>> table2, String filePath) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Summary");

        // Header Row
        int rowIndex = 0;
        Row headerRow = sheet.createRow(rowIndex++);
        Cell cell = headerRow.createCell(0);
        cell.setCellValue("Table Name");
        cell = headerRow.createCell(1);
        cell.setCellValue("Total Rows");
        cell = headerRow.createCell(2);
        cell.setCellValue("Similar Rows");
        cell = headerRow.createCell(3);
        cell.setCellValue("Different Rows");

        // Table 1
        Map<String, Integer> table1Result = compareTable(table1, table2);
        Row row = sheet.createRow(rowIndex++);
        cell = row.createCell(0);
        cell.setCellValue("Table 1");
        cell = row.createCell(1);
        cell.setCellValue(table1.size());
        cell = row.createCell(2);
        cell.setCellValue(table1Result.get("similar"));
        cell = row.createCell(3);
        cell.setCellValue(table1Result.get("different"));

        // Table 2
        Map<String, Integer> table2Result = compareTable(table2, table1);
        row = sheet.createRow(rowIndex++);
        cell = row.createCell(0);
        cell.setCellValue("Table 2");
        cell = row.createCell(1);
        cell.setCellValue(table2.size());
        cell = row.createCell(2);
        cell.setCellValue(table2Result.get("similar"));
        cell = row.createCell(3);
        cell.setCellValue(table2Result.get("different"));

        // Save workbook to file
        FileOutputStream fileOutputStream = new FileOutputStream(filePath);
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        workbook.close();
    }

    private Map<String, Integer> compareTable(List<Map<String, Object>> table1, List<Map<String, Object>> table2) {
        Set<Map<String, Object>> similarRows = new HashSet<>(table1);
        similarRows.retainAll(table2);
        int similarCount = similarRows.size();

        Set<Map<String, Object>> differentRows = new HashSet<>(table1);
        differentRows.addAll(table2);
        differentRows.removeAll(similarRows);
        int differentCount = differentRows.size();

        Map<String, Integer> result = new HashMap<>();
        result.put("similar", similarCount);
        result.put("different", differentCount);

        return result;
    }
}


//

// First, compare the two dynamic tables and get the results

List<Map<String, Object>> table1 = // dynamic table 1
List<Map<String, Object>> table2 = // dynamic table 2

List<Map<String, Object>> similarRows = new ArrayList<>();
List<Map<String, Object>> differentRows = new ArrayList<>();
List<Map<String, Object>> missingRows = new ArrayList<>();

for (Map<String, Object> row1 : table1) {
    boolean found = false;
    for (Map<String, Object> row2 : table2) {
        if (row1.equals(row2)) {
            similarRows.add(row1);
            found = true;
            break;
        }
    }
    if (!found) {
        missingRows.add(row1);
    }
}

for (Map<String, Object> row2 : table2) {
    boolean found = false;
    for (Map<String, Object> row1 : table1) {
        if (row1.equals(row2)) {
            found = true;
            break;
        }
    }
    if (!found) {
        missingRows.add(row2);
    }
}

int duplicateCount = table1.size() + table2.size() - similarRows.size();
int mismatchedCount = differentRows.size() + missingRows.size();

// Create the summary Excel sheet

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet summarySheet = workbook.createSheet("Summary");

// Add headers

int rowNum = 0;
XSSFRow row = summarySheet.createRow(rowNum++);
row.createCell(0).setCellValue("Duplicate Count");
row.createCell(1).setCellValue(duplicateCount);

row = summarySheet.createRow(rowNum++);
row.createCell(0).setCellValue("Mismatched Count");
row.createCell(1).setCellValue(mismatchedCount);

row = summarySheet.createRow(rowNum++);
row.createCell(0).setCellValue("Similar Rows");
for (int i = 0; i < similarRows.size(); i++) {
    row = summarySheet.createRow(rowNum++);
    Map<String, Object> rowValues = similarRows.get(i);
    int colNum = 0;
    for (Map.Entry<String, Object> entry : rowValues.entrySet()) {
        row.createCell(colNum++).setCellValue(entry.getValue().toString());
    }
}

rowNum++; // add a blank row between the sections

row = summarySheet.createRow(rowNum++);
row.createCell(0).setCellValue("Different Rows");
for (int i = 0; i < differentRows.size(); i++) {
    row = summarySheet.createRow(rowNum++);
    Map<String, Object> rowValues = differentRows.get(i);
    int colNum = 0;
    for (Map.Entry<String, Object> entry : rowValues.entrySet()) {
        row.createCell(colNum++).setCellValue(entry.getValue().toString());
    }
}

rowNum++; // add a blank row between the sections

row = summarySheet.createRow(rowNum++);
row.createCell(0).setCellValue("Missing Rows");
for (int i = 0; i < missingRows.size(); i++) {
    row = summarySheet.createRow(rowNum++);
    Map<String, Object> rowValues = missingRows.get(i);
    int colNum = 0;
    for (Map.Entry<String, Object> entry : rowValues.entrySet()) {
        row.createCell(colNum++).setCellValue(entry.getValue().toString());
    }
}

// Save the workbook to a file

FileOutputStream outputStream = new FileOutputStream("summary.xlsx");
workbook.write(outputStream);
workbook.close();
outputStream.close();
赞(0）分享  回复(0）举报  2023-05-06
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com