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 Freexxxxxxxxxx
public class ExcelFIleFromDB {
private String SQL_Query = "Your SQL Query goes here";
private String DB_URL = "jdbc:mysql://localhost:3306/<your SQL database name goes here>";
private Connection con = null;
private PreparedStatement pr_stmt = null;
private ResultSet rs = null;
private ResultSetMetaData rsmd = null;
private String[] column_names = null;
{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DB_URL, "<db_user>", "<db_pass>");
pr_stmt = con.prepareStatement(SQL_Query);
rs = pr_stmt.executeQuery();
rsmd = pr_stmt.getMetaData();
}
private ExcelFIleFromDB() throws SQLException, ClassNotFoundException, IOException {
column_names = genColumnNames();
getRowData();
}
private String[] genColumnNames() throws SQLException {
column_names = new String[rsmd.getColumnCount()+1];
if (rsmd.getColumnCount() > 1) {
for (int i = 1; i < rsmd.getColumnCount()+1; i++) {
column_names[i] = rsmd.getColumnName(i);
}
} else if (rsmd.getColumnCount() == 1) {
column_names = new String[rsmd.getColumnCount()];
column_names[rsmd.getColumnCount() - 1] = rsmd.getColumnName(rsmd.getColumnCount());
}
return column_names;
}
private void getRowData() throws SQLException, IOException {
LinkedList<Object[]> lst = new LinkedList<Object[]>();
if (column_names.length > 1) {
while (rs.next()) {
String[] p = null;
StringBuffer row = new StringBuffer();
for (int i = 0; i < column_names.length; i++) {
if (column_names[i] != null) {
String temp = rs.getString(column_names[i]) + ",";
row.append(temp);
}
}
p = row.substring(0, row.length() - 1).split(",");
lst.add(p);
}
} else if (column_names.length == 1) {
System.out.println("invoked value =1 " + column_names[0]);
while (rs.next()) {
if (column_names[0] != null) {
String[] p = null;
String row = rs.getString(column_names[0]) + ",";
p = row.substring(0, row.length() - 1).split(",");
lst.add(p);
}
}
}
addtoList(lst);
}
private void addtoList(LinkedList<Object[]> lst) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet spreadsheet = workbook.createSheet("<Your EXCELSHEET NAME GOES HERE>");
XSSFRow row;
Map < String, Object[] > empinfo = new TreeMap < String, Object[] >();
empinfo.put( "1", new Object[] {column_names});
for (int i = 0; i < lst.size(); i++) {
Object[] objArr = lst.get(i);
row = spreadsheet.createRow(i+1);
int cellnum = 0;
for (Object obj : objArr) {
Cell cell = row.createCell(cellnum++);
if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else if (obj instanceof Boolean) {
cell.setCellValue((Boolean) obj);
} else if (obj instanceof String) {
cell.setCellValue((String) obj);
} else if (obj instanceof Double) {
cell.setCellValue((Double) obj);
}
}
}
FileOutputStream out = new FileOutputStream(new File("<EXEL FILE NAME PATH.xlsx>"));
workbook.write(out);
out.close();
}
public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
new ExcelFIleFromDB();
}
}
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.
Opinions expressed by DZone contributors are their own.
Comments