This program creates an excel sheet by simply passing a SQL query to the database and replicating the view to the excel grid. It uses similar constructs to the previous program mentioned about Batch insert without using POJO. Consider the code mentioned below.
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();
}
}Explanation
The above program obtains a number of columns of the view generated after a SQL query is executed.
The column names are put into an array if column numbers are >1. The getString method is parameterized to use strings, thus the array is re-iterated for every row obtained after an iteration of the resultset. Therefore the array is accessed from the list and using the enhanced for loop and the data is written to the excel grid.