在网上看了许多poi的文章,最后自己总结了一下~
就直接进入主题吧!!!!!
首先 pom.xml
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
然后 PrincipalCase.java
@Data
public class PrincipalCase {
private Integer principalCaseId;
private Integer principalId;
private String batch;
private Integer batchTypeId;
private String caseType;
private String allot;
private String inDate;
private String endDate;
private String remark;
}
dao层
//导入案件(传list或者传单个对象)
void savePrincipalCase(PrincipalCase principalCase);
//查询所有案件
List<PrincipalCase> queryAllPrincipalCase();
//查询是否存在
int queryById(int principalCaseId);
//修改
void updatePrincipalCasex(PrincipalCase principalCase);
service层
//查询所有案件
List<PrincipalCase> queryAllPrincipalCase();
//导入
boolean readExcel(String fileName, MultipartFile file) throws IOException;
service下的impl层
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Service
@SuppressWarnings("all")
public class PrincipalCaseServiceImpl implements PrincipalCaseService {
@Autowired
private PrincipalCaseMapper principalCaseMapper;
@Override
public List<PrincipalCase> queryAllPrincipalCase() {
return principalCaseMapper.queryAllPrincipalCase();
}
@Override
public boolean readExcel(String fileName, MultipartFile file) throws IOException {
boolean notNull = false;
List<PrincipalCase> principalCaseList = new ArrayList<>();
//识别文件格式
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new MyException("上传文件格式不正确");
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
// 读取Excel文件
InputStream is = null;
is = file.getInputStream();
Workbook workbook = null;
if (isExcel2003) {
workbook = new HSSFWorkbook(is);
is.close();
} else {
workbook = new XSSFWorkbook(is);
is.close();
}
Sheet sheet = workbook.getSheetAt(0);
if (sheet != null) {
notNull = true;
}
PrincipalCase principalCase;
String cell = null;
// 循环工作表
for (int i = 1; i <= sheet.getLastRowNum(); i++) {//r = ? 表示从第几行开始循环 从你的数据那行开始
Row row = sheet.getRow(i);//通过sheet表单对象得到 行对象
if (row == null) {
continue;
}
principalCase = new PrincipalCase();
if (row.getCell(0).getCellType().equals(1)) {//循环时,得到每一行的单元格进行判断
throw new MyException("导入失败(第" + (i + 1) + "行,请确认格式)");
}
/**
* 获取单元格 row.getCell(0)
* 设置单元格类型 row.getCell(0).setCellType(CellType.STRING)
* 获取单元格数据 String cellValue = row.getCell(0).getStringCellValue();
*/
row.getCell(0).setCellType(CellType.STRING);
try {
cell = row.getCell(0).getStringCellValue();//得到每一行第1个单元格的值
} catch (Exception e) {
throw new MyException("导入失败(第" + (i + 1) + "行,principalCaseId未填写)");
}
String principalCaseId = cell;
row.getCell(1).setCellType(CellType.STRING);
try {
cell = row.getCell(1).getStringCellValue();//得到每一行第2个单元格的值
} catch (Exception e) {
throw new MyException("导入失败(第" + (i + 1) + "行,principalId未填写)");
}
String principalId = cell;
row.getCell(2).setCellType(CellType.STRING);
try {
cell = row.getCell(2).getStringCellValue();//得到每一行第3个单元格的值
} catch (Exception e) {
throw new MyException("导入失败(第" + (i + 1) + "行,batch未填写)");
}
String batch = cell;
row.getCell(3).setCellType(CellType.STRING);
try {
cell = row.getCell(3).getStringCellValue();//得到每一行第4个单元格的值
} catch (Exception e) {
throw new MyException("导入失败(第" + (i + 1) + "行,batchTypeId未填写)");
}
String batchTypeId = cell;
row.getCell(4).setCellType(CellType.STRING);
try {
cell = row.getCell(4).getStringCellValue();//得到每一行第5个单元格的值
} catch (Exception e) {
throw new MyException("导入失败(第" + (i + 1) + "行,caseType未填写)");
}
String caseType = cell;
row.getCell(5).setCellType(CellType.STRING);
try {
cell = row.getCell(5).getStringCellValue();//得到每一行第6个单元格的值
} catch (Exception e) {
throw new MyException("导入失败(第" + (i + 1) + "行,allot未填写)");
}
String allot = cell;
row.getCell(6).setCellType(CellType.STRING);
try {
cell = row.getCell(6).getStringCellValue();//得到每一行第7个单元格的值
} catch (Exception e) {
throw new MyException("导入失败(第" + (i + 1) + "行,inDate未填写)");
}
String inDate = cell;
row.getCell(7).setCellType(CellType.STRING);
try {
cell = row.getCell(7).getStringCellValue();//得到每一行第8个单元格的值
} catch (Exception e) {
throw new MyException("导入失败(第" + (i + 1) + "行,endDate未填写)");
}
String endDate = cell;
row.getCell(8).setCellType(CellType.STRING);
try {
cell = row.getCell(8).getStringCellValue();//得到每一行第9个单元格的值
} catch (Exception e) {
cell = "";//没有插入空,前面也能设置
throw new MyException("导入失败(第" + (i + 1) + "行,remark未填写)");
}
String remark = cell;
principalCase.setPrincipalCaseId(Integer.valueOf(principalCaseId));
principalCase.setPrincipalId(Integer.valueOf(principalId));
principalCase.setBatch(batch);
principalCase.setBatchTypeId(Integer.valueOf(batchTypeId));
principalCase.setCaseType(caseType);
principalCase.setAllot(allot);
principalCase.setInDate(inDate.substring(0,10));//读取并截取时间的长度:yyyy-mm-dd
principalCase.setEndDate(endDate.substring(0,10));
principalCase.setRemark(remark);
principalCaseList.add(principalCase);
}
for (PrincipalCase aCase : principalCaseList) {
//查询是否存在,1存在,0不存在
int count = principalCaseMapper.queryById(aCase.getPrincipalCaseId());
System.out.println(count);
if (count == 0) {
principalCaseMapper.savePrincipalCase(aCase);
System.out.println(" 插入 " + aCase);
} else {
principalCaseMapper.updatePrincipalCasex(aCase);
System.out.println(" 更新 " + aCase);
}
}
return notNull;
}
}
controller层
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
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 java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/myframeleft")
public class PrincipalCaseController {
@Autowired
private PrincipalCaseService principalCaseService;
//查询所有案件
@RequestMapping("queryAllPrincipalCase")
public Map queryAllPrincipalCase() {
System.out.println("进来了");
Map map = new HashMap();
map.put("data", principalCaseService.queryAllPrincipalCase());
map.put("code", 0);
return map;
}
//导出
@RequestMapping("export")
public void export(HttpServletResponse response) {
List<PrincipalCase> principalCaseList = principalCaseService.queryAllPrincipalCase();
// 创建一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个工作表
HSSFSheet sheet = workbook.createSheet("案件");
// 添加表头行
HSSFRow hssfRow = sheet.createRow(0);
// 设置单元格格式居中
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 添加表头内容
HSSFCell headCell = hssfRow.createCell(0);
headCell.setCellValue("principalCaseId");
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(1);
headCell.setCellValue("principalId");
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(2);
headCell.setCellValue("batch");
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(3);
headCell.setCellValue("batchTypeId");
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(4);
headCell.setCellValue("caseType");
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(5);
headCell.setCellValue("allot");
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(6);
headCell.setCellValue("inDate");
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(7);
headCell.setCellValue("endDate");
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(8);
headCell.setCellValue("remark");
headCell.setCellStyle(cellStyle);
// 添加数据内容
for (int i = 0; i < principalCaseList.size(); i++) {
hssfRow = sheet.createRow((int) i + 1);
PrincipalCase principalCase = principalCaseList.get(i);
// 创建单元格,并设置值
HSSFCell cell = hssfRow.createCell(0);
cell.setCellValue(principalCase.getPrincipalCaseId());
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(1);
cell.setCellValue(principalCase.getPrincipalId());
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(2);
cell.setCellValue(principalCase.getBatch());
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(3);
cell.setCellValue(principalCase.getBatchTypeId());
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(4);
cell.setCellValue(principalCase.getCaseType());
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(5);
cell.setCellValue(principalCase.getAllot());
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(6);
cell.setCellValue(principalCase.getInDate().substring(0,10));//设置时间长度yyyy-mm-dd
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(7);
cell.setCellValue(principalCase.getEndDate().substring(0,10));
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(8);
cell.setCellValue(principalCase.getRemark());
cell.setCellStyle(cellStyle);
}
// 保存Excel文件
try {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream os = response.getOutputStream();
response.setHeader("Content-disposition", "attachment;filename=principalCase.xls");//默认Excel名称
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//导入
@RequestMapping("readExcel")
@ResponseBody
public Map readExcel(@RequestParam MultipartFile file) {
boolean a = false;
String fileName = file.getOriginalFilename();
Map map = new HashMap();
try {
a = principalCaseService.readExcel(fileName, file);
if (a) {
map.put("code", 0);
map.put("msg", "导入EXCEL成功!");
return map;
} else {
map.put("code", 1);
map.put("msg", "导入EXCEL失败!");
return map;
}
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
}
jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<title>layui在线调试</title>
<link rel="stylesheet" href="../layui/css/layui.css?t=1559524960918" media="all">
<style>
body {
margin: 10px;
height: 200px;
}
</style>
</head>
<body>
<table class="layui-hide" id="demo" lay-filter="test"></table>
<script type="text/html" id="toolbarDemo">
<a href="${pageContext.request.contextPath}/myframeleft/export" class="layui-btn">导出</a>
<button type="button" class="layui-btn" id="test3">导入</button>
</script>
<script type="text/html" id="barDemo">
<a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="detail">查看</a>
<a class="layui-btn layui-btn-xs" lay-event="edit">编辑</a>
<a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del">删除</a>
</script>
<script src="../layui/layui.js?t=1559524960918"></script>
<script>
layui.config({
version: '1559524960918' //为了更新 js 缓存,可忽略
});
layui.use(['laydate', 'laypage', 'layer', 'table', 'carousel', 'upload', 'element', 'slider'], function () {
var laydate = layui.laydate //日期
, laypage = layui.laypage //分页
, layer = layui.layer //弹层
, table = layui.table //表格
, carousel = layui.carousel //轮播
, upload = layui.upload //上传
, element = layui.element //元素操作
, slider = layui.slider //滑块
;
//执行一个 table 实例
table.render({
elem: '#demo'
// ,height: 420
, url: '/myframeleft/queryAllPrincipalCase' //数据接口
, title: '用户表'
, page: true //开启分页
, toolbar: '#toolbarDemo' //开启工具栏,此处显示默认图标,可以自定义模板,详见文档
// , totalRow: true //开启合计行
, cols: [[ //表头
{type: 'checkbox', fixed: 'left'}
, {field: 'batch', title: '批次号', width: 90, sort: true, fixed: 'left'}
, {field: 'principalId', title: '委托方名称', width: 100, align: 'center'}
, {field: 'batchTypeId', title: '批次状态', width: 100, sort: true}
, {field: 'caseType', title: '案件类型', width: 100, sort: true}
, {field: 'inDate', title: '委案录入日期', width: 135, sort: true}
, {field: 'endDate', title: '委案结束日期', width: 135, sort: true}
, {field: 'remark', title: '备注', sort: true, totalRow: true}
, {fixed: 'right', title: '操作', toolbar: '#barDemo', width: 150}
]]
});
//上传
upload.render({
elem: '#test3'
, url: '/myframeleft/readExcel'
, accept: 'file' //普通文件
, exts: 'xls|xlsx' //允许上传的文件后缀
, multiple: true
, done: function (res) {
layer.msg(res.msg);
location.reload();
}
});
});
</script>
</body>
</html>
基本 就是这样了,谢谢大家的阅读~作为一个新晋级的程序猿,请大家多多包涵,一起学习~~~