HardBirch

POI生成excel报表

时间:09-06-10 栏目:iOS移动应用开发技术 作者:张飞不张,文采横飞 评论:0 点击: 1,636 次

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

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

2.POIjar文件poi-3.5-beta5-20090219.jar(非本版本亦可)加到项目类路径下。

3.编写导出excel文件的类,代码如下:

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类,代码如下:

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报表如下图所示:

声明: 本文由( 张飞不张,文采横飞 )原创编译,转载请保留链接: POI生成excel报表

POI生成excel报表:等您坐沙发呢!

发表评论


QQ群互动

Linux系统与内核学习群:194051772

WP建站技术学习交流群:194062106

魔豆之路QR

魔豆的Linux内核之路

魔豆的Linux内核之路

优秀工程师当看优秀书籍

优秀程序员,要看优秀书!

赞助商广告

友荐云推荐