Java实体类
1 package bean;
2
3 public class Question {
4
5 private String timu; //题干
6 private String leixing; //类型
7 private String axuanxiang; //A选项
8 private String bxuanxiang; //B选项
9 private String cxuanxiang; //C选项
10 private String dxuanxiang; //D选项
11 private String daan; //答案
12 private String jiexi; //解析
13 public String getTimu() {
14 return timu;
15 }
16 public void setTimu(String timu) {
17 this.timu = timu;
18 }
19 public String getLeixing() {
20 return leixing;
21 }
22 public void setLeixing(String leixing) {
23 this.leixing = leixing;
24 }
25
26 public String getAxuanxiang() {
27 return axuanxiang;
28 }
29 public void setAxuanxiang(String axuanxiang) {
30 this.axuanxiang = axuanxiang;
31 }
32 public String getBxuanxiang() {
33 return bxuanxiang;
34 }
35 public void setBxuanxiang(String bxuanxiang) {
36 this.bxuanxiang = bxuanxiang;
37 }
38 public String getCxuanxiang() {
39 return cxuanxiang;
40 }
41 public void setCxuanxiang(String cxuanxiang) {
42 this.cxuanxiang = cxuanxiang;
43 }
44 public String getDxuanxiang() {
45 return dxuanxiang;
46 }
47 public void setDxuanxiang(String dxuanxiang) {
48 this.dxuanxiang = dxuanxiang;
49 }
50 public String getDaan() {
51 return daan;
52 }
53 public void setDaan(String daan) {
54 this.daan = daan;
55 }
56 public String getJiexi() {
57 return jiexi;
58 }
59 public void setJiexi(String jiexi) {
60 this.jiexi = jiexi;
61 }
62 @Override
63 public String toString() {
64 return "Question [timu=" + timu + ", leixing=" + leixing + ", axuanxiang=" + axuanxiang + ", bxuanxiang="
65 + bxuanxiang + ", cxuanxiang=" + cxuanxiang + ", dxuanxiang=" + dxuanxiang + ", daan=" + daan
66 + ", jiexi=" + jiexi + "]";
67 }
68 public Quest io n(String timu, String leixing, String axuanxiang, String bxuanxiang, String cxuanxiang,
69 String dxuanxiang, String daan, String jiexi) {
70 super();
71 this.timu = timu;
72 this.leixing = leixing;
73 this.axuanxiang = axuanxiang;
74 this.bxuanxiang = bxuanxiang;
75 this.cxuanxiang = cxuanxiang;
76 this.dxuanxiang = dxuanxiang;
77 this.daan = daan;
78 this.jiexi = jiexi;
79 }
80 public Question() {
81 super();
82 }
83
84
85
86 }
导出Java
package Action.Excel2; import java.io. file ; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.List; import org.apache.commons.io.FileUtils; import org.apache.commons.lang3.StringUtils; import org.apache. poi .hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSF font ; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.eclipse.jdt.internal.compiler.ast.ThisReference; import org.jdom.Attribute; import org.jdom.Element; import bean.Question; public class GenerateExcelPaper { /** * 产生Excel试卷 *@param list 题目集合 */ public static void exportExcelPaper(List<Question> list) { // 标题 String[] title = { "题目", "类型", "A选项", "B选项", "C选项", "D选项", "答案", "解析" }; // 创建一个工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个工作表sheet HSSFSheet sheet = workbook.createSheet(); // 设置列宽 setColumnWidth(sheet, 8); // 创建第一行 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]); } // 模拟数据 /*List<Question> list = new ArrayList<>(); list.add(new Question("你喜欢吃什么", "单选", "老司机", "方便吗", "辣条", "牛奶", "老司机", "我是老司机")); list.add(new Question("你喜欢吃什么", "单选", "老司机", "方便吗", "辣条", "牛奶", "老司机", "我是老司机")); list.add(new Question("你喜欢吃什么", "单选", "老司机", "方便吗", "辣条", "牛奶", "老司机", "我是老司机")); list.add(new Question("你喜欢吃什么", "单选", "老司机", "方便吗", "辣条", "牛奶", "老司机", "我是老司机")); list.add(new Question("你是人", "判断", "", "", "", "", "是", "我是老司机")); list.add(new Question("你是人", "判断", "", "", "", "", "是", "我是老司机"));*/ System.out.println(list); // 从第二行开始追加数据 for (int i = 1; i < (list.size() + 1); i++) { // 创建第i行 HSSFRow nextRow = sheet.createRow(i); for (int j = 0; j < 8; j++) { Question eQuestion = list.get(i-1); HSSFCell cell2 = nextRow.createCell(j); if (j == 0) { cell2.setCellValue( eQuestion.getTimu()); } if (j == 1) { cell2.setCellValue(eQuestion.getLeixing()); } if (j == 2) { cell2.setCellValue(eQuestion.getAxuanxiang()); } if (j == 3) { cell2.setCellValue(eQuestion.getBxuanxiang()); } if (j == 4) { cell2.setCellValue(eQuestion.getCxuanxiang()); } if (j == 5) { cell2.setCellValue(eQuestion.getDxuanxiang()); } if (j == 6) { cell2.setCellValue(eQuestion.getDaan()); } if (j == 7) { cell2.setCellValue(eQuestion.getJiexi()); } } } // 创建一个文件 File file = new File("E:/1.xls"); try { file.createNewFile(); // 打开文件流 FileOutputStream outputStream = FileUtils.openOutputStream(file); workbook.write(outputStream); outputStream. close (); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } // 设置列宽() private static void setColumnWidth(HSSFSheet sheet, int colNum) { 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); } } }
测试:
附一个从后台查询满足条件的数据并导出到Excel中d的例子:
大概思路是:
1.查询满足条件的数据
2.生成Excel到本地
3.打开Excel的OutputStream提供下载
package cn.xm.jwxt.controller.trainScheme; import cn.xm.jwxt. annotation .MyLogAnnotation; import cn.xm.jwxt.service.trainScheme.CourseBaseInfoService; import cn.xm.jwxt.utils.*; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.*; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.cache.annotation.Cacheable; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.Request Map ping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.lang.reflect.Method; import java.net.URLEncoder; import java.sql.SQLException; import java.util.Date; import java.util.List; import java.util.Map; /** * @Author: qlq * @Description 导出课程信息到Excel中 * @Date: 10:11 2018/4/29 */ @Controller public class ExtCourseExcel { @Autowired private CourseBaseInfoService courseBaseInfoService; private Logger logger = Logger.getLogger(ExtCourseExcel.class); //1.先从缓存中取数据，看能取到取不到 //2.写入excel到本地 //3.打开流提供下载 //1.查询数据 public List<Map<String, Object>> getCourseBaseInfosByCondition(@RequestParam Map<String, Object> condition) { List<Map<String, Object>> datas = null; try { datas = courseBaseInfoService.getCourseBaseInfosByCondition(condition); } catch (SQLException e) { logger.error("导出课程信息的时候查询数据库出错",e); } return datas; } //2.写文件到excel中 /** * 写数据到本地磁盘 * @param datas 课程数据 * @param fileQualifyName 文件全路径(比如C:/USER/XXX.excel) */ public void writeCourse2LocalExcel(List<Map<String,Object>> datas,String fileQualifyName){ String[] title = { "课程编号", "课程平台","课程性质","中文名称","英文名称","学分", "学时","讲课时长","实验时长","上机时长","实践时长","周学时分配","计分方式","学时单位" }; //2.1写入表头信息 // 创建一个工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个工作表sheet HSSFSheet sheet = workbook.createSheet(); // 设置列宽 this.setColumnWidth(sheet, 14); // 创建第一行 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<String, Object> course = datas.get(i - 1); for (int j = 0; j < 14; j++) { HSSFCell cell2 = nextRow.createCell(j); cell2.setCellStyle(cellStyle); if (j == 0) { cell2.setCellValue(course.get("courseNum"). toString ());//课程编号 continue; } if (j == 1) { cell2.setCellValue(course.get("coursePlatform").toString());//课程平台 continue; } if (j == 2) { cell2.setCellValue(course.get("courseNature").toString());//课程性质 continue; } if (j == 3) { cell2.setCellValue(course.get("courseNameCN").toString());//中文名称 continue; } if (j == 4) { cell2.setCellValue(course.get("courseNameEN").toString());//英文名称 continue; } if (j == 5) { cell2.setCellValue(course.get("credit").toString());//学分 continue; } if (j == 6) { cell2.setCellValue(course.get("courseHour").toString());//学时 continue; } if (j == 7) { cell2.setCellValue(course.get("teachHour").toString());//讲课时长 continue; } if (j == 8) { cell2.setCellValue(course.get("experimentHour").toString());//实验时长 continue; } if (j == 9) { cell2.setCellValue(course.get("computerHour").toString());//上机时长 continue; } if (j == 10) { cell2.setCellValue(course.get("practiceHour").toString());//实践时长 continue; } if (j == 11) { cell2.setCellValue(course.get("weeklyHour").toString());//周学时分配 continue; } if (j == 12) { cell2.setCellValue(course.get("scoringWay").toString());//积分方式 continue; } if (j == 13) { cell2.setCellValue(course.get("courseHourMethod").toString());//学时单位 continue; } } } // 创建一个文件 File file = new File(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(); } } /** * 设置列宽的函数 * @param sheet 对哪个sheet进行设置， * @param colNum */ private void setColumnWidth(HSSFSheet sheet, int colNum) { for (int i = 0; i < colNum; i++) { int v = 0; // v = Math.round(Float.parseFloat("15.0") * 37F); v = Math.round(Float.parseFloat("16.0") * 267.5F); sheet.setColumnWidth(i, v); } } //3.打开流提供下载 @MyLogAnnotation(operateDescription = "下载了课程信息") @RequestMapping("/downCourses") public void down(HttpServletRequest request, HttpServletResponse response,@RequestParam Map condition){ //1.查询数据 List<Map<String, Object>> 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(new File(fileQualifyName))); fileName = URLEncoder.encode(fileName,"UTF-8"); //设置文件下载头 response.addHeader("Content-Disposition", "attachment;filename=" + fileName); //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型 response.setContentType("multipart/form-data"); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream()); int len = 0; while((len = bis.read()) != -1){ out.write(len); out.flush(); } out.close(); } catch (Exception e) { logger.error("下载课程信息出错!",e); } } }
