有 Java 编程相关的问题?

你可以在下面搜索框中键入要查询的问题!

excel使用java从xlsx中删除特定行

我正在尝试将文件名与xlsx表进行比较。。。如果文件名与excel工作表的值匹配。。。我想从excel工作表中删除该特定行。。。。 下面是我到目前为止尝试的代码

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JButton;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class try2 {
    public static void main(String[] args)
            throws FileNotFoundException, IOException {
        File[] files=  new File
                ("C:\\wamp\\www\\ptry\\sample\\xl").listFiles();
        String s = null;
        for(File file:files){
            s=file.getName();
            s=s.replaceAll(".xlsx", "");
            }
            File xl=new File("C:\\wamp\\www\\ptry\\sample\\xl.xlsx");
            FileInputStream f=new FileInputStream(xl);
            XSSFWorkbook wb = new XSSFWorkbook (f);
            XSSFSheet sheet = wb.getSheetAt(0);
            int row=sheet.getLastRowNum()+1;
            int colm=sheet.getRow(0).getLastCellNum();
            for(int i=0;i<row;i++){
                XSSFRow r=sheet.getRow(i);
                String m=cellToString(r.getCell(0));
                if(s.equals(m)){
                    System.out.println(m);
                }
            }
        }


    public static String cellToString(XSSFCell cell) {
        int type;
        Object result = null;
        type = cell.getCellType();
        switch (type) {
            case XSSFCell.CELL_TYPE_STRING:
                result = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                result = "";
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                result = cell.getCellFormula();
        }
        return result.toString();
    }
    }

这里的“s”是用于保存文件名的变量,“m”是用于保存excel值的变量 问题是: 如果我使用

   if(s.equals(m))
  {
    System.out.println(m);
  }

如何从EXCEL中删除匹配的行??? (例如)

文件名:

a.xlsx

b.xlsx

c.xlsx

出类拔萃。xlsx

a

b

d

c

我想从excel中删除a和b。xlsx

更新: 基于YASH suggustion,我尝试了以下代码

                 if(s.equals(m)){
                    System.out.println(m);
                    sheet.removeRow(r);
                }

它从excel中删除了第一个值。xlsx(a)。。。。并在线程“main”java中显示异常。lang.NullPointerException错误位于下一行

                     String m=cellToString(r.getCell(0));

我试过了

              XSSFRow r = sheet.getRow(i);
                    if(r==null){
                        continue;
                    }

它只从excel中获取两个值(a、b)。xlsx


共 (2) 个答案

  1. # 1 楼答案

    After removing the row with RemoveRow(r) shift the remaining rows by 1 as shown below to avoid NullPointer Exception
    
        sheet.RemoveRow(r);   
    
                int rowIndex = r.RowNum;
    
                int lastRowNum = sheet.LastRowNum;
    
                if (rowIndex >= 0 && rowIndex < lastRowNum)
            {
                sheet.ShiftRows(rowIndex + 1, lastRowNum, -1);
            }
    
  2. # 2 楼答案

    最后我得到了答案

            import java.io.File;
            import java.io.FileInputStream;
            import java.io.FileNotFoundException;
            import java.io.FileOutputStream;
            import java.io.IOException;
            import java.util.ArrayList;
            import java.util.Iterator;
            import java.util.List;
            import java.util.logging.Level;
            import java.util.logging.Logger;
            import javax.swing.JButton;
            import org.apache.poi.EncryptedDocumentException;
            import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
            import org.apache.poi.openxml4j.util.ZipSecureFile;
            import org.apache.poi.sl.usermodel.Sheet;
            import org.apache.poi.ss.usermodel.Cell;
            import org.apache.poi.ss.usermodel.Row;
            import org.apache.poi.ss.usermodel.Workbook;
            import org.apache.poi.ss.usermodel.WorkbookFactory;
            import org.apache.poi.xssf.usermodel.XSSFCell;
            import org.apache.poi.xssf.usermodel.XSSFRow;
            import org.apache.poi.xssf.usermodel.XSSFSheet;
            import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
            public class try1 {
               public static void main(String[] args)
            throws FileNotFoundException, IOException {
                 File[] files=new File("D:\\aa\\a").listFiles();
                       String s = null;
                                       for(File file:files){
                                        s=file.getName();
                                                s=s.replaceAll(".xlsx", "");  
    
                        File xl=new File("D:\\aa\\w1.xlsx");
                        FileInputStream f=new FileInputStream(xl);
    
                       XSSFWorkbook wb = new XSSFWorkbook (f);
                        XSSFSheet sheet = wb.getSheetAt(0);
                        int row=sheet.getLastRowNum();
                    int colm=sheet.getRow(0).getLastCellNum();
                    for(int i=0;i<row;i++){
    
                        XSSFRow r = sheet.getRow(i);
                        if(r==null){
                           sheet.getRow(i+1);
                            continue;
                        }
                    Cell cell=r.getCell(0);
                    String m=cellToString(r.getCell(0));
                    if(s.equals(m)){
                      System.out.println("s :"+m);
                       sheet.removeRow(r);
    
                       }}
    
    
                    FileOutputStream out= 
                new FileOutputStream(new File("D:\\aa\\w1.xlsx"));
                   wb.write(out);
                    }               
                         }public static String cellToString(XSSFCell cell) {
    
                    int type;
                    Object result = null;
                    type = cell.getCellType();
                   switch (type) {
                     case XSSFCell.CELL_TYPE_STRING:
                        result = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        result = "";
                        break;
                    case XSSFCell.CELL_TYPE_FORMULA:
                        result = cell.getCellFormula();
                    }
    
                    return result.toString();
                }
    
            }