使用sql查询一张表所有信息写入excel返回

使用sql查询一张表所有信息写入excel返回

1.获取所有表注解

首先,通过传入的tableName查询所有注解,此项目使用pgsql,获取注解有些区别,因为是模糊查询,无实体类接收,故使用List<Map<String,Object>>

    @Select("SELECT " +
            "    column_name, " +
            "    col_description((table_schema || '.' || table_name)::regclass::oid, ordinal_position) AS column_comment" +
            "  FROM information_schema.columns\n" +
            "  WHERE table_name=#{tablename} AND table_schema = 'public'")
    List<Map<String,Object>> exportXlsColumnByTablename(String tablename);

2.写入表格

        List<Map<String,Object>> rsc = dbNormalisationMapper.exportXlsColumnByTablename(tablename);
		// 创建Excel文件和sheet
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        Row annotationRow = sheet.createRow(0);
        Row headerRow = sheet.createRow(1);

        // 写入字段注解无注解写入字段名
        int cellNum = 0;
        for (Map<String, Object> map : rsc) {
            Cell cell = annotationRow.createCell(cellNum++);
            cell.setCellValue((String) (map.get("column_comment")!= null ? map.get("column_comment") : map.get("column_name")));
        }
		//第二行写入字段名
        List<String> nameList = new ArrayList<>();
        int cellNum2 = 0;
        for (Map<String, Object> map : rsc) {
            Cell cell = headerRow.createCell(cellNum2++);
            cell.setCellValue((String) map.get("column_name"));
            nameList.add((String) map.get("column_name"));
        }

3.获取所有数据

通过传入的tableName查询所有数据,接收同上

    @Select("SELECT * FROM ${tableName}")
    List<Map<String,Object>> exportXlsByTablename(String tablename);

4.写入所有数据

从第三行开始写入所有数据并返回

        int rowNum = 2;
        List<Map<String,Object>> rs = dbNormalisationMapper.exportXlsByTablename(tablename);
        for (Map<String, Object> r : rs) {
            int nameNum = 0;
            Row row = sheet.createRow(rowNum++);
            for (int i = 1; i <= r.size(); i++) {
                Cell cell = row.createCell(i - 1);
                cell.setCellValue(r.get(nameList.get(nameNum)).toString());
                nameNum++;
            }
        }

        response.setContentType("application/vnd.ms-excel");
        try {
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

5.使用到的包

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;