Java excel 操作

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<T> {
 
 public void exportExcel(Collection<T> dataset,OutputStream out){
  exportExcel("测试POI导出excel文档",null,dataset,out,"yyyy-MM-dd");
 }

 public  void exportExcel(String[] headers,Collection<T> dataset, OutputStream out) {
  exportExcel("测试POI导出excel文档",headers,dataset,out,"yyyy-MM-dd");
   
 }
   
 public void exportExcel(String[] headers,Collection<T> dataset,OutputStream out,String pattern){
  exportExcel("测试POI导出excel文档",headers,dataset,out,"yyyy-MM-dd");
 
 }
 @SuppressWarnings("unchecked")
 public void exportExcel(String title,String[] headers,Collection<T> 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<headers.length;i++){
   
   HSSFCell cell=row.createCell(i);
   cell.setCellStyle(style);
   cell.setCellValue(new HSSFRichTextString(headers[i]));
  }
  //遍历集合数据，产生数据行
  Iterator<T> 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<fields.length;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<Student> ex=new ExportExcel<Student>();
  String[] headers={"学号","姓名","年龄","性别","出生日期"};
  List<Student> dataset=new ArrayList<Student>();
  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<Book> ex_book=new ExportExcel<Book>();
  String[] headers_book={"图书编号","图书名称","图书作者","图书价格","图书ISBN","图书出版社","封面图片"};
  List<Book> dataset_book=new ArrayList<Book>();
  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<Book> export_book=new ExportExcel<Book>();
  String[] headers_book={"图书编号","图书名称","图书作者","图书价格","图书ISBN","图书出版社","封面图片"};
  List<Book> dataset_book=new ArrayList<Book>();
  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();
  }
 }
}