Example usage for org.apache.poi.ss.usermodel Row createCell

List of usage examples for org.apache.poi.ss.usermodel Row createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. createCell

Introduction

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

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:com.gtwm.pb.servlets.ReportDownloader.java

License:Open Source License

/**
 * Add a worksheet to the report for the specified workbook
 *//*from  w  w w .  ja  v  a2  s .c o m*/
private void addSummaryWorksheet(CompanyInfo company, SessionDataInfo sessionData, ChartInfo reportSummary,
        Workbook workbook) throws SQLException, CantDoThatException {
    ChartDataInfo reportSummaryData = this.databaseDefn.getDataManagement().getChartData(company, reportSummary,
            sessionData.getReportFilterValues(), false);
    if (reportSummaryData == null) {
        return;
    }
    int rowNum;
    Row row;
    Cell cell;
    int columnNum;
    String fieldValue;
    Sheet summarySheet;
    String summaryTitle = reportSummary.getTitle();
    if (summaryTitle == null) {
        summaryTitle = "Summary";
    } else if (summaryTitle.equals("")) {
        summaryTitle = "Summary";
    }
    // Replace any invalid characters : \ / ? * [ or ]
    // http://support.microsoft.com/kb/215205
    summaryTitle = summaryTitle.replaceAll("[\\/\\:\\\\\\?\\*\\[\\]]", "-");
    try {
        summarySheet = workbook.createSheet(summaryTitle);
    } catch (IllegalArgumentException iaex) {
        // sheet name must be unique
        summarySheet = workbook.createSheet(summaryTitle + " " + reportSummary.getId());
    }
    // header
    rowNum = 0;
    row = summarySheet.createRow(rowNum);
    columnNum = 0;
    CellStyle boldCellStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldCellStyle.setFont(font);
    Set<ChartAggregateInfo> aggregateFunctions = reportSummary.getAggregateFunctions();
    Set<ChartGroupingInfo> groupings = reportSummary.getGroupings();
    for (ChartGroupingInfo grouping : groupings) {
        BaseField groupingBaseField = grouping.getGroupingReportField().getBaseField();
        if (groupingBaseField instanceof RelationField) {
            fieldValue = groupingBaseField.getTableContainingField() + ": "
                    + ((RelationField) groupingBaseField).getDisplayField();
        } else {
            fieldValue = groupingBaseField.getFieldName();
        }
        cell = row.createCell(columnNum);
        cell.setCellValue(fieldValue);
        cell.setCellStyle(boldCellStyle);
        columnNum++;
    }
    for (ChartAggregateInfo aggregateFunction : aggregateFunctions) {
        fieldValue = aggregateFunction.toString();
        cell = row.createCell(columnNum);
        cell.setCellValue(fieldValue);
        cell.setCellStyle(boldCellStyle);
        columnNum++;
    }
    List<ChartDataRowInfo> reportSummaryDataRows = reportSummaryData.getChartDataRows();
    rowNum++;
    for (ChartDataRowInfo summaryDataRow : reportSummaryDataRows) {
        row = summarySheet.createRow(rowNum);
        columnNum = 0;
        for (ChartGroupingInfo grouping : groupings) {
            fieldValue = summaryDataRow.getGroupingValue(grouping);
            row.createCell(columnNum).setCellValue(fieldValue);
            columnNum++;
        }
        for (ChartAggregateInfo aggregateFunction : aggregateFunctions) {
            Double number = summaryDataRow.getAggregateValue(aggregateFunction).doubleValue();
            row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC).setCellValue(number);
            columnNum++;
        }
        rowNum++;
    }
}

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 w w  w. j  a va 2 s .  c om

    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.hauldata.dbpa.file.book.XlsxTargetSheet.java

License:Apache License

private ArrayList<Styles> writeRow(ArrayList<Object> rowValues, int rowIndex, RowPosition rowPosition,
        ArrayList<Styles> previousRowCellStyles) {

    ArrayList<Styles> rowCellStyles = new ArrayList<Styles>();

    Styles rowStyles = null;//w  w  w. j  av  a 2s . co  m
    if (getTargetOptions().isStyled()) {

        ValueStyles valueStyles = ValueStyles.parse(TableTag.TR, rowValues.get(0));
        rowValues.set(0, valueStyles.value);
        rowStyles = valueStyles.styles;
    }

    Styles leftStyles = null;

    Row row = sheet.createRow(rowIndex - 1);

    int columnIndex = 0;
    while (columnIndex < columnCount) {

        Styles aboveStyles = (previousRowCellStyles != null) ? previousRowCellStyles.get(columnIndex) : null;

        Object object = rowValues.get(columnIndex);

        Styles cellStyles = null;
        if (getTargetOptions().isStyled()) {

            TableTag tag = (rowPosition == RowPosition.HEADER) ? TableTag.TH : TableTag.TD;
            ValueStyles valueStyles = ValueStyles.parse(tag, object);
            object = valueStyles.value;
            cellStyles = valueStyles.styles;
        }

        Cell cell = row.createCell(columnIndex++);

        setCellValue(cell, object);

        if (!sheetStyles.areDefault() || (rowStyles != null) || (cellStyles != null)) {
            leftStyles = setCellStyle(cell, cellStyles, rowStyles, sheetStyles, rowPosition,
                    getColumnPosition(columnIndex), leftStyles, aboveStyles);
        } else {
            leftStyles = null;
        }
        rowCellStyles.add(leftStyles);
    }

    return rowCellStyles;
}

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

private void initialize(String title, List<String> headerList) {
    this.wb = new SXSSFWorkbook(500);
    this.sheet = wb.createSheet("Export");
    this.styles = createStyles(wb);
    // Create title
    if (StringUtils.isNotBlank(title)) {
        Row titleRow = sheet.createRow(rownum++);
        titleRow.setHeightInPoints(30);/*from   w  w  w. j  av  a 2  s .c om*/
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(styles.get("title"));
        titleCell.setCellValue(title);
        sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(),
                titleRow.getRowNum(), headerList.size() - 1));
    }
    // Create header
    if (headerList == null) {
        throw new RuntimeException("headerList not null!");
    }
    Row headerRow = sheet.createRow(rownum++);
    headerRow.setHeightInPoints(16);
    for (int i = 0; i < headerList.size(); i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellStyle(styles.get("header"));
        String[] ss = StringUtils.split(headerList.get(i), "**", 2);
        if (ss.length == 2) {
            cell.setCellValue(ss[0]);
            Comment comment = this.sheet.createDrawingPatriarch()
                    .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
        } else {
            cell.setCellValue(headerList.get(i));
        }
        sheet.autoSizeColumn(i);
    }
    for (int i = 0; i < headerList.size(); i++) {
        int colWidth = sheet.getColumnWidth(i) * 2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
    }
    log.debug("Initialize success.");
}

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?/*from w  w w .  jav a2 s.c o m*/
 * 
 * @param row
 *            
 * @param column
 *            ?
 * @param val
 *            
 * @param align
 *            ??1?23??
 * @return ?
 */
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType, String[] constraintValue) {
    Cell cell = row.createCell(column);
    CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
    try {
        if (val == null) {
            cell.setCellValue("");
        } else if (val instanceof String) {
            cell.setCellValue((String) val);
        } else if (val instanceof Integer) {
            cell.setCellValue((Integer) val);
        } else if (val instanceof Long) {
            cell.setCellValue((Long) val);
        } else if (val instanceof Double) {
            cell.setCellValue((Double) val);
        } else if (val instanceof Float) {
            cell.setCellValue((Float) val);
        } else if (val instanceof Date) {
            DataFormat format = wb.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellValue((Date) val);
        } else {
            if (fieldType != Class.class) {
                cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
            } else {
                cell.setCellValue((String) Class
                        .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                "fieldtype." + val.getClass().getSimpleName() + "Type"))
                        .getMethod("setValue", Object.class).invoke(null, val));
            }
        }

        if (constraintValue.length > 0) {
            // ?
            // ???
            CellRangeAddressList regions = new CellRangeAddressList(row.getRowNum(), row.getRowNum(), column,
                    column);
            // ?
            XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(constraintValue);
            DataValidationHelper help = sheet.getDataValidationHelper();

            // 
            DataValidation validation = help.createValidation(constraint, regions);

            validation.createErrorBox("", "");
            validation.setShowErrorBox(true);

            // sheet
            sheet.addValidationData(validation);
        }
    } catch (Exception ex) {
        log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
        cell.setCellValue(val.toString());
    }
    cell.setCellStyle(style);
    return cell;
}

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;/*w  w  w .  j  a  v  a2 s  .  co  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.poi.excel.ExcelReadUtilsTest.java

License:Apache License

@Test
public void testGetCellValueObject() {
    for (final EExcelVersion eVersion : EExcelVersion.values()) {
        final Workbook aWB = eVersion.createWorkbook();
        final Sheet aSheet = aWB.createSheet();
        final Row aRow = aSheet.createRow(0);
        final Cell aCell = aRow.createCell(0);

        // boolean
        aCell.setCellValue(true);/*from ww w  .java2 s . c  o m*/
        assertEquals(Boolean.TRUE, ExcelReadUtils.getCellValueObject(aCell));

        // int
        aCell.setCellValue(4711);
        assertEquals(Integer.valueOf(4711), ExcelReadUtils.getCellValueObject(aCell));

        // long
        aCell.setCellValue(Long.MAX_VALUE);
        assertEquals(Long.valueOf(Long.MAX_VALUE), ExcelReadUtils.getCellValueObject(aCell));

        // double
        aCell.setCellValue(3.14159);
        assertEquals(Double.valueOf(3.14159), ExcelReadUtils.getCellValueObject(aCell));

        // String
        aCell.setCellValue("Anyhow");
        assertEquals("Anyhow", ExcelReadUtils.getCellValueObject(aCell));

        // Rich text string
        final Font aFont = aWB.createFont();
        aFont.setItalic(true);
        final RichTextString aRTS = eVersion.createRichText("Anyhow");
        aRTS.applyFont(1, 3, aFont);
        aCell.setCellValue(aRTS);
        assertEquals("Anyhow", ExcelReadUtils.getCellValueObject(aCell));
    }
}

From source file:com.hotaviano.tableexporter.xls.XLSExporter.java

License:Open Source License

private void createHeader(Row row, Element element, CellStyle style) {
    int index = 0;

    for (Element el : element.getChildren()) {
        Cell headerColumn = row.createCell(index);
        headerColumn.setCellStyle(style);
        headerColumn.setCellValue(el.getText());
        index++;/*from  w w  w.j av  a 2s  .co m*/
    }

}

From source file:com.hotaviano.tableexporter.xls.XLSExporter.java

License:Open Source License

private void createBody(Sheet sheet, Element element) {
    int index = 0;

    for (Element trs : element.getChildren()) {
        Row row = sheet.createRow(++index);
        for (int i = 0; i < trs.getChildren().size(); i++) {
            Element td = trs.getChildren().get(i);
            row.createCell(i).setCellValue(td.getText());
        }/* ww  w. j  a v a  2s  .  c  om*/
    }

}

From source file:com.hp.action.CalculateDistanceAction.java

public String exportExcel() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize/*from   w ww  . ja  v  a2  s .c  om*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    locationDistanceList = (List<LocationDistance>) session.getAttribute("locationDistanceList");

    if (locationDistanceList == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Qung ?ng");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                4 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo co Qung ?ng");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                4 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < locationDistanceList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(locationDistanceList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

    }

    outputFile = "BaoCaoTinhQuangDuong" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}