这里推荐一个比较好的poi导出word模版,http://m.blog.csdn.net/liushimiao0104/article/details/78520120
但需要注意的是导入的jar包,poi-3.9.jar,ooxml-schemas-1.1.jar,poi-tl-1.2.0.jar
以下是使用poi导出word与excel模版部份代码
@RequestMapping(value = "/downloadMaterialReqNotificationLetter", method = RequestMethod.GET) public void downloadMaterialReqNotificationLetter( @RequestParam(required = false, value = "receiver") String receiver, @RequestParam(required = false, value = "receiverPhone") String receiverPhone, @RequestParam(required = true, value = "isGenerateElectronicTransfer") int isGenerateElectronicTransfer, @RequestParam(required = true, value = "materialReqIds") String materialReqIds, HttpServletResponse response,HttpServletRequest request) { String prePath=request.getSession().getServletContext().getRealPath("/exportTemplate");; try { SimpleDateFormat sfd = new SimpleDateFormat("yyyy-MM-dd"); Map<String, Object> data = new HashMap<String, Object>(); receiver = Utils.isBlank(receiver) ? "aa" : receiver; receiverPhone = Utils.isBlank(receiverPhone) ? "13928806144" : receiverPhone; data.put("receiver", receiver); data.put("receiverPhone", receiverPhone); data.put("address", "广州物流"); data.put("receiveNotificationMail", ""); data.put("email", ""); data.put("faxNumber", ""); data.put("currentDate", sfd.format(new Date())); List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>(); String typegroupcode = ""; TSTypegroup tsTypegroup = systemService.findUniqueByProperty(TSTypegroup.class, "typegroupcode", "unit"); if (tsTypegroup != null) { typegroupcode = tsTypegroup.getId(); } //判断供应商是否为同一个 for (String id : materialReqIds.split(",")) { Map<String, Object> mapData = materialReqService.getMaterialReqAnnouncement(typegroupcode, id); mapData = Utils.mapTojson(mapData); data.put("vendorName", mapData.get("vendorname") + ""); data.put("projectName", mapData.get("projectname") + ""); if (mapData != null) { datas.add(mapData); } } String fileName = "送货通知函"; response.setContentType("application/octet-stream "); response.setHeader("Connection", "close"); // 表示不能用浏览器直接打开 response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".zip"); response.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载 response.setCharacterEncoding("UTF-8"); HSSFWorkbook workbook = exportExcelForAnnoucement(datas,prePath,data.get("vendorName") + ""); XWPFTemplate template = exportWordForAnnoucement(data, isGenerateElectronicTransfer,prePath); OutputStream toWebOs = response.getOutputStream(); //doc名 String tmpName = System.currentTimeMillis()+"" ; String docFileName = tmpName+".docx" ; //文件路径 String uploadLocal = ResourceUtil.getConfigByName("uploadLocal") + File.separator + "tmp"+File.separator; File fileDir = new File(uploadLocal); if (!fileDir.exists()) { fileDir.mkdir(); } //输出的doc文件 File docxFile = new File(uploadLocal + docFileName); FileOutputStream out = new FileOutputStream(docxFile); template.write(out); String xlsFileName = tmpName+ ".xls"; //生成xls File xlsFile = new File(uploadLocal + xlsFileName); FileOutputStream xlsOus = new FileOutputStream(xlsFile); workbook.write(xlsOus); List<File> fileList = new ArrayList<>(); fileList.add(docxFile); fileList.add(xlsFile); //zip输出名字 String outPutZipFileName = tmpName + ".zip"; //压缩文件 ZipUtils.zipFiles(fileList, new File(uploadLocal + outPutZipFileName)); //输出浏览器 InputStream is = new BufferedInputStream(new FileInputStream(uploadLocal+outPutZipFileName)); byte buffer[] = new byte[1024]; int len; while ((len = is.read(buffer, 0, 1024)) != -1) { toWebOs.write(buffer, 0, len); } out.close(); xlsOus.close(); toWebOs.close(); is.close(); } catch (Exception e) { e.printStackTrace(); throw new BusinessException(e); } } private HSSFWorkbook exportExcelForAnnoucement(List<Map<String, Object>> data, String prePath,String venderName) throws Exception { String announcementDocFileName = prePath+File.separator+"announcement.xlsx"; String[] headers = {"materialreqid", "departname", "materialname", "spec", "unit", "contractcode", "projectcode", "projectname", "reqquantity", "reqarrivetime", "newreqarrivetime", "constdep", "consignee", "consigneetel", "materialreqremark"}; File file = new File(announcementDocFileName); InputStream in = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(in); HSSFSheet sheet = workbook.getSheet("导出信息"); ExportExcel exportExcel = new ExportExcel(); HSSFCell vendorNameCell = sheet.getRow(0).getCell(1); exportExcel.dataProcessing(vendorNameCell, venderName); for (int i = 0; i < data.size(); i++) { int excelIndex = i + 6; //实际EXCEL行号 HSSFRow dataRow = sheet.createRow(excelIndex); dataRow.setHeight((short) (500)); Map map = data.get(i); for (int h = 0; h < headers.length; h++) { HSSFCell cell = dataRow.createCell((short) h); Object cellValue = map.get(headers[h]); exportExcel.dataProcessing(cell, cellValue); } } return workbook; } private XWPFTemplate exportWordForAnnoucement(Map<String, Object> data, int isGenerateElectronicTransfer, String prePath) throws Exception { String announcementDocFileName = prePath+File.separator+"announcement.docx"; String announcementElectronicTransferDocFileName = prePath+File.separator+"announcement_electronic_transfer.docx"; XWPFTemplate template = XWPFTemplate.compile(announcementDocFileName); if (isGenerateElectronicTransfer == 1) { template = XWPFTemplate.compile(announcementElectronicTransferDocFileName); data.put("dedicatedMail", ""); data.put("fileDownloadUrl", ""); } template.render(data); return template; }
压缩工具类:
public class ZipUtils { private ZipUtils() { throw new UnsupportedOperationException("u can't instantiate me..."); } /** * 批量压缩文件 * * @param resFiles 待压缩文件集合 * @param zipFilePath 压缩文件路径 * @return {@code true}: 压缩成功<br>{@code false}: 压缩失败 * @throws IOException IO错误时抛出 */ public static boolean zipFiles(Collection<File> resFiles, String zipFilePath) throws IOException { return zipFiles(resFiles, zipFilePath, null); } /** * 批量压缩文件 * * @param resFiles 待压缩文件集合 * @param zipFilePath 压缩文件路径 * @param comment 压缩文件的注释 * @return {@code true}: 压缩成功<br>{@code false}: 压缩失败 * @throws IOException IO错误时抛出 */ public static boolean zipFiles(Collection<File> resFiles, String zipFilePath, String comment) throws IOException { return zipFiles(resFiles, getFileByPath(zipFilePath), comment); } /** * 批量压缩文件 * * @param resFiles 待压缩文件集合 * @param zipFile 压缩文件 * @return {@code true}: 压缩成功<br>{@code false}: 压缩失败 * @throws IOException IO错误时抛出 */ public static boolean zipFiles(Collection<File> resFiles, File zipFile) throws IOException { return zipFiles(resFiles, zipFile, null); } /** * 批量压缩文件 * * @param resFiles 待压缩文件集合 * @param zipFile 压缩文件 * @param comment 压缩文件的注释 * @return {@code true}: 压缩成功<br>{@code false}: 压缩失败 * @throws IOException IO错误时抛出 */ public static boolean zipFiles(Collection<File> resFiles, File zipFile, String comment) throws IOException { if (resFiles == null || zipFile == null) return false; ZipOutputStream zos = null; try { zos = new ZipOutputStream(new FileOutputStream(zipFile)); for (File resFile : resFiles) { if (!zipFile(resFile, "", zos, comment)) return false; } return true; } finally { if (zos != null) { zos.finish(); zos.close(); } } } /** * 压缩文件 * * @param resFilePath 待压缩文件路径 * @param zipFilePath 压缩文件路径 * @return {@code true}: 压缩成功<br>{@code false}: 压缩失败 * @throws IOException IO错误时抛出 */ public static boolean zipFile(String resFilePath, String zipFilePath) throws IOException { return zipFile(resFilePath, zipFilePath, null); } /** * 压缩文件 * * @param resFilePath 待压缩文件路径 * @param zipFilePath 压缩文件路径 * @param comment 压缩文件的注释 * @return {@code true}: 压缩成功<br>{@code false}: 压缩失败 * @throws IOException IO错误时抛出 */ public static boolean zipFile(String resFilePath, String zipFilePath, String comment) throws IOException { return zipFile(getFileByPath(resFilePath), getFileByPath(zipFilePath), comment); } /** * 压缩文件 * * @param resFile 待压缩文件 * @param zipFile 压缩文件 * @return {@code true}: 压缩成功<br>{@code false}: 压缩失败 * @throws IOException IO错误时抛出 */ public static boolean zipFile(File resFile, File zipFile) throws IOException { return zipFile(resFile, zipFile, null); } /** * 压缩文件 * * @param resFile 待压缩文件 * @param zipFile 压缩文件 * @param comment 压缩文件的注释 * @return {@code true}: 压缩成功<br>{@code false}: 压缩失败 * @throws IOException IO错误时抛出 */ public static boolean zipFile(File resFile, File zipFile, String comment) throws IOException { if (resFile == null || zipFile == null) return false; ZipOutputStream zos = null; try { zos = new ZipOutputStream(new FileOutputStream(zipFile)); return zipFile(resFile, "", zos, comment); } finally { if (zos != null) { zos.close(); } } } /** * 压缩文件 * * @param resFile 待压缩文件 * @param rootPath 相对于压缩文件的路径 * @param zos 压缩文件输出流 * @param comment 压缩文件的注释 * @return {@code true}: 压缩成功<br>{@code false}: 压缩失败 * @throws IOException IO错误时抛出 */ private static boolean zipFile(File resFile, String rootPath, ZipOutputStream zos, String comment) throws IOException { rootPath = rootPath + (Utils.isBlank(rootPath) ? "" : File.separator) + resFile.getName(); if (resFile.isDirectory()) { File[] fileList = resFile.listFiles(); // 如果是空文件夹那么创建它,我把'/'换为File.separator测试就不成功,eggPain if (fileList == null || fileList.length <= 0) { ZipEntry entry = new ZipEntry(rootPath + '/'); if (!Utils.isBlank(comment)) entry.setComment(comment); zos.putNextEntry(entry); zos.closeEntry(); } else { for (File file : fileList) { // 如果递归返回false则返回false if (!zipFile(file, rootPath, zos, comment)) return false; } } } else { InputStream is = null; try { is = new BufferedInputStream(new FileInputStream(resFile)); ZipEntry entry = new ZipEntry(rootPath); if (!Utils.isBlank(comment)) entry.setComment(comment); zos.putNextEntry(entry); byte buffer[] = new byte[1024]; int len; while ((len = is.read(buffer, 0, 1024)) != -1) { zos.write(buffer, 0, len); } zos.closeEntry(); } finally { is.close(); } } return true; } /** * 获取压缩文件中的文件路径链表 * * @param zipFilePath 压缩文件路径 * @return 压缩文件中的文件路径链表 * @throws IOException IO错误时抛出 */ public static List<String> getFilesPath(String zipFilePath) throws IOException { return getFilesPath(getFileByPath(zipFilePath)); } /** * 获取压缩文件中的文件路径链表 * * @param zipFile 压缩文件 * @return 压缩文件中的文件路径链表 * @throws IOException IO错误时抛出 */ public static List<String> getFilesPath(File zipFile) throws IOException { if (zipFile == null) return null; List<String> paths = new ArrayList<>(); Enumeration<?> entries = getEntries(zipFile); while (entries.hasMoreElements()) { paths.add(((ZipEntry) entries.nextElement()).getName()); } return paths; } /** * 获取压缩文件中的注释链表 * * @param zipFilePath 压缩文件路径 * @return 压缩文件中的注释链表 * @throws IOException IO错误时抛出 */ public static List<String> getComments(String zipFilePath) throws IOException { return getComments(getFileByPath(zipFilePath)); } /** * 获取压缩文件中的注释链表 * * @param zipFile 压缩文件 * @return 压缩文件中的注释链表 * @throws IOException IO错误时抛出 */ public static List<String> getComments(File zipFile) throws IOException { if (zipFile == null) return null; List<String> comments = new ArrayList<>(); Enumeration<?> entries = getEntries(zipFile); while (entries.hasMoreElements()) { ZipEntry entry = ((ZipEntry) entries.nextElement()); comments.add(entry.getComment()); } return comments; } /** * 获取压缩文件中的文件对象 * * @param zipFilePath 压缩文件路径 * @return 压缩文件中的文件对象 * @throws IOException IO错误时抛出 */ public static Enumeration<?> getEntries(String zipFilePath) throws IOException { return getEntries(getFileByPath(zipFilePath)); } /** * 获取压缩文件中的文件对象 * * @param zipFile 压缩文件 * @return 压缩文件中的文件对象 * @throws IOException IO错误时抛出 */ public static Enumeration<?> getEntries(File zipFile) throws IOException { if (zipFile == null) return null; return new ZipFile(zipFile).entries(); } public static File getFileByPath(String filePath) { return Utils.isBlank(filePath) ? null : new File(filePath); } public static String getFileName(String filePath) { if (Utils.isBlank(filePath)) return filePath; int lastSep = filePath.lastIndexOf(File.separator); return lastSep == -1 ? filePath : filePath.substring(lastSep + 1); } /** * 判断目录是否存在,不存在则判断是否创建成功 * * @param file 文件 * @return {@code true}: 存在或创建成功<br>{@code false}: 不存在或创建失败 */ public static boolean createOrExistsDir(File file) { // 如果存在,是目录则返回true,是文件则返回false,不存在则返回是否创建成功 return file != null && (file.exists() ? file.isDirectory() : file.mkdirs()); } }
生成excel工具类:
public class ExportExcel { private String name; public ExportExcel() { } /** * EXCEL名称 * * @param name */ public ExportExcel(String name) { this.name = name; } /** * 生成EXCEL * * @param data 数据 * @param headers 表头 * @return 返回相对路径,失败返回null */ public String exportExcel(List<Map> data, Columns[] headers, String savePath) { return exportExcel(data, headers, null, savePath); } /** * 生成EXCEL * * @param data 数据 * @param headers 表头 * @param footers 尾行 * @return 返回相对路径,失败返回null */ public String exportExcel(List<Map> data, Columns[] headers, List<Map> footers, String savePath) { if (data.isEmpty()) { return null; } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(name); //生成表头单元样式 HSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setBorderTop(CellStyle.BORDER_THIN); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); //生成一个字体 HSSFFont headerFont = workbook.createFont(); headerFont.setColor(HSSFColor.WHITE.index); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle.setFont(headerFont); //生成数据单元样式 HSSFCellStyle dataStyle = workbook.createCellStyle(); dataStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); dataStyle.setBorderBottom(CellStyle.BORDER_THIN); dataStyle.setBorderLeft(CellStyle.BORDER_THIN); dataStyle.setBorderRight(CellStyle.BORDER_THIN); dataStyle.setBorderTop(CellStyle.BORDER_THIN); dataStyle.setAlignment(CellStyle.ALIGN_CENTER); dataStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //生成另一个字体 HSSFFont dataFont = workbook.createFont(); dataFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); //把字体应用到当前的样式 dataStyle.setFont(dataFont); //尾行样式 HSSFCellStyle footerStyle = workbook.createCellStyle(); footerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); footerStyle.setBorderBottom(CellStyle.BORDER_THIN); footerStyle.setBorderLeft(CellStyle.BORDER_THIN); footerStyle.setBorderRight(CellStyle.BORDER_THIN); footerStyle.setBorderTop(CellStyle.BORDER_THIN); footerStyle.setAlignment(CellStyle.ALIGN_CENTER); footerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //生成一个字体 HSSFFont footerFont = workbook.createFont(); footerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); footerStyle.setFont(footerFont); //生成表头 HSSFRow row = sheet.createRow(0); Map<String, Integer> columnIndex = new HashMap<String, Integer>(); for (int i = 0; i < headers.length; i++) { //设置列宽 sheet.setColumnWidth((short) i, (short) (headers[i].getWidth() * 256)); //填充数据 HSSFCell cell = row.createCell((short) i); cell.setCellStyle(headerStyle); HSSFRichTextString text = new HSSFRichTextString(headers[i].getTitle()); cell.setCellValue(text); columnIndex.put(headers[i].getField(), i); } //生成数据 for (int i = 0; i < data.size(); i++) { int excelIndex = i + 1; //实际EXCEL行号 HSSFRow dataRow = sheet.createRow((short) excelIndex); Map map = data.get(i); for (int h = 0; h < headers.length; h++) { HSSFCell cell = dataRow.createCell((short) h); // cell.setCellStyle(dataStyle); Object cellValue = headers[h].getFormatter().formatter(map.get(headers[h].getField()), excelIndex); dataProcessing(cell, cellValue); } } //设置尾行 if (footers != null) { int footersIndex = data.size() + 1; for (int i = 0; i < footers.size(); i++) { footersIndex += i; HSSFRow footerRow = sheet.createRow((short) footersIndex); Map<String, Object> map = footers.get(i); for (Map.Entry<String, Object> m : map.entrySet()) { HSSFCell cell = footerRow.createCell(columnIndex.get(m.getKey()).shortValue()); cell.setCellStyle(footerStyle); dataProcessing(cell, m.getValue()); } } } //生成EXCEL OutputStream out = null; try { File file = new File(savePath); if (!file.exists()) { file.mkdir(); } String outName = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss").format(new Date()) + ".xls"; String fileName = file.getPath() + File.separator + outName; out = new FileOutputStream(fileName); workbook.write(out); return outName; } catch (IOException e) { e.printStackTrace(); return null; } finally { if (out != null) { try { out.close(); } catch (IOException e) { e.printStackTrace(); return null; } } } } /** * 对各种类型处理 * * @param cellValue * @param cell */ public void dataProcessing(HSSFCell cell, Object value) { if (value instanceof Boolean) { cell.setCellValue((Boolean) value); } else if (value instanceof Date) { cell.setCellValue((Date) value); } else if (value instanceof Double) { cell.setCellValue((Double) value); } else if (value instanceof Float || value instanceof Long || value instanceof Integer) { cell.setCellValue(Double.valueOf(value.toString())); } else if (value instanceof BigDecimal) { cell.setCellValue(String.valueOf(value)); } else { HSSFRichTextString str = new HSSFRichTextString((String) value); cell.setCellValue(str); } } /** * 数据处理回调 */ public interface Formatter { /** * 数据单元的回调方法 * * @param value * @param index * @return 返回处理后的数据 */ public Object formatter(Object value, int index); } /** * 表头数据类型 */ public class Columns { private String field; private String title; private int width = 15; private Formatter formatter = new Formatter() { public Object formatter(Object value, int index) { return value; } }; /** * 设置数据 * * @param field 字段 * @param title 标题 */ public Columns(String field, String title) { this.field = field; this.title = title; } public String getField() { return field; } public String getTitle() { return title; } public Formatter getFormatter() { return formatter; } /** * 数据处理回调方法 * * @param formatter * @return */ public Columns setFormatter(Formatter formatter) { this.formatter = formatter; return this; } public int getWidth() { return width; } /** * 设置表格宽度(字符为单位) * * @param width * @return */ public Columns setWidth(int width) { this.width = width; return this; } } /** * 导出定额非定额的物资清单 * * @param fileName * @param data * @param headers * @param response * @param vendorArray * @throws Exception */ public void ExportExcelForVendor(String fileName, List<Map<String, Object>> data, Columns[] headers, HttpServletResponse response, List<Map<String, Object>> vendorArray) throws Exception { response.setContentType("application/octet-stream "); response.setHeader("Connection", "close"); // 表示不能用浏览器直接打开 response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".zip"); response.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载 response.setCharacterEncoding("UTF-8"); OutputStream ouputStream = response.getOutputStream(); ZipOutputStream zipOutputStream = new ZipOutputStream(ouputStream); int vendorSize = vendorArray.size(); String[] vendorNameArray = new String[vendorSize]; for (int i = 0; i < vendorSize; i++) { vendorNameArray[i] = (String) vendorArray.get(i).get("vendor_name"); } List<Map<String, Object>>[] datas = new ArrayList[vendorSize]; for (int i = 0; i < vendorSize; i++) { datas[i] = new ArrayList<Map<String, Object>>(); } for (int i = 0; i < vendorSize; i++) { String vendorName = (String) vendorArray.get(i).get("vendor_name"); for (int j = 0; j < data.size(); j++) { if (vendorName.equals((String) data.get(j).get("vendor_name"))) { Map<String, Object> map = data.get(j); datas[i].add(map); } } } for (int k = 0; k < vendorSize; k++) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(name); sheet.createFreezePane(0, 1, 0, 1); //生成表头单元样式 HSSFCellStyle headerStyle = workbook.createCellStyle(); // headerStyle.setFillForegroundColor(HSSFColor..index); // headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); /* headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setBorderTop(CellStyle.BORDER_THIN);*/ headerStyle.setAlignment(CellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headerStyle.setWrapText(true); //生成一个字体 HSSFFont headerFont = workbook.createFont(); headerFont.setColor(HSSFColor.BLACK.index); headerFont.setFontHeightInPoints((short) 10); // headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName("宋体"); headerStyle.setFont(headerFont); //生成数据单元样式 HSSFCellStyle dataStyle = workbook.createCellStyle(); // ; /* dataStyle.setBorderBottom(CellStyle.BORDER_THIN); dataStyle.setBorderLeft(CellStyle.BORDER_THIN); dataStyle.setBorderRight(CellStyle.BORDER_THIN); dataStyle.setBorderTop(CellStyle.BORDER_THIN);*/ // dataStyle.setFillForegroundColor(HSSFColor.AQUA.index); dataStyle.setWrapText(true); dataStyle.setAlignment(CellStyle.ALIGN_CENTER); dataStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // dataStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); //生成另一个字体 HSSFFont dataFont = workbook.createFont(); //dataFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); dataFont.setColor(HSSFColor.BLACK.index); dataFont.setFontHeightInPoints((short) 10); //把字体应用到当前的样式 dataStyle.setFont(dataFont); //尾行样式 HSSFCellStyle footerStyle = workbook.createCellStyle(); footerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); footerStyle.setBorderBottom(CellStyle.BORDER_THIN); footerStyle.setBorderLeft(CellStyle.BORDER_THIN); footerStyle.setBorderRight(CellStyle.BORDER_THIN); footerStyle.setBorderTop(CellStyle.BORDER_THIN); footerStyle.setAlignment(CellStyle.ALIGN_CENTER); footerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //生成一个字体 HSSFFont footerFont = workbook.createFont(); footerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); footerStyle.setFont(footerFont); //生成表头 HSSFRow row = sheet.createRow(0); row.setHeight((short) (500)); Map<String, Integer> columnIndex = new HashMap<String, Integer>(); for (int i = 0; i < headers.length; i++) { //设置列宽 sheet.setColumnWidth((short) i, (short) (9 * 256)); //填充数据 HSSFCell cell = row.createCell((short) i); cell.setCellStyle(headerStyle); HSSFRichTextString text = new HSSFRichTextString(headers[i].getTitle()); cell.setCellValue(text); columnIndex.put(headers[i].getField(), i); } //生成数据 for (int i = 0; i < datas[k].size(); i++) { int excelIndex = i + 1; //实际EXCEL行号 HSSFRow dataRow = sheet.createRow(excelIndex); dataRow.setHeight((short) (500)); Map map = datas[k].get(i); for (int h = 0; h < headers.length; h++) { HSSFCell cell = dataRow.createCell((short) h); cell.setCellStyle(dataStyle); Object cellValue = headers[h].getFormatter().formatter(map.get(headers[h].getField()), excelIndex); dataProcessing(cell, cellValue); } } ZipEntry entry = new ZipEntry(vendorArray.get(k).get("vendor_name") + ".xls"); zipOutputStream.putNextEntry(entry); workbook.write(zipOutputStream); } zipOutputStream.flush(); zipOutputStream.close(); } public void cpInfoExportExcel(String fileName,List<Map<String, Object>> data, Columns[] headers,HttpServletResponse response)throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(name); sheet.createFreezePane( 0, 1, 0, 1 ); //生成表头单元样式 HSSFCellStyle headerStyle = workbook.createCellStyle(); // headerStyle.setFillForegroundColor(HSSFColor..index); // headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); /* headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setBorderTop(CellStyle.BORDER_THIN);*/ headerStyle.setAlignment(CellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headerStyle.setWrapText(true); //生成一个字体 HSSFFont headerFont = workbook.createFont(); headerFont.setColor(HSSFColor.BLACK.index); headerFont.setFontHeightInPoints((short) 10); // headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName("宋体"); headerStyle.setFont(headerFont); //生成数据单元样式 HSSFCellStyle dataStyle = workbook.createCellStyle(); // ; /* dataStyle.setBorderBottom(CellStyle.BORDER_THIN); dataStyle.setBorderLeft(CellStyle.BORDER_THIN); dataStyle.setBorderRight(CellStyle.BORDER_THIN); dataStyle.setBorderTop(CellStyle.BORDER_THIN);*/ // dataStyle.setFillForegroundColor(HSSFColor.AQUA.index); dataStyle.setWrapText(true); dataStyle.setAlignment(CellStyle.ALIGN_CENTER); dataStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // dataStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); //生成另一个字体 HSSFFont dataFont = workbook.createFont(); //dataFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); dataFont.setColor(HSSFColor.BLACK.index); dataFont.setFontHeightInPoints((short) 10); //把字体应用到当前的样式 dataStyle.setFont(dataFont); //尾行样式 HSSFCellStyle footerStyle = workbook.createCellStyle(); footerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); footerStyle.setBorderBottom(CellStyle.BORDER_THIN); footerStyle.setBorderLeft(CellStyle.BORDER_THIN); footerStyle.setBorderRight(CellStyle.BORDER_THIN); footerStyle.setBorderTop(CellStyle.BORDER_THIN); footerStyle.setAlignment(CellStyle.ALIGN_CENTER); footerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //生成一个字体 HSSFFont footerFont = workbook.createFont(); footerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); footerStyle.setFont(footerFont); //生成表头 HSSFRow row = sheet.createRow(0); row.setHeight((short)(500)); Map<String, Integer> columnIndex = new HashMap<String, Integer>(); for (int i=0;i<headers.length;i++) { //设置列宽 sheet.setColumnWidth((short)i, (short)(9*256)); //填充数据 HSSFCell cell = row.createCell((short)i); cell.setCellStyle(headerStyle); HSSFRichTextString text = new HSSFRichTextString(headers[i].getTitle()); cell.setCellValue(text); columnIndex.put(headers[i].getField(), i); } //生成数据 for(int i=0;i<data.size();i++){ int excelIndex = i+1; //实际EXCEL行号 HSSFRow dataRow = sheet.createRow(excelIndex); dataRow.setHeight((short)(500)); Map map = data.get(i); for (int h=0;h<headers.length;h++) { HSSFCell cell = dataRow.createCell((short)h); cell.setCellStyle(dataStyle); Object cellValue = headers[h].getFormatter().formatter(map.get(headers[h].getField()), excelIndex); dataProcessing(cell, cellValue); } } response.setContentType("application/vnd.ms-excel"); response.setHeader( "Content-Disposition", "attachment;filename=\""+ new String( fileName.getBytes( "gb2312" ), "ISO8859-1" )+ ".xls" + "\"" ); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); }}