Excel拆分&循环调接口导入员工

最新推荐文章于 2023-02-21 17:07:32 发布

勇敢的兵

最新推荐文章于 2023-02-21 17:07:32 发布

阅读量369

点赞数

分类专栏： 03接口自动化测试

本文链接：https://blog.csdn.net/qq_37251897/article/details/116334361

版权

03接口自动化测试专栏收录该内容

7 篇文章 2 订阅

订阅专栏

文章目录

界面导入1次最多支持100条，现在有1w条左右员工需要导入。怎么办？
思路：
1）将excel文件拆分成100个文件，每个文档里面100条数据
2）通过接口导入，循环100次

excel拆分脚本

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelTest {
    private static Logger logger= Logger.getLogger(ExcelTest.class);

    /**
     *
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        int bizType = 1;//1：导入入职；2：导入修改
        File file = new File("C:\\Users\\yangzc\\Downloads\\员工信息2021-05-01.xlsx");
        //Excel
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
        //获得指定的表
        XSSFSheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        int size = 100;
        int n;
        if(bizType == 1){
            n = lastRowNum/size;
        }else{
            n = (lastRowNum-1)/size;
        }
        if(bizType == 1 && n*size < lastRowNum){
            n += 1;
        }
        if(bizType == 2 && n*size < lastRowNum-1){
            n += 1;
        }

        for(int i=1;i<=n;i++){
            File file2 = new File("C:\\Users\\yangzc\\Downloads\\新建 XLSX 工作表" + i + ".xlsx");
            if(!file2.exists()){
                file2.createNewFile();
            }
            FileOutputStream out = new FileOutputStream(file2);
            workbook = new XSSFWorkbook(new FileInputStream(file));
            sheet = workbook.getSheetAt(0);
            lastRowNum = sheet.getLastRowNum();
            if(bizType == 1 && i < n){
                sheet.shiftRows(lastRowNum+1,2*lastRowNum - size*i,size*i - lastRowNum);
            }
            if(bizType == 1 && i >= 2){
                sheet.shiftRows(size*(i-1)+1,(i-1)*2*size,size*(1-i));
            }
            if(bizType == 2 && i < n){
                sheet.shiftRows(lastRowNum+1,2*lastRowNum - size*i - 1,size*i - lastRowNum + 1);
            }
            if(bizType == 2 && i >= 2){
                sheet.shiftRows(size*(i-1)+2,(i-1)*2*size + 1,size*(1-i));
            }
            lastRowNum = sheet.getLastRowNum();
            System.out.println("=========:"+lastRowNum);
            //获得数据总行数
            int rowCount = sheet.getLastRowNum();
            logger.info("found excel rows count: " + rowCount);
            workbook.write(out);
        }
    }

}

在这里插入图片描述

循环调接口导入文件

import java.io.*;
import org.apache.http.*;
import org.apache.http.client.methods.*;
import org.apache.http.entity.*;
import org.apache.http.entity.mime.*;
import org.apache.http.impl.client.*;
import org.apache.http.util.*;
import org.apache.log4j.Logger;
import org.testng.*;
import org.testng.annotations.*;
import com.alibaba.fastjson.*;
/**
 *
 * 导入员工
 *
 * @author yangzc
 *
 */
public class UploadFileTest {
    private CloseableHttpClient client;
    private HttpPost request;
    private static Logger logger= Logger.getLogger(UploadFileTest.class);

    @DataProvider(parallel=false)
    public Object[][] data(){
        String bizType = "1";//1：导入入职；2：导入修改
        int n = 1;  //循环次数
        int idx;
        File[] files = new File[n];
        Object[][] arr = new Object[n][];
        for(int i=0;i<files.length;i++){
            idx = i+1;
            files[i] = new File("C:\\Users\\yangzc\\Downloads\\新建 XLSX 工作表" + idx + ".xlsx");
            //files[i] = new File("C:\\Users\\A11-19\\Downloads\\员工信息" + idx + ".xlsx");
            arr[i] = new Object[]{"导入第"+idx+"个excel文件",bizType,files[i],0};
        }
        return arr;
    }
    @BeforeTest
    public void begin(){
        //创建一个可关闭的客户端工具
        client = HttpClients.createDefault();
        //创建一个Post请求
        request = new HttpPost("https://xxxxxxx.cn/pro/api/xxx/xlc-xxx-x/xxx/xxx/entryUser/excelImport");
    }
    @AfterTest
    public void end() throws Exception{
        //关闭客户端
        client.close();
    }
    @Test(dataProvider="data")
    public void f(String desc,String bizType,File file,int expected) throws Exception{
        //构造发送的数据
        HttpEntity send_data = MultipartEntityBuilder.create()
                .setMode(HttpMultipartMode.RFC6532)
                .addTextBody("bizType", bizType)
                .addBinaryBody("file", file, ContentType.DEFAULT_BINARY, file.getName())
                .build();
        request.setEntity(send_data); //将send_data设置为请求的正文
        request.setHeader("Cookie","t=19697776cxxxxxxxxx192xxxxxcaa639762; sso_sys_token=19697776cxxxxxxxxx192xxxxxcaa639762");
        request.setHeader("sso_sys_token","19697776cxxxxxxxxx192xxxxxcaa639762");
        logger.info("*********************************"+desc+"*********************************");
        HttpResponse response = client.execute(request); //execute方法会返回接口发送回来的数据

        /*
        for(Header h:response.getAllHeaders()){  //打印响应头
                System.out.println(h.toString());
        }
        */

        //打印响应正文
        String str = EntityUtils.toString(response.getEntity());
        //System.out.println(str);
        if(bizType == "1"){
            logger.info("入职导入结果明细："+str);
        }else{
            logger.info("修改导入结果明细："+str);
        }
        logger.info("***********************************************************************");

        JSONObject obj = (JSONObject)JSON.parse(str);
        Assert.assertEquals(obj.get("code"), expected, desc);

    }
}

在这里插入图片描述

依赖配置

  <dependencies>
    <!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind -->
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.9.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.httpcomponents/httpclient -->
    <dependency>
      <groupId>org.apache.httpcomponents</groupId>
      <artifactId>httpclient</artifactId>
      <version>4.5.11</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.62</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.testng</groupId>
      <artifactId>testng</artifactId>
      <version>7.0.0-beta1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.httpcomponents</groupId>
      <artifactId>httpmime</artifactId>
      <version>4.5.12</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

log4j配置


#you cannot specify every priority with different file for log4j
log4j.rootLogger=debug,stdout,info,debug,warn,error 

#console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender 
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout 
log4j.appender.stdout.layout.ConversionPattern= [%d{yyyy-MM-dd HH:mm:ss a}]:%p %l%m%n
#info log
log4j.logger.info=info
log4j.appender.info=org.apache.log4j.DailyRollingFileAppender 
log4j.appender.info.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.info.File=d:/yangzc/log/info.log
log4j.appender.info.Append=true
log4j.appender.info.Threshold=INFO
log4j.appender.info.layout=org.apache.log4j.PatternLayout 
log4j.appender.info.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#debug log
log4j.logger.debug=debug
log4j.appender.debug=org.apache.log4j.DailyRollingFileAppender 
log4j.appender.debug.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.debug.File=d:/yangzc/log/debug.log
log4j.appender.debug.Append=true
log4j.appender.debug.Threshold=DEBUG
log4j.appender.debug.layout=org.apache.log4j.PatternLayout 
log4j.appender.debug.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#warn log
log4j.logger.warn=warn
log4j.appender.warn=org.apache.log4j.DailyRollingFileAppender 
log4j.appender.warn.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.warn.File=d:/yangzc/log/warn.log
log4j.appender.warn.Append=true
log4j.appender.warn.Threshold=WARN
log4j.appender.warn.layout=org.apache.log4j.PatternLayout 
log4j.appender.warn.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#error
log4j.logger.error=error
log4j.appender.error = org.apache.log4j.DailyRollingFileAppender
log4j.appender.error.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.error.File = d:/yangzc/log/error.log 
log4j.appender.error.Append = true
log4j.appender.error.Threshold = ERROR 
log4j.appender.error.layout = org.apache.log4j.PatternLayout
log4j.appender.error.layout.ConversionPattern = %d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n

参考资料：
[01] java poi 操作Excel 删除行内容和直接删除行
 [02] log4j-1.2.17的详细配置

微信扫一扫关注公众号

点击链接加入群聊

https://jq.qq.com/?_wv=1027&k=5eVEhfN
软件测试学习交流QQ群号：511619105

勇敢的兵

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Excel拆分&循环调接口导入员工

界面导入1次最多支持100条，现在有1w条左右员工需要导入。怎么办？思路：1）将excel文件拆分成100个文件，每个文档里面100条数据2）通过接口导入，循环100次excel拆分脚本import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import org.apache.log4j.Logger;import org.apache.poi.xssf.usermodel.X.
复制链接

扫一扫

专栏目录