WriteExcel.java - AWS Code Sample


/* Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved. This file is licensed under the Apache License, Version 2.0 (the "License"). You may not use this file except in compliance with the License. A copy of the License is located at http://aws.amazon.com/apache2.0/ This file is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. */ package com.aws.services; 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 com.aws.entities.WorkItem; 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<WorkItem> 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<WorkItem> 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); workbook.createSheet("Work Item Report", 0); WritableSheet excelSheet = workbook.getSheet(0); createLabel(excelSheet) ; int size = createContent(excelSheet, list); // 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, "Writer"); addCaption(sheet, 1, 0, "Date"); addCaption(sheet, 2, 0, "Guide"); addCaption(sheet, 3, 0, "Description"); addCaption(sheet, 4, 0, "Status"); } // Write the Work Item Data to the Excel Report private int createContent(WritableSheet sheet, List<WorkItem> list) throws WriteException { int size = list.size() ; // Add customer data to the Excel report for (int i = 0; i < size; i++) { WorkItem wi = list.get(i); //Get tne work item values String name = wi.getName(); String guide = wi.getGuide(); String date = wi.getDate(); String des = wi.getDescription(); String status = wi.getStatus(); // First column addLabel(sheet, 0, i+2, name); // Second column addLabel(sheet, 1, i+2, date); // Third column addLabel(sheet, 2, i+2,guide); // Forth column addLabel(sheet, 3, i+2, des); // Fifth column addLabel(sheet, 4, i+2, status); } 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; } }