目前操作Excel文件的框架都是基于POI进行封装 这里列举一个POI导出Excel的工具类 有导出单Sheet以及多Sheet的
maven依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> <type>jar</type> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.8</version> </dependency>
贴出工具类代码
1 public class ExportExcelUtil { 2 3 /** 4 * * 5 * 6 * @param filename 保存到客户端的文件名 例：用户.xls 7 * @param title 标题行 例：String[]{"名称","地址"} 8 * @param key 从查询结果List取得的MAP的KEY顺序， 9 * 需要和title顺序匹配， 例：String[]{"name","address"} 10 * @param values 结果集 11 * @param httpServletResponse 12 * @throws IOException 13 */ 14 public static void createN(String filename, String[] title, String[] key, List<Map<String, Object>> values, HttpServletResponse httpServletResponse) throws IOException { 15 String filename2 = new String(filename.getBytes(), "iso-8859-1"); 16 ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream(); 17 XSSFWorkbook workbook = null; 18 httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + filename2); 19 httpServletResponse.setContentType("application/x-download"); 20 workbook = new XSSFWorkbook(); 21 XSSFSheet sheet = workbook.createSheet(); 22 XSSFRow row = null; 23 XSSFCell cell = null; 24 row = sheet.createRow((short) 0); 25 for (int i = 0; title != null && i < title.length; i++) { 26 cell = row.createCell((short) i); 27 cell.setCellType(XSSFCell.CELL_TYPE_STRING); 28 cell.setCellValue(new XSSFRichTextString(title[i])); 29 } 30 31 38 Map map = null; 39 for (int i = 0; values != null && i < values.size(); i++) { 40 row = sheet.createRow((short) (i + 1)); 41 map = values.get(i); 42 for (int i2 = 0; i2 < key.length; i2++) { 43 cell = row.createCell((short) (i2)); 44 cell.setCellType(XSSFCell.CELL_TYPE_STRING); 45 if (map.get(key[i2]) == null) { 46 cell.setCellValue(new XSSFRichTextString("")); 47 } else { 48 cell.setCellValue(new XSSFRichTextString(map.get(key[i2]).toString())); 49 } 50 } 51 } 52 workbook.write(servletOutputStream); 53 servletOutputStream.flush(); 54 servletOutputStream.close(); 55 } 56 57 58 59 /** 60 * 创建多sheet页 Excel表格 61 * @Author: Tan 62 * @Date: 2020/10/12 63 * @param fileName: 64 * @param response: 65 * @param list: 66 * @return: void 67 **/ 68 public static void multiSheetExport(String fileName,HttpServletResponse response,List<Map<String,Object>> list) throws Exception{ 69 String filename2 = new String(fileName.getBytes(), "iso-8859-1"); 70 ServletOutputStream servletOutputStream = response.getOutputStream(); 71 XSSFWorkbook workbook = null; 72 response.setHeader("Content-disposition", "attachment; filename=" + filename2); 73 response.setContentType("application/x-download"); 74 workbook = new XSSFWorkbook(); 75 for (Map<String, Object> map : list) { 76 XSSFSheet sheet = workbook.createSheet(); 77 workbook.setSheetName((int)map.get("sheetIndex"),(String)map.get("sheetName")); 78 createSheet(sheet,(String[])map.get("title"),(String[])map.get("key"),(List<Map<String,Object>>)map.get("values")); 79 } 80 workbook.write(servletOutputStream); 81 servletOutputStream.flush(); 82 servletOutputStream.close(); 83 } 84 85 /** 86 * 写入指定sheet页数据 87 * @Author: Tan 88 * @Date: 2020/10/12 89 * @param sheet: 90 * @param title: 91 * @param key: 92 * @param values: 93 * @return: void 94 **/ 95 public static void createSheet(XSSFSheet sheet,String[] title, String[] key, List<Map<String,Object>> values){ 96 97 XSSFRow row = null; 98 XSSFCell cell = null; 99 row = sheet.createRow((short) 0); 100 for (int i = 0; title != null && i < title.length; i++) { 101 cell = row.createCell((short) i); 102 cell.setCellType(XSSFCell.CELL_TYPE_STRING); 103 cell.setCellValue(new XSSFRichTextString(title[i])); 104 } 113 Map map = null; 114 for (int i = 0; values != null && i < values.size(); i++) { 115 row = sheet.createRow((short) (i + 1)); 116 map = values.get(i); 117 for (int i2 = 0; i2 < key.length; i2++) { 118 cell = row.createCell((short) (i2)); 119 cell.setCellType(XSSFCell.CELL_TYPE_STRING); 120 if (map.get(key[i2]) == null) { 121 cell.setCellValue(new XSSFRichTextString("")); 122 } else { 123 cell.setCellValue(new XSSFRichTextString(map.get(key[i2]).toString())); 124 } 125 } 126 } 127 } 128 129 130 131 132 133 }
导出单sheet的写法 这些方法都是封装了通过response下载文件,就是生成Excel并下载一步到位,如果需要生成文件而不下载 可以修改,传入一个OutputStream即可,输出到指定的流
1 public static void main(String[] args) { 2 //表头 3 String[] title={"姓名","年龄","性别"}; 4 //和表头顺序对应 因为要导出的内容 都是从数据库查询出来的 都是k-v 5 String[] key={"name","age","sex"}; 6 //导出的集合 7 List<Map<String,Object>> list=new ArrayList<>(); 8 Map<String,Object> data1=new HashMap<>(); 9 data1.put("name","张三"); 10 data1.put("age","18"); 11 data1.put("sex","男"); 12 list.add(data1); 13 //导出 14 ExportExcelUtil.createN("文件名.xlsx",title,key,list,response); 15 }
如果你的数据是实体类对象,那么请转成Map,百度方法很多,利用反射也可以转
接下来贴出 多个sheet页导出的demo,其实和导出单个差不多 只不过需要根据sheet页的数量 创建多个sheet 写入数据的步骤是一样的,主要是演示怎么调用
public static void main(String[] args) { //存储多个sheet页 每一个元素代表一个sheet页 List<Map<String,Object>> sheetList=new ArrayList<>(); //表头 String[] title={"姓名","年龄","性别"}; //和表头顺序对应 因为要导出的内容 都是从数据库查询出来的 都是k-v String[] key={"name","age","sex"}; //导出的集合 List<Map<String,Object>> list=new ArrayList<>(); Map<String,Object> data1=new HashMap<>(); data1.put("name","张三"); data1.put("age","18"); data1.put("sex","男"); list.add(data1); //举例一个sheet页的配置 多个以此类推 Map<String,Object> sheet1=new HashMap<>(); //sheet页的排序 从0开始 sheet1.put("sheetIndex",0); //sheet页的名字 sheet1.put("sheetName","4G"); sheet1.put("title",title); sheet1.put("key",key); sheet1.put("values",list); list.add(sheet1); ExportExcelUtil.multiSheetExport("文件名.xlsx",response,sheetList); }