思路:首先有一个excel模板文件放到项目中去,到后台创建操作excel的对象Workbook,找到模板文件,读出来,把从数据库拿到的数据写进去,在读出到用户的电脑。
前台:
<button id ="export" >导出</button>
/**
* 导出
*/
$("#export").click(function(){
//获取参数
var termCode = $("#term").val();
var gradeCode = $("#grade").val();
var week = $("#week").val();
var firstWeek = $("#firstWeek").val();
//调到后台
window.open("/week/week_exportWeek.action?termCode="+termCode+"&gradeCode="+gradeCode+"&week="+week+"&firstWeek="+firstWeek);
});
后台:
方法:
/**
*导出每周数据
*/
public void exportWeek(){
String termCode = request.getParameter("termCode");//学期code
String gradeCode = request.getParameter("gradeCode");//年级code
String week = request.getParameter("week");//日期
String firstWeek = request.getParameter("firstWeek");//第一周日期
List<HashMap> list = getExportWeek(termCode,gradeCode,week,firstWeek);//获取数据
if(null == list || list.size() == 0){
toWrite("<html><head><title>错误情况</title></head><body>导出模板未找到,请联系管理员</body></html>");
}
Workbook wb = null;//定义Workbook对象 操作excel对象的
try {
String path = NormalAction.class.getResource("/").toString();//获取这个类的路径
path = path.substring(6, path.indexOf("/WEB-INF")) + "/uploadFiles/每周得分导出.xls";//导出模板的路径
InputStream is = new FileInputStream(path);
wb = WorkbookFactory.create(is);//创建对象
Sheet st = wb.getSheetAt(0);//获取sheet页
Row row = null;//定义行
int index = 2;
for(int i = 0; i <list.size(); i++){
row = st.createRow(index++);//给sheet页创建行
row.createCell(0).setCellValue(i+1);//序号
row.createCell(1).setCellValue(StringUtil.getDefaultValue(list.get(i).get("className"), " "));
row.createCell(2).setCellValue(StringUtil.getDefaultValue(list.get(i).get("HLJ"), " "));
row.createCell(3).setCellValue(StringUtil.getDefaultValue(list.get(i).get("LD"), " "));
row.createCell(4).setCellValue(StringUtil.getDefaultValue(list.get(i).get("YBJC"), " "));
row.createCell(5).setCellValue(StringUtil.getDefaultValue(list.get(i).get("SQYQ"), " "));
row.createCell(6).setCellValue(StringUtil.getDefaultValue(list.get(i).get("WMLY"), " "));
row.createCell(7).setCellValue(StringUtil.getDefaultValue(list.get(i).get("ZC"), " "));
row.createCell(8).setCellValue(StringUtil.getDefaultValue(list.get(i).get("LJ"), " "));
row.createCell(9).setCellValue(StringUtil.getDefaultValue(list.get(i).get("PM"), " "));
row.createCell(10).setCellValue(StringUtil.getDefaultValue(list.get(i).get("LASTLJ"), " "));
row.createCell(11).setCellValue(StringUtil.getDefaultValue(list.get(i).get("LASTPM"), " "));
row.createCell(12).setCellValue(StringUtil.getDefaultValue(list.get(i).get("ZLJ"), " "));
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}catch (InvalidFormatException e) {
e.printStackTrace();
}catch (IOException e) {
e.printStackTrace();
}
try{
if(null != wb){
response.setContentType("application/x-msdownload;");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition","attachment;filename="
+ new String("每周得分导出.xls".getBytes(),"ISO8859-1"));//设置头信息 读取什么名称
wb.write(response.getOutputStream());//写到那里去
response.getOutputStream().flush();//刷新缓存
response.getOutputStream().close();//关闭流
}else{
response.setContentType("text/html");
response.setCharacterEncoding("UTF-8");
response.getWriter().println("<html><head><title>错误情况</title></head><body>导出模板未找到,请联系管理员</body></html>");
response.getWriter().flush();
response.getWriter().close();
}
} catch (Exception e) {
}
}