项目说明：
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();
}
}
}
}