POI 导入导出实践

根据一个朋友的需求，其需要处理一个 Excel 模板，把其中某一列的数据进行降序排序，并增加一列存放某两列的乘积

经过一段时间的折腾，基本功能算实现完了。发现对于我来说，难点在于单元格的合并。
1）排序后，前面的序号乱了，需写逻辑处理序号并合并相应单元格
2）对于excel内容的合并

处理前：

处理后(原料含量排序、增加一列实际含量，实际含量=原料含量*原料成分含量)：

完整代码如下：
创建一个 springboot 项目
建表（表字段见实体类或mapper类）

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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.5</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.xiao</groupId>
	<artifactId>handerPF</artifactId>
	<version>xiao.1.0</version>
	<name>handerPF</name>
	<description>handerPFexcel</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		
		<dependency>
		    <groupId>org.mybatis.spring.boot</groupId>
		    <artifactId>mybatis-spring-boot-starter</artifactId>
		    <version>2.1.4</version>
		</dependency>
		<dependency>
	  		<groupId>org.springframework.boot</groupId>
	  		<artifactId>spring-boot-starter-jdbc</artifactId>
	  	</dependency>
		<dependency>
			<groupId>com.oracle.database.jdbc</groupId>
			<artifactId>ojdbc8</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi</artifactId>
		    <version>3.16</version>
		</dependency>
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>3.16</version>
		</dependency>
		<dependency>
		    <groupId>net.sf.json-lib</groupId>
		    <artifactId>json-lib</artifactId>
		    <version>2.4</version>
		    <classifier>jdk15</classifier>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

application.properties

server.port=8080

## 配置数据源
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/xiao_database202105?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
#spring.datasource.password=mysql8

## 配置数据源
#spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
#spring.datasource.url=jdbc:oracle:thin:@localhost:1521:ORCL
#spring.datasource.username=SCOTT
#spring.datasource.password=xiao

# mybatis 全局配置/主配置文件
mybatis.config-location=classpath:mybatis/mybatis-config.xml
# mybatis mapper映射文件
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

logging.file.name=logs/handlePF.log

三个实体类

public class Pffile {
   
	private String id;
	private String fjmc;
	private Date cjsj;
	private String pfmc;
	private String pfxh;
	
	//getter、setter方法
}

public class Ylinfo {
   
	private String id;
	private int xh;
	private String ylmc;
	private String inci;
	private BigDecimal ylhl;
	private BigDecimal ylcfhl;
	private BigDecimal ylsjhl;
	private String symd;
	private String bz;
	private String pfid;
	private int ylxh;
	
	private String pfmc; // 仅传值使用

	//getter、setter方法
}

// 处理表格时使用
public class PoiModel {
   
    //内容
    private Object content;
    //行标
    private int rowIndex;
    //列标
    private int cellIndex;
    //标记
    public int flag;
    
    //getter、setter方法
}

mapper.xml

<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xiao.dao.PffileMapper">
	<sql id="columns">
		ID,FJMC,CJSJ,PFMC,PFXH
	</sql>
	
	<select id="queryPffile" resultType="com.xiao.entity.Pffile" parameterType="java.lang.String">
		select <include refid="columns"/>
		from XIAO_PFFILE_INFO
		where FJMC = #{fjmc, jdbcType=VARCHAR}
		order by PFXH ASC
	</select>	

	<insert id="insertPffile" parameterType="com.xiao.entity.Pffile">
		insert into XIAO_PFFILE_INFO 
		values(#{id},#{fjmc},#{cjsj},#{pfmc},#{pfxh})
	</insert>
	
	<delete id="deletePffileByFjmc" parameterType="java.lang.String">
		DELETE FROM XIAO_PFFILE_INFO WHERE FJMC = #{fjmc, jdbcType=VARCHAR}
	</delete>
</mapper>

<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xiao.dao.YlinfoMapper">
	<sql id="columns">
		ID,XH,YLMC,INCI,YLHL,YLCFHL,YLSJHL,SYMD,BZ,PFID
	</sql>
	
	<select id="queryYlInfo" resultType="com.xiao.entity.Ylinfo" parameterType="java.lang.String">
		select <include refid="columns"/>
		from XIAO_YLINFO
		where pfid = #{pfid, jdbcType=VARCHAR}
		order by ylhl desc,xh asc,ylcfhl desc
	</select>	

	<insert id="insertYlInfo" parameterType="com.xiao.entity.Ylinfo">
		insert into XIAO_YLINFO 
		values(#{id},#{xh},#{ylmc},#{inci},#{ylhl},#{ylcfhl},#{ylsjhl},#{symd},#{bz},#{pfid})
	</insert>
	
	<!-- oracle批量插入 -->
	<insert id="batchSave" parameterType="java.util.List">
		INSERT INTO XIAO_YLINFO
		(<include refid="columns"/>)
		<foreach item="item" index="index" collection="list" separator="union all">
         (
			SELECT 
				#{item.id},
				#{item.xh},
				#{item.ylmc},
				#{item.inci},
				#{item.ylhl},
				#{item.ylcfhl},
				#{item.ylsjhl},
				#{item.symd},
				#{item.bz},
				#{item.pfid}
			FROM DUAL
         )
         </foreach>
	</insert>
	
	<!-- mysql批量插入 -->
	<insert id="batchSaveMysql" parameterType="java.util.List">
		INSERT INTO XIAO_YLINFO
		(<include refid="columns"/>)
		VALUES
		<foreach item="item" index="index" collection="list" separator=",">
         (
			#{item.id},
			#{item.xh},
			#{item.ylmc},
			#{item.inci},
			#{item.ylhl},
			#{item.ylcfhl},
			#{item.ylsjhl},
			#{item.symd},
			#{item.bz},
			#{item.pfid}
         )
         </foreach>
	</insert>
	
	<delete id="deleteYlinfoByPfid" parameterType="java.lang.String">
		DELETE FROM XIAO_YLINFO WHERE PFID = #{pfid, jdbcType=VARCHAR}
	</delete>
</mapper>

DAO

public interface PffileMapper {
   
	/**
	 * 保存配方文件数据
	 * @param pffile
	 */
	void insertPffile(Pffile pffile);
	
	/**
	 * 查询配方文件数据
	 * @param fjmc
	 * @return
	 */
	List<Pffile> queryPffile(String fjmc);
	
	/**
	 * 删除配方文件数据
	 * @param fjmc
	 */
	void deletePffileByFjmc(String fjmc);
}

public interface YlinfoMapper {
   

	/**
	 * 保存原料数据
	 * @param ylpo
	 */
	void insertYlInfo(Ylinfo ylpo);
	
	/**
	 * 批量保存原料数据--oracle
	 * @param list
	 */
	void batchSave(List<Ylinfo> list);
	
	/**
	 * 批量保存原料数据--mysql
	 * @param list
	 */
	void batchSaveMysql(List<Ylinfo> list);
	
	/**
	 * 查询原料数据
	 * @param pfid
	 * @return
	 */
	List<Ylinfo> queryYlInfo(String pfid);
	
	/**
	 * 删除原料数据
	 * @param pfid
	 * @return
	 */
	void deleteYlinfoByPfid(String pfid);
}

工具类

public class CommonUtil {
   
	// 获取uuid