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 
 119 
 120 
 121 
 122 
 123 
 124 
 125 
 126 
 127 
 128 
 129 
 130 
 131 
 132 
 133 
 134 
 135 
 136 

package fr.koor.webstore.servlet;

import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;


@WebServlet( urlPatterns="/export.xls" )
public class ExcelExport extends HttpServlet {

    private static final long serialVersionUID = 8181226250983408872L;

    
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
                                    throws ServletException, IOException {
        
        ServletContext context = getServletContext();
        String databaseDriver = context.getInitParameter( "JDBC_DRIVER" );
        String databaseURL = context.getInitParameter( "JDBC_URL" );
        String databaseLogin = context.getInitParameter( "JDBC_LOGIN" );
        String databasePassword = context.getInitParameter( "JDBC_PASSWORD" );

        try {
            Class.forName( databaseDriver );
            
            try ( HSSFWorkbook workbook = new HSSFWorkbook(); 
                  Connection connection = DriverManager.getConnection(
                                    databaseURL, databaseLogin, databasePassword ) ) {
                
                // --- Define some cell styles ---
                CellStyle headerCellStyle = workbook.createCellStyle();
                HSSFFont font = workbook.createFont();
                font.setBold( true );
                headerCellStyle.setFont(font);  
                headerCellStyle.setAlignment( HorizontalAlignment.CENTER );

                CellStyle numericCellStyle = workbook.createCellStyle();
                numericCellStyle.setDataFormat( 
                    workbook.getCreationHelper().createDataFormat().getFormat("0.00"));

                // --- Export T_Articles table ---
                HSSFSheet articleSheet = workbook.createSheet( "T_Articles table" );
                HSSFRow row = articleSheet.createRow( 0 );
                HSSFCell cell;

                row.setRowStyle( headerCellStyle );
                cell = row.createCell( 0 );
                cell.setCellStyle( headerCellStyle);
                cell.setCellValue( "Article identifier" );
                
                cell = row.createCell( 1 );
                cell.setCellStyle( headerCellStyle);
                cell.setCellValue( "Description" );
                
                cell = row.createCell( 2 );
                cell.setCellStyle( headerCellStyle);
                cell.setCellValue( "Brand" );
                
                cell = row.createCell( 3 );
                cell.setCellStyle( headerCellStyle);
                cell.setCellValue( "Unitary price" );
                
                int rowIndex = 1;
                String strSql = "SELECT * FROM T_Articles";
                try ( Statement statement = connection.createStatement() ) {
                    try ( ResultSet resultSet = statement.executeQuery( strSql ) ) {
                        while ( resultSet.next() ) {
                            row = articleSheet.createRow( rowIndex++ );
                            cell = row.createCell( 0 ); 
                            cell.setCellValue( resultSet.getInt( "idArticle" ) );

                            cell = row.createCell( 1 ); 
                            cell.setCellValue( resultSet.getString( "description" ) );

                            cell = row.createCell( 2 ); 
                            cell.setCellValue( resultSet.getString( "brand" ) );

                            cell = row.createCell( 3, CellType.NUMERIC );
                            cell.setCellStyle( numericCellStyle );
                            cell.setCellValue( resultSet.getDouble( "unitaryPrice" ) );

                        }
                    }
                }
                
                // Auto size each columns of the article's sheet
                for( int i=0; i<4; i++ ) articleSheet.autoSizeColumn( i );
            
                // --- Export T_Users table ---
                HSSFSheet userSheet = workbook.createSheet( "T_Users table" );
                // TODO: Continue to export other data 
            
            
                // Write the XLSX file to the HTTP socket
                response.setContentType( "application/vnd.ms-excel" );
                try ( OutputStream out = response.getOutputStream() ) {
                    workbook.write( out );
                }
                
            }           
            
        } catch (Exception e) {
            e.printStackTrace();
            
            response.setContentType( "text/html" );
            try ( PrintWriter out = response.getWriter() ) {
                out.println( "An error is produced, please view de server logs " + 
                             "for more informations" );
            }
            
        }
        
    }
    
}