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

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

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

Introduction

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

Prototype

@Override
public void removeSheetAt(int index)

Source Link

Document

Removes sheet at the given index.

Care must be taken if the removed sheet is the currently active or only selected sheet in the workbook.

Usage

From source file:HSSFReadWrite.java

License:Apache License

/**
 * given a filename this outputs a sample sheet with just a set of
 * rows/cells.//  www.  j a va 2  s  . 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:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

public boolean deleteTestSuitesFromXls(String filePath, String testSuiteName) {
    boolean hasTrue = false;
    Iterator<Row> rowIterator;
    try {/* w  ww  .  j a  va2  s .co  m*/
        FileInputStream myInput = new FileInputStream(filePath);
        HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput);

        HSSFSheet mySheet = myWorkBook.getSheetAt(0);
        rowIterator = mySheet.rowIterator();
        for (int i = 0; i <= 2; i++) {
            rowIterator.next();
        }
        while (rowIterator.hasNext()) {
            Row next = rowIterator.next();
            if (getValue(next.getCell(2)).equalsIgnoreCase(testSuiteName)) {
                mySheet.removeRow(next);
                int rowNum = next.getRowNum();
                int newNum = rowNum + 1;
                mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1);
                int numberOfSheets = myWorkBook.getNumberOfSheets();
                for (int j = 0; j < numberOfSheets; j++) {
                    HSSFSheet mySheet1 = myWorkBook.getSheetAt(j);
                    if (mySheet1.getSheetName().equalsIgnoreCase(testSuiteName)) {
                        myWorkBook.removeSheetAt(j);
                        hasTrue = true;
                        break;
                    }
                }
                myInput.close();
                FileOutputStream outFile = new FileOutputStream(filePath);
                myWorkBook.write(outFile);
                outFile.close();
                break;
            }
        }
    } catch (Exception e) {

    }

    return hasTrue;
}

From source file:demo.poi.BigExample.java

License:Apache License

public static void main(String[] args) throws IOException {
    int rownum;//  w w  w  .  ja va  2s .  c  o  m

    // create a new file
    FileOutputStream out = new FileOutputStream("target/bigworkbook.xls");
    // create a new workbook
    HSSFWorkbook wb = new HSSFWorkbook();
    // create a new sheet
    HSSFSheet s = wb.createSheet();
    // declare a row object reference
    HSSFRow r = null;
    // declare a cell object reference
    HSSFCell c = null;
    // create 3 cell styles
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
    // create 2 fonts objects
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    //set font 1 to 12 point type
    f.setFontHeightInPoints((short) 12);
    //make it red
    f.setColor(HSSFColor.RED.index);
    // make it bold
    //arial is the default font
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    //set font 2 to 10 point type
    f2.setFontHeightInPoints((short) 10);
    //make it the color at palette index 0xf (white)
    f2.setColor(HSSFColor.WHITE.index);
    //make it bold
    f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

    //set cell stlye
    cs.setFont(f);
    //set the cell format see HSSFDataFromat for a full list
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));

    //set a thin border
    cs2.setBorderBottom(CellStyle.BORDER_THIN);
    //fill w fg fill color
    cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    // set foreground fill to red
    cs2.setFillForegroundColor(HSSFColor.RED.index);

    // set the font
    cs2.setFont(f2);

    // set the sheet name to HSSF Test
    wb.setSheetName(0, "HSSF Test");
    // create a sheet with 300 rows (0-299)
    for (rownum = 0; rownum < 300; rownum++) {
        // create a row
        r = s.createRow(rownum);
        // on every other row
        if ((rownum % 2) == 0) {
            // make the row height bigger  (in twips - 1/20 of a point)
            r.setHeight((short) 0x249);
        }

        //r.setRowNum(( short ) rownum);
        // create 50 cells (0-49) (the += 2 becomes apparent later
        for (int cellnum = 0; cellnum < 50; cellnum += 2) {
            // create a numeric cell
            c = r.createCell(cellnum);
            // do some goofy math to demonstrate decimals
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));

            // on every other row
            if ((rownum % 2) == 0) {
                // set this cell to the first cell style we defined
                c.setCellStyle(cs);
            }

            // create a string cell (see why += 2 in the
            c = r.createCell(cellnum + 1);

            // set the cell's string value to "TEST"
            c.setCellValue("TEST");
            // make this column a bit wider
            s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20)));

            // on every other row
            if ((rownum % 2) == 0) {
                // set this to the white on red cell style
                // we defined above
                c.setCellStyle(cs2);
            }

        }
    }

    //draw a thick black border on the row at the bottom using BLANKS
    // advance 2 rows
    rownum++;
    rownum++;

    r = s.createRow(rownum);

    // define the third style to be the default
    // except with a thick black border at the bottom
    cs3.setBorderBottom(CellStyle.BORDER_THICK);

    //create 50 cells
    for (int cellnum = 0; cellnum < 50; cellnum++) {
        //create a blank type cell (no value)
        c = r.createCell(cellnum);
        // set it to the thick black border style
        c.setCellStyle(cs3);
    }

    //end draw thick black border

    // demonstrate adding/naming and deleting a sheet
    // create a sheet, set its title then delete it
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);
    //end deleted sheet

    // write the workbook to the output stream
    // close our file (don't blow out our file handles
    wb.write(out);
    out.close();
}

From source file:gda.hrpd.data.HSSF.java

License:Apache License

/**
 * Constructor HSSF - given a filename this outputs a sample sheet with just a set of rows/cells.
 *
 * @param filename//from w  w  w .j av  a2 s  .c om
 * @param write
 * @exception IOException
 */

public HSSF(String filename, @SuppressWarnings("unused") boolean write) throws IOException {
    short rownum = 0;
    FileOutputStream out = new FileOutputStream(filename);
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r;
    HSSFCell c = null;
    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 = (short) 0; rownum < 300; rownum++) {
        r = s.createRow(rownum);
        if ((rownum % 2) == 0) {
            r.setHeight((short) 0x249);
        }

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

    // draw a thick black border on the row at the bottom using BLANKS
    rownum++;
    rownum++;
    r = s.createRow(rownum);
    cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK);
    for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
        c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK);

        // c.setCellValue(0);
        c.setCellStyle(cs3);
    }
    s.addMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3));
    s.addMergedRegion(new Region((short) 100, (short) 100, (short) 110, (short) 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
    wb.write(out);
    out.close();
}

From source file:org.openelis.bean.QcChartReport1Bean.java

License:Open Source License

private HSSFWorkbook fillWorkbook(HSSFWorkbook wb, ArrayList<Value> values, String qcName, String qcType,
        String qcFormat, Integer plotType, ReportStatus status) throws Exception {
    int rowIndex, sheetIndex, valueIndex;
    HSSFSheet sheet;//from w  w  w.  j  a  v  a2 s.c  o  m
    Row row;
    String lastAnalyte, lastFormat;

    lastAnalyte = "___";
    lastFormat = "___";
    sheet = null;
    sheetIndex = 1;
    valueIndex = 0;

    try {
        baseFont = wb.createFont();
        baseFont.setFontName("Arial");
        baseFont.setFontHeightInPoints((short) 8);
        baseStyle = wb.createCellStyle();
        baseStyle.setFont(baseFont);

        headerFont = wb.createFont();
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 8);
        headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(CellStyle.ALIGN_LEFT);
        headerStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
        headerStyle.setFont(headerFont);

        maxChars = new ArrayList<Integer>();
        worksheetColumnMap = new HashMap<String, HashMap<String, Integer>>();
        worksheetHeaders = new ArrayList<String>();
        worksheetHeaderNames = new ArrayList<String>();

        if (qcColumns != null && !qcColumns.isEmpty())
            rowIndex = 32;
        else
            rowIndex = 3;

        for (Value value : values) {
            valueIndex++;

            if ("N".equals(value.getIsPlot()))
                continue;

            if (!lastAnalyte.equals(value.getAnalyteName())) {
                if (!"___".equals(lastAnalyte)) {
                    while (rowIndex < sheet.getLastRowNum()) {
                        sheet.removeRow(sheet.getRow(rowIndex));
                        rowIndex++;
                    }
                    finishSheet(sheet, wb, qcName, qcType, lastAnalyte);
                }
                sheet = wb.getSheet("Sheet" + (sheetIndex++));
                if (sheet == null)
                    sheet = wb.createSheet();
                lastAnalyte = value.getAnalyteName();
                if (qcColumns != null && !qcColumns.isEmpty())
                    rowIndex = 32;
                else
                    rowIndex = 3;
                lastFormat = "___";

                if (Constants.dictionary().CHART_TYPE_FIXED.equals(plotType))
                    setStatisticCells(wb, sheet, value);
            }

            if (!lastFormat.equals(value.getWorksheetFormat())) {
                lastFormat = value.getWorksheetFormat();
                if (qcColumns == null || qcColumns.isEmpty())
                    loadWorksheetFormat(lastFormat);
            }

            row = sheet.createRow(rowIndex++);
            setBaseCells(value, row);
            setResultCells(value, row, qcFormat, lastFormat);

            status.setPercentComplete(70 * (valueIndex / values.size()) + 20);
            session.setAttribute("qcChartReport", status);
        }

        finishSheet(sheet, wb, qcName, qcType, lastAnalyte);

        while (sheetIndex < wb.getNumberOfSheets())
            wb.removeSheetAt(sheetIndex);
    } finally {
        baseFont = null;
        baseStyle = null;
        headerFont = null;
        headerStyle = null;
        maxChars = null;
        worksheetColumnMap = null;
        worksheetHeaders = null;
        worksheetHeaderNames = null;
    }

    return wb;
}

From source file:org.orbeon.oxf.processor.serializer.legacy.XLSSerializer.java

License:Open Source License

protected void readInput(final PipelineContext pipelineContext, ProcessorInput input, Config config,
        OutputStream outputStream) {
    try {// w  ww .  j ava 2  s  . c  o  m
        Document dataDocument = readInputAsDOM4J(pipelineContext, INPUT_DATA);
        final DocumentWrapper wrapper = new DocumentWrapper(dataDocument, null,
                XPathCache.getGlobalConfiguration());

        Document configDocument = readInputAsDOM4J(pipelineContext, INPUT_CONFIG);

        // Read template sheet
        String templateName = configDocument.getRootElement().attributeValue("template");
        //String fileName = configDocument.getRootElement().attributeValue("filename");
        InputStream templateInputStream = URLFactory.createURL(templateName).openStream();
        final HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(templateInputStream));
        final HSSFDataFormat dataFormat = workbook.createDataFormat();
        templateInputStream.close();

        int sheetIndex = 0;

        PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper,
                "/workbook/sheet", getLocationData());
        List<Object> nodes = expr.evaluateToJavaReturnToPool();

        for (Iterator i = nodes.iterator(); i.hasNext();) {

            final Element sheetElement = (Element) i.next();
            HSSFSheet sheet = workbook.cloneSheet(0);
            workbook.setSheetName(sheetIndex + 1, sheetElement.attributeValue("name"));

            // Duplicate rows if we find a "repeat-row" in the config
            for (Iterator j = configDocument.selectNodes("/config/repeat-row").iterator(); j.hasNext();) {

                // Get info about row to repeat
                Element repeatRowElement = (Element) j.next();
                final int rowNum = Integer.parseInt(repeatRowElement.attributeValue("row-num"));
                final String forEach = repeatRowElement.attributeValue("for-each");
                HSSFRow templateRow = sheet.getRow(rowNum);
                int repeatCount = ((Double) sheetElement.selectObject("count(" + forEach + ")")).intValue();

                // Move existing rows lower
                int lastRowNum = sheet.getLastRowNum();
                for (int k = lastRowNum; k > rowNum; k--) {
                    HSSFRow sourceRow = sheet.getRow(k);
                    HSSFRow newRow = sheet.createRow(k + repeatCount - 1);
                    XLSUtils.copyRow(workbook, newRow, sourceRow);
                }

                // Create rows, copying the template row
                for (int k = rowNum + 1; k < rowNum + repeatCount; k++) {
                    HSSFRow newRow = sheet.createRow(k);
                    XLSUtils.copyRow(workbook, newRow, templateRow);
                }

                // Modify the XPath expression on each row
                for (int k = rowNum; k < rowNum + repeatCount; k++) {
                    HSSFRow newRow = sheet.getRow(k);
                    for (short m = 0; m <= newRow.getLastCellNum(); m++) {
                        HSSFCell cell = newRow.getCell(m);
                        if (cell != null) {
                            String currentFormat = dataFormat.getFormat(cell.getCellStyle().getDataFormat());
                            final Matcher matcher = FORMAT_XPATH.matcher(currentFormat);
                            if (matcher.find()) {
                                String newFormat = matcher.group(1) + "\"" + forEach + "[" + (k - rowNum + 1)
                                        + "]/" + matcher.group(2) + "\"";
                                cell.getCellStyle().setDataFormat(dataFormat.getFormat(newFormat));
                            }
                        }
                    }
                }
            }

            // Set values in cells with an XPath expression
            XLSUtils.walk(dataFormat, sheet, new XLSUtils.Handler() {
                public void cell(HSSFCell cell, String sourceXPath, String targetXPath) {
                    if (sourceXPath.charAt(0) == '/')
                        sourceXPath = sourceXPath.substring(1);

                    // Set cell value
                    PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(),
                            wrapper.wrap(sheetElement), "string(" + sourceXPath + ")", getLocationData());
                    String newValue = (String) expr.evaluateSingleToJavaReturnToPoolOrNull();

                    if (newValue == null) {
                        throw new OXFException("Nothing matches the XPath expression '" + sourceXPath
                                + "' in the input document");
                    }
                    try {
                        cell.setCellValue(Double.parseDouble(newValue));
                    } catch (NumberFormatException e) {
                        cell.setCellValue(newValue);
                    }

                    // Set cell format
                    Object element = sheetElement.selectObject(sourceXPath);
                    if (element instanceof Element) {
                        // NOTE: We might want to support other properties here
                        String bold = ((Element) element).attributeValue("bold");
                        if (bold != null) {
                            HSSFFont originalFont = workbook.getFontAt(cell.getCellStyle().getFontIndex());
                            HSSFFont newFont = workbook.createFont();
                            XLSUtils.copyFont(newFont, originalFont);
                            if ("true".equals(bold))
                                newFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                            cell.getCellStyle().setFont(newFont);
                        }
                    }
                }
            });
            sheetIndex++;
        }

        workbook.removeSheetAt(0);

        // Write out the workbook
        workbook.write(outputStream);
    } catch (IOException e) {
        throw new OXFException(e);
    }
}

From source file:poi.hssf.usermodel.examples.BigExample.java

License:Apache License

public static void main(String[] args) throws IOException {
    int rownum;//from   w  w  w .  j  a va  2 s  . co  m

    // create a new file
    FileOutputStream out = new FileOutputStream("workbook.xls");
    // create a new workbook
    HSSFWorkbook wb = new HSSFWorkbook();
    // create a new sheet
    HSSFSheet s = wb.createSheet();
    // declare a row object reference
    HSSFRow r = null;
    // declare a cell object reference
    HSSFCell c = null;
    // create 3 cell styles
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
    // create 2 fonts objects
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    //set font 1 to 12 point type
    f.setFontHeightInPoints((short) 12);
    //make it red
    f.setColor(HSSFColor.RED.index);
    // make it bold
    //arial is the default font
    f.setBoldweight(f.BOLDWEIGHT_BOLD);

    //set font 2 to 10 point type
    f2.setFontHeightInPoints((short) 10);
    //make it the color at palette index 0xf (white)
    f2.setColor(HSSFColor.WHITE.index);
    //make it bold
    f2.setBoldweight(f2.BOLDWEIGHT_BOLD);

    //set cell stlye
    cs.setFont(f);
    //set the cell format see HSSFDataFromat for a full list
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));

    //set a thin border
    cs2.setBorderBottom(cs2.BORDER_THIN);
    //fill w fg fill color
    cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    // set foreground fill to red
    cs2.setFillForegroundColor(HSSFColor.RED.index);

    // set the font
    cs2.setFont(f2);

    // set the sheet name to HSSF Test
    wb.setSheetName(0, "HSSF Test");
    // create a sheet with 300 rows (0-299)
    for (rownum = 0; rownum < 300; rownum++) {
        // create a row
        r = s.createRow(rownum);
        // on every other row
        if ((rownum % 2) == 0) {
            // make the row height bigger  (in twips - 1/20 of a point)
            r.setHeight((short) 0x249);
        }

        //r.setRowNum(( short ) rownum);
        // create 50 cells (0-49) (the += 2 becomes apparent later
        for (int cellnum = 0; cellnum < 50; cellnum += 2) {
            // create a numeric cell
            c = r.createCell(cellnum);
            // do some goofy math to demonstrate decimals
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));

            // on every other row
            if ((rownum % 2) == 0) {
                // set this cell to the first cell style we defined
                c.setCellStyle(cs);
            }

            // create a string cell (see why += 2 in the
            c = r.createCell(cellnum + 1);

            // set the cell's string value to "TEST"
            c.setCellValue("TEST");
            // make this column a bit wider
            s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20)));

            // on every other row
            if ((rownum % 2) == 0) {
                // set this to the white on red cell style
                // we defined above
                c.setCellStyle(cs2);
            }

        }
    }

    //draw a thick black border on the row at the bottom using BLANKS
    // advance 2 rows
    rownum++;
    rownum++;

    r = s.createRow(rownum);

    // define the third style to be the default
    // except with a thick black border at the bottom
    cs3.setBorderBottom(cs3.BORDER_THICK);

    //create 50 cells
    for (int cellnum = 0; cellnum < 50; cellnum++) {
        //create a blank type cell (no value)
        c = r.createCell(cellnum);
        // set it to the thick black border style
        c.setCellStyle(cs3);
    }

    //end draw thick black border

    // demonstrate adding/naming and deleting a sheet
    // create a sheet, set its title then delete it
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);
    //end deleted sheet

    // write the workbook to the output stream
    // close our file (don't blow out our file handles
    wb.write(out);
    out.close();
}

From source file:poi.HSSFReadWrite.java

License:Apache License

/**
 * given a filename this outputs a sample sheet with just a set of
 * rows/cells.//from   ww w  .  jav  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
    wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);

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

    //      wb.close();
}

From source file:ReadExcel.HSSFReadWrite.java

License:Apache License

/**
 * given a filename this outputs a sample sheet with just a set of
 * rows/cells./*w w  w.jav a 2  s  .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.setBold(true);
    f2.setFontHeightInPoints((short) 10);
    f2.setColor((short) 0xf);
    f2.setBold(true);
    cs.setFont(f);
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
    cs2.setBorderBottom(BorderStyle.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(BorderStyle.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
    wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);
    // end deleted sheet
    FileOutputStream out = new FileOutputStream(outputFilename);
    try {
        wb.write(out);
    } finally {
        out.close();
    }
    wb.close();
}