execl word

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>ipam-backend</artifactId>
        <groupId>com.hainei</groupId>
        <version>2.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>ipam-common</artifactId>
<dependencies>
    <!--阿里云上传视频-->
    <dependency>
        <groupId>com.aliyun</groupId>
        <artifactId>aliyun-java-sdk-core</artifactId>
        <version>3.7.1</version>
    </dependency>
    <dependency>
        <groupId>com.aliyun</groupId>
        <artifactId>aliyun-java-sdk-vod</artifactId>
        <version>2.11.6</version>
    </dependency>
    <dependency>
        <groupId>com.google.code.gson</groupId>
        <artifactId>gson</artifactId>
        <version>1.4</version>
    </dependency>
    <!-- jackson -->
    <dependency>
        <groupId>org.codehaus.jackson</groupId>
        <artifactId>jackson-core-asl</artifactId>
        <version>1.9.13</version>
    </dependency>
    <dependency>
        <groupId>org.codehaus.jackson</groupId>
        <artifactId>jackson-mapper-asl</artifactId>
        <version>1.9.13</version>
    </dependency>


    <!--数据导出excel-->
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.16</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.16</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.16</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-scratchpad</artifactId>
        <version>3.16</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>3.0.3</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-test</artifactId>
        <version>5.1.3.RELEASE</version>
        <scope>compile</scope>
    </dependency>

</dependencies>


</project>

工具类：

parseExcel

package com.hainei.common.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import static com.hainei.common.constants.ExportParam.END_STR;
import static com.hainei.common.constants.ExportParam.START_STR;

/**
 * @Auther: GUJUANJUAN
 * @Date: 2019/12/24 09:38
 * @Description: 读取excel内容
 */
public class ParseExcel {

    /**
     * 解析xls为后缀的excel，获取所有{{}}中的值
     * @param file
     * @return
     * @throws Exception
     */
    public static List<String> parseXls(MultipartFile file) throws IOException {
        List<String> list = new ArrayList<>();
        //POI导入文件,存放到list集合
        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
        //只读取第一个sheet
        HSSFSheet sheetAt = workbook.getSheetAt(0);
        //这个表示当前sheet有多少行数据,一行一行读取就行
        int rows = sheetAt.getPhysicalNumberOfRows();
        //循环行数
        for (int i = 0; i < rows; i++) {
            //获取当前行的列数
            int cellls = sheetAt.getRow(i).getPhysicalNumberOfCells();
            //循环列数
            for (int j = 0; j < cellls; j++) {
                HSSFCell cell = sheetAt.getRow(i).getCell(j);
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    String value = cell.getStringCellValue();
                    list.addAll(splitValue(value));
                }
            }
        }
        return list;
    }

    /**
     * 获取{{}}中的值
     * @param value
     * @return
     */
    public static List<String> splitValue(String value) {
        List<String> list = new ArrayList<>();
        //如果值 包含{{
        if (value.contains(START_STR)) {
            //将字符串以{{分隔为数组
            String[] arry = value.split("\\{" + "\\{");
            for (int i = 0; i < arry.length; i++) {
                String str = arry[i];
                //如果字符串包含}}
                if (str.contains(END_STR)) {
                    //将字符串的}}去掉
                    str = str.substring(0, str.lastIndexOf("}}")).trim();
                    list.add(str);
                }
            }
        }
        return list;
    }

    /**
     * 解析xlsx为后缀的excel，获取所有{{}}中的值
     * @param file
     * @return
     * @throws Exception
     */
    public static List<String> parseXlsx(MultipartFile file) throws IOException {
        List<String> list = new ArrayList<>();
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        //只读取第一个sheet
        XSSFSheet sheetAt = workbook.getSheetAt(0);
        //当前sheet有多少行数据,一行一行读取就行,但是会把没有数据的行读出来,需要加异常处理
        int rows = sheetAt.getPhysicalNumberOfRows();
        for (int i = 0; i < rows; i++) {
            int cells = sheetAt.getRow(i).getPhysicalNumberOfCells();
            for (int j = 0; j < cells; j++) {
                XSSFCell cell = sheetAt.getRow(i).getCell(j);
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    String value = cell.getStringCellValue();
                    list.addAll(splitValue(value));
                }
            }
        }
        return list;
    }
}

ParseWord

package com.hainei.common.utils;

import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Range;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblPr;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @Auther: GUJUANJUAN
 * @Date: 2019/12/25 09:16
 * @Description:
 */
public class ParseWord {
    /**
     * 解析doc
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static List<String> parseDoc(File file) throws IOException {
        List<String> list = new ArrayList<>();
        InputStream is = new FileInputStream(file);
        HWPFDocument doc = new HWPFDocument(is);
        Range range = doc.getRange();
        list = printInfo(range, list);
        is.close();
        return list;
    }

    /**
     * 遍历Range 找到所有被{{}}包含的字段
     *
     * @param range
     */
    private static List<String> printInfo(Range range, List<String> list) {
        //获取段落数
        int paraNum = range.numParagraphs();
        for (int i = 0; i < paraNum; i++) {
            String value = range.getParagraph(i).text();
            list.addAll(ParseExcel.splitValue(value));
        }
        return list;
    }

    /**
     * 解析docx
     * @param file
     * @return
     * @throws IOException
     */
    public static List<String> parseDocx(File file) throws IOException {
        List<String> list = new ArrayList<>();
        FileInputStream fis = new FileInputStream(file);
        XWPFDocument doc = new XWPFDocument(fis);
        list = parseXWPFParagraph(doc, list);
        list = parseXWPFTable(doc, list);
        return list;
    }

    /**
     * 解析docx的所有段落
     *
     * @param doc
     * @param list
     * @return
     */
    private static List<String> parseXWPFParagraph(XWPFDocument doc, List<String> list) {
        List<XWPFParagraph> paras = doc.getParagraphs();
        for (XWPFParagraph para : paras) {
            String value = para.getText();
            list.addAll(ParseExcel.splitValue(value));
        }
        return list;
    }

    /**
     * 解析docx的所有表格
     *
     * @param doc
     * @param list
     * @return
     */
    private static List<String> parseXWPFTable(XWPFDocument doc, List<String> list) {
        List<XWPFTable> tables = doc.getTables();
        List<XWPFTableRow> rows;
        List<XWPFTableCell> cells;
        for (XWPFTable table : tables) {
            //表格属性
            CTTblPr pr = table.getCTTbl().getTblPr();
            //获取表格对应的行
            rows = table.getRows();
            for (XWPFTableRow row : rows) {
                //获取行对应的单元格
                cells = row.getTableCells();
                for (XWPFTableCell cell : cells) {
                    String value = cell.getText();
                    list.addAll(ParseExcel.splitValue(value));
                }
            }
        }
        return list;
    }
}

 

ExportExcel

package com.hainei.common.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.Map;

import static com.hainei.common.constants.ExportParam.END_STR;
import static com.hainei.common.constants.ExportParam.START_STR;


/**
 * @Auther: GUJUANJUAN
 * @Date: 2019/12/24 10:52
 * @Description: 导出excel工具类
 */
public class ExportExcel {
    /**
     * 导出后缀名为xls的excel
     *
     * @param file
     * @param response
     * @param fileName
     * @param map
     * @throws Exception
     */
    public static void exportXls(MultipartFile file, HttpServletResponse response, String fileName, Map<String, Object> map) throws Exception {
        ServletOutputStream out = response.getOutputStream();
        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
        response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
        //只读取第一个sheet
        HSSFSheet sheetAt = workbook.getSheetAt(0);
        //这个表示当前sheet有多少行数据,一行一行读取就行
        int rows = sheetAt.getPhysicalNumberOfRows();
        for (int i = 0; i < rows; i++) {
            int cellls = sheetAt.getRow(i).getPhysicalNumberOfCells();
            for (int j = 0; j < cellls; j++) {
                HSSFCell cell = sheetAt.getRow(i).getCell(j);
                if (cell.getCellTypeEnum().equals(CellType.STRING)) {
                    //获取单元格的值
                    String value = cell.getStringCellValue();
                    cell.setCellValue(setValue(value, map));
                }
            }
        }
        workbook.write(out);
        out.flush();
        out.close();
    }

    public static String setValue(String value, Map<String, Object> map) {
        if (value.contains(START_STR)) {
            String[] arry = value.split("\\{" + "\\{");
            for (int i = 0; i < arry.length; i++) {
                String str = arry[i];
                if (str.contains(END_STR)) {
                    str = str.substring(0, str.lastIndexOf("}}"));
                    if (map.containsKey(str.trim())) {
                        value = value.replace(START_STR + str + END_STR, map.get(str.trim()) + "");
                    }
                }
            }
        }
        return value;
    }

    /**
     * 导出后缀名为xlsx的excel
     *
     * @param file
     * @param response
     * @param fileName
     * @param map
     * @throws Exception
     */
    public static void exportXlsx(MultipartFile file, HttpServletResponse response, String fileName, Map<String, Object> map) throws Exception {
        ServletOutputStream out = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        //只读取第一个sheet
        XSSFSheet sheetAt = workbook.getSheetAt(0);
        //这个表示当前sheet有多少行数据,一行一行读取就行,但是会把没有数据的行读出来,需要加异常处理
        int rows = sheetAt.getPhysicalNumberOfRows();
        for (int i = 0; i < rows; i++) {
            int cells = sheetAt.getRow(i).getPhysicalNumberOfCells();
            for (int j = 0; j < cells; j++) {
                XSSFCell cell = sheetAt.getRow(i).getCell(j);
                if (CellType.STRING.equals(cell.getCellTypeEnum())) {
                    String value = cell.getStringCellValue();
                    cell.setCellValue(setValue(value, map));
                }
            }
        }
        workbook.write(out);
        out.flush();
        out.close();
    }
}

ExportWord

package com.hainei.common.utils;


import cn.afterturn.easypoi.word.WordExportUtil;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Range;
import org.apache.poi.xwpf.usermodel.XWPFDocument;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Map;

import static com.hainei.common.constants.ExportParam.END_STR;
import static com.hainei.common.constants.ExportParam.START_STR;

/**
 * @Auther: GUJUANJUAN
 * @Date: 2019/12/25 10:21
 * @Description:
 */
public class ExportWord {
    /**
     * doc 替换值并导出
     *
     * @param response
     * @param fileName
     * @param map
     * @throws Exception
     */
    public static void exportDoc(File file, String temDir, HttpServletResponse response, String fileName, Map<String, Object> map, HttpServletRequest request) {
        try {
            String userAgent = request.getHeader("user-agent").toLowerCase();
            if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes("utf-8"), "ISO-8859-1");
            }
            InputStream is = new FileInputStream(file);
            HWPFDocument doc = new HWPFDocument(is);
            Range range = doc.getRange();
            for (String key : map.keySet()) {
                range.replaceText(key, map.get(key) + "");
                range.replaceText(START_STR, "");
                range.replaceText(END_STR, "");
            }
            String tmpPath = temDir + fileName;
            FileOutputStream fos = new FileOutputStream(tmpPath);
            doc.write(fos);
            // 设置强制下载不打开
            response.setContentType("application/force-download");
            // 设置文件名
            response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
            OutputStream out = response.getOutputStream();
            doc.write(out);
            out.close();
            fos.close();
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            delFileWord(temDir,fileName);
        }
    }

    public static void delFileWord(String filePath, String fileName) {
        File file = new File(filePath + fileName);
        File file1 = new File(filePath);
        file.delete();
        file1.delete();
    }

    public static void exportDocx(String templatePath, String temDir, HttpServletRequest request, HttpServletResponse response, String fileName, Map<String, Object> map) throws Exception {
        try {
            String userAgent = request.getHeader("user-agent").toLowerCase();
            if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes("utf-8"), "ISO-8859-1");
            }
            XWPFDocument doc = WordExportUtil.exportWord07(templatePath, map);
            String tmpPath = temDir + fileName;
            FileOutputStream fos = new FileOutputStream(tmpPath);
            doc.write(fos);
            // 设置强制下载不打开
            response.setContentType("application/force-download");
            // 设置文件名
            response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
            OutputStream out = response.getOutputStream();
            doc.write(out);
            out.close();
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            delFileWord(temDir,fileName);
        }

    }
}