private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class); /** * @param list 导出的数据list * @param clazz 导出的对象class * @param fileName 导出的文件名称 * @param response reponse * @param <T> */ public static <T> void exportExcel(List<T> list, Class<T> clazz, String fileName, HttpServletResponse response) { SXSSFWorkbook workBook = getWorkBook(list, clazz); try { response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8")); OutputStream output = response.getOutputStream(); BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output); bufferedOutPut.flush(); workBook.write(bufferedOutPut); bufferedOutPut.close(); } catch (Exception e) { e.printStackTrace(); } } private static <T> SXSSFWorkbook getWorkBook(List<T> list, Class<T> clazz) { SXSSFWorkbook wb = new SXSSFWorkbook(100); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(0); row.setHeightInPoints(23); Field[] declaredFields = getAllFields(clazz); //添加序号 Cell cellOrder = row.createCell(0); cellOrder.setCellValue("序号"); cellOrder.setCellStyle(titleStyle(wb)); sheet.setColumnWidth(0, 10 * 256); //设置表头 for (Field field : declaredFields) { Excel annotation = field.getAnnotation(Excel.class); if (annotation != null) { Cell cell = row.createCell(Integer.valueOf(annotation.orderNum()) ); cell.setCellValue(annotation.name()); cell.setCellStyle(titleStyle(wb)); //设置列的宽度 sheet.setColumnWidth(Integer.valueOf(annotation.orderNum()), (int)annotation.width() * 256); } } // 遍历集合数据，产生数据行 Iterator<?> it = list.iterator(); int index = 0; while (it.hasNext()) { index++; row = sheet.createRow(index); Cell cell = row.createCell(0); cell.setCellStyle(orderStyle(wb)); cell.setCellValue(index); row.setHeightInPoints(18); T t = (T) it.next(); // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值 Field[] fields = getAllFields(t.getClass()); for (Field field : fields) { Excel annotation = field.getAnnotation(Excel.class); if (annotation != null) { cell = row.createCell(Integer.valueOf(annotation.orderNum()) ); cell.setCellStyle(contentStyle(wb)); String fieldName = field.getName(); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { Method getMethod = t.getClass().getMethod(getMethodName, new Class[]{}); Object value = getMethod.invoke(t, new Object[]{}); cell.setCellValue(null == value ? "" : String.valueOf(value)); } catch (Exception e) { e.printStackTrace(); } } } } return wb; } //设置标题样式 private static CellStyle titleStyle(SXSSFWorkbook wb) { CellStyle cellStyle = contentStyle(wb); //设置居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); //设置单元格背景颜色 cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); //设置单元格填充样式(使用纯色背景颜色填充) cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置字体加粗 Font font = wb.createFont(); font.setBold(true); font.setFontHeightInPoints((short) 12); //设置字体 font.setFontName("宋体"); cellStyle.setFont(font); return cellStyle; } //设置单元格样式 private static CellStyle contentStyle(SXSSFWorkbook wb) { //给单元格设置样式 CellStyle cellStyle = wb.createCellStyle(); Font font = wb.createFont(); //设置字体大小 font.setFontHeightInPoints((short) 11); //设置字体 font.setFontName("宋体"); //给字体设置样式 cellStyle.setFont(font); //字体设置边框 cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); //设置垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return cellStyle; } //设置单元格样式 private static CellStyle orderStyle(SXSSFWorkbook wb) { CellStyle cellStyle = contentStyle(wb); cellStyle.setAlignment(HorizontalAlignment.CENTER); return cellStyle; } private static <T> Field[] getAllFields(Class<T> clazz) { Field[] declaredFields = clazz.getDeclaredFields(); Class superClass = clazz.getSuperclass(); if (!superClass.equals(Object.class)) { Field[] superFields = superClass.getDeclaredFields(); int sonLength = declaredFields.length; int superLength = superFields.length; // 合并两个数组 Field[] newFields = new Field[sonLength + superLength]; System.arraycopy(declaredFields, 0, newFields, 0, sonLength); System.arraycopy(superFields, 0, newFields, sonLength, superLength); return newFields; } return declaredFields; } private static final String XLS = "xls"; private static final String XLSX = "xlsx"; /** * 根据文件后缀名类型获取对应的工作簿对象 * @param inputStream 读取文件的输入流 * @param fileType 文件后缀名类型（xls或xlsx） * @return 包含文件数据的工作簿对象 * @throws IOException */ public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException { Workbook workbook = null; if (fileType.equalsIgnoreCase(XLS)) { workbook = new HSSFWorkbook(inputStream); } else if (fileType.equalsIgnoreCase(XLSX)) { workbook = new XSSFWorkbook(inputStream); } return workbook; } //导入
public void add(MultipartFile file) { String fileName = file.getOriginalFilename(); File file1 = new File(fileName); ImportParams params = new ImportParams(); params.setTitleRows(1); params.setHeadRows(2); List<Bill> bills = ExcelImportUtil.importExcel(file1, Bill.class, params); for (Bill bill : bills) { billlMapper.insert(bill); } } //导出 @RequestMapping("/exportExcelDc") public void exportExcelDc(HttpServletResponse response) throws Exception { List<Bill> list = billService.list(); ExcelUtils.exportExcel(list, Bill.class, "数据", response); } //另一种导出
@RequestMapping("/exportExcelDc1") public void exportExcelDc1(HttpServletResponse response) throws Exception { ExportParams params = new ExportParams(); params.setTitle("年度账单"); params.setSheetName("年度账单表"); params.setType(ExcelType.XSSF); List<Bill> list = billService.list(); Workbook workbook = ExcelExportUtil.exportExcel(params, Bill.class, list); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("测试" + ".xls", "UTF-8")); OutputStream output = response.getOutputStream(); BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output); bufferedOutPut.flush(); workbook.write(bufferedOutPut); bufferedOutPut.close(); } // 注解 @Excel(name = "序号",orderNum = "0")