Create and write Excel file using Google App Engine

This article explains how you can export data in excel using Google App Engine.

1. Apache POI library -- Here is maven dependency

        <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
    <version>3.16</version>

2. Download Servlet

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;




public class ExcelServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
     private static final String FILE_NAME = "/Users/user1/Desktop/MyFirstExcel.xlsx";

   
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   
    DataDTO data1=new DataDTO("A","1","2017");
    DataDTO data1=new DataDTO("B","2","2017");
     DataDTO data1=new DataDTO("C","3","2017");

        List<DataDTO>  dataList =new ArrayList<>();
       dataList.add(data1);
       dataList.add(data2);
        dataList.add(data3);

        String fileName="Export.xlsx";
        response.setContentType("application/xls");
        response.setHeader("Content-Disposition", "attachment; filename="+fileName);
        exportDataUsingObject(request, response,dataList);
       
    }
   
   
   
    private static void exportDataUsingObject(HttpServletRequest request, HttpServletResponse response, List<DataDTO> dataList) throws IOException{
          
            String sheetName = "Sheet1";//name of sheet

            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet(sheetName) ;
           
            String [] objHeaderArr=new String[] {
                    "Column1", "Column2", "Column3"
            };
           
           
            if(dataList !=null){
                int rowCount=0;
                HSSFRow row = sheet.createRow(++rowCount);
                int cellnum = 0;
                for (String obj : objHeaderArr) {
                    HSSFCell cell = row.createCell(cellnum++);
                    cell.setCellValue(obj);
                }
                //Add remaining rows
                for(DimensionVADTO obj:dataList){
                    row = sheet.createRow(++rowCount);
                    writeDataDTO(obj, row);
                }
            }
           
            ServletOutputStream outputStream = response.getOutputStream();
            outputStream.write(wb.getBytes());
            System.out.println("****************************************");
    }

    private static void writeDataDTO(DataDTO dtoObj, HSSFRow row) {
       
        HSSFCell cell = row.createCell(0);
        cell.setCellValue(dtoObj.getColumn1());
       
        cell = row.createCell(1);
        cell.setCellValue(dtoObj.getColumn2());
       
        cell = row.createCell(2);
        cell.setCellValue(dtoObj.getColumn3());
       
     
       
    }
   
    public static void writeXLSFile() throws IOException {
       
        String excelFileName = FILE_NAME;//name of excel file

        String sheetName = "Sheet1";//name of sheet

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName) ;

        //iterating r number of rows
        for (int r=0;r < 5; r++ ){
            HSSFRow row = sheet.createRow(r);
   
            //iterating c number of columns
            for (int c=0;c < 5; c++ ){
                HSSFCell cell = row.createCell(c);
               
                cell.setCellValue("Test Cell "+r+" "+c);
            }
        }
       
        FileOutputStream fileOut = new FileOutputStream(excelFileName);
       
        //write this workbook to an Outputstream.
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }

    public static void main(String[] args) {
       
         try {
             writeXLSFile(); // If you want to run local
        } catch (IOException e) {
            e.printStackTrace();
        }
           
     }