Excel Sheet From Database Without Use of POJO
This program creates an excel sheet by simply passing a SQL query to the database and replicating the view to the excel grid.
Join the DZone community and get the full member experience.
Join For Free
Java
xxxxxxxxxx
1
195
1
2
public class ExcelFIleFromDB {
3
4
private String SQL_Query = "Your SQL Query goes here";
5
6
private String DB_URL = "jdbc:mysql://localhost:3306/<your SQL database name goes here>";
7
8
private Connection con = null;
9
10
private PreparedStatement pr_stmt = null;
11
12
private ResultSet rs = null;
13
14
private ResultSetMetaData rsmd = null;
15
16
private String[] column_names = null;
17
18
{
19
20
Class.forName("com.mysql.jdbc.Driver");
21
22
con = DriverManager.getConnection(DB_URL, "<db_user>", "<db_pass>");
23
24
pr_stmt = con.prepareStatement(SQL_Query);
25
26
rs = pr_stmt.executeQuery();
27
28
rsmd = pr_stmt.getMetaData();
29
30
}
31
32
private ExcelFIleFromDB() throws SQLException, ClassNotFoundException, IOException {
33
34
column_names = genColumnNames();
35
36
getRowData();
37
38
}
39
40
private String[] genColumnNames() throws SQLException {
41
42
column_names = new String[rsmd.getColumnCount()+1];
43
44
if (rsmd.getColumnCount() > 1) {
45
46
for (int i = 1; i < rsmd.getColumnCount()+1; i++) {
47
48
column_names[i] = rsmd.getColumnName(i);
49
50
}
51
52
} else if (rsmd.getColumnCount() == 1) {
53
54
column_names = new String[rsmd.getColumnCount()];
55
56
column_names[rsmd.getColumnCount() - 1] = rsmd.getColumnName(rsmd.getColumnCount());
57
58
}
59
60
return column_names;
61
62
}
63
64
private void getRowData() throws SQLException, IOException {
65
66
LinkedList<Object[]> lst = new LinkedList<Object[]>();
67
68
if (column_names.length > 1) {
69
70
while (rs.next()) {
71
72
String[] p = null;
73
74
StringBuffer row = new StringBuffer();
75
76
for (int i = 0; i < column_names.length; i++) {
77
78
if (column_names[i] != null) {
79
80
String temp = rs.getString(column_names[i]) + ",";
81
82
row.append(temp);
83
84
}
85
86
}
87
88
p = row.substring(0, row.length() - 1).split(",");
89
90
lst.add(p);
91
92
}
93
94
95
96
} else if (column_names.length == 1) {
97
98
System.out.println("invoked value =1 " + column_names[0]);
99
100
while (rs.next()) {
101
102
if (column_names[0] != null) {
103
104
String[] p = null;
105
106
String row = rs.getString(column_names[0]) + ",";
107
108
p = row.substring(0, row.length() - 1).split(",");
109
110
lst.add(p);
111
112
}
113
114
}
115
116
117
118
}
119
120
addtoList(lst);
121
122
}
123
124
private void addtoList(LinkedList<Object[]> lst) throws IOException {
125
126
XSSFWorkbook workbook = new XSSFWorkbook();
127
128
129
130
XSSFSheet spreadsheet = workbook.createSheet("<Your EXCELSHEET NAME GOES HERE>");
131
132
XSSFRow row;
133
134
Map < String, Object[] > empinfo = new TreeMap < String, Object[] >();
135
136
empinfo.put( "1", new Object[] {column_names});
137
138
for (int i = 0; i < lst.size(); i++) {
139
140
Object[] objArr = lst.get(i);
141
142
row = spreadsheet.createRow(i+1);
143
144
int cellnum = 0;
145
146
for (Object obj : objArr) {
147
148
149
150
Cell cell = row.createCell(cellnum++);
151
152
if (obj instanceof Date) {
153
154
cell.setCellValue((Date) obj);
155
156
} else if (obj instanceof Boolean) {
157
158
cell.setCellValue((Boolean) obj);
159
160
} else if (obj instanceof String) {
161
162
cell.setCellValue((String) obj);
163
164
} else if (obj instanceof Double) {
165
166
cell.setCellValue((Double) obj);
167
168
}
169
170
}
171
172
}
173
174
175
176
FileOutputStream out = new FileOutputStream(new File("<EXEL FILE NAME PATH.xlsx>"));
177
178
workbook.write(out);
179
180
out.close();
181
182
183
184
}
185
186
187
188
public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
189
190
new ExcelFIleFromDB();
191
192
}
193
194
}
EXPLAINATION
This application establishes connection before the class is being loaded in the memory by the class loader. Once the predefined Query is executed the ResultSet structure is acquired along with some MetaData.
MetaData the name suggests data about data, this is what helps to define the structure of the ExcelSheet.
From ResultSetMetaData class number of columns are determined.
Hence if the number of columns is Unity(1) then the getColumnName() is called once, else if greater than Unity then the array is looped to obtain the columnNames.
The number of columnNames will be equal to the size of the record holding values that are pertaining to those columnNames.
To extract the record from the ResultSet the following steps occur "while" looping over the ResultSet.
For every invocation of next( ) that returns true.
1) Create an array of length equal to the number of columns found from ResultSetMetaData getColumnCount method, that will hold a record.
2) Fill every index of the created array by invoking the getStringMethod parameterized with the index in the loop.
3) Add the array to a LinkedList.
Once the LinkedList has the entire set of rows that were found in the ResultSet class.
A TreeMap holds the structure of the data to be written into the grid of the ExcelSheet.
The first Key is set for columnNames array.From the next Key to the end of the LinkedList carrying the records iteration starts a new Row is created.
For every row having cells the row's data is looped and the index of the cells are incremented
as the array's index data gets accessed linearly. Depending on the DataType of the object the value is casted.
Once the Workbook has the data set in the grid.FileOutstream writes the data to the file in the disk.
Topics:
pojo,
jdbc,
snippet,
java,
sql,
excel
Opinions expressed by DZone contributors are their own.
Comments