首先下载poi包jar
然后配置文件：
<action name="ExportExcelAction" class="GuessYouLikeAction" method="exceport" > <result name="excel" type="stream"> <param name="contentType">application/vnd.ms-excel</param> <param name="contentDisposition">attachment;filename="${downloadFileName}"</param> <param name="bufferSize">1024</param> <param name="inputName">excelFile</param> </result> </action>
action里面首先要有一个成员变量
private InputStream excelFile;
private String downloadFileName;
并且生成get set 方法如下：
public InputStream getExcelFile() { return excelFile; } public void setExcelFile(InputStream excelFile) { this.excelFile = excelFile; } public String getDownloadFileName() { SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd "); String downloadFileName = (sf.format(new Date()).toString())+ "结果列表.xls"; try { downloadFileName = new String(downloadFileName.getBytes(),"ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } return downloadFileName; } public void setDownloadFileName(String downloadFileName) { this.downloadFileName = downloadFileName; }
然后就可以开始准备到处excel了，代码如下：
public String exceport()throws Exception { int a=0; System.out.println(a); List<ibas_guessyl_result> listRes =new ArrayList<ibas_guessyl_result>(); try{ listRes=guessYouLikeServiceImpl.searchGuessYouLikeExportExcel(); }catch(Exception e){ System.out.println(e); } HSSFWorkbook workbook = exportExcel(listRes); ByteArrayOutputStream output = new ByteArrayOutputStream(); workbook.write(output); byte[] ba = output.toByteArray(); excelFile = new ByteArrayInputStream(ba); output.flush(); output.close(); return "excel"; }
public HSSFWorkbook exportExcel(List<ibas_guessyl_result> dataList) throws Exception { HSSFWorkbook workbook = null; try { // 创建工作簿实例 workbook = new HSSFWorkbook(); // 创建工作表实例 HSSFSheet sheet = workbook.createSheet("TscExcel"); // 设置列宽 this.setSheetColumnWidth(sheet); // 获取样式 HSSFCellStyle style = this.createTitleStyle(workbook); if (dataList != null && dataList.size() > 0) { // 创建第一行标题,标题名字的本地信息通过resources从资源文件中获取 System.out.println("开始生成excel"); HSSFRow row = sheet.createRow((short) 0);// 建立新行 this.createCell(row, 0, style, HSSFCell.CELL_TYPE_STRING,"序号"); this.createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING,"号码"); this.createCell(row, 2, style, HSSFCell.CELL_TYPE_STRING,"品牌"); this.createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING,"地市"); this.createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING,"套餐"); this.createCell(row, 5, style, HSSFCell.CELL_TYPE_STRING,"ARPU值"); this.createCell(row, 6, style, HSSFCell.CELL_TYPE_STRING,"职业"); this.createCell(row, 7, style, HSSFCell.CELL_TYPE_STRING,"互联网偏好"); this.createCell(row, 8, style, HSSFCell.CELL_TYPE_STRING,"用户分群"); this.createCell(row, 9, style, HSSFCell.CELL_TYPE_STRING,"主要活动区"); System.out.println("!!!!sssssssssssssssss!!!!"); // 给excel填充数据 for (int i = 0; i < dataList.size(); i++) { System.out.println("!!-------填充数据------!!"); // 将dataList里面的数据取出来，假设这里取出来的是Model,也就是某个javaBean的意思啦 ibas_guessyl_result model = (ibas_guessyl_result) dataList.get(i); HSSFRow row1 = sheet.createRow((short) (i + 1));// 建立新行 this.createCell(row1, 0, style, HSSFCell.CELL_TYPE_STRING,i + 1); if (model.getPhone_id() != 0) this.createCell(row1, 1, style,HSSFCell.CELL_TYPE_STRING, model.getPhone_id()); if (model.getBrand_name() != null) this.createCell(row1, 2, style,HSSFCell.CELL_TYPE_STRING, model.getBrand_name()); if (model.getC_city_name() != null) this.createCell(row1, 3, style,HSSFCell.CELL_TYPE_STRING, model.getC_city_name()); if (model.getMain_pack_name() != null) this.createCell(row1, 4, style,HSSFCell.CELL_TYPE_STRING, model.getMain_pack_name()); if (model.getArpu() != 0.0) this.createCell(row1, 5, style,HSSFCell.CELL_TYPE_STRING, model.getArpu()); if (model.getJob() != null) this.createCell(row1, 6, style,HSSFCell.CELL_TYPE_STRING, model.getJob()); if (model.getLabel_name() != null) this.createCell(row1, 7, style,HSSFCell.CELL_TYPE_STRING, model.getLabel_name()); if (model.getGroup_name() != null) this.createCell(row1, 7, style,HSSFCell.CELL_TYPE_STRING, model.getGroup_name()); if (model.getMain_act_community() != null) this.createCell(row1, 7, style,HSSFCell.CELL_TYPE_STRING, model.getMain_act_community()); if(i%65500==0 && i!=0){ break; } } } else { this.createCell(sheet.createRow(0), 0, style, HSSFCell.CELL_TYPE_STRING, "查无资料"); System.out.println("--------查无资料-------"); } } catch (Exception e) { e.printStackTrace(); System.out.println("异常为----"+e); } return workbook; }
private void setSheetColumnWidth(HSSFSheet sheet) { // 根据你数据里面的记录有多少列，就设置多少列 sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 8000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 8000); sheet.setColumnWidth(5, 5000); sheet.setColumnWidth(6, 5000); sheet.setColumnWidth(7, 5000); sheet.setColumnWidth(8, 5000); sheet.setColumnWidth(9, 5000); } // 设置excel的title样式 private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) { HSSFFont boldFont = wb.createFont(); boldFont.setFontHeight((short) 200); HSSFCellStyle style = wb.createCellStyle(); style.setFont(boldFont); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00")); return style; } // 创建Excel单元格 private void createCell(HSSFRow row, int column, HSSFCellStyle style,int cellType, Object value) { HSSFCell cell = row.createCell(column); if (style != null) { cell.setCellStyle(style); } switch (cellType) { case HSSFCell.CELL_TYPE_BLANK: { } break; case HSSFCell.CELL_TYPE_STRING: { cell.setCellValue(value.toString()); } break; case HSSFCell.CELL_TYPE_NUMERIC: { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(value.toString())); } break; default:break; } }
以上就是到处excel 过程及其详细代码。