Java实现Excel删除或插入，下面的行数上移或下移

package utils;

import java.io.File;

import java.io.FileOutputStream;

import java.util.ArrayList;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import common.Common;

import entity.HDD;

public class RAID_HOTSPARE_Add_HDD_Start_Last_Row_Utils {

private String filename;

private ArrayList<HDD> HDDs=new ArrayList<>();

private String Folder_path;

public RAID_HOTSPARE_Add_HDD_Start_Last_Row_Utils(String file_name,ArrayList<HDD> HDDs,String Folder_path) {

this.filename=file_name;

this.HDDs=HDDs;

this.Folder_path= Folder_path;

}

public boolean Write_For_RAID_HOTSPARE_From_Last_Row(){

long start = System.currentTimeMillis();

String source_file_path = Folder_path+"\\"+filename;

String dist_file_path = Folder_path+"\\Update\\"+filename;

System.out.println("path: "+source_file_path);

File file = new File(source_file_path);

if(!file.exists()){

System.out.println("Can't find the File: "+source_file_path);

return false;

}

try {

@SuppressWarnings("deprecation")

XSSFWorkbook xssfWorkbook = new XSSFWorkbook(source_file_path);

XSSFSheet xssfSheet = xssfWorkbook.getSheet("RAID_HOTSPARE_PRIHDD_SGLCONT0");

if(xssfSheet==null){

xssfSheet = xssfWorkbook.getSheetAt(0);

}

if(xssfSheet==null){

System.out.println("Can't find this Sheet");

return false;

}

//RAID_HOTSPARE_GR2_PRIHDD_MULTICONT

//RAID_HOTSPARE_GR2_PRIHDD_SGLCONT0

//RAID_HOTSPARE_GR2_SECHDD_MULTICONT

//RAID_HOTSPARE_GR2_SECHDD_SGLCONT0

//RAID_HOTSPARE_GR2_TERTHDD_MULTICONT

//RAID_HOTSPARE_GR2_QUADHDD_MULTICONT

XSSFCellStyle style=Common.SetCellStyle(xssfWorkbook);

int total_index=xssfSheet.getLastRowNum()+1;

int end_index = Common.getEOF_Row(xssfSheet, total_index);

int FC_column_number = Common.Get_RAID_FC_Row(xssfSheet, filename);

int Row_length =Common.getLastCellForRow(xssfSheet, end_index);

System.out.println("Row_length: "+Row_length);

ArrayList<String> Exist_FC_List=Common.GetExistFCList(xssfSheet, end_index, FC_column_number);

HDDs=Common.RemoveDuplicateFCs(Exist_FC_List,HDDs);

int current_insert_row=end_index;

for(int i=0;i<HDDs.size();i++){

// 参数1表示下移一行，如果是负数，则表示上移，从current_insert_row+i 这一行开始下移，xssfSheet.getLastRowNum()下移结束

xssfSheet.shiftRows(current_insert_row+i, xssfSheet.getLastRowNum(), 1,true,false);

XSSFRow Row = xssfSheet.createRow(current_insert_row+i);

XSSFRow source_Row = xssfSheet.getRow(current_insert_row-1);

for(int j=0;j<Row_length;j++){

XSSFCell Cell=Row.createCell(j);

if(j==FC_column_number||j==(FC_column_number-2)){

Cell.setCellValue(HDDs.get(i).getFC());

}else{

Cell.setCellValue(Common.getCellStringValue(source_Row.getCell(j)));

}

Cell.setCellStyle(style);

}

}

FileOutputStream fout = new FileOutputStream(dist_file_path);

xssfWorkbook.write(fout);

fout.close();

long end = System.currentTimeMillis();

long cost =end-start;

System.out.println("Cost time: "+ cost);

System.out.println("Write "+filename+" finish!!!!!!!");

} catch (Exception e) {

e.printStackTrace();

System.out.println("Write "+filename+" FAIL!!!!!!!");

return false;

}

return true;

}

}

我正在使用POI来操纵现有的Excel工作表。

我想删除开头的行，并在结尾添加行。当我删除行时，我会将所有行都向上移动。

int numOfRows = sheet.getLastRowNum()+1;

int extraRows = numOfRows - maxAllowedRows;

for(int i=1;i<=extraRows;i++){ //i intialized with 1 to ignore header row.

sheet.removeRow(sheet.getRow(i));

}

//这种情况就是上移了extraRows 行

sheet.shiftRows(extraRows+1,sheet.getLastRowNum(),-extraRows);

在该程序的当前运行中，它工作正常。但是，当程序再次运行时，在第一次运行中删除并移入的行数与之相等，因此空白行出现在底部的工作表中，而我尝试在工作表底部添加的任何新行都会在这些空白后添加行。

有没有一种方法可以避免在删除和移位操作之后出现空白行(即，是否有任何方法可以实际删除行？)