1、import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;/* * 利用开源组件POI3.0.2动态导出EXCEL文档 * * version v1.0 * param * 应用泛型,代表任意一个符合javabean风格的类 * 注意这里为了简单起见,boolean型的属性xxx的get器方式为getXxx(),而不是isXxx() * byte表jpg格式的图片数据 */public class ExportExcel public void exportExcel(
2、Collection dataset, OutputStream out) exportExcel(测试POI导出EXCEL文档, null, dataset, out, yyyy-MM-dd); public void exportExcel(Map headers, Collection dataset, OutputStream out) , headers, dataset, out, OutputStream out, String pattern) , headers, dataset, out, pattern); /* * 这是一个通用的方法,利用了JAVA的反射机制,可以将放
3、置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 * param title * 表格标题名 * param headers * 表格属性列名数组 * param dataset * 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 * javabean属性的数据类型有基本数据类型及String,Date,byte(图片数据) * param out * 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中 * param pattern * 如果有时间数据,设定输出格式。默认为yyy-MM-dd Suppre
4、ssWarnings(unchecked) public void exportExcel(String title, Map headers, Collection / 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); / 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); / 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(15); / 生成一个样式 HSSFCellStyle style = workbook.createCellStyle
5、(); / 设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellSty
6、le.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); / 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints(short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); / 把字体应用到当前的样式 style.setFont(font); / 生成并设置另一个样式 HSSFCellStyle s
7、tyle2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN)
8、; style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); / 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); style2.setFont(font2); / 声明一个画图的顶级管理器 HSSF
9、Patriarch 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(sys
10、tem / 产生表格标题行 HSSFRow row = sheet.createRow(0); Iterator iter = headers.entrySet().iterator(); int i = 0; while (iter.hasNext() Map.Entry entry = (Map.Entry) iter.next(); / String key = (String) entry.getKey(); String val = (String) entry.getValue(); HSSFCell cell = row.createCell(i); cell.setCellSt
11、yle(style); HSSFRichTextString text = new HSSFRichTextString(val); cell.setCellValue(text); i+; / 遍历集合数据,产生数据行 Iterator it = dataset.iterator(); int index = 0; while (it.hasNext() index+; row = sheet.createRow(index); T t = (T) it.next(); / 利用反射,根据headersd的kay,动态调用getXxx()方法得到属性值 int j = -1; Iterato
12、r iterk = headers.entrySet().iterator(); while (iterk.hasNext() Map.Entry entry = (Map.Entry) iterk.next(); String key = (String) entry.getKey(); String fieldName = key; j+; HSSFCell cell = row.createCell(j); cell.setCellStyle(style2); String getMethodName = get + fieldName.substring(0, 1).toUpperCa
13、se() + fieldName.substring(1); try Class tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName, new Class ); Object value = getMethod.invoke(t, new Object ); / 判断值的类型后进行强制类型转换 String textValue = null; if (value instanceof Date) Date date = (Date) value; SimpleDateFormat sdf = new Simp
14、leDateFormat(pattern); textValue = sdf.format(date); else if (value instanceof byte) / 有图片时,设置行高为60px; row.setHeightInPoints(60); / 设置图片所在列宽度为80px,注意这里单位的一个换算 sheet.setColumnWidth(i, (short) (35.7 * 80); / sheet.autoSizeColumn(i); byte bsValue = (byte) value; HSSFClientAnchor anchor = new HSSFClient
15、Anchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index); anchor.setAnchorType(2); patriarch.createPicture(anchor, workbook.addPicture( bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG); else / 其它数据类型都当作字符串简单处理 textValue = value.toString(); / 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 if (textValue != null) Pa
16、ttern p = Ppile(/d+(/./d+)?$ Matcher matcher = p.matcher(textValue); if (matcher.matches() / 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue); else HSSFRichTextString richString = new HSSFRichTextString( textValue); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLUE.i
17、ndex); richString.applyFont(font3); cell.setCellValue(richString); catch (SecurityException e) / TODO Auto-generated catch block e.printStackTrace(); catch (NoSuchMethodException e) catch (IllegalArgumentException e) catch (IllegalAccessException e) catch (InvocationTargetException e) finally / 清理资源
18、 try workbook.write(out); catch (IOException e) / TODO Auto-generated catch block e.printStackTrace(); public static void main(String args) / 测试学生 ExportExcel ex = new ExportExcel(); Map headers = new LinkedHashMap headers.put(id, 学号name姓名age年龄sex性别birthday出生日期 List dataset = new ArrayList dataset .
19、add(new Student(10000001张三20true2013-9-820000002李四24false, 2014-9-930000003王五222014-9-19 OutputStream out = new FileOutputStream(E:/a.xls ex.exportExcel(headers, dataset, out); out.close(); JOptionPane.showMessageDialog(null, 导出成功! System.out.println(excel导出成功! catch (FileNotFoundException e) PoiExc
20、elExample servlet 类:import java.io.*;import javax.servlet.ServletException;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse; * POI 导出 Excel 事例public class PoiExcelExample extends javax.servlet.http.HttpServlet static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException response.setContentType(octets/stream response.addHeader(Content-Dispositionattachment;filename=test.xls / 测试学生 ExportExcel dataset.add(new Student(,
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1