思路:count()查出数据库数据的总条数,然后根据count()总条数%excel中多少条分一页算出所导出数据库的数据,可以分多少页(上图)分批查出数据库的数据导出到excel中。
(本图为每四条数据分一页,五条数据,共分两页)
上代码:
public ResponseEntity<byte[]> excelReport(){
#定义参数为OutPutStream流
try (ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()){
#定义excel表头
String keyValue = "表型名称:meterName,现金:cash,支付宝:alipay,微信:weChat" +
",银联:unionPay,累计:total";
List<ReportDetailDTO> list = null;
HSSFWorkbook wb = new HSSFWorkbook();
#查询数据库所有的数据条数
int count = reportService.countDataBase();
#根据excel每页存放多少条数据,算出总页数
int sheet = count%num>0 ? count/num+1 : count/num;
for(int i=0;i<sheet;i++){
#定义limit的第一个参数(查询数据的起始位置)
int a = i*num;
#100w的查询数据库的数据,分批查询
list = reportService.selectDataBase(a,num);
ExcelUtils.exportExcel(wb,byteArrayOutputStream, keyValue, list, "cn.enn.chaoscloud.domain.report.dto.ReportDTO",i+1,sheet);
}
#将数据输出为二进制的形式转为byte[]
byte[] bytes = byteArrayOutputStream.toByteArray();
#输出
return ResponseEntity.ok()
.contentLength(bytes.length)
.contentType(MediaType.valueOf("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
.body(bytes);
}catch (Exception e){
e.printStackTrace();
}
return ResponseEntity.badRequest().build();
}
ExcelUtils工具类
package cn.enn.chaoscloud.master.utils;
import com.lkx.util.ExcelParam;
import com.lkx.util.ExcelTypeEnum;
import com.lkx.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelUtils implements Serializable {
private static final long serialVersionUID = 1L;
/**
* getMap:(将传进来的表头和表头对应的属性存进Map集合,表头字段为key,属性为value)
*
* @author likaixuan
* @param
* : String keyValue = "手机名称:phoneName,颜色:color,售价:price";
* @return
* @since JDK 1.7
*/
public static Map<String, String> getMap(String keyValue) {
Map<String, String> map = new HashMap<String, String>();
if (keyValue != null) {
String[] str = keyValue.split(",");
for (String element : str) {
String[] str2 = element.split(":");
map.put(str2[0], str2[1]);
}
}
return map;
}
/**
* @author likaixuan
* @param
* @return List
* @Date 2018年5月9日 21:42:24
* @since JDK 1.7
*/
public static List<String> getList(String keyValue) {
List<String> list = new ArrayList<String>();
if (keyValue != null) {
String[] str = keyValue.split(",");
for (String element : str) {
String[] str2 = element.split(":");
list.add(str2[0]);
}
}
return list;
}
/**
* setter:(反射的set方法给属性赋值)
*
* @author likaixuan
* @param obj
* 具体的类
* @param att
* 类的属性
* @param value
* 赋予属性的值
* @param type
* 属性是哪种类型 比如:String double boolean等类型
* @throws Exception
* @since JDK 1.7
*/
public static void setter(Object obj, String att, Object value, Class<?> type, int row, int col, Object key)
throws Exception {
try {
Method method = obj.getClass().getMethod("set" + StringUtil.toUpperCaseFirstOne(att), type);
method.invoke(obj, value);
} catch (Exception e) {
throw new Exception("第" + (row + 1) + " 行 " + (col + 1) + "列 属性:" + key + " 赋值异常 " + e);
}
}
/**
* getAttrVal:(反射的get方法得到属性值)
*
* @author likaixuan
* @param obj
* 具体的类
* @param att
* 类的属性
* @param
*
* @param type
* 属性是哪种类型 比如:String double boolean等类型
* @throws Exception
* @since JDK 1.7
*/
public static Object getAttrVal(Object obj, String att, Class<?> type) throws Exception {
try {
Method method = obj.getClass().getMethod("get" + StringUtil.toUpperCaseFirstOne(att));
Object value = new Object();
value = method.invoke(obj);
return value;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* getValue:(得到Excel列的值)
*
* @author likaixuan
* @param
* @return
* @throws Exception
* @since JDK 1.7
*/
public static void getValue(Cell cell, Object obj, String attr, Class attrType, int row, int col, Object key)
throws Exception {
Object val = null;
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
val = cell.getBooleanCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
if (attrType == String.class) {
val = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
} else {
val = dateConvertFormat(sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())));
}
} catch (ParseException e) {
throw new Exception("第" + (row + 1) + " 行 " + (col + 1) + "列 属性:" + key + " 日期格式转换错误 ");
}
} else {
if (attrType == String.class) {
cell.setCellType(Cell.CELL_TYPE_STRING);
val = cell.getStringCellValue();
} else if (attrType == BigDecimal.class) {
val = new BigDecimal(cell.getNumericCellValue());
} else if (attrType == long.class) {
val = (long) cell.getNumericCellValue();
} else if (attrType == Double.class) {
val = cell.getNumericCellValue();
} else if (attrType == Float.class) {
val = (float) cell.getNumericCellValue();
} else if (attrType == int.class || attrType == Integer.class) {
val = (int) cell.getNumericCellValue();
} else if (attrType == Short.class) {
val = (short) cell.getNumericCellValue();
} else {
val = cell.getNumericCellValue();
}
}
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
val = cell.getStringCellValue();
}
setter(obj, attr, val, attrType, row, col, key);
}
/**
* exportExcel:(导出Excel)
*
* @author likaixuan
* @param
* @return
* @throws Exception
* @since JDK 1.7
*/
public static void exportExcel(OutputStream outputStream, String keyValue, List<?> list, String classPath,Integer sheetNum,HSSFWorkbook wb,Integer sheet1)
throws Exception {
Map<String, String> map = getMap(keyValue);
List<String> keyList = getList(keyValue);
Class<?> demo = null;
demo = Class.forName(classPath);
Object obj = demo.newInstance();
if(sheetNum == 0) {
// 创建HSSFWorkbook对象(excel的文档对象)
wb = new HSSFWorkbook();
}
// 建立新的sheet对象(excel的表单)
HSSFSheet sheet = wb.createSheet("sheet"+sheetNum);
// 声明样式
HSSFCellStyle style = wb.createCellStyle();
// 居中显示
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 在sheet里创建第一行为表头,参数为行索引(excel的行),可以是0~65535之间的任何一个
HSSFRow rowHeader = sheet.createRow(0);
// 创建单元格并设置单元格内容
// 存储属性信息
Map<String, String> attMap = new HashMap();
int index = 0;
for (String key : keyList) {
rowHeader.createCell(index).setCellValue(key);
attMap.put(Integer.toString(index), map.get(key).toString());
index++;
}
// 在sheet里创建表头下的数据
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(i + 1);
for (int j = 0; j < map.size(); j++) {
Class<?> attrType = BeanUtils.findPropertyType(attMap.get(Integer.toString(j)),
new Class[]{obj.getClass()});
Object value = getAttrVal(list.get(i), attMap.get(Integer.toString(j)), attrType);
if (null == value) {
value = "";
}
row.createCell(j).setCellValue(value.toString());
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}
}
// 输出Excel文件
try {
if((sheetNum+1) == sheet1) {
wb.write(outputStream);
outputStream.close();
}
} catch (FileNotFoundException e) {
throw new FileNotFoundException("导出失败!" + e);
} catch (IOException e) {
throw new IOException("导出失败!" + e);
}
}
/**
* String类型日期转为Date类型
*
* @param dateStr
* @return
* @throws ParseException
* @throws Exception
*/
public static Date dateConvertFormat(String dateStr) throws ParseException {
Date date = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
date = format.parse(dateStr);
return date;
}
}