org.apache.poi poi 3.17 复制代码
@RequestMapping("/recordExcel")public void recordExcel(HttpServletResponse response,LbsLocRecord lbsLocRecord) { List recordList = lbsLocRecordService.getList(lbsLocRecord); HSSFWorkbook workbook = new HSSFWorkbook(); String sheetName = "定位记录"; HSSFSheet sheet = workbook.createSheet(sheetName); sheet.setDefaultRowHeightInPoints(1F); sheet.setDefaultColumnWidth(20); sheet.setColumnWidth(7,50*256); // 字体 HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 13); // 单元格内容样式 HSSFCellStyle normalStyle = workbook.createCellStyle(); normalStyle.setLocked(true); normalStyle.setWrapText(true); normalStyle.setFont(font); HSSFRow firstRow = sheet.createRow(0); ArrayList headCellNames = Lists.newArrayList("姓名", "员工编号", "手机号", "部门", "经度", "纬度", "定位时间", "位置描述", "错误码", "错误信息","创建时间"); ExcelUtil.setHeadCellNames(firstRow,normalStyle,headCellNames); ArrayList cellValues; for (int i=0;i< recordList.size();i++){ LbsLocRecord record = recordList.get(i); cellValues = Lists.newArrayList( record.getPersonName(), record.getPersonCode(), record.getDeviceId(), record.getDeptName(), record.getLon(), record.getLat(), record.getTime(), record.getPosition(), record.getErrcode(), record.getErrmsg(), DateUtil.DateToString(record.getCreateAt())); HSSFRow hssfRow = sheet.createRow( i + 1); ExcelUtil.setCellValue(hssfRow,normalStyle,cellValues); } try { String filename = "record.xls"; // 自定义输出文件名 String typeName = new String(filename.getBytes("UTF-8"), "UTF-8"); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + typeName); OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } catch (IOException e) { e.printStackTrace(); }}复制代码 public class ExcelUtil {public static void setHeadCellNames(HSSFRow firstRow,HSSFCellStyle cellStyle, List headCellNames){ // 添加表头内容 for (int i = 0; i < headCellNames.size(); i++) { HSSFCell headCell = firstRow.createCell(i); headCell.setCellValue(headCellNames.get(i)); headCell.setCellStyle(cellStyle); }}public static void setCellValue(HSSFRow hssfRow,HSSFCellStyle cellStyle,List cellValues){ // 添加数据内容 for (int i = 0; i < cellValues.size(); i++) { // 创建单元格，并设置值 HSSFCell cell = hssfRow.createCell(i); cell.setCellValue(cellValues.get(i)); cell.setCellStyle(cellStyle); }}public static void createExcel(String fileName, List headCellNames , List dataList,HttpServletResponse response) throws InvocationTargetException { // 创建一个Excel文件 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个工作表 HSSFSheet sheet = workbook.createSheet(); sheet.setDefaultRowHeightInPoints(1F); sheet.setDefaultColumnWidth(20); // 设置单元格格式 HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.LEFT); // 单元格内容样式 cellStyle.setLocked(true); cellStyle.setWrapText(true); // 字体 HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 13); cellStyle.setFont(font); // 添加表头行 HSSFRow firstRow = sheet.createRow(0); // 添加表头内容 setHeadCellNames(firstRow,cellStyle,headCellNames); for (int i=0;i< dataList.size();i++){ T data = dataList.get(i); Method[] methods = data.getClass().getMethods(); HSSFRow hssfRow = sheet.createRow( i + 1); List cellValues = Lists.newArrayList(); for (Method temp : methods) try { if (temp.getName().contains("get")){ Object object = temp.invoke(data); if (object==null){ cellValues.add(""); }else { cellValues.add(object.toString()); } } } catch (IllegalAccessException e) { e.printStackTrace(); } ExcelUtil.setCellValue(hssfRow,cellStyle,cellValues); } try { // 自定义输出文件名 String typeName = new String(fileName.getBytes("UTF-8"), "UTF-8"); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + typeName); OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } catch (IOException e) { e.printStackTrace(); } }}复制代码