Example usage for org.apache.poi.ss.usermodel Workbook createSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createSheet

Introduction

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

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

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

Usage

From source file:Cuentas.editaCuentas.java

private void bt_actualiza2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bt_actualiza2ActionPerformed
    // TODO add your handling code here:
    javax.swing.JFileChooser jF1 = new javax.swing.JFileChooser();
    jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    String ruta = null;//  ww  w . ja  v  a2 s  . co  m
    if (jF1.showSaveDialog(null) == jF1.APPROVE_OPTION) {
        ruta = jF1.getSelectedFile().getAbsolutePath();
        if (ruta != null) {
            File archivoXLS = new File(ruta + ".xls");
            try {
                if (archivoXLS.exists())
                    archivoXLS.delete();
                archivoXLS.createNewFile();
                Workbook libro = new HSSFWorkbook();
                FileOutputStream archivo = new FileOutputStream(archivoXLS);
                Sheet hoja = libro.createSheet("Clientes");
                for (int ren = 0; ren < (t_datos.getRowCount() + 1); ren++) {
                    Row fila = hoja.createRow(ren);
                    for (int col = 0; col < t_datos.getColumnCount(); col++) {
                        Cell celda = fila.createCell(col);
                        if (ren == 0) {
                            celda.setCellValue(columnas[col]);
                        } else {
                            if (t_datos.getValueAt(ren - 1, col) == null)
                                celda.setCellValue("");
                            else {
                                if (t_datos.getValueAt(ren - 1, col).toString()
                                        .compareToIgnoreCase("false") == 0)
                                    celda.setCellValue("");
                                else {
                                    if (t_datos.getValueAt(ren - 1, col).toString()
                                            .compareToIgnoreCase("true") == 0)
                                        celda.setCellValue("x");
                                    else
                                        celda.setCellValue(t_datos.getValueAt(ren - 1, col).toString());
                                }
                            }
                        }
                    }
                }
                libro.write(archivo);
                archivo.close();
                Desktop.getDesktop().open(archivoXLS);
            } catch (Exception e) {
                System.out.println(e);
                JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte");
            }
        }
    }
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

public boolean processFile(Path input, boolean openFile) {
    boolean result = false;
    int endRow = 0;

    try {//from w  ww. j  a va  2 s . c o  m
        updateMessages(String.format("Inicializando el documento: %s", input.toString()));
        Path copy = createCopy(input);

        if (copy != null && Files.exists(copy, LinkOption.NOFOLLOW_LINKS)) {
            Workbook workbook = WorkbookFactory.create(copy.toFile());
            Sheet sheet = workbook.getSheetAt(0);
            Sheet newSheet = workbook.createSheet("Procesado");

            workbook.setSheetName(0, "Crudo");

            endRow = getLasRow(sheet);

            // seccion para la creacion de los encabezados
            updateMessages("Creando la cabecera de los datos");
            createHeaderData(newSheet, getCellStyleHeaderData(workbook));

            // seccion para los values USD
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MTH),
                    11, 35, 14);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.QRT),
                    35, 49, 38);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.YTD),
                    49, 54, 52);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MAT),
                    54, 59, 57);

            // seccion para los values units
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MTH),
                    59, 83, 63);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.QRT),
                    83, 97, 87);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.YTD),
                    97, 102, 101);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MAT),
                    102, 107, 106);
            //            
            //            // seccion para los values units standars
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MTH),
                    107, 131, 112);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.QRT),
                    131, 145, 136);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.YTD),
                    145, 150, 150);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MAT),
                    150, 155, 155);

            // fin de la seccion para la creacion de los encabezados

            // seccion para escribir los CT
            updateMessages("Escribiendo las clases terampeuticas...");
            writeCT(newSheet, sheet, 13, endRow);

            // seccion para escribir los productos
            updateMessages("Escribiendo los productos...");
            writeProducts(newSheet, sheet, 14);

            // seccion para escribir los otros valores
            updateMessages("Escribiendo datos en general...");
            writerOthersValues(newSheet, sheet, 15);

            // seccion para escribir los key competitors
            updateMessages("Escribiendo los Key Competitors...");
            writeKeyCompetitors(newSheet, 3, endRow, 9, 5);

            // seccion para escribir el pais
            XmlContry contry = writeContries(newSheet, 3, 0, input);

            // seccion para escribir la region
            writeRegions(contry, newSheet, 3, 1);

            for (int i = 0; i < 155; i++)
                newSheet.autoSizeColumn(i);

            newSheet.setAutoFilter(CellRangeAddress.valueOf(String.format("A3:K%d", newSheet.getLastRowNum())));

            String pathOutput = "DAS PF - " + input.getFileName().toString();

            try (FileOutputStream fos = new FileOutputStream(
                    Paths.get(this.out.toString(), pathOutput).toFile())) {

                updateMessages(String.format("Guadando el trabajo en la ruta: '%s'",
                        Paths.get(this.out.toString(), pathOutput)));

                workbook.write(fos);
            } catch (IOException ex) {
                Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                workbook.close();
            }

            if (openFile && Desktop.isDesktopSupported()
                    && Desktop.getDesktop().isSupported(Desktop.Action.OPEN))
                Desktop.getDesktop().open(Paths.get(this.out.toString(), pathOutput).toFile());

            result = true;

            newSheet = null;
            sheet = null;
            workbook = null;

            Files.delete(copy);
        }
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);

        Util.showException("No se pudo guardar el archivo", ex);
    }

    return result;
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

public boolean consolidateFiles() {
    boolean result = false;
    AtomicInteger rowIndex = new AtomicInteger(3);

    String outputName = "DAS - " + this.out.getFileName().toString() + " consolidate.xlsx";
    Workbook consolidateWb = new XSSFWorkbook();

    try {//w ww .ja v  a 2  s  .  c  o  m
        Sheet sheetConsolidate = consolidateWb.createSheet("Consolidado");

        Files.list(this.out).filter((p) -> {
            String name = p.toString();

            return (name.endsWith(".xlsx") || name.endsWith(".xls"))
                    && !p.getFileName().toString().equals(outputName);
        }).sorted((p1, p2) -> {
            String acronym = getAcromynName(p1);
            String acronym2 = getAcromynName(p2);

            return acronym.compareToIgnoreCase(acronym2);
        }).forEach(p -> {
            try {
                Workbook wb = WorkbookFactory.create(p.toFile());
                Sheet sheet = wb.getSheet("Procesado");

                updateMessages(String.format("Copiando los datos del archivo: %s dentro del archivo: %s",
                        p.toString(), outputName));

                for (int index = 3; index < sheet.getLastRowNum(); index++) {
                    Row row = sheet.getRow(index);
                    Row r = sheetConsolidate.createRow(rowIndex.getAndIncrement());

                    row.forEach(c -> {
                        if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) {
                            final Cell cell = r.createCell(c.getColumnIndex(), c.getCellType());

                            updateMessages(
                                    String.format("Copiando los datos de la fila: #%d", c.getRowIndex()));

                            switch (c.getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC:
                                cell.setCellValue(c.getNumericCellValue());

                                break;

                            case Cell.CELL_TYPE_STRING:
                                cell.setCellValue(c.getRichStringCellValue());

                                break;
                            }
                        }
                    });

                    row = null;
                }

                sheet = null;
                wb.close();
                wb = null;
            } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
                Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
            }
        });

        Path path = Files.list(this.out).filter((p) -> {
            String name = p.toString();

            return (name.endsWith(".xlsx") || name.endsWith(".xls"))
                    && !p.getFileName().toString().equals(outputName);
        }).findFirst().get();

        createHeadersConsolidateFile(consolidateWb, path);

        for (int i = 0; i < 155; i++)
            sheetConsolidate.autoSizeColumn(i);

        sheetConsolidate.setAutoFilter(
                CellRangeAddress.valueOf(String.format("A3:K%d", sheetConsolidate.getLastRowNum())));

        try (FileOutputStream fos = new FileOutputStream(Paths.get(out.toString(), outputName).toFile())) {
            updateMessages(String.format("Guadando el trabajo en la ruta: '%s'",
                    Paths.get(this.out.toString(), outputName)));

            consolidateWb.write(fos);

            result = true;
        } catch (IOException ex) {
            Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE,
                    "Ocurrio un error al intenatr guardar el archivo consolidado", ex);
        } finally {
            consolidateWb.close();
        }

    } catch (IOException ex) {
        Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
    }

    consolidateWb = null;

    return result;
}

From source file:Data.Database.java

public void dumpExcel() throws FileNotFoundException, IOException {
    //Workbook wb = new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("leaguedata");

    // set headers on excel sheet
    Row row = sheet.createRow((short) 0);
    String headers[] = new String[] { "Team", "Player", "Position", "Age", "Yrs Played", "GP", "G", "A", "PTS",
            "+/-", "STP", "SOG", "SH%", "Hits", "Blocks", "TOI", "G/60", "A/60", "PTS/60", "STP/60", "SOG/60",
            "Hits/60", "Blocks/60" };

    for (int i = 0; i < headers.length; i++) {
        Cell cell = row.createCell(i);//w w w.  j  a  va2s .  co  m
        cell.setCellValue(createHelper.createRichTextString(headers[i]));
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cell.setCellStyle(cellStyle);
    }

    // add player data
    int track = 2;

    //        // dump ALL players!!!!!
    //        for (Map.Entry<String, Player> entry : players.entrySet()){
    //            Row newrow = sheet.createRow((short)track);
    //            entry.getValue().dumpExcel(newrow, "null");
    //            track++;
    //        }
    //        
    //                // Write the output to a file
    //        FileOutputStream fileOut = new FileOutputStream("RFHL_allplayers.xlsx");
    //        wb.write(fileOut);
    //        fileOut.close();

    // dump fantasy teams!!!
    for (int i = 0; i < fh_teams.size(); i++) {
        track = fh_teams.get(i).dumpExcel(sheet, track);
        track++;
    }

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("RFHL.xlsx");
    wb.write(fileOut);
    fileOut.close();

}

From source file:data.ReportDB.java

public static Workbook getUserEmail() {

    // get the data from the database
    EntityManager em = DBUtil.getEmFactory().createEntityManager();
    String qString = "SELECT u FROM User u WHERE u.registrationDate > = :startDate" + "ORDER BY u.lastName";
    TypedQuery<User> q = em.createQuery(qString, User.class);
    List<User> users = null;

    // Get first date of currentMonth.
    Date date = new Date();
    String startDate = new SimpleDateFormat("yyyy-MM").format(date);
    startDate += "-01";
    try {//from  w ww .  j a  v a  2 s .  c  o  m
        q.setParameter("startDate", startDate);
        users = q.getResultList();
    } catch (NoResultException e) {
        System.err.println(e);
        return null;
    } finally {
        em.close();
    }

    // create the workbook, its worksheet, and its title row
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("User Report");
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("The User Report");

    // create the header row
    row = sheet.createRow(2);
    row.createCell(0).setCellValue("LastName");
    row.createCell(1).setCellValue("FirstName");
    row.createCell(2).setCellValue("Email");
    row.createCell(3).setCellValue("CompanyName");
    row.createCell(4).setCellValue("Address1");
    row.createCell(5).setCellValue("Address2");
    row.createCell(6).setCellValue("City");
    row.createCell(7).setCellValue("State");
    row.createCell(8).setCellValue("Zip");
    row.createCell(9).setCellValue("Country");
    row.createCell(10).setCellValue("UserID");

    // create the data rows
    int i = 3;
    for (User u : users) {
        row = sheet.createRow(i);
        row.createCell(0).setCellValue(u.getLastName());
        row.createCell(1).setCellValue(u.getFirstName());
        row.createCell(2).setCellValue(u.getEmail());
        row.createCell(4).setCellValue(u.getAddress());
        row.createCell(6).setCellValue(u.getCity());
        row.createCell(7).setCellValue(u.getState());
        row.createCell(8).setCellValue(u.getZip());
        row.createCell(10).setCellValue(u.getUserId());
        i++;
    }

    return workbook;
}

From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetriever.java

License:Open Source License

/**
 * Creates an Excel worksheet containing the metric's data (timestamps and values) for the
 * specified time range. This worksheet is titled with the trhe metric's name and added to the
 * specified Workbook./*from w w w  .j a v  a 2 s .  co m*/
 *
 * @param wb          the workbook to add this worksheet to
 * @param metricName  the name of the metric whose data is being rendered in this worksheet
 * @param rrdFilename the name of the RRD file to retrieve the metric's data from
 * @param startTime   start time, in seconds since Unix epoch, to fetch metric's data
 * @param endTime     end time, in seconds since Unix epoch, to fetch metric's data
 * @throws IOException
 * @throws MetricsGraphException
 */
private void createSheet(Workbook wb, String metricName, String rrdFilename, long startTime, long endTime)
        throws IOException, MetricsGraphException {
    LOGGER.trace("ENTERING: createSheet");

    MetricData metricData = getMetricData(rrdFilename, startTime, endTime);

    String displayableMetricName = convertCamelCase(metricName);

    String title = displayableMetricName + " for " + getCalendarTime(startTime) + " to "
            + getCalendarTime(endTime);

    Sheet sheet = wb.createSheet(displayableMetricName);

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle columnHeadingsStyle = wb.createCellStyle();
    columnHeadingsStyle.setFont(headerFont);

    CellStyle bannerStyle = wb.createCellStyle();
    bannerStyle.setFont(headerFont);
    bannerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
    bannerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    int rowCount = 0;

    Row row = sheet.createRow((short) rowCount);
    Cell cell = row.createCell(0);
    cell.setCellValue(title);
    cell.setCellStyle(bannerStyle);
    rowCount++;

    // Blank row for spacing/readability
    row = sheet.createRow((short) rowCount);
    cell = row.createCell(0);
    cell.setCellValue("");
    rowCount++;

    row = sheet.createRow((short) rowCount);
    cell = row.createCell(0);
    cell.setCellValue("Timestamp");
    cell.setCellStyle(columnHeadingsStyle);
    cell = row.createCell(1);
    cell.setCellValue("Value");
    cell.setCellStyle(columnHeadingsStyle);
    rowCount++;

    List<Long> timestamps = metricData.getTimestamps();
    List<Double> values = metricData.getValues();

    for (int i = 0; i < timestamps.size(); i++) {
        String timestamp = getCalendarTime(timestamps.get(i));
        row = sheet.createRow((short) rowCount);
        row.createCell(0).setCellValue(timestamp);
        row.createCell(1).setCellValue(values.get(i));
        rowCount++;
    }

    if (metricData.hasTotalCount()) {
        // Blank row for spacing/readability
        row = sheet.createRow((short) rowCount);
        cell = row.createCell(0);
        cell.setCellValue("");
        rowCount++;

        row = sheet.createRow((short) rowCount);
        cell = row.createCell(0);
        cell.setCellValue("Total Count: ");
        cell.setCellStyle(columnHeadingsStyle);
        row.createCell(1).setCellValue(metricData.getTotalCount());
    }

    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);

    LOGGER.trace("EXITING: createSheet");
}

From source file:de.alpharogroup.export.excel.poi.ExcelPoiFactory.java

License:Open Source License

/**
 * Creates a new Sheet with the given name.
 * <p>/*w  ww.  ja v  a 2 s .  c  o m*/
 * Note that sheet name is Excel must not exceed 31 characters and must not contain any of the
 * any of the following characters:
 * <ul>
 * <li>0x0000</li>
 * <li>0x0003</li>
 * <li>colon (:)</li>
 * <li>backslash (\)</li>
 * <li>asterisk (*)</li>
 * <li>question mark (?)</li>
 * <li>forward slash (/)</li>
 * <li>opening square bracket ([)</li>
 * <li>closing square bracket (])</li>
 * </ul>
 * 
 * @param workbook
 *            the workbook
 * @param name
 *            the name
 * @return the Sheet
 */
public static Sheet newSheet(final Workbook workbook, final String name) {
    return workbook.createSheet(WorkbookUtil.createSafeSheetName(name));
}

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

/**
 * Create a new {@link Sheet} if the sheet with the given name doesn't exist,
 * otherwise returns the existing sheet.
 * @param workbook/*from w  ww . j  ava 2s  . co m*/
 * @param name
 * @return Existing or newly created sheet
 */
private Sheet getSheet(final Workbook workbook, final String name) {
    final String validName = name.replaceAll("[\\\\/\\?\\*\\[\\]]", "_");
    Sheet sheet = workbook.getSheet(validName);
    if (sheet == null)
        sheet = workbook.createSheet(validName);
    return sheet;
}

From source file:de.fme.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body./*from  www  .  j  a va 2s  .c om*/
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");

    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle() != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle();
                descriptions[i] = pd.getDescription();
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        try {
            sheet.createFreezePane(0, 1);
        } catch (IndexOutOfBoundsException e) {
            //https://issues.apache.org/bugzilla/show_bug.cgi?id=51431 & http://stackoverflow.com/questions/6469693/apache-poi-clearing-freeze-split-panes
        }
        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.excelimport.ExcelUtilTest.java

License:Open Source License

private void testGetFullCellName(Workbook wb) {

    Sheet sheet1 = wb.createSheet("Sheet-1");
    Row row1 = sheet1.createRow(0);/* ww w .  j  ava2 s.c  o m*/
    Cell cell1 = row1.createCell(0);

    assertEquals("Sheet-1 A-1", ExcelUtils.getFullCellName(cell1));
    assertEquals("<no-cell>", ExcelUtils.getCellRefName(null));
}