界面导入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