1、使用XSSFWorkbook进行Excel的渲染、使用XSSFSheet创建一个工作簿
XSSFWorkbook wb = new XSSFWorkbook();// 创建一个Excel文件 XSSFSheet sheet = wb.createSheet("页签");// 创建一个工作簿 // 样式1 XSSFCellStyle style = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 14); font.setFontName("宋体"); style.setFont(font); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直 style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平2、绘制 Excel 表头字段列表
// 表格第一行 XSSFRow row1 = sheet.createRow((short) 0); XSSFCell cell1_1 = row1.createCell((short) 0); cell1_1.setCellValue("序号"); cell1_1.setCellStyle(style); XSSFCell cell1_2 = row1.createCell((short) 1); cell1_2.setCellValue("项目名称"); cell1_2.setCellStyle(style); ........3、字段设置样式
XSSFCellStyle style2 = wb.createCellStyle(); XSSFFont font2 = wb.createFont(); font2.setFontHeightInPoints((short) 11); font2.setFontName("宋体"); style2.setFont(font2); style2.setWrapText(true);// 开启自动换行需要setWrapText（true）//设置字体为宋体、大小为11，开启自动换行，垂直居中对齐，水平居中对齐 XSSFCellStyle style3_1 = wb.createCellStyle(); style3_1.setFont(font2); style3_1.setWrapText(true); style3_1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style3_1.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 设置字体为宋体、大小为11，开启自动换行，垂直顶部对齐，水平右对齐 XSSFCellStyle style3_2 = wb.createCellStyle(); style3_2.setFont(font2); style3_2.setWrapText(true); style3_2.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); style3_2.setAlignment(XSSFCellStyle.ALIGN_RIGHT);4、为字段赋值
for (Record record : list) { XSSFRow rowN = sheet.createRow(i++); XSSFCell cellN1 = rowN.createCell((short) 0); cellN1.setCellValue(index++); cellN1.setCellStyle(style3_1); XSSFCell cellN2 = rowN.createCell((short) 1); cellN2.setCellValue(record.getStr("name")); cellN2.setCellStyle(style3_1); }5、设置字段宽度
sheet.setColumnWidth(0, 1500); sheet.setColumnWidth(1, 5000); sheet.setColumnWidth(2, 5000); ........6、采用输出流写出文件
OutputStream output = null; try { HttpServletResponse response = getRequestContext().getRes(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Expires", "0"); response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0"); response.setHeader("Pragma", "public"); // 设置头时如果有中文需要用UrlEncode编码一下 String fileNameURL = URLEncoder.encode("名字.xlsx", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL); output = response.getOutputStream(); wb.write(output); output.flush(); requestContext.setRequestComplete(true); } catch (Exception e) { e.printStackTrace(); } finally { if (output != null) { try { wb.close(); output.close(); } catch (IOException e) { e.printStackTrace(); } } }到此，Excel就可以下载下来了。。。
