结果集到 Excel 文件

Question

有CSVWriter类用于将结果集写入CSV文件。

是否有任何类似的类用于将结果集输出写入EXCEL文件。？？

编辑：我的结果集数据将是动态的，所以我只想将结果集数据转储到 excel 文件中

编辑2：参考 http://www.codereye.com/2009/09/converting-resultset-to-excel-table-in.html。它将数据导出到 excel 工作表，但我需要 pas 每列的数据类型。我想删除该依赖项，以便结果集中的任何内容，它应该盲目地导出到 excelsheet。

Answer 1

我最近使用了OpenCSV，它对我来说很好用。例如，在这里，如何将ResultSet写入 CSV 文件：

java.sql.ResultSet myResultSet = ...
writer.writeAll(myResultSet, includeHeaders);

Answer 2

我无法使用链接的示例，所以我使用OpenCSV中的方法作为基础来解析SQL表。

public class ResultSetToExcel {
public static final int CLOBBUFFERSIZE = 2048;
public void toXLS(ResultSet rs, WritableSheet sheet) throws SQLException, IOException, RowsExceededException, WriteException{
    List<String> header = getColumnNames(rs);
    Integer i=0;
    for(String headCell : header){
        sheet.addCell(new Label(i,0,headCell));
        i++;
    }
    int index = 1;
    while(rs.next()){
        List<String> rowValues = getColumnValues(rs);
        int j=0;
        for(String value: rowValues){
            sheet.addCell(new Label(j,index,value));
            j++;
        }
        index++;
    }
}
public List<String> getColumnNames(ResultSet rs) throws SQLException {
    List<String> names = new ArrayList<String>();
    ResultSetMetaData metadata = rs.getMetaData();
    for (int i = 0; i < metadata.getColumnCount(); i++) {
        names.add(metadata.getColumnName(i+1));
    }
    return names;
}
public List<String> getColumnValues(ResultSet rs) throws SQLException, IOException{
    List<String> values = new ArrayList<String>();
    ResultSetMetaData metadata = rs.getMetaData();
    for (int i = 0; i < metadata.getColumnCount(); i++) {
        values.add(getColumnValue(rs, metadata.getColumnType(i + 1), i + 1));
    }
    return values;
}
private String handleObject(Object obj){
    return obj == null ? "" : String.valueOf(obj);
}
private String handleBigDecimal(BigDecimal decimal) {
    return decimal == null ? "" : decimal.toString();
}
private String handleLong(ResultSet rs, int columnIndex) throws SQLException {
    long lv = rs.getLong(columnIndex);
    return rs.wasNull() ? "" : Long.toString(lv);
}
private String handleInteger(ResultSet rs, int columnIndex) throws SQLException {
    int i = rs.getInt(columnIndex);
    return rs.wasNull() ? "" : Integer.toString(i);
}
private String handleDate(ResultSet rs, int columnIndex) throws SQLException {
    try {
        if(rs.getString(columnIndex) != null){
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
            Date fecha = new Date( formatter.parse(rs.getString(columnIndex)).getTime());
            return formatter.format(fecha);
        }
        else
            return "";
    } catch (ParseException e) {
        throw new SQLException("Fecha erronea o faltante");
    }
}
private String handleTime(Time time) {
    return time == null ? null : time.toString();
}
private String handleTimestamp(Timestamp timestamp) {
    SimpleDateFormat timeFormat = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
    return timestamp == null ? null : timeFormat.format(timestamp);
}
private String getColumnValue(ResultSet rs, int colType, int colIndex)
        throws SQLException, IOException {
    String value = "";
    switch (colType){
        case Types.BIT:
        case Types.JAVA_OBJECT:
            value = handleObject(rs.getObject(colIndex));
            break;
        case Types.BOOLEAN:
            boolean b = rs.getBoolean(colIndex);
            value = Boolean.valueOf(b).toString();
            break;
        case Types.NCLOB: // todo : use rs.getNClob
        case Types.CLOB:
            Clob c = rs.getClob(colIndex);
            if (c != null) {
                value = read(c);
            }
            break;
        case Types.BIGINT:
            value = handleLong(rs, colIndex);
            break;
        case Types.DECIMAL:
        case Types.DOUBLE:
        case Types.FLOAT:
        case Types.REAL:
        case Types.NUMERIC:
            value = handleBigDecimal(rs.getBigDecimal(colIndex));
            break;
        case Types.INTEGER:
        case Types.TINYINT:
        case Types.SMALLINT:
            value = handleInteger(rs, colIndex);
            break;
        case Types.DATE:
            value = handleDate(rs, colIndex);
            break;
        case Types.TIME:
            value = handleTime(rs.getTime(colIndex));
            break;
        case Types.TIMESTAMP:
            value = handleTimestamp(rs.getTimestamp(colIndex));
            break;
        case Types.NVARCHAR: // todo : use rs.getNString
        case Types.NCHAR: // todo : use rs.getNString
        case Types.LONGNVARCHAR: // todo : use rs.getNString
        case Types.LONGVARCHAR:
        case Types.VARCHAR:
        case Types.CHAR:
            value = rs.getString(colIndex);
            break;
        default:
            value = "";
    }

    if (value == null){
        value = "";
    }
    return value;
}
private static String read(Clob c) throws SQLException, IOException
{
    StringBuilder sb = new StringBuilder( (int) c.length());
    Reader r = c.getCharacterStream();
    char[] cbuf = new char[CLOBBUFFERSIZE];
    int n;
    while ((n = r.read(cbuf, 0, cbuf.length)) != -1) {
            sb.append(cbuf, 0, n);
    }
    return sb.toString();
}

}

Answer 3

您可以从结果中获取数据并使用Apache POI Jars写入excel文件。