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

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. 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.jornada.server.service.GWTServiceNotaImpl.java

License:Open Source License

public String getExcelBoletimAnual(ArrayList<TableMultipleBoletimAnual> listTableMBD) {

    XSSFWorkbook wb = new XSSFWorkbook();
    for (int i = 0; i < listTableMBD.size(); i++) {
        TableMultipleBoletimAnual tableMBD = listTableMBD.get(i);
        String strTab = Integer.toString(i + 1) + ") ";
        strTab += tableMBD.getNomeCurso().substring(0, 3);
        XSSFSheet sheet = wb.createSheet(strTab);
        NotaServer.getExcelBoletimAnual(wb, sheet, tableMBD.getIdCurso());
    }//from   w  ww . jav a 2 s  .com
    return ExcelFramework.getExcelAddress(wb, "GerarExcelBoletimAnual_");
}

From source file:com.jornada.server.service.GWTServiceNotaImpl.java

License:Open Source License

public String getExcelBoletimDisciplina(int idCurso, int idPeriodo, int idDisciplina) {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet("Boletim Disciplina");
    NotaServer.gerarExcelBoletimDisciplina(wb, sheet, idCurso, idPeriodo, idDisciplina);
    return ExcelFramework.getExcelAddress(wb, "GerarExcelBoletimDisciplina_");
}

From source file:com.jornada.server.service.GWTServiceNotaImpl.java

License:Open Source License

public String getExcelBoletimDisciplina(ArrayList<TableMultipleBoletimDisciplina> listTableMBD) {
    XSSFWorkbook wb = new XSSFWorkbook();
    for (int i = 0; i < listTableMBD.size(); i++) {
        TableMultipleBoletimDisciplina tableMBD = listTableMBD.get(i);
        String strTab = Integer.toString(i + 1) + ") ";
        strTab += tableMBD.getNomeCurso().substring(0, 3) + "-";
        strTab += tableMBD.getNomePeriodo().substring(0, 3) + "-";
        strTab += tableMBD.getNomeDisciplina().substring(0, 3);
        XSSFSheet sheet = wb.createSheet(strTab);
        NotaServer.gerarExcelBoletimDisciplina(wb, sheet, tableMBD.getIdCurso(), tableMBD.getIdPeriodo(),
                tableMBD.getIdDisciplina());
    }//  w w w. j  ava2 s. com

    return ExcelFramework.getExcelAddress(wb, "GerarExcelBoletimDisciplina_");
}

From source file:com.khartec.waltz.web.endpoints.extracts.BaseDataExtractor.java

License:Open Source License

private Object writeAsExcel(String suggestedFilenameStem, Select<?> qry, Response response) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet(sanitizeSheetName(suggestedFilenameStem));

    writeExcelHeader(qry, sheet);//  w w w  . ja v a2  s.c  o m
    writeExcelBody(qry, sheet);

    sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, qry.fields().length));
    sheet.createFreezePane(0, 1);

    byte[] bytes = convertExcelToByteArray(workbook);

    HttpServletResponse httpResponse = response.raw();

    httpResponse.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    httpResponse.setHeader("Content-Disposition", "attachment; filename=" + suggestedFilenameStem + ".xlsx");
    httpResponse.setHeader("Content-Transfer-Encoding", "7bit");

    httpResponse.setContentLength(bytes.length);
    httpResponse.getOutputStream().write(bytes);
    httpResponse.getOutputStream().flush();
    httpResponse.getOutputStream().close();

    return httpResponse;
}

From source file:com.l3.info.magenda.emplois_du_temps.Semaine.java

void writeInSheet(Workbook workbook, int week_of_year) {

    XSSFWorkbook xssfWorkbook = workbook.getWorkbook();
    Sheet sheet = xssfWorkbook.createSheet("Sem. " + week_of_year);
    Row row = sheet.createRow((short) 0);

    row.setHeight(Workbook.PixelsToTwips(64));
    Cell cell = row.createCell((short) 0);

    // first row (0-based) - last row  (0-based) - first column (0-based) -last column  (0-based)
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (20 - 7) * 2 + 2));

    // Cree une nouvelle police
    Font font = xssfWorkbook.createFont();
    font.setFontHeightInPoints((short) 18);
    font.setFontName("Arial");

    // Fonts are set into a style so create a new one to use.
    XSSFCellStyle style = xssfWorkbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setFont(font);/*from w  ww  .j  a va2  s .  co m*/

    // Create a cell and put a value in it.
    cell.setCellValue("Semaine " + this.num_semaine);
    cell.setCellStyle(style);

    sheet.setDefaultRowHeight((short) 500);

    int x = 0, y = 2;
    for (Jour day : days_of_week) {
        day.writeInSheet(workbook, sheet, x, y);
        y += 2 + day.getNbrLigne();
    }
}

From source file:com.liferay.events.global.mobile.portlet.PollsPortlet.java

License:Open Source License

@Override
public void serveResource(ResourceRequest request, ResourceResponse response)
        throws PortletException, IOException {
    // do search and return result
    String cmd = ParamUtil.getString(request, "cmd");
    long questionId = ParamUtil.getLong(request, "questionId");

    EventPollQuestion question;//from  w w w  .  jav a2 s .c o m
    List<EventPollAnswer> answers;
    try {
        question = EventPollQuestionLocalServiceUtil.getEventPollQuestion(questionId);
        answers = EventPollAnswerLocalServiceUtil.getAllAnswerObjs(questionId);
    } catch (SystemException e) {
        throw new PortletException("Cannot get answers for questionId " + questionId);
    } catch (PortalException e) {
        throw new PortletException("Cannot get question or answers for questionId " + questionId);
    }

    if (Validator.equals(cmd, "exportAnswersCSV")) {

        File f = FileUtil.createTempFile();

        CSVWriter writer = new CSVWriter(new FileWriter(f), ',');

        // find out all headers
        List<String> headers = new ArrayList<String>();
        headers.add("ID");
        headers.add("RAW ANSWER");

        Set<String> payloadHeaders = new HashSet<String>();

        for (EventPollAnswer answer : answers) {
            JSONObject payloadObj = null;

            try {
                payloadObj = JSONFactoryUtil.createJSONObject(answer.getPayload());
                if (Validator.isNull(payloadObj)) {
                    continue;
                }
            } catch (JSONException e) {
                throw new PortletException("cannot read payload: " + answer.getPayload());
            }
            Iterator<String> keyIt = payloadObj.keys();

            while (keyIt.hasNext()) {
                String key = keyIt.next();
                payloadHeaders.add(key);
            }
        }

        headers.addAll(payloadHeaders);

        Map<String, Integer> headerCols = new HashMap<String, Integer>();
        for (int i = 0; i < headers.size(); i++) {
            headerCols.put(headers.get(i), i);
        }

        // now print them
        writer.writeNext(headers.toArray(new String[] {}));

        for (EventPollAnswer answer : answers) {
            List<String> vals = new ArrayList<String>();

            JSONObject payloadObj = null;

            try {
                payloadObj = JSONFactoryUtil.createJSONObject(answer.getPayload());
                if (Validator.isNull(payloadObj)) {
                    continue;
                }
            } catch (JSONException e) {
                throw new PortletException("cannot read payload: " + answer.getPayload());
            }

            for (String headerCol : headers) {
                String val;
                if (headerCol.equals("ID")) {
                    val = String.valueOf(answer.getAnswerId());
                } else if (headerCol.equals("RAW ANSWER")) {
                    val = String.valueOf(answer.getAnswer());
                } else {
                    val = payloadObj.getString(headerCol);
                }

                if (Validator.isNull(val)) {
                    val = "";
                }
                vals.add(val);
            }

            writer.writeNext(vals.toArray(new String[] {}));
        }
        writer.flush();
        writer.close();
        PortletResponseUtil.sendFile(request, response,
                question.getShortTitle().replaceAll("[^0-9A-Za-z]", "-") + ".csv", new FileInputStream(f));

    } else if (Validator.equals(cmd, "exportAnswersXLSX")) {

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Poll Answers");

        Row headerRow = sheet.createRow(0);
        Cell headerCell = headerRow.createCell(0);
        headerCell.setCellValue("ID");

        headerCell = headerRow.createCell(1);
        headerCell.setCellValue("Raw Answer");

        HashMap<String, Integer> rowMap = new HashMap<String, Integer>();

        int currentRow = 1;
        int nextHeaderCol = 2;
        for (EventPollAnswer answer : answers) {
            Row row = sheet.createRow(currentRow);
            currentRow++;
            JSONObject payloadObj = null;
            long answerId = answer.getAnswerId();
            Cell idCell = row.createCell(0);
            idCell.setCellValue(String.valueOf(answerId));

            try {
                payloadObj = JSONFactoryUtil.createJSONObject(answer.getPayload());
                if (Validator.isNull(payloadObj)) {
                    continue;
                }
            } catch (JSONException e) {
                throw new PortletException("cannot read payload: " + answer.getPayload());
            }

            Cell answerCell = row.createCell(1);
            answerCell.setCellValue(String.valueOf(answer.getAnswer()));

            Iterator<String> keyIt = payloadObj.keys();

            while (keyIt.hasNext()) {
                String key = keyIt.next();
                Integer headerCol = rowMap.get(key);
                if (Validator.isNull(headerCol)) {
                    rowMap.put(key, nextHeaderCol);
                    Cell nextHeaderCell = headerRow.createCell(nextHeaderCol);
                    nextHeaderCell.setCellValue(key.toUpperCase());
                    headerCol = nextHeaderCol;
                    nextHeaderCol++;
                }
                Cell cell = row.createCell(headerCol);
                cell.setCellValue(payloadObj.getString(key));
            }
        }

        File f = FileUtil.createTempFile();
        FileOutputStream fos = new FileOutputStream(f);
        workbook.write(fos);
        fos.flush();
        fos.close();
        PortletResponseUtil.sendFile(request, response,
                question.getShortTitle().replaceAll("[^0-9A-Za-z]", "-") + ".xlsx", new FileInputStream(f));

    }
}

From source file:com.MainGui.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
    // JOptionPane.showMessageDialog(null, evt.getActionCommand());
    if (evt.getActionCommand().equals("Save Details")) {
        try {/*from  w  ww .  ja  v a 2  s.com*/
            FuntionLibrary fb = new FuntionLibrary();
            File file = new File(excelFileName);
            XSSFWorkbook wb;
            XSSFSheet sheet;
            if (file.exists()) {
                FileInputStream fis = new FileInputStream(file);
                wb = new XSSFWorkbook(fis);
                sheet = wb.getSheet(sheetName);
            } else {
                wb = new XSSFWorkbook();
                sheet = wb.createSheet(sheetName);
            }
            mailId = fb.validateEmail(jTextField1.getText(), sheet);
            pass = fb.validatePassword(jTextField3.getText());
            sheet = wb.getSheet(sheetName);
            mob = fb.validateMobileNO(jFormattedTextField1.getText(), sheet);
            if (mob != null && mailId != null && FuntionLibrary.Pflag == true && FuntionLibrary.Mflag == true
                    && FuntionLibrary.Eflag == true) {
                fb.writetoExcel(jTextField4.getText(), mailId, pass, mob, sheet, jTextField2.getText());
                FileOutputStream fileOut = new FileOutputStream(excelFileName);
                //write this workbook to an Outputstream.
                wb.write(fileOut);
                fileOut.flush();
                fileOut.close();
                System.out.println("Your excel file has been generated!");
                JOptionPane.showMessageDialog(null,
                        " Data Base Updated Successfully !!\n Path of data file is " + excelFileName);
            } else {
                JOptionPane.showMessageDialog(null, "Please enter required fields correctly. Thanks !!");
            }
            jTextField1.setText("");
            jTextField3.setText("");
            jTextField2.setText("");
            jTextField4.setText("");
            jFormattedTextField1.setText("");
        } catch (InvalidFormatException | IOException ex) {
            Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "Error in Saving Data");
        }
    }
}

From source file:com.netsteadfast.greenstep.bsc.command.PdcaReportExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {

    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    int row = 0;//from  ww w  . ja v a2s.c  o m
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh1 = wb.createSheet("PDCA Report");

    row += this.createPdca(wb, sh1, row, context);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "pdca-report.xlsx");
    file = null;
    return oid;
}

From source file:com.opendoorlogistics.speedregions.excelshp.io.ExcelWriter.java

License:Apache License

public static void writeSheets(File file, ExportTable... tables) {
    // create empty workbook with a bold font style
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFCellStyle headerStyle = wb.createCellStyle();
    XSSFFont boldfont = wb.createFont();
    boldfont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    boldfont.setFontHeight(12);/*  w  w w  .j av a2 s  .  co  m*/
    headerStyle.setFont(boldfont);

    // fill workbook
    for (ExportTable table : tables) {
        Sheet sheet = wb.createSheet(table.getName());

        Row headerRow = sheet.createRow(0);
        for (int c = 0; c < table.getHeader().size(); c++) {
            Cell cell = headerRow.createCell(c);
            cell.setCellStyle(headerStyle);
            cell.setCellValue(table.getHeader().get(c).getName());
        }

        List<List<String>> rows = table.getRows();

        int nr = rows.size();
        for (int r = 0; r < nr; r++) {
            Row row = sheet.createRow(r + 1);
            List<String> srcRow = rows.get(r);
            int nc = srcRow.size();
            for (int c = 0; c < nc; c++) {
                //JsonFormatTypes type = table.getColumnType(c);
                Cell cell = row.createCell(c);
                String value = srcRow.get(c);

                writeToCell(value, c < table.getHeader().size() ? table.getHeader().get(c).getFormatType()
                        : JsonFormatTypes.STRING, cell);

            }
        }
    }

    // try saving
    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(file);
        wb.write(fileOut);
    } catch (Exception e) {
        // TODO: handle exception
    } finally {

        try {
            if (fileOut != null) {
                fileOut.close();
            }
        } catch (Exception e2) {
            throw new RuntimeException(e2);
        }

        try {
            if (wb != null) {
                wb.close();
            }
        } catch (Exception e2) {
            throw new RuntimeException(e2);
        }
    }

    LOGGER.info("Wrote Excel file " + file.getAbsolutePath());
}

From source file:com.pe.nisira.movil.view.action.MultitablaAction.java

public StreamedContent downFormatExcel() throws Exception {
    InputStream stream = null;/*w  ww .  j a v a 2s  . c o m*/
    StreamedContent arch = null;
    try {
        String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION";
        File ruta = new File(folder);
        if (!ruta.isDirectory()) {
            ruta.mkdirs();
        }
        String rutaArchivo = folder + "\\FI_MULTITABLA.xlsx";
        File fileXls = new File(rutaArchivo);
        if (fileXls.exists()) {
            fileXls.delete();
        }
        fileXls.createNewFile();
        XSSFWorkbook libro = new XSSFWorkbook();
        FileOutputStream file = new FileOutputStream(fileXls);
        XSSFSheet hoja = libro.createSheet("IMPORTAR_MULTITABLA");
        CreationHelper factory = libro.getCreationHelper();
        hoja = libro.getSheetAt(0);
        XSSFCellStyle style = libro.createCellStyle();
        Font font = libro.createFont();
        Font font1 = libro.createFont();
        Drawing drawing = hoja.createDrawingPatriarch();
        ClientAnchor anchor1 = factory.createClientAnchor();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 8);
        font1.setFontHeightInPoints((short) 8);
        font1.setFontName("Arial");
        font.setFontName("Arial");
        style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70)));
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setAlignment(CellStyle.VERTICAL_CENTER);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(font);
        for (int f = 0; f < 1; f++) {
            XSSFRow fila = hoja.createRow(f);
            for (int c = 0; c < 4; c++) {
                XSSFCell celda = fila.createCell(c);
                celda.setCellStyle(style);
                anchor1.setCol1(celda.getColumnIndex());
                anchor1.setCol2(celda.getColumnIndex() + 4);
                anchor1.setRow1(fila.getRowNum());
                anchor1.setRow2(fila.getRowNum() + 3);
                Comment comment = drawing.createCellComment(anchor1);
                if (f == 0 && c == 0) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Indicar si es es Padre (Usar SI o NO).");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("Es Padre");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 1) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Opcional \n - Escribir la Abreviatura del campo del cual depende este.");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("Abreviatura Padre");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 2) {
                    RichTextString str = factory
                            .createRichTextString("ADM:\nCampo Obligatorio \n - Descripcion de la multitabla");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("DESCRIPCION");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 3) {
                    RichTextString str = factory
                            .createRichTextString("ADM:\nCampo Obligatorio \n - Abreviatura de la multitabla.");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("ABREVIATURA");
                    celda.setCellComment(comment);
                }
            }
        }
        hoja.autoSizeColumn((short) 0);
        hoja.autoSizeColumn((short) 1);
        hoja.autoSizeColumn((short) 2);
        libro.write(file);
        file.close();
        stream = new FileInputStream(new File(rutaArchivo));
        arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_MULTITABLA.xlsx");
    } catch (FileNotFoundException ex) {
        System.out.println("Error al Descargar : " + ex.getMessage());
    }
    return arch;
}