POI生成excel报表

转载请注明:来自http://blog.youkuaiyun.com/M_ChangGong/作者:张燕广

本文旨在简单介绍在项目中加入POI，利用POI生成excel报表。

1．创建一个java工程或者web工程。

2．把POI的jar文件poi-3.5-beta5-20090219.jar（非本版本亦可）加到项目类路径下。

3．编写导出excel文件的类，代码如下:

package com.ctcmctv.poi; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.Region; public class TestExport implements ExportExcelReport { private static final long serialVersionUID = 1L; private HSSFWorkbook wb = null; private HSSFSheet sheet = null; private List<Student> studentList = null; /** * 实现接口中的方法，必要的时候可以进行扩展 */ public void MyDoExport(OutputStream out) { doExport(out); } /** * 执行导出方法 */ public void doExport(OutputStream out){ try { wb = new HSSFWorkbook(); sheet = wb.createSheet(); wb.setSheetName(0, "学生信息"); studentList = getStudentList(); createSheet(out, sheet, studentList); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } } /** * 创建excel工作薄 */ @Deprecated public void createSheet(OutputStream out, HSSFSheet sheet, List<Student> sortList) { try { int rowNum = 0; // 标题开始行 int colNum = 0; // 标题开始列 HSSFRow row = null; HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 定义时段单元格列头内容的显示格式 HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont bold1 = wb.createFont(); bold1.setFontName("黑体"); bold1.setFontHeightInPoints((short) 12); bold1.setBoldweight((short) 50); headerStyle.setFont(bold1); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); //基本数据样式设置字体 cellStyle.setFont(bold1); //定义十堰自办频道显示格式 HSSFCellStyle syCellStyle = wb.createCellStyle(); syCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); syCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); syCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); syCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); syCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); syCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont syBold = wb.createFont(); syBold.setFontName("黑体"); syBold.setFontHeightInPoints((short) 12); syBold.setBoldweight((short) 50); syBold.setColor(HSSFFont.COLOR_RED); syCellStyle.setFont(syBold); //列头名称数组 String[] titles = {"班级","学生编号","学生姓名","学生年龄"}; HSSFCell cell = null; row = sheet.createRow(rowNum++); for (int k = 0; k < titles.length; k++) { cell = row.createCell((short) colNum++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString(titles[k])); } //将学生信息写入excel文件中 for(Student stu : studentList){ row = sheet.createRow(rowNum++); colNum=0; cell = row.createCell((short) colNum++); cell.setCellStyle(cellStyle); cell.setCellValue(new HSSFRichTextString(stu.getClassName())); cell = row.createCell((short) colNum++); cell.setCellStyle(cellStyle); cell.setCellValue(new HSSFRichTextString(stu.getStuNo())); cell = row.createCell((short) colNum++); cell.setCellStyle(cellStyle); cell.setCellValue(new HSSFRichTextString(stu.getStuName())); cell = row.createCell((short) colNum++); cell.setCellStyle(cellStyle); cell.setCellValue(new HSSFRichTextString(String.valueOf(stu.getStuAge()))); } int rowFrom = 1; int rowTo = studentList.size(); short colFrom = 0; short colTo = 0; sheet.addMergedRegion(new Region(rowFrom,colFrom,rowTo,colTo)); sheet.setHorizontallyCenter(true); } catch (Exception e) { e.printStackTrace(); } } /** * 模拟数据库获取数据 * @return */ private List<Student> getStudentList(){ List<Student> stuList = new ArrayList(); for(int i=0;i<9;i++){ Student stu = new Student(); stu.setStuNo("20040103010"+(i+1)); stu.setStuName("Changong"+i); stu.setStuAge(20+i); stu.setClassName("04计1"); stuList.add(stu); } return stuList; } } class Student{ private String stuNo; private String stuName; private int stuAge; private String className; public String getStuNo() { return stuNo; } public void setStuNo(String stuNo) { this.stuNo = stuNo; } public String getStuName() { return stuName; } public void setStuName(String stuName) { this.stuName = stuName; } public int getStuAge() { return stuAge; } public void setStuAge(int stuAge) { this.stuAge = stuAge; } public String getClassName() { return className; } public void setClassName(String className) { this.className = className; } }

 

4．编写测试类（当然也可以用junit），此处没有使用junit，仅仅编写了一个普通java类，代码如下：

package com.ctcmctv.test; import java.io.FileOutputStream; import java.io.OutputStream; import com.ctcmctv.poi.ExportExcelReport; import com.ctcmctv.poi.TestExport; public class Test { public static void main(String[] args) { ExportExcelReport exportExcelReport = new TestExport(); String path = "d://test//04计1.xls"; OutputStream fileOut; try { fileOut = new FileOutputStream(path); exportExcelReport.MyDoExport(fileOut); } catch (Exception e1) { e1.printStackTrace(); } } }

5．生成excel报表如下图所示：