Thread: [Squirrel-sql-develop] Adding xls export functionality
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: <a.s...@aa...> - 2006-12-12 11:41:49
|
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.=20 (Licensed under LGPL) http://www.andykhan.com/jexcelapi/ Currently this patch does almost the same thing as the CSV export (I did=20 not even cleanup the export dialog) It also export anything as string values, so numeric values, booleans,=20 dates etc. are exported as string in excel. Probably this could be refactored to be a plugin, but actually I had no=20 time to look at the plugin architecture. The patch can be found in the BUG section of SF. (Bug ID 1613883) Andr=E9 |
From: CptBlaubaer <cpt...@gm...> - 2006-12-12 12:16:57
|
Hi Andr=E9, 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? Anyway have a nice day Andreas 2006/12/12, Andr=E9 Schild <a.s...@aa...>: > > 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=E9 > > ------------------------------------------------------------------------- > 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=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > _______________________________________________ > Squirrel-sql-develop mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > |
From: <a.s...@aa...> - 2006-12-12 13:09:57
|
Hello Andreas, there is nothing wrong with POI, it's just that in the past I have done=20 most work with JExcelApi, because it has support for images in XLS files, and it can be better=20 tuned when handling very large XLS files. You can also use template XLT files to create new ones. Generally when not using too much of the XLS format replacing POI with=20 JExcelApi (or vice-versa) is just a matter changing includes and renaming some classes and method call= s. I don't think there are license issues. Sorry, I just joined the list, what function(s) did you create ? Andr=E9 CptBlaubaer schrieb: > Hi Andr=E9, > > that sounds interesting. What's wrong about the POI? In the function I=20 > created I also used the datatype information from the MetaData, which=20 > I guess would be an advantage. Sounds as if none of the=20 > chief-developers are really interested in this functionality. Perhaps=20 > you have an idea how the POI might be included into the project? Or=20 > are their any license issues I'm not aware of? > > Gerd, Rob, Collin could any of you guys comment on that? > > Anyway have a nice day > > Andreas > > > 2006/12/12, Andr=E9 Schild <a.s...@aa...=20 > <mailto:a.s...@aa...>>: > > 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, boolea= ns, > 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=E9 > > -------------------------------------------------------------------= ------ > 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 c= ash > http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&= CID=3DDEVDEV > <http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge= &CID=3DDEVDEV> > _______________________________________________ > Squirrel-sql-develop mailing list > Squ...@li... > <mailto:Squ...@li...> > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > > |
From: Gerd W. <bir...@t-...> - 2006-12-12 23:39:18
|
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.s...@aa... > <mailto:a.s...@aa...>>: > > 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 > Squ...@li... > <mailto:Squ...@li...> > 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 > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop |
From: <a.s...@aa...> - 2006-12-13 07:19:12
|
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 ? 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 jus= t=20 > one export function named "Export CSV / MS Excel". > > First of all thanks a lot, Andr=E9, for the patch. Second you would do=20 > me a big favor if you could look at TableExportCsvCommand.writeExcel()=20 > and see if you can do something about data types. Probably the most=20 > 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=E9, >> >> that sounds interesting. What's wrong about the POI? In the function=20 >> I created I also used the datatype information from the MetaData,=20 >> which I guess would be an advantage. Sounds as if none of the=20 >> chief-developers are really interested in this functionality. Perhaps=20 >> you have an idea how the POI might be included into the project? Or=20 >> 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.s...@aa...=20 >> <mailto:a.s...@aa...>>: >> >> 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,=20 >> booleans, >> dates etc. are exported as string in excel. >> >> Probably this could be refactored to be a plugin, but actually I=20 >> 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 >> >> =20 >> ----------------------------------------------------------------------= ---=20 >> >> 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=20 >> cash >> =20 >> http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID= =3DDEVDEV=20 >> >> =20 >> <http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CI= D=3DDEVDEV>=20 >> >> _______________________________________________ >> Squirrel-sql-develop mailing list >> Squ...@li... >> <mailto:Squ...@li...> >> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop >> >> >> >> ----------------------------------------------------------------------= -- >> >> ----------------------------------------------------------------------= ---=20 >> >> Take Surveys. Earn Cash. Influence the Future of IT >> Join SourceForge.net's Techsay panel and you'll get the chance to=20 >> share your >> opinions on IT & business topics through brief surveys - and earn cash >> http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID= =3DDEVDEV=20 >> >> >> >> ----------------------------------------------------------------------= -- >> >> _______________________________________________ >> Squirrel-sql-develop mailing list >> Squ...@li... >> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > |
From: <a.s...@aa...> - 2006-12-18 22:30:24
|
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.s...@aa...=20 >>>> <mailto:a.s...@aa...>>: >>>> >>>> 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 >>>> |
From: Gerd W. <bir...@t-...> - 2006-12-20 21:49:11
|
Hi André, your patch is applied to CVS. I've already seen that summing in Excel now works :-). Thanks a lot for your contribution. Gerd André Schild wrote: > Hello, > > here is version who also implements correct Date/Time handling in the > XLS export. > > > private boolean writeXLS(File file, boolean includeHeaders, int > nbrSelCols, int[] selCols, int nbrSelRows, TableExportCsvController > ctrl, int[] selRows) > throws IOException, WriteException > { > WritableWorkbook workbook = Workbook.createWorkbook(file); > WritableSheet sheet = workbook.createSheet("Squirrel SQL Export", 0); > > > int curRow= 0; > if (includeHeaders) > { > for (int colIdx = 0; colIdx < nbrSelCols; ++colIdx) > { > String columnName = _table.getColumnName(selCols[colIdx]); > jxl.write.Label label = new jxl.write.Label(colIdx, curRow, > columnName); > sheet.addCell(label); > } > curRow++; > } > > > for (int rowIdx = 0; rowIdx < nbrSelRows; ++rowIdx) > { > for (int colIdx = 0; colIdx < nbrSelCols; ++colIdx) > { > WritableCell xlsCell= null; > Object cellObj; > if(ctrl.useGloablPrefsFormatting() && > _table.getColumnModel().getColumn(colIdx) instanceof ExtTableColumn) > { > ExtTableColumn col = (ExtTableColumn) > _table.getColumnModel().getColumn(colIdx); > cellObj = _table.getValueAt(selRows[rowIdx], > selCols[colIdx]); > > if(null != cellObj) > { > ColumnDisplayDefinition colDef= > col.getColumnDisplayDefinition(); > int colType= colDef.getSqlType(); > switch (colType) > { > case Types.BIT: > case Types.BOOLEAN: > xlsCell= new jxl.write.Boolean(colIdx, > curRow, (Boolean) cellObj); > break; > case Types.INTEGER: > xlsCell= new jxl.write.Number(colIdx, > curRow, new Float((Integer) cellObj)); > break; > case Types.SMALLINT: > case Types.TINYINT: > xlsCell= new jxl.write.Number(colIdx, > curRow, new Float((Short) cellObj)); > break; > case Types.DECIMAL: > xlsCell= new jxl.write.Number(colIdx, > curRow, new Float((Integer) cellObj)); > break; > case Types.NUMERIC: > xlsCell= new jxl.write.Number(colIdx, > curRow, ((BigDecimal) cellObj).floatValue()); > break; > case Types.FLOAT: > xlsCell= new jxl.write.Number(colIdx, > curRow, new Float((Integer) cellObj)); > break; > case Types.DOUBLE: > xlsCell= new jxl.write.Number(colIdx, > curRow, new Float((Double) cellObj)); > break; > case Types.REAL: > xlsCell= new jxl.write.Number(colIdx, > curRow, (Float) cellObj); > break; > case Types.BIGINT: > xlsCell= new jxl.write.Number(colIdx, > curRow, new Float((Integer) cellObj)); > break; > case Types.DATE: > case Types.TIMESTAMP: > /* Work arround some UTC and Daylight saving > offsets */ > java.util.Date xlsUTCDate= new > java.util.Date((((java.sql.Timestamp) > cellObj).getTime())-(((java.sql.Timestamp) > cellObj).getTimezoneOffset()*60*1000)); > xlsCell= new jxl.write.DateTime(colIdx, > curRow, xlsUTCDate, jxl.write.DateTime.GMT); > break; > case Types.CHAR: > case Types.VARCHAR: > case Types.LONGVARCHAR: > cellObj = > CellComponentFactory.renderObject(cellObj, > col.getColumnDisplayDefinition()); > xlsCell = new jxl.write.Label(colIdx, curRow, > getDataXLS(cellObj)); > break; > default: > Class c = cellObj.getClass(); > String s = c.getName() ; > s_log.warn("Coldef class: > "+colDef.getClassName()); > s_log.warn("Colvalue Type: "+s+" : colType: > "+colType); > s_log.warn("Colvalue > Value:"+cellObj.toString()); > cellObj = > CellComponentFactory.renderObject(cellObj, > col.getColumnDisplayDefinition()); > xlsCell = new jxl.write.Label(colIdx, > curRow, getDataXLS(cellObj)); > } > } > } > else > { > cellObj = _table.getValueAt(selRows[rowIdx], > selCols[colIdx]); > xlsCell = new jxl.write.Label(colIdx, curRow, > getDataXLS(cellObj)); > } > if (xlsCell != null) > { > sheet.addCell(xlsCell); > } > } > curRow++; > } > > // All sheets and cells added. Now write out the workbook > workbook.write(); > workbook.close(); > > return true; > } > > > > André > > > Gerd Wagner schrieb: >> André 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) >>> inside the writeExcel() method ? >> Inside the second for loop only just take care for the part where you >> have an ExtTableColumn. Then look at >> ExtTableColumn.getColumnDisplayDefinition(). Inside the >> 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 >>> 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.s...@aa... >>>>> <mailto:a.s...@aa...>>: >>>>> >>>>> 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 > _______________________________________________ > Squirrel-sql-develop mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > |
From: Gerd W. <bir...@t-...> - 2006-12-13 22:15:22
|
André 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) > inside the writeExcel() method ? Inside the second for loop only just take care for the part where you have an ExtTableColumn. Then look at ExtTableColumn.getColumnDisplayDefinition(). Inside the 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 > 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.s...@aa... >>> <mailto:a.s...@aa...>>: >>> >>> 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 >>> Squ...@li... >>> <mailto:Squ...@li...> >>> 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 >>> Squ...@li... >>> 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 > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > |
From: CptBlaubaer <cpt...@gm...> - 2006-12-13 07:22:14
|
Hi there, I can understand that my posting gets ignored since Andr=E9's Patch is alre= ady available. As I pointed out quite a few times before. I've written a piece of Java-Code that creates an execel-document from a JDBC Resultset and also uses the datatype information for the formatting of the Excel cells. Perhaps it might be usefull if someone might point out what needs to be done, to include stuff from other projects into SQuirreL. I still don't kno= w how to do that and I wanted help about that and didn't get any kind of reaction. OK, I should leave the coding to the experts then. Anyway, have a nice day and take care Andreas 2006/12/13, Gerd Wagner <bir...@t-...>: > > Hi, > > I just integrated Andr=E9's patch with some modifications: There is just = one > export function named "Export CSV / MS Excel". > > First of all thanks a lot, Andr=E9, 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 tha= t > 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 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 licens= e > > 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.s...@aa... > > <mailto:a.s...@aa...>>: > > > > 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 ha= d > no > > time to look at the plugin architecture. > > > > The patch can be found in the BUG section of SF. (Bug ID 1613883) > > > > > > Andr=E9 > > > > > ------------------------------------------------------------------------- > > 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=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > > < > http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV> > > _______________________________________________ > > Squirrel-sql-develop mailing list > > Squ...@li... > > <mailto:Squ...@li...> > > 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=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > > > > > > -----------------------------------------------------------------------= - > > > > _______________________________________________ > > Squirrel-sql-develop mailing list > > Squ...@li... > > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > > |
From: Gerd W. <bir...@t-...> - 2006-12-13 22:07:25
|
Hi Andreas, be sure your suggestion is appreciated. The main problem is (as almost always) time. André's patch was mostly ready and it worked on the data format that is available underneath the table GUI. To base the functionality on a ResultSet at that point would have been much harder. Thanks for caring about SQuirreL Gerd CptBlaubaer wrote: > Hi there, > > I can understand that my posting gets ignored since André's Patch is > already available. > As I pointed out quite a few times before. I've > written a piece of Java-Code that creates an execel-document from a JDBC > Resultset and also uses the datatype information for the formatting of > the Excel cells. > > Perhaps it might be usefull if someone might point out what needs to be > done, to include stuff from other projects into SQuirreL. I still don't > know how to do that and I wanted help about that and didn't get any kind > of reaction. > > OK, I should leave the coding to the experts then. > > Anyway, have a nice day and > > take care > > Andreas > > 2006/12/13, Gerd Wagner < bir...@t-... > <mailto:bir...@t-...>>: > > 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.s...@aa... > <mailto:a.s...@aa...> > > <mailto: a.s...@aa... <mailto:a.s...@aa...>>>: > > > > 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 > > Squ...@li... > <mailto:Squ...@li...> > > <mailto:Squ...@li... > <mailto:Squ...@li...>> > > 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 > > Squ...@li... > <mailto:Squ...@li...> > > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > > |
From: CptBlaubaer <cpt...@gm...> - 2006-12-13 07:42:23
|
Hi Andr=E9, I'm not quite sure if that might help, but I did the following to access th= e 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 =3D stmt.executeQuery(sb.toString()); //Fetch the Meta-Data for the ResultSet ResultSetMetaData rsmd =3D rs.getMetaData(); //Access the Meta-Data for (int i =3D 1; i <=3D 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 =3D sheet.createRow(rownum); //Iterate over the columns for (int i =3D 1; i <=3D rsmd.getColumnCount(); i++) { //Create a new Excel-Cell in the row cell =3D 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 =3D 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 =3D 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=E9 Schild <a.s...@aa...>: > > 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=E9 > > > Gerd Wagner schrieb: > > Hi, > > > > I just integrated Andr=E9's patch with some modifications: There is jus= t > > one export function named "Export CSV / MS Excel". > > > > First of all thanks a lot, Andr=E9, 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=E9, > >> > >> 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=E9 Schild <a.s...@aa... > >> <mailto:a.s...@aa...>>: > >> > >> 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=E9 > >> > >> > >> > ------------------------------------------------------------------------- > >> > >> 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=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > >> > >> > >> < > http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV> > >> > >> _______________________________________________ > >> Squirrel-sql-develop mailing list > >> Squ...@li... > >> <mailto:Squ...@li...> > >> 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=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > >> > >> > >> > >> > ------------------------------------------------------------------------ > >> > >> _______________________________________________ > >> Squirrel-sql-develop mailing list > >> Squ...@li... > >> 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=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > _______________________________________________ > Squirrel-sql-develop mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > |
From: <a.s...@aa...> - 2006-12-13 07:47:17
|
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=E9 CptBlaubaer schrieb: > Hi Andr=E9, > > I'm not quite sure if that might help, but I did the following to=20 > access the information from the Meta-Data and the data. Perhaps you=20 > can use some of it. > > Take care > > Andreas > > //Create a ResultSet or take it as a parameter > ResultSet rs =3D stmt.executeQuery(sb.toString()); > //Fetch the Meta-Data for the ResultSet > ResultSetMetaData rsmd =3D rs.getMetaData(); > //Access the Meta-Data > for (int i =3D 1; i <=3D rsmd.getColumnCount(); i++) > { > //Debug-Output of results just for demonstration > System.out.println("Name: " + rsmd.getColumnName(i) + " Type: " +=20 > rsmd.getColumnTypeName(i) + " Typenumber: " + rsmd.getColumnType (i)); > } > //Access the data of the ResultSet > while (rs.next()) > { > //Create a new row in Excel-Sheet > row =3D sheet.createRow(rownum); > //Iterate over the columns > for (int i =3D 1; i <=3D rsmd.getColumnCount (); i++) > { > //Create a new Excel-Cell in the row > cell =3D 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 =3D wb.createCellStyle(); > =20 > 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 =3D wb.createCellStyle (); > =20 > 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; =20 > } > default: > { > cell.setCellType (HSSFCell.CELL_TYPE_STRING); > cell.setCellValue("n.a."); > } > } > } > rownum++; > } > > > > > > 2006/12/13, Andr=E9 Schild < a.s...@aa...=20 > <mailto:a.s...@aa...>>: > > Hello Gerd, > > good idea to put it in one class. > > Do you have a idea how I can access the type information (and value= s) > inside the writeExcel() method ? > Once I have access to the types and values, it's very easy to expor= t > them in a more appropriate format. > > Andr=E9 > > > Gerd Wagner schrieb: > > Hi, > > > > I just integrated Andr=E9's patch with some modifications: There > is just > > one export function named "Export CSV / MS Excel". > > > > First of all thanks a lot, Andr=E9, for the patch. Second you wou= ld do > > me a big favor if you could look at > TableExportCsvCommand.writeExcel() > > and see if you can do something about data types. Probably the mo= st > > 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=E9, > >> > >> 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=E9 Schild < a.s...@aa... > <mailto:a.s...@aa...> > >> <mailto:a.s...@aa... <mailto:a.s...@aa...>>>: > >> > >> 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=E9 > >> > >> > >> > -------------------------------------------------------------------= ------ > > >> > >> 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=3Djoin.php&p=3Dsourceforge&= CID=3DDEVDEV > <http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge= &CID=3DDEVDEV> > >> > >> > >> > <http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge= &CID=3DDEVDEV > <http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge= &CID=3DDEVDEV>> > >> > >> _______________________________________________ > >> Squirrel-sql-develop mailing list > >> Squ...@li... > <mailto:Squ...@li...> > >> <mailto:Squ...@li... > <mailto:Squ...@li...>> > >> =20 > 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 t= o > >> share your > >> opinions on IT & business topics through brief surveys - and > earn cash > >> > http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&= CID=3DDEVDEV > <http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge= &CID=3DDEVDEV> > >> > >> > >> > >> > -------------------------------------------------------------------= ----- > >> > >> _______________________________________________ > >> Squirrel-sql-develop mailing list > >> Squ...@li... > <mailto:Squ...@li...> > >> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develo= p > > > > > -------------------------------------------------------------------= ------ > 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=3Djoin.php&p=3Dsourceforge&= CID=3DDEVDEV > <http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge= &CID=3DDEVDEV> > _______________________________________________ > Squirrel-sql-develop mailing list > Squ...@li... > <mailto:Squ...@li...> > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > > |
From: CptBlaubaer <cpt...@gm...> - 2006-12-13 07:51:51
|
Hi Andr=E9, 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=E9 Schild <a.s...@aa...>: > > 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=E9 > > > CptBlaubaer schrieb: > > Hi Andr=E9, > > > > 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 =3D stmt.executeQuery(sb.toString()); > > //Fetch the Meta-Data for the ResultSet > > ResultSetMetaData rsmd =3D rs.getMetaData(); > > //Access the Meta-Data > > for (int i =3D 1; i <=3D 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 =3D sheet.createRow(rownum); > > //Iterate over the columns > > for (int i =3D 1; i <=3D rsmd.getColumnCount (); i++) > > { > > //Create a new Excel-Cell in the row > > cell =3D 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 =3D 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 =3D 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=E9 Schild < a.s...@aa... > > <mailto:a.s...@aa...>>: > > > > 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 expor= t > > them in a more appropriate format. > > > > Andr=E9 > > > > > > Gerd Wagner schrieb: > > > Hi, > > > > > > I just integrated Andr=E9's patch with some modifications: There > > is just > > > one export function named "Export CSV / MS Excel". > > > > > > First of all thanks a lot, Andr=E9, for the patch. Second you wou= ld > 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=E9, > > >> > > >> 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=E9 Schild < a.s...@aa... > > <mailto:a.s...@aa...> > > >> <mailto:a.s...@aa... <mailto:a.s...@aa...>>>: > > >> > > >> 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=E9 > > >> > > >> > > >> > > > ------------------------------------------------------------------------- > > > > >> > > >> 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=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > > < > http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV> > > >> > > >> > > >> > > < > http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > > < > http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > >> > > >> > > >> _______________________________________________ > > >> Squirrel-sql-develop mailing list > > >> Squ...@li... > > <mailto:Squ...@li...> > > >> <mailto:Squ...@li... > > <mailto:Squ...@li...>> > > >> > > 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 t= o > > >> share your > > >> opinions on IT & business topics through brief surveys - and > > earn cash > > >> > > > http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > > < > http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV> > > >> > > >> > > >> > > >> > > > ------------------------------------------------------------------------ > > >> > > >> _______________________________________________ > > >> Squirrel-sql-develop mailing list > > >> Squ...@li... > > <mailto:Squ...@li...> > > >> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develo= p > > > > > > > > > > ------------------------------------------------------------------------- > > 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=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > > < > http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV> > > _______________________________________________ > > Squirrel-sql-develop mailing list > > Squ...@li... > > <mailto:Squ...@li...> > > 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=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > _______________________________________________ > Squirrel-sql-develop mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > |
From: <a.s...@aa...> - 2006-12-13 07:56:14
|
Hello Andreas, CptBlaubaer schrieb: > Hi Andr=E9, > > oops, then sorry for pointing out something that was obvious to you=20 > and not according to your problem. No problem, I was not offended :) > > Perhaps I should really forget about trying to code in Java. I think that you should perhaps not start with such a complex task as add= ing a plugin to Squirrel SQL. But coding in java is generally a good idea. Andr=E9 |