JavaWeb中导出excel文件的简单方法

JavaWeb中导出excel文件的简单方法

在平时做系统项目时，经常会需要做导出功能，不论是导出excel，还是导出cvs文件。我下面的demo是在springmvc的框架下实现的。

1.js中只需要用GET模式请求导出就可以了：

$('#word-export-btn').parent().on('click',function(){

var promotionWord = JSON.stringify($('#mainForm').serializeObject());

location.href="${ctx}/promotionWord/export?promotionWord="+promotionWord;

});

2.在controller中要做的是将文件以数据流格式输出：

@RequestMapping("/export")

public void export(HttpSession session, String promotionWord, HttpServletRequest request, HttpServletResponse response) throws IOException {

User sessionUser = (User) session.getAttribute("user");

JSONObject jsonObj = JSONObject.parseObject(promotionWord);

HSSFWorkbook wb = promotionWordService.export(sessionUser.getId(), jsonObj);

response.setContentType("application/vnd.ms-excel");

Calendar cal = Calendar.getInstance();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

String fileName = "word-" + sdf.format(cal.getTime()) + ".xls";

response.setHeader("Content-disposition", "attachment;filename=" + fileName);

OutputStream ouputStream = response.getOutputStream();

wb.write(ouputStream);

ouputStream.flush();

ouputStream.close();

}

3.在service中需要将数据写入到格式文件中：

public HSSFWorkbook export(String userId, JSONObject jsonObj) {

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("word");

HSSFRow row = sheet.createRow(0);

HSSFCellStyle style = wb.createCellStyle();

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

List pWordList;

Map map = new HashMap<>();

map.put("userId", userId);

map.put("checkExistRule", jsonObj.getString("checkExistRule"));

map.put("status", jsonObj.getString("status"));

map.put("qsStar", jsonObj.getString("qsStar"));

map.put("impressionCount", jsonObj.getString("impressionCount"));

map.put("selectGroupId", jsonObj.getString("selectGroupId"));

map.put("isCheck", jsonObj.getString("isCheck"));

map.put("word", jsonObj.getString("word"));

Long impression = jsonObj.getLong("impressionCount");

Long click = jsonObj.getLong("clickCount");

if(impression != null){

PromotionWord word = new PromotionWord();

word.setCreatedBy(userId);

word.setImpressionCount7(impression);

pWordList = getTwentyPercentlists(word);

if(pWordList != null && pWordList.size() > 0){

map.put("impressionCount", pWordList.get(pWordList.size()-1).getImpressionCount());

}else{

map.put("impressionCount", 1);

}

}else if(click != null){

PromotionWord word = new PromotionWord();

word.setCreatedBy(userId);

word.setClickCount7(click);

pWordList = getTwentyPercentlists(word);

if(pWordList != null && pWordList.size() > 0){

map.put("clickCount", pWordList.get(pWordList.size()-1).getClickCount());

}else{

map.put("clickCount", 1);

}

}

List list = commonDao.queryList(PROMOTION_WORD_DAO + ".queryExportDataByUser", map);

String[] excelHeader = {"关键词", "价格","搜索热度","推广评分","购买热度","曝光量","点击量","点击率","推广时长","花费","平均点击花费","匹配产品数","预估排名","状态"};

for (int i = 0; i < excelHeader.length; i++) {

HSSFCell cell = row.createCell(i);

cell.setCellValue(excelHeader[i]);

cell.setCellStyle(style);

if(i == 0){

sheet.setColumnWidth(0, 30*256);

}else{

sheet.setColumnWidth(i, 10*256);

}

}

if(list != null && list.size() > 0)

for (int i = 0; i < list.size(); i++) {

row = sheet.createRow(i + 1);

PromotionWord word = list.get(i);

row.createCell(0).setCellValue(word.getWord());

row.createCell(1).setCellValue(word.getPrice()+"");

row.createCell(2).setCellValue(word.getSearchCount());

row.createCell(3).setCellValue(word.getQsStar());

row.createCell(4).setCellValue(word.getBuyCount());

row.createCell(5).setCellValue(word.getImpressionCount7());

row.createCell(6).setCellValue(word.getClickCount7());

if(word.getClickCount7() == 0L){

row.createCell(7).setCellValue("0.00%");

}else{

DecimalFormnnqLPNAfeeat df = new DecimalFormat("0.00%");

row.createCell(7).setCellValue(df.format((Double.valueOf(word.getClickCount7())/Double.valueOf(word.getImpressionCount7()))));

}

row.createCell(8).setCellValue(word.getOnlineTime7());

row.createCell(9).setCellValue(word.getCost7()+"");

row.createCell(10).setCellValue(word.getAvgCost7()+"");

row.createCell(11).setCellValue(word.getMatchCount());

String rank = "";

if(word.getMatchCount() != null && word.getMatchCount() != 0){

if(word.getProspectRank() == null || word.getProspectRank() == 0L){

rank = "其他位置";

}else{

rank = "第"+word.getProspectRank()+"位";

}

}else{

rank = "---";

}

row.createCell(12).setCellValue(rank);

row.createCell(13).setCellValue(word.getStatus() == 1 ?"暂停":"启动");

}

return wb;

}

这样之后就可以直接点击导出就有效果了。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。