Hi André,

oops, then sorry for pointing out something that was obvious to you and not according to your problem.

Perhaps I should really forget about trying to code in Java.

Andreas


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

actually this is what I normaly do when I have a resultset at hand.
But in the place where the excel export is generated I don't see a way
to access the meta data or any type informations.


André


CptBlaubaer schrieb:
> 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
> <mailto: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>
>     >> <mailto: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>
>     >>
>     >>
>     >>
>     <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>
>     >>     <mailto: 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
>     < 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
>     <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