WriteExcel.java - AWS Code Sample

WriteExcel.java

package com.example.photo; import jxl.CellView; import jxl.Workbook; import jxl.WorkbookSettings; import jxl.format.UnderlineStyle; import jxl.write.Label; import jxl.write.Number; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import org.springframework.stereotype.Component; import java.io.IOException; import java.util.List; import java.util.Locale; @Component public class WriteExcel { private WritableCellFormat timesBoldUnderline; private WritableCellFormat times; // Returns an InputStream that represents the Excel Report public java.io.InputStream exportExcel( List<List> list) { try { java.io.InputStream is = write(list); return is ; } catch(WriteException | IOException e) { e.printStackTrace(); } return null; } // Generates the report and returns an inputstream public java.io.InputStream write( List<List> list) throws IOException, WriteException { java.io.OutputStream os = new java.io.ByteArrayOutputStream() ; WorkbookSettings wbSettings = new WorkbookSettings(); wbSettings.setLocale(new Locale("en", "EN")); // Create a Workbook - pass the OutputStream WritableWorkbook workbook = Workbook.createWorkbook(os, wbSettings); //Outer list int size = list.size() ; // Outer list for (int i = 0; i < size; i++) { //Need to get the WorkItem from each list List innerList = (List) list.get(i); WorkItem wi = (WorkItem)innerList.get(i); workbook.createSheet(wi.getKey() +" Sheet ", 0); WritableSheet excelSheet = workbook.getSheet(0); createLabel(excelSheet); createContent(excelSheet, innerList); } // Close the workbook workbook.write(); workbook.close(); // Get an inputStram that represents the Report java.io.ByteArrayOutputStream stream = new java.io.ByteArrayOutputStream(); stream = (java.io.ByteArrayOutputStream)os; byte[] myBytes = stream.toByteArray(); java.io.InputStream is = new java.io.ByteArrayInputStream(myBytes) ; return is ; } // Create Headings in the Excel spreadsheet private void createLabel(WritableSheet sheet) throws WriteException { // Create a times font WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10); // Define the cell format times = new WritableCellFormat(times10pt); // Lets automatically wrap the cells times.setWrap(true); // create create a bold font with unterlines WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false, UnderlineStyle.SINGLE); timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline); // Lets automatically wrap the cells timesBoldUnderline.setWrap(true); CellView cv = new CellView(); cv.setFormat(times); cv.setFormat(timesBoldUnderline); cv.setAutosize(true); // Write a few headers addCaption(sheet, 0, 0, "Photo"); addCaption(sheet, 1, 0, "Label"); addCaption(sheet, 2, 0, "Confidence"); } // Write the Work Item Data to the Excel Report private int createContent(WritableSheet sheet, List<List> list) throws WriteException { int size = list.size() ; // list for (int i = 0; i < size; i++) { WorkItem wi = (WorkItem)list.get(i); //Get tne work item values String key = wi.getKey(); String label = wi.getName(); String confidence = wi.getConfidence(); // First column addLabel(sheet, 0, i + 2, key); // Second column addLabel(sheet, 1, i + 2, label); // Third column addLabel(sheet, 2, i + 2, confidence); } return size; } private void addCaption(WritableSheet sheet, int column, int row, String s) throws WriteException { Label label; label = new Label(column, row, s, timesBoldUnderline); int cc = countString(s); sheet.setColumnView(column, cc); sheet.addCell(label); } private void addNumber(WritableSheet sheet, int column, int row, Integer integer) throws WriteException { Number number; number = new Number(column, row, integer, times); sheet.addCell(number); } private void addLabel(WritableSheet sheet, int column, int row, String s) throws WriteException { Label label; label = new Label(column, row, s, times); int cc = countString(s); if (cc > 200) sheet.setColumnView(column, 150); else sheet.setColumnView(column, cc+6); sheet.addCell(label); } private int countString (String ss) { int count = 0; //Counts each character except space for(int i = 0; i < ss.length(); i++) { if(ss.charAt(i) != ' ') count++; } return count; } }