一、整体数据一次性写入
解释：其中的HttpServletResponse response 参数可以不用（如果你是需要写到本地的话）
//util类，可以直接拿来用
package com.sunlight.util;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
public class ExportUtil {
//导出excel
public void exportExcel(HttpServletResponse response, List<String> titleList, List<List<String>>contentList){
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-download");
//定义文件名
String fileName = "myExcel.xlsx";
try {
fileName = URLEncoder.encode(fileName,"UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
// 第一步：定义一个新的工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 第二步：创建一个Sheet页
XSSFSheet mySheet = wb.createSheet("mySheet1");
//定义第一行(标题行)
XSSFRow titleRow = mySheet.createRow(0);
for(int i=0;i<titleList.size();i++){
XSSFCell titleCell = titleRow.createCell(i);//定义第1行第i列
titleCell.setCellValue(titleList.get(i));
}
for(int i=0;i<contentList.size();i++){
XSSFRow contentRow = mySheet.createRow(i+1); //定义第i行
List<String> fieldList = contentList.get(i);
for(int j=0;j<fieldList.size();j++){
XSSFCell contentCell = contentRow.createCell(j);//定义第行第i列
contentCell.setCellValue(fieldList.get(j));
}
}
//输出此excel
try {
OutputStream out = response.getOutputStream();
wb.write(out);
out.close();
wb.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//调用 ExportUtil
@RequestMapping(value = "/learnTimeExcel", method = {RequestMethod.GET})
@ResponseBody
public void learnTimeExcel(HttpServletResponse response,@RequestParam(value="learnDate", required=false) String learnDate) {
System.out.println("cell /content/selectAllLearnTimeByLogDate");
Map<String,Object> resultMap = new HashMap<String,Object>();
List<String> titleList = new ArrayList<String>();
List<List<String>> contentList = new ArrayList<List<String>>();
//定义标题
titleList.add("学号");
titleList.add("姓名");
titleList.add("年级");
titleList.add("日期");
titleList.add("阅读时间");
titleList.add("学习音频时间");
titleList.add("学习视频时间");
//定义内容
//Iterator<ReadTimeLog> dataIter = resultList.iterator();
// while (dataIter.hasNext()){
List<String> cellList = new ArrayList<String>();
// ReadTimeLog ReadTimeLog = dataIter.next();
cellList.add(ReadTimeLog.getUserName());//学号
cellList.add(ReadTimeLog.getRealName());//姓名
cellList.add(ReadTimeLog.getGrade());//年级
cellList.add(formatter.format(ReadTimeLog.getLogDate()));//日期
cellList.add(String.valueOf(ReadTimeLog.getLearnTimeArticle()));//阅读时间
cellList.add(String.valueOf(ReadTimeLog.getLearnTimeVoice()));//学习音频时间
cellList.add(String.valueOf(ReadTimeLog.getLearnTimeVideo()));//学习视频时间
contentList.add(cellList);
//}
System.out.println("待下载的excel的列数为："+contentList.size());
ExportUtil exportUtil = new ExportUtil();
exportUtil.exportExcel(response,titleList,contentList);
}
二、数据多次追加输入
此种情况下常运用到共享文件中，此excel为从无到有，并可以被多人打开使用关闭。
注意：下面代码的执行过程：
-
创建excel文件（如果不存在的话），存在就执行下一步---3.追加数据。
-
写入头标题和第一条记录。
-
追加数据。
另外，我在下面的代码中增加了一个类似的缓存机制，先把数据存到一个list中，等到数据满100条后一次性写入这100条数据，如果当前系统中只剩下最后一个用户后，会逐条插入他的数据。
//util 类 package com.sunlight.util; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.List; public class ExcelExportUtil { //导出excel public void exportExcel( List<String> titleList, List<List<String>>contentList){ // //定义文件名 String fileName = "myExcel.xlsx"; FileOutputStream out =null; //输出此excel try { //OutputStream out = response.getOutputStream(); File file = new File("E:/outputData/"+fileName); if(!file.exists()) { file.createNewFile(); // 第一步：定义一个新的工作簿 HSSFWorkbook wb = new HSSFWorkbook(); // 第二步：创建一个Sheet页 HSSFSheet mySheet = wb.createSheet("mySheet1"); HSSFRow titleRow = mySheet.createRow(0); for(int i=0;i<titleList.size();i++){ HSSFCell titleCell = titleRow.createCell(i);//定义第1行第i列 titleCell.setCellValue(titleList.get(i)); } for(int i=0;i<contentList.size();i++){ HSSFRow contentRow = mySheet.createRow(i+1); //定义第i行 List<String> fieldList = contentList.get(i); for(int j=0;j<fieldList.size();j++){ HSSFCell contentCell = contentRow.createCell(j);//定义第行第i列 contentCell.setCellValue(fieldList.get(j)); }//for }//for out = new FileOutputStream("E:/outputData/" + fileName); //向E:/outputData/myExcel.xsl中写数据 wb.write(out); out.flush(); out.close(); wb.close(); }else {//if FileInputStream fs = new FileInputStream("E:/outputData/" + fileName); //获取文件 // POIFSFileSystem ps = new POIFSFileSystem(fs); //使用POI提供的方法得到excel的信息 HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); //获取到工作表，因为一个excel可能有多个工作表 HSSFRow contentRow = sheet.getRow(0); //获取第一行（excel中的行默认从0开始，所以这就是为什么，一个excel必须有字段列头），即，字段列头，便于赋值 System.out.println(sheet.getLastRowNum() + " " + contentRow.getLastCellNum()); //分别得到最后一行的行号，和一条记录的最后一个单元格 //row = sheet.createRow((short) (sheet.getLastRowNum() + 1)); //在现有行号后追加数据 for(int i=0;i<contentList.size();i++){ // HSSFRow contentRow = sheet.createRow(i+1); //定义第i行 contentRow = sheet.createRow((short) (sheet.getLastRowNum() + 1)); //在现有行号后追加数据 List<String> fieldList = contentList.get(i); for(int j=0;j<fieldList.size();j++){ HSSFCell contentCell = contentRow.createCell(j);//定义第行第i列 contentCell.setCellValue(fieldList.get(j)); }//for }//for out = new FileOutputStream("E:/outputData/" + fileName); //向d://test.xls中写数据 wb.write(out); out.flush(); out.close(); wb.close(); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }util类的调用：
@RequestMapping(value = "/getUserLeaveBehaviour",method = {RequestMethod.POST},produces = MediaType.APPLICATION_JSON_UTF8_VALUE) //以json格式返回 @ResponseBody public void getUserLeaveBehaviourInformation( HttpServletRequest request, @RequestBody Map<String,Object> myMap){ Result<Map<String,Object>> result = new Result<Map<String,Object>>(); Map<String,Object> resultMap = new HashMap<String,Object>(); Integer questionId = (Integer)myMap.get("questionId"); int userId = Integer.parseInt(myMap.get("userId").toString()); //根据用户id获取用户姓名 User user = userService.getUserById(userId); String username = user.getUserName(); String loginInTime = myMap.get("loginInTime").toString(); String leaveTime = myMap.get("leaveTime").toString(); DateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date loginInTimeDate=new Date(); Date leaveTimeDate=new Date(); try { loginInTimeDate = simpleDateFormat.parse(loginInTime); leaveTimeDate = simpleDateFormat.parse(leaveTime); }catch (ParseException e){ e.printStackTrace(); } //计算时间间隔（单位为毫秒） Long timeInterval=leaveTimeDate.getTime()-loginInTimeDate.getTime(); //时间处理 String timeIntervalDate=TimeProcessing.longTimeToDay(timeInterval); //将数据写入excel文件 List<String> titleList = new ArrayList<String>(); //定义标题 titleList.add("用户ID"); titleList.add("用户姓名"); titleList.add("问题ID"); titleList.add("登录时间"); titleList.add("离开时间"); titleList.add("页面停留时间"); //定义内容 List<String> cellList = new ArrayList<String>(); cellList.add(String.valueOf(userId)); cellList.add(username); cellList.add(questionId.toString()); cellList.add(loginInTime); cellList.add(leaveTime); cellList.add(timeIntervalDate); contentList.add(cellList); System.out.println("待插入的excel的列数为："+contentList.size()); HttpSession session = request.getSession(); ServletContext application = session.getServletContext(); Set onlineUserSet = (Set)application.getAttribute("onlineUserSet"); if(contentList.size()>=100||onlineUserSet.size()==1) { List<List<String>> contentListcopy = new ArrayList<List<String>>(300); contentListcopy.addAll(contentList); contentList.clear(); ExcelExportUtil exportUtil = new ExcelExportUtil(); exportUtil.exportExcel( titleList, contentListcopy); contentListcopy.clear(); }
网友评论