Example usage for org.apache.poi.ss.usermodel Sheet autoSizeColumn

List of usage examples for org.apache.poi.ss.usermodel Sheet autoSizeColumn

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. autoSizeColumn

Introduction

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

Prototype

void autoSizeColumn(int column);

Source Link

Document

Adjusts the column width to fit the contents.

Usage

From source file:jp.ac.tohoku.ecei.sb.metabolome.lims.progenesis.ProgenesisLoader.java

License:Open Source License

public static XSSFWorkbook createTemplateXlsxFile(InputStream is)
        throws IOException, InvalidSampleInfoFormatException {
    XSSFWorkbook xlsx = new XSSFWorkbook(
            ProgenesisLoader.class.getResourceAsStream("metabolome-dataset-template.xlsx"));
    Sheet injectionSheet = xlsx.getSheet("Injection");
    int numberOfInjections = 0;

    try (Reader reader = new InputStreamReader(is)) {
        CSVReader csvReader = new CSVReader(reader);
        String[] header1 = csvReader.readNext();
        String[] header2 = csvReader.readNext();
        String[] header3 = csvReader.readNext();

        int dataOffset1 = Arrays.asList(header1).indexOf("Raw abundance");
        if (dataOffset1 < 0)
            throw new InvalidSampleInfoFormatException(
                    "\"Raw abundance\" is not found in the first line. May be invalid progenesis file");

        int dataOffset2 = Arrays.asList(header2).indexOf("Tags");
        if (dataOffset2 < 0 || dataOffset1 >= dataOffset2)
            throw new InvalidSampleInfoFormatException(
                    "\"Tags\" is not found in the second line. May be invalid progenesis file");

        List<String> filenameList = Arrays.asList(header3).subList(dataOffset1, dataOffset2);
        numberOfInjections = filenameList.size();

        for (int i = 0; i < numberOfInjections; i++) {
            Row row = injectionSheet.createRow(i + 1);
            row.createCell(0, Cell.CELL_TYPE_NUMERIC).setCellValue(i + 1);
            row.createCell(3, Cell.CELL_TYPE_STRING).setCellValue(filenameList.get(i));
        }/* w  w  w.ja v  a  2 s . c om*/
        injectionSheet.autoSizeColumn(0);
        injectionSheet.autoSizeColumn(3);
    }

    {
        // Create plate template
        Sheet plateSheet = xlsx.getSheet("Plate");
        for (int i = 0; i < Math.ceil(numberOfInjections / 117.); i++) {
            Row row = plateSheet.createRow(1 + i);
            row.createCell(0, Cell.CELL_TYPE_NUMERIC).setCellValue(1 + i);
            row.createCell(1, Cell.CELL_TYPE_NUMERIC).setCellValue("Sample plate " + (i + 1));
            row.createCell(2, Cell.CELL_TYPE_STRING).setCellValue(DATE_FORMAT.format(new Date()));
        }

        for (int i = 0; i < 3; i++)
            plateSheet.autoSizeColumn(i);
    }

    {
        // Create sample template
        Sheet sampleSheet = xlsx.getSheet("Sample");
        Row blankRow = sampleSheet.createRow(1);
        blankRow.createCell(0, Cell.CELL_TYPE_NUMERIC).setCellValue(1);
        blankRow.createCell(1, Cell.CELL_TYPE_STRING).setCellValue("C7772A67-3345-4444-A3AF-14D8A0FB77F4");
        blankRow.createCell(2, Cell.CELL_TYPE_STRING).setCellValue("BLANK");
        blankRow.createCell(3, Cell.CELL_TYPE_STRING).setCellValue("blank");

        for (int i = 0; i < 2; i++) {
            Row qcRow = sampleSheet.createRow(2 + i);
            qcRow.createCell(0, Cell.CELL_TYPE_NUMERIC).setCellValue(2 + i);
            qcRow.createCell(2, Cell.CELL_TYPE_STRING).setCellValue("QC");
            qcRow.createCell(3, Cell.CELL_TYPE_STRING).setCellValue("Quality Control " + (i + 1));
        }

        for (int i = 0; i < 4; i++)
            sampleSheet.autoSizeColumn(i);
    }

    return xlsx;
}

From source file:model.Report.java

/**
 *
 * @param sheet//www  . j av a2  s  .  c  om
 * @param columns
 */
public void autoSize(Sheet sheet, int columns) {
    for (int i = 0; i < columns; i++) {
        sheet.autoSizeColumn(i);
    }
}

From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java

License:Open Source License

private File createExcelTempFile(Map<AbstractEntity, Map<Service, List<ArticleDemande>>> map,
        AbstractEntity abstractEntity, String nomFichierSansExtension) throws IOException {
    Workbook wb = new XSSFWorkbook();
    Map<Service, List<ArticleDemande>> mapServiceListeArticleDemande = map.get(abstractEntity);

    for (Service service : mapServiceListeArticleDemande.keySet()) {
        Sheet sheet = wb
                .createSheet(service.getDirection().getLibelleCourt() + "-" + service.getLibelleCourt());
        sheet.protectSheet(configService.getMotDePasseVerrouExcel());

        construitEntete(wb, mapServiceListeArticleDemande, abstractEntity, service, sheet);
        remplitLigneArticle(wb, mapServiceListeArticleDemande.get(service), sheet);

        // Taille automatique des colonnes selon le contenu
        for (int i = 0; i < 20; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 768);
        }//from   ww  w  . ja  v a 2 s. co m
    }
    // Ecriture dans le fichier
    File result = File.createTempFile(nomFichierSansExtension, ".xlsx");
    FileOutputStream fileStream = new FileOutputStream(result);
    wb.write(fileStream);
    return result;
}

From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java

License:Open Source License

@Override
public void exportCatalogue(Catalogue catalogue) throws IOException {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("Feuil1");

    construitEnteteExportCatalogue(wb, sheet);
    int i = 1;//  w  w  w  .  j a  v a 2  s .  c o m
    for (Famille famille : catalogue.getListeFamille()) {
        for (SousFamille sousFamille : famille.getListeSousFamille()) {
            for (ArticleCatalogue articleCatalogue : sousFamille.getListeArticleCatalogue()) {
                remplitLigneArticleExportCatalogue(wb, sheet, articleCatalogue, i++);
            }
        }
    }

    // Taille automatique des colonnes selon le contenu
    for (int colonne = 0; colonne <= 12; colonne++) {
        sheet.autoSizeColumn(colonne);
        sheet.setColumnWidth(colonne, sheet.getColumnWidth(colonne) + 768);
    }

    // Ecriture dans le fichier
    File result = File.createTempFile("Export Catalogue", ".xlsx");
    FileOutputStream fileStream = new FileOutputStream(result);
    wb.write(fileStream);

    downloadService.downloadToUser(result,
            "Export Catalogue " + AppockUtil.replaceAllSpecialCharacter(catalogue.getLibelle()) + ".xlsx");
    result.delete();
}

From source file:nc.noumea.mairie.organigramme.utils.ExportExcelUtil.java

License:Open Source License

@GlobalCommand
public static void genereExportExcel(EntiteDto entiteDto, boolean afficheFdpInactive,
        ISirhWSConsumer sirhWSConsumer) throws IOException {
    Workbook wb = new HSSFWorkbook();
    Sheet feuillePrincipale = wb.createSheet("Fiches de postes");
    creerEntete(feuillePrincipale);//ww w. jav  a  2 s.com

    String listIdStatutFDP = StatutFichePoste.getListIdStatutActif();

    if (afficheFdpInactive) {
        listIdStatutFDP += "," + StatutFichePoste.INACTIVE.getId();
    }

    int numeroLigne = 1;
    for (FichePosteDto fichePosteDto : sirhWSConsumer.getFichePosteByIdEntite(entiteDto.getIdEntite(),
            listIdStatutFDP, true)) {
        Row row = feuillePrincipale.createRow((short) numeroLigne);
        row.createCell(NUM_COL_SIGLE).setCellValue(fichePosteDto.getSigle());
        row.createCell(NUM_COL_NUMERO).setCellValue(fichePosteDto.getNumero());
        row.createCell(NUM_COL_TITRE).setCellValue(fichePosteDto.getTitre());
        row.createCell(NUM_COL_STATUT).setCellValue(fichePosteDto.getStatutFDP());
        row.createCell(NUM_COL_CATEGORIE).setCellValue(fichePosteDto.getLibelleGradeCategorie());
        row.createCell(NUM_COL_AGENT).setCellValue(fichePosteDto.getAgent());
        row.createCell(NUM_COL_REGLEMENTAIRE).setCellValue(fichePosteDto.getReglementaire());
        row.createCell(NUM_COL_COMMENTAIRE).setCellValue(fichePosteDto.getCommentaire());

        numeroLigne++;
    }

    // Aucolumns
    for (int i = NUM_COL_SIGLE; i <= NUM_COL_COMMENTAIRE; i++) {
        feuillePrincipale.autoSizeColumn(i);
    }

    File tempFile = File.createTempFile("tempFile", ".xls");
    FileOutputStream fileStream = new FileOutputStream(tempFile);
    wb.write(fileStream);
    FileInputStream fileInputStream = new FileInputStream(tempFile);

    // Cration et sauvegarde du fichier
    String nomFichier = "Organigramme - Impression Fiche Poste - " + entiteDto.getSigle() + "-"
            + DateUtil.formatDateForFile(new Date()) + ".xls";
    Filedownload.save(IOUtils.toByteArray(fileInputStream), null, nomFichier);
}

From source file:net.rrm.ehour.ui.timesheet.export.TimesheetExcelExport.java

License:Open Source License

private ExcelWorkbook createWorkbook(Report report) {
    ExcelWorkbook workbook = new ExcelWorkbook();

    String sheetName = WebUtils.formatDate("MMMM yyyy", report.getReportRange().getDateStart());
    Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(sheetName));

    sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.DATE.getColumn()));
    sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.CUSTOMER_CODE.getColumn()));
    sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.PROJECT.getColumn()));
    sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.PROJECT_CODE.getColumn()));
    sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.HOURS.getColumn()));
    sheet.setColumnWidth(0, 1024);/*from  ww  w  .j a v a2s.  co  m*/

    int rowNumber = 9;

    rowNumber = new ExportReportHeader(CELL_BORDER, sheet, report, workbook).createPart(rowNumber);
    rowNumber = new ExportReportBodyHeader(CELL_BORDER, sheet, report, workbook).createPart(rowNumber);
    rowNumber = new ExportReportBody(CELL_BORDER, sheet, report, workbook).createPart(rowNumber);
    rowNumber = new ExportReportTotal(CELL_BORDER, sheet, report, workbook).createPart(rowNumber);

    if (isInclSignOff(report)) {
        new ExportReportSignOff(CELL_BORDER, sheet, report, workbook).createPart(rowNumber + 1);
    }

    return workbook;
}

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void postProcessSheet(Sheet sheet) {
    // Set the with to auto
    int numcells = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < numcells; i++) {
        sheet.autoSizeColumn(i);
    }//w w w . j a v  a  2s. co  m
    processUpdates(sheet);
}

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void processQuitters() {
    if (hasPrevious()) {
        Sheet sheet = workbook.getSheet(SorterFactory.GROUP_NAME_ALL);
        List<Row> quitters = new ArrayList<Row>();
        FileInputStream previousStream = null;
        try {/*  w  w  w.  ja va2s .c o  m*/
            previousStream = new FileInputStream(previous);
            //Get the workbook instance for XLS file
            HSSFWorkbook prevWorkbook = new HSSFWorkbook(previousStream);
            Sheet prevSheet = prevWorkbook.getSheet(SorterFactory.GROUP_NAME_ALL);
            if (prevSheet == null) {
                return;
            }
            // Check of er mensen vertrokken zijn
            for (Row row : prevSheet) {
                if (row.getRowNum() > 0) {
                    String lidnummer = row.getCell(NUM_LIDNUMMER_CELL).getStringCellValue();
                    Row currentRow = getLidFromSheet(lidnummer, sheet);
                    if (currentRow == null) {
                        quitters.add(row);
                    }
                }
            }
        } catch (IOException ex) {
            System.out.println("Error Reading the previous file: " + ex.getLocalizedMessage());
            return;
        } finally {
            try {
                if (previousStream != null) {
                    previousStream.close();
                }
            } catch (IOException ex) {
                System.out.println("Problems closing file: " + ex.getLocalizedMessage());
            }
        }
        if (quitters.isEmpty()) {
            return;
        }
        Sheet removedSheet = workbook.createSheet(SHEET_REMOVED_PERSONS);

        // Create header
        Row header = removedSheet.createRow(0);
        //Lidnummer   Achternaam   Tussenvoegsel   Voornaam   Geslacht    Telefoonnummer   Mobiel  Geboortedatum

        Cell lidnummer = header.createCell(0);
        lidnummer.setCellValue("Lidnummer");
        lidnummer.setCellStyle(headingStyle);
        Cell achternaam = header.createCell(1);
        achternaam.setCellValue("Achternaam");
        achternaam.setCellStyle(headingStyle);
        Cell tussenvoegsel = header.createCell(2);
        tussenvoegsel.setCellValue("Tussenvoegsel");
        tussenvoegsel.setCellStyle(headingStyle);
        Cell voornaam = header.createCell(3);
        voornaam.setCellValue("Voornaam");
        voornaam.setCellStyle(headingStyle);
        Cell geslacht = header.createCell(4);
        geslacht.setCellValue("Geslacht");
        geslacht.setCellStyle(headingStyle);
        Cell telefoonnummer = header.createCell(5);
        telefoonnummer.setCellValue("Telefoonnummer");
        telefoonnummer.setCellStyle(headingStyle);
        Cell geboortedatum = header.createCell(6);
        geboortedatum.setCellValue("Geboortedatum");
        geboortedatum.setCellStyle(headingStyle);

        // Iterate over quitters
        int index = 1;
        for (Row quitter : quitters) {
            Row r = removedSheet.createRow(index);
            r.createCell(0).setCellValue(quitter.getCell(0).getStringCellValue());
            r.createCell(1).setCellValue(quitter.getCell(1).getStringCellValue());
            r.createCell(2).setCellValue(quitter.getCell(2).getStringCellValue());
            r.createCell(3).setCellValue(quitter.getCell(3).getStringCellValue());
            r.createCell(4).setCellValue(quitter.getCell(5).getStringCellValue());
            r.createCell(5).setCellValue(quitter.getCell(10).getStringCellValue());
            r.createCell(6).setCellValue(quitter.getCell(21).getStringCellValue());
            index++;
        }

        removedSheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 6));
        int numcells = removedSheet.getRow(0).getLastCellNum();
        for (int i = 0; i < numcells; i++) {
            removedSheet.autoSizeColumn(i);
        }
    }
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetColumnFitToSize.java

License:Open Source License

public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException {
    cfSpreadSheetData spreadsheet = null;

    /*//from  ww w . ja v  a 2  s .  c o  m
     * Collect up the parameters
     */
    spreadsheet = (cfSpreadSheetData) parameters.get(1);
    String column = parameters.get(0).getString();

    // Not a single number; lets try the string method
    Sheet sheet = spreadsheet.getActiveSheet();
    Set<Integer> numbers = tagUtils.getNumberSet(column);
    Iterator<Integer> it = numbers.iterator();
    while (it.hasNext()) {
        sheet.autoSizeColumn(it.next() - 1);
    }

    return cfBooleanData.TRUE;
}

From source file:org.alfresco.repo.web.scripts.datalist.DataListDownloadWebScript.java

License:Open Source License

@Override
protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties)
        throws IOException {
    NodeRef list = (NodeRef) resource;/*from  w  w  w  .  j  a v a  2  s  .  c  om*/
    List<NodeRef> items = getItems(list);

    // Our various formats
    DataFormat formatter = workbook.createDataFormat();

    CellStyle styleInt = workbook.createCellStyle();
    styleInt.setDataFormat(formatter.getFormat("0"));
    CellStyle styleDate = workbook.createCellStyle();
    styleDate.setDataFormat(formatter.getFormat("yyyy-mm-dd"));
    CellStyle styleDouble = workbook.createCellStyle();
    styleDouble.setDataFormat(formatter.getFormat("General"));
    CellStyle styleNewLines = workbook.createCellStyle();
    styleNewLines.setWrapText(true);

    // Export the items
    int rowNum = 1, colNum = 0;
    for (NodeRef item : items) {
        Row r = sheet.createRow(rowNum);

        colNum = 0;
        for (QName prop : properties) {
            Cell c = r.createCell(colNum);

            Serializable val = nodeService.getProperty(item, prop);
            if (val == null) {
                // Is it an association, or just missing?
                List<AssociationRef> assocs = nodeService.getTargetAssocs(item, prop);
                if (assocs.size() > 0) {
                    StringBuffer text = new StringBuffer();
                    int lines = 1;

                    for (AssociationRef ref : assocs) {
                        NodeRef child = ref.getTargetRef();
                        QName type = nodeService.getType(child);
                        if (ContentModel.TYPE_PERSON.equals(type)) {
                            if (text.length() > 0) {
                                text.append('\n');
                                lines++;
                            }
                            text.append(nodeService.getProperty(child, ContentModel.PROP_USERNAME));
                        } else if (ContentModel.TYPE_CONTENT.equals(type)) {
                            // TODO Link to the content
                            if (text.length() > 0) {
                                text.append('\n');
                                lines++;
                            }
                            text.append(nodeService.getProperty(child, ContentModel.PROP_TITLE));
                        } else {
                            System.err.println("TODO: handle " + type + " for " + child);
                        }
                    }

                    String v = text.toString();
                    c.setCellValue(v);
                    if (lines > 1) {
                        c.setCellStyle(styleNewLines);
                        r.setHeightInPoints(lines * sheet.getDefaultRowHeightInPoints());
                    }
                } else {
                    // This property isn't set
                    c.setCellType(Cell.CELL_TYPE_BLANK);
                }
            } else {
                // Regular property, set
                if (val instanceof String) {
                    c.setCellValue((String) val);
                } else if (val instanceof Date) {
                    c.setCellValue((Date) val);
                    c.setCellStyle(styleDate);
                } else if (val instanceof Integer || val instanceof Long) {
                    double v = 0.0;
                    if (val instanceof Long)
                        v = (double) (Long) val;
                    if (val instanceof Integer)
                        v = (double) (Integer) val;
                    c.setCellValue(v);
                    c.setCellStyle(styleInt);
                } else if (val instanceof Float || val instanceof Double) {
                    double v = 0.0;
                    if (val instanceof Float)
                        v = (double) (Float) val;
                    if (val instanceof Double)
                        v = (double) (Double) val;
                    c.setCellValue(v);
                    c.setCellStyle(styleDouble);
                } else {
                    // TODO
                    System.err.println("TODO: handle " + val.getClass().getName() + " - " + val);
                }
            }

            colNum++;
        }

        rowNum++;
    }

    // Sensible column widths please!
    colNum = 0;
    for (QName prop : properties) {
        sheet.autoSizeColumn(colNum);
        colNum++;
    }
}