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()

Source Link

Document

create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns the high level representation.

Usage

From source file:HSSFReadWrite.java

License:Apache License

/**
 * given a filename this outputs a sample sheet with just a set of
 * rows/cells.//ww  w  . j  a  v  a 2s. c o m
 */
private static void testCreateSampleSheet(String outputFilename) throws IOException {
    int rownum;
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    f.setFontHeightInPoints((short) 12);
    f.setColor((short) 0xA);
    f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    f2.setFontHeightInPoints((short) 10);
    f2.setColor((short) 0xf);
    f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cs.setFont(f);
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
    cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cs2.setFillPattern((short) 1); // fill w fg
    cs2.setFillForegroundColor((short) 0xA);
    cs2.setFont(f2);
    wb.setSheetName(0, "HSSF Test");
    for (rownum = 0; rownum < 300; rownum++) {
        HSSFRow r = s.createRow(rownum);
        if ((rownum % 2) == 0) {
            r.setHeight((short) 0x249);
        }

        for (int cellnum = 0; cellnum < 50; cellnum += 2) {
            HSSFCell c = r.createCell(cellnum);
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs);
            }
            c = r.createCell(cellnum + 1);
            c.setCellValue(new HSSFRichTextString("TEST"));
            // 50 characters divided by 1/20th of a point
            s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs2);
            }
        }
    }

    // draw a thick black border on the row at the bottom using BLANKS
    rownum++;
    rownum++;
    HSSFRow r = s.createRow(rownum);
    cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK);
    for (int cellnum = 0; cellnum < 50; cellnum++) {
        HSSFCell c = r.createCell(cellnum);
        c.setCellStyle(cs3);
    }
    s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3));
    s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110));

    // end draw thick black border
    // create a sheet, set its title then delete it
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);

    // end deleted sheet
    FileOutputStream out = new FileOutputStream(outputFilename);
    wb.write(out);
    out.close();
}

From source file:bad.robot.excel.DateCellTest.java

License:Apache License

@Test
public void shouldSetDataFormatWhenAddingACell() throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    HSSFRow row = sheet.createRow(0);/*from   w w w.  ja va 2s .  c o m*/
    cell.addTo(row, column(A), workbook);
    assertThat(getCellDataFormatAtCoordinate(coordinate(A, 1), workbook), is("dd-MMM-yyyy"));
}

From source file:bad.robot.excel.DateCellTest.java

License:Apache License

@Test
public void shouldSetDataFormatWhenReplacingACell() throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    HSSFRow row = sheet.createRow(0);/* w  w  w.j a v  a2 s  . c  om*/
    HSSFCell original = row.createCell(0);
    cell.update(original, workbook);
    assertThat(getCellDataFormatAtCoordinate(coordinate(A, 1), workbook), is("dd-MMM-yyyy"));
}

From source file:br.com.deltex.sqlexecutor.core.controller.SqlExecutorController.java

public void exportExcel() throws FileNotFoundException, IOException {
    // create a new workbook       
    HSSFWorkbook workBook = new HSSFWorkbook();
    // create a new sheet
    HSSFSheet sheet = workBook.createSheet();
    // declare a row object reference
    HSSFRow row = null;/*from   ww  w  . j a  v a2 s.c o  m*/
    // declare a cell object reference
    HSSFCell cell = null;

    int rowIndex = 0;
    int cellIndex = 0;

    try {
        for (List<String> rowDataQuery : this.dataQuery) {
            row = sheet.createRow(rowIndex);

            for (String column : rowDataQuery) {
                cell = row.createCell(cellIndex);
                cell.setCellValue(column);

                cellIndex++;
            }
            cellIndex = 0;
            rowIndex++;
        }

        // create a new file
        FileOutputStream out = new FileOutputStream(
                new File("C:/Users/harlan.bruno.santos/Desktop/workbook.xls"));

        workBook.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } catch (IOException e) {
        logger.error(e.getMessage(), e);
        throw e;
    }
}

From source file:Calendar.Calendario.java

private void crearExcel(double[][] excel, String name) {
    HSSFWorkbook libro = new HSSFWorkbook();
    HSSFSheet hoja = libro.createSheet();
    HSSFRow fila;/*from   ww  w  . j  a  va2s .c  om*/
    HSSFCell celda;
    fila = hoja.createRow(2);
    celda = fila.createCell(3);
    HSSFRichTextString texto = new HSSFRichTextString("prueba");
    celda.setCellValue(texto);
    celda = fila.createCell(4);
    celda.setCellValue("prueba2");

    /* for (int i = 0; i < modelo.getColumnCount(); i++) {
    celda = fila.createCell(i);
    HSSFRichTextString texto = new HSSFRichTextString((String) (modelo.getColumnName(i)));
    celda.setCellValue(texto);
    }
    for (int i = 0; i < modelo.getRowCount(); i++) {
    fila = hoja.createRow(i + 1);
    for (int k = 0; k < modelo.getColumnCount(); k++) {
        celda = fila.createCell(k);
        HSSFRichTextString texto = new HSSFRichTextString((String) (modelo.getValueAt(i, k)));
        celda.setCellValue(texto);
    }
    }*/

    FileOutputStream elFichero;
    try {
        elFichero = new FileOutputStream(name + ".xls");
        libro.write(elFichero);
        elFichero.close();
        JOptionPane.showMessageDialog(null, "Se genero la planilla correctamente");
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null,
                "Error al crear la planilla de Excel. Recuerde que el nombre del archivo no puede contener ninguno de los siguientes caracteres: \\ / : * ? \"< > | Intentelo nuevamente y si el problema persiste contacte con el administrador.");
        System.out.println(ex);
        System.exit(0);
    }
}

From source file:cn.edu.zju.acm.onlinejudge.action.ShowRankListAction.java

License:Open Source License

private byte[] exportToExcel(AbstractContest contest, List<Problem> problems, RankList ranklist)
        throws Exception {
    List<RankListEntry> entries = ranklist.getEntries();
    long time = this.getTimeEscaped(contest);

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFRow row = sheet.createRow(0);// w  w  w  . java2s. c  om
    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue(contest.getTitle());
    if (ranklist.getRole() != null) {
        row = sheet.createRow(1);
        cell = row.createCell((short) 0);
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(ranklist.getRole().getDescription());
    }

    row = sheet.createRow(2);
    cell = row.createCell((short) 0);
    cell.setCellValue("Length");
    cell = row.createCell((short) 1);
    cell.setCellValue(Utility.toTime(contest.getLength() / 1000));

    row = sheet.createRow(3);
    cell = row.createCell((short) 0);
    cell.setCellValue("Time Escaped");
    cell = row.createCell((short) 1);
    cell.setCellValue(Utility.toTime(time / 1000));

    row = sheet.createRow(5);
    row.createCell((short) 0).setCellValue("Rank");
    row.createCell((short) 1).setCellValue("Handle");
    row.createCell((short) 2).setCellValue("Nickname");
    row.createCell((short) 3).setCellValue("Solved");
    short columnIndex = 4;
    for (Problem problem2 : problems) {
        Problem problem = problem2;
        row.createCell(columnIndex).setCellValue(problem.getCode());
        columnIndex++;
    }
    row.createCell(columnIndex).setCellValue("Penalty");

    int rowIndex = 6;
    for (RankListEntry rankListEntry : entries) {
        RankListEntry entry = rankListEntry;
        row = sheet.createRow(rowIndex);
        row.createCell((short) 0).setCellValue(rowIndex - 5);
        row.createCell((short) 1).setCellValue(entry.getUserProfile().getHandle());
        String nick = entry.getUserProfile().getHandle();
        if (entry.getUserProfile().getNickName() != null) {
            nick = entry.getUserProfile().getNickName();
        }
        cell = row.createCell((short) 2);
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(nick);

        row.createCell((short) 3).setCellValue(entry.getSolved());

        for (short i = 0; i < problems.size(); ++i) {
            String score = entry.getAcceptTime(i) > 0
                    ? entry.getAcceptTime(i) + "(" + entry.getSubmitNumber(i) + ")"
                    : "" + entry.getSubmitNumber(i);
            row.createCell((short) (4 + i)).setCellValue(score);
        }
        row.createCell((short) (4 + problems.size())).setCellValue(entry.getPenalty());
        rowIndex++;
    }

    // output to stream
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    try {
        wb.write(out);
        return out.toByteArray();
    } finally {
        out.close();
    }
}

From source file:cn.edu.zju.acm.onlinejudge.action.UserSearchAction.java

License:Open Source License

private byte[] exportToExcel(UserCriteria criteria, List<UserProfile> users) throws Exception {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    short rowId = 0;
    for (Object user : users) {
        HSSFRow row = sheet.createRow(rowId);
        rowId++;/*from  ww  w  . j  a  v a 2  s .co  m*/
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue(((UserProfile) user).getHandle());
    }

    // output to stream
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    try {
        wb.write(out);
        return out.toByteArray();
    } finally {
        out.close();
    }
}

From source file:com.aan.girsang.client.ui.master.barang.BarangPanel.java

private void exportExcel(List<Barang> dataList) throws IOException {
    if (dataList != null && !dataList.isEmpty()) {
        HSSFWorkbook workBook = new HSSFWorkbook();
        HSSFSheet sheet = workBook.createSheet();
        HSSFSheet worksheet = workBook.createSheet("Sheet 0");
        // Nama Field
        Row judul = sheet.createRow((short) 0);
        Cell cell = judul.createCell((short) 0);
        cell.setCellValue("This is a test of merging");
        HSSFRow headingRow = sheet.createRow((short) 2);
        headingRow.createCell((short) 0).setCellValue("ID");
        headingRow.createCell((short) 1).setCellValue("BARCODE 1");
        headingRow.createCell((short) 2).setCellValue("BARCODE 2");
        headingRow.createCell((short) 3).setCellValue("NAMA BARANG");
        headingRow.createCell((short) 4).setCellValue("GOLONGAN");
        headingRow.createCell((short) 5).setCellValue("SAT. JUAL");
        headingRow.createCell((short) 6).setCellValue("ST. TOKO");
        headingRow.createCell((short) 7).setCellValue("ST. GUDANG");
        headingRow.createCell((short) 8).setCellValue("SAT. BELI");
        headingRow.createCell((short) 9).setCellValue("ISI PEM.");
        headingRow.createCell((short) 10).setCellValue("HRG PEM.");
        headingRow.createCell((short) 11).setCellValue("HRG NORMAL");
        headingRow.createCell((short) 12).setCellValue("HRG MEMBER");
        headingRow.createCell((short) 13).setCellValue("JUAL");
        int panjang = headingRow.getLastCellNum() - 1;
        short rowNo = 3;

        sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based)
                0, //last row  (0-based)
                0, //first column (0-based)
                panjang //last column  (0-based)
        ));//from   w ww . j a v  a 2  s  .co m
        CellStyle styleData = workBook.createCellStyle();
        styleData.setBorderBottom(CellStyle.BORDER_THIN);
        styleData.setBorderRight(CellStyle.BORDER_THIN);
        styleData.setBorderLeft(CellStyle.BORDER_THIN);
        for (Barang b : dataList) {
            HSSFRow row = sheet.createRow(rowNo);
            String jual;
            if (b.getJual() == true) {
                jual = "Jual";
            } else {
                jual = "Tidak";
            }
            row.createCell((short) 0).setCellValue(b.getPlu());
            row.createCell((short) 1).setCellValue(b.getBarcode1());
            row.createCell((short) 2).setCellValue(b.getBarcode2());
            row.createCell((short) 3).setCellValue(b.getNamaBarang());
            row.createCell((short) 4).setCellValue(b.getGolonganBarang().getGolonganBarang());
            row.createCell((short) 5).setCellValue(b.getSatuan());
            row.createCell((short) 6).setCellValue(b.getStokToko());
            row.createCell((short) 7).setCellValue(b.getStokGudang());
            row.createCell((short) 8).setCellValue(b.getSatuanPembelian());
            row.createCell((short) 9).setCellValue(b.getIsiPembelian());
            row.createCell((short) 10).setCellValue(TextComponentUtils.formatNumber(b.getHargaBeli()));
            row.createCell((short) 11).setCellValue(TextComponentUtils.formatNumber(b.getHargaNormal()));
            row.createCell((short) 12).setCellValue(TextComponentUtils.formatNumber(b.getHargaMember()));
            row.createCell((short) 13).setCellValue(jual);
            for (int i = 0; i <= 13; i++) {
                row.getCell((short) i).setCellStyle(styleData);
            }
            rowNo++;
        }
        for (int i = 0; i <= 13; i++) {
            sheet.autoSizeColumn(i);
        }
        Font font = workBook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //style judul
        CellStyle styleTitle = workBook.createCellStyle();
        styleTitle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
        styleTitle.setFont(font);
        judul.getCell(0).setCellStyle(styleTitle);

        //judul field
        CellStyle styleHeading = workBook.createCellStyle();
        styleHeading.setFont(font);
        styleHeading.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
        styleHeading.setBorderBottom(CellStyle.BORDER_THIN);
        styleHeading.setBorderTop(CellStyle.BORDER_THIN);
        styleHeading.setBorderRight(CellStyle.BORDER_THIN);
        styleHeading.setBorderLeft(CellStyle.BORDER_THIN);
        for (int i = 0; i < headingRow.getLastCellNum(); i++) {//For each cell in the row 
            headingRow.getCell(i).setCellStyle(styleHeading);//Set the style
        }
        String file = "D:/Student_detais.xls";
        try {
            try (FileOutputStream fos = new FileOutputStream(file)) {
                workBook.write(fos);
            }
            JOptionPane.showMessageDialog(null, "Sukses");
        } catch (FileNotFoundException e) {
            System.out.println("Invalid directory or file not found");
        } catch (IOException e) {
            System.out.println("Error occurred while writting excel file to directory");
        }
    }
}

From source file:com.alibaba.differ.biz.TableExporter.java

License:Open Source License

public void export() throws IOException {
    fc.setFileFilter(new ExcelFileFilter());
    fc.setFileHidingEnabled(true);/*from  w w w .  j  a va  2 s.  co  m*/
    fc.setAcceptAllFileFilterUsed(false);
    int returnValue = fc.showSaveDialog(null);
    if (returnValue != JFileChooser.APPROVE_OPTION) {
        return;
    }
    File file = fc.getSelectedFile();
    if (file.exists()) {
        JOptionPane.showMessageDialog(null, "");
        return;
    }
    FileOutputStream fos = new FileOutputStream(file + ".xls");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet hs = wb.createSheet();
    TableModel tm = table.getModel();
    int row = tm.getRowCount();
    int cloumn = tm.getColumnCount();
    HSSFCellStyle style = wb.createCellStyle();
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 11);
    style.setFont(font);
    HSSFCellStyle style1 = wb.createCellStyle();
    style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style1.setFillForegroundColor(HSSFColor.ORANGE.index);
    style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont font1 = wb.createFont();
    font1.setFontHeightInPoints((short) 15);
    font1.setBoldweight((short) 700);
    style1.setFont(font);

    for (int i = 0; i < row + 1; i++) {
        HSSFRow hr = hs.createRow(i);
        for (int j = 0; j < cloumn; j++) {
            if (i == 0) {
                String value = tm.getColumnName(j);
                hs.setColumnWidth(j, UIConfig.EXCEL_COLUMN_WIDTH);
                HSSFRichTextString srts = new HSSFRichTextString(value);
                HSSFCell hc = hr.createCell((short) j);
                hc.setCellStyle(style1);
                hc.setCellValue(srts);
            } else {
                if (tm.getValueAt(i - 1, j) != null) {
                    String value = tm.getValueAt(i - 1, j).toString();
                    HSSFRichTextString srts = new HSSFRichTextString(value);
                    HSSFCell hc = hr.createCell((short) j);
                    hc.setCellStyle(style);

                    if (value.equals("") || value == null) {
                        hc.setCellValue(new HSSFRichTextString(""));
                    } else {
                        hc.setCellValue(srts);
                    }
                }
            }
        }
    }
    wb.write(fos);
    fos.close();
    JOptionPane.showMessageDialog(null, "Excel");
}

From source file:com.cimmyt.reports.impl.ServiceReportLaboratoryImpl.java

License:Apache License

private HSSFWorkbook createBookPlateExcel(StudyLabReportBean beanReport) {
    HSSFWorkbook book = new HSSFWorkbook();
    // Create a new Sheet in book
    HSSFSheet sheet = book.createSheet();
    // Creates a new row for headers
    HSSFRow row = sheet.createRow(0);/* www.j av a2 s . c om*/
    // Creates a new cell for title
    HSSFCell cell = row.createCell(0);
    // Create cell contents.
    HSSFRichTextString text = new HSSFRichTextString("");
    cell.setCellValue(text);
    // Creates a new
    HSSFRow rowHeaders = sheet.createRow(2);
    styleCellNormallyHeader = styleCellNormally(book, true);
    styleCellNormally = styleCellNormally(book, false);
    styleCellControl = getStyleCeldSolidForeground(book, cellControl);
    styleCellControlDART = getStyleCeldSolidForeground(book, cellControlDART);
    styleCellControlRandom = getStyleCeldSolidForeground(book, cellControlRandom);
    styleCellControlKBIo = getStyleCeldSolidForeground(book, cellKBiocontrolRandom);
    styleCellBlank = getStyleCeldSolidForeground(book, cellBlankForegroundColor);
    if (beanReport.getMapPlateSamples().size() > 0) {
        Iterator iteratorMapFirst = beanReport.getMapPlateSamples().entrySet().iterator();
        int rowCounter = 1;
        while (iteratorMapFirst.hasNext()) {
            Map.Entry entry = (Map.Entry) iteratorMapFirst.next();
            Map<String, SampleDetail> mapInner = (Map<String, SampleDetail>) entry.getValue();
            Integer key = (Integer) entry.getKey();
            sheet.createRow(rowCounter);
            rowCounter++;
            rowCounter = createHeaderPlate(sheet, rowCounter, beanReport.getNumberColumn(),
                    styleCellNormallyHeader, beanReport.getPatternPlate() + key.toString());
            int rowCounterLabel = 0;

            for (int sizeRow = 0; sizeRow < beanReport.getNameRow().length; sizeRow++) {
                HSSFRow rowData = sheet.createRow(rowCounter);

                for (int sizeColumn = 0; sizeColumn <= beanReport.getNumberColumn(); sizeColumn++) {
                    if (sizeColumn == 0) {
                        writeCell(rowData, sizeColumn, beanReport.getNameRow()[rowCounterLabel],
                                styleCellNormallyHeader);
                        rowCounterLabel++;
                    } else {
                        SampleDetail detail = mapInner.get(beanReport.getPatternPlate() + key.toString()
                                + beanReport.getNameRow()[sizeRow] + (sizeColumn));
                        if (detail != null) {
                            HSSFCellStyle style = null;
                            String sampleName = "";
                            if (detail.getControltype() != null && !detail.getControltype().equals(""))
                                style = validateStatusSample(detail.getControltype());
                            else
                                sampleName = getFieldsReport(beanReport, detail);
                            String strDetail = getTemplateFiled(detail);
                            if (strDetail != null && !strDetail.isEmpty()) {
                                if (!sampleName.isEmpty()) {
                                    sampleName = sampleName + "\n" + strDetail;
                                } else {
                                    sampleName = sampleName + strDetail;
                                }
                            }

                            writeCell(rowData, sizeColumn, sampleName, style);
                        }
                    }
                }
                rowCounter++;
            }
        }
        return book;
    }
    return null;
}