思路: 做法是从数据库中读取表格中的数据,然后通过POI组件将其组装成Excel文件,然后Flex通过navigateToURL()方式获取服务器请求地址,并组装数据供服务器端使用。就这么简单,但实现起来还是略微有点儿小麻烦。下面我详细介绍整个流程。
1.环境准备:
1.1添加POI组件依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
1.2 安装Flex Builder 4.6
默认安装即可(略),以插件形式安装至eclipse。
2.JAVA端组装数据
以下是业务方面的代码,不同的系统使用的框架略有不同。我这里使用的是spring 3.0 + springmvc+hibernate组合。代码如下所示:
2.1 Controller层代码
@RequestMapping(params ="action=exportExcel", method = {RequestMethod.GET, RequestMethod.POST, RequestMethod.HEAD})
public void excel(HttpServletRequest request, HttpServletResponse response) throws Exception{
String filename = "节点控制器.xls";
String isTemplate = request.getParameter("isTemplate");
int isTemplateNum = Integer.parseInt(isTemplate);
String concentUID = request.getParameter("concentUID");
String groupMaintainId = request.getParameter("groupMaintainId");
String titleLabel = "节点控制器";
HttpWebUtil.responseExportExcelConfig(response, filename);
try{
String[] titles = {"编号(必填)", "UID(必填)" , "名称(必填)" , "类型(必填)(1:单灯,2:双灯)", "经度(可不填)", "纬度(可不填)", "地理位置描述(可不填)", "备注(可不填)"};
List<NodeDevice> nodeDeviceList;
if(("0").equals(groupMaintainId)){
nodeDeviceList = nodeDeviceService.findAllNodeDeviceByConcentUid(concentUID);
titleLabel += "(" + "所属集中器'" + concentUID + "')";
}else{
Long groupId = Long.parseLong(groupMaintainId);
nodeDeviceList = nodeDeviceService.findAllNodeDeviceByGroupMaintainId(groupId);
GroupMaintainEntity entity = groupMaintainService.get(GroupMaintainEntity.class, groupId);
titleLabel += "(" + "所属分组'" + entity.getName() + "')";
}
Object[][] nodeDevice_datas = {{}};
if(nodeDeviceList != null && isTemplateNum != 1){
nodeDevice_datas = new Object[nodeDeviceList.size()][];
//包装数据
for (int i = 0 ; i < nodeDeviceList.size(); i++){
NodeDevice nodeDevice = nodeDeviceList.get(i);
Object [] itemObject = {nodeDevice.getId(), nodeDevice.getUid(), nodeDevice.getName(),
nodeDevice.getType(), nodeDevice.getLatitude(),nodeDevice.getLatitude(),
nodeDevice.getLocationDesc(), nodeDevice.getRemark()
};
nodeDevice_datas[i] = itemObject;
}
}
//导出数据至Excel表格中
ExcelFileAssembly.getInstance().assembleData("节点控制器数据", response, titleLabel, titles, nodeDevice_datas);
} catch (Exception e) {
e.printStackTrace();
}
}
2.2 Excel操作类(职责:组装数据,设定样式等)
POI学习地址: http://poi.apache.org/spreadsheet/quick-guide.html,如下所示:
/**
* Excel操作类(职责:组装数据,设定样式等)
* @author xuzhongming
* 2013-08-22
*/
public class ExcelFileAssembly {
/** 使用单例模式 **/
public static ExcelFileAssembly excelFileAssembly = new ExcelFileAssembly();
public static ExcelFileAssembly getInstance(){
if(excelFileAssembly == null){
excelFileAssembly = new ExcelFileAssembly();
}
return excelFileAssembly;
}
/**
* 组装数据至excel文件中
* @param sheetLabel
* @param response
* @param title
* @param titles
* @param sample_data
* @throws Exception
*/
public void assembleData(String sheetLabel, HttpServletResponse response,
String title, String[] titles, Object[][] sample_data) throws Exception {
Workbook wb = new HSSFWorkbook();
// else wb = new XSSFWorkbook();
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet(sheetLabel);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
sheet.setDefaultColumnWidth(20);
//title row
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(title);
titleCell.setCellStyle(styles.get("title"));
//设置标题宽度
sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$I$1"));
//设置列
Row headerRow = sheet.createRow(1);
headerRow.setHeightInPoints(40);
Cell headerCell;
for (int i = 0; i < titles.length; i++) {
headerCell = headerRow.createCell(i);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
}
int rownum = 2;
for (int i = 0; i < 10; i++) {
Row row = sheet.createRow(rownum++);
for (int j = 0; j < titles.length; j++) {
Cell cell = row.createCell(j);
}
}
//设置数据
for (int i = 0; i < sample_data.length; i++) {
Row row = sheet.getRow(2 + i);
for (int j = 0; j < sample_data[i].length; j++) {
if(sample_data[i][j] == null) continue;
if(sample_data[i][j] instanceof String) {
row.getCell(j).setCellValue((String)sample_data[i][j]);
} else if(sample_data[i][j] instanceof Double){
row.getCell(j).setCellValue((Double)sample_data[i][j]);
}else if(sample_data[i][j] instanceof Long){
row.getCell(j).setCellValue((Long)sample_data[i][j]);
}
}
}
//设置行宽
//finally set column widths, the width is measured in units of 1/256th of a character width
for (int i = 0; i < 9; i++) {
sheet.setColumnWidth(i, 15 * 256); //15 characters wide
}
// Write the output to a file
// if(wb instanceof XSSFWorkbook) filename += "x";
// FileOutputStream out = new FileOutputStream("tt.xls");
// wb.write(out);
wb.write(response.getOutputStream());
}
/**
* Create a library of cell styles
*/
private static Map<String, CellStyle> createStyles(Workbook wb){
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style;
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short)18);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFont(titleFont);
styles.put("title", style);
Font monthFont = wb.createFont();
monthFont.setFontHeightInPoints((short)11);
monthFont.setColor(IndexedColors.WHITE.getIndex());
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(monthFont);
style.setWrapText(true);
styles.put("header", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
styles.put("cell", style);
return styles;
}
}
3.Flex端访问该请求,并获取下载地址
/**
* 动态获取服务器地址,并组装集中器下载地址
**/
public function getFileAddress(areaId:int, concentUID:String, groupMaintainId:int, isTemplate:int):String{
var _loc_1:* =ExternalInterface.call("function getURL(){return window.location.href;}");
var _loc_2:* = _loc_1.split(":");
var serverIP:String = _loc_2[1].toString().substring(2);
//根据集中器或分组情况导出其数据
var url:String="http://"+serverIP+":8080/sems/nodeCtrlDataImport.do?action=exportExcel&areaId=" +
areaId + "&concentUID=" + concentUID + "&groupMaintainId=" +
groupMaintainId + "&isTemplate=" + isTemplate;
return url;
}
/**
* 模板下载
**/
protected function templeateButton_clickHandler(event:MouseEvent):void
{
//DownFileUtil.getInstance().downFile('节点控制器数据导入模板.xls');
var url:String = getFileAddress(areaSelecter.selectedItem.id, concSelecter.selectedItem.label, groupSelecter.selectedItem.id, 1);
var u:URLRequest = new URLRequest(url);
u.method = URLRequestMethod.POST;
navigateToURL(u,"_blank")
}
/**
* 数据导出
**/
protected function exportButton_clickHandler(event:MouseEvent):void
{
var url:String = getFileAddress(areaSelecter.selectedItem.id, concSelecter.selectedItem.label, groupSelecter.selectedItem.id, 0);
var urlRequest:URLRequest = new URLRequest(url);
urlRequest.method = URLRequestMethod.POST;
navigateToURL(urlRequest,"_blank");
}
4.效果如下所示:
表格如下: