• 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>&nbsp;&nbsp;&nbsp;
            <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> &gt;&gt;
                  </tbody>
              </table>
        </div>
    </div>
</body>
</html>
  • 相关阅读:
    使用StoryBoard执行动画
    导航控制器里边添加UIScrollView （automaticallyAdjustsScrollViewInsets）
    IOS中手势UIGestureRecognizer
    已知UIScrollView放大后的Frame和放大之前的Frame计算放大的瞄点坐标
    UIScrollView 在手指点击的坐标处放大
    [ZOJ3899]State Reversing
    多项式操作汇总
    [BZOJ4916]神犇和蒟蒻
    [BZOJ4176]Lucas的数论
    [BZOJ3309]DZY Loves Math
  • 原文地址：https://www.cnblogs.com/itzlg/p/10792475.html
Copyright © 2020-2023  润新知