public class ExportExcel<T> {
public void exportExcel(Collection<T> dataset, OutputStream out) { // exportExcel("测试POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd"); } public static void main(String[] args) { try { String path2 = "d://123.png"; List<User> bs = new ArrayList<User>(); User u = new User(); u.setLoginName(QRCodeUtil.getRnNumber() + ""); u.setQrCodePath(path2); bs.add(u); bs.add(u); bs.add(u); bs.add(u); String path = "E://"; //导出excel OutputStream out2 = new FileOutputStream(path+QRCodeUtil.getRnNumber()+".xls"); String[] headers = {"用户名","二维码"}; List<String> exportFile = new ArrayList<String>(); exportFile.add("loginName");exportFile.add("qrCodePath");ExportExcel<User> export = new ExportExcel<User>();
export.exportExcel("二维码",headers, bs,exportFile, out2,"yyyy"); out2.close(); JOptionPane.showMessageDialog(null, "导出成功!"); System.out.println("excel导出成功！"); System.exit(0); } catch (Exception e) { e.printStackTrace(); } } /**往Excel中插入图片 * @param dataSheet 待插入的工作表 * @param col 图片从该列开始 * @param row 图片从该行开始 * @param width 图片所占的列数 * @param height 图片所占的行数 * @param imgFile 要插入的图片文件 */ public static void insertImg(WritableSheet dataSheet, int col, int row, int width, int height, File imgFile){ WritableImage img = new WritableImage(col, row, width, height, imgFile); dataSheet.addImage(img); }@SuppressWarnings("unchecked")
public void exportExcel(String title, String[] headers, List<T> dataset,List<String> exportFile, OutputStream out, String pattern) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); //产生表格标题行 HSSFRow row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } //遍历集合数据，产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; while (it.hasNext()) {index++; row = sheet.createRow(index); T t = (T) it.next(); int j = 0; for (short i = 0; i < exportFile.size(); i++) { try { String name = exportFile.get(i); Object value = BeanUtils.getProperty(t, name); HSSFCell cell = row.createCell(j); cell.setCellStyle(style2); j++; // 判断值的类型后进行强制类型转换 String textValue = null; /*if (value instanceof Date) { Date date = (Date) value; SimpleDateFormat sdf = new SimpleDateFormat(pattern); textValue = sdf.format(date); } else {*/ // 其它数据类型都当作字符串简单处理 if (null != value) textValue = value.toString(); //} // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成 if (textValue != null) { Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); }else if(name.equals("qrCodePath")){ //属性名为qrCodePath即为图片路径信息 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg = ImageIO.read(new File(textValue)); ImageIO.write(bufferImg, "PNG", byteArrayOut); // 有图片时，设置行高为327px; row.setHeightInPoints(327); // 设置图片所在列宽度为195px,注意这里单位的一个换算 sheet.setColumnWidth(i, (short) (40 * 195)); // sheet.autoSizeColumn(i); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 680, 95, (short) i, index, (short) i, index); HSSFPicture pic = patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); pic.resize(); }else { HSSFRichTextString richString = new HSSFRichTextString(textValue); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLACK.index); richString.applyFont(font3); cell.setCellValue(richString); } } } catch (Exception e) { e.printStackTrace(); } finally {}
} } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } } /** * 创建单个sheet * * @param sheetName * 导出的sheet名字 * @param headers * 列头 * @param dataset * 需要导出的数据 * @param exportFile * 需要的导出的字段 * @param response * @throws IOException */ public void exportExcel(String sheetName, String[] headers, Collection<T> dataset, List<String> exportFile, HttpServletResponse response) throws IOException { OutputStream out = null; response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("gbk");
response.setHeader("Content-disposition", "attachment;filename=" + new String(sheetName.getBytes("GBK"), "ISO-8859-1") + ".xls"); out = response.getOutputStream(); HSSFWorkbook workbook = new HSSFWorkbook(); createSheet(sheetName, headers, dataset, exportFile, out, "yyyy-MM-dd", workbook); try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } out.flush(); out.close(); }/**
* 创建多个sheet * * @param title * 文件名 * @param headerMap * 以sheet名为Key，列头为value * @param dataMap * 以sheet名为Key，数据集合为value * @param cellMap * 以sheet名为Key，数据列为value * @param response * @throws IOException */ public void exportExcel(String title, Map<String, Object> headerMap, Map<String, Object> dataMap, Map<String, Object> cellMap, HttpServletResponse response) throws IOException { OutputStream out = null; response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("gbk"); response.setHeader("Content-disposition", "attachment;filename=" + new String(title.getBytes("GBK"), "ISO-8859-1") + ".xls"); out = response.getOutputStream(); HSSFWorkbook workbook = new HSSFWorkbook(); for (String key : headerMap.keySet()) { String sheetName = key; String[] headers = (String[]) headerMap.get(key); Collection<T> dataSet = (Collection<T>) dataMap.get(key); List<String> exportFile = (List<String>) cellMap.get(key); createSheet(sheetName, headers, dataSet, exportFile, out, "yyyy-MM-dd HH：mm：ss", workbook); } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } out.flush(); out.close(); }/**
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 * * @param sheetName * 表格sheet名 * @param headers * 表格属性列名数组 * @param dataset * 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 * javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) * @param out * 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中 * @param pattern * 如果有时间数据，设定输出格式。默认为"yyy-MM-dd" * @param workbook * 声明一个工作薄 */ public void createSheet(String sheetName, String[] headers, Collection<T> dataset, List<String> exportFile, OutputStream out, String pattern, HSSFWorkbook workbook) { // 生成一个表格 HSSFSheet sheet = workbook.createSheet(sheetName); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); /* * // 定义注释的大小和位置,详见文档 HSSFComment comment = patriarch.createComment(new * HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 * comment.setString(new HSSFRichTextString("可以在POI中添加注释！")); // * 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容. comment.setAuthor("leno"); */ // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); // cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } // 遍历集合数据，产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; // 设置单元格内容靠上 HSSFCellStyle style = workbook.createCellStyle(); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); int j = 0; for (short i = 0; i < exportFile.size(); i++) { try { String name = exportFile.get(i); Object value = BeanUtils.getProperty(t, name); HSSFCell cell = row.createCell(j); cell.setCellStyle(style); j++; // 判断值的类型后进行强制类型转换 String textValue = null; /*if (value instanceof Date) { Date date = (Date) value; SimpleDateFormat sdf = new SimpleDateFormat(pattern); textValue = sdf.format(date); } else {*/ // 其它数据类型都当作字符串简单处理 if (null != value) textValue = value.toString(); //} // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成 if (textValue != null) { Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); }else if(name.equals("qrCodePath")){ //属性名为qrCodePath即为图片路径信息 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg = ImageIO.read(new File(textValue)); ImageIO.write(bufferImg, "PNG", byteArrayOut); // 有图片时，设置行高为327px; 调整高度为120 row.setHeightInPoints(120); // 设置图片所在列宽度为195px,注意这里单位的一个换算 sheet.setColumnWidth(i, (short) (40 * 195)); // sheet.autoSizeColumn(i); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 680, 95, (short) i, index, (short) i, index); HSSFPicture pic = patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); pic.resize(); } else { HSSFRichTextString richString = new HSSFRichTextString( textValue); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLACK.index); richString.applyFont(font3); cell.setCellValue(richString); } } } catch (Exception e) { e.printStackTrace(); } finally {}
} } }}