Java 使用 Apache POI HSSF 从 Excel 工作表中删除一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1834971/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Removing a row from an Excel sheet with Apache POI HSSF
提问by fmaste
I'm using the Apache POi HSSF library to import info into my application. The problem is that the files have some extra/empty rows that need to be removed first before parsing.
我正在使用 Apache POi HSSF 库将信息导入我的应用程序。问题是文件有一些额外/空行，需要在解析之前先删除。
There's not a HSSFSheet.removeRow( int rowNum )method. Only removeRow( HSSFRow row ). The problem with this it that empty rows can't be removed. For example:
没有HSSFSheet.removeRow( int rowNum )方法。只有removeRow( HSSFRow row )。问题是无法删除空行。例如：
sheet.removeRow( sheet.getRow(rowNum) );
gives a NullPointerException on empty rows because getRow()returns null.
Also, as I read on forums, removeRow()only erases the cell contents but the row is still there as an empty row.
在空行上给出 NullPointerException，因为getRow()返回 null。此外，正如我在论坛上阅读的那样，removeRow()只删除了单元格内容，但该行仍然作为空行存在。
Is there a way of removing rows (empty or not) without creating a whole new sheet without the rows that I want to remove?
有没有办法删除行（空或非空）而不创建一个没有我想要删除的行的全新工作表？
回答by delfuego
I'm trying to reach back into the depths of my brain for my POI-related experience from a year or two ago, but my first question would be: why do the rows need to be removed before parsing? Why don't you just catch the null result from the sheet.getRow(rowNum)call and move on?
我正试图回到我的大脑深处，以了解一两年前与 POI 相关的经历，但我的第一个问题是：为什么需要在解析之前删除行？为什么不从sheet.getRow(rowNum)调用中捕获空结果并继续？
回答by True Soft
The HSSFRowhas a method called setRowNum(int rowIndex).
该HSSFRow有一个名为方法setRowNum(int rowIndex)。
When you have to "delete" a row, you put that index in a List. Then, when you get to the next row non-empty, you take an index from that list and set it calling setRowNum(), and remove the index from that list. (Or you can use a queue)
当您必须“删除”一行时，您可以将该索引放在List. 然后，当您到达非空的下一行时，您从该列表中获取一个索引并将其设置为调用setRowNum()，然后从该列表中删除该索引。（或者你可以使用队列）
回答by Erich Kitzmueller
Something along the lines of
类似的东西
int newrownum=0;
for (int i=0; i<=sheet.getLastRowNum(); i++) {
HSSFRow row=sheet.getRow(i);
if (row) row.setRowNum(newrownum++);
}
should do the trick.
应该做的伎俩。
回答by AndreAY
/**
* Remove a row by its index
* @param sheet a Excel sheet
* @param rowIndex a 0 based index of removing row
*/
public static void removeRow(HSSFSheet sheet, int rowIndex) {
int lastRowNum=sheet.getLastRowNum();
if(rowIndex>=0&&rowIndex<lastRowNum){
sheet.shiftRows(rowIndex+1,lastRowNum, -1);
}
if(rowIndex==lastRowNum){
HSSFRow removingRow=sheet.getRow(rowIndex);
if(removingRow!=null){
sheet.removeRow(removingRow);
}
}
}
回答by trailmax
I know, this is a 3 year old question, but I had to solve the same problem recently, and I had to do it in C#. And here is the function I'm using with NPOI, .Net 4.0
我知道，这是一个 3 年前的问题，但我最近不得不解决同样的问题，而且我不得不用 C# 来解决。这是我与 NPOI 一起使用的功能，.Net 4.0
public static void DeleteRow(this ISheet sheet, IRow row)
{
sheet.RemoveRow(row); // this only deletes all the cell values
int rowIndex = row.RowNum;
int lastRowNum = sheet.LastRowNum;
if (rowIndex >= 0 && rowIndex < lastRowNum)
{
sheet.ShiftRows(rowIndex + 1, lastRowNum, -1);
}
}
回答by relos
My special case (it worked for me):
我的特殊情况（它对我有用）：
//Various times to delete all the rows without units
for (int j=0;j<7;j++) {
//Follow all the rows to delete lines without units (and look for the TOTAL row)
for (int i=1;i<sheet.getLastRowNum();i++) {
//Starting on the 2nd row, ignoring first one
row = sheet.getRow(i);
cell = row.getCell(garMACode);
if (cell != null)
{
//Ignore empty rows (they have a "." on first column)
if (cell.getStringCellValue().compareTo(".") != 0) {
if (cell.getStringCellValue().compareTo("TOTAL") == 0) {
cell = row.getCell(garMAUnits+1);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(BB1" + ":BB" + (i - 1) + ")");
} else {
cell = row.getCell(garMAUnits);
if (cell != null) {
int valor = (int)(cell.getNumericCellValue());
if (valor == 0 ) {
//sheet.removeRow(row);
removeRow(sheet,i);
}
}
}
}
}
}
}
回答by Vishwanath gowda k
This answer is an extension over AndreAY's answer, Giving you complete function on deleting a row.
这个答案是对 AndreAY 答案的扩展，为您提供删除行的完整功能。
public boolean deleteRow(String sheetName, String excelPath, int rowNo) throws IOException {
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
try {
FileInputStream file = new FileInputStream(new File(excelPath));
workbook = new XSSFWorkbook(file);
sheet = workbook.getSheet(sheetName);
if (sheet == null) {
return false;
}
int lastRowNum = sheet.getLastRowNum();
if (rowNo >= 0 && rowNo < lastRowNum) {
sheet.shiftRows(rowNo + 1, lastRowNum, -1);
}
if (rowNo == lastRowNum) {
XSSFRow removingRow=sheet.getRow(rowNo);
if(removingRow != null) {
sheet.removeRow(removingRow);
}
}
file.close();
FileOutputStream outFile = new FileOutputStream(new File(excelPath));
workbook.write(outFile);
outFile.close();
} catch(Exception e) {
throw e;
} finally {
if(workbook != null)
workbook.close();
}
return false;
}
