poi小案例
一：pom
<?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">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.cykj</groupId>
<artifactId>ycappdemo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>ycappdemo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!--引入web场景-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--springboot的jdbc场景启动器-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--springboot的mybatis启动器-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<!--引入mysql的jar包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.30</version>
<scope>runtime</scope>
</dependency>
<!--引入jstl标签库-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
</dependency>
<!--添加jsp依赖-->
<dependency>
<groupId>org.apache.tomcat.embed</groupId>
<artifactId>tomcat-embed-jasper</artifactId>
</dependency>
<!--引入lombok依赖-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.18</version>
</dependency>
<!--热部署-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
<scope>true</scope>
</dependency>
<!--POI-->
<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.14</version>
</dependency>
<!--文件上传-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<!--springboot的test场景-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
二：application.yml
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/ycapp
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
三：application.properties
# 配置默认前缀
spring.mvc.view.prefix=/WEB-INF/view/
# 配置默认后缀
spring.mvc.view.suffix=.jsp
# 对xml中实体对象引用的配置
mybatis.type-aliases-package=com.cykj.ycappdemo.domain
# 指定xml路径，xml在resources目录的位置
mybatis.mapper-locations=classpath:mybatis/*.xml
# mybatis的核心配置文件路径
#mybatis.config-location=
四：domain
package com.cykj.ycappdemo.domain;
import lombok.*;
import lombok.experimental.Accessors;
import java.io.Serializable;
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
public @Data class Machine implements Serializable{
private Integer id;
private String cpu;
private String memory;
private String rom;
private String price_gear;
private String android_version;
private String brand;
private String model;
public Machine(String cpu, String memory, String rom, String price_gear, String android_version, String brand, String model) {
this.cpu = cpu;
this.memory = memory;
this.rom = rom;
this.price_gear = price_gear;
this.android_version = android_version;
this.brand = brand;
this.model = model;
}
/*public static void main(String[] args) {
Machine machine = new Machine();
machine.setCpu("1").setMemory("2").setRom("3").setPrice_gear("4");
System.out.println(machine);
}*/
}
五：mapper
package com.cykj.ycappdemo.mapper;
import com.cykj.ycappdemo.domain.Machine;
import java.util.List;
public interface MachineMapper {
public List<Machine> findAllMachine();
public Boolean addMachine(Machine machine);
// public Boolean updateMachine(Machine machine);
}
六：mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cykj.ycappdemo.mapper.MachineMapper">
<select id="findAllMachine" parameterType="Machine" resultType="Machine">
SELECT id,cpu,memory,rom,price_gear,android_version,brand,model
FROM machine_evaluation
</select>
<insert id="addMachine" parameterType="machine">
INSERT INTO machine_evaluation(cpu,memory,rom,price_gear,android_version,brand,model)
VALUES (#{cpu},#{memory},#{rom},#{price_gear},#{android_version},#{brand},#{model})
</insert>
<!--<update id="updateMachine" parameterType="machine">
UPDATE machine_evaluation SET
</update>-->
</mapper>
七：service
package com.cykj.ycappdemo.service;
import com.cykj.ycappdemo.domain.Machine;
import org.springframework.web.multipart.MultipartFile;
import javax.crypto.Mac;
import java.util.List;
public interface MachineService {
public List<Machine> findAll();
public Boolean add(Machine machine);
public Boolean batchImport(String fileName, MultipartFile file) throws Exception;
// public Boolean update(Machine machines);
}
八：serviceImpl
package com.cykj.ycappdemo.service.impl;
import com.cykj.ycappdemo.common.MyException;
import com.cykj.ycappdemo.domain.Machine;
import com.cykj.ycappdemo.mapper.MachineMapper;
import com.cykj.ycappdemo.service.MachineService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Service
public class MachineServiceImpl implements MachineService {
@Autowired
private MachineMapper machineMapper;
@Override
public List<Machine> findAll() {
return machineMapper.findAllMachine();
}
@Override
public Boolean add(Machine machine) {
return machineMapper.addMachine(machine);
}
@Transactional(readOnly = false, rollbackFor = Exception.class)
@Override
public Boolean batchImport(String fileName, MultipartFile file) throws Exception {
boolean notNull = false;
List<Machine> machineList = new ArrayList<>();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new MyException("上传文件格式不正确");
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if (sheet != null) {
notNull = true;
}
Machine machine;
for (int r = 2; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
if (row == null) {
continue;
}
//sheet.getLastRowNum() 的值是 10，所以Excel表中的数据至少是10条；不然报错 NullPointerException
machine = new Machine();
/*if (row.getCell(0).getCellType() != 1) {//循环时，得到每一行的单元格进行判断
throw new MyException("导入失败(第" + (r + 1) + "行,用户名请设为文本格式)");
}*/
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String cpu = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值
if (cpu == null || cpu.isEmpty()) {//判断是否为空
throw new MyException("导入失败(第" + (r + 1) + "行,cpu未填写)");
}
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String memory = row.getCell(1).getStringCellValue();
if (memory == null || memory.isEmpty()) {
throw new MyException("导入失败(第" + (r + 1) + "行,memory未填写)");
}
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String rom = row.getCell(2).getStringCellValue();
if (rom == null || rom.isEmpty()) {
throw new MyException("导入失败(第" + (r + 1) + "行,rom未填写)");
}
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String price_gear = row.getCell(3).getStringCellValue();
if (price_gear == null || price_gear.isEmpty()) {
throw new MyException("导入失败(第" + (r + 1) + "行,price_gear未填写)");
}
row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String android_version = row.getCell(4).getStringCellValue();
if (android_version == null || android_version.isEmpty()) {
throw new MyException("导入失败(第" + (r + 1) + "行,android_version未填写)");
}
row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String brand = row.getCell(5).getStringCellValue();
if (brand == null || brand.isEmpty()) {
throw new MyException("导入失败(第" + (r + 1) + "行,brand未填写)");
}
String model;
if(row.getCell(6) == null){
model = null;
}else {
row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
model = row.getCell(6).getStringCellValue();
}
/*if (model == null || model.isEmpty()) {
throw new MyException("导入失败(第" + (r + 1) + "行,model未填写)");
}*/
//完整的循环一次 就组成了一个对象
machine.setCpu(cpu).setMemory(memory).setRom(rom).setPrice_gear(price_gear)
.setAndroid_version(android_version).setBrand(brand).setModel(model);
machineList.add(machine);
}
for (Machine machineResord : machineList) {
machineMapper.addMachine(machineResord);
/*String name = machineResord.getUsername();
int cnt = userMapper.selectByName(name);
if (cnt == 0) {
userMapper.addUser(userResord);
System.out.println(" 插入 "+userResord);
} else {
userMapper.updateUserByName(userResord);
System.out.println(" 更新 "+userResord);
}*/
}
return notNull;
}
}
九：controller
package com.cykj.ycappdemo.controller;
import com.cykj.ycappdemo.domain.Machine;
import com.cykj.ycappdemo.service.MachineService;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
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.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
@Controller
public class PoiController {
@Autowired
private MachineService machineService;
@RequestMapping(value = "/import")
public String exImport(@RequestParam(value = "filename")MultipartFile file, HttpSession session) {
boolean a = false;
String fileName = file.getOriginalFilename();
try {
a = machineService.batchImport(fileName, file);
} catch (Exception e) {
e.printStackTrace();
}
return "redirect:index";
}
@RequestMapping(value = "/export")
@ResponseBody
public void export(HttpServletResponse response) throws IOException {
List<Machine> machineList = machineService.findAll();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("machine数据");
HSSFRow row = null;
row = sheet.createRow(0);//创建第一个单元格
row.setHeight((short) (26.25 * 20));
row.createCell(0).setCellValue("整机评测数据列表");//为第一行单元格设值
/*为标题设计空间
* firstRow从第1行开始
* lastRow从第0行结束
*
*从第1个单元格开始
* 从第3个单元格结束
*/
CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 6);
sheet.addMergedRegion(rowRegion);
/*CellRangeAddress columnRegion = new CellRangeAddress(1,4,0,0);
sheet.addMergedRegion(columnRegion);*/
/*
* 动态获取数据库列 sql语句 select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name='user' and table_schema='test'
* 第一个table_name 表名字
* 第二个table_name 数据库名称
* */
row = sheet.createRow(1);
row.setHeight((short) (22.50 * 20));//设置行高
row.createCell(0).setCellValue("cpu");//为第一个单元格设值
row.createCell(1).setCellValue("memory");//为第二个单元格设值
row.createCell(2).setCellValue("rom");//为第三个单元格设值
row.createCell(3).setCellValue("price_gear");//为第三个单元格设值
row.createCell(4).setCellValue("android_version");//为第三个单元格设值
row.createCell(5).setCellValue("brand");//为第三个单元格设值
row.createCell(6).setCellValue("model");//为第三个单元格设值
for (int i = 0; i < machineList.size(); i++) {
row = sheet.createRow(i + 2);
Machine machine = machineList.get(i);
row.createCell(0).setCellValue(machine.getCpu());
row.createCell(1).setCellValue(machine.getMemory());
row.createCell(2).setCellValue(machine.getRom());
row.createCell(3).setCellValue(machine.getPrice_gear());
row.createCell(4).setCellValue(machine.getAndroid_version());
row.createCell(5).setCellValue(machine.getBrand());
row.createCell(6).setCellValue(machine.getModel());
}
sheet.setDefaultRowHeight((short) (16.5 * 20));
//列宽自适应
for (int i = 0; i <= 13; i++) {
sheet.autoSizeColumn(i);
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream os = response.getOutputStream();
response.setHeader("Content-disposition", "attachment;filename=user.xls");//默认Excel名称
wb.write(os);
os.flush();
os.close();
}
}
package com.cykj.ycappdemo.controller;
import com.cykj.ycappdemo.domain.Machine;
import com.cykj.ycappdemo.service.MachineService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import java.util.List;
@Controller
public class IndexController {
@Autowired
private MachineService machineService;
@RequestMapping("/index")
public String index(Model model){
List<Machine> machineList = machineService.findAll();
model.addAttribute("machineList",machineList);
return "index";
}
}
config
package com.cykj.ycappdemo.config;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.context.annotation.Bean;
import org.springframework.web.servlet.config.annotation.ViewControllerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
@Configurable
public class MyWebMvcConfig implements WebMvcConfigurer{
// 所有的WebMvcConfigurer组件都会一起起作用
@Bean
public WebMvcConfigurer webMvcConfigurer(){
WebMvcConfigurer configurer = new WebMvcConfigurer() {
@Override
public void addViewControllers(ViewControllerRegistry registry) {
registry.addViewController("/").setViewName("index");
}
};
return configurer;
}
}
myexception
package com.cykj.ycappdemo.common;
public class MyException extends RuntimeException{
private static final long serialVersionUID = 1L;
/**
* 错误编码
*/
private String errorCode;
/**
* 消息是否为属性文件中的Key
*/
private boolean propertiesKey = true;
/**
* 构造一个基本异常.
*
* @param message
* 信息描述
*/
public MyException(String message)
{
super(message);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
*/
public MyException(String errorCode, String message)
{
this(errorCode, message, true);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
*/
public MyException(String errorCode, String message, Throwable cause)
{
this(errorCode, message, cause, true);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
* @param propertiesKey
* 消息是否为属性文件中的Key
*/
public MyException(String errorCode, String message, boolean propertiesKey)
{
super(message);
this.setErrorCode(errorCode);
this.setPropertiesKey(propertiesKey);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
*/
public MyException(String errorCode, String message, Throwable cause, boolean propertiesKey)
{
super(message, cause);
this.setErrorCode(errorCode);
this.setPropertiesKey(propertiesKey);
}
/**
* 构造一个基本异常.
*
* @param message
* 信息描述
* @param cause
* 根异常类（可以存入任何异常）
*/
public MyException(String message, Throwable cause)
{
super(message, cause);
}
public String getErrorCode()
{
return errorCode;
}
public void setErrorCode(String errorCode)
{
this.errorCode = errorCode;
}
public boolean isPropertiesKey()
{
return propertiesKey;
}
public void setPropertiesKey(boolean propertiesKey)
{
this.propertiesKey = propertiesKey;
}
}
<%--
Created by IntelliJ IDEA.
User: ligong.zhang
Date: 2019/4/28
Time: 15:59
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>云测app性能看板主页</title>
<%-- 支持移动设备优先 --%>
<meta name="viewport" content="width=device-width, initial-scale=1">
<%-- 引入css样式 --%>
<link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
<%-- 引入jquery文件 --%>
<script type="text/javascript" src="bootstrap/js/jquery-1.11.0.min.js"></script>
<%-- 引入bootstrap.js文件之前必须先引入jquery文件 --%>
<script type="text/javascript" src="bootstrap/js/bootstrap.js"></script>
</head>
<body>
<script type="text/javascript">
alert();
</script>
<div class="container">
<br/><br/>
<form class="form-inline" id="form_table" action="/import" enctype="multipart/form-data" method="post">
<div class="form-group">
<a href="/export" class="btn btn-info">导出</a>
</div>
<div class="form-group">
<input type="submit" class="btn btn-success" value="导入"/>
</div>
<div class="form-group">
<input type="file" class="btn btn-default" name="filename"/>
</div>
</form>
<div class="table-responsive">
<table class="table table-hover table-striped table-condensed">
<thead>
<tr>
<th>ID</th>
<th>CPU</th>
<th>MEMORY</th>
<th>ROM</th>
<th>价格档位</th>
<th>安卓版本</th>
<th>品牌</th>
<th>机型</th>
</tr>
</thead>
<tbody>
<c:forEach var="machine" items="${machineList}">
<tr >
<td>${machine.id}</td>
<td class="danger">${machine.cpu}</td>
<td>${machine.memory}</td>
<td class="warning">${machine.rom}</td>
<td >${machine.price_gear}</td>
<td class="success">${machine.android_version}</td>
<td >${machine.brand}</td>
<td class="info">${machine.model}</td>
</tr>
</c:forEach> >>
</tbody>
</table>
</div>
</div>
</body>
</html>
