easy-excel 使用源代码

 easy-excel

 

package com.china.me.service;

 

 

import java.io.IOException;

import java.io.OutputStream;

import java.net.URLEncoder;

import java.util.List;

 

 

import javax.servlet.http.HttpServletResponse;

 

 

import org.apache.poi.ss.usermodel.HorizontalAlignment;

import org.springframework.stereotype.Component;

 

 

import com.alibaba.excel.EasyExcel;

import com.alibaba.excel.ExcelWriter;

import com.alibaba.excel.support.ExcelTypeEnum;

import com.alibaba.excel.write.metadata.style.WriteCellStyle;

import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;

 

 

@Component

public class ExportExcelService {

 

 

    public ExcelWriter  writeExcel(HttpServletResponse response, String fileName) throws Exception {

        //表头策略

        WriteCellStyle headWriteCellStyle = new WriteCellStyle();

        // 居中

        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        // 内容策略

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

        // 居左

        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);

        //注册策略

        HorizontalCellStyleStrategy horizontalCellStyleStrategy =

                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        // 生成excel

       return EasyExcel.write(getOutputStream(fileName, response)).excelType(ExcelTypeEnum.XLSX).

               registerWriteHandler(horizontalCellStyleStrategy).build();

              

                // 自动适应列宽 存在bug

                // .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())

                //.doWrite(data);

        

 

 

    }

 

 

    private OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {

        try {

            fileName = URLEncoder.encode(fileName, "UTF-8");

            response.setContentType("application/vnd.ms-excel");

            response.setCharacterEncoding("utf8");

            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");

            response.setHeader("Pragma", "public");

            response.setHeader("Cache-Control", "no-store");

            response.addHeader("Cache-Control", "max-age=0");

            return response.getOutputStream();

        } catch (IOException e) {

            throw new Exception("下载失败!", e);

        }

    }

 

 

}

 

 

 

package com.china.me.service;

 

 

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.time.LocalTime;

import java.time.format.DateTimeFormatter;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.List;

 

 

import javax.servlet.http.HttpServletResponse;

 

 

import org.apache.commons.lang3.StringUtils;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

 

 

import com.alibaba.excel.EasyExcel;

import com.alibaba.excel.ExcelWriter;

import com.alibaba.excel.write.metadata.WriteSheet;

import com.china.me.entity.Order;

import com.china.me.entity.OrderItem;

import com.china.me.entity.UserCertificate;

import com.china.me.request.QueryOrderPagesRequestData;

import com.china.me.response.QueryOrdePagesResponseData;

import com.china.me.response.ResponseData;

import com.china.me.util.Cloneutil;

import com.china.me.util.JsonConvertor;

 

 

@Service

public class OrderQueryPagesService {

 

 

    @Autowired

    ExportExcelService exportExcelService;

 

 

    // �����ƾ֤��Ϣ�滻������������������

    // String urlPreFix = "https://company.pospal.cn:18443";

    private static final String urlPreFix = "https://area39-win.pospal.cn:443";

    private static final String appId = "DAE13356750316685F41E646AA47B658";

    private static final String appKey = "722207078801623536";

    private static final String urlString = urlPreFix + "/pospal-api2/openapi/v1/orderOpenApi/queryOrderPages";

    private static final String fileName = "订单列表-";

    private static final String sheetName = "订单列表";

 

 

    // ��������ϵͳ���Žӿڵķ���ƾ֤

    private static final UserCertificate certificate = new UserCertificate(appId, appKey);

 

 

    public void execut(String dayNum, HttpServletResponse response) throws Exception {

        // ���ô����������java���͵�����Ҫ�ֶ����ô���,fiddler����ץ������

        // HttpUtil.setCustomProxy("127.0.0.1", 8888);

        String startTime = null;

        String endTime = null;

        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        DateTimeFormatter df1 = DateTimeFormatter.ofPattern("yyyy-MM-dd HH-mm-ss");

        String nowDateTime = LocalDateTime.now().format(df1);

        if (StringUtils.isBlank(dayNum)) {

            LocalDateTime today_start = LocalDateTime.of(LocalDate.now(), LocalTime.MIN);

            LocalDateTime today_end = LocalDateTime.of(LocalDate.now(), LocalTime.MAX);

            startTime = today_start.format(df);

            endTime = today_end.format(df);

        } else {

            try {

                Integer.parseInt(dayNum);

            } catch (Exception e) {

                // TODO Auto-generated catch block

                throw new Exception("参数不为正整数!");

            }

            LocalDateTime today_start = LocalDateTime.of(LocalDate.now().plusDays(-Integer.parseInt(dayNum)),

                    LocalTime.MIN);

            LocalDateTime today_end = LocalDateTime.of(LocalDate.now().plusDays(-Integer.parseInt(dayNum)),

                    LocalTime.MAX);

            startTime = today_start.format(df);

            endTime = today_end.format(df);

 

 

        }

        // System.out.println(startTime+""+endTime);

        //System.out.println(nowDateTime);

        queryRemoteData(startTime, endTime, response, nowDateTime);

        /*

         * new ResponseDataProcessor(){

         *

         * @Override public void process(QueryOrdePagesResponseData responseData) { int

         * wantQuerySize = responseData.getData().getPageSize(); int realQuerySize =

         * responseData.getData().getResult().size();

         * System.out.println("wantQuerySize="+wantQuerySize+",realQuerySize="+

         * realQuerySize); //System.out.println(JsonConvertor.toJson(responseData));

         *

         * exportExcelService.writeExcel(response, data, startTime, sheetName, model); }

         * }

         */

 

 

    }

 

 

    public void queryRemoteData(String startTime, String endTime, HttpServletResponse response, String nowDateTime)

            throws Exception {

        boolean queryNextPage = true;

        QueryOrderPagesRequestData requestData = new QueryOrderPagesRequestData(startTime, endTime);

        // 记录sheet号

        ExcelWriter excelWriter = exportExcelService.writeExcel(response, fileName + nowDateTime);

        WriteSheet zhuWriteSheet = EasyExcel.writerSheet(1, sheetName).head(Order.class).build();

        List<Order> orderListdaishenhe=new ArrayList();

        while (queryNextPage) {

            ResponseData sendRequestData = PospalApiSerivce.sendRequestData(urlString, requestData, certificate);

            String responseContent = sendRequestData.getResponseContent();

            QueryOrdePagesResponseData orderResponseData = JsonConvertor.fromJson(responseContent,

                    QueryOrdePagesResponseData.class);

            if (orderResponseData.isSuccess() && orderResponseData.getData() != null) {

                List<Order> orderList = orderResponseData.getData().getResult();

                for (Order order : orderList) {

                    //if(order.getState()==1) {

                        List<OrderItem>    orderItems =order.getItems();

                        for(OrderItem orderItem:orderItems) {

                            String productAndNum=orderItem.getProductName()+","+orderItem.getProductQuantity();

                            for(int i=0;i<orderItem.getProductQuantity();i++) {

                                Order tempOrder=(Order)Cloneutil.clone(order);

                                tempOrder.setProductAndNum(productAndNum);

                                orderListdaishenhe.add(tempOrder);

                            }

                            

                        }

                        

                    //}

                }

 

 

            } else {

                throw new Exception(Arrays.toString(orderResponseData.getMessages()));

            }

 

 

            int wantQuerySize = orderResponseData.getData().getPageSize();

            int realQuerySize = orderResponseData.getData().getResult().size();

            queryNextPage = !(wantQuerySize > realQuerySize);

            requestData.setPostBackParameter(orderResponseData.getData().getPostBackParameter());

 

 

        }

        excelWriter.write(orderListdaishenhe, zhuWriteSheet);

        excelWriter.finish();

    }

 

 

}

 

 

 

 

 

<?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.china.me</groupId>

     <artifactId>orderexcel</artifactId>

     <version>0.0.1-SNAPSHOT</version>

     <packaging>jar</packaging>

     <properties>

          <java.version>1.8</java.version>

     </properties>

     <dependencies>

          <dependency>

              <groupId>org.springframework.boot</groupId>

              <artifactId>spring-boot-starter-web</artifactId>

          </dependency>

          <dependency>

              <groupId>com.alibaba</groupId>

              <artifactId>fastjson</artifactId>

              <version>1.2.61</version>

          </dependency>

          <dependency>

              <groupId>org.apache.httpcomponents</groupId>

              <artifactId>httpclient</artifactId>

          </dependency>

          <dependency>

              <groupId>org.apache.commons</groupId>

              <artifactId>commons-lang3</artifactId>

              <version>3.9</version>

          </dependency>

          <dependency>

              <groupId>com.alibaba</groupId>

              <artifactId>easyexcel</artifactId>

              <version>2.1.4</version>

          </dependency>

          <dependency>

              <groupId>com.google.code.gson</groupId>

              <artifactId>gson</artifactId>

              <version>2.8.5</version>

          </dependency>

     </dependencies>

     <build>

          <finalName>orderexcel</finalName>

          <plugins>

              <plugin>

                   <groupId>org.springframework.boot</groupId>

                   <artifactId>spring-boot-maven-plugin</artifactId>

              </plugin>

          </plugins>

     </build>

</project>

 

 

写入多个sheel时不用每次都 finish .最终执行finish操作即可

遇到的问题:  以 sheel 2,,3,4,5写入后,又写入sheel 1 ,最后写入的sheel 和sheel3合并在了一起,修改最后写入的sheel为999导出正常