数据写入到excel中采用的是Apache POI：
//java后台的一个工具类（该工具类适用于为不同字段添加，方便）
/* 下面这个方法是将list转换为Excel工作表的 */
public static HSSFWorkbook getWorkbook(List<ExporTable> list)
throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
String[] columnNames;
String[] columnMethods;
// 首先，我们读取list中的第一个元素，根据它来确定工作表的列名，以及输出数据所对应的方法数组
ExporTable exp = list.get(0);
columnNames = exp.getColumnNames();
columnMethods = exp.getColumnMethods();
HSSFRow row = sheet.createRow(0); // 创建第1行，也就是输出表头
HSSFCell cell;
for (int i = 0; i < columnNames.length; i++) {
cell = row.createCell(i); // 创建第i列
cell.setCellValue(new HSSFRichTextString(columnNames[i]));
}
// 下面是输出各行的数据
for (int i = 0; i < list.size(); i++) {
exp = (ExporTable) list.get(i);
row = sheet.createRow(i + 1);// 创建第i+1行
for (int j = 0; j < columnMethods.length; j++) {
cell = row.createCell(j);// 创建第j列
Method method;
method = exp.getClass().getMethod(columnMethods[j]); // 这里用到了反射机制，通过方法名来取得对应方法返回的结果对象
Object obj = method.invoke(exp);
if (obj != null) {
cell.setCellValue(obj.toString());
}
}
}
return workbook;
}
/**
* 将ExportBean转换为Excel工作表
*
* @param exportBeans 集合，每个元素代表一个sheet
* @return
* @throws Exception
*/
public static HSSFWorkbook getWorkbooks(ExportBean<?>... exportBeans)
throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
for (ExportBean<?> eb : exportBeans) {
HSSFSheet sheet = workbook.createSheet(eb.getSheetName());
String[] headers = eb.getHeaders();
HSSFRow row = sheet.createRow(0); // 创建第1行，也就是输出表头
HSSFCell cell;
// 设置表头粗体样式
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle cellStyleHeader = workbook.createCellStyle();
cellStyleHeader.setFont(font);
for (int i = 0; i < headers.length; i++) {
// 设置列宽
sheet.setColumnWidth((short) i, 6000);
cell = row.createCell(i); // 创建第i列
cell.setCellStyle(cellStyleHeader);
cell.setCellValue(new HSSFRichTextString(headers[i]));
}
String[] getMethods = eb.getGetMethods();
List rows = eb.getRows();
// 下面是输出各行的数据
for (int i = 0; i < rows.size(); i++) {
Object rowData = rows.get(i);
row = sheet.createRow(i + 1);// 创建第i+1行
for (int j = 0; j < getMethods.length; j++) {
cell = row.createCell(j);// 创建第j列
// cell.setCellStyle(cellStyle);
Method method = null;
try {
method = rowData.getClass().getMethod(getMethods[j]); // 这里用到了反射机制，通过方法名来取得对应方法返回的结果对象
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
Object obj = method.invoke(rowData);
if (obj != null) {
// cell.setCellType(HSSFCell.);
if (obj instanceof java.util.Date) {
cell.setCellValue((java.util.Date) obj);
cell.setCellStyle(cellStyle);
} else if (obj instanceof BigDecimal) {
cell.setCellValue(((BigDecimal) obj).longValue());
} else {
cell.setCellValue(obj.toString());
}
}
}
}
}
return workbook;
}
// 将Workbook写入到InputStream
public static InputStream workbook2InputStream(HSSFWorkbook workbook,
ByteArrayOutputStream baos, String fileName) throws Exception {
workbook.write(baos);
baos.flush();
byte[] aa = baos.toByteArray();
InputStream excelStream = new ByteArrayInputStream(aa, 0, aa.length);
return excelStream;
}
// 将Workbook写入到byte数组
public static byte[] workbook2ByteArray(HSSFWorkbook workbook,
ByteArrayOutputStream baos) throws Exception {
workbook.write(baos);
baos.flush();
byte[] aa = baos.toByteArray();
return aa;
}
/**
* 考虑到多sheet情况暂时加个数据结构封装下吧
* @author 王云超
* 时间 2014-7-16 下午5:30:23
*/
public class ExportBean<T> {
private String sheetName ; //sheet名称
private String[] headers; //表头
private String[] getMethods; //方法集合
private List<T> rows; //数据行
public String[] getGetMethods() {
return getMethods;
}
public void setGetMethods(String[] getMethods) {
this.getMethods = getMethods;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public String[] getHeaders() {
return headers;
}
public void setHeaders(String[] headers) {
this.headers = headers;
}
public List<T> getRows() {
return rows;
}
public void setRows(List<T> rows) {
this.rows = rows;
}
}
//java代码：
public void DbToExcels(){
byte[] bytes = null;
try {
ExportBean<CirCuit> eb = new ExportBean<CirCuit>();
eb.setHeaders(new String[] { "id","名字","时间" });
eb.setGetMethods(new String[] {"getId","getCircuit_type","getSystem_id"});
List<CirCuit> list = cirCuitBo.findAll();
eb.setRows(list);
eb.setSheetName("信息");
HSSFWorkbook workbook = PoiUtil.getWorkbooks(eb);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
bytes = PoiUtil.workbook2ByteArray(workbook, baos);
} catch (Exception e) {
e.printStackTrace();
log.error("导出数据失败",e);
}
return bytes;
}
//flex代码：
Alert.show("是否要保存文件！","提示",Alert.YES|Alert.NO,null,function(evt){
if(evt.detail == Alert.YES){
var fileRef:FileReference = new FileReference();
fileRef.save(data.result,"信息表.xls");
}
});
