从数据库导出数据到excel之POI操作

项目说明：　　

1：数据库中有两张表，主键关联

2：根据条件查询数据

3：处理为需要的数据封装类型，然后传到导出excel的方法中

<--框架部署就不详谈了，用的spring框架-->

补充：POI详解：http://www.cnblogs.com/huajiezh/p/5467821.html

　　　POI中设置Excel单元格格式样式（居中，字体，边框，背景色、列宽、合并单元格等）　

直接上代码：首先是数据的获取，这里只上控制层代码，底层就不多说了

导入的包：

import java.io.BufferedOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Iterator;

import javax.servlet.http.HttpServletResponse;

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;

 

实体类代码：（导出的类型）

public class ExportDateTest implements Serializable{
    private String name;
    //private String gender;//性别
    private String weight;
    //private String grades;//班级
    private Double Networkprotocol;
    private Double javaEE;
    private Double Computerbasis;
    private Double Linuxoperatingsystem;
    private Double networksecurity;
    private Double SQLdatabase;
    private Double datastructure;
    public ExportDateTest() {
    
        // TODO Auto-generated constructor stub
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    /*
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    */
    public String getWeight() {
        return weight;
    }
    public void setWeight(String weight) {
        this.weight = weight;
    }
    /*
    public String getGrades() {
        return grades;
    }
    public void setGrades(String grades) {
        this.grades = grades;
    }
    */
    public Double getNetworkprotocol() {
        return Networkprotocol;
    }
    public void setNetworkprotocol(Double networkprotocol) {
        Networkprotocol = networkprotocol;
    }
    public Double getJavaEE() {
        return javaEE;
    }
    public void setJavaEE(Double javaEE) {
        this.javaEE = javaEE;
    }
    public Double getComputerbasis() {
        return Computerbasis;
    }
    public void setComputerbasis(Double computerbasis) {
        Computerbasis = computerbasis;
    }
    public Double getLinuxoperatingsystem() {
        return Linuxoperatingsystem;
    }
    public void setLinuxoperatingsystem(Double linuxoperatingsystem) {
        Linuxoperatingsystem = linuxoperatingsystem;
    }
    public Double getNetworksecurity() {
        return networksecurity;
    }
    public void setNetworksecurity(Double networksecurity) {
        this.networksecurity = networksecurity;
    }
    public Double getSQLdatabase() {
        return SQLdatabase;
    }
    public void setSQLdatabase(Double sQLdatabase) {
        SQLdatabase = sQLdatabase;
    }
    public Double getDatastructure() {
        return datastructure;
    }
    public void setDatastructure(Double datastructure) {
        this.datastructure = datastructure;
    }
    public ExportDateTest(String name, String gender, String weight, String grades, Double networkprotocol, Double javaEE,
            Double computerbasis, Double linuxoperatingsystem, Double networksecurity, Double sQLdatabase,
            Double datastructure) {
        super();
        this.name = name;
        //this.gender = gender;
        this.weight = weight;
        //this.grades = grades;
        Networkprotocol = networkprotocol;
        this.javaEE = javaEE;
        Computerbasis = computerbasis;
        Linuxoperatingsystem = linuxoperatingsystem;
        this.networksecurity = networksecurity;
        SQLdatabase = sQLdatabase;
        this.datastructure = datastructure;
    }
    @Override
    public String toString() {
        return "ExportDate [name=" + name + ""
                //+ ", gender=" + gender + ""
                + ", weight=" + weight + ""
            //    + ", grades=" + grades
                + ", Networkprotocol=" + Networkprotocol + ", javaEE=" + javaEE + ", Computerbasis=" + Computerbasis
                + ", Linuxoperatingsystem=" + Linuxoperatingsystem + ", networksecurity=" + networksecurity
                + ", SQLdatabase=" + SQLdatabase + ", datastructure=" + datastructure + "]";
    }
    

    

}

控制层部分代码：

List<ExportDate> list=expot.GetStudentTest(gender.getGender());// 
System.out.println("listDate:"+list); 
//ExportExcelXSSF<ExportDate> ee= new ExportExcelXSSF<ExportDate>();
 ExportExcelHSSF<ExportDate> ee= new ExportExcelXSSF<ExportDate>();
 ExportExcelOutputStream ee=new ExportExcelOutputStream(); 

//String[] headers = { "姓名", "性别", "体重","班级","网络协议","javaEE","计算机基础","Linux操作系统","网络安全","sql数据库","数据结构" }; 
String[] headers = { "姓名","体重","网络协议","javaEE","计算机基础","Linux操作系统","网络安全","sql数据库","数据结构" }; 
String fileName = "信息表"; System.out.println(); 
ee.exportExcel(list, headers,fileName, response);

关键的导出代码：

public class ExportExcelHSSFTest<T> {
     public void exportExcel(String[] headers,Collection<T> dataset, String fileName,HttpServletResponse response) {
            // 声明一个工作薄  
            HSSFWorkbook workbook = new HSSFWorkbook();  
            // 生成一个表格  
            HSSFSheet sheet = workbook.createSheet(fileName);
            //样式对象
            HSSFCellStyle style=workbook.createCellStyle();                            
            // 设置表格默认列宽度为15个字节  
            sheet.setDefaultColumnWidth(15);  
            // 产生表格标题行  
            HSSFRow row = sheet.createRow(0);  //设置行高            
                row.setHeightInPoints(30);//设置行高    
            for (int i = 0; i < headers.length; i++) {                         
                HSSFCell cell=row.createCell(i);
                //设置背景
                style.setFillBackgroundColor((short)13);
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                //设置字体
                HSSFFont font2 = workbook.createFont();    
                font2.setFontName("仿宋_GB2312");    
                font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示    
                font2.setFontHeightInPoints((short) 12);  //字体大小              
                font2.setColor(HSSFColor.RED.index);//设置字体颜色
                style.setFont(font2);//选择需要用到的字体格式                               
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellStyle(style);
                cell.setCellValue(text);               
            }  
            try {  
                // 遍历集合数据，产生数据行  
                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 < headers.length; i++) {                          
                        HSSFCell cell = row.createCell(i);  
                        Field field = fields[i];                      
                        String fieldName = field.getName();                          
                        String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);  
                        Class tCls = t.getClass();  
                        Method getMethod = tCls.getMethod(getMethodName, new Class[] {});  
                        Object value = getMethod.invoke(t, new Object[] {});  
                        // 判断值的类型后进行强制类型转换  
                        String textValue = null;  
                        // 其它数据类型都当作字符串简单处理  
                        if(value != null && value != ""){  
                            textValue = value.toString();  
                        }  
                        if (textValue != null) {  
                            HSSFRichTextString richString = new HSSFRichTextString(textValue);  
                            cell.setCellValue(richString);  
                        }  
                    }  
                }  
                getExportedFile(workbook, fileName,response);  
            } catch (Exception e) {  
                e.printStackTrace();  
            }   
        }            
        /** 
         *  
         * 方法说明: 指定路径下生成EXCEL文件 
         * @return 
         */  
        public void getExportedFile(HSSFWorkbook workbook, String name,HttpServletResponse response) throws Exception {  
            System.out.println("name:"+name);
            BufferedOutputStream fos = null;  
            try {  
                String fileName = name + ".xls";  
                response.setContentType("application/x-msdownload");  
                response.setHeader("Content-Disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ));  
                fos = new BufferedOutputStream(response.getOutputStream());  
                workbook.write(fos);  
            } catch (Exception e) {  
                e.printStackTrace();  
            } finally {  
                if (fos != null) {  
                    System.out.println("ok");
                    fos.close();  
                }  
            }  
        }  
      
    }  

 下一篇：从数据库导出数据到excel之List<Map<String,Object>>

下下篇：从数据库导出数据到excel之List<List<Object>>