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

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

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

Introduction

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

Prototype

Row createRow(int rownum);

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

private Cell createInputHeader(Sheet sheet, String label) {
    Row row = sheet.createRow(sheet.getPhysicalNumberOfRows());
    Cell cell1 = row.createCell(0);//from w  w  w .  jav  a 2 s . c  om
    cell1.setCellValue(label);
    cell1.setCellStyle(boldStyle);

    return cell1;
}

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

private Cell createInputCell(Sheet sheet, String label) {
    Row row = sheet.createRow(sheet.getPhysicalNumberOfRows());
    Cell cell1 = row.createCell(0);/* w w  w  .  j  a v a2 s. c o  m*/
    cell1.setCellValue(label + ":");
    Cell cell2 = row.createCell(1);
    CellUtil.setAlignment(cell2, sheet.getWorkbook(), CellStyle.ALIGN_RIGHT);

    inputCells.put(label, cell2);

    return cell2;
}

From source file:com.dbumama.market.web.core.render.excel.PoiExporter.java

License:Apache License

public Workbook export() {
    Preconditions.checkNotNull(data, "data can not be null");
    Preconditions.checkNotNull(headers, "headers can not be null");
    Preconditions.checkNotNull(columns, "columns can not be null");
    Preconditions.checkArgument(/*from w  w w .j  a v a2s . c o  m*/
            data.length == sheetNames.length && sheetNames.length == headers.length
                    && headers.length == columns.length,
            "data,sheetNames,headers and columns'length should be the same." + "(data:" + data.length
                    + ",sheetNames:" + sheetNames.length + ",headers:" + headers.length + ",columns:"
                    + columns.length + ")");
    Preconditions.checkArgument(cellWidth >= 0, "cellWidth can not be less than 0");
    Workbook wb;
    if (VERSION_2003.equals(version)) {
        wb = new HSSFWorkbook();
        if (data.length > 1) {
            for (int i = 0; i < data.length; i++) {
                List<?> item = data[i];
                Preconditions.checkArgument(item.size() < MAX_ROWS,
                        "Data [" + i + "] is invalid:invalid data size (" + item.size()
                                + ") outside allowable range (0..65535)");
            }
        } else if (data.length == 1 && data[0].size() > MAX_ROWS) {
            data = dice(data[0], MAX_ROWS).toArray(new List<?>[] {});
            String sheetName = sheetNames[0];
            sheetNames = new String[data.length];
            for (int i = 0; i < data.length; i++) {
                sheetNames[i] = sheetName + (i == 0 ? "" : (i + 1));
            }
            String[] header = headers[0];
            headers = new String[data.length][];
            for (int i = 0; i < data.length; i++) {
                headers[i] = header;
            }
            String[] column = columns[0];
            columns = new String[data.length][];
            for (int i = 0; i < data.length; i++) {
                columns[i] = column;
            }
        }
    } else {
        wb = new XSSFWorkbook();
    }
    if (data.length == 0) {
        return wb;
    }
    for (int i = 0; i < data.length; i++) {
        Sheet sheet = wb.createSheet(sheetNames[i]);
        Row row;
        Cell cell;
        if (headers[i].length > 0) {
            row = sheet.createRow(0);
            if (headerRow <= 0) {
                headerRow = HEADER_ROW;
            }
            headerRow = Math.min(headerRow, MAX_ROWS);
            for (int h = 0, lenH = headers[i].length; h < lenH; h++) {
                if (cellWidth > 0) {
                    sheet.setColumnWidth(h, cellWidth);
                }
                cell = row.createCell(h);
                cell.setCellValue(headers[i][h]);
            }
        }

        for (int j = 0, len = data[i].size(); j < len; j++) {
            row = sheet.createRow(j + headerRow);
            Object obj = data[i].get(j);
            if (obj == null) {
                continue;
            }
            if (obj instanceof Map) {
                processAsMap(columns[i], row, obj);
            } else if (obj instanceof Model) {
                processAsModel(columns[i], row, obj);
            } else if (obj instanceof Record) {
                processAsRecord(columns[i], row, obj);
            } else {
                throw new RuntimeException("Not support type[" + obj.getClass() + "]");
            }
        }
    }
    return wb;
}

From source file:com.devnexus.ting.web.controller.admin.RegistrationController.java

License:Apache License

private void addTicketTypesToSheet(String[] ticketTypes, Sheet ticketTypeSheet) {
    for (int i = 0, length = ticketTypes.length; i < length; i++) {
        String ticketType = ticketTypes[i];
        Row row = ticketTypeSheet.createRow(i);
        Cell cell = row.createCell(0);/*from ww w  .  j  a  v  a 2  s.  c  om*/
        cell.setCellValue(ticketType);
    }
}

From source file:com.dituiba.excel.BaseExcelService.java

License:Apache License

/**
 * sheet ?/*from ww w . j ava  2  s.co  m*/
 * @param sheet
 * @param row
 * @param length
 * @param data
 */
public static void addTitle(Sheet sheet, int row, int length, String data) {
    if (data == null || data.equals("") || data.equals("null")) {
        return;
    }
    Row sheetRow = sheet.createRow(row);
    for (int i = 0; i < length; i++) {
        sheetRow.createCell(i);
    }
    CellStyle style = sheet.getWorkbook().createCellStyle(); // ?
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 
    CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row, 0, length - 1);
    sheet.addMergedRegion(cellRangeAddress);
    Cell cell = sheetRow.getCell(0);
    cell.setCellStyle(style);
    cell.setCellValue(data);
}

From source file:com.dituiba.excel.BaseExcelService.java

License:Apache License

/**
 * ?/*from ww  w .j a v  a  2  s.  com*/
 * @param sheet
 * @param row
 * @param data
 * @return
 */
public static Row addRow(Sheet sheet, int row, String[] data) {
    Row sheetRow = sheet.createRow(row);
    CellStyle style = sheet.getWorkbook().createCellStyle(); // ?
    style.setWrapText(true);
    for (int i = 0; i < data.length; i++) {
        Cell cell = sheetRow.createCell(i);
        cell.setCellValue(data[i]);
        cell.setCellStyle(style);
    }
    return sheetRow;
}

From source file:com.dituiba.excel.BaseExcelService.java

License:Apache License

/**
 * ?/* www.j av a  2s.  c om*/
 * @param sheet
 * @param row
 * @param length
 * @return
 */
public static Row createRow(Sheet sheet, int row, int length) {
    Row sheetRow = sheet.createRow(row);
    for (int i = 0; i < length; i++) {
        sheetRow.createCell(i);
    }
    return sheetRow;
}

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ??// ww  w  . j a  v a  2  s . c om
 * @param config
 * @param sheet
 * @param columnIndex
 * @param valueSet
 */
protected void createDicCodeSheet(DicValidateConfig config, Sheet sheet, int columnIndex,
        Set<String> valueSet) {
    Workbook workbook = sheet.getWorkbook();
    Sheet codeSheet = workbook.getSheet(DICCODE_SHEET_NAME);
    if (codeSheet == null) {
        log.debug("?Sheet?Sheet");
        codeSheet = workbook.createSheet(DICCODE_SHEET_NAME);
    }
    int codeIndex = config.columnName() - 'A';
    log.debug("codeIndex{}", codeIndex);
    if (codeSheet.getRow(0) == null || codeSheet.getRow(0).getCell(codeIndex) == null) {
        log.debug("????");
        int i = 0;
        for (String dic : valueSet) {
            Row row = codeSheet.getRow(i);
            if (row == null)
                row = codeSheet.createRow(i);
            Cell cell = row.createCell(codeIndex);
            cell.setCellValue(dic);
            i++;
        }
    } else {
        log.debug("????");
    }
    Name name = workbook.getName(config.columnName() + "");
    if (name == null || name.isDeleted()) {
        log.debug("?Name?Name");
        name = workbook.createName();
        name.setNameName(config.columnName() + "");
    }
    name.setRefersToFormula(DICCODE_SHEET_NAME + "!$" + config.columnName() + "$1:$" + config.columnName() + "$"
            + valueSet.size());
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(name.getNameName());
    CellRangeAddressList addressList = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
            columnIndex, columnIndex);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    workbook.setSheetHidden(workbook.getSheetIndex(DICCODE_SHEET_NAME), Workbook.SHEET_STATE_VERY_HIDDEN);
    setValidationTip(validation, config);
    sheet.addValidationData(validation);
    log.debug("??");
}

From source file:com.dituiba.excel.ExcelUtility.java

License:Apache License

public static void copyRows(Sheet st, int startRow, int endRow, int pPosition) {
    int pStartRow = startRow;
    int pEndRow = endRow;
    int targetRowFrom;
    int targetRowTo;
    int columnCount;
    CellRangeAddress region = null;/*  w  w  w.  j a  va 2s  .  c  o m*/
    int i;
    int j;
    for (i = 0; i < st.getNumMergedRegions(); i++) {
        region = st.getMergedRegion(i);
        if ((region.getFirstRow() >= pStartRow) && (region.getLastRow() <= pEndRow)) {
            targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
            targetRowTo = region.getLastRow() - pStartRow + pPosition;

            CellRangeAddress newRegion = region.copy();

            newRegion.setFirstRow(targetRowFrom);
            newRegion.setFirstColumn(region.getFirstColumn());
            newRegion.setLastRow(targetRowTo);
            newRegion.setLastColumn(region.getLastColumn());
            st.addMergedRegion(newRegion);
        }
    }
    //set the column height and value
    for (i = pStartRow; i <= pEndRow; i++) {
        Row sourceRow = st.getRow(i);
        columnCount = sourceRow.getLastCellNum();
        if (sourceRow != null) {
            Row newRow = st.createRow(pPosition + i);
            newRow.setHeight(sourceRow.getHeight());
            for (j = 0; j < columnCount; j++) {
                Cell templateCell = sourceRow.getCell(j);
                if (templateCell != null) {
                    Cell newCell = newRow.createCell(j);
                    copyCell(templateCell, newCell);
                }
            }
        }
    }
}

From source file:com.dufeng.core.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from  w  w  w.ja v a2  s  .c  o  m

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

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

    Sheet sheet = wb.createSheet("Business Plan");

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

    //the following three statements are required only for HSSF
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    //the header row: centered text in 48pt font
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    //columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    //calendar.setTime(fmt.parse("9-Jul"));
    calendar.setTime(new Date());
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    //freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                //calendar.setTime(fmt.parse(data[i][j]));
                calendar.setTime(new Date());
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    //group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    //set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

    // Write the output to a file
    String file = "E:/businessplan.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}