从数据库导出数据到excel之List<map>导出
说明:很多时候取出来的数据是封装为List<Map<String,Object>>,可以直接导出excel表格
项目说明就在 “上一篇”
直接上代码(数据层和业务层不用说了,查出的代码格式为List<Map<String,Object>>即可):
控制层
List<Map<String,String>> list=expot.GetStudentTest(gender.getGender()); System.out.println("listDate:"+list); ExportExcelHSSF<List<Map<String,String>>> ee= new ExportExcelHSSF<>(); ExportExcelOutputStream ee=new ExportExcelOutputStream(); //String[] headers = { "姓名", "性别", "体重","班级","网络协议","javaEE","计算机基础","Linux操作系统","网络安全","sql数据库","数据结构" }; String[] headers = { "姓名","体重","网络协议","javaEE","计算机基础","Linux操作系统","网络安全","sql数据库","数据结构" }; String fileName = "信息表"; ee.exportExcel(list, headers,fileName, response);
实体类:
public class ExportDateTest implements Serializable{ private String name; //private String gender;//性别 private String weight; //private String grades;//班级 private Double Networkprotocol; private Double javaEE; private Double Computerbasis; private Double Linuxoperatingsystem; private Double networksecurity; private Double SQLdatabase; private Double datastructure; public ExportDateTest() { // TODO Auto-generated constructor stub } public String getName() { return name; } public void setName(String name) { this.name = name; } /* public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } */ public String getWeight() { return weight; } public void setWeight(String weight) { this.weight = weight; } /* public String getGrades() { return grades; } public void setGrades(String grades) { this.grades = grades; } */ public Double getNetworkprotocol() { return Networkprotocol; } public void setNetworkprotocol(Double networkprotocol) { Networkprotocol = networkprotocol; } public Double getJavaEE() { return javaEE; } public void setJavaEE(Double javaEE) { this.javaEE = javaEE; } public Double getComputerbasis() { return Computerbasis; } public void setComputerbasis(Double computerbasis) { Computerbasis = computerbasis; } public Double getLinuxoperatingsystem() { return Linuxoperatingsystem; } public void setLinuxoperatingsystem(Double linuxoperatingsystem) { Linuxoperatingsystem = linuxoperatingsystem; } public Double getNetworksecurity() { return networksecurity; } public void setNetworksecurity(Double networksecurity) { this.networksecurity = networksecurity; } public Double getSQLdatabase() { return SQLdatabase; } public void setSQLdatabase(Double sQLdatabase) { SQLdatabase = sQLdatabase; } public Double getDatastructure() { return datastructure; } public void setDatastructure(Double datastructure) { this.datastructure = datastructure; } public ExportDateTest(String name, String gender, String weight, String grades, Double networkprotocol, Double javaEE, Double computerbasis, Double linuxoperatingsystem, Double networksecurity, Double sQLdatabase, Double datastructure) { super(); this.name = name; //this.gender = gender; this.weight = weight; //this.grades = grades; Networkprotocol = networkprotocol; this.javaEE = javaEE; Computerbasis = computerbasis; Linuxoperatingsystem = linuxoperatingsystem; this.networksecurity = networksecurity; SQLdatabase = sQLdatabase; this.datastructure = datastructure; } @Override public String toString() { return "ExportDate [name=" + name + "" //+ ", gender=" + gender + "" + ", weight=" + weight + "" // + ", grades=" + grades + ", Networkprotocol=" + Networkprotocol + ", javaEE=" + javaEE + ", Computerbasis=" + Computerbasis + ", Linuxoperatingsystem=" + Linuxoperatingsystem + ", networksecurity=" + networksecurity + ", SQLdatabase=" + SQLdatabase + ", datastructure=" + datastructure + "]"; } }
导出代码:
/** * 将List<Map<>>数据格式导出到excel中 * @author 开发者 * */ public class ExportExcelOutputStreamTest { public String exportExcel(List<Map<String, String>> orderlist,String[] headerlist, String name,HttpServletResponse response ) { String result = "系统提示:Excel文件导出成功!"; // 以下开始输出到EXCEL try { //定义输出流,以便打开保存对话框______________________begin OutputStream os = response.getOutputStream();// 取得输出流 response.reset();// 清空输出流 String fileName = name + ".xls"; response.setContentType("application/x-msdownload");// 设定输出文件类型 response.setHeader("Content-Disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" )); //设定文件输出类型 //定义输出流,以便打开保存对话框_______________________end /** **********创建工作簿************ */ WritableWorkbook workbook = Workbook.createWorkbook(os); /** **********创建工作表************ */ WritableSheet sheet = workbook.createSheet("Sheet1", 0); /** **********设置纵横打印(默认为纵打)、打印纸***************** */ SheetSettings sheetset = sheet.getSettings(); sheetset.setProtected(false); sheetset.setDefaultColumnWidth(20); /** ************设置单元格字体************** */ WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10); WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD); WritableFont TitleFont = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD); /** ************以下设置三种单元格样式,灵活备用************ */ // 用于表名,要高端大气! WritableCellFormat title_center = new WritableCellFormat(TitleFont); title_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条 title_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐 title_center.setAlignment(Alignment.CENTRE); // 文字水平对齐 title_center.setWrap(false); // 文字是否换行 // 用于标题居中 WritableCellFormat wcf_center = new WritableCellFormat(BoldFont); wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条 wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐 wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐 wcf_center.setWrap(false); // 文字是否换行 // 用于正文居左 WritableCellFormat wcf_left = new WritableCellFormat(NormalFont); wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条 wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐 wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐 wcf_left.setWrap(true); // 文字是否换行 /** ***************以下是EXCEL第一行列标题********************* */ for (int i = 0; i < headerlist.length; i++) { sheet.addCell(new Label(i, 3, headerlist[i], wcf_center)); } /** ***************以下是EXCEL正文数据********************* */ //这里要分成两种情况,第一种传的是List<Map>,用Map的方式处理 //第二种传的是List<Object>,用普通类取属性的方式来处理; Iterator iterator = orderlist.iterator(); Object o = iterator.next(); Map m = (Map) o; System.out.println("m:"+m); Iterator iter = m.entrySet().iterator(); int first_j=0; while (iter.hasNext()) { Map.Entry entry = (Map.Entry) iter.next(); String[] key_value = entry.toString().split("="); System.out.println(key_value[1]); sheet.addCell(new Label(first_j, 4, key_value[1], wcf_left)); first_j++; } int i=5; while(iterator.hasNext()){ Map row_map = (Map) iterator.next(); Iterator row_iterator = row_map.entrySet().iterator(); int second_j=0; while(row_iterator.hasNext()){ Map.Entry entry_column = (Map.Entry) row_iterator.next(); String[] key_value = entry_column.toString().split("="); sheet.addCell(new Label(second_j, i, key_value[1], wcf_left)); second_j++; } i++; } /** **********将以上缓存中的内容写到EXCEL文件中******** */ workbook.write(); /** *********关闭文件************* */ workbook.close(); System.out.println(result); } catch (Exception e) { result = "系统提示:Excel文件导出失败,原因:" + e.toString(); System.out.println(result); e.printStackTrace(); } return result; }