Hello,
here is version who also implements correct Date/Time handling in the=20
XLS export.
private boolean writeXLS(File file, boolean includeHeaders, int=20
nbrSelCols, int[] selCols, int nbrSelRows, TableExportCsvController=20
ctrl, int[] selRows)
throws IOException, WriteException
{
WritableWorkbook workbook =3D Workbook.createWorkbook(file);
WritableSheet sheet =3D workbook.createSheet("Squirrel SQL Export",=
0);
int curRow=3D 0;
if (includeHeaders)
{
for (int colIdx =3D 0; colIdx < nbrSelCols; ++colIdx)
{
String columnName =3D _table.getColumnName(selCols[colIdx]);
jxl.write.Label label =3D new jxl.write.Label(colIdx, curRow,=
=20
columnName);
sheet.addCell(label);
}
curRow++;
}
for (int rowIdx =3D 0; rowIdx < nbrSelRows; ++rowIdx)
{
for (int colIdx =3D 0; colIdx < nbrSelCols; ++colIdx)
{
WritableCell xlsCell=3D null;
Object cellObj;
if(ctrl.useGloablPrefsFormatting() &&=20
_table.getColumnModel().getColumn(colIdx) instanceof ExtTableColumn)
{
ExtTableColumn col =3D (ExtTableColumn)=20
_table.getColumnModel().getColumn(colIdx);
cellObj =3D _table.getValueAt(selRows[rowIdx],=20
selCols[colIdx]);
if(null !=3D cellObj)
{
ColumnDisplayDefinition colDef=3D=20
col.getColumnDisplayDefinition();
int colType=3D colDef.getSqlType();
switch (colType)
{
case Types.BIT:
case Types.BOOLEAN:
xlsCell=3D new jxl.write.Boolean(colIdx,=20
curRow, (Boolean) cellObj);
break;
case Types.INTEGER:
xlsCell=3D new jxl.write.Number(colIdx,=20
curRow, new Float((Integer) cellObj));
break;
case Types.SMALLINT:
case Types.TINYINT:
xlsCell=3D new jxl.write.Number(colIdx,=20
curRow, new Float((Short) cellObj));
break;
case Types.DECIMAL:
xlsCell=3D new jxl.write.Number(colIdx,=20
curRow, new Float((Integer) cellObj));
break;
case Types.NUMERIC:
xlsCell=3D new jxl.write.Number(colIdx,=20
curRow, ((BigDecimal) cellObj).floatValue());
break;
case Types.FLOAT:
xlsCell=3D new jxl.write.Number(colIdx,=20
curRow, new Float((Integer) cellObj));
break;
case Types.DOUBLE:
xlsCell=3D new jxl.write.Number(colIdx,=20
curRow, new Float((Double) cellObj));
break;
case Types.REAL:
xlsCell=3D new jxl.write.Number(colIdx,=20
curRow, (Float) cellObj);
break;
case Types.BIGINT:
xlsCell=3D new jxl.write.Number(colIdx,=20
curRow, new Float((Integer) cellObj));
break;
case Types.DATE:
case Types.TIMESTAMP:
/* Work arround some UTC and Daylight saving=20
offsets */
java.util.Date xlsUTCDate=3D new=20
java.util.Date((((java.sql.Timestamp)=20
cellObj).getTime())-(((java.sql.Timestamp)=20
cellObj).getTimezoneOffset()*60*1000));
xlsCell=3D new jxl.write.DateTime(colIdx,=20
curRow, xlsUTCDate, jxl.write.DateTime.GMT);
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
cellObj =3D=20
CellComponentFactory.renderObject(cellObj,=20
col.getColumnDisplayDefinition());
xlsCell =3D new jxl.write.Label(colIdx, curRow=
,=20
getDataXLS(cellObj));
break;
default:
Class c =3D cellObj.getClass();
String s =3D c.getName() ;
s_log.warn("Coldef class:=20
"+colDef.getClassName());
s_log.warn("Colvalue Type: "+s+" : colType:=20
"+colType);
s_log.warn("Colvalue=20
Value:"+cellObj.toString());
cellObj =3D=20
CellComponentFactory.renderObject(cellObj,=20
col.getColumnDisplayDefinition());
xlsCell =3D new jxl.write.Label(colIdx,=20
curRow, getDataXLS(cellObj));
}
}
}
else
{
cellObj =3D _table.getValueAt(selRows[rowIdx],=20
selCols[colIdx]);
xlsCell =3D new jxl.write.Label(colIdx, curRow,=20
getDataXLS(cellObj));
}
if (xlsCell !=3D null)
{
sheet.addCell(xlsCell);
}
}
curRow++;
}
// All sheets and cells added. Now write out the workbook
workbook.write();
workbook.close();
return true;
}
Andr=E9
Gerd Wagner schrieb:
> Andr=E9 Schild wrote:
>> Hello Gerd,
>>
>> good idea to put it in one class.
>>
>> Do you have a idea how I can access the type information (and values)=20
>> inside the writeExcel() method ?
> Inside the second for loop only just take care for the part where you=20
> have an ExtTableColumn. Then look at=20
> ExtTableColumn.getColumnDisplayDefinition(). Inside the=20
> ColumnDisplayDefinition you'll find anything you need.
>
> Thanks again.
>
> Gerd
>
>> Once I have access to the types and values, it's very easy to export=20
>> them in a more appropriate format.
>>
>> Andr=E9
>>
>>
>> Gerd Wagner schrieb:
>>> Hi,
>>>
>>> I just integrated Andr=E9's patch with some modifications: There is=20
>>> just one export function named "Export CSV / MS Excel".
>>>
>>> First of all thanks a lot, Andr=E9, for the patch. Second you would d=
o=20
>>> me a big favor if you could look at=20
>>> TableExportCsvCommand.writeExcel() and see if you can do something=20
>>> about data types. Probably the most important problem is that=20
>>> numbers occur as strings in Excel.
>>>
>>> I'd be grateful for another patch.
>>>
>>> Thanks in advance.
>>>
>>> Gerd
>>>
>>>
>>>
>>> CptBlaubaer wrote:
>>>> Hi Andr=E9,
>>>>
>>>> that sounds interesting. What's wrong about the POI? In the=20
>>>> function I created I also used the datatype information from the=20
>>>> MetaData, which I guess would be an advantage. Sounds as if none of=20
>>>> the chief-developers are really interested in this functionality.=20
>>>> Perhaps you have an idea how the POI might be included into the=20
>>>> 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=E9 Schild <a.schild@...
>>>> <mailto:a.schild@...>>:
>>>>
>>>> Hello,
>>>>
>>>> I have made a small patch to be able to export results into exce=
l
>>>> 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=20
>>>> export (I
>>>> did
>>>> not even cleanup the export dialog)
>>>> It also export anything as string values, so numeric values,=20
>>>> booleans,
>>>> dates etc. are exported as string in excel.
>>>>
>>>> Probably this could be refactored to be a plugin, but actually=20
>>>> 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=E9
>>>>
|