java--poi技术解析Excel入库

概述:

apache POI是可以对微软office文档进行读和写的工具。

官网

http://poi.apache.org/

组件功能：

POI中的组件（HSSF和XSS）可以读写excel

HSSF和XSSF的区别：

• HSSF:操作97格式的excel，扩展名：.xls 纯二进制，最大行数65535。
• XSSF：操作2007格式excel，扩展名：.xlsx压缩的xml，最大理论无限行。

两个格式的选择：

如果没有特别的要求，不超过65535，优先选.xls，效率高。否则选.xlsx。

使用

导入坐标

        <poi.version>3.17</poi.version>

        <!-- Apache POI -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>${poi.version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>${poi.version}</version>
		</dependency>

测试数据

测试页面

<!DOCTYPE HTML>
<html>
<head>
    <meta charset="UTF-8" />
    <title>数据生成</title>
</head>
<body>
<div>
    <a href="/FileUpload/template/ceshi.xlsx" download="测试表.xlsx">点击下载</a>
    <form method="post" id="file" action="" enctype="multipart/form-data">
        <h3>选择一个文件:</h3>
       <input id="excelFile" type="file" name="uploadFile" />
       <br/><br/>
       <input type="button" value="上传" onclick="uploadFiles();"/>
    </form>
</div>
</body>
<script src="js/jquery-1.10.2.js" type="text/javascript"></script>
<script>
    function uploadFiles(){
// 	var uploadFile = $('#excelFile').get(0).files[0];
        var uploadFile = new FormData($("#file")[0]);
        console.log(uploadFile);
        if("undefined" != typeof(uploadFile) && uploadFile != null && uploadFile != ""){
            $.ajax({
                url:'../upload/templateFile',
                type:'POST',
                data:uploadFile,
                async: false,
                cache: false,
                contentType: false, //不设置内容类型
                processData: false, //不处理数据
                success:function(res){
                    console.log(res.message);
                    console.log(res.data);
                },
                error:function(){
                    alert("上传失败！");
                }
            })
        }else {
            alert("选择的文件无效！请重新选择");
        }
    }
</script>
</html>

pojo

package cn.bufanli.pojo;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;

/**
 * @author BuShuangLi
 * @date 2019/4/11
 * 解析excel 测类类
 */
@Entity
@Table(name = "address")
public class Address  implements Serializable {
	/**
	 * 主键属性:id
	 * GeneratedValue: 指定主键策略
	 * Column:指定列名,如果和属性名一致则可以省略
	 */
	@Id
	//@GeneratedValue(strategy = "")
	@Column(name="id")
	private Long id;
	/**
	 * 序号
	 */
	@Column(name="serial_number")
	private String serialNumber;
	/**
	 * 省份
	 */
	@Column(name="province")
	private String province;
	/**
	 * 城市
	 */
	@Column(name="city")
	private String city;
	/**
	 * 区域
	 */
	@Column(name="area")
	private String area;
	/**
	 * 邮编
	 */
	@Column(name="zip_code")
	private String zipCode;

	get&&set方法略
}

数据库建表语句

CREATE TABLE `address` (
  `id` bigint(20) NOT NULL COMMENT '主键id',
  `serial_number` varchar(20) COLLATE utf8mb4_cs_0900_ai_ci DEFAULT NULL COMMENT '序号',
  `province` varchar(20) COLLATE utf8mb4_cs_0900_ai_ci DEFAULT NULL COMMENT '省份',
  `city` varchar(20) COLLATE utf8mb4_cs_0900_ai_ci DEFAULT NULL COMMENT '城市',
  `area` varchar(20) COLLATE utf8mb4_cs_0900_ai_ci DEFAULT NULL COMMENT '区域',
  `zip_code` varchar(255) COLLATE utf8mb4_cs_0900_ai_ci DEFAULT NULL COMMENT '邮编',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_cs_0900_ai_ci;

统一返回Message类

package cn.bufanli.utils;

import java.io.Serializable;

/** @author bsl */
public class Message<T> implements Serializable {

  /** id */
  private String id;
  /** 状态码 */
  private String code;
  /** 返回数据 */
  private T data;
  /** 返回信息说明 */
  private String message;
     /**
      * 分页总条数
      */
  private Long total;
     /**
      * 总页数
      */
     private Long totalPage;
  public Message() {}

   get&&set略
}

 

id生成工具类

https://blog.youkuaiyun.com/adminBfl/article/details/86536225

controller

package cn.bufanli.controller;

import cn.bufanli.dao.AddressDao;
import cn.bufanli.pojo.Address;
import cn.bufanli.utils.IdWorker;
import cn.bufanli.utils.Message;
import com.sun.javafx.scene.control.skin.VirtualFlow;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;

/**
 * @author BuShuangLi
 * @date 2019/2/14
 */
@Controller
@RequestMapping("upload")
public class UploadController {
    //id生成工具类
	@Autowired
	private IdWorker idWorker;
    //持久层接口
	@Autowired
	private AddressDao addressDao;
     /**
      * 解析excel保存到数据库
      *
      * @return
      */
     @ResponseBody
     @RequestMapping(value="templateFile", method= RequestMethod.POST)
     public Message uploadExcel(@RequestParam MultipartFile uploadFile) throws Exception {
          Message message =  new Message();
          //创建集合接受解析的数据
		List<Address> objects = new LinkedList<>();
		//解析excel 入库
		//解析97  .xls  方法和07 版本一模一样
		//HSSFWorkbook hssfWorkbook = new HSSFWorkbook(uploadFile.getInputStream());

		//解析07版本  .xlsx
		//1.打开工作部
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(uploadFile.getInputStream());
		//2.打开工作表
		//根据名字获取 不推荐使用  名字是 在文件左下角
		//xssfWorkbook.getSheet("Sheet1");
		//根据索引获取 推荐
		XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
		//3.遍历表中的每一行
		for (Row row : sheetAt) {
			//跳过第一行标题
			if(!(row.getRowNum()==0)){
				Address address = new Address();
				//存储id
				address.setId(idWorker.nextId());
				//一格一格读一格一格存
				//第一个是数字
				Cell cell = row.getCell(0);
				//数字用String 字符串接受
				cell.setCellType(CellType.STRING);
				//生成的数据是数字的话会带有 .0
				address.setSerialNumber(cell.getStringCellValue());
				String province = row.getCell(1).getStringCellValue();
				address.setProvince(province);
				String city = row.getCell(2).getStringCellValue();
				address.setCity(city);
				String area = row.getCell(3).getStringCellValue();
				address.setArea(area);

				//数字用String 字符串接受
				Cell zipCode = row.getCell(4);
				zipCode.setCellType(CellType.STRING);
				address.setZipCode(zipCode.getStringCellValue());
				//存到集合
				objects.add(address);
			}
		}
		//保存
		List<Address> addresses = addressDao.saveAll(objects);
		message.setData(addresses);
		message.setMessage("成功");
		return message;
     }

	/**
	 * 存储到本地
	 * @param filecontent
	 */
     public void approvalFile( MultipartFile filecontent){
          OutputStream os = null;
          InputStream inputStream = null;
          String fileName = null;
          try {
               inputStream = filecontent.getInputStream();
               fileName = filecontent.getOriginalFilename();
          } catch (IOException e) {
               e.printStackTrace();
          }
          try {
               String path = "F:\\test\\";
               // 2、保存到临时文件
               // 1K的数据缓冲
               byte[] bs = new byte[1024];
               // 读取到的数据长度
               int len;
               // 输出的文件流保存到本地文件
               File tempFile = new File(path);
               if (!tempFile.exists()) {
                    tempFile.mkdirs();
               }
               os = new FileOutputStream(tempFile.getPath() + File.separator + fileName);
               // 开始读取
               while ((len = inputStream.read(bs)) != -1) {
                    os.write(bs, 0, len);
               }
          } catch (IOException e) {
               e.printStackTrace();
          } catch (Exception e) {
               e.printStackTrace();
          } finally {
               // 完毕，关闭所有链接
               try {
                    os.close();
                    inputStream.close();
               } catch (IOException e) {
                    e.printStackTrace();
               }
          }
     }

}