java实现批量修改Excel表格内容

java实现批量修改Excel表格内容

• 怎么快速修改Excel表格里的内容，肯定好多人直接在Excel表格里ctrl+f查找替换了，如果需要改的很多，Excel表格里的数据很多几钱多条呢？这样的话半天就啥也变干了，搞数据吧。
  

一、导入依赖

 <dependency>
      <groupId>fr.opensagres.xdocreport</groupId>
      <artifactId>xdocreport</artifactId>
      <version>2.0.1</version>
    </dependency>
    <dependency>
      <groupId>org.apache.xmlbeans</groupId>
      <artifactId>xmlbeans</artifactId>
      <version>5.0.1</version>
    </dependency>
    <dependency>
      <groupId>org.apache.xmlgraphics</groupId>
      <artifactId>xmlgraphics-commons</artifactId>
      <version>2.2</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.9</version>
      <exclusions>
        <exclusion>
          <groupId>org.apache.xmlbeans</groupId>
          <artifactId>xmlbeans</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-examples</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-excelant</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-scratchpad</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>com.github.cloudecho</groupId>
      <artifactId>xmlbean</artifactId>
      <version>1.5.5</version>
    </dependency>
    <dependency>
      <groupId>org.dom4j</groupId>
      <artifactId>dom4j</artifactId>
      <version>2.1.1</version>
    </dependency>
    <dependency>
      <groupId>org.apache.xmlbeans</groupId>
      <artifactId>xmlbeans</artifactId>
      <version>3.1.0</version>
    </dependency>

有些依赖是使用不到的，还有其他小工具的依赖，就没有删掉，实现这个功能还是不影响的 。

二、代码如下

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @Author zhangdapao
 * @Date 2021/7/28 16:46
 */

public class ExcelPOI {

    public void excelPOI () {
    try {
        String fileName = "F:\\shu.xlsx";//修改f盘的.xlsx文件
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(fileName));
        Map<String, String> fields = new HashMap<String, String>();
        fields = getFieldMap();  //获取要修改字段的集合
        String fillStr="";    //存储aaa文件里的数据
        String[] fillSplit=null;
        XSSFSheet xSheet = xwb.getSheetAt(0);  //获取excel表的第一个sheet
        for (int i = 0; i <= xSheet.getLastRowNum(); i++) {  //遍历所有的行
            if(xSheet.getRow(i)==null){ //这行为空执行下次循环
                continue;
            }
            for (int j = 0; j <=  xSheet.getRow(i).getPhysicalNumberOfCells(); j++) {  //遍历当前行的所有列
                StringBuffer sb =new StringBuffer();
                if(xSheet.getRow(i).getCell(j)==null){//为空跳出循环
                    continue;
                }
                fillStr = (xSheet.getRow(i)).getCell(j).toString();//获取当前单元格的数据
                fillSplit=fillStr.split(",");//切割，以","为分隔符的这个可以根据自己情况改变
                XSSFCell xCell=xSheet.getRow(i).getCell(j); //获取单元格对象
                String s = xCell.toString();
                fillSplit=s.split(",");
                for (int js = 0; js < fillSplit.length; js++) {
                    fillSplit[js]=(fields.get(fillSplit[js].trim())==null?fillSplit[js]:fields.get(fillSplit[js].trim()));
                    if(js+1==fillSplit.length){
                        sb.append(fillSplit[js]+"。");
                    }else{
                        sb.append(fillSplit[js]+",");
                    }
                }
                String s1 = sb.toString();
                xCell.setCellValue(s1);
            }
            System.out.println("已经完成第"+(i+1)+"列");
        }
        FileOutputStream out = new FileOutputStream(fileName);
        xwb.write(out);
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

    private Map<String, String> getFieldMap(){
        Map<String, String> fields = new HashMap<String, String>();
	        fields.put("A", "测试1");
	        fields.put("B", "测试2");
	        return fields;
	    }
	    public static void main(String[] args) {
	        ExcelPOI a = new ExcelPOI();
      	  a.excelPOI();
  	  }
}

 
 
 
 

看完如果对你有帮助，感谢点赞支持！