博主
258
258
258
258
标签
Vue3 springboot elasticsearch linux jdk centos docker vmware kibana
专辑

第二十四节 JQuery实现复选框的全选与反选

亮子 2023-11-14 01:02:25 510 0 0 0

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>