根据表名导出动态表到excel

首先新建一个类用于接收参数，由于字段名和值不确定，所以

public class Pair<T, U> {
    private T first;
    private U second;

    public Pair(T first, U second) {
        this.first = first;
        this.second = second;
    }

    public T getFirst() {
        return first;
    }

    public void setFirst(T first) {
        this.first = first;
    }

    public U getSecond() {
        return second;
    }

    public void setSecond(U second) {
        this.second = second;
    }
}

由于前端传入参数只有表名，所以需要先由表名得出各字段名作为excel的第一行，再在for循环中根据字段名得出字段值逐行输出到excel。

mapper.xml及mapper.java内内容

 <select id="getTableFields" parameterType="string" resultType="string">
        select column_name from information_schema.COLUMNS where table_name=#{tableName}
 </select>
 <select id="getTableValues" resultType="map">
        select  
        <trim>
            <foreach collection="columnList" item="listItem" separator=",">
                 ${listItem} 
            </foreach> 
        </trim>
        from ${tableName}
    </select>

 

List<String> getTableFields(@Param("tableName") String tableName);

List<Map<String, ?>> getTableValues(@Param("tableName") String tableName,@Param("columnList") List<String> columnList);

 

 

service及serviceimpl内容

public Pair<List<String>, List<Map<String, ?>>> getTableFields(String tableName);

public List<Map<String, ?>> getTableValues(String tableName,List<String> columnList);

    private DataToExcelMapper dao;

    public Pair<List<String>, List<Map<String, ?>>> getTableFields(String tableName) {
        List<Map<String, ?>> dataList = null;
        List<String> columnList = dao.getTableFields(tableName);
        if (!CollectionUtils.isEmpty(columnList)) {
            dataList = dao.getTableValues(tableName, columnList);
        }
        return new Pair<List<String>, List<Map<String, ?>>>(columnList, dataList);
    }

    public List<Map<String, ?>> getTableValues(String tableName, List<String> columnList) {
        return dao.getTableValues(tableName, columnList);
    }

最后，action内内容

        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String str = sdf.format(date);
        String filejg="手工表"; 
        if(tableName==null) {
            filejg="空表";
        }else {
            filejg=tableName; 
        }
        String fileName = filejg+ str + ".xls";
        response.setContentType("application/x.ms-excel");
        response.setHeader("Content-disposition",
                "attachment;filename=".concat(new String(fileName.getBytes("UTF-8"), "ISO-8859-1")));
        Pair<List<String>, List<Map<String, ?>>> pair = DTES.getTableFields(tableName);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("表");
        HSSFRow row = sheet.createRow((int) 0);
        if (!CollectionUtils.isEmpty(pair.getFirst())) {
            int i = 0;
            for (String columnName : pair.getFirst()) {
                HSSFCellStyle style = wb.createCellStyle();
                HSSFCell cell = row.createCell((short) i);
                cell.setCellValue(columnName);
                cell.setCellStyle(style);
                i++;
            }
        }

        if (!CollectionUtils.isEmpty(pair.getSecond())) {
            for (int i = 0, j = pair.getSecond().size(); i < j; i++) {
                Map<String, ?> valueMap = pair.getSecond().get(i);
                row = sheet.createRow(i + 1);

                for (int k = 0; k < pair.getFirst().size(); k++) {
                    Object value = valueMap.get(pair.getFirst().get(k));
                    row.createCell((short) k).setCellValue(value.toString() /** TODO translate values */
                    );
                }
            }
        }
        try {
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
        response.getOutputStream().flush();

...作为java新手，暂时只会这样导出动态表。

待改进复用吧...