1、前台页面
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%--
Created by IntelliJ IDEA.
User: andy
Date: 2023/11/3
Time: 13:12
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>货物列表</title>
<%-- <script src="/zg2/js/jquery-1.8.3.js"></script>--%>
</head>
<body>
<div>
<a href="/zg2/prod/exportExel.do">货物导出</a><br/>
<a href="/zg2/prod/importExel.do">货物导入</a><br/>
</div>
<div>
<input type="button" onclick="selectAll()" value="全选" />
<input type="button" onclick="selectAllNo()" value="全不选" />
<input type="button" onclick="selectReverse()" value="反选" />
</div>
<div>
<form action="/zg2/prod/exportSelect.do" method="post">
<table>
<tr>
<th>选择</th>
<th>订单号</th>
<th>货物编码</th>
<th>类型ID</th>
<th>货物名称</th>
<th>类型名称</th>
<th>货物数量</th>
<th>入口时间</th>
</tr>
<c:forEach items="${list}" var="item">
<tr>
<td>
<input type="checkbox" name="ids" value="${item.storeId}" class="selectType" />
</td>
<td>${item.storeId}</td>
<td>${item.productId}</td>
<td>${item.typeId}</td>
<td>${item.productName}</td>
<td>${item.typeName}</td>
<td>${item.productCount}</td>
<td>
<fmt:formatDate value="${item.createTime}" pattern="yyyy-MM-dd HH:mm:ss" />
</td>
</tr>
</c:forEach>
</table>
<br>
<input type="submit" value="选中导出" />
</form>
</div>
<div>
<div id="main" style="width: 600px;height:400px;"></div>
</div>
</body>
<script src="<%=request.getContextPath()%>/js/echarts.js"></script>
<script src="<%=request.getContextPath()%>/js/jquery.min.js"></script>
<script src="<%=request.getContextPath()%>/js/jquery.validate.js"></script>
<script src="<%=request.getContextPath()%>/js/jquery-1.8.3.js"></script>
<script src="<%=request.getContextPath()%>/js/jquery-ui.js"></script>
<script type="text/javascript">
// 全选
function selectAll() {
$(".selectType").prop("checked", true);
}
// 全部选
function selectAllNo() {
$(".selectType").prop("checked", false);
}
// 全选
function selectReverse() {
$(".selectType").click();
}
</script>
</html>
2、控制层代码
package com.bw.controller;
import com.bw.pojo.TbStore;
import com.bw.service.ProductService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/**
* @author 军哥
* @version 1.0
* @description: TODO
* @date 2023/11/3 13:14
*/
@Controller
@Slf4j
@RequestMapping(value = "/prod")
public class ProductController {
@Autowired
private ProductService productService;
/***
* @description 展示货物列表
* @return java.lang.String
* @author 军哥
* @date 2023/11/3 13:28
*/
@GetMapping(value = "/list")
public String list(Model model) {
List<TbStore> list = productService.getProductList();
model.addAttribute("list", list);
return "list";
}
/***
* @description 导出货物
* @return java.lang.String
* @author 军哥
* @date 2023/11/3 13:28
*/
@GetMapping(value = "/exportExel")
public String exportExel(Model model) {
Boolean ok = productService.getProductExport();
if(ok) {
model.addAttribute("msg", "导出成功");
}
else {
model.addAttribute("msg", "导出失败");
}
return "message";
}
/***
* @description 货物导入
* @return java.lang.String
* @author 军哥
* @date 2023/11/3 14:14
*/
@GetMapping(value = "/importExel")
public String importExel(Model model) {
String msg = productService.importExel();
model.addAttribute("msg", msg);
return "message";
}
/***
* @description 导出选择的记录
* @return java.lang.String
* @author 军哥
* @date 2023/11/3 15:59
*/
@PostMapping(value = "/exportSelect")
public String exportSelect(Model model, String ids) {
System.out.println(ids);
String msg = productService.exportSelect(ids);
model.addAttribute("msg", msg);
return "message";
}
/***
* @description 分组导出并统计和合并单元格
* @return java.lang.String
* @author 军哥
* @date 2023/11/3 16:25
*/
@GetMapping(value = "/exportMerge")
public String exportMerge(Model model) {
String msg = productService.getProductByType();
model.addAttribute("msg", msg);
return "message";
}
@PostMapping(value = "/getChartData")
public Object getChartData() {
List<TbStore> list = productService.getChatData();
HashMap<String, Object> map = new HashMap<>();
// 放入折线图和柱状图的数据
ArrayList<String> names = new ArrayList<>();
ArrayList<Integer> values = new ArrayList<>();
for (TbStore tbStore : list) {
names.add(tbStore.getProductName());
values.add(tbStore.getProductCount());
}
map.put("names", names);
map.put("values", values);
// 放入饼状图的数据
// TODO
return null;
}
}
3、实现层代码
package com.bw.service.impl;
import com.bw.mapper.ProductMapper;
import com.bw.pojo.TbProduct;
import com.bw.pojo.TbStore;
import com.bw.pojo.TbType;
import com.bw.service.ProductService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;
import java.util.UUID;
/**
* @author 军哥
* @version 1.0
* @description: 货物管理的实现类
* @date 2023/11/3 11:27
*/
@Service
public class ProductServiceImpl implements ProductService {
@Autowired
ProductMapper productMapper;
@Override
public List<TbStore> getProductList() {
List<TbStore> productList = productMapper.getProductList();
return productList;
}
@Override
public Boolean getProductExport() {
// 创建表格对象
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfWorkbook.createSheet("product");
// 创建标题
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell00 = row0.createCell(0);
cell00.setCellValue("货物列表");
HSSFCellStyle cellStyleTitle = hssfWorkbook.createCellStyle();
// 设置水平剧中
cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
// 设置背景颜色为红色
cellStyleTitle.setFillForegroundColor(IndexedColors.RED.index);
cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell00.setCellStyle(cellStyleTitle);
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
// 创建表头
HSSFRow row1 = sheet.createRow(1);
HSSFCell cell10 = row1.createCell(0);
cell10.setCellValue("订单号");
HSSFCell cell11 = row1.createCell(1);
cell11.setCellValue("货物编码");
HSSFCell cell12 = row1.createCell(2);
cell12.setCellValue("货物名称");
HSSFCell cell13 = row1.createCell(3);
cell13.setCellValue("类型名称");
HSSFCell cell14 = row1.createCell(4);
cell14.setCellValue("货物数量");
HSSFCell cell15 = row1.createCell(5);
cell15.setCellValue("入口时间");
// 填充数据
int rowNum = 2;
List<TbStore> productList = productMapper.getProductList();
for (TbStore tbStore : productList) {
HSSFRow row = sheet.createRow(rowNum);
HSSFCell cell20 = row.createCell(0);
cell20.setCellValue(tbStore.getStoreId());
HSSFCellStyle style20 = hssfWorkbook.createCellStyle();
style20.setFillForegroundColor(IndexedColors.RED.index);
style20.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell20.setCellStyle(style20);
HSSFCell cell21 = row.createCell(1);
cell21.setCellValue(tbStore.getProductId());
HSSFCellStyle style21 = hssfWorkbook.createCellStyle();
style21.setFillForegroundColor(IndexedColors.YELLOW.index);
style21.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell21.setCellStyle(style21);
HSSFCell cell22 = row.createCell(2);
cell22.setCellValue(tbStore.getProductName());
HSSFCellStyle style22 = hssfWorkbook.createCellStyle();
style22.setFillForegroundColor(IndexedColors.GREEN.index);
style22.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell22.setCellStyle(style22);
HSSFCell cell23 = row.createCell(3);
cell23.setCellValue(tbStore.getTypeName());
HSSFCell cell24 = row.createCell(4);
cell24.setCellValue(tbStore.getProductCount());
HSSFCell cell25 = row.createCell(5);
cell25.setCellValue(tbStore.getCreateTime().toString());
rowNum ++;
}
try {
// 文件保存
String s = UUID.randomUUID().toString();
FileOutputStream fileOutputStream = new FileOutputStream("D:\\var\\docs\\"+ s + ".xls");
hssfWorkbook.write(fileOutputStream);
fileOutputStream.close();
hssfWorkbook.close();
} catch (IOException e) {
e.printStackTrace();
return false;
}
return true;
}
@Override
public String importExel() {
try {
FileInputStream fileInputStream = new FileInputStream("D:\\var\\docs\\货物导入表.xls");
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
HSSFSheet sheet = hssfWorkbook.getSheet("product");
if(sheet == null) {
return "模板格式不正确";
}
int lastRowNum = sheet.getLastRowNum();
// 读出表头
// 读出数据
for (int i = 1; i <=lastRowNum ; i++) {
HSSFRow row = sheet.getRow(i);
String typeName = row.getCell(0).getStringCellValue();
String productName = row.getCell(1).getStringCellValue();
Integer productCount = (int)(row.getCell(2).getNumericCellValue());
// 准备写入数据库
TbStore tbStore = new TbStore();
tbStore.setProductName(productName);
tbStore.setTypeName(typeName);
tbStore.setProductCount(productCount);
tbStore.setCreateTime(new Date());
// 根据类型名字获取类型id
TbType tbType = productMapper.getTypeIdByName(typeName);
if(tbType != null) {
tbStore.setTypeId(tbType.getTypeId());
}
// 根据产品的名字获取产品的ID
TbProduct tbProduct = productMapper.getProductIdByName(productName);
if(tbProduct != null) {
tbStore.setProductId(tbProduct.getProductId());
}
// 存储到入库表中
productMapper.addStore(tbStore);
}
hssfWorkbook.close();
fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
return "货物表导入出现错误";
}
return "货物表导入成功";
}
@Override
public String exportSelect(String ids) {
List<TbStore> productList = productMapper.exportSelect(ids);
// 导出到文件
// 创建表格对象
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfWorkbook.createSheet("product");
// 创建标题
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell00 = row0.createCell(0);
cell00.setCellValue("货物列表");
HSSFCellStyle cellStyleTitle = hssfWorkbook.createCellStyle();
// 设置水平剧中
cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
// 设置背景颜色为红色
cellStyleTitle.setFillForegroundColor(IndexedColors.RED.index);
cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell00.setCellStyle(cellStyleTitle);
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
// 创建表头
HSSFRow row1 = sheet.createRow(1);
HSSFCell cell10 = row1.createCell(0);
cell10.setCellValue("订单号");
HSSFCell cell11 = row1.createCell(1);
cell11.setCellValue("货物编码");
HSSFCell cell12 = row1.createCell(2);
cell12.setCellValue("货物名称");
HSSFCell cell13 = row1.createCell(3);
cell13.setCellValue("类型名称");
HSSFCell cell14 = row1.createCell(4);
cell14.setCellValue("货物数量");
HSSFCell cell15 = row1.createCell(5);
cell15.setCellValue("入口时间");
// 填充数据
int rowNum = 2;
// List<TbStore> productList = productMapper.getProductList();
for (TbStore tbStore : productList) {
HSSFRow row = sheet.createRow(rowNum);
HSSFCell cell20 = row.createCell(0);
cell20.setCellValue(tbStore.getStoreId());
HSSFCellStyle style20 = hssfWorkbook.createCellStyle();
style20.setFillForegroundColor(IndexedColors.RED.index);
style20.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell20.setCellStyle(style20);
HSSFCell cell21 = row.createCell(1);
cell21.setCellValue(tbStore.getProductId());
HSSFCellStyle style21 = hssfWorkbook.createCellStyle();
style21.setFillForegroundColor(IndexedColors.YELLOW.index);
style21.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell21.setCellStyle(style21);
HSSFCell cell22 = row.createCell(2);
cell22.setCellValue(tbStore.getProductName());
HSSFCellStyle style22 = hssfWorkbook.createCellStyle();
style22.setFillForegroundColor(IndexedColors.GREEN.index);
style22.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell22.setCellStyle(style22);
HSSFCell cell23 = row.createCell(3);
cell23.setCellValue(tbStore.getTypeName());
HSSFCell cell24 = row.createCell(4);
cell24.setCellValue(tbStore.getProductCount());
HSSFCell cell25 = row.createCell(5);
cell25.setCellValue(tbStore.getCreateTime().toString());
rowNum ++;
}
try {
// 文件保存
String s = UUID.randomUUID().toString();
FileOutputStream fileOutputStream = new FileOutputStream("D:\\var\\docs\\"+ s + ".xls");
hssfWorkbook.write(fileOutputStream);
fileOutputStream.close();
hssfWorkbook.close();
} catch (IOException e) {
e.printStackTrace();
return "选中导出失败了";
}
return "选中导出成功了";
}
@Override
public String getProductByType() {
//-- 1、获取合并后的数据
List<TbStore> list = productMapper.getProductByType();
//-- 2、创建表格对象
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfWorkbook.createSheet("汇总");
// 创建标题
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell00 = row0.createCell(0);
cell00.setCellValue("货物汇总列表");
HSSFCellStyle cellStyleTitle = hssfWorkbook.createCellStyle();
// 设置水平剧中
cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
// 设置背景颜色为红色
cellStyleTitle.setFillForegroundColor(IndexedColors.RED.index);
cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell00.setCellStyle(cellStyleTitle);
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
//-- 3、写表头
HSSFRow row1 = sheet.createRow(1);
HSSFCell cell10 = row1.createCell(0);
cell10.setCellValue("货物名称");
HSSFCell cell11 = row1.createCell(1);
cell11.setCellValue("类型名称");
HSSFCell cell12 = row1.createCell(2);
cell12.setCellValue("货物数量");
//-- 4、写入数据
int rowNum = 2;
String typeName = "";
int column = 1; // 合并的第几列
int start = 2; // 合并的从第几行开始
int end = 2; // 合并到第几行
for (TbStore tbStore : list) {
HSSFRow row = sheet.createRow(rowNum);
HSSFCell cell20 = row.createCell(0);
cell20.setCellValue(tbStore.getProductName());
HSSFCellStyle style20 = hssfWorkbook.createCellStyle();
style20.setFillForegroundColor(IndexedColors.RED.index);
style20.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell20.setCellStyle(style20);
HSSFCell cell21 = row.createCell(1);
cell21.setCellValue(tbStore.getTypeName());
if(typeName.equals("")) {
typeName = tbStore.getTypeName();
}
else if(typeName.equals(tbStore.getTypeName())) {
end ++;
}
else {
// 要进行合并了
if(end > start) {
sheet.addMergedRegion(new CellRangeAddress(start, end, 1, 1));
}
// 对表格进行了合并
typeName = tbStore.getTypeName();
end ++;
start = end;
}
HSSFCellStyle style21 = hssfWorkbook.createCellStyle();
style21.setFillForegroundColor(IndexedColors.YELLOW.index);
style21.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell21.setCellStyle(style21);
HSSFCell cell22 = row.createCell(2);
cell22.setCellValue(tbStore.getProductCount());
HSSFCellStyle style22 = hssfWorkbook.createCellStyle();
style22.setFillForegroundColor(IndexedColors.GREEN.index);
style22.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell22.setCellStyle(style22);
rowNum ++;
}
//-- 4、存储到磁盘
try {
// 文件保存
String s = UUID.randomUUID().toString();
FileOutputStream fileOutputStream = new FileOutputStream("D:\\var\\docs\\"+ s + ".xls");
hssfWorkbook.write(fileOutputStream);
fileOutputStream.close();
hssfWorkbook.close();
} catch (IOException e) {
e.printStackTrace();
return "合并统计导出失败了";
}
return "合并统计导出成功了";
}
@Override
public List<TbStore> getChatData() {
List<TbStore> list = productMapper.getProductByType();
return list;
}
}
4、mapper的xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bw.mapper.ProductMapper">
<select id="getProductList" resultType="com.bw.pojo.TbStore">
select a.*,b.product_name,c.type_name from tb_store a
left join tb_product b on a.product_id=b.product_id
left join tb_type c on a.type_id=c.type_id
</select>
<select id="getTypeIdByName" resultType="com.bw.pojo.TbType">
select * from tb_type where type_name=#{typeName} limit 1
</select>
<select id="getProductIdByName" resultType="com.bw.pojo.TbProduct">
select * from tb_product where product_name=#{productName} limit 1
</select>
<insert id="addStore" useGeneratedKeys="true" keyColumn="store_id" keyProperty="storeId" parameterType="com.bw.pojo.TbStore">
insert into tb_store(type_id,product_id,product_count,create_time)
values (#{typeId},#{productId},#{productCount},#{createTime})
</insert>
<select id="exportSelect" resultType="com.bw.pojo.TbStore" parameterType="java.lang.String">
select a.*,b.product_name,c.type_name from tb_store a
left join tb_product b on a.product_id=b.product_id
left join tb_type c on a.type_id=c.type_id
where a.store_id in (${ids})
</select>
<select id="getProductByType" resultType="com.bw.pojo.TbStore">
select product_sum.product_name,product_sum.type_name,sum(product_sum.product_count) product_count from
(
select a.*,b.product_name,c.type_name from tb_store a
left join tb_product b on a.product_id=b.product_id
left join tb_type c on a.type_id=c.type_id
) as product_sum
group by product_sum.product_name,product_sum.type_name
order by product_sum.type_name
</select>
</mapper>