java excel报表文件_java POI导出Excel报表

package com.venustech.custom.utils;

import java.io.BufferedInputStream;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.OutputStream;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Collection;

import java.util.Date;

import java.util.Iterator;

import java.util.List;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;

import org.apache.poi.hssf.usermodel.HSSFComment;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFPatriarch;

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 com.venustech.custom.bean.Book;

import com.venustech.custom.bean.Student;

public class ExportExcel {

public void exportExcel(Collection dataset,OutputStream out){

exportExcel("测试POI导出excel文档",null,dataset,out,"yyyy-MM-dd");

}

public  void exportExcel(String[] headers,Collection dataset, OutputStream out) {

exportExcel("测试POI导出excel文档",headers,dataset,out,"yyyy-MM-dd");

}

public void exportExcel(String[] headers,Collection dataset,OutputStream out,String pattern){

exportExcel("测试POI导出excel文档",headers,dataset,out,"yyyy-MM-dd");

}

@SuppressWarnings("unchecked")

public void exportExcel(String title,String[] headers,Collection dataset,OutputStream out,String pattern){

//声明一个工作薄

HSSFWorkbook workbook=new HSSFWorkbook();

//生成一个表格

HSSFSheet sheet=workbook.createSheet(title);

//设置表格默认宽度为15个字节

sheet.setDefaultColumnWidth(15);

//生成一个样式

HSSFCellStyle style=workbook.createCellStyle();

//设置这些样式

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(HSSFCellStyle.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 style_ =workbook.createCellStyle();

style_.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

style_.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

style_.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style_.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style_.setBorderRight(HSSFCellStyle.BORDER_THIN);

style_.setBorderTop(HSSFCellStyle.BORDER_THIN);

style_.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style_.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

//生成另一个字体

HSSFFont font_=workbook.createFont();

//font.setColor(color);

//font.setFontHeightInPoints(height);

font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

style_.setFont(font_);

//生成一个画图的顶级管理器

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(int i=0;i

HSSFCell cell=row.createCell(i);

cell.setCellStyle(style);

cell.setCellValue(new HSSFRichTextString(headers[i]));

}

//遍历集合数据，产生数据行

Iterator it=dataset.iterator();

int index=0;

while(it.hasNext()){

index++;

row=sheet.createRow(index);

T t=(T)it.next();

//利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值

Field[] fields=t.getClass().getDeclaredFields();

for(int i=0;i

HSSFCell cell=row.createCell(i);

cell.setCellStyle(style_);

Field field=fields[i];

String fieldName=field.getName();

String getMethodName="get"+fieldName.substring(0,1).toUpperCase()+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 Boolean){

boolean bValue=(Boolean) value;

textValue="男";

if(!bValue){

textValue="女";

}

}else if (value instanceof Date) {

Date date=(Date)value;

SimpleDateFormat sdf=new SimpleDateFormat(pattern);

textValue=sdf.format(date);

}else if (value instanceof byte[]) {

//有图片时，设置行高为60px

row.setHeightInPoints(60);

//设置图片所在列宽度为80px，注意这里单位的一个

sheet.setColumnWidth(i,(short)(35.7*80));

byte[] bsValue=(byte[])value;

HSSFClientAnchor anchor=new HSSFClientAnchor(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){

Pattern p=Pattern.compile("^\\d+(\\.\\d+)?$");

Matcher matcher = p.matcher(textValue);

if(matcher.matches()){

//是数字当作double处理

cell.setCellValue(Double.parseDouble(textValue));

}else{

HSSFRichTextString richString=new HSSFRichTextString(textValue);

HSSFFont _font=workbook.createFont();

_font.setColor(HSSFColor.BLUE.index);

richString.applyFont(_font);

cell.setCellValue(richString);

}

}

}catch(Exception e){

e.printStackTrace();

}

}

}

try{

workbook.write(out);

}catch(Exception e){

e.printStackTrace();

}

}

/**

* @param args

*/

public static void main(String[] args) {

ExportExcel ex=new ExportExcel();

String[] headers={"学号","姓名","年龄","性别","出生日期"};

List dataset=new ArrayList();

dataset.add(new Student(10000001L,"张三",20, true,new Date()));

dataset.add(new Student(10000002L,"李四",21, false,new Date()));

dataset.add(new Student(10000003L,"王五",23, true,new Date()));

ExportExcel ex_book=new ExportExcel();

String[] headers_book={"图书编号","图书名称","图书作者","图书价格","图书ISBN","图书出版社","封面图片"};

List dataset_book=new ArrayList();

try{

BufferedInputStream bis=new BufferedInputStream(new FileInputStream("book.jpg"));

byte[] buf=new byte[bis.available()];

while(bis.read(buf)!=-1){

}

dataset_book.add(new Book(1,"jsp","leno",300.33f,"1234567","清华出版社",buf));

dataset_book.add(new Book(2,"java 编程思想","leno",300.33f,"1234567","清华出版社",buf));

dataset_book.add(new Book(3,"C++","leno",300.33f,"1234567","清华出版社",buf));

dataset_book.add(new Book(4,"C#入门 编程思想","leno",300.33f,"1234567","清华出版社",buf));

dataset_book.add(new Book(5,"Flex3.0入门","leno",300.33f,"1234567","清华出版社",buf));

dataset_book.add(new Book(6,"java与模式","leno",300.33f,"1234567","清华出版社",buf));

OutputStream out=new FileOutputStream("c:\\a.xls");

OutputStream out2=new FileOutputStream("c:\\b.xls");

ex.exportExcel(headers,dataset, out);

ex_book.exportExcel(headers_book, dataset_book, out2);

out.close();

JOptionPane.showMessageDialog(null, "导出成功！");

System.out.println("xls导出成功！");

}catch(Exception e){

e.printStackTrace();

}

}

}

package com.venustech.custom.servlet;

import java.io.BufferedInputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.util.ArrayList;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.venustech.custom.bean.Book;

import com.venustech.custom.utils.ExportExcel;

public class ExportExcelServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

this.doPost(request, response);

}

public void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

File file=new File(getServletContext().getRealPath(("WEB-INF/book.jpg")));

response.setContentType("octets/stream");

response.addHeader("Content-Disposition", "attachment;filename=test.xls");

ExportExcel export_book=new ExportExcel();

String[] headers_book={"图书编号","图书名称","图书作者","图书价格","图书ISBN","图书出版社","封面图片"};

List dataset_book=new ArrayList();

try{

BufferedInputStream bis=new BufferedInputStream(new FileInputStream(file));

byte[] buf=new byte[bis.available()];

while(bis.read(buf)!=-1){

//将图片数据存放到缓冲数组中

}

dataset_book.add(new Book(1,"jsp","leno",300.33f,"1234567","清华出版社",buf));

dataset_book.add(new Book(2,"java 编程思想","leno",300.33f,"1234567","清华出版社",buf));

dataset_book.add(new Book(3,"C++","leno",300.33f,"1234567","清华出版社",buf));

dataset_book.add(new Book(4,"C#入门 编程思想","leno",300.33f,"1234567","清华出版社",buf));

dataset_book.add(new Book(5,"Flex3.0入门","leno",300.33f,"1234567","清华出版社",buf));

dataset_book.add(new Book(6,"java与模式","leno",300.33f,"1234567","清华出版社",buf));

OutputStream out=response.getOutputStream();

export_book.exportExcel(headers_book, dataset_book, out);

out.close();

System.out.println("excel导出成功！");

}catch(Exception e){

e.printStackTrace();

}

}

}