Interview Questions

SQL Query results to Excel

Given a SQL query, the following class will generate an Excel sheet with the results from the query.

SQLToExcel.java

/**
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
 * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */

import java.awt.Font;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;

/**
 * The purpose of this class is to generate an excel sheet based on the results from a sql query.
 */

public class SQLToExcel
{
        public SQLToExcel()
        {
        }

        public static void main(String[] args)
        {
                SQLToExcel sqlExcel = new SQLToExcel();
                String sqlQuery = "SELECT * FROM USERS";
                ResultSet rs = sqlExcel.getResultsetFromSql(sqlQuery);
                sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "USERS");
        }

        /**
         * This method returns a database connection given the necessary parameters to create the connection.
         */

        public Connection getDBConnection()
        {
                try
                {
                        Class.forName("oracle.jdbc.driver.OracleDriver");
                }
                catch (ClassNotFoundException e)
                {
                        e.printStackTrace();
                }

                Connection conn = null;
                try
                {
                        conn = DriverManager.getConnection("<jdbc_url>", "<user_name>", "<pass_word>");
                }
                catch (SQLException e)
                {
                        e.printStackTrace();
                }
                return conn;
        }

        /**
         * This method returns a ResultSet for the given sql query.
         */

        public ResultSet getResultsetFromSql(String sql)
        {
                Connection conn = getDBConnection();
                ResultSet rs = null;
                try
                {
                        Statement stmt = conn.createStatement();
                        rs = stmt.executeQuery(sql);
                }
                catch (SQLException e)
                {
                        e.printStackTrace();
                }
                return rs;
        }

        /**
         * This method returns a Map with keys as row numbers and values as another LinkedHashMap containing key as column
         * name and value as column value , present in the ResulSet. We have used LinkedHashMap because it maintains the
         * order in which the values are put in the Map.
         */

        public Map<String, LinkedHashMap<String, String>> processResultSet(ResultSet rs)
        {
                ArrayList<String> columnNames = new ArrayList<String>();
                LinkedHashMap<String, String> rowDetails = new LinkedHashMap<String, String>();
                Map<String, LinkedHashMap<String, String>> resultMap = new LinkedHashMap<String, LinkedHashMap<String, String>>();
                ResultSetMetaData rsm = null;

                if (rs != null)
                {
                        try
                        {
                                rsm = (ResultSetMetaData) rs.getMetaData();
                                for (int i = 1; i <= rsm.getColumnCount(); i++)
                                {
                                        System.out.println(i + " -> " + rsm.getColumnName(i));
                                        columnNames.add(rsm.getColumnName(i));
                                }
                        }
                        catch (SQLException e)
                        {
                                e.printStackTrace();
                        }
                }

                try
                {
                        int rowCount = 1;
                        while (rs.next())
                        {
                                for (int i = 1; i <= rsm.getColumnCount(); i++)
                                {
                                        rowDetails.put(rsm.getColumnName(i), rs.getString(i));
                                }
                                resultMap.put(new Integer(rowCount).toString(), rowDetails);
                                rowCount++;
                                rowDetails = new LinkedHashMap<String, String>();
                        }
                }
                catch (SQLException e)
                {
                        e.printStackTrace();
                }
                return resultMap;
        }

        /**
         * This method generates an excel sheet containing data from the given Map. The name of the excel sheet will be the
         * String passed as a parameter.
         */

        public void generateExcel(Map<String, LinkedHashMap<String, String>> resultMap, String name)
        {
                FileOutputStream fileOut = null;

                try
                {
                        HSSFWorkbook wb = new HSSFWorkbook();
                        HSSFCellStyle headerStyle = wb.createCellStyle();
                        HSSFSheet sheet3 = wb.createSheet(name);

                        HSSFFont headerFont = wb.createFont();
                        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

                        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                        headerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
                        headerStyle.setFillBackgroundColor(HSSFColor.WHITE.index);
                        headerStyle.setFont(headerFont);

                        try
                        {
                                fileOut = new FileOutputStream("C:\\" + name + ".xls");
                        }
                        catch (FileNotFoundException e)
                        {
                                e.printStackTrace();
                        }

                        HSSFRow sessionname = sheet3.createRow(2);
                        HSSFCell title = sessionname.createCell(3);
                        title.setCellStyle(headerStyle);
                        title.setCellValue(name);

                        HSSFRow row = sheet3.createRow(5);

                        Map<String, LinkedHashMap<String, String>> rMap = resultMap;
                        Map<String, String> columnDetails = rMap.get("1");

                        Set<String> s = columnDetails.keySet();
                        int cellNo = 0;
                        for (String s1 : s)
                        {
                                HSSFCell cell0 = row.createCell(cellNo);
                                cell0.setCellStyle(headerStyle);
                                cell0.setCellValue(s1);
                                cellNo++;
                        }

                        for (int i = 1; i <= rMap.size(); i++)
                        {
                                columnDetails = rMap.get(new Integer(i).toString());
                                System.out.println(i);
                                HSSFRow nextrow = sheet3.createRow(5 + i);
                                Set<String> set = columnDetails.keySet();
                                int cellNum = 0;
                                for (String s2 : set)
                                {
                                        nextrow.createCell(cellNum).setCellValue(columnDetails.get(s2));
                                        cellNum++;
                                }
                        }

                        sheet3.autoSizeColumn(0);
                        sheet3.autoSizeColumn(1);
                        sheet3.autoSizeColumn(2);
                        sheet3.autoSizeColumn(3);
                        wb.write(fileOut);
                        fileOut.flush();
                        fileOut.close();
                }
                catch (FileNotFoundException fe)
                {
                        fe.printStackTrace();
                }
                catch (IOException e)
                {
                        e.printStackTrace();
                }
                finally
                {
                        try
                        {
                                fileOut.flush();
                                fileOut.close();
                        }
                        catch (IOException e)
                        {
                                e.printStackTrace();
                        }
                }
        }
}