Jakata Poi HSSF:纯java的Excel解决方案总结 作者: jfish 发表日期: 2006-01-14 16:28 文章属性: 原创 复制链接 |
Jakata Poi HSSF:纯java的Excel解决方案总结 1.背景 Jakarta POI是apache的子项目,目标是处理ole2对象。目前比较成熟的是HSSF接口,处理MS Excel(97-2002)对象。它不象我们仅仅是用csv生成的没有格式的可以由Excel转换的东西,而是真正的Excel对象,你可以控制一些属性如sheet,cell等等。 2.HSSFWorkbook excell文档对象介绍 HSSFSheet excell的表单 HSSFRow excell的行 HSSFCell excell的格子单元 HSSFFont excell字体 HSSFName 名称 HSSFDataFormat 日期格式 在poi1.7中才有以下2项: HSSFHeader sheet头 HSSFFooter sheet尾 和这个样式 HSSFCellStyle cell样式 辅助操作包括 HSSFDateUtil 日期 HSSFPrintSetup 打印 HSSFErrorConstants 错误信息表 3.org.apache.poi.hssf包的结构图 图1 基本对象 从中不难可以发现每一个基本对象都关联了一个Record对象。Record对象是一个参考Office格式的相关记录。 图2 HSSFWorkbook HSSFWorkbook即是一个Excell对象。这幅类图体现的是HSSFWorkbook和基本对象的相互关系。可见,许多对象中也建立了Workbook的引用。还需要注意的是在HSSFWorkbook和HSSFSheet中建立了log机制POILogger,而且POILogger也是使用apache Log4J实现的。 4. struts+poi UserExcelViewAction.java package net.pms.web.action; import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import net.pms.model.PmsUser; import net.pms.service.UserManager; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFHeader; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.struts.action.Action; import org.apache.struts.action.ActionForm; import org.apache.struts.action.ActionForward; import org.apache.struts.action.ActionMapping; public class UserExcelViewAction extends Action { private static final String CONTENT_TYPE = "application/vnd.ms-excel"; private UserManager mgr = null; private PmsUser b = null; public void setUserManager(UserManager userManager) { this.mgr = userManager; } public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { response.setContentType(CONTENT_TYPE); try { try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); wb.setSheetName(0, "用户报表", HSSFWorkbook.ENCODING_UTF_16); HSSFHeader header = s.getHeader(); header.setCenter("用户报表"); HSSFRow row = s.createRow(0); HSSFCell cell = null; // 编号 cell = row.createCell((short) 0); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("编号"); // 登陆名 cell = row.createCell((short) 1); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("登陆名"); // 姓名 cell = row.createCell((short) 2); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("姓名"); // 部门 cell = row.createCell((short) 3); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("部门"); // 职务 cell = row.createCell((short) 4); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("职务"); // 创建时间 HSSFCell c5 = row.createCell((short) 5); c5.setEncoding(HSSFCell.ENCODING_UTF_16); c5.setCellValue("创建时间"); // List list = mgr .getPmsUsers("from PmsUser p where p.active='Y'"); for (short i = 0; i < list.size(); i++) { b = (PmsUser) list.get(i); row = s.createRow(i + 1); if (b != null) { row.createCell((short) 0).setCellValue(b.getUserid()); row.createCell((short) 1) .setCellValue(b.getLoginname()); cell = row.createCell((short) 2); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(b.getName()); cell = row.createCell((short) 3); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(b.getPmsDept().getName()); cell = row.createCell((short) 4); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(b.getPmsDuty().getName()); // 使用样式 HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat .getBuiltinFormat("m/d/yy h:mm")); cell = row.createCell((short) 5); cell.setCellStyle(cellStyle); cell.setCellValue(b.getTs()); } } s.setGridsPrinted(true); ServletOutputStream os = response.getOutputStream(); wb.write(os); os.flush(); os.close(); } catch (Exception e) { System.out.println("error in JSP"); } return null; } catch (Throwable e) { e.printStackTrace(); } return new ActionForward(mapping.getInput()); } } 5. spring+poi UserExcelView.java package net.pms.web.action; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import net.pms.model.PmsUser; import net.pms.service.UserManager; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.web.servlet.view.document.AbstractExcelView; public class UserExcelView extends AbstractExcelView { private PmsUser user = null; private UserManager mgr = null; public void setUserManager(UserManager userManager) { this.mgr = userManager; } @Override protected void buildExcelDocument(Map model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.createSheet("My users"); sheet.setDefaultColumnWidth((short) 12); List users = mgr.getPmsUsers("from PmsUser p where p.active='Y'"); for (int i = 0; i < users.size(); i++) { user = (PmsUser) users.get(i); HSSFCell cell = getCell(sheet, i, 0); setText(cell, user.getUserid()); } } } 附:POI主页:http://jakarta.apache.org/poi/,初学者如何快速上手使用POI HSSF:http://jakarta.apache.org/poi/hssf/quick-guide.html。 下载网址:http://www.apache.org/dyn/closer.cgi/jakarta/poi/,下载poi-bin-2.5.1-final-20040804.zip。 参考文章:简单EXCEL报表方案:Spring+POI http://www.blogjava.net/beyondduke/archive/2005/12/15/23978.aspx Jakata Poi HSSF:纯java的Excel解决方案 http://www.suiyuanwu.com/detail.asp?id=1188 |
Jakata Poi HSSF:纯java的Excel解决方案总结
最新推荐文章于 2020-05-05 15:15:25 发布