Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet.

Prototype

@Override
public XSSFSheet createSheet(String sheetname) 

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:com.vodafone.poms.ii.helpers.ExportManager.java

public void exportASPPO() {
    List<AspPo> aspPOs = aspPOController.getExportItems(fromDate, toDate);

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("All POs");
    Row row = sheet.createRow(0);//  w w  w .  j a  v a2 s.  co m
    for (int i = 0; i < aspPOHeaders.length; i++) {
        row.createCell(i).setCellValue(aspPOHeaders[i]);
    }
    int innerRow = 0;
    for (int i = 0; i < aspPOs.size(); i++) {
        row = sheet.createRow(i + 1 + innerRow);
        //po#
        row.createCell(0).setCellValue(aspPOs.get(i).getPoNumber());
        //poDate
        row.createCell(1).setCellValue(aspPOs.get(i).getPoDate());
        //domain
        row.createCell(2).setCellValue(aspPOs.get(i).getDomainName().getDomainName());
        //type
        row.createCell(3).setCellValue(aspPOs.get(i).getPoType().getTypeName());
        //description
        row.createCell(4).setCellValue(aspPOs.get(i).getPoDescription());
        //factor
        row.createCell(5).setCellValue(aspPOs.get(i).getFactor());
        //service_value
        row.createCell(6).setCellValue(aspPOs.get(i).getServiceValue().toString());
        //po_value
        row.createCell(7).setCellValue(aspPOs.get(i).getPoValue().toString());
        //po_value with taxes
        row.createCell(8).setCellValue(aspPOs.get(i).getPoValueTaxes().toString());
        //work done
        row.createCell(9).setCellValue(aspPOs.get(i).getWorkDone());
        //remaining in po
        row.createCell(10).setCellValue(aspPOs.get(i).getRemainingInPo().toString());
        //taxes
        row.createCell(11).setCellValue(aspPOs.get(i).getTaxes());
        //ASP
        row.createCell(12).setCellValue(aspPOs.get(i).getAsp().getSubcontractorName());
        //VendorPO
        row.createCell(13).setCellValue((aspPOs.get(i).getVendorPoCollection().isEmpty() ? "Uncorrelated"
                : ((VendorPo) aspPOs.get(i).getVendorPoCollection().toArray()[0]).getPoNumber()));
        Object[] grns = aspPOs.get(i).getAspGrnCollection().toArray();
        for (int j = 0; j < grns.length; j++) {
            if (j > 1) {
                row = sheet.createRow(i + 1 + innerRow);
                innerRow++;
            }
            //md_deserved
            row.createCell(14).setCellValue(((AspGrn) grns[j]).getGrnDeserved().toString());
            //md_value
            row.createCell(15)
                    .setCellValue(((AspGrn) grns[j]).getGrnValue() != null
                            ? ((AspGrn) grns[j]).getGrnValue().toString()
                            : "");
            //md_date
            row.createCell(16).setCellValue(
                    ((AspGrn) grns[j]).getGrnDate() != null ? ((AspGrn) grns[j]).getGrnDate() : null);
            //md_number
            row.createCell(17).setCellValue(
                    ((AspGrn) grns[j]).getGrnNumber() != null ? ((AspGrn) grns[j]).getGrnNumber() : "");
            //invoiced
            row.createCell(18).setCellValue(
                    ((AspGrn) grns[j]).getInvoiced() != null ? ((AspGrn) grns[j]).getInvoiced() : false);
            //remaining in md
            row.createCell(19)
                    .setCellValue(((AspGrn) grns[j]).getRemainingInGrn() != null
                            ? ((AspGrn) grns[j]).getRemainingInGrn().toString()
                            : "");

        }

    }
    FacesContext facesContext = FacesContext.getCurrentInstance();
    ExternalContext externalContext = facesContext.getExternalContext();
    externalContext.setResponseContentType("application/vnd.ms-excel");
    externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"ASP POs.xlsx\"");

    try {
        workbook.write(externalContext.getResponseOutputStream());
        externalContext.getResponseOutputStream().close();
    } catch (IOException ex) {
        Logger.getLogger(ExportManager.class.getName()).log(Level.SEVERE, null, ex);
    }
    facesContext.responseComplete();
    JsfUtil.addSuccessMessage("Customer PO Report is now exported");

}

---

From source file:com.vsquaresystem.safedeals.amenity.AmenityService.java

public Boolean exportExcel() throws IOException {

    List<Amenity> rs = amenityDAL.findAllAmenities();
    System.out.println("result set excel sop" + rs);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Amenity Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);/*  www  .  ja v a 2  s.c o  m*/
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.getCell(1).setCellValue("Id");
    xssfrow.getCell(2).setCellValue("Amenity Name");
    xssfrow.getCell(3).setCellValue("Amenity Code Id");
    //        List<Book> listBook = excelWriter.getListBook();
    String fileName = "/AmenityMasterData.xls";
    String exportPath = attachmentUtils.getAmenityExportAttachmentRootDirectory() + fileName;

    System.out.println("exportPath" + exportPath);

    int rowCount = 0;

    for (Amenity aAmenity : rs) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aAmenity, row);
    }

    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }

    return true;
}

---

From source file:com.vsquaresystem.safedeals.city.CityService.java

public Boolean exportExcel() throws IOException {
    logger.info("getExportExcel method is working");
    List<City> rs = cityDAL.findAllCities();
    System.out.println("result set excel sop" + rs);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("City Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);/*from   w  ww .  j  av  a  2  s  .  c  om*/
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.createCell(4);
    xssfrow.createCell(5);
    xssfrow.createCell(6);
    xssfrow.getCell(1).setCellValue("City Id");
    xssfrow.getCell(2).setCellValue("Name");
    xssfrow.getCell(3).setCellValue("Country Id");
    xssfrow.getCell(4).setCellValue("State Id");
    xssfrow.getCell(5).setCellValue("Latitude Id");
    xssfrow.getCell(6).setCellValue("Longitude Id");
    String fileName = "/CityMasterData.xls";
    String exportPath = attachmentUtils.getCityExportAttachmentRootDirectory() + fileName;
    System.out.println("exportPath" + exportPath);
    int rowCount = 0;

    for (City aLocation : rs) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aLocation, row);
    }

    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }

    return true;
}

---

From source file:com.vsquaresystem.safedeals.location.LocationService.java

public Boolean exportExcel() throws IOException {
    logger.info("getExportExcel method is working");
    List<Location> rs = locationDAL.findAllLocations();
    System.out.println("result set excel sop" + rs);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Location Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);/*from   www  . ja v  a  2  s  .  co  m*/
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.getCell(1).setCellValue("Location Id");
    xssfrow.getCell(2).setCellValue("Location Name");
    xssfrow.getCell(3).setCellValue("City Id");
    String fileName = "/LocationMasterData.xls";
    String exportPath = attachmentUtils.getLocationExportAttachmentRootDirectory() + fileName;
    System.out.println("exportPath" + exportPath);
    int rowCount = 0;
    for (Location aLocation : rs) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aLocation, row);
    }
    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }
    return true;
}

---

From source file:com.vsquaresystem.safedeals.rawmarketprice.RawMarketPriceService.java

public Boolean exportExcel() throws IOException {
    logger.info("getExportExcel method is working for raw market price");
    List<RawMarketPrice> rmp = rawMarketPriceDAL.findAllRawMarketPrice();
    //        logger.info("result set excel sop" + rmp);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Raw Market Price Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);/*ww w  .  j  av a 2  s  . com*/
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.createCell(4);
    xssfrow.createCell(5);
    xssfrow.createCell(6);
    xssfrow.createCell(7);
    xssfrow.createCell(8);
    xssfrow.createCell(9);
    xssfrow.createCell(10);
    xssfrow.createCell(11);
    xssfrow.createCell(12);
    xssfrow.createCell(13);
    xssfrow.createCell(14);
    xssfrow.createCell(15);
    xssfrow.createCell(16);
    xssfrow.createCell(17);
    xssfrow.createCell(18);
    xssfrow.createCell(19);
    xssfrow.createCell(20);
    xssfrow.createCell(21);
    xssfrow.createCell(22);
    xssfrow.createCell(23);
    xssfrow.createCell(24);
    xssfrow.createCell(25);
    xssfrow.getCell(1).setCellValue("Id");
    xssfrow.getCell(2).setCellValue("City Name");
    xssfrow.getCell(3).setCellValue("Location Name");
    xssfrow.getCell(4).setCellValue("Year");
    xssfrow.getCell(5).setCellValue("Month");
    xssfrow.getCell(6).setCellValue("MP Agri Land Lowest");
    xssfrow.getCell(7).setCellValue("MP Agri Land Highest");
    xssfrow.getCell(8).setCellValue("MP Plot Lowest");
    xssfrow.getCell(9).setCellValue("MP Plot Highest");
    xssfrow.getCell(10).setCellValue("MP Residential Lowest");
    xssfrow.getCell(11).setCellValue("MP Residential Highest");
    xssfrow.getCell(12).setCellValue("MP Commercial Lowest");
    xssfrow.getCell(13).setCellValue("MP Commercial Highest");
    xssfrow.getCell(14).setCellValue("Safedeal Zone Id");
    xssfrow.getCell(15).setCellValue("Location Type Id");
    xssfrow.getCell(16).setCellValue("Location Categories");
    xssfrow.getCell(17).setCellValue("Description");
    xssfrow.getCell(18).setCellValue("Major Approach Road");
    xssfrow.getCell(19).setCellValue("Source of Water");
    xssfrow.getCell(20).setCellValue("Public Transport");
    xssfrow.getCell(21).setCellValue("Advantage");
    xssfrow.getCell(22).setCellValue("Disadvantage");
    xssfrow.getCell(23).setCellValue("Population");
    xssfrow.getCell(24).setCellValue("Migration Rate");
    xssfrow.getCell(25).setCellValue("Commercial Center");

    String fileName = "/RawMarketPriceMasterData.xls";
    String exportPath = attachmentUtils.getRawMarketPriceExportAttachmentRootDirectory() + fileName;

    logger.info("exportPath" + exportPath);

    int rowCount = 0;

    for (RawMarketPrice aRawMarketPrice : rmp) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aRawMarketPrice, row);
    }

    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }

    return true;
}

---

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?xlsxExcel//from  ww  w .j a v a 2s .  c  o  m
 * @param heads 
 * @param data ?
 * @param sheetName Excel?
 * @param out ?
 * @return ByteArrayOutputStream
 * @throws IOException
 */
public static void writeToXLSX(ExcelRow heads, ExcelData data, String sheetName, ByteArrayOutputStream out)
        throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet1 = wb.createSheet(sheetName);
    XSSFRow row = sheet1.createRow(0);
    XSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    XSSFCell cell;
    for (int i = 0; i < heads.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(heads.get(i));
        cell.setCellStyle(style);
    }

    if (data != null && data.size() != 0) {
        for (int n = 0; n < data.size(); n++) {
            row = sheet1.createRow(n + 1);
            ExcelRow datarow = data.get(n);
            for (int m = 0; m < datarow.size(); m++) {
                cell = row.createCell(m);
                cell.setCellValue(datarow.get(m));
                cell.setCellStyle(style);
            }
        }
    }
    wb.write(out);
}

---

From source file:controller.application.employee.ViewEmployeController.java

private void toExcel(ArrayList<ToExcelEmployee> lst, ArrayList<RFIDTimestamp> times, File file, Timestamp from,
        Timestamp to) throws IOException {

    try {/*from www  .ja  va  2  s  . com*/
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet spreadsheet = workbook.createSheet("Total arbetad tid " + file.getName());
        XSSFRow row = spreadsheet.createRow(0);
        XSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("Frnamn:");
        cell = row.createCell(1);
        cell.setCellValue("Efternamn:");
        cell = row.createCell(2);
        cell.setCellValue("Anstllningsnummer:");
        cell = row.createCell(3);
        cell.setCellValue("Individuell tid (h) from.: " + (from.toString() + " tom.: " + to.toString())
                .replaceAll("(\\s)((\\p{Digit}{2}:){2}\\p{Digit}{2})\\..", ""));
        Double totAllTime = 0.0;
        for (int i = 1; i <= lst.size(); i++) {
            row = spreadsheet.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(lst.get(i - 1).surname);
            cell = row.createCell(1);
            cell.setCellValue(lst.get(i - 1).lastname);
            cell = row.createCell(2);
            cell.setCellValue(lst.get(i - 1).id);
            cell = row.createCell(3);
            cell.setCellValue(lst.get(i - 1).time);
            totAllTime += lst.get(i - 1).time;
            if (i == lst.size()) {
                row = spreadsheet.createRow(i + 1);
                cell = row.createCell(3);
                cell.setCellValue("Sammanstllning:");
                row = spreadsheet.createRow(i + 2);
                cell = row.createCell(3);
                cell.setCellValue(totAllTime);
            }

        }

        XSSFSheet spreadsheetTimes = workbook.createSheet("Tider");
        row = spreadsheetTimes.createRow(0);
        cell = row.createCell(0);
        cell.setCellValue("RFID");
        cell = row.createCell(1);
        cell.setCellValue("IN/UT");
        cell = row.createCell(2);
        cell.setCellValue("Datum/Tid");

        for (int i = 1; i <= times.size(); i++) {
            row = spreadsheetTimes.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(times.get(i - 1).getRFID().toString());
            cell = row.createCell(1);
            cell.setCellValue(times.get(i - 1).getInOut());
            cell = row.createCell(2);
            cell.setCellValue(times.get(i - 1).getTime());
        }
        for (int k = 0; k < spreadsheet.getRow(0).getLastCellNum(); k++) {
            spreadsheet.autoSizeColumn(k);
        }
        for (int j = 0; j < spreadsheetTimes.getRow(0).getLastCellNum(); j++) {
            spreadsheetTimes.autoSizeColumn(j);
        }

        try (FileOutputStream out = new FileOutputStream(file)) {
            workbook.write(out);
        }
        System.out.println(file.getName() + " written successfully");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

---

From source file:courtscheduler.persistence.CourtScheduleIO.java

License:Apache License

private XSSFSheet initializePage(XSSFWorkbook workbook, String conference) {
    // Create a new sheet with titles and headings for each new conference
    XSSFSheet sheet = workbook.createSheet(conference);
    rowNumber = 0;//from w w  w .  ja  v  a 2  s .co  m
    Row header = sheet.createRow(rowNumber);

    // Set sheet to Landscape so all columns will fit on one page
    XSSFPrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setOrientation(PrintOrientation.LANDSCAPE);

    // Column widths determined by specific sizes of heading strings (further down)
    sheet.setColumnWidth(0, 7424);
    sheet.setColumnWidth(1, 1024);
    sheet.setColumnWidth(2, 7424);
    sheet.setColumnWidth(3, 3072);
    sheet.setColumnWidth(4, 2816);
    sheet.setColumnWidth(5, 2816);
    sheet.setColumnWidth(6, 2403);
    sheet.setColumnWidth(7, 1792);

    header.createCell(0).setCellValue("THE COURTS");

    header.createCell(2).setCellValue("Game Schedule");
    rowNumber = rowNumber + 2;

    header = sheet.createRow(rowNumber);
    header.createCell(0).setCellValue("Conference " + conference);
    rowNumber = rowNumber + 2;

    header = sheet.createRow(rowNumber);
    header.createCell(0).setCellValue("TEAM");
    header.createCell(1).setCellValue(" ");
    header.createCell(2).setCellValue("OPPONENT");
    header.createCell(3).setCellValue("CONFERENCE");
    header.createCell(4).setCellValue("DAY");
    header.createCell(5).setCellValue("DATE");
    header.createCell(6).setCellValue("TIME");
    header.createCell(7).setCellValue("COURT");

    return sheet;
}

---

From source file:cv_extractor.DocReader.java

protected static void generateExcel(String directory, String fileName) {
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Email Address");

    //Create CellStyle for header (First row)
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    Font font = sheet.getWorkbook().createFont();
    font.setBold(true);/* w w w.  java2 s.c o m*/
    font.setFontHeightInPoints((short) 16);
    cellStyle.setFont(font);

    //Create first row (Header)
    Row header = sheet.createRow(0);

    //Creating a cell (Only one required, for single attribute E-mail)
    Cell cellTitle = header.createCell(1);
    cellTitle.setCellStyle(cellStyle);
    cellTitle.setCellValue("E-Mail");

    //Counter to use while creating further rows, starting from row 1
    int rowNum = 1;

    //Iterate over data and write to sheet   
    Iterator<String> iterator = data.iterator();

    while (iterator.hasNext()) {
        //Create a row for each E-mail string
        Row row = sheet.createRow(rowNum++);

        //Create a cell to put the E-mail string
        Cell cell = row.createCell(1);

        //Put value in the cell
        cell.setCellValue(iterator.next());
    }

    try {
        //Write the workbook in file system

        if (fileName.equals("")) {
            fileName = "Email List";
        }

        FileOutputStream out = new FileOutputStream(new File(directory + "/" + fileName + ".xlsx"));
        workbook.write(out);
        out.close();

        JOptionPane.showMessageDialog(null, "File generated !");

    } catch (Exception e) {
        e.printStackTrace();
    }
}

---

From source file:cz.lbenda.dataman.db.ExportTableData.java

License:Apache License

/** Write rows to XLSX file
 * @param sqlQueryRows rows//from  ww w .  j a  va 2s .c om
 * @param sheetName name of sheet where is data write
 * @param outputStream stream where are data write */
public static void writeSqlQueryRowsToXLSX(SQLQueryRows sqlQueryRows, String sheetName,
        OutputStream outputStream) throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet(sheetName);
    XSSFRow headerRow = sheet.createRow(0);
    int c = 0;
    for (ColumnDesc columnDesc : sqlQueryRows.getMetaData().getColumns()) {
        XSSFCell cell = headerRow.createCell(c);
        cell.setCellValue(columnDesc.getName());
        c++;
    }
    int r = 1;
    for (RowDesc row : sqlQueryRows.getRows()) {
        XSSFRow xlsxRow = sheet.createRow(r);
        c = 0;
        for (ColumnDesc columnDesc : sqlQueryRows.getMetaData().getColumns()) {
            XSSFCell cell = xlsxRow.createCell(c);
            cell.setCellValue(row.getColumnValueStr(columnDesc));
            c++;
        }
        r++;
    }
    wb.write(outputStream);
}

---

• «
• 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
• »