// 1.导入excel模板
String targetPath = rs.getSession().getServletContext() .getRealPath("/excelModel/" + fileName + ".xlsx"); // 2.创建一个workbook，对应一个Excel文件 File fi = new File(targetPath); FileInputStream is = new FileInputStream(fi); XSSFWorkbook wb = new XSSFWorkbook(is); int lastRowNum=wb.getSheetAt(0).getLastRowNum(); if(fileName.contains("横向明细")){ wb.getSheetAt(0).getRow(3).getCell(0).setCellValue("分配期号："+hxmxFpqh); } SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(wb,1000);Sheet sheet = sxssfWorkbook.getSheetAt(0);
// 开始填写查找出来的数据writeExcel(list, sheet ，lastRowNum); // 下载 tkDownload2(sheetName, sxssfWorkbook, res);
-------------2-------------------
public static void writeExcel(List<?> list, Sheet sheet, int lastRowNum)
throws IOException, IllegalArgumentException, IllegalAccessException {Row row;
for (int i = 0; i < list.size(); i++) { row = sheet.createRow((int) i + lastRowNum + 1); row.createCell(0).setCellValue(i + 1); Object tjb = list.get(i); Class class1 = (Class) tjb.getClass(); Field[] fs = class1.getDeclaredFields(); for (int j = 1; j < fs.length; j++) { Field f = fs[j]; f.setAccessible(true); Object v = f.get(tjb); String type = f.getType().toString(); if (v == null || v.toString() == "") { row.createCell(j).setCellValue(""); } else {if (type.endsWith("Double") || type.endsWith("double")) {
row.createCell(j).setCellValue( Double.parseDouble(v.toString())); } else if (type.endsWith("Integer") || type.endsWith("int")) { row.createCell(j).setCellValue( Double.parseDouble(v.toString())); }else if (type.endsWith("String")) {
row.createCell(j).setCellValue(v.toString()); } } } } }
-------------3---------------------
public static void tkDownload2(String sheetName, SXSSFWorkbook wb,
HttpServletResponse res) throws IOException { String fileName = sheetName; ByteArrayOutputStream os = new ByteArrayOutputStream(); wb.write(os); byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数，可以打开下载页面 res.reset(); res.setContentType("application/vnd.ms-excel;charset=utf-8");res.addHeader("Content-Disposition", "attachment;filename="
+ URLEncoder.encode(fileName + ".xlsx", "UTF-8"));// 设置文件名try (ServletOutputStream out = res.getOutputStream(); BufferedInputStream bis = new BufferedInputStream(is); BufferedOutputStream bos = new BufferedOutputStream(out)){ byte[] buff = new byte[2048]; int bytesRead; // Simple read/write loop. while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (Exception e) { //e.printStackTrace(); logger.error("tkDownload failed |{}",()->e.toString()); logger.debug("failed {}", e); } }