1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
| package jxl.oracle;
import java.io.File;
import java.util.Date;
import java.util.Locale;
import jxl.Workbook;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import jxl.write.DateTime;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.Label;
import jxl.write.WritableWorkbook;
import java.lang.Integer;
import jxl.write.Number;
import java.lang.Double;
import jxl.write.DateFormat;
import oracle.jdbc.OracleDriver;
public class write
{
public static void main(String[] args)
{
Connection con = null;
String sstatement;
int i;
String colname;
try
{
Date date_actuelle = new Date();
System.out.println("Debut:" + date_actuelle);
/*standalone xls creation*/
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ());
con = DriverManager.getConnection("jdbc:oracle:oci:@xxx.xxx.xxx.xxx:xx:ORCL", "TATA", "TOTO");
WritableWorkbook copy=Workbook.createWorkbook(new File("c:/test.xls"));
*/
/* inside data base*/
/*OracleDriver ora = new OracleDriver();
con = ora.defaultConnection();
WritableWorkbook copy=Workbook.createWorkbook(new File("/oradata/test.xls"));
*/
WritableSheet sheet = copy.createSheet("First Sheet", 0);
i = 0;
Statement stmt = con.createStatement();
sstatement = "select * from tagada'";
ResultSet rs = stmt.executeQuery(sstatement);
int nbcol = rs.getMetaData().getColumnCount();
int j = 1;
while (j<=nbcol)
{
colname=rs.getMetaData().getColumnName(j);
Label labeldata = new Label(j-1, i, colname);
sheet.addCell(labeldata);
j=j+1;
}
j=1;
i=1;
WritableCellFormat myformat = new WritableCellFormat(new DateFormat("dd/MM/yyyy"));
while (rs.next() && i<65535)
{
j=1;
while (j<=nbcol)
{
String coltype = rs.getMetaData().getColumnTypeName(j);
if (rs.getMetaData().getColumnTypeName(j)=="INTEGER" )
{
int sdata = rs.getInt(j);
if (!rs.wasNull())
{
Number labeldata = new Number(j-1, i, sdata);
sheet.addCell(labeldata);
}
}
if (rs.getMetaData().getColumnTypeName(j)=="NUMBER" )
{
double sdata = rs.getDouble(j);
if (!rs.wasNull())
{
Number labeldata = new Number(j-1, i, sdata);
sheet.addCell(labeldata);
}
}
if (rs.getMetaData().getColumnTypeName(j)=="VARCHAR2" || rs.getMetaData().getColumnTypeName(j)=="CHAR")
{
String sdata = rs.getString(j);
if (!rs.wasNull())
{
Label labeldata = new Label(j-1, i, sdata);
sheet.addCell(labeldata);
}
}
if (rs.getMetaData().getColumnTypeName(j)=="DATE")
{
Date sdata = rs.getDate(j);
if (!rs.wasNull())
{
DateTime labeldata = new DateTime(j-1, i, sdata, myformat);
sheet.addCell(labeldata);
}
}
j = j+1;
}
i = i+1;
}
copy.write();
rs.close();
con.close();
copy.close();
date_actuelle = new Date();
System.out.println("Fin:" + date_actuelle);
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
} |
jexcelapi stocké dans la base
Répondre avec citation
Partager