51dev.com IT技术开发者社区

51dev.com 技术开发者社区

poi操作修改excel、word模板工具

代码星球阅读(13)2020-05-23 收藏0次评论

这是一个操作excel或者word的工具,个人认为使用起来还是比较方便的,本工具值针对操作文本内容的。

  jdk7

  poi使用的版本是 3.10-FINAL

 

先说Excel模板的修改,这是修改前:

测试代码

public class TestOfficeUtils {


    public static void main(String[] args) throws IOException {
        //创建数据
        Map<String,String> data = new HashMap<>();
        data.put("name","刘凯");
        data.put("age","25");
        //获取模板
        File file= new File("F:/test.xlsx");
        InputStream stream = new FileInputStream(file);
        XSSFWorkbook xwb = new XSSFWorkbook(stream);
        //修改的模板数据在sheet0里面
        OfficeUtils.changeExcelSheet(xwb, data, 0);
        //输出到新位置
        File fileNew = new File("F:/test_new.xlsx");
        FileOutputStream streamOut = new FileOutputStream(fileNew);
        xwb.write(streamOut);
        stream.close();
    }
}

  

运行后:

 

下面是修word的,,word中的数据修改分为两种,一种操作纯文本的,有一种是操作文档中表格数据的:

  运行前:

测试代码:

    public static void main(String[] args) throws IOException {
        //创建数据
        Map<String,String> data = new HashMap<>();
        data.put("name","刘凯");
        data.put("age","25");
        //获取模板
        File file= new File("F:/test.docx");
        InputStream stream = new FileInputStream(file);
        XWPFDocument xwb = new XWPFDocument(stream);
        //修改word中文本
        OfficeUtils.changeText(xwb, data);
        //修改word中表格
        OfficeUtils.changeTables(xwb, data);
        //输出到新位置
        File fileNew = new File("F:/test_new.docx");
        FileOutputStream streamOut = new FileOutputStream(fileNew);
        xwb.write(streamOut);
        stream.close();
    }

 

运行后:

下面是工具类的代码:FileUtils是我们项目里的工具类,删掉或者替换掉即可:

package com.xpsd.cloud.office;

import com.xpsd.cloud.commons.utils.FileUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Office操作工具
 * @LewKAY
 * 20180410
 */
public class OfficeUtils {

    /**
     * 修改Doc中文本域内对象
     * 处理对象${name}类型
     */
    public static void changeText(XWPFDocument document, Map<String, String> map) {

        List<XWPFParagraph> paragraphs = document.getParagraphs();
        for (XWPFParagraph paragraph : paragraphs) {
            //判断此段落时候需要进行替换
            String text = paragraph.getText();
            if (checkText(text)) {
                List<XWPFRun> runs = paragraph.getRuns();
                for (XWPFRun run : runs) {
                    run.setText(changeValue(run.toString(), map), 0);
                }
            }
        }

    }

    /**
     * 修改Doc中Tables text对象
     * 处理对象${name}类型
     *
     * @param document
     */
    public static void changeTables(XWPFDocument document, Map map) {
        List<XWPFTable> tables = document.getTables();
        for (int i = 0; i < tables.size(); i++) {
            XWPFTable table = tables.get(i);
            if (table.getRows().size() >= 1) {
                if (checkText(table.getText())) {
                    List<XWPFTableRow> rows = table.getRows();
                    eachTable(rows, map);
                }
            }
        }
    }

    /**
     * 遍历表格
     */
    public static void eachTable(List<XWPFTableRow> rows, Map<String, String> textMap) {
        for (XWPFTableRow row : rows) {
            List<XWPFTableCell> cells = row.getTableCells();
            for (XWPFTableCell cell : cells) {
                //判断单元格是否需要替换
                if (checkText(cell.getText())) {
                    List<XWPFParagraph> paragraphs = cell.getParagraphs();
                    for (XWPFParagraph paragraph : paragraphs) {
                        List<XWPFRun> runs = paragraph.getRuns();
                        for (XWPFRun run : runs) {
                            run.setText(changeValue(run.toString(), textMap), 0);
                        }
                    }
                }
            }
        }
    }

    /**
     * 匹配传入信息集合与模板
     *
     * @param value 模板需要替换的区域
     */
    public static String changeValue(String value, Map<String, String> textMap) {
        value.trim();
        Set<Map.Entry<String, String>> textSets = textMap.entrySet();
        for (Map.Entry<String, String> textSet : textSets) {
            //匹配模板与替换值 格式${key}
            String key = "${" + textSet.getKey() + "}";
            if (value.indexOf(key) != -1) {
                value = textSet.getValue();
            }
        }
        //模板未匹配到区域替换为空
        if (checkText(value)) {
            value = "";
        }
        return value;
    }

    /**
     * 判断文本中时候包含$
     */
    public static boolean checkText(String text) {
        boolean check = false;
        if (text.indexOf("$") != -1) {
            check = true;
        }
        return check;

    }


    public static void createOfficeFile(POIXMLDocument source, String dirs, String fileName) throws IOException {
        FileUtils.createDirectory(dirs);
        File file = new File(dirs + fileName);
        FileOutputStream stream = new FileOutputStream(file);
        source.write(stream);
        stream.close();
    }

    /**
     * 修改Excell 值
     */
    public static void changeExcelSheet(XSSFWorkbook xwb, Map<String, String> data, int sheet) {
        XSSFSheet xSheet = xwb.getSheetAt(sheet);
        int begin = xSheet.getFirstRowNum();
        int end = xSheet.getLastRowNum();
        for (int i = begin; i <= end; i++) {
            if (null == xSheet.getRow(i)) {
                continue;
            }
            int beginCell = xSheet.getRow(i).getFirstCellNum();
            int endCell = xSheet.getRow(i).getLastCellNum();
            for (int j = beginCell; j <= endCell; j++) {
                if (xSheet.getRow(i).getCell(j) == null) {
                    continue;
                }
                String value =changeValue(xSheet.getRow(i).getCell(j).getStringCellValue(), data);
                if(!value.equals("")){
                    xSheet.getRow(i).getCell(j).setCellValue(value);
                }
            }
        }
    }
}

 

以上就是poi操作修改excel、word模板工具的全部内容。