1.常规的导出Excel方法:poi导出方式
public class User {
private Long userId;
private String username;
private String sex;
private Long age;
public User (Long userId,String username,String sex,Long age){
this.userId = userId;
this.username = username;
this.sex = sex;
this.age = age;
}
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Long getAge() {
return age;
}
public void setAge(Long age) {
this.age = age;
}
}
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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;
public class CreateClassExcel {
public static List<User> getUser() throws Exception {
//这里可以使用原型模式进行浅复制来减少实例化,优化大批量数据产生
List<User> list = new ArrayList<User>();
User user1 = new User(1L, "张三", "男", 10L);
User user2 = new User(2L, "张四", "男", 22L);
User user3 = new User(3L, "老王", "男", 30L);
list.add(user1);
list.add(user2);
list.add(user3);
return list;
}
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("学生表一");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("学号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("姓名");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("性别");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("年龄");
cell.setCellStyle(style);
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
List<User> list = CreateClassExcel.getUser();
for (int i = 0; i < list.size(); i++)
{
row = sheet.createRow((int) i + 1);
User user = list.get(i);
// 第四步,创建单元格,并设置值
row.createCell((short) 0).setCellValue( user.getUserId());
row.createCell((short) 1).setCellValue(user.getUsername());
row.createCell((short) 2).setCellValue(user.getSex());
row.createCell((short) 3).setCellValue(user.getAge());
}
// 第六步,将文件存到指定位置
try
{
FileOutputStream fout = new FileOutputStream("D:/users.xls");
wb.write(fout);
fout.close();
}
catch (Exception e)
{
String result="系统提示:Excel文件导出失败,原因:"+ e.toString();
System.out.println(result);
e.printStackTrace();
}
}
}
2.导出Excel(CSV)方法:csv文本文件导出方式
/**
* 数据导出到CSV文件
* @param request
* @param httpSession
* @return
*/
@RequestMapping(value = "/csvExport", method = RequestMethod.GET)
public void csvExport(
HttpServletRequest request,
HttpServletResponse response) {
List<User> userList=new ArrayList<User>();
List<User> userList=getUser();
response.reset();// 清空输出流
response.setContentType("application/csv;charset=GBK");//定义输出类型
response.setHeader("Content-Disposition","attachment;filename=capitalData "
+new Timestamp(System.currentTimeMillis())+".csv");//定义输出文件头
PrintWriter out = response.getWriter();// 取得输出流
out.println("用户ID,"
+ "姓名,"
+ "性别,"
+ "年龄");
for (int i = 0,length = userList.size(); i < length; i++) {
user = userList.get(i);
String str = user.getUserId()+","
+user.getUserName()+","
+user.getSex()+","
+user.getAge();
str = str.replace("null", "");
out.println(str);
}
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
3.大批量导出遇到问题
1.内存溢出
2.组装list,生成excel慢,50万的数据花了一个小时都没见完成
4.解决方案
1.采用第一种方式 , 比如SXSSFWorkbook wb = new SXSSFWorkbook(100);在内存中只保留100行记录,超过100就将之前的存储到磁盘里。
2.设置JVM里面的相关参数
3.尽量减少实例化,采用原型模式里面的克隆接口实现复制;或将String改为StringBuffer,重点是在组装一行数据时,一直比较喜欢用map来拼装,但是在我功能上发现还是耗内存的,后来的GC时间太长,造成严重拖累组装数据的效率,后来发现由HashMap改为用StringBuffer拼接行数据效率直接就上去了,当然指定合理的StringBuffer的起始容量效率就更好了。
4.将文件进行压缩,减少下载的容量;
5.采用异步处理,多线程处理。
6.常用的分批导出或CSV文本文件等方式。