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.admin.poi.ExcelUtils.java

License:Apache License

/**
 * /*  w  ww  .j  a  v a2s.  co  m*/
 *
 * @param excelSheet sheet ?
 * @param sheet      sheet
 */
private static void writeHead(ExcelSheet excelSheet, Sheet sheet) {
    Row row = Optional.ofNullable(sheet.getRow(excelSheet.getHeadRowIndex()))
            .orElse(sheet.createRow(excelSheet.getHeadRowIndex()));

    for (Map.Entry<String[], ExcelFieldMapping.ExcelFieldMappingAttribute> entry : excelSheet.getFieldMapping()
            .export().entrySet()) {
        ExcelFieldMapping.ExcelFieldMappingAttribute attribute = entry.getValue();
        Integer colIndex = attribute.getIndex();
        Cell cell = row.getCell(colIndex);
        if (cell == null) {
            cell = row.createCell(colIndex);
        }

        cell.setCellValue(attribute.getHead());
    }

}

From source file:com.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServlet.java

License:Apache License

@SuppressWarnings("squid:S3776")
protected Workbook createSpreadsheet(ManagedProcess report) {
    Workbook wb = new XSSFWorkbook();

    String name = report.getName();
    for (char ch : new char[] { '\\', '/', '*', '[', ']', ':', '?' }) {
        name = StringUtils.remove(name, ch);
    }/*ww w .  j a v  a2 s  .c o  m*/
    Sheet sheet = wb.createSheet(name);
    sheet.createFreezePane(0, 1, 0, 1);

    Row headerRow = sheet.createRow(0);
    CellStyle headerStyle = createHeaderStyle(wb);
    CellStyle dateStyle = wb.createCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy/mm/dd h:mm:ss"));

    for (String columnName : Arrays.asList("Time", "Path", "Error", "Stack trace")) {
        Cell headerCell = headerRow.createCell(headerRow.getPhysicalNumberOfCells());
        headerCell.setCellValue(columnName);
        headerCell.setCellStyle(headerStyle);
    }

    Collection<ArchivedProcessFailure> rows = report.getReportedErrorsList();
    //make rows, don't forget the header row
    for (ArchivedProcessFailure error : rows) {
        Row row = sheet.createRow(sheet.getPhysicalNumberOfRows());
        Cell c;

        c = row.createCell(0);
        c.setCellValue(error.time);
        c.setCellStyle(dateStyle);
        c = row.createCell(1);
        c.setCellValue(error.nodePath);
        c = row.createCell(2);
        c.setCellValue(error.error);
        c = row.createCell(3);
        c.setCellValue(error.stackTrace);
    }
    autosize(sheet, 4);
    sheet.setAutoFilter(new CellRangeAddress(0, 1 + rows.size(), 0, 3));
    return wb;
}

From source file:com.alibaba.ims.platform.util.ExcelUtil.java

License:Open Source License

private static void write(List<List<String>> rows, File file) throws IOException {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("sheet1");

    for (int i = 0; i < rows.size(); i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < rows.get(i).size(); j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(rows.get(i).get(j));
        }//  w  w  w  .ja  v a  2  s .c  om
    }

    FileOutputStream fos = null;
    try {
        fos = new FileOutputStream(file);
        workbook.write(fos);
    } finally {
        if (fos != null) {
            IOUtils.closeQuietly(fos);
        }
    }
}

From source file:com.alibaba.stonelab.webxsample.sample.web.module.screen.Download.java

License:Open Source License

private Workbook getWorkbook() {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("gift");
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("id");
    row.createCell(1).setCellValue("name");
    for (int i = 1; i <= 10; i++) {
        Row r = sheet.createRow(i);/* w  w  w. j  av a2  s. c o m*/
        r.createCell(0).setCellValue(i);
        r.createCell(1).setCellValue("name" + i);
    }
    return wb;
}

From source file:com.AllenBarr.CallSheetGenerator.Generator.java

License:Open Source License

public int generateSheet(File file, Contributor contrib) {
    //create workbook file
    final String fileName = file.toString();
    final Workbook wb;
    if (fileName.endsWith(".xlsx")) {
        wb = new XSSFWorkbook();
    } else if (fileName.endsWith(".xls")) {
        wb = new HSSFWorkbook();
    } else {//ww w  . jav  a 2 s.c o  m
        return 1;
    }
    //create sheet
    final Sheet sheet = wb.createSheet("Call Sheet");
    final Header header = sheet.getHeader();
    header.setCenter("Anderson for Iowa Call Sheet");
    //add empty cells
    final Row[] row = new Row[22 + contrib.getDonationsLength()];
    final Cell[][] cell = new Cell[6][22 + contrib.getDonationsLength()];
    for (int i = 0; i < (22 + contrib.getDonationsLength()); i++) {
        row[i] = sheet.createRow((short) i);
        for (int j = 0; j < 6; j++) {
            cell[j][i] = row[i].createCell(j);
        }
    }
    //populate cells with data
    //column 1
    cell[0][0].setCellValue(contrib.getName());
    cell[0][3].setCellValue("Sex:");
    cell[0][4].setCellValue("Party:");
    cell[0][5].setCellValue("Phone #:");
    cell[0][6].setCellValue("Home #:");
    cell[0][7].setCellValue("Cell #:");
    cell[0][8].setCellValue("Work #:");
    cell[0][10].setCellValue("Email:");
    cell[0][12].setCellValue("Employer:");
    cell[0][13].setCellValue("Occupation:");
    cell[0][15].setCellValue("Past Contact:");
    cell[0][17].setCellValue("Notes:");
    cell[0][21].setCellValue("Contribution History:");
    //column 2
    cell[1][3].setCellValue(contrib.getSex());
    cell[1][4].setCellValue(contrib.getParty());
    cell[1][5].setCellValue(contrib.getPhone());
    cell[1][6].setCellValue(contrib.getHomePhone());
    cell[1][7].setCellValue(contrib.getCellPhone());
    cell[1][8].setCellValue(contrib.getWorkPhone());
    cell[1][9].setCellValue("x" + contrib.getWorkExtension());
    cell[1][10].setCellValue(contrib.getEmail());
    cell[1][12].setCellValue(contrib.getEmployer());
    cell[1][13].setCellValue(contrib.getOccupation());
    cell[1][17].setCellValue(contrib.getNotes());
    //column 4
    cell[3][3].setCellValue("Salutation:");
    cell[3][4].setCellValue("Age:");
    cell[3][5].setCellValue("Spouse:");
    cell[3][7].setCellValue("Address:");
    cell[3][10].setCellValue("TARGET:");
    //column 5
    cell[4][0].setCellValue("VANID:");
    cell[4][3].setCellValue(contrib.getSalutation());
    cell[4][4].setCellValue(contrib.getAge());
    cell[4][5].setCellValue(contrib.getSpouse());
    cell[4][7].setCellValue(contrib.getStreetAddress());
    cell[4][8].setCellValue(contrib.getCity() + ", " + contrib.getState() + " " + contrib.getZip());
    //column 6
    cell[5][0].setCellValue(contrib.getVANID());
    //contribution cells
    for (int i = 0; i < contrib.getDonationsLength(); i++) {
        cell[0][i + 22].setCellValue(contrib.getDonation(i).getDonationDate());
        cell[1][i + 22].setCellValue(contrib.getDonation(i).getRecipient());
        cell[5][i + 22].setCellValue(contrib.getDonation(i).getAmount());
    }

    //format cells
    //Name cell
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
    final CellStyle leftBoldUnderline14Style = wb.createCellStyle();
    final Font boldUnderline14Font = wb.createFont();
    boldUnderline14Font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldUnderline14Font.setUnderline(Font.U_SINGLE);
    boldUnderline14Font.setFontHeightInPoints((short) 14);
    boldUnderline14Font.setFontName("Garamond");
    leftBoldUnderline14Style.setFont(boldUnderline14Font);
    leftBoldUnderline14Style.setAlignment(CellStyle.ALIGN_LEFT);
    cell[0][0].setCellStyle(leftBoldUnderline14Style);
    //field name cells
    final CellStyle rightBold10Style = wb.createCellStyle();
    final Font bold10Font = wb.createFont();
    bold10Font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    bold10Font.setFontHeightInPoints((short) 10);
    bold10Font.setFontName("Garamond");
    rightBold10Style.setFont(bold10Font);
    rightBold10Style.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 3; i < 22; i++) {
        cell[0][i].setCellStyle(rightBold10Style);
    }
    sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 1));
    for (int i = 3; i < 11; i++) {
        cell[3][i].setCellStyle(rightBold10Style);
    }
    cell[4][0].setCellStyle(rightBold10Style);
    //field content cells
    final CellStyle left10Style = wb.createCellStyle();
    final Font garamond10Font = wb.createFont();
    garamond10Font.setFontHeightInPoints((short) 10);
    garamond10Font.setFontName("Garamond");
    left10Style.setFont(garamond10Font);
    left10Style.setAlignment(CellStyle.ALIGN_LEFT);
    for (int i = 3; i < 5; i++) {
        cell[1][i].setCellStyle(left10Style);
    }
    //phone number cells
    final CellStyle phoneStyle = wb.createCellStyle();
    phoneStyle.setFont(garamond10Font);
    phoneStyle.setAlignment(CellStyle.ALIGN_LEFT);
    final CreationHelper createHelper = wb.getCreationHelper();
    phoneStyle.setDataFormat(createHelper.createDataFormat().getFormat("[<=9999999]###-####;(###) ###-####"));
    for (int i = 5; i < 9; i++) {
        cell[1][i].setCellStyle(phoneStyle);
        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));

    }
    cell[1][9].setCellStyle(left10Style);
    //email through past contact
    for (int i = 10; i < 16; i++) {
        cell[1][i].setCellStyle(left10Style);
    }
    //notes
    CellStyle noteStyle = wb.createCellStyle();
    noteStyle.cloneStyleFrom(left10Style);
    noteStyle.setWrapText(true);
    cell[1][17].setCellStyle(noteStyle);
    //column E
    for (int i = 3; i < 11; i++) {
        cell[4][i].setCellStyle(left10Style);
    }
    //VanID Cell
    final CellStyle right10Style = wb.createCellStyle();
    right10Style.setFont(garamond10Font);
    right10Style.setAlignment(CellStyle.ALIGN_RIGHT);
    cell[5][0].setCellStyle(right10Style);
    //Notes cell
    sheet.addMergedRegion(new CellRangeAddress(17, 19, 1, 5));
    //contribution cells
    final CellStyle date10Style = wb.createCellStyle();
    date10Style.setFont(garamond10Font);
    date10Style.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));
    date10Style.setBorderBottom(CellStyle.BORDER_THIN);
    date10Style.setBorderTop(CellStyle.BORDER_THIN);
    date10Style.setBorderLeft(CellStyle.BORDER_THIN);
    date10Style.setBorderRight(CellStyle.BORDER_THIN);
    final CellStyle contributionStyle = wb.createCellStyle();
    contributionStyle.cloneStyleFrom(left10Style);
    contributionStyle.setBorderBottom(CellStyle.BORDER_THIN);
    contributionStyle.setBorderTop(CellStyle.BORDER_THIN);
    contributionStyle.setBorderLeft(CellStyle.BORDER_THIN);
    contributionStyle.setBorderRight(CellStyle.BORDER_THIN);
    final CellStyle money10Style = wb.createCellStyle();
    money10Style.setFont(garamond10Font);
    money10Style.setDataFormat(
            createHelper.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
    money10Style.setBorderBottom(CellStyle.BORDER_THIN);
    money10Style.setBorderTop(CellStyle.BORDER_THIN);
    money10Style.setBorderLeft(CellStyle.BORDER_THIN);
    money10Style.setBorderRight(CellStyle.BORDER_THIN);
    for (int i = 22; i < 22 + contrib.getDonationsLength(); i++) {
        cell[0][i].setCellStyle(date10Style);
        cell[1][i].setCellStyle(contributionStyle);
        cell[2][i].setCellStyle(contributionStyle);
        cell[3][i].setCellStyle(contributionStyle);
        cell[4][i].setCellStyle(contributionStyle);
        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 4));
        cell[5][i].setCellStyle(money10Style);

    }
    //resize columns
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    try {
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        return 1;
    } catch (IOException ex) {
        return 1;
    }

    return 0;
}

From source file:com.alvexcore.repo.documents.generation.ExportDataListToXlsx.java

License:Open Source License

protected Workbook createXlsx(JSONArray rows, String XLS_SHEET_NAME) {

    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet(XLS_SHEET_NAME);

    for (int k = 0; k < rows.size(); k++) {
        Row row = sheet.createRow((short) k);
        JSONArray cells = (JSONArray) rows.get(k);
        for (int c = 0; c < cells.size(); c++) {
            String displayValue;//from  ww w  .j  a v a 2s. c  o  m
            Object item = cells.get(c);
            if (item == null)
                displayValue = I18NUtil.getMessage("label.empty");
            else if (item instanceof Boolean)
                displayValue = (Boolean) item ? I18NUtil.getMessage("label.yes")
                        : I18NUtil.getMessage("label.no");
            else
                displayValue = item.toString();
            row.createCell(c).setCellValue(createHelper.createRichTextString(displayValue));
        }
    }

    return wb;
}

From source file:com.alvexcore.repo.documents.generation.ExportReportToXlsx.java

License:Open Source License

protected Workbook createXlsx(JSONArray rows, String XLS_SHEET_NAME) {

    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet(XLS_SHEET_NAME);

    for (int k = 0; k < rows.size(); k++) {
        Row row = sheet.createRow((short) k);
        JSONArray cells = (JSONArray) rows.get(k);
        row.createCell(0).setCellValue(createHelper.createRichTextString((String) cells.get(0)));
        for (int c = 1; c < cells.size(); c++)
            row.createCell(c).setCellValue((String) cells.get(c));
    }/*from  w  w  w . j av a 2s .  c  o  m*/

    return wb;
}

From source file:com.asakusafw.testdata.generator.excel.SheetEditor.java

License:Apache License

/**
 * Creates a data sheet with specified name.
 * @param name sheet name//w  ww.j  a  va 2s. c  om
 * @param model the target model
 * @throws IllegalArgumentException if some parameters were {@code null}
 */
public void addData(String name, ModelDeclaration model) {
    if (name == null) {
        throw new IllegalArgumentException("name must not be null"); //$NON-NLS-1$
    }
    if (model == null) {
        throw new IllegalArgumentException("model must not be null"); //$NON-NLS-1$
    }
    Sheet sheet = info.workbook.createSheet(name);
    Row titleRow = sheet.createRow(0);
    Row valueRow = sheet.createRow(1);
    int index = 0;
    for (PropertyDeclaration property : model.getDeclaredProperties()) {
        if (index >= info.version.getMaxColumns()) {
            LOG.warn(MessageFormat.format(Messages.getString("SheetEditor.warnExceedColumnCount"), //$NON-NLS-1$
                    info.version.getMaxColumns(), model.getName()));
            break;
        }
        Cell title = titleRow.createCell(index);
        title.setCellStyle(info.titleStyle);
        title.setCellValue(property.getName().identifier);

        Cell value = valueRow.createCell(index);
        value.setCellStyle(info.dataStyle);
        if (property.getType() instanceof BasicType) {
            BasicType type = (BasicType) property.getType();
            switch (type.getKind()) {
            case DATE:
                value.setCellStyle(info.dateDataStyle);
                break;
            case DATETIME:
                value.setCellStyle(info.datetimeDataStyle);
                break;
            default:
                break;
            }
        }
        index++;
    }
    adjustDataWidth(sheet);
}

From source file:com.asakusafw.testdata.generator.excel.SheetEditor.java

License:Apache License

private Cell getCell(Sheet sheet, int rowIndex, int columnIndex) {
    assert sheet != null;
    Row row = sheet.getRow(rowIndex);//from ww  w. j av  a 2s .  c om
    if (row == null) {
        row = sheet.createRow(rowIndex);
    }
    Cell cell = row.getCell(columnIndex, Row.CREATE_NULL_AS_BLANK);
    return cell;
}

From source file:com.asakusafw.testdriver.excel.ExcelSheetSink.java

License:Apache License

/**
 * Creates a new instance.//w w  w . j av  a  2s .c om
 * @param definition the data model definition
 * @param sheet target sheet
 * @param maxColumns the count of max columns
 * @throws IllegalArgumentException if some parameters were {@code null}
 */
public ExcelSheetSink(DataModelDefinition<?> definition, Sheet sheet, int maxColumns) {
    if (definition == null) {
        throw new IllegalArgumentException("definition must not be null"); //$NON-NLS-1$
    }
    if (sheet == null) {
        throw new IllegalArgumentException("sheet must not be null"); //$NON-NLS-1$
    }
    this.sheet = sheet;
    this.info = new WorkbookInfo(sheet.getWorkbook());
    this.engine = new Engine(definition, info, maxColumns);
    engine.createHeaderRow(sheet.createRow(0));
    this.rowIndex = 1;
}