在Springmvc中导出报表下载Excel文件

文章链接地址：

http://blog.csdn.net/sinat_31824863/article/details/50515615

controller层

@Controller
public class TeLogControl2{
private static Logger log = Logger.getLogger(TeLogControl2.class);


@Resource
protected TeLogServ teLogServ;
@Resource
protected TeSupplierServ teSupplierServ;

/**
*  导出excel
*/
    @RequestMapping(value = "/admin/supplierTeLog/export")
public String export(HttpServletRequest request, HttpServletResponse response, TeLog teLog) throws Exception{
    log.info("======> /admin/supplierTeLog/export");
    if (teLog == null)
teLog = new TeLog();
teLog.setStartRow();
teLog.setPageSize(999999999);
List<TeLog> retList = this.teLogServ.getTeLog(teLog);
for (TeLog tel : retList) {
TeSupplier teSupplier = new TeSupplier();
teSupplier.setSupplier_id(tel.getSupplier_id());
teSupplier = teSupplierServ.getTeSupplierByID(teSupplier);
if (teSupplier != null) {
tel.setSupplier_name(teSupplier.getSupplier_name());//供应商名称
}else{
tel.setSupplier_name(String.valueOf(tel.getSupplier_id()));
}
tel.setData_req_time_str(DateUtil.dateToStr(tel.getData_req_time(), "yyyy-MM-dd hh:mm:ss"));//详单时间
//tel.setCheck_result_str(this.converBasedata("fhjg", String.valueOf(tel.getCheck_result())));//结果
}
List<Map<String, Object>> list = createExcelRecord(retList);
String fileName = "供应商详单报表";
// 列名
String columnNames[] = { "供应商名称", "姓名", "手机号码", "身份证号码", "账单月份", "详单时间", "结果"};
// Map中的key
String keys[] = { "supplierName", "realname", "mobile", "idCardNum", "reqMonth", "dataReqTimeStr", "checkResultStr" };
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
ExcelExportUtil.createWorkBook(list, keys, columnNames).write(os);
} catch (IOException e) {
log.error("ExcelExportUtil.createWorkBook occur error，"+e.getMessage());
e.printStackTrace();
}


byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
return null;
}

private List<Map<String, Object>> createExcelRecord(List<TeLog> teLogs) {
List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put("sheetName", "sheet1");
listmap.add(map);
for (TeLog teLog : teLogs) {
Map<String, Object> mapValue = new HashMap<String, Object>();
mapValue.put("supplierName", teLog.getSupplier_name());
mapValue.put("realname", teLog.getRealname());
mapValue.put("mobile", teLog.getMobile());
mapValue.put("idCardNum", teLog.getId_card_num());
mapValue.put("reqMonth", teLog.getReq_month());
mapValue.put("dataReqTimeStr", teLog.getData_req_time_str());
mapValue.put("checkResultStr", teLog.getCheck_result_str());
listmap.add(mapValue);
}
return listmap;
}

Service层

@Service("teLogServ")
public class TeLogServImpl implements TeLogServ {
@Resource(name = "teLogDao")
private TeLogDao teLogDao;

@Override
public List<TeLog> getTeLog(TeLog teLog) {
// TODO Auto-generated method stub
return teLogDao.getTeLog(teLog);
}

}

Dao层

@Repository("teLogDao")
public interface TeLogDao {
public List<TeLog> getTeLog(TeLog teLog);
}

Mapper.Xml

<select id="getTeLog" parameterType="teLog" resultType="teLog">  
select * from t_te_log where 1=1    
<if test="supplier_id >0">
<![CDATA[
and supplier_id=#{supplier_id} 
]]>
</if>
<if test="check_result >0">
<![CDATA[
and check_result=#{check_result} 
]]>
</if>
<if test="user_id >0">
<![CDATA[
and user_id=#{user_id} 
]]>
</if>
<if test="req_month !=null and req_month !=''">
<![CDATA[
and req_month=#{req_month}   
]]>
</if>
order by log_id desc
    limit #{startRow},#{pageSize}
</select>

Excel工具

public class ExcelExportUtil {

public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) {
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
// 创建第一个sheet（页），并命名
Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < keys.length; i++) {
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
// 创建第一行
Row row = sheet.createRow((short) 0);

// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();

// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();

// 创建第一种字体样式（用于列名）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);

// 创建第二种字体样式（用于值）
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());

// 设置第一种单元格的样式（用于列名）
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);

// 设置第二种单元格的样式（用于值）
cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_CENTER);
// 设置列名
for (int i = 0; i < columnNames.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
// 设置每行每列的值
for (short i = 1; i < list.size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow((short) i);
// 在row行上创建一个方格
for (short j = 0; j < keys.length; j++) {
Cell cell = row1.createCell(j);
cell.setCellValue(list.get(i).get(keys[j]) == null ? "" : list.get(i).get(keys[j]).toString());
cell.setCellStyle(cs2);
}
}
return wb;
}

html页面：

<div class="sbut float_l "><a href="javascript:doExport(1);" id="exportBtn" class="but1">导出</a></div>

function doExport(page){
var supplier_id=$("#supplier_id_q").val();
var check_result=$("#check_result").val();
var req_month=$("#req_month").val();
req_month=req_month.substring(0,4)+req_month.substring(5,7);
window.location.href = getContextPath()+"/admin/supplierTeLog/export.do?supplier_id="+ supplier_id + "&check_result=" + check_result+"&req_month="+req_month;
}