Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createSheet

Introduction

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

Prototype

@Override
public HSSFSheet createSheet(String sheetname)

Source Link

Document

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

Usage

From source file:controllers.transformer.ExcelTransformer.java

License:Open Source License

public byte[] getBytes() {
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");

    if (results == null) {
        results = survey.resultCollection;
    }/* w  w  w.  ja  v a2 s.c  o  m*/

    /** Header **/
    HSSFRow row = sheet.createRow(0);
    int fieldcounter = 0;
    row.createCell(fieldcounter++).setCellValue("ResultId");
    row.createCell(fieldcounter++).setCellValue("SurveyId");
    row.createCell(fieldcounter++).setCellValue("Title");
    row.createCell(fieldcounter++).setCellValue("Start time");
    row.createCell(fieldcounter++).setCellValue("End time");
    row.createCell(fieldcounter++).setCellValue("Date Sent");
    row.createCell(fieldcounter++).setCellValue("User");
    row.createCell(fieldcounter++).setCellValue("Phone Number");
    row.createCell(fieldcounter++).setCellValue("Lat");
    row.createCell(fieldcounter++).setCellValue("Lon");

    /** Header Fields**/
    for (Question question : survey.getQuestions()) {
        row.createCell(fieldcounter++).setCellValue(question.label);
    }

    int countrow = 0;
    row = sheet.createRow(++countrow);

    //SimpleDateFormat dateFormat = new SimpleDateFormat("EEE, d MMM yyyy HH:mm:ss Z");
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss Z");

    for (NdgResult result : results) {
        fieldcounter = 0;
        row.createCell(fieldcounter++).setCellValue(result.resultId);
        row.createCell(fieldcounter++).setCellValue(result.survey.surveyId);
        row.createCell(fieldcounter++).setCellValue(result.title);
        row.createCell(fieldcounter++).setCellValue(dateFormat.format(result.startTime));
        row.createCell(fieldcounter++).setCellValue(dateFormat.format(result.endTime));

        if (result.dateSent != null) {
            row.createCell(fieldcounter++).setCellValue(dateFormat.format(result.dateSent));
        } else {
            row.createCell(fieldcounter++).setCellValue("");
        }

        row.createCell(fieldcounter++).setCellValue(result.ndgUser.username);
        row.createCell(fieldcounter++).setCellValue(result.ndgUser.phoneNumber);
        row.createCell(fieldcounter++).setCellValue(result.latitude);
        row.createCell(fieldcounter++).setCellValue(result.longitude);

        for (Question question : survey.getQuestions()) {//to ensure right answer order
            Collection<Answer> answers = CollectionUtils.intersection(question.answerCollection,
                    result.answerCollection);//only one should left, hope that it does not modify results
            if (answers.isEmpty()) {
                row.createCell(fieldcounter++).setCellValue("");
            } else if (answers.size() == 1) {
                Answer answer = answers.iterator().next();
                if (answer.question.questionType.typeName.equalsIgnoreCase(QuestionTypesConsts.IMAGE)) {//TODO handle other binary data
                    row.createCell(fieldcounter++).setCellValue(storeImagesAndGetValueToExport(survey.surveyId,
                            result.resultId, answer.id, answer.binaryData));
                } else if (answer.question.questionType.typeName.equalsIgnoreCase(QuestionTypesConsts.INT)) {
                    Integer value = Integer.valueOf(answer.textData);
                    row.createCell(fieldcounter++).setCellValue(value);
                } else if (answer.question.questionType.typeName
                        .equalsIgnoreCase(QuestionTypesConsts.DECIMAL)) {
                    Float value = Float.valueOf(answer.textData);
                    row.createCell(fieldcounter++).setCellValue(value);
                } else {
                    String value = answer.textData;
                    value = value.trim().replaceAll("\n", "");
                    row.createCell(fieldcounter++).setCellValue(value);
                }
            } else {
                Logger.getAnonymousLogger().log(Level.WARNING,
                        "to many answers. ResID={0}questioId={1}answerCount={2}",
                        new Object[] { result.resultId, question.id, question.answerCollection.size() });
                break;
            }
        }
        row = sheet.createRow(++countrow);
    }
    try {
        wb.write(out);
    } catch (IOException e) {
        e.printStackTrace();
    }
    return out.toByteArray();
}

From source file:corner.orm.tapestry.service.excel.ExcelService.java

License:Apache License

/**
 * ??DB?HSSFWorkbook/* w  w w. jav a  2 s  .  co  m*/
 * FIXME:HSSFWorkbook.getBytes()??excel??POIbug,HSSFWorkbook
 * 
 * @param page
 *            ?ServicePage
 * @param enableTitle            ?exceltitle
 * @param model
 *            ??TableView{@link ITableModel}
 * @return {@link HSSFWorkbook}
 */
protected HSSFWorkbook generateExcelData(IPage page, boolean enableTitle, ITableModel model) {

    // create workbook
    HSSFWorkbook wb = new HSSFWorkbook();

    // create sheet
    HSSFSheet sheet1 = wb.createSheet("sheet");

    // create title
    if (enableTitle) {
        createExcelTitleRow(wb, sheet1, model.getColumnModel().getColumns());
    }
    // create excel body
    createExcelDataRows(wb, sheet1, model, enableTitle);
    return wb;
}

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

License:Apache License

/** Write rows to XLS file
 * @param sqlQueryRows rows/*from w  w  w .  j a v a2s  . c  om*/
 * @param sheetName name of sheet where is data write
 * @param outputStream stream where are data write */
public static void writeSqlQueryRowsToXLS(SQLQueryRows sqlQueryRows, String sheetName,
        OutputStream outputStream) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(sheetName);
    HSSFRow headerRow = sheet.createRow(0);
    int c = 0;
    for (ColumnDesc columnDesc : sqlQueryRows.getMetaData().getColumns()) {
        HSSFCell cell = headerRow.createCell(c);
        cell.setCellValue(columnDesc.getName());
        c++;
    }
    int r = 1;
    for (RowDesc row : sqlQueryRows.getRows()) {
        HSSFRow xlsxRow = sheet.createRow(r);
        c = 0;
        for (ColumnDesc columnDesc : sqlQueryRows.getMetaData().getColumns()) {
            HSSFCell cell = xlsxRow.createCell(c);
            cell.setCellValue(row.getColumnValueStr(columnDesc));
            c++;
        }
        r++;
    }
    wb.write(outputStream);
}

From source file:data.pkg.ReadWriteExcelFile.java

public static void writeXLSFile(String fileName, Data data) throws IOException {

    String excelFileName = fileName;//name of excel file

    String sheetName = "Sheet1";//name of sheet

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(sheetName);

    //iterating r number of rows
    for (int r = 0; r < data.getLength(); r++) {
        HSSFRow row = sheet.createRow(r);

        //iterating c number of columns
        for (int c = 0; c < 2; c++) {
            if (r == 0 && c == 0) {
                HSSFCell cell = row.createCell(c);
                cell.setCellValue("Deslocamento");
            }//from w w w  . j av a2s. c  o  m
            if (r == 0 && c == 1) {
                HSSFCell cell = row.createCell(c);
                cell.setCellValue("Fora");
            }
            if (r > 0 && c == 0) {
                HSSFCell cell = row.createCell(c);

                cell.setCellValue(data.getDeslocamento(r - 1));
            }
            if (r > 0 && c == 1) {
                HSSFCell cell = row.createCell(c);

                cell.setCellValue(data.getForca(r - 1));
            }
        }
    }

    FileOutputStream fileOut = new FileOutputStream(excelFileName);

    //write this workbook to an Outputstream.
    wb.write(fileOut);
    fileOut.flush();
    fileOut.close();
}

From source file:data.services.BaseParamService.java

public HSSFWorkbook getXls() {
    Logger log = Logger.getLogger(this.getClass());
    try {/*from  w  w  w. j a  v a2 s.co m*/
        HSSFWorkbook workbook = new HSSFWorkbook();
        List<BaseParam> bpList = getParams();
        HSSFSheet sheet = workbook.createSheet("FirstSheet");
        HSSFRow rowhead = sheet.createRow((short) 0);
        rowhead.createCell(0).setCellValue("UID");
        rowhead.createCell(1).setCellValue("????");
        rowhead.createCell(2).setCellValue("");
        rowhead.createCell(3).setCellValue("??");
        rowhead.createCell(4).setCellValue("??");
        rowhead.createCell(5).setCellValue("??");
        rowhead.createCell(6).setCellValue("??");
        rowhead.createCell(7).setCellValue("?");
        rowhead.createCell(8).setCellValue("?");
        int n = 1;
        if (!bpList.isEmpty()) {
            for (BaseParam bp : bpList) {
                HSSFRow rowbody = sheet.createRow((short) n);
                rowbody.createCell(0).setCellValue(StringAdapter.getString(bp.getUid().trim()));
                rowbody.createCell(1).setCellValue(StringAdapter.getString(bp.getName()));
                rowbody.createCell(2).setCellValue(StringAdapter.getString(bp.getParamType().getName()));
                rowbody.createCell(3).setCellValue(StringAdapter.getString(bp.getStaticType().getName()));
                rowbody.createCell(4).setCellValue(StringAdapter.getString(getPercentParamsAsString(bp)));
                rowbody.createCell(5).setCellValue(StringAdapter.getString(bp.getRadical()));
                rowbody.createCell(6).setCellValue(StringAdapter.getString(bp.getAudial()));
                rowbody.createCell(7).setCellValue(StringAdapter.getString(bp.getVisual()));
                rowbody.createCell(8).setCellValue(StringAdapter.getString(bp.getKinestet()));
                n++;
            }
            ;
        }
        return workbook;
    } catch (Exception e) {
        log.warn("HSSFWorkbook.getXls()", e);
    }
    return null;
}

From source file:data.services.CarCompletionOptionService.java

public HSSFWorkbook getXls() {
    Logger log = Logger.getLogger(this.getClass());
    try {/*from  ww w.j  a  va 2s. c  o m*/
        HSSFWorkbook workbook = new HSSFWorkbook();
        List<CarCompletionOption> oblist = carCompletionOptionDao.getAllAsc();
        HSSFSheet sheet = workbook.createSheet("FirsSheet");
        HSSFRow rowhead = sheet.createRow((short) 0);
        rowhead.createCell(0).setCellValue("ID");
        rowhead.createCell(1).setCellValue("TITLE");
        rowhead.createCell(2).setCellValue(" ");
        rowhead.createCell(3).setCellValue("UID");
        rowhead.createCell(4).setCellValue("");
        rowhead.createCell(5).setCellValue("");
        rowhead.createCell(6).setCellValue("");
        rowhead.createCell(7).setCellValue("?");
        rowhead.createCell(8).setCellValue("");
        rowhead.createCell(9).setCellValue("?");
        int n = 1;
        if (!oblist.isEmpty()) {
            for (CarCompletionOption ob : oblist) {
                HSSFRow rowbody = sheet.createRow((short) n);
                rowbody.createCell(0).setCellValue(StringAdapter.getString(ob.getCcoId()));
                rowbody.createCell(1).setCellValue(StringAdapter.getString(ob.getTitle()));
                rowbody.createCell(2).setCellValue(StringAdapter.getString(ob.getTitleFull()));
                rowbody.createCell(3).setCellValue(StringAdapter.getString(ob.getUid()));
                rowbody.createCell(4)
                        .setCellValue(StringAdapter.getString(ob.getParamValue()).replace(".", ","));
                rowbody.createCell(5).setCellValue(StringAdapter.getString(ob.getPercentValue()));
                rowbody.createCell(6).setCellValue(StringAdapter.getString(ob.getRadical()));
                rowbody.createCell(7).setCellValue(StringAdapter.getString(ob.getAudial()));
                rowbody.createCell(8).setCellValue(StringAdapter.getString(ob.getVisual()));
                rowbody.createCell(9).setCellValue(StringAdapter.getString(ob.getKinestet()));
                n++;
            }
        }
        return workbook;
    } catch (Exception e) {
        log.warn("HSSFWorkbook.getXls()", e);
    }
    return null;
}

From source file:data.services.CarOptionValueService.java

public HSSFWorkbook getXls() {

    Logger log = Logger.getLogger(this.getClass());
    try {/* w ww.ja v a 2 s .c  o  m*/
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("FirstSheet");
        HSSFRow rowhead = sheet.createRow((short) 0);
        rowhead.createCell(0).setCellValue("ID");
        rowhead.createCell(1).setCellValue("UID");
        rowhead.createCell(2).setCellValue("??");
        rowhead.createCell(3).setCellValue("??");
        rowhead.createCell(4).setCellValue("");
        rowhead.createCell(5).setCellValue("");
        rowhead.createCell(6).setCellValue("");
        rowhead.createCell(7).setCellValue("?");
        rowhead.createCell(8).setCellValue("");
        rowhead.createCell(9).setCellValue("?");
        List<CarOptionValue> covlist = getUniqueOptionNames(Long.valueOf(0));
        int n = 1;
        if (!covlist.isEmpty()) {
            for (CarOptionValue cov : covlist) {
                HSSFRow rowbody = sheet.createRow((short) n);
                String desc = cov.getDescription();
                if (desc.equals("")) {
                    desc = " ??";
                }
                rowbody.createCell(0).setCellValue(StringAdapter.getString(cov.getId()));
                rowbody.createCell(1).setCellValue(StringAdapter.getString(cov.getCCO().getUid()));
                rowbody.createCell(2).setCellValue(StringAdapter.getString(cov.getCCO().getTitle()));
                rowbody.createCell(3).setCellValue(desc);
                rowbody.createCell(4).setCellValue(StringAdapter.getString(cov.getRadical()));
                rowbody.createCell(5)
                        .setCellValue(StringAdapter.getString(cov.getParamValue()).replace(".", ","));
                rowbody.createCell(6).setCellValue(StringAdapter.getString(cov.getPercentValue()));
                rowbody.createCell(7).setCellValue(StringAdapter.getString(cov.getAudial()));
                rowbody.createCell(8).setCellValue(StringAdapter.getString(cov.getVisual()));
                rowbody.createCell(9).setCellValue(StringAdapter.getString(cov.getKinestet()));
                n++;
            }
            ;
        }
        return workbook;
    } catch (Exception e) {
        log.warn("HSSFWorkbook.getXls()", e);
    }
    return null;
}

From source file:data.services.CarPropertyService.java

public HSSFWorkbook getXls() {
    Logger log = Logger.getLogger(this.getClass());
    try {//w  w w.j  a  v a2s .c o m
        HSSFWorkbook workbook = new HSSFWorkbook();
        List<CarProperty> oblist = carPropertyDao.getAllAsc();
        HSSFSheet sheet = workbook.createSheet("FirsSheet");
        HSSFRow rowhead = sheet.createRow((short) 0);
        rowhead.createCell(0).setCellValue("ID");
        rowhead.createCell(1).setCellValue("TITLE");
        rowhead.createCell(2).setCellValue("");
        rowhead.createCell(3).setCellValue("UID");
        rowhead.createCell(4).setCellValue("");
        rowhead.createCell(5).setCellValue("");
        rowhead.createCell(6).setCellValue("");
        int n = 1;
        if (!oblist.isEmpty()) {
            for (CarProperty ob : oblist) {
                HSSFRow rowbody = sheet.createRow((short) n);
                rowbody.createCell(0).setCellValue(StringAdapter.getString(ob.getCpId()));
                rowbody.createCell(1).setCellValue(StringAdapter.getString(ob.getTitle()));
                rowbody.createCell(2).setCellValue(StringAdapter.getString(ob.getCPG().getTitle()));
                rowbody.createCell(3).setCellValue(StringAdapter.getString(ob.getUid()));
                rowbody.createCell(4)
                        .setCellValue(StringAdapter.getString(ob.getParamValue()).replace(".", ","));
                rowbody.createCell(5).setCellValue(StringAdapter.getString(ob.getPercentValue()));
                rowbody.createCell(6).setCellValue(StringAdapter.getString(ob.getRadical()));
                n++;
            }
            ;
        }
        return workbook;
    } catch (Exception e) {
        log.warn("HSSFWorkbook.getXls()", e);
    }
    return null;
}

From source file:data.services.ColorGroupService.java

public HSSFWorkbook getXls() {
    Logger log = Logger.getLogger(this.getClass());
    try {//from   w w  w  . ja v a2 s.co m
        HSSFWorkbook workbook = new HSSFWorkbook();
        List<ColorGroup> oblist = colorGroupDao.getAllAsc();
        HSSFSheet sheet = workbook.createSheet("FirsSheet");
        HSSFRow rowhead = sheet.createRow((short) 0);
        rowhead.createCell(0).setCellValue("ID");
        rowhead.createCell(1).setCellValue("TITLE");
        rowhead.createCell(2).setCellValue("????");
        rowhead.createCell(3).setCellValue("UID");
        rowhead.createCell(4).setCellValue("");
        rowhead.createCell(5).setCellValue("");
        rowhead.createCell(6).setCellValue("");
        int n = 1;
        if (!oblist.isEmpty()) {
            for (ColorGroup ob : oblist) {
                HSSFRow rowbody = sheet.createRow((short) n);
                rowbody.createCell(0).setCellValue(StringAdapter.getString(ob.getColorGroupId()));
                rowbody.createCell(1).setCellValue(StringAdapter.getString(ob.getName()));
                rowbody.createCell(2).setCellValue(StringAdapter.getString(ob.getTitle()));
                rowbody.createCell(3).setCellValue(StringAdapter.getString(ob.getUid()));
                rowbody.createCell(4).setCellValue(StringAdapter.getString(ob.getParamValue()));
                rowbody.createCell(5).setCellValue(StringAdapter.getString(ob.getPercentValue()));
                rowbody.createCell(6).setCellValue(StringAdapter.getString(ob.getRadical()));
                n++;
            }
            ;
        }
        return workbook;
    } catch (Exception e) {
        log.warn("HSSFWorkbook.getXls()", e);
    }
    return null;
}

From source file:data.services.ColorService.java

public HSSFWorkbook getXls() {
    Logger log = Logger.getLogger(this.getClass());
    try {/*  ww  w  . j a v a 2  s  . c om*/
        HSSFWorkbook workbook = new HSSFWorkbook();
        List<Color> oblist = colorDao.getAllAsc();
        HSSFSheet sheet = workbook.createSheet("FirstSheet");
        HSSFRow rowhead = sheet.createRow((short) 0);
        rowhead.createCell(0).setCellValue("ID");
        rowhead.createCell(1).setCellValue("TITLE");
        rowhead.createCell(2).setCellValue("????");
        rowhead.createCell(3).setCellValue("UID");
        rowhead.createCell(4).setCellValue("");
        rowhead.createCell(5).setCellValue("");
        rowhead.createCell(6).setCellValue("");
        rowhead.createCell(7).setCellValue("?");
        rowhead.createCell(8).setCellValue("");
        rowhead.createCell(9).setCellValue("?");
        int n = 1;
        if (!oblist.isEmpty()) {
            for (Color ob : oblist) {
                HSSFRow rowbody = sheet.createRow((short) n);
                rowbody.createCell(0).setCellValue(StringAdapter.getString(ob.getColorId()));
                rowbody.createCell(1).setCellValue(StringAdapter.getString(ob.getName()));
                rowbody.createCell(2).setCellValue(StringAdapter.getString(ob.getTitle()));
                rowbody.createCell(3).setCellValue(StringAdapter.getString(ob.getUid()));
                rowbody.createCell(4)
                        .setCellValue(StringAdapter.getString(ob.getParamValue()).replace(".", ","));
                rowbody.createCell(5).setCellValue(StringAdapter.getString(ob.getPercentValue()));
                rowbody.createCell(6).setCellValue(StringAdapter.getString(ob.getRadical()));
                rowbody.createCell(7).setCellValue(StringAdapter.getString(ob.getAudial()));
                rowbody.createCell(8).setCellValue(StringAdapter.getString(ob.getVisual()));
                rowbody.createCell(9).setCellValue(StringAdapter.getString(ob.getKinestet()));
                n++;
            }
            ;
        }
        return workbook;
    } catch (Exception e) {
        log.warn("HSSFWorkbook.getXls()", e);
    }
    return null;
}