Java Code Examples for org.apache.poi.ss.usermodel.Sheet#createRow()

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);	
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

private static void createRows(List<String> keyList,
        Map<String, ResourceBundle> defaultProperties,
        Map<String, Properties> localizedProperties, Sheet sheet,
        List<String> localeList, String sheetName) {
    sheet.createFreezePane(1, 1);
    int rowIdx = 1;
    int colIdx = 0;
    for (String key : keyList) {
        Row row = sheet.createRow(rowIdx++);
        colIdx = 0;
        row.createCell(colIdx++).setCellValue(key);

        for (String locale : localeList) {
            String cellValue = null;
            cellValue = getCellValue(defaultProperties,
                    localizedProperties, key, locale, sheetName);
            row.createCell(colIdx++).setCellValue(cellValue);
        }
    }
}
 

/**
 * @param wb
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	sheet.setColumnWidth(EXTRACTED_TEXT_COL, EXTRACTED_TEXT_WIDTH*256);
	sheet.setDefaultColumnStyle(EXTRACTED_TEXT_COL, defaultStyle);
	Cell extractedHeaderCell = row.createCell(EXTRACTED_TEXT_COL);
	extractedHeaderCell.setCellStyle(headerStyle);
	extractedHeaderCell.setCellValue(EXTRACTED_TEXT_TITLE);
	for (int i = FIRST_LIC_ID_COL; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
		sheet.setColumnWidth(i, LIC_ID_COL_WIDTH*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

/**
 * @param wb
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);

	sheet.setColumnWidth(TYPE_COL, TYPE_COL_WIDTH*256);
	sheet.setDefaultColumnStyle(TYPE_COL, defaultStyle);
	Cell typeHeaderCell = row.createCell(TYPE_COL);
	typeHeaderCell.setCellStyle(headerStyle);
	typeHeaderCell.setCellValue(TYPE_COL_TEXT_TITLE);

	for (int i = FIRST_RELATIONSHIP_COL; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
		sheet.setColumnWidth(i, FIRST_RELATIONSHIP_COL_WIDTH*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

/**
 * シートから任意アドレスのセルを取得する。
 *
 * <p>{@literal jxl.Sheet.getCell(int column, int row)}</p>
 * @param sheet シートオブジェクト
 * @param column 列番号（0から始まる）
 * @param row 行番号（0から始まる）
 * @return セル
 * @throws IllegalArgumentException {@literal sheet == null}
 */
public static Cell getCell(final Sheet sheet, final int column, final int row) {
    ArgUtils.notNull(sheet, "sheet");

    Row rows = sheet.getRow(row);
    if(rows == null) {
        rows = sheet.createRow(row);
    }

    Cell cell = rows.getCell(column);
    if(cell == null) {
        cell = rows.createCell(column, CellType.BLANK);
    }

    return cell;
}
 

/**
 * 按报表模板格式写入合计（暂时不支持分页功能）
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-18
 * @version     v1.0
 *
 * @param i_DataWorkbook   数据工作薄
 * @param i_DataSheet      数据工作表
 * @param io_RTotal        将数据写入Excel时的辅助统计信息
 * @param io_RSystemValue  系统变量信息
 * @param i_Datas          数据
 * @param i_RTemplate      报表模板对象
 */
public final static void writeTotal(RWorkbook i_DataWorkbook ,Sheet i_DataSheet ,RTotal io_RTotal ,RSystemValue io_RSystemValue, Object i_Datas ,RTemplate i_RTemplate) 
{
    Sheet v_TemplateSheet         = i_RTemplate.getTemplateSheet();
    int   v_TemplateRowCountTotal = i_RTemplate.getRowCountTotal();
    int   v_ExcelRowIndex         = io_RTotal.getExcelRowIndex();
    
    copyMergedRegionsTotal(i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板合并单元格
    copyImagesTotal(       i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板复制图片
    
    for (int v_RowNo=0; v_RowNo<v_TemplateRowCountTotal; v_RowNo++) 
    {
        int v_TemplateRowNo = i_RTemplate.getTotalBeginRow() + v_RowNo;
        Row v_TemplateRow   = v_TemplateSheet.getRow(v_TemplateRowNo);
        
        int v_DataRowNo = v_RowNo + v_ExcelRowIndex;
        Row v_DataRow   = i_DataSheet.createRow(v_DataRowNo);
        io_RTotal.addExcelRowIndex(1);
        io_RTotal.addRealDataCount(1);
        
        if ( v_TemplateRow != null ) // 模板空白行（无任何数据）时，可能返回NULL
        {
            copyRow(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
        }
    }
}
 

public static void setBlankRows(int rows,int columns,HSSFWorkbook workbook){
	//得到第一页
	Sheet sheet = workbook.getSheetAt(0);
	//样式
	CellStyle cellStyle = getOneStyle(workbook);
	for (int i = 1; i <=rows; i++) {
		Row row = sheet.createRow(i);
		for (int j = 0; j < columns; j++) {
			 row.createCell(j).setCellStyle(cellStyle);
		}
	}
}
 

private static List<String> createFirstRow(String sheetName,
        List<Locale> locales, Sheet sheet, CellStyle styleTitle) {
    int colIdx = 0;
    Row titleRow = sheet.createRow(0);
    sheet.setColumnWidth(colIdx, 30 * 256);
    Cell titleCell = titleRow.createCell(colIdx++);
    titleCell.setCellStyle(styleTitle);
    titleCell.setCellValue(getDefaultResourceBundle().getString(
            BaseBean.LABEL_SHOP_TRANSLARIONS_KEY));
    return createColumnHeaders(sheetName, locales, sheet, styleTitle,
            colIdx, titleRow);
}
 

/**
 * Render the Table as a Workbook.
 *
 * @param split character to split multiple cell values on
 * @return Workbook
 */
public Workbook asWorkbook(String split) {
  Workbook wb = new XSSFWorkbook();
  wb.createSheet();

  // Add headers
  Sheet sheet = wb.getSheetAt(0);
  org.apache.poi.ss.usermodel.Row headerRow = sheet.createRow(0);
  int colIdx = 0;
  for (Column c : columns) {
    String name = c.getDisplayName();
    Cell xlsxCell = headerRow.createCell(colIdx);
    xlsxCell.setCellValue(name);
    colIdx++;
  }

  // Add rows
  for (Row row : rows) {
    row.addToWorkbook(wb, columns, split);
  }

  // Set auto sizing
  // TODO - this takes up, relatively, a lot of time.
  /* for (int idx = 0; idx < columns.size(); idx++) {
    sheet.autoSizeColumn(idx);
  } */

  return wb;
}
 

/**
 * 写入excel并填充内容,一个sheet只能写65536行以下，超出会报异常，写入时建议使用AbstractExcel2007Writer
 * @param fileName
 * @throws IOException
 */
@SuppressWarnings("resource")
public static void writeExcel(String fileName) throws IOException{
		
		// 创建excel2003对象
		Workbook wb = new HSSFWorkbook();
		
		// 设置文件放置路径和文件名
	    FileOutputStream fileOut = new FileOutputStream(fileName);
	    // 创建新的表单
	    Sheet sheet = wb.createSheet( "newsheet" );
	    // 创建新行
	    for(int i=0;i<20000;i++){
		    Row row = sheet.createRow(i);
		    // 创建单元格
		    Cell cell = row.createCell(0);
		    // 设置单元格值
		    cell.setCellValue(1);
		    row.createCell(1).setCellValue(1+i);
		    row.createCell(2).setCellValue(true);
		    row.createCell(3).setCellValue(0.43d);
		    row.createCell(4).setCellValue('d');
		    row.createCell(5).setCellValue( "" );
		    row.createCell(6).setCellValue( "第七列"+i);
		    row.createCell(7).setCellValue( "第八列"+i);
	    }
	    wb.write(fileOut);
	    fileOut.close();
}
 

protected Row createRow(Sheet sheet, int rowIndex, RowModel rowModel){
	Row row = sheet.createRow(rowIndex);
	if(rowModel.getHeight()>0)
		row.setHeight(rowModel.getHeight());
	
	this.generator.getWorkbookData().getWorkbookListener().afterCreateRow(row, rowIndex);
	return row;
}
 

/**
 * Create new row.
 * @param sheet Sheet
 * @return Row Row
 */
private Row createRow(final Sheet sheet) {
    Row row;
    if (this.index == -1) {
        final int num = sheet.getLastRowNum();
        row = sheet.createRow(num);
        sheet.createRow(num + 1);
    } else {
        row = sheet.getRow(this.index - 1);
        if (row == null) {
            row = sheet.createRow(this.index - 1);
        }
    }
    return row;
}
 

@Override
public boolean write(JSONObject object, OutputStream outputStream) {
    try (Workbook workbook = new XSSFWorkbook()) {
        JSONArray sheets = object.getJSONArray("sheets");
        for (int sheetIndex = 0, sheetSize = sheets.size(); sheetIndex < sheetSize; sheetIndex++) {
            JSONObject sheetJson = sheets.getJSONObject(sheetIndex);
            Sheet sheet = workbook.createSheet(sheetJson.containsKey("name") ? sheetJson.getString("name") : ("sheet " + sheetIndex));
            int firstRow = sheetJson.containsKey("first") ? sheetJson.getIntValue("first") : 0;
            JSONArray rows = sheetJson.getJSONArray("rows");
            for (int rowIndex = 0, rowSize = rows.size(); rowIndex < rowSize; rowIndex++) {
                JSONObject rowJson = rows.getJSONObject(rowIndex);
                int firstCol = rowJson.containsKey("first") ? rowJson.getIntValue("first") : 0;
                Row row = sheet.createRow(firstRow + rowIndex);
                JSONArray cells = rowJson.getJSONArray("cells");
                for (int cellIndex = 0, cellSize = cells.size(); cellIndex < cellSize; cellIndex++) {
                    JSONObject cellJson = cells.getJSONObject(cellIndex);
                    Cell cell = row.createCell(firstCol + cellIndex);
                    cell.setCellValue(cellJson.getString("value"));
                }
            }
        }
        workbook.write(outputStream);
        outputStream.close();

        return true;
    } catch (Throwable throwable) {
        logger.warn(throwable, "输出Excel时发生异常！");

        return false;
    }
}
 

public int commonFillSheet(Sheet sheet, CrossTab cs, CreationHelper createHelper, int startRow, Locale locale)
		throws SerializationException, JSONException {
	int columnsDepth = cs.getColumnsRoot().getSubTreeDepth();
	int rowsDepth = cs.getRowsRoot().getSubTreeDepth();

	MeasureFormatter measureFormatter = new MeasureFormatter(cs);
	int rowsNumber = cs.getDataMatrix().length;
	// + 1 because there may be also the bottom row with the totals
	int totalRowsNumber = columnsDepth + rowsNumber + 1;
	for (int i = 0; i < totalRowsNumber + 5; i++) {
		sheet.createRow(startRow + i);
	}

	CellStyle memberCellStyle = this.buildHeaderCellStyle(sheet);
	CellStyle dimensionCellStyle = this.buildDimensionCellStyle(sheet);

	// build headers for column first ...
	buildColumnsHeader(sheet, cs, cs.getColumnsRoot().getChilds(), startRow, rowsDepth - 1, createHelper, locale, memberCellStyle, dimensionCellStyle);
	// ... then build headers for rows ....
	buildRowsHeaders(sheet, cs, cs.getRowsRoot().getChilds(), columnsDepth - 1 + startRow, 0, createHelper, locale, memberCellStyle);
	// then put the matrix data
	buildDataMatrix(sheet, cs, columnsDepth + startRow - 1, rowsDepth - 1, createHelper, measureFormatter);

	buildRowHeaderTitle(sheet, cs, columnsDepth - 2, 0, startRow, createHelper, locale, dimensionCellStyle);

	return startRow + totalRowsNumber;
}
 

/**
 * Get a row from the spreadsheet, and create it if it doesn't exist.
 *
 * @param rowIndex The 0 based row number
 * @param sheet The sheet that the row is part of.
 * @return The row indicated by the rowCounter
 */
public static Row getRow(int rowIndex, Sheet sheet) {
    Row row = sheet.getRow(rowIndex);
    if (row == null) {
        row = sheet.createRow(rowIndex);
    }
    return row;
}
 

public static void setCellValue(Sheet sheet, int row, int col, String value, CellStyle cellStyle) {
    Row sheetRow = sheet.getRow(row);
    if (sheetRow == null) {
        sheetRow = sheet.createRow(row);
    }
    Cell cell = sheetRow.getCell(col);
    if (cell == null) {
        cell = sheetRow.createCell(col);
    }
    cell.setCellValue(value);
    cell.setCellStyle(cellStyle);
}
 

/**
 * 创建 最主要的 Cells
 * 
 * @param styles
 * @param rowHeight
 * @throws Exception
 */
public int createCells(Drawing patriarch, int index, Object t,
                       List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook,
                       short rowHeight) throws Exception {
    ExcelExportEntity entity;
    Row row = sheet.createRow(index);
    row.setHeight(rowHeight);
    int maxHeight = 1, cellNum = 0;
    int indexKey = createIndexCell(row, index, excelParams.get(0));
    cellNum += indexKey;
    for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        if (entity.getList() != null) {
            Collection<?> list = getListCellValue(entity, t);
            int listC = 0;
            for (Object obj : list) {
                createListCells(patriarch, index + listC, cellNum, obj, entity.getList(),
                    sheet, workbook);
                listC++;
            }
            cellNum += entity.getList().size();
            if (list != null && list.size() > maxHeight) {
                maxHeight = list.size();
            }
        } else {
            Object value = getCellValue(entity, t);
            if (entity.getType() == 1) {
                createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                    index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
            } else {
                createImageCell(patriarch, entity, row, cellNum++,
                    value == null ? "" : value.toString(), t);
            }
        }
    }
    // 合并需要合并的单元格
    cellNum = 0;
    for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        if (entity.getList() != null) {
            cellNum += entity.getList().size();
        } else if (entity.isNeedMerge()) {
            for (int i = index + 1; i < index + maxHeight; i++) {
                sheet.getRow(i).createCell(cellNum);
                sheet.getRow(i).getCell(cellNum).setCellStyle(getStyles(false, entity));
            }
            sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight - 1, cellNum,
                cellNum));
            cellNum++;
        }
    }
    return maxHeight;

}
 

private static int modifyCellData(final Sheet worksheet,
                                  final List<Integer> rowIndexList,
                                  final List<Integer> columnIndexList,
                                  final List<String> dataList) {
    int rowCount = 0;

    for (Integer rowIndex : rowIndexList) {
        boolean isModified = false;
        int i = 0;
        Row row = worksheet.getRow(rowIndex);
        //if the specified row does not exist
        if (row == null) {
            row = worksheet.createRow(rowIndex);

        }
        for (Integer columnIndex : columnIndexList) {
            Cell cell = row.getCell(columnIndex);
            //if the specified cell does not exist
            if (cell == null) {
                cell = row.createCell(columnIndex);
            }
            //the cell is a merged cell, cannot modify it
            if (isMergedCell(worksheet, rowIndex, columnIndex)) {
                i++;
                incompleted = true;
            } else {
                //if the cell needs to be modified is in formula type,
                if (cell.getCellType() == CellType.FORMULA) {
                    cell.setCellType(CellType.STRING);
                }
                try {
                    double valueNumeric = Double.parseDouble(dataList.get(i).trim());
                    cell.setCellValue(valueNumeric);
                }
                //for non-numeric value
                catch (Exception e) {
                    try {
                        Date date = new Date(dataList.get(i).trim());
                        cell.setCellValue(date);
                    } catch (Exception e1) {
                        cell.setCellValue(dataList.get(i).trim());
                    }
                }
                i++;
                isModified = true;
            }
        }
        if (isModified) rowCount++;
    }

    return rowCount;
}