首页 > 代码库 > POI 实现Excel 导出案例分析
POI 实现Excel 导出案例分析
无论使用poi还是使用jxl导出excel都需要用到流
一种是outputstrean，另一种fileoutputstream
第一种：
如果想要弹出保存的提示框必须加入下列三句
response.setContentType("application/vnd.ms-excel; charset=utf-8");
response.setHeader("Content-Disposition","attachment;filename="+filename);
response.setCharacterEncoding("utf-8");
OutputStream os=response.getOutputStream();
在使用第一种的时候，我用的ajax请求。导致excel无法导出，最后我直接请求可以导出（document.location.href="http://www.mamicode.com/${pageContext.request.contextPath}/tran/export.do?"）
原因是：ajax也用到了response.getWriter()方法 要将 数据结果回传，这里 我虽然 放弃了 回传的写入流writer 参数， 但是ajax还是会默认的去掉用，把流已经占用了，当然返回不了任何东西了。
第二种：
action中使用FileOutputStream fos=new FileOutputStream(file);
此时可以使用ajax请求，在导出成功后返回文件路径，在页面中使用window.open(path);即可打开导出的excel文件
jsp页面：
function exportExcel(){
var ids = [];
var rows = $(‘#dbgrid‘).datagrid(‘getSelections‘);
if (rows.length < 1) {
$.messager.alert(‘提示‘, ‘<br>请选中您要导出的记录!‘, ‘info‘);
return;
}
for (var i = 0; i < rows.length; i++) {
ids.push(rows[i][getId()]);
}
//console.log("---ids---"+ids.join(","))
location.href = "http://www.mamicode.com/${path}/admin/export/export.do?ids="+ids.join(",");
}
Action:
package com.shangyu.action.dsz;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.shangyu.common.util.SpringContextHelper;
import com.shangyu.entity.dsz.DispatchOrder;
import com.shangyu.entity.dsz.DispatchParam;
import com.shangyu.service.dsz.DispatchOrderService;
import com.shangyu.service.dsz.DispatchParamService;
import com.shangyu.service.system.SysUserService;
/**
* @desc 导出Excel
* @author hykang
*
*/
@Controller
@RequestMapping("/admin/export")
public class ExportController {
@Resource
private DispatchOrderService dispatchOrderService;
@Resource
private DispatchParamService dispatchParamService;
@Resource
private SysUserService sysUserService;
@SuppressWarnings("deprecation")
@RequestMapping("export.do")
public void exportList(HttpServletRequest req,HttpServletResponse response) throws Exception{
String ids = req.getParameter("ids");
String[] str=ids.split(",");
String[] ids1=new String[str.length];
List<DispatchOrder> orderlist = new ArrayList<>();
for(int i=0;i<str.length;i++){
ids1[i]=str[i];
DispatchOrder entity = dispatchOrderService.getById(ids1[i]);
orderlist.add(entity);
}
HSSFWorkbook wb=new HSSFWorkbook();
HSSFSheet sheet=wb.createSheet("派工单数据汇总");
HSSFRow row=sheet.createRow(0);
//创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//style.setWrapText(true);//允许自动换行
//表头
String[] headers={"派工单编号","部门名称","负责人","创建时间","派工时间","合计工时"};
//填充表头
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
for (int i = 0; i < orderlist.size(); i++) {
row = sheet.createRow(i + 1);
row.setHeightInPoints(18); //设置行高
DispatchOrder record = orderlist.get(i);
String createtime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(record.getCreateTime());
String disptchtime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(record.getDispatchTime());
row.createCell(0).setCellValue(record.getDispatchCode());
sheet.setColumnWidth(0, (record.getDispatchCode().getBytes().length + 4)*256);
if(org.apache.commons.lang3.StringUtils.isNotEmpty(getName(record.getDeptName()) )){
row.createCell(1).setCellValue(getName(record.getDeptName()));
sheet.setColumnWidth(0, (record.getDispatchCode().getBytes().length + 4)*256);
}
if(org.apache.commons.lang3.StringUtils.isNotEmpty(getName(record.getManager()))){
row.createCell(2).setCellValue(getName(record.getManager()));
sheet.setColumnWidth(1, (getName(record.getDeptName()).getBytes().length + 4)*256);
}
row.createCell(3).setCellValue(createtime);
sheet.setColumnWidth(3, (createtime.getBytes().length + 4)*256);
row.createCell(4).setCellValue(disptchtime);
sheet.setColumnWidth(4, (disptchtime.getBytes().length + 4)*256);
if(org.apache.commons.lang3.StringUtils.isNotEmpty(String.valueOf(record.getTotalWork()))){
row.createCell(5).setCellValue(String.valueOf(record.getTotalWork()));
}
}
//sheet.setDefaultColumnWidth(18); //为全部列的列宽设置默认值
String filename=new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒").format(new Date())+".xls";
//response.reset();//一般情况可以不写
//response.setHeader("Content-disposition", "attachment;filename="+toUtf8String(fileName));//字符串转码
response.setHeader("Content-Disposition", "attachment;filename="
+new String(filename.getBytes("utf-8"),"iso8859-1"));
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/vnd.ms-excel");
wb.write(response.getOutputStream());
}
public static String toUtf8String(String s){
StringBuffer sb = new StringBuffer();
for (int i=0;i<s.length();i++){
char c = s.charAt(i);
if (c >= 0 && c <= 255){sb.append(c);}
else{
byte[] b;
try { b = Character.toString(c).getBytes("utf-8");}
catch (Exception ex) {
System.out.println(ex);
b = new byte[0];
}
for (int j = 0; j < b.length; j++) {
int k = b[j];
if (k < 0) k += 256;
sb.append("%" + Integer.toHexString(k).toUpperCase());
}
}
}
return sb.toString();
}
public static String getName(String id){
String name = "";
DispatchParamService dispatchParamService = SpringContextHelper.getBean(DispatchParamService.class);
try {
DispatchParam param = dispatchParamService.getById(id);
name = param.getParamName();
} catch (Exception e) {
e.printStackTrace();
}
return name;
}
}
参考文档：
Springmvc和poi3.9导出excel并弹出下载框
利用POI 导出EXCEL，弹出保存框
本文出自 “不凡人生——求知者” 博客，请务必保留此出处http://825272560.blog.51cto.com/11004487/1947114
POI 实现Excel 导出案例分析