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

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.griffinslogistics.excel.BookLabelGenerator.java

private static void generateTitle(Sheet sheet, Map<String, CellStyle> styles, BookLabelModel bookLabelModel) {
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A6:A7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D6:D7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A16:A17"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D16:D17"));

    Row row6 = sheet.getRow(5);
    Row row7 = sheet.getRow(6);/*from  w  w  w.ja  v a  2 s  .c  om*/

    Cell a6 = row6.createCell(0);
    a6.setCellStyle(styles.get(STYLE_LABEL));
    a6.setCellValue(TITLE);

    Cell a7 = row7.createCell(0);
    a7.setCellStyle(styles.get(STYLE_LABEL));

    Cell b6 = row6.createCell(1);
    b6.setCellStyle(styles.get(STYLE_CONTENT));
    b6.setCellValue(bookLabelModel.getTitle());

    Cell b7 = row7.createCell(1);
    b7.setCellStyle(styles.get(STYLE_CONTENT));
    b7.setCellValue(bookLabelModel.getBookNumber());

    Cell d6 = row6.createCell(3);
    d6.setCellStyle(styles.get(STYLE_LABEL));
    d6.setCellValue(TITLE);

    Cell d7 = row7.createCell(3);
    d7.setCellStyle(styles.get(STYLE_LABEL));

    Cell e6 = row6.createCell(4);
    e6.setCellStyle(styles.get(STYLE_CONTENT));
    e6.setCellValue(bookLabelModel.getTitle());

    Cell e7 = row7.createCell(4);
    e7.setCellStyle(styles.get(STYLE_CONTENT));
    e7.setCellValue(bookLabelModel.getBookNumber());

    Row row16 = sheet.getRow(15);
    Row row17 = sheet.getRow(16);

    Cell a16 = row16.createCell(0);
    a16.setCellStyle(styles.get(STYLE_LABEL));
    a16.setCellValue(TITLE);

    Cell a17 = row17.createCell(0);
    a17.setCellStyle(styles.get(STYLE_LABEL));

    Cell b16 = row16.createCell(1);
    b16.setCellStyle(styles.get(STYLE_CONTENT));
    b16.setCellValue(bookLabelModel.getTitle());

    Cell b17 = row17.createCell(1);
    b17.setCellStyle(styles.get(STYLE_CONTENT));
    b17.setCellValue(bookLabelModel.getBookNumber());

    Cell d16 = row16.createCell(3);
    d16.setCellStyle(styles.get(STYLE_LABEL));
    d16.setCellValue(TITLE);

    Cell d17 = row17.createCell(3);
    d17.setCellStyle(styles.get(STYLE_LABEL));

    Cell e16 = row16.createCell(4);
    e16.setCellStyle(styles.get(STYLE_CONTENT));
    e16.setCellValue(bookLabelModel.getTitle());

    Cell e17 = row17.createCell(4);
    e17.setCellStyle(styles.get(STYLE_CONTENT));
    e17.setCellValue(bookLabelModel.getBookNumber());
}

---

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos,
        Medico AdministratorUser) throws IOException {

    Guardias[] lGuardias;/*from www  .  ja va 2  s  . com*/

    Gson gson = new GsonBuilder().create();

    lGuardias = gson.fromJson(JSONContenidos, Guardias[].class);

    boolean xlsx = true;
    int year = calendar.get(Calendar.YEAR);
    int month = calendar.get(Calendar.MONTH);

    DateFormat _format = new SimpleDateFormat("yyyy-MM-dd");

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    calendar.set(Calendar.MONTH, month);
    calendar.set(Calendar.DAY_OF_MONTH, 1);

    calendar.setFirstDayOfWeek(Calendar.MONDAY);
    //create a sheet for each month
    Sheet sheet = wb.createSheet(_format.format(calendar.getTime()));

    CellStyle styleBORDER = wb.createCellStyle();
    styleBORDER.setBorderRight(CellStyle.BORDER_THICK);
    styleBORDER.setBorderBottom(CellStyle.BORDER_THICK);
    styleBORDER.setBorderTop(CellStyle.BORDER_THICK);
    styleBORDER.setBorderLeft(CellStyle.BORDER_THICK);
    styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    //

    //turn off gridlines
    sheet.setDisplayGridlines(true);
    sheet.autoSizeColumn(0);
    sheet.setPrintGridlines(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //header with month titles
    Row monthRow = sheet.createRow(1);
    Font fontH = wb.createFont();
    CellStyle CStyleH = wb.createCellStyle();
    CStyleH.setBorderRight(CellStyle.BORDER_THICK);
    CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    fontH.setBold(true);
    CStyleH.setFont(fontH);
    for (int i = 0; i < days.length; i++) {

        Cell monthCell = monthRow.createCell(i);

        monthCell.setCellStyle(CStyleH);
        monthCell.setCellValue(days[i]);
        sheet.autoSizeColumn(i);

    }

    int cnt = 1, day = 1;
    int rownum = 2;
    for (int j = 0; j < 6; j++) {
        Row row = sheet.createRow(rownum++);
        Row rowGuardias;
        boolean bRowsCreated = false;

        // row.setHeightInPoints(100);
        for (int i = 0; i < days.length; i++) {
            Cell dayCell_1 = row.createCell(i);
            //  Cell dayCell_2 = row.createCell(i*2 + 1);

            int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7;
            //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);  
            if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) {

                Font font = wb.createFont();
                CellStyle CStyle = wb.createCellStyle();
                short colorI = HSSFColor.AQUA.index; // presencia
                //font.set(colorI);
                CStyle.setFillForegroundColor(colorI);
                CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //CStyle.setBorderBottom( colorBorder);
                CStyle.setBorderRight(CellStyle.BORDER_THICK);
                CStyle.setBorderBottom(CellStyle.BORDER_THICK);
                CStyle.setBorderTop(CellStyle.BORDER_THICK);
                CStyle.setBorderLeft(CellStyle.BORDER_THICK);
                CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                //CStyle.setFont(font);

                dayCell_1.setCellValue(day);
                dayCell_1.setCellStyle(CStyle);

                sheet.autoSizeColumn(i);

                String _Dia = _format.format(calendar.getTime());

                int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia
                for (int d = 0; d < lGuardias.length; d++) {

                    Guardias oGuardias = lGuardias[d];
                    if (oGuardias.getDiaGuardia().equals(_Dia)) {

                        if (!bRowsCreated)
                            rowGuardias = sheet.createRow(rownum++);
                        else
                            rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont);

                        Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i);
                        //    Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1);

                        List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(),
                                AdministratorUser.getServicioId());

                        Medico _oMedico = _lMedico.get(0);

                        font = wb.createFont();
                        CStyle = wb.createCellStyle();
                        // PRESENCIA 
                        // LOCALIZADA
                        //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre());
                        colorI = HSSFColor.LIGHT_ORANGE.index; // presencia                        
                        if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase()))
                            colorI = HSSFColor.GREEN.index;
                        else if (oGuardias.getTipo()
                                .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase()))
                            colorI = HSSFColor.BLUE.index;
                        else if (oGuardias.getTipo().equals("")) // residente
                            colorI = HSSFColor.RED.index;

                        font.setColor(colorI);

                        CStyle.setFont(font);
                        //CStyle.setBorderBottom( colorBorder);
                        CStyle.setBorderRight(CellStyle.BORDER_THICK);
                        CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());

                        dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre()
                                + "[" + _oMedico.getIDMEDICO() + "]");
                        dayCell_1_GUARDIAS.setCellStyle(CStyle);

                        DataRowCont++;

                    }

                }
                bRowsCreated = true;

                //      dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString());

                calendar.set(Calendar.DAY_OF_MONTH, ++day);

                /*if(i == 0 || i == days.length-1) {
                    dayCell_1.setCellStyle(styles.get("weekend_left"));
                    dayCell_2.setCellStyle(styles.get("weekend_right"));
                } else {
                    dayCell_1.setCellStyle(styles.get("workday_left"));
                    dayCell_2.setCellStyle(styles.get("workday_right"));
                }
                } else {
                dayCell_1.setCellStyle(styles.get("grey_left"));
                dayCell_2.setCellStyle(styles.get("grey_right"));*/
            }
            cnt++;
        }
        if (calendar.get(Calendar.MONTH) > month)
            break;
    }
    //  }

    // Write the output to a file
    String file = RutaFile;
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    wb.close();

}

---

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

@Override
public void apply() {
    for (DataObject dataObject : data) {
        HSSFCell templateCell = dataObject.templateCell;
        HSSFCell resultCell = dataObject.resultCell;
        BandData bandData = dataObject.bandData;

        HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook();
        HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook();

        String templateCellValue = templateCell.getStringCellValue();

        Matcher matcher = pattern.matcher(templateCellValue);
        if (matcher.find()) {
            String paramName = matcher.group(1);
            String styleName = (String) bandData.getParameterValue(paramName);
            if (styleName == null)
                continue;

            HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName);
            if (cellStyle == null)
                continue;

            HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle);

            if (resultStyle == null) {
                HSSFCellStyle newStyle = resultWorkbook.createCellStyle();
                // color
                newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor());
                newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor());
                newStyle.setFillPattern(cellStyle.getFillPattern());

                // borders
                newStyle.setBorderLeft(cellStyle.getBorderLeft());
                newStyle.setBorderRight(cellStyle.getBorderRight());
                newStyle.setBorderTop(cellStyle.getBorderTop());
                newStyle.setBorderBottom(cellStyle.getBorderBottom());

                // border colors
                newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor());
                newStyle.setRightBorderColor(cellStyle.getRightBorderColor());
                newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor());
                newStyle.setTopBorderColor(cellStyle.getTopBorderColor());

                // alignment
                newStyle.setAlignment(cellStyle.getAlignment());
                newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment());
                // misc
                DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat();
                newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString()));
                newStyle.setHidden(cellStyle.getHidden());
                newStyle.setLocked(cellStyle.getLocked());
                newStyle.setIndention(cellStyle.getIndention());
                newStyle.setRotation(cellStyle.getRotation());
                newStyle.setWrapText(cellStyle.getWrapText());
                // font
                HSSFFont cellFont = cellStyle.getFont(templateWorkbook);
                HSSFFont newFont = fontCache.getFontByTemplate(cellFont);

                if (newFont == null) {
                    newFont = resultWorkbook.createFont();

                    newFont.setFontName(cellFont.getFontName());
                    newFont.setItalic(cellFont.getItalic());
                    newFont.setStrikeout(cellFont.getStrikeout());
                    newFont.setTypeOffset(cellFont.getTypeOffset());
                    newFont.setBoldweight(cellFont.getBoldweight());
                    newFont.setCharSet(cellFont.getCharSet());
                    newFont.setColor(cellFont.getColor());
                    newFont.setUnderline(cellFont.getUnderline());
                    newFont.setFontHeight(cellFont.getFontHeight());
                    newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints());
                    fontCache.addCachedFont(cellFont, newFont);
                }//from  w ww. j a  v  a 2  s  .c o m
                newStyle.setFont(newFont);

                resultStyle = newStyle;
                styleCache.addCachedNamedStyle(cellStyle, resultStyle);
            }

            fixNeighbourCellBorders(cellStyle, resultCell);

            resultCell.setCellStyle(resultStyle);

            Sheet sheet = resultCell.getSheet();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {

                    int firstRow = mergedRegion.getFirstRow();
                    int lastRow = mergedRegion.getLastRow();
                    int firstCol = mergedRegion.getFirstColumn();
                    int lastCol = mergedRegion.getLastColumn();

                    for (int row = firstRow; row <= lastRow; row++)
                        for (int col = firstCol; col <= lastCol; col++)
                            sheet.getRow(row).getCell(col).setCellStyle(resultStyle);

                    // cell includes only in one merged region
                    break;
                }
            }
        }
    }
}

---

From source file:com.helger.genericode.excel.ExcelSheetToCodeList04.java

License:Apache License

@Nonnull
public static CodeListDocument convertToSimpleCodeList(@Nonnull final Sheet aExcelSheet,
        @Nonnull final ExcelReadOptions<UseType> aReadOptions, @Nonnull final String sCodeListName,
        @Nonnull final String sCodeListVersion, @Nonnull final URI aCanonicalUri,
        @Nonnull final URI aCanonicalVersionUri, @Nullable final URI aLocationURI) {
    ValueEnforcer.notNull(aExcelSheet, "ExcelSheet");
    ValueEnforcer.notNull(aReadOptions, "ReadOptions");

    final ObjectFactory aFactory = new ObjectFactory();
    final CodeListDocument ret = aFactory.createCodeListDocument();

    // create annotation
    final Annotation aAnnotation = aFactory.createAnnotation();
    final AnyOtherContent aContent = aFactory.createAnyOtherContent();
    aContent.getAny().add(new JAXBElement<String>(QNAME_ANNOTATION, String.class, null,
            "Automatically created by ph-genericode. Do NOT edit."));
    aAnnotation.setAppInfo(aContent);//from  w w w .  ja va 2 s.  c  om
    ret.setAnnotation(aAnnotation);

    // create identification
    final Identification aIdentification = aFactory.createIdentification();
    aIdentification.setShortName(Genericode04Utils.createShortName(sCodeListName));
    aIdentification.setVersion(sCodeListVersion);
    aIdentification.setCanonicalUri(aCanonicalUri.toString());
    aIdentification.setCanonicalVersionUri(aCanonicalVersionUri.toString());
    if (aLocationURI != null)
        aIdentification.getLocationUri().add(aLocationURI.toString());
    ret.setIdentification(aIdentification);

    // create columns
    final List<ExcelReadColumn<UseType>> aExcelColumns = aReadOptions.getAllColumns();
    final ColumnSet aColumnSet = aFactory.createColumnSet();
    for (final ExcelReadColumn<UseType> aExcelColumn : aExcelColumns) {
        // Read short name (required)
        final String sShortName = aExcelSheet.getRow(aReadOptions.getLineIndexShortName())
                .getCell(aExcelColumn.getIndex()).getStringCellValue();

        // Read long name (optional)
        String sLongName = null;
        if (aReadOptions.getLineIndexLongName() >= 0)
            sLongName = aExcelSheet.getRow(aReadOptions.getLineIndexLongName()).getCell(aExcelColumn.getIndex())
                    .getStringCellValue();

        // Create Genericode column set
        final Column aColumn = Genericode04Utils.createColumn(aExcelColumn.getColumnID(),
                aExcelColumn.getUseType(), sShortName, sLongName, aExcelColumn.getDataType());

        // add column
        aColumnSet.getColumnChoice().add(aColumn);

        if (aExcelColumn.isKeyColumn()) {
            // Create key definition
            final Key aKey = Genericode04Utils.createKey(aExcelColumn.getColumnID() + "Key", sShortName,
                    sLongName, aColumn);

            // Add key
            aColumnSet.getKeyChoice().add(aKey);
        }
    }
    ret.setColumnSet(aColumnSet);

    // Read items
    final SimpleCodeList aSimpleCodeList = aFactory.createSimpleCodeList();

    // Determine the row where reading should start
    int nRowIndex = aReadOptions.getLinesToSkip();
    while (true) {
        // Read a single excel row
        final org.apache.poi.ss.usermodel.Row aExcelRow = aExcelSheet.getRow(nRowIndex++);
        if (aExcelRow == null)
            break;

        // Create Genericode row
        final Row aRow = aFactory.createRow();
        for (final ExcelReadColumn<UseType> aExcelColumn : aExcelColumns) {
            final String sValue = ExcelReadUtils.getCellValueString(aExcelRow.getCell(aExcelColumn.getIndex()));
            if (StringHelper.hasText(sValue) || aExcelColumn.getUseType() == UseType.REQUIRED) {
                // Create a single value in the current row
                final Value aValue = aFactory.createValue();
                aValue.setColumnRef(Genericode04Utils.getColumnOfID(aColumnSet, aExcelColumn.getColumnID()));
                aValue.setSimpleValue(Genericode04Utils.createSimpleValue(sValue));
                aRow.getValue().add(aValue);
            }
        }
        aSimpleCodeList.getRow().add(aRow);
    }
    ret.setSimpleCodeList(aSimpleCodeList);

    return ret;
}

---

From source file:com.helger.genericode.excel.ExcelSheetToCodeList10.java

License:Apache License

@Nonnull
public static CodeListDocument convertToSimpleCodeList(@Nonnull final Sheet aExcelSheet,
        @Nonnull final ExcelReadOptions<UseType> aReadOptions, @Nonnull final String sCodeListName,
        @Nonnull final String sCodeListVersion, @Nonnull final URI aCanonicalUri,
        @Nonnull final URI aCanonicalVersionUri, @Nullable final URI aLocationURI) {
    ValueEnforcer.notNull(aExcelSheet, "ExcelSheet");
    ValueEnforcer.notNull(aReadOptions, "ReadOptions");

    final ObjectFactory aFactory = new ObjectFactory();
    final CodeListDocument ret = aFactory.createCodeListDocument();

    // create annotation
    final Annotation aAnnotation = aFactory.createAnnotation();
    final AnyOtherContent aContent = aFactory.createAnyOtherContent();
    aContent.getAny().add(new JAXBElement<String>(QNAME_ANNOTATION, String.class, null,
            "Automatically created by ph-genericode. Do NOT edit."));
    aAnnotation.setAppInfo(aContent);//from  w  w w  . j  a  va  2s.  c o m
    ret.setAnnotation(aAnnotation);

    // create identification
    final Identification aIdentification = aFactory.createIdentification();
    aIdentification.setShortName(Genericode10Utils.createShortName(sCodeListName));
    aIdentification.setVersion(sCodeListVersion);
    aIdentification.setCanonicalUri(aCanonicalUri.toString());
    aIdentification.setCanonicalVersionUri(aCanonicalVersionUri.toString());
    if (aLocationURI != null)
        aIdentification.getLocationUri().add(aLocationURI.toString());
    ret.setIdentification(aIdentification);

    // create columns
    final List<ExcelReadColumn<UseType>> aExcelColumns = aReadOptions.getAllColumns();
    final ColumnSet aColumnSet = aFactory.createColumnSet();
    for (final ExcelReadColumn<UseType> aExcelColumn : aExcelColumns) {
        // Read short name (required)
        final String sShortName = aExcelSheet.getRow(aReadOptions.getLineIndexShortName())
                .getCell(aExcelColumn.getIndex()).getStringCellValue();

        // Read long name (optional)
        String sLongName = null;
        if (aReadOptions.getLineIndexLongName() >= 0)
            sLongName = aExcelSheet.getRow(aReadOptions.getLineIndexLongName()).getCell(aExcelColumn.getIndex())
                    .getStringCellValue();

        // Create Genericode column set
        final Column aColumn = Genericode10Utils.createColumn(aExcelColumn.getColumnID(),
                aExcelColumn.getUseType(), sShortName, sLongName, aExcelColumn.getDataType());

        // add column
        aColumnSet.getColumnChoice().add(aColumn);

        if (aExcelColumn.isKeyColumn()) {
            // Create key definition
            final Key aKey = Genericode10Utils.createKey(aExcelColumn.getColumnID() + "Key", sShortName,
                    sLongName, aColumn);

            // Add key
            aColumnSet.getKeyChoice().add(aKey);
        }
    }
    ret.setColumnSet(aColumnSet);

    // Read items
    final SimpleCodeList aSimpleCodeList = aFactory.createSimpleCodeList();

    // Determine the row where reading should start
    int nRowIndex = aReadOptions.getLinesToSkip();
    while (true) {
        // Read a single excel row
        final org.apache.poi.ss.usermodel.Row aExcelRow = aExcelSheet.getRow(nRowIndex++);
        if (aExcelRow == null)
            break;

        // Create Genericode row
        final Row aRow = aFactory.createRow();
        for (final ExcelReadColumn<UseType> aExcelColumn : aExcelColumns) {
            final String sValue = ExcelReadUtils.getCellValueString(aExcelRow.getCell(aExcelColumn.getIndex()));
            if (StringHelper.hasText(sValue) || aExcelColumn.getUseType() == UseType.REQUIRED) {
                // Create a single value in the current row
                final Value aValue = aFactory.createValue();
                aValue.setColumnRef(Genericode10Utils.getColumnOfID(aColumnSet, aExcelColumn.getColumnID()));
                aValue.setSimpleValue(Genericode10Utils.createSimpleValue(sValue));
                aRow.getValue().add(aValue);
            }
        }
        aSimpleCodeList.getRow().add(aRow);
    }
    ret.setSimpleCodeList(aSimpleCodeList);

    return ret;
}

---

From source file:com.helger.genetic.tsp.result.evaluation.MainTSPRunnerBerlin52BestSeveral.java

License:Apache License

public static void main(final String[] args) throws IOException {
    GlobalDebug.setDebugModeDirect(true);
    final Matrix aDistances = readTSPFromFile(new ClassPathResource("tsp/berlin52.tsp"), true);

    final int nOptimumDistance = 7542;
    final int nCities = aDistances.getRowDimension();
    final TSPFitnessFunction ff = new TSPFitnessFunction(aDistances);
    final TSPChromosomeValidator cv = true ? null : new TSPChromosomeValidator(nCities);

    // Use fixed seed only once
    RandomGenerator.setRandomGenerator(new RandomGeneratorRandom(new Random(789234789989L)));

    final int nRepeats = 5;
    final List<TIntList> aDistanceListPerPopulations = new ArrayList<TIntList>(nRepeats);
    final List<TIntList> aDistanceListBest = new ArrayList<TIntList>(nRepeats);
    for (int i = 0; i < nRepeats; ++i) {
        final int nPopulationSize = nCities;
        final TSPEventHandlerGenerationTracker eh = new TSPEventHandlerGenerationTracker(ff);
        IContinuation cont = null;/*from  w w w. j a  va 2  s.c  o  m*/
        cont = new ContinuationTotalGeneration(1000, cont);
        cont = new ContinuationKnownOptimum(ff.getFitness(nOptimumDistance), eh, cont);
        if (false)
            cont = new ContinuationTimeBased(20 * CGlobal.MILLISECONDS_PER_SECOND, cont);
        final IPopulationCreator pc = new TSPPopulationCreatorRandom(nCities, nPopulationSize, ff, cv);
        final ISelector s = new SelectorAllSortedBest(2);
        final ICrossover c = new CrossoverEdgeRecombination(new DecisionMakerPercentage(2));
        final IMutation m = new TSPMutationGreedyBeginning(aDistances, eh,
                new MutationRandomPartialReverse(new DecisionMakerPercentage(80)));

        new TSPRunner("berlin52").run(aDistances, nOptimumDistance, ff, eh, cont, pc, s, c, m);
        aDistanceListPerPopulations.add(eh.getDistanceListPerPopulation());
        aDistanceListBest.add(eh.getDistanceListBest());
    }

    final boolean bWithBest = false;
    final Workbook aWB = EExcelVersion.XLSX.createWorkbook();
    final Sheet aSheet = aWB.createSheet("STW CT");
    int nColumn = 0;
    Row aRow = aSheet.createRow(0);
    aRow.createCell(nColumn++).setCellValue("Generation");
    aRow.createCell(nColumn++).setCellValue("100%");
    aRow.createCell(nColumn++).setCellValue("110%");
    aRow.createCell(nColumn++).setCellValue("125%");
    for (int i = 0; i < nRepeats; ++i) {
        aRow.createCell(nColumn++).setCellValue("Run " + (i + 1));
        if (bWithBest)
            aRow.createCell(nColumn++).setCellValue("Best " + (i + 1));
    }

    // Ensure all rows are present
    int nMaxRows = 0;
    for (final TIntList aIL : aDistanceListPerPopulations)
        nMaxRows = Math.max(nMaxRows, aIL.size());

    // Create rows and set generation and optimum
    final double dOptimumDistance110 = nOptimumDistance * 1.1;
    final double dOptimumDistance125 = nOptimumDistance * 1.25;
    for (int i = 0; i < nMaxRows; ++i) {
        aRow = aSheet.createRow(1 + i);
        aRow.createCell(0).setCellValue(i + 1);
        aRow.createCell(1).setCellValue(nOptimumDistance);
        aRow.createCell(2).setCellValue(dOptimumDistance110);
        aRow.createCell(3).setCellValue(dOptimumDistance125);
    }

    // Set all values
    nColumn = 4;
    for (int i = 0; i < nRepeats; ++i) {
        int nRow = 1;
        final TIntIterator itPerPop = aDistanceListPerPopulations.get(i).iterator();
        while (itPerPop.hasNext())
            aSheet.getRow(nRow++).createCell(nColumn).setCellValue(itPerPop.next());
        ++nColumn;

        if (bWithBest) {
            nRow = 1;
            final TIntIterator itBest = aDistanceListBest.get(i).iterator();
            while (itBest.hasNext())
                aSheet.getRow(nRow++).createCell(nColumn).setCellValue(itBest.next());
            ++nColumn;
        }
    }
    aWB.write(FileHelper.getOutputStream(new File("data/berlin52/results-best-multiple.xlsx")));
}

---

From source file:com.helger.masterdata.tools.MainReadCountry2Continent.java

License:Apache License

public static void main(final String[] args) {
    final IReadableResource aRes = new ClassPathResource("country2continent.xlsx");
    final Workbook aWB = ExcelReadUtils.readWorkbookFromInputStream(aRes);
    final Sheet aSheet = aWB.getSheetAt(0);
    // Skip one row
    int nRow = 1;
    int nNotFound = 0;
    final Map<Locale, EContinent> aMap = new TreeMap<Locale, EContinent>(new ComparatorLocaleCountry(LOC));
    while (true) {
        final Row aRow = aSheet.getRow(nRow);
        if (aRow == null)
            break;
        final String sContinent = ExcelReadUtils.getCellValueString(aRow.getCell(0));
        if (StringHelper.hasNoText(sContinent))
            break;
        final EContinent eContinent = _findContinent(sContinent);
        final String sCountryName = ExcelReadUtils.getCellValueString(aRow.getCell(1));
        final Locale aCountry = _findCountryComplex(sCountryName);
        if (aCountry == null) {
            System.out.println("No such country: '" + sCountryName + "'");
            ++nNotFound;//w  w w  .  j av a  2 s.  c  o  m
        } else {
            final EContinent eOld = aMap.put(aCountry, eContinent);
            if (eOld != null)
                System.out.println("Country " + aCountry.getDisplayCountry() + " is assigned to "
                        + eContinent.getDisplayText(LOC) + " and " + eOld.getDisplayText(LOC));
        }

        ++nRow;
    }
    System.out.println("Countries not found: " + nNotFound);

    for (final Map.Entry<Locale, EContinent> e : aMap.entrySet()) {
        System.out.println("s_aMap.put (CountryCache.getCountry (\"" + e.getKey().getCountry()
                + "\"), EContinent." + e.getValue().name() + "),");
    }
}

---

From source file:com.helger.poi.excel.ExcelReadUtilsTest.java

License:Apache License

/**
 * Validate reference sheets/*from  w  ww  . j a  v  a2  s . c o  m*/
 * 
 * @param aWB
 *        Workbook to use
 */
private void _validateWorkbook(@Nonnull final Workbook aWB) {
    final Sheet aSheet1 = aWB.getSheet("Sheet1");
    assertNotNull(aSheet1);
    assertNotNull(aWB.getSheet("Sheet2"));
    final Sheet aSheet3 = aWB.getSheet("Sheet3");
    assertNotNull(aSheet3);
    assertNull(aWB.getSheet("Sheet4"));

    Cell aCell = aSheet1.getRow(0).getCell(0);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("A1", aCell.getStringCellValue());

    aCell = aSheet1.getRow(1).getCell(1);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("B2", aCell.getStringCellValue());

    aCell = aSheet1.getRow(2).getCell(2);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("C\n3", aCell.getStringCellValue());

    aCell = aSheet1.getRow(3).getCell(3);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_NUMERIC, aCell.getCellType());
    assertEquals(0.00001, 4.4, aCell.getNumericCellValue());

    for (int i = 0; i < 6; ++i) {
        aCell = aSheet3.getRow(i).getCell(i);
        assertNotNull(aCell);
        assertEquals(Cell.CELL_TYPE_NUMERIC, aCell.getCellType());
        assertEquals(0.00001, i + 1, aCell.getNumericCellValue());
    }

    // ="abc"
    aCell = aSheet1.getRow(4).getCell(0);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("\"abc\"", aCell.getCellFormula());
    assertEquals("abc", aCell.getStringCellValue());
    CellValue aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE)
            .evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aEvaluated.getCellType());
    assertEquals("abc", aEvaluated.getStringValue());

    // =4711
    aCell = aSheet1.getRow(5).getCell(1);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("4711", aCell.getCellFormula());
    assertEquals(0.00001, 4711, aCell.getNumericCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_NUMERIC, aEvaluated.getCellType());
    assertEquals(0.00001, 4711, aEvaluated.getNumberValue());

    // =TRUE
    aCell = aSheet1.getRow(6).getCell(2);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("TRUE", aCell.getCellFormula());
    assertTrue(aCell.getBooleanCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_BOOLEAN, aEvaluated.getCellType());
    assertTrue(aEvaluated.getBooleanValue());

    // Refers to cell at 6/2
    aCell = aSheet1.getRow(7).getCell(3);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("C7", aCell.getCellFormula());
    assertTrue(aCell.getBooleanCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_BOOLEAN, aEvaluated.getCellType());
    assertTrue(aEvaluated.getBooleanValue());
}

---

From source file:com.hp.idc.resm.util.ExcelUtil.java

License:Open Source License

/**
 * ,/* ww  w. j av  a 2 s . co  m*/
 * 
 * @param fileName
 *            excel, getModelExcel
 * @return 
 * @throws FileNotFoundException 
 */
public Map<String, String> readModelExcel(File file, String modelId) {
    if (modelId == null)
        return null;
    Map<String, String> m = new HashMap<String, String>();
    try {
        InputStream in = new FileInputStream(file);
        Workbook wb;
        try {
            wb = new HSSFWorkbook(in);
        } catch (IllegalArgumentException e) {
            wb = new XSSFWorkbook(in);
        }
        Sheet sheet = wb.getSheetAt(0);
        int total = sheet.getLastRowNum();
        Row row0 = sheet.getRow(0);
        String[] head = new String[row0.getLastCellNum()];
        for (int j = 0; j < row0.getLastCellNum(); j++) {
            String[] str = row0.getCell(j).getStringCellValue().split("/");
            if (str.length == 2) {
                head[j] = str[1];
            } else {
                head[j] = "";
            }
            System.out.println(head[j]);
        }
        Row row = null;
        Cell cell = null;
        for (int i = 1; i < total; i++) {
            m.clear();
            row = sheet.getRow(i);
            for (int j = 0; j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                m.put(head[j], cell.getStringCellValue());
                System.out.println(head[j] + "--" + cell.getStringCellValue());
            }
            // ServiceManager.getResourceUpdateService().addResource(modelId,
            // m, 1);
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        file.delete();
    }
    return m;
}

---

From source file:com.hust.zsuper.DealWithPatent.WorkhseetToMySQL.java

License:Open Source License

private void extractTypes(Sheet sheet) {

    {/*from   ww  w. j a v a  2s.  com*/
        int cellCount = columnOffset;
        //First row - get column names
        final Iterator<Cell> cellIterator = sheet.getRow(rowOffset).cellIterator();

        for (final Cell cell : new IteratorWrapper<Cell>(cellIterator)) {
            final String columnName = Utils.cleanUp(cell.getStringCellValue());
            addColumnName(cellCount, columnName, sheet);
            cellCount++;
        }
    }
    {
        int cellCount = columnOffset;
        //Second row - work out column type based on these values
        final Iterator<Cell> cellIterator = sheet.getRow(rowOffset + 1).cellIterator();
        for (final Cell cell : new IteratorWrapper<Cell>(cellIterator)) {
            final Entry<String, ExcelType> type = types.get(cellCount);
            if (type != null) {
                type.setValue(Utils.excelTypeToMySql(cell));
            }
            cellCount++;
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »