spring mvc + hibernate + spring + jsp 实现输入sql导出excel
jsp层:
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <%@ include file="/commons/head.jspf"%> <title>Insert title here</title> <style type="text/css"> body, ul, li { margin: 0; padding: 0; } body { background-color: #e4e9f1; color: #002446; margin: 0; } input, select, textarea, th, td { font-size: 1em; } ol.tree { padding: 0 0 0 30px; width: 300px; } li { position: relative; margin-left: -15px; list-style: none; } li.file { margin-left: -18px !important; } li.file a { background: url(document.png) 0 0 no-repeat; color: #002446; padding-left: 21px; text-decoration: none; display: block; } li input { position: absolute; left: 0; margin-left: 0; opacity: 0; z-index: 2; cursor: pointer; height: 1em; width: 1em; top: 0; } input+ol { display: none; } input+ol>li { height: 0; overflow: hidden; margin-left: -14px !important; padding-left: 1px; } li label { cursor: pointer; display: block; padding-left: 17px; background: url(toggle-small-expand.png) no-repeat 0px 1px; } input:checked+ol { background: url(toggle-small.png) 44px 5px no-repeat; margin: -22px 0 0 -44px; padding: 27px 0 0 80px; height: auto; display: block; } input:checked+ol>li { height: auto; } #inputdemo:hover { box-shadow: 0 12px 16px 0 rgba(0, 0, 0, 0.24), 0 17px 50px 0 rgba(0, 0, 0, 0.19); } </style> </head> <body> <div id="cc" class="easyui-layout" style="width: 600px; height: 400px;" data-options="fit:true"> <div data-options="region:'east',title:'导出功能选择',split:true" style="width: 400px;"> <ol class="tree"> <li><label for="folder1" style="font-size: 15px; color: #555555">功能总览</label> <input type="checkbox" id="folder1" checked="checked" /> <ol> <li><label for="subfolder1" style="font-size: 15px; color: #555555">实时数据</label> <input type="checkbox" id="subfolder1" /> <ol> <li class="file"><a href=""></a></li> <li><label for="subsubfolder1" style="font-size: 15px; color: #555555">下级</label> <input type="checkbox" id="subsubfolder1" /> <ol> <li class="file"><a href="${app}/hit/check/checkArchives.do" style="text-decoration: none; font-size: 12px;">数据检测平台</a></li> <li class="file"><a href="${app}/hit/check/resperinfo.do" style="text-decoration: none; font-size: 10px;" style="font-size: 20px; color: blue">档案信息管理</a></li> </ol></li> </ol></li> </ol> </ol> </div>
<!--核心在下面，从页面输入sql-->
<div data-options="region:'center',title:'SQL输入页面'" style="padding: 5px; background: #eee;"> <h1 style="text-align: center; color: gold; letter-spacing: 0; text-shadow: 0px 1px 0px #999, 0px 2px 0px #888, 0px 3px 0px #777, 0px 4px 0px #666, 0px 5px 0px #555, 0px 6px 0px #444, 0px 7px 0px #333, 0px 8px 7px #001135">数据检测平台</h1> <form action="" name="fm" style="text-align: center;"> <textarea id="sql" name="sql" placeholder="请输入查询sql,支持各种复杂sql查询,各种功能函数,覆盖所有表" value="" style="width: 1000px; height: 500px; font-size: 20px; font-style: 楷体;"></textarea> <br /> <input id='inputdemo' type="button" value="数据导出" onclick="download()" style="background-color: #008CBA; border: none; color: white; padding: 15px 32px; text-align: center; text-decoration: none; display: inline-block; font-size: 16px;" /> </form> </div> </div> <script type="text/javascript"> document.onkeyup = function(e){ if(e.keyCode === 13){ var form = fm.sql.value; var url = "${app}/hit/check/download_excel.do?sql=" + form; window.open(url); } } function download() { var form = fm.sql.value; var url = "${app}/hit/check/download_excel.do?sql=" + form; window.open(url); } </script> </html>
controller层:
package com.hitoo.dgmill.checkexception;
import java.net.URLEncoder;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import com.hitoo.dgmill.checkexception.service.CheckExceptionDateService;
@Controller
@RequestMapping("/hit/check")
public class CheckExceptionDateController {
@Autowired
private CheckExceptionDateService checkService;
@RequestMapping("/checkexception")
public String checkException(HttpServletRequest httpServletRequest) {
return "check/info/inputsql";
}
@RequestMapping(value = "/download_excel", method = RequestMethod.GET)
public @ResponseBody
String down(HttpServletResponse response, @RequestParam String sql) {
response.setContentType("application/binary;charset=UTF-8");
try {
ServletOutputStream outputStream = response.getOutputStream();
response.setHeader("Content-Disposition", "attachment;filename="
+ URLEncoder.encode("测试excel" + ".xls", "UTF-8"));
checkService.getAll(outputStream, sql);
} catch (Exception e) {
e.printStackTrace();
}
return "error";
}
/**
* 跳转到综合检测平台
*/
@RequestMapping("/checkArchives")
public String checkArchives() {
return "check/info/checkArchives";
}
/**
* 跳转到综合检测平台
*/
@RequestMapping("/resperinfo")
public String resperinfo() {
return "check/info/resperinfo";
}
}
Service层:
package com.hitoo.dgmill.checkexception.service;
import javax.servlet.ServletOutputStream;
public interface CheckExceptionDateService {
public void getAll(ServletOutputStream outputStream,String sqlString) throws Exception;
}
Service实现层:
package com.hitoo.dgmill.checkexception.service.impl;
import java.io.IOException;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.ServletOutputStream;
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;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.hitoo.dgmill.checkexception.dao.CheckExceptionDateDao;
import com.hitoo.dgmill.checkexception.service.CheckExceptionDateService;
@Service
public class CheckExceptionDateServiceImpl implements CheckExceptionDateService {
@Autowired
private CheckExceptionDateDao checkDao;
public void getAll(ServletOutputStream outputStream, String sqlString) throws Exception {
// 第一步,创建一个workbook,对应一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet;
HSSFSheet hssfSheet = workbook.createSheet("数据详情");
// 第三步，在sheet中添加表头第0行，注意老版本的poi对Excel的行数有限制short
HSSFRow row = hssfSheet.createRow(0);
HSSFRow row2 = hssfSheet.createRow(0);
// 第四步，创建单元格，并设置表头，设置表头居中
HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
// 居中样式
hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCell hssfCell = null;
List<Map<Object, Object>> list = checkDao.getAllInforMation(sqlString);
System.out.println(list);
//核心计算模块
for (int i = 0; i < list.size(); i++) {
row = hssfSheet.createRow(i + 1);
Map<Object, Object> mapList = list.get(i);
Iterator<Object> it = mapList.keySet().iterator();
Set set = new HashSet();
while (it.hasNext()) {
int j = 0, d = 0;
String str = (String) it.next();
if(set.add(str)) {
Iterator iterator = set.iterator();
while (iterator.hasNext()) {
String string = iterator.next().toString();
row2.createCell(d++).setCellValue(string);
if(null != mapList.get(string)) {
row.createCell(j++).setCellValue(mapList.get(string).toString());
}
}
}
}
}
try {
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
DAO层:
package com.hitoo.dgmill.checkexception.dao;
import java.util.List;
import java.util.Map;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import com.hitoo.frame.base.SQLEntity;
@Component
public class CheckExceptionDateDao {
@Autowired
private SessionFactory sessionFactory;
protected Session getCurrentSession() {
Session session = sessionFactory.openSession();
return session;
}
@SuppressWarnings("unchecked")
public List<Map<Object, Object>> getAllInforMation(String sql) throws Exception {
SQLEntity sqlEntity = new SQLEntity();
sqlEntity.setSql(sql);
List<Map<Object, Object>> perInfos = getCurrentSession().createSQLQuery(sqlEntity.getSql()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
if(perInfos == null || perInfos.size() <= 0) {
return null;
}
if(getCurrentSession() != null || getCurrentSession().isConnected()) {
getCurrentSession().close();
}
return perInfos;
}
}
