packagecn.xm.jwxt.controller.trainScheme;importcn.xm.jwxt.service.trainScheme.CourseBaseInfoService;import cn.xm.jwxt.utils.*;importcom.github.pagehelper.PageHelper;importcom.github.pagehelper.PageInfo;importorg.apache.log4j.Logger;import org.apache.poi.hssf.usermodel.*;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.cache.annotation.Cacheable;importorg.springframework.stereotype.Controller;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RequestParam;importorg.springframework.web.bind.annotation.ResponseBody;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;import java.io.*;importjava.lang.reflect.Method;importjava.net.URLEncoder;importjava.sql.SQLException;importjava.util.Date;importjava.util.List;importjava.util.Map;/*** @Author: qlq
* @Description 导出课程信息到Excel中
* @Date: 10:11 2018/4/29*/@Controllerpublic classExtCourseExcel {
@AutowiredprivateCourseBaseInfoService courseBaseInfoService;private Logger logger = Logger.getLogger(ExtCourseExcel.class);//1.先从缓存中取数据,看能取到取不到//2.写入excel到本地//3.打开流提供下载//1.查询数据
public List> getCourseBaseInfosByCondition(@RequestParam Mapcondition) {
List> datas = null;try{
datas=courseBaseInfoService.getCourseBaseInfosByCondition(condition);
}catch(SQLException e) {
logger.error("导出课程信息的时候查询数据库出错",e);
}returndatas;
}//2.写文件到excel中
/*** 写数据到本地磁盘
*@paramdatas 课程数据
*@paramfileQualifyName 文件全路径(比如C:/USER/XXX.excel)*/
public void writeCourse2LocalExcel(List>datas,String fileQualifyName){
String[] title= { "序号", "课程编号", "课程平台","课程性质","中文名称","英文名称","学分/学时", "周学时分配","计分方式"};//2.1写入表头信息//创建一个工作簿
HSSFWorkbook workbook = newHSSFWorkbook();//创建一个工作表sheet
HSSFSheet sheet =workbook.createSheet();//设置列宽
this.setColumnWidth(sheet, 9);//创建第一行
HSSFRow row = sheet.createRow(0);//创建一个单元格
HSSFCell cell = null;//创建表头
for (int i = 0; i < title.length; i++) {
cell=row.createCell(i);//设置样式
HSSFCellStyle cellStyle =workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置字体居中//设置字体
HSSFFont font =workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗//font.setFontHeight((short)12);
font.setFontHeightInPoints((short) 13);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(title[i]);
}//2.2写入数据//从第二行开始追加数据
for (int i = 1, length_1 = (datas.size() + 1); i < length_1; i++) {//创建第i行
HSSFRow nextRow =sheet.createRow(i);//设置样式
HSSFCellStyle cellStyle =workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置字体居中//获取数据(一条数据)
Map course = datas.get(i - 1);for (int j = 0; j < 9; j++) {
HSSFCell cell2=nextRow.createCell(j);
cell2.setCellStyle(cellStyle);if (j == 0) {
cell2.setCellValue(i);//第一列是序号
continue;
}if (j == 1) {
cell2.setCellValue(course.get("courseNum").toString());//课程编号
continue;
}if (j == 2) {
cell2.setCellValue(course.get("coursePlatform").toString());//课程平台
continue;
}if (j == 3) {
cell2.setCellValue(course.get("courseNature").toString());//课程性质
continue;
}if (j == 4) {
cell2.setCellValue(course.get("courseNameCN").toString());//中文名称
continue;
}if (j == 5) {
cell2.setCellValue(course.get("courseNameEN").toString());//英文名称
continue;
}if (j == 6) {
cell2.setCellValue(course.get("credit").toString()+"/"+course.get("courseHour").toString());//学分/学时
continue;
}if (j == 7) {
cell2.setCellValue(course.get("weeklyHour").toString());//周学时
continue;
}if (j == 8) {
cell2.setCellValue(course.get("scoringWay").toString());//计分方式
continue;
}
}
}//创建一个文件
File file = newFile(fileQualifyName);//获取文件的父文件夹并删除文件夹下面的文件
File parentFile =file.getParentFile();//获取父文件夹下面的所有文件
File[] listFiles =parentFile.listFiles();if (parentFile != null &&parentFile.isDirectory()) {for(File fi : listFiles) {//删除文件
fi.delete();
}
}//如果存在就删除
if(file.exists()) {
file.delete();
}try{
file.createNewFile();//打开文件流并写入文件
FileOutputStream outputStream =org.apache.commons.io.FileUtils.openOutputStream(file);
workbook.write(outputStream);
outputStream.close();
}catch(IOException e) {
e.printStackTrace();
}
}/*** 设置列宽的函数
*@paramsheet 对哪个sheet进行设置,
*@paramcolNum*/
private void setColumnWidth(HSSFSheet sheet, intcolNum) {for (int i = 0; i < colNum; i++) {int v = 0;
v= Math.round(Float.parseFloat("15.0") *37F);
v= Math.round(Float.parseFloat("20.0") * 267.5F);
sheet.setColumnWidth(i, v);
}
}//3.打开流提供下载
@RequestMapping("/downCourses")public voiddown(HttpServletRequest request, HttpServletResponse response,@RequestParam Map condition){//1.查询数据
List> datas = this.getCourseBaseInfosByCondition(condition);//2.写入excel
String dir = ResourcesUtil.getValue("path","courseExcelFile");
String fileName=DefaultValue.COURSE_DEFAULT_FILENAME;
String fileQualifyName= dir + fileName;//生成的excel名字
this.writeCourse2LocalExcel(datas,fileQualifyName);//写入数据(生成文件)//3.打开流提供下载//获取输入流
try{
InputStream bis= new BufferedInputStream(new FileInputStream(newFile(fileQualifyName)));
fileName= URLEncoder.encode(fileName,"UTF-8");//设置文件下载头
response.addHeader("Content-Disposition", "attachment;filename=" +fileName);//1.设置文件ContentType类型,这样设置,会自动判断下载文件类型
response.setContentType("multipart/form-data");
BufferedOutputStream out= newBufferedOutputStream(response.getOutputStream());int len = 0;while((len = bis.read()) != -1){
out.write(len);
out.flush();
}
out.close();
}catch(Exception e) {
logger.error("下载课程信息出错!",e);
}
}
}