java - 如何使用java删除或删除Excel工作表中的空行
问题描述
输入Excel文件......
25010082512 25002207512 1044 1044 NGN NGN
36620841728 36617009228 1066 1066 NGN NGN
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class RemoveEmptyCellInExcel {
//shifting empty columns
public static void shift(File f){
File F=f;
HSSFWorkbook workbook = null;
HSSFSheet sheet=null;
Boolean isRowEmpty=false;
try{
FileInputStream is=new FileInputStream(F);
workbook= new HSSFWorkbook(is);
sheet = workbook.getSheetAt(0);
//sheet.setDisplayGridlines(false);
for(int i = 3; i < sheet.getLastRowNum(); i++){
if(sheet.getRow(i)==null){
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
i--;
continue;
}
for(int j=0; j<sheet.getRow(i).getLastCellNum();j++){
if(sheet.getRow(i).getCell(j).toString().trim().equals(""))
{
isRowEmpty=true;
}else {
isRowEmpty=false;
break;
}
}
if(isRowEmpty==true){
sheet.shiftRows(i+ 1, sheet.getLastRowNum(), -1);
i--;
}
}
//Writing output to the same file.
FileOutputStream fileOut = new FileOutputStream("--------");
workbook.write(fileOut);
fileOut.close();
System.out.println("Successfully wrote the content in the file");
}
catch(Exception e){
e.printStackTrace();
}
}
public static void main(String args[]) {
//Input file path
File f=new File("------------");
RemoveEmptyCellInExcel.shift(f);
}
}
我需要以下输出.....
25010082512 25002207512 1044 1044 NGN NGN
36620841728 36617009228 1066 1066 NGN NGN
解决方案
尝试使用此代码:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
public class RemoveEmptyCellInExcel {
//shifting empty columns
public static void shift(File f){
HSSFWorkbook workbook;
HSSFSheet sheet;
int firstColumn;
int endColumn;
boolean isRowEmpty = true;
try{
FileInputStream is=new FileInputStream(f);
workbook= new HSSFWorkbook(is);
sheet = workbook.getSheetAt(0);
//sheet.setDisplayGridlines(false);
//block to set column bounds
Iterator<Row> iter = sheet.rowIterator();
firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
endColumn = 0;
while (iter.hasNext()) {
Row row = iter.next();
short firstCell = row.getFirstCellNum();
if (firstCell >= 0) {
firstColumn = Math.min(firstColumn, firstCell);
endColumn = Math.max(endColumn, row.getLastCellNum());
}
}
// main logic block
for (int i = 0; i< sheet.getLastRowNum(); i++) {
if (sheet.getRow(i) != null) {
isRowEmpty = true;
Row row = sheet.getRow(i);
for (int j = firstColumn; j < endColumn; j++) {
if (j >= row.getFirstCellNum() && j < row.getLastCellNum()) {
Cell cell = row.getCell(j);
if (cell != null) {
if (!cell.getStringCellValue().equals("")) {
isRowEmpty = false;
break;
}
}
}
}
//if empty
if (isRowEmpty) {
System.out.println("Found empty row on: " + row.getRowNum());
sheet.shiftRows(row.getRowNum() + 1, sheet.getLastRowNum(), -1);
i--;
}
}
// if row is null
else{
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
i--;
}
}
//Writing output to the same file.
FileOutputStream fileOut = new FileOutputStream("Test.xls");
workbook.write(fileOut);
fileOut.close();
System.out.println("Successfully wrote the content in the file");
}
catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) {
//Input file path
File f=new File("Test.xls");
RemoveEmptyCellInExcel.shift(f);
}
}
推荐阅读
- swift - AVPlayer SwiftUI 检查状态 (MVVM)
- c - 如果发生多个渲染器切换,SDL_SetRenderTarget() 会挂起一段时间
- html - 绝对定位没有按我的意愿工作
- python - 如何使用python重复片段的部分n次
- linux - 如果所有线程都访问同一个函数,我们是否需要使用信号量?
- java - 在 ExecutorService 中没有得到想要的结果
- excel - 为什么我的 VBA 代码在 Windows 7 中有效,但在 10 中无效?如何解决此错误?
- java - Hibernate 单表继承 - 作为属性嵌套在子类中的基类抛出 PropertyAccessException
- python - 如何在defaultdict(list)中存储字典
- c++ - (x+1) > x 如何计算为 0 和 1?