后端导出excel文件

guoyuji

2024-04-16 6d6e2c77e34e7dbae4fba340c423eaafc2b9c231

后端导出excel文件

 north-glass-erp/northglass-erp/src/views/sd/order/OrderReport.vue

@@ -1,7 +1,7 @@
<script setup>
import BasicTable from "@/components/BasicTable.vue"
import { ref} from "vue"

import request from "@/utils/request"
const childrenData = ref({
  columns:[
    {type:'expand',fixed:"left",width: 80,slots: { content:'content' }},
@@ -48,6 +48,23 @@
  footList:['quantity','grossArea','computeGrossArea','perimeter','price','grossAmount']
})

request.post('/order/exportOrderReport',null,{responseType :'blob'}).then(res => {
  const blob = new Blob([res])
  // console.log(blob)
  // if ('download' in document.createElement('a')) { // 非IE下载
  //   const elink = document.createElement('a')
  //   elink.download = 'a.xlsx'
  //   elink.style.display = 'none'
  //   elink.href = URL.createObjectURL(blob)
  //   document.body.appendChild(elink)
  //   elink.click()
  //   URL.revokeObjectURL(elink.href) // 释放URL 对象
  //   document.body.removeChild(elink)
  // } else { // IE10+下载
  //   navigator.msSaveBlob(blob, fileName)
  // }

  })
</script>

<template>

 north-glass-erp/pom.xml

@@ -131,6 +131,12 @@
            <version>4.3.5.Final</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.1</version>
        </dependency>


    </dependencies>

@@ -152,31 +158,31 @@
    </build>


    <!--<repositories>
        <repository>
            <id>nexus-aliyun</id>
            <name>nexus-aliyun</name>
            <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
            <releases>
                <enabled>true</enabled>
            </releases>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
    </repositories>
<!--    <repositories>-->
<!--        <repository>-->
<!--            <id>nexus-aliyun</id>-->
<!--            <name>nexus-aliyun</name>-->
<!--            <url>http://maven.aliyun.com/nexus/content/groups/public/</url>-->
<!--            <releases>-->
<!--                <enabled>true</enabled>-->
<!--            </releases>-->
<!--            <snapshots>-->
<!--                <enabled>false</enabled>-->
<!--            </snapshots>-->
<!--        </repository>-->
<!--    </repositories>-->

    <pluginRepositories>
        <pluginRepository>
            <id>public</id>
            <name>aliyun nexus</name>
            <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
            <releases>
                <enabled>true</enabled>
            </releases>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </pluginRepository>
    </pluginRepositories>-->
<!--    <pluginRepositories>-->
<!--        <pluginRepository>-->
<!--            <id>public</id>-->
<!--            <name>aliyun nexus</name>-->
<!--            <url>http://maven.aliyun.com/nexus/content/groups/public/</url>-->
<!--            <releases>-->
<!--                <enabled>true</enabled>-->
<!--            </releases>-->
<!--            <snapshots>-->
<!--                <enabled>false</enabled>-->
<!--            </snapshots>-->
<!--        </pluginRepository>-->
<!--    </pluginRepositories>-->
</project>

 north-glass-erp/src/main/java/com/example/erp/controller/sd/OrderController.java

@@ -7,11 +7,14 @@
import com.example.erp.entity.sd.OrderGlassDetail;
import com.example.erp.exception.ServiceException;
import com.example.erp.service.sd.OrderService;
import com.example.erp.tools.DownExcel;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.time.LocalDate;
import java.util.List;
import java.util.Map;
@@ -90,4 +93,9 @@
        return  Result.seccess(orderService.getOrderReport(pageNum,pageSize,selectDate,orderDetail));

    }
    @ApiOperation("订单报表导出")
    @PostMapping("/exportOrderReport")
    public void exportOrderReport(HttpServletResponse response) throws IOException, IllegalAccessException, InstantiationException {
        DownExcel.download(response,Order.class, orderService.exportOrderReport(),"orderReport");
    }
}

 north-glass-erp/src/main/java/com/example/erp/entity/sd/Order.java

@@ -1,5 +1,6 @@
package com.example.erp.entity.sd;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
@@ -13,9 +14,11 @@
@TableName("sd.`order`")
public class Order {
    @TableId(type = IdType.AUTO)

    @ExcelProperty("id")
    private Long id;
    @ExcelProperty("订单编号")
    private String orderId;
    @ExcelProperty("项目名称")
    private String project;
    private Integer customerId;
    private String customerName;

 north-glass-erp/src/main/java/com/example/erp/mapper/sd/OrderDetailMapper.java

@@ -18,4 +18,6 @@
    List<OrderDetail> getOrderReport(Integer offset, Integer pageSize, String startDate, String endDate, OrderDetail orderDetail);

    Map<String,Integer> getOrderReportTotal(Integer offset, Integer pageSize, String startDate, String endDate, OrderDetail orderDetail);

    List<Order> exportOrderReport();
}

 north-glass-erp/src/main/java/com/example/erp/service/sd/OrderService.java

@@ -271,4 +271,8 @@
//        map.put("total",orderMapper.getPageTotal(offset, pageSize, startDate, endDate, orderDetail));
        return map;
    }

    public List<Order> exportOrderReport() {
        return orderDetailMapper.exportOrderReport();
    }
}

 north-glass-erp/src/main/java/com/example/erp/tools/DownExcel.java

New file
@@ -0,0 +1,17 @@
package com.example.erp.tools;

import com.alibaba.excel.EasyExcel;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

//exccel下载
public class DownExcel {
    public static void download(HttpServletResponse response, Class t, List list,String reportName) throws IOException, IllegalAccessException,InstantiationException {
        response.setContentType("application/vnd.ms-excel");// 设置文本内省
        response.setCharacterEncoding("utf-8");// 设置字符编码
        response.setHeader("Content-disposition", "attachment;filename="+reportName+".xlsx"); // 设置响应头
        EasyExcel.write(response.getOutputStream(), t).sheet("模板").doWrite(list); //用io流来写入数据
    }
}

 north-glass-erp/src/main/resources/mapper/sd/OrderDetail.xml

@@ -391,4 +391,22 @@
        order by a.id desc
    </select>

    <select id="exportOrderReport">
        SELECT
            *,
            a.create_time as createTime,
            d.type_name as levelOne,
            e.type_name as levelTwo
        from order_detail as a
         left join sd.`order` as b
                   on b.order_id = a.order_id
         left join sd.product as c
                   on c.id = a.product_id
         left join sd.basic_glass_type as d
                   on d.type_id = c.type_id
         left join sd.basic_glass_type as e
                   on e.type_id = d.belong

    </select>

</mapper>

 north-glass-erp/target/classes/mapper/sd/OrderDetail.xml

@@ -391,4 +391,22 @@
        order by a.id desc
    </select>

    <select id="exportOrderReport">
        SELECT
            *,
            a.create_time as createTime,
            d.type_name as levelOne,
            e.type_name as levelTwo
        from order_detail as a
         left join sd.`order` as b
                   on b.order_id = a.order_id
         left join sd.product as c
                   on c.id = a.product_id
         left join sd.basic_glass_type as d
                   on d.type_id = c.type_id
         left join sd.basic_glass_type as e
                   on e.type_id = d.belong

    </select>

</mapper>