javaapache-poistruts1

How to create new excel document in java


Here I am using poi-jar to export data from database to excel it working fine . But here I want change instead of creating manual path. I wanted make that as to download automatically with out creating any manual path like this:

OutputStream file = new FileOutputStream(new File("D:\\venki1213.xls"));

And this is my code:

Session ses = HibernateUtil.getSessionFactory().openSession();
String query;
query = "from LibraryImportEntity ";
List<LibraryImportEntity> list = ses.createQuery(query).list();
ses.close();
System.out.println("list size" + list.size());
String filename = "D://ranjith//ranjith1213.xls";
OutputStream file = new FileOutputStream(new File("D:\\venki1213.xls"));
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");
HSSFRow rowhead = sheet.createRow((short) 0);
rowhead.createCell(0).setCellValue("Sl.No");
rowhead.createCell(1).setCellValue("Magazine Name");
rowhead.createCell(2).setCellValue("Volume No");
rowhead.createCell(3).setCellValue("Issue No");
rowhead.createCell(4).setCellValue("Cost");
int i = 1;
for (LibraryImportEntity l1 : list) {
    System.out.println("sl_no" + l1.getSl_no());
    System.out.println("Magazinename" + l1.getMagazinename());
    System.out.println("sl_no" + l1.getVolumeno());
    System.out.println("sl_no" + l1.getCost());
    HSSFRow row = sheet.createRow((short) i);
    row.createCell(0).setCellValue(l1.getSl_no());
    row.createCell(1).setCellValue(l1.getMagazinename());
    row.createCell(2).setCellValue(l1.getVolumeno());
    row.createCell(3).setCellValue(l1.getIssueno());
    row.createCell(4).setCellValue(l1.getCost());
    i++;
}
try {
    FileOutputStream fileOut = new FileOutputStream(filename);
    hwb.write(file);
    fileOut.close();
} catch (IOException ex) {
    Logger.getLogger(LibraryExportDAO.class.getName()).log(Level.SEVERE, null, ex);
}
System.out.println("Your excel file has been generated!");
return "success";
}

Solution

  • If you are generating the excel in a browser just call the method you want to generate the excel file based on a url and set the response properties like this,

      //1.Fill the data from db
    
      //2.Set the response properties
      String fileName = "Excel.xls";
      response.setHeader("Content-Disposition", "inline; filename=" + fileName);
      // Make sure to set the correct content type(the below content type is ok)
      response.setContentType("application/vnd.ms-excel");
    
      //3.Write to the output stream
      Writer.write();//call write method of Writer class to write the data to o/p stream
    

    Writer Class:

    public class Writer {
    
         private static Logger logger = Logger.getLogger("service");
             /**
              * Writes the report to the output stream
              */
             public static void write(HttpServletResponse response, HSSFSheet worksheet) {
    
              logger.debug("Writing excel data to the stream");
                  try {
                       // Retrieve the output stream
                       ServletOutputStream outputStream = response.getOutputStream();
                       // Write to the output stream
                       worksheet.getWorkbook().write(outputStream);
                       // Flush the stream
                       outputStream.flush();
                  } 
                  catch (Exception e) {
                      logger.error("Unable to write excel data to the output stream");
                  }
         }
    }
    

    In the response receiving end you'll be prompted to download the file in the browser window.. LINK