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

The following examples show how to use org.apache.poi.ss.usermodel.Sheet#shiftRows() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: AddCellService.java    From cs-actions with Apache License 2.0 8 votes vote down vote up 
/**
 * Inserts rows at the specified indexes in the worksheet
 *
 * @param worksheet    Worksheet where rows will be inserted
 * @param rowIndexList List of row indexes where rows will be inserted
 */
public static void shiftRows(final Sheet worksheet, final List<Integer> rowIndexList) {
    int insertPoint;
    int nRows;
    int i = 0;
    while (i < rowIndexList.size()) {
        insertPoint = rowIndexList.get(i);
        nRows = 1;
        while (i < rowIndexList.size() - 1 && (insertPoint + nRows == rowIndexList.get(i + 1))) {
            nRows++;
            i++;
        }
        if (insertPoint > worksheet.getLastRowNum()) {
            for (int j = insertPoint; j < insertPoint + nRows; j++) {
                worksheet.createRow(j);
            }
        } else {
            worksheet.shiftRows(insertPoint, worksheet.getLastRowNum(), nRows, false, true);
        }
        i++;
    }
}
Example 2
Source File: POIUtils.java    From xlsmapper with Apache License 2.0 6 votes vote down vote up 
/**
 * 指定した行の下に行を1行追加する
 * @param sheet
 * @param rowIndex 追加する行数
 * @return 追加した行を返す。
 */
public static Row insertRow(final Sheet sheet, final int rowIndex) {

    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notMin(rowIndex, 0, "rowIndex");

    // 最終行を取得する
    int lastRow = sheet.getLastRowNum();
    if(lastRow < rowIndex) {
        // データが定義されている範囲害の場合は、行を新たに作成して返す。
        return sheet.createRow(rowIndex);
    }

    sheet.shiftRows(rowIndex, lastRow+1, 1);
    return sheet.createRow(rowIndex);
}
Example 3
Source File: POIUtils.java    From xlsmapper with Apache License 2.0 6 votes vote down vote up 
/**
 * 指定した行を削除する。
 * <p>削除した行は上に詰める。
 * @since 0.5
 * @param sheet
 * @param rowIndex 削除する行数
 * @return 削除した行
 */
public static Row removeRow(final Sheet sheet, final int rowIndex) {

    ArgUtils.notNull(sheet, "cell");
    ArgUtils.notMin(rowIndex, 0, "rowIndex");

    final Row row = sheet.getRow(rowIndex);
    if(row == null) {
        // 削除対象の行にデータが何もない場合
        return row;
    }

    sheet.removeRow(row);

    // 上に1つ行をずらす
    int lastRow = sheet.getLastRowNum();
    if(rowIndex +1 > lastRow) {
        return row;
    }

    sheet.shiftRows(rowIndex+1, lastRow, -1);

    return row;
}
Example 4
Source File: ExcelExportOfTemplateUtil.java    From autopoi with Apache License 2.0 5 votes vote down vote up 
/**
 * 往Sheet 填充正常数据,根据表头信息 使用导入的部分逻辑,坐对象映射
 * 
 * @param teplateParams
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception {

	if (workbook instanceof XSSFWorkbook) {
		super.type = ExcelType.XSSF;
	}
	// 获取表头数据
	Map<String, Integer> titlemap = getTitleMap(sheet);
	Drawing patriarch = sheet.createDrawingPatriarch();
	// 得到所有字段
	Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
	ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
	String targetId = null;
	if (etarget != null) {
		targetId = etarget.value();
	}
	// 获取实体对象的导出数据
	List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
	getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
	// 根据表头进行筛选排序
	sortAndFilterExportField(excelParams, titlemap);
	short rowHeight = getRowHeight(excelParams);
	int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
	// 下移数据,模拟插入
	sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true);
	if (excelParams.size() == 0) {
		return;
	}
	Iterator<?> its = dataSet.iterator();
	while (its.hasNext()) {
		Object t = its.next();
		index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
	}
	// 合并同类项
	mergeCells(sheet, excelParams, titleHeight);
}
Example 5
Source File: PoiTest.java    From easyexcel with Apache License 2.0 5 votes vote down vote up 
@Test
public void lastRowNum255() throws IOException, InvalidFormatException {
    String file = "D:\\test\\complex.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new File(file));
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    xssfSheet.shiftRows(1, 4, 10, true, true);

    FileOutputStream fileout = new FileOutputStream("d://test/r2" + System.currentTimeMillis() + ".xlsx");
    sxssfWorkbook.write(fileout);
    sxssfWorkbook.dispose();
    sxssfWorkbook.close();

    xssfWorkbook.close();
}
Example 6
Source File: ExcelExportOfTemplateUtil.java    From jeasypoi with Apache License 2.0 5 votes vote down vote up 
/**
 * 往Sheet 填充正常数据,根据表头信息 使用导入的部分逻辑,坐对象映射
 * 
 * @param teplateParams
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception {

	if (workbook instanceof XSSFWorkbook) {
		super.type = ExcelType.XSSF;
	}
	// 获取表头数据
	Map<String, Integer> titlemap = getTitleMap(sheet);
	Drawing patriarch = sheet.createDrawingPatriarch();
	// 得到所有字段
	Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
	ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
	String targetId = null;
	if (etarget != null) {
		targetId = etarget.value();
	}
	// 获取实体对象的导出数据
	List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
	getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
	// 根据表头进行筛选排序
	sortAndFilterExportField(excelParams, titlemap);
	short rowHeight = getRowHeight(excelParams);
	int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
	// 下移数据,模拟插入
	sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true);
	if (excelParams.size() == 0) {
		return;
	}
	Iterator<?> its = dataSet.iterator();
	while (its.hasNext()) {
		Object t = its.next();
		index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
	}
	// 合并同类项
	mergeCells(sheet, excelParams, titleHeight);
}
Example 7
Source File: ExcelExportOfTemplateUtil.java    From easypoi with Apache License 2.0 5 votes vote down vote up 
/**
 * 往Sheet 填充正常数据,根据表头信息 使用导入的部分逻辑,坐对象映射
 * 
 * @param teplateParams
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet,
                            Workbook workbook) throws Exception {

    if (workbook instanceof XSSFWorkbook) {
        super.type = ExcelType.XSSF;
    }
    // 获取表头数据
    Map<String, Integer> titlemap = getTitleMap(sheet);
    Drawing patriarch = sheet.createDrawingPatriarch();
    // 得到所有字段
    Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
    ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
    String targetId = null;
    if (etarget != null) {
        targetId = etarget.value();
    }
    // 获取实体对象的导出数据
    List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
    getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
    // 根据表头进行筛选排序
    sortAndFilterExportField(excelParams, titlemap);
    short rowHeight = getRowHeight(excelParams);
    int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
    //下移数据,模拟插入
    sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(),
        sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true);
    if (excelParams.size() == 0) {
        return;
    }
    Iterator<?> its = dataSet.iterator();
    while (its.hasNext()) {
        Object t = its.next();
        index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
    }
    // 合并同类项
    mergeCells(sheet, excelParams, titleHeight);
}
Example 8
Source File: ExcelUtils.java    From onetwo with Apache License 2.0 5 votes vote down vote up 
public static void addRow(Sheet sheet, int rowNumber, int count){
	if(count<0){
		//remove
		rowNumber += Math.abs(count);
	}
	if(rowNumber>sheet.getLastRowNum())
		rowNumber = sheet.getLastRowNum();
	sheet.shiftRows(rowNumber, sheet.getLastRowNum(), count, true, true);
}
Example 9
Source File: SpreadsheetShiftRows.java    From openbd-core with GNU General Public License v3.0 5 votes vote down vote up 
public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	cfSpreadSheetData	spreadsheet = null;
	int start=0, end=0, rows = 1;
	
	
	/*
	 * Collect up the parameters
	 */
	if ( parameters.size() == 2 ){
		spreadsheet	= (cfSpreadSheetData)parameters.get(1);
		start				= parameters.get(0).getInt() - 1;
		end					= start;
	}else if ( parameters.size() == 3 ){
		spreadsheet	= (cfSpreadSheetData)parameters.get(2);
		start				= parameters.get(1).getInt() - 1;
		rows				= parameters.get(0).getInt() - 1;
	}else if ( parameters.size() == 4 ){
		spreadsheet	= (cfSpreadSheetData)parameters.get(3);
		start				= parameters.get(2).getInt() - 1;
		end					= parameters.get(1).getInt() - 1;
		rows				= parameters.get(0).getInt() - 1;
	}  	
	
	/*
	 * Validate parameters
	 */
	if ( start < 0 )
		throwException(_session, "column must be 1 or greater (" + start + ")");
	
	Sheet	sheet = spreadsheet.getActiveSheet();
	sheet.shiftRows( start, end, rows, true, true );

	return cfBooleanData.TRUE;
}