1.控制器代码
@RequestMapping("/AexportresultE/{cou_id}.action")
@Transactional
public void AexportresultE(HttpServletResponse response,@PathVariable Integer cou_id) throws IOException {
//查询要导出到excel中的数据放入集合
List<Student> liststudent=courseService.AqueryresultByCid(cou_id);
Workbook wb=new HSSFWorkbook();
Sheet sheet=wb.createSheet("test");
//poi单元格日期处理
CreationHelper createHelper = wb.getCreationHelper();
CellStyle style = wb.createCellStyle();
style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
//对齐
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);//垂直居中
//表头
CellStyle stylet = wb.createCellStyle();
Row rowt=sheet.createRow(0);
Cell cellt=rowt.createCell(0);
cellt.setCellValue("已结束课程成绩表");
cellt.setCellStyle(stylet);
stylet.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
stylet.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);//垂直居中
//设置字体
Font font = wb.createFont();
//设置字体高度为24像素
font.setFontHeightInPoints((short)24);
//设置字体类型
font.setFontName("宋体");
//设置斜体
//font.setItalic(true);
//设置字体颜色
//font.setColor(IndexedColors.RED.index);
//字体与单元格样式关联
stylet.setFont(font);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(
0,//开始第一行索引号,从0开始
2,//结束行索引号
0,//开始列索引号
6//结束列索引号
));
//创建行
sheet.setColumnWidth(0,256*20);
sheet.setColumnWidth(1,256*20);
sheet.setColumnWidth(2,256*20);
sheet.setColumnWidth(3,256*20);
sheet.setColumnWidth(4,256*20);
sheet.setColumnWidth(5,256*20);
sheet.setColumnWidth(6,256*20);
Row row = null;
Cell cell = null;
int startColumn =3;
int startRow =3;
startColumn =0;
row=sheet.createRow(startRow++);
cell=row.createCell(startColumn++);
cell.setCellValue("名次");
cell=row.createCell(startColumn++);
cell.setCellValue("学号");
cell=row.createCell(startColumn++);
cell.setCellValue("学生名称");
cell=row.createCell(startColumn++);
cell.setCellValue("班级名称");
cell=row.createCell(startColumn++);
cell.setCellValue("重复率");
cell=row.createCell(startColumn++);
cell.setCellValue("学生分数");
cell=row.createCell(startColumn++);
cell.setCellValue("教师评价");
int i=1;
for(Student student:liststudent) {
row=sheet.createRow(startRow++);
startColumn =0;
cell=row.createCell(startColumn++);
cell.setCellValue(i++);
cell=row.createCell(startColumn++);
cell.setCellValue(student.getStu_no());
cell=row.createCell(startColumn++);
cell.setCellValue(student.getStu_name());
cell=row.createCell(startColumn++);
cell.setCellValue(student.getClassname());
cell=row.createCell(startColumn++);
cell.setCellValue(student.getStukhz());
cell=row.createCell(startColumn++);
cell.setCellValue(student.getStuscore()==null?0:student.getStuscore());
cell=row.createCell(startColumn++);
cell.setCellValue(student.getTea_remark());
}
response.setContentType("application/vnd.ms-excel");
String fileName = "已结束课程成绩表.xls";//创建文件名(改)
String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
ServletOutputStream out=response.getOutputStream();
wb.write(out);
out.close();
}
更新一下(2019.11.11)
使用生成好的模板导出表格,个人感觉优点是省空间,缺点是导出格式固定,不同模板需要调用不同方法。
控制器代码:
/**
* 导出用户
* @param appUser
* @param request
* @return
* @throws UnsupportedEncodingException
* @throws IllegalAccessException
* @throws InvocationTargetException
* @throws NoSuchMethodException
*/
@RequestMapping("/exportUser")
public void exportUser(AppUser appUser,HttpServletRequest request,HttpServletResponse response, HttpSession session) throws UnsupportedEncodingException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
SessionInfo sessionInfo = (SessionInfo) session.getAttribute(Contans.SESSION_BEAN);
OperInf operInfObj = sessionInfo.getOperInf();
List<Map> list = appUserService.findBySql(appUser,operInfObj);
String fileName = "用户信息_" + DateUtil.getCurrentDateToString14() + ".xls";
String path = request.getSession().getServletContext().getRealPath("/WEB-INF/template/yonghu.xls");
// 指定下载的文件名
response.setHeader("Content-Disposition", "attachment;filename=" +URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
try {
OutputStream output = response.getOutputStream();
ExcelUtil.writeExcel(list, path,output);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
logger.error(e.getMessage());
}
//添加系统日志
SysLog sysLog = new SysLog();
// 操作方法
sysLog.setOperMethod("导出用户");
// 所属模块
sysLog.setOperModule("全部用户");
// 操作结果
sysLog.setResultMsg("导出成功");
// 操作内容
sysLog.setCompareInf("");
// 操作关键字
sysLog.setOperDesc("");
writeSysLog(sysLog, request, session);
}
工具类代码:
/**
* 导出用户信息
* @param dataList
* @param finalXlsxPath
* @return
*/
public static void writeExcel(List<Map> dataList,String finalXlsxPath,OutputStream output ){
try {
// 读取Excel文档
File finalXlsxFile = new File(finalXlsxPath);
Workbook workBook = getWorkbok(finalXlsxFile);
// sheet 对应一个工作页
Sheet sheet = workBook.getSheetAt(0);
/**
* 往Excel中写新数据
*/
for (int j = 0; j < dataList.size(); j++) {
// 创建一行:从第二行开始,跳过属性列
Row row = sheet.createRow(j + 1);
// 得到要插入的每一条记录
Map dataMap = dataList.get(j);
// 在一行内循环
Cell first = row.createCell(0);
first.setCellValue((String)dataMap.get("aumobile"));
Cell second = row.createCell(1);
second.setCellValue((String)dataMap.get("name"));
Cell third = row.createCell(2);
third.setCellValue((String)dataMap.get("idCard"));
Cell third1 = row.createCell(3);
third1.setCellValue((String)dataMap.get("birthday"));
Cell third2 = row.createCell(4);
third2.setCellValue((String)dataMap.get("sex"));
Cell third3 = row.createCell(5);
third3.setCellValue((String)dataMap.get("addr"));
Cell third4 = row.createCell(6);
third4.setCellValue((String)dataMap.get("contactPerson"));
Cell third5 = row.createCell(7);
third5.setCellValue((String)dataMap.get("relation"));
Cell third6 = row.createCell(8);
third6.setCellValue((String)dataMap.get("regtime"));
}
workBook.write(output);
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(output != null){
output.flush();
output.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("数据导出成功");
}
getWorkbok方法判断excel版本,有时不判断会报错,而且有的写法只支持其中一种版本:
/**
- 判断Excel的版本,获取Workbook
- @param in
- @param filename
- @return
- @throws IOException
*/
public static Workbook getWorkbok(File file) throws IOException{
Workbook wb = null;
FileInputStream in = new FileInputStream(file);
if(file.getName().endsWith(EXCEL_XLS)){ //Excel 2003
wb = new HSSFWorkbook(in);
}else if(file.getName().endsWith(EXCEL_XLSX)){ // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
另外我卖鞋,A锥、椰子、麦昆、古驰、匡威、彪马、UGG、CK、巴黎世家等等,啥都有,物美价廉,有想法加微信哦:AJ666cmy