//此为实现类方法
//导出符合查询条件的excel
public void exportExcel(HttpServletResponse response, String syb, String model, String country, String tag, String deadline, String life, String imei) throws ParseException, IOException {
//处理参数
Date startTime = null;
Date endTime = null;
Integer lifeStart = null;
Integer lifeEnd = null;
String modelArr[] = null;
String countryArr[] = null;
Integer start = 0;
//sql每次查询5000条 不然一次全查出来 超过百万会内存溢出
Integer size = 5000;
if (model != null && !model.equals("")) {
modelArr = model.split(",");
}
if (country != null && !country.equals("")) {
countryArr = country.split(",");
}
if (deadline != null && !deadline.equals("")) {
String timeArr[] = deadline.split(",");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
startTime = simpleDateFormat.parse(simpleDateFormat.format(new Date(Long.parseLong(timeArr[0]))));
endTime = simpleDateFormat.parse(simpleDateFormat.format(new Date(Long.parseLong(timeArr[1]))));
}
if (life != null && !life.equals("")) {
String lifeArr[] = life.split(",");
lifeStart = Integer.parseInt(lifeArr[0]);
lifeEnd = Integer.parseInt(lifeArr[1]);
}
List<PhoneInfo> phoneInfoList = new ArrayList<>();
List<CountryInfo> countryInfos = countryMapper.getAllCountry();
List<AreaInfo> areaInfos = areaMapper.getAllArea();
//总页数计算方式
//int totalPageNum = (totalRecord + pageSize - 1) / pageSize;
try {
//第一步,创建一个webbook,对应一个Excel文件
SXSSFWorkbook wb = new SXSSFWorkbook(10000);
//第二步,在webbook中添加一个sheet,对应Excel文件中的 sheet
SXSSFSheet sheet1 = wb.createSheet("1");
//cell标题行
SXSSFRow row = sheet1.createRow(0);
row.createCell(0).setCellValue("imei");
row.createCell(1).setCellValue("机型");
row.createCell(2).setCellValue("区域");
row.createCell(3).setCellValue("国家");
row.createCell(4).setCellValue("Province");
row.createCell(5).setCellValue("City");
row.createCell(6).setCellValue("事业部");
row.createCell(7).setCellValue("公司");
row.createCell(8).setCellValue("se版本");
row.createCell(9).setCellValue("安卓版本");
row.createCell(10).setCellValue("PhoneType");
row.createCell(11).setCellValue("Channel_no");
row.createCell(12).setCellValue("机龄");
row.createCell(13).setCellValue("标签");
row.createCell(14).setCellValue("首次登录时间");
row.createCell(15).setCellValue("最后登录时间");
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//用于计算分页limit 起始index
int c = 0;
boolean b = true;
//excel的行数
int rowNum = 1;
//记录cell行数
int num = 0;
while (b) {
start = c * size;
phoneInfoList = phoneInfoMapper.exportExcel(syb, countryArr, modelArr, tag, imei, startTime, endTime, lifeStart, lifeEnd, start, size);
if (phoneInfoList.size() != 0) {
for (PhoneInfo phoneInfo: phoneInfoList) {
for (AreaInfo areaInfo : areaInfos) {
if (phoneInfo.getArea() != null && !phoneInfo.getArea().equals("") && !phoneInfo.getArea().equals("null")) {
if (Integer.parseInt(phoneInfo.getArea()) == areaInfo.getId()) {
phoneInfo.setArea(areaInfo.getArea());
break;
}
}
}
row = sheet1.createRow(rowNum - num);
row.createCell(0).setCellValue(phoneInfo.getImei());
row.createCell(1).setCellValue(phoneInfo.getModel());
row.createCell(2).setCellValue(phoneInfo.getArea());
row.createCell(3).setCellValue(phoneInfo.getCountry());
row.createCell(4).setCellValue(phoneInfo.getProvince());
row.createCell(5).setCellValue(phoneInfo.getCity());
row.createCell(6).setCellValue(phoneInfo.getSyb());
row.createCell(7).setCellValue(phoneInfo.getCompany());
row.createCell(8).setCellValue(phoneInfo.getSe_version());
row.createCell(9).setCellValue(phoneInfo.getAndroid_version());
row.createCell(10).setCellValue(phoneInfo.getPhonetype());
row.createCell(11).setCellValue(phoneInfo.getChannel_no());
row.createCell(12).setCellValue(Integer.toString(phoneInfo.getAge()));
row.createCell(13).setCellValue(phoneInfo.getTag());
row.createCell(14).setCellValue(formatter.format( phoneInfo.getCtime()));
row.createCell(15).setCellValue(formatter.format( phoneInfo.getUtime()));
//如果超过 65530行 新建sheet 保存数据
if (rowNum % 65530 == 0 && rowNum != 0) {
num = rowNum ;
sheet1 = wb.createSheet(""+rowNum+"");
row = sheet1.createRow(0);
row.createCell(0).setCellValue("imei");
row.createCell(1).setCellValue("机型");
row.createCell(2).setCellValue("区域");
row.createCell(3).setCellValue("国家");
row.createCell(4).setCellValue("Province");
row.createCell(5).setCellValue("City");
row.createCell(6).setCellValue("事业部");
row.createCell(7).setCellValue("公司");
row.createCell(8).setCellValue("se版本");
row.createCell(9).setCellValue("安卓版本");
row.createCell(10).setCellValue("PhoneType");
row.createCell(11).setCellValue("Channel_no");
row.createCell(12).setCellValue("机龄");
row.createCell(13).setCellValue("标签");
row.createCell(14).setCellValue("首次登录时间");
row.createCell(15).setCellValue("最后登录时间");
}
rowNum ++;
}
}else {
b = false;
}
c ++;
}
//读写完毕前端响应下载
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("table.xls", "UTF-8"));
wb.write(response.getOutputStream());
//保存到本地
/*if(!new File("C:/exceltest").exists()) {
new File("C:/exceltest").mkdirs();
}
wb.write(new FileOutputStream("C:/exceltest/table.xlsx"));*/
}
catch(Exception e) {
e.printStackTrace();
}
}
js: 不能用ajax方式请求
var downExcel = request_url+"/user/exportExcel.json?page=1&size=20&syb="+syb+"&country="+country+"&model="
+ model + "&tag="+tag +"&deadline="+deadline+"&life="+ life+ "&imei=" +imei;
$("#exportInfo").attr("href",downExcel);
jar包
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi
-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
外连 (后来改成单表查了 area country在代码里查出所有在遍历比三表查直接查出结果要快 数据量多的情况下)
select t.id,t.ctime,t.utime,t.imei,t.model,t.province,t.city,t.syb,t.company,t.se_version,
t.android_version,t.phonetype,t.channel_no,t.`status`,t.age,t.tag,a.area,c.country
from (t_phone_info t LEFT JOIN t_country c on t.country = c.`code`)left join t_area a on a.id = t.area where 1=1
(area为空的 country也会为空显示不出来)
连表
select t.id,t.ctime,t.utime,t.imei,t.model,t.province,t.city,t.syb,t.company,t.se_version,
t.android_version,t.phonetype,t.channel_no,t.`status`,t.age,t.tag,a.area,c.country
from t_phone_info t,t_country c, t_area a where 1=1 and t.country = c.`code` and a.id = t.area
(area为空的会被筛选掉)