CODEWITHSUNDEEP
javaexcelresultset
happy
happy

Reputation: 2628

Resultset to Excel file

There is CSVWriter class for writing resultset to CSV file.

Is there any similar class for writing resultset output to EXCEL file.??

Edit: My resultset data will be dynamic so I just want to dump the resultset data into excel file

Edit2: Ref http://www.codereye.com/2009/09/converting-resultset-to-excel-table-in.html. It exports the data to excel sheet but I need to pas the datatype of each column .I want to remove that dependency so that whatver is in the resultset ,blindly it should export to excelsheet.

Upvotes: 5

Views: 30713

Answers (3)

IgnacioPL
IgnacioPL

Reputation: 67

I couldn't use the example of the link, so I made my own method using as base the one in OpenCSV to parse the SQL Table.

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();
}

}

Upvotes: 2

Maxx
Maxx

Reputation: 1453

I recently used OpenCSV, it worked fine for me. Here, for example, how to write a ResultSet to a CSV file:

java.sql.ResultSet myResultSet = ...

writer.writeAll(myResultSet, includeHeaders);

Upvotes: 2

Stauz
Stauz

Reputation: 316

You can fetch data from result and write in excel file using Apache POI Jars.

Upvotes: 1

Related Questions