Hi André,

I'm not quite sure if that might help, but I did the following to access the information from the Meta-Data and the data. Perhaps you can use some of it.

Take care

Andreas

//Create a ResultSet or take it as a parameter
ResultSet rs = stmt.executeQuery(sb.toString());
//Fetch the Meta-Data for the ResultSet
ResultSetMetaData rsmd = rs.getMetaData();
//Access the Meta-Data
for (int i = 1; i <= rsmd.getColumnCount(); i++)
{
  //Debug-Output of results just for demonstration
  System.out.println("Name: " + rsmd.getColumnName(i) + " Type: " + rsmd.getColumnTypeName(i) + " Typenumber: " + rsmd.getColumnType (i));
}
//Access the data of the ResultSet
while (rs.next())
{
  //Create a new row in Excel-Sheet
  row = sheet.createRow(rownum);
  //Iterate over the columns
  for (int i = 1; i <= rsmd.getColumnCount (); i++)
  {
    //Create a new Excel-Cell in the row
    cell = row.createCell((short) (i-1));
    //Format the cell according to the datatype of the database value
    switch (rsmd.getColumnType(i))
    {
      //formatting a string
      case Types.VARCHAR :
      {
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(rs.getString(i));
        break;
      }
      //formatting a date
      case Types.DATE :
      {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        cell.setCellValue (rs.getTimestamp(i));
        cell.setCellStyle(cellStyle);
        break;
      }
      //formatting a timestamp
      case Types.TIMESTAMP :
      {
        HSSFCellStyle cellStyle = wb.createCellStyle ();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        cell.setCellValue(rs.getTimestamp(i));
        cell.setCellStyle(cellStyle);
        break;
      }
      //formatting an integer
      case Types.INTEGER :
      {
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(rs.getInt(i));
        break;
      }
      //formatting a numeric
      case Types.NUMERIC :
      {
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(rs.getDouble(i));
        break;
      }
      //formatting a CLOB
      case Types.CLOB :
      {
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(rs.getClob(i).toString());
        break;         
      }
      default:
      {
        cell.setCellType (HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue("n.a.");
      }
    }
  }
  rownum++;
}





2006/12/13, André Schild < a.schild@aarboard.ch>:
Hello Gerd,

good idea to put it in one class.

Do you have a idea how I can access the type information (and values)
inside the writeExcel() method ?
Once I have access to the types and values, it's very easy to export
them in a more appropriate format.

André


Gerd Wagner schrieb:
> Hi,
>
> I just integrated André's patch with some modifications: There is just
> one export function named "Export CSV / MS Excel".
>
> First of all thanks a lot, André, for the patch. Second you would do
> me a big favor if you could look at TableExportCsvCommand.writeExcel()
> and see if you can do something about data types. Probably the most
> important problem is that numbers occur as strings in Excel.
>
> I'd be grateful for another patch.
>
> Thanks in advance.
>
> Gerd
>
>
>
> CptBlaubaer wrote:
>> Hi André,
>>
>> that sounds interesting. What's wrong about the POI? In the function
>> I created I also used the datatype information from the MetaData,
>> which I guess would be an advantage. Sounds as if none of the
>> chief-developers are really interested in this functionality. Perhaps
>> you have an idea how the POI might be included into the project? Or
>> are their any license issues I'm not aware of?
>>
>> Gerd, Rob, Collin could any of you guys comment on that?
> Done ;-)
>
>>
>> Anyway have a nice day
>>
>> Andreas
>>
>>
>> 2006/12/12, André Schild < a.schild@aarboard.ch
>> <mailto:a.schild@aarboard.ch>>:
>>
>>     Hello,
>>
>>     I have made a small patch to be able to export results into excel
>>     files.
>>
>>     It does not use the apache POI, but the JExcelApi from Andy Kahn.
>>     (Licensed under LGPL)
>>
>>     http://www.andykhan.com/jexcelapi/
>>
>>     Currently this patch does almost the same thing as the CSV export (I
>>     did
>>     not even cleanup the export dialog)
>>     It also export anything as string values, so numeric values,
>> booleans,
>>     dates etc. are exported as string in excel.
>>
>>     Probably this could be refactored to be a plugin, but actually I
>> had no
>>     time to look at the plugin architecture.
>>
>>     The patch can be found in the BUG section of SF. (Bug ID 1613883)
>>
>>
>>     André
>>
>>
>> -------------------------------------------------------------------------
>>
>>     Take Surveys. Earn Cash. Influence the Future of IT
>>     Join SourceForge.net's Techsay panel and you'll get the chance to
>>     share your
>>     opinions on IT & business topics through brief surveys - and earn
>> cash
>>
>> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
>>
>>
>> <http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV>
>>
>>     _______________________________________________
>>     Squirrel-sql-develop mailing list
>>     Squirrel-sql-develop@lists.sourceforge.net
>>     <mailto:Squirrel-sql-develop@lists.sourceforge.net>
>>     https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> -------------------------------------------------------------------------
>>
>> Take Surveys. Earn Cash. Influence the Future of IT
>> Join SourceForge.net's Techsay panel and you'll get the chance to
>> share your
>> opinions on IT & business topics through brief surveys - and earn cash
>> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> Squirrel-sql-develop mailing list
>> Squirrel-sql-develop@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
>


-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Squirrel-sql-develop mailing list
Squirrel-sql-develop@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop