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=" 600px; height: 400px;"
        data-options="fit:true">
        <div data-options="region:'east',title:'导出功能选择',split:true"
            style=" 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=" 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;
	}
	
	
	
	
	
	

}

　　

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/xuehu666/p/12450994.html