- 一、加载入POI的所有jar包:poi-3.9-20121203.jar;poi-examples-3.9-20121203.jar;poi-excelant-3.9-20121203.jar;poi-ooxml-3.9-20121203.jar;poi-ooxml-schemas-3.9-20121203.jar;poi-scratchpad-3.9-20121203.jar;commons-codec-1.5.jar;commons-logging-1.1.jar;junit-3.8.1.jar;log4j-1.2.13.jar;
- 二、建立一个web项目 在WEB-INF中的bin中把jar包导入
- 三、编写一个按钮<input type="button" value="导出" οnclick="outputData()"/>
- function outputData(){
- window.location='XXXAction!outputStatisticsData';
- }
- 四、在Action之中添加代码 :
- @SuppressWarnings("deprecation")
- public void outputStatisticsData() throws JSONException{
- //创建response对象
- HttpServletResponse response = ServletActionContext.getResponse() ;
- //创建一个新的Excel
- HSSFWorkbook workBook = new HSSFWorkbook() ;
- //创建sheet页
- HSSFSheet sheet0 = workBook.createSheet();
- //sheet页名称
- workBook.setSheetName(0, "短期投资上报导出数据 ");
- //设置Sheet页为默认
- sheet0.setSelected(true);
- //设置放大比例
- //sheet0.setZoom(6,5);
- //创建header页
- HSSFHeader header = sheet0.getHeader();
- //设置标题居中
- header.setCenter("标题");
- // //让Sheet页自适应页面大小
- // PrintSetup ps = sheet0.getPrintSetup();
- // ps.setFitHeight((short)1);
- // ps.setFitWidth((short)1);
- //设置自动换行
- sheet0.setAutobreaks(true) ;
- //设置列宽
- sheet0.setColumnWidth((short)0,(short)(35.7*80)); //设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)1,(short)(35.7*80)); //设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)2,(short)(35.7*80)); //设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)3,(short)(35.7*80)); //设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)4,(short)(35.7*80)); //设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)5,(short)(35.7*80)); //设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)6,(short)(35.7*80)); //设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)7,(short)(35.7*100)); //设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)8,(short)(35.7*100)); //设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)9,(short)(35.7*100)); //设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)10,(short)(35.7*100));//设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)11,(short)(35.7*100));//设置列宽(35.7*n,其中n是像素值)
- sheet0.setColumnWidth((short)12,(short)(35.7*100));//设置列宽(35.7*n,其中n是像素值)
- //设置第一行为上报表的表头
- HSSFRow row0 = sheet0.createRow((short)0); //取得Excel对象的第一行
- row0.setHeightInPoints((short)30.0) ;
- HSSFCell cell0 = row0.createCell((short)0) ;//合并单元格 第一行就只有一条数据
- cell0.setCellValue("村集体经济组织短期投资最终汇总表") ;
- //创建Excel合并单元格对象
- sheet0.addMergedRegion(new CellRangeAddress(
- 0, //firstRow这是从第一列开始
- 0, //lastRow 这是终结的列号
- 0, //firstCol这是第一列的号码
- 12)); //lastCol这是要合并的最后一列
- //创建单元格的格式
- CellStyle cellStyleHeader = workBook.createCellStyle() ;
- //设置单元格的水平居中
- cellStyleHeader.setAlignment(XSSFCellStyle.ALIGN_CENTER) ;
- //设置单元格的垂直居中
- cellStyleHeader.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER) ;
- //设置单元格的北京颜色25%的灰色
- //cellStyleHeader.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
- cellStyleHeader.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
- cellStyleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND);
- //创建字体格式
- Font fontHeader = workBook.createFont() ;
- //设置字体加粗显示
- fontHeader.setBoldweight(Font.BOLDWEIGHT_BOLD) ;
- //设置字体的字号size
- fontHeader.setFontHeightInPoints((short)20) ;
- //设置字体的文字类型
- fontHeader.setFontName("黑体");
- //fontHeader.setItalic(true) ;//设置是否为斜体
- cellStyleHeader.setFont(fontHeader) ;
- cell0.setCellStyle(cellStyleHeader) ;
- //创建Excel对象的第二行内容
- HSSFRow row1 = sheet0.createRow((short)1) ;
- row1.setHeightInPoints((short)16);
- //获取到当前组织形式的组织名称
- String orgName = getiCurrentinvestService().getOrgName() ;
- HSSFCell cell1_0 = row1.createCell((short)0);
- cell1_0.setCellValue("集体经济组织名称:"+orgName) ;
- HSSFCell cell1_1 = row1.createCell((short)5) ;
- Date date = new Date() ;
- SimpleDateFormat smdFormat = new SimpleDateFormat("yyyy-MM-dd");
- cell1_1.setCellValue("时间: " +smdFormat.format(date)) ;
- HSSFCell cell1_2 = row1.createCell((short)9) ;
- cell1_2.setCellValue("金额单位:元") ;
- sheet0.addMergedRegion(new CellRangeAddress(
- 1, //firstRow这是从第2列开始
- 1, //lastRow 这是终结的列号
- 0, //firstCol这是第2列的号码
- 4)); //lastCol这是要合并的最后一列
- sheet0.addMergedRegion(new CellRangeAddress(
- 1, //firstRow这是从第2列开始
- 1, //lastRow 这是终结的列号
- 5, //firstCol这是第2列的号码
- 8)); //lastCol这是要合并的最后一列
- sheet0.addMergedRegion(new CellRangeAddress(
- 1, //firstRow这是从第2列开始
- 1, //lastRow 这是终结的列号
- 9, //firstCol这是第2列的号码
- 12)); //lastCol这是要合并的最后一列
- CellStyle titleCellStyle = workBook.createCellStyle() ;
- titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER) ;
- titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
- titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
- Font fontContent = workBook.createFont() ;
- fontContent.setFontName("楷体");
- fontContent.setFontHeightInPoints((short)10) ;
- fontContent.setBoldweight(Font.BOLDWEIGHT_BOLD) ;
- titleCellStyle.setFont(fontContent) ;
- cell1_0.setCellStyle(titleCellStyle);
- cell1_1.setCellStyle(titleCellStyle) ;
- cell1_2.setCellStyle(titleCellStyle) ;
- //设置第三行数据
- HSSFRow row2 = sheet0.createRow((short)2) ;
- HSSFCell cell2_0 = row2.createCell((short)0);
- sheet0.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
- cell2_0.setCellValue("序号") ;
- HSSFCell cell2_1 = row2.createCell((short)1);
- sheet0.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
- cell2_1.setCellValue("组织名称") ;
- HSSFCell cell2_2 = row2.createCell((short)2);
- sheet0.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));
- cell2_2.setCellValue("证券名称") ;
- HSSFCell cell2_3 = row2.createCell((short)3);
- sheet0.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));
- cell2_3.setCellValue("证券编号");
- HSSFCell cell2_4 = row2.createCell((short)4);
- sheet0.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));
- cell2_4.setCellValue("证券单位") ;
- HSSFCell cell2_5 = row2.createCell((short)5);
- sheet0.addMergedRegion(new CellRangeAddress(2, 2, 5, 6));
- cell2_5.setCellValue("期初结余");
- HSSFCell cell2_7 = row2.createCell((short)7);
- sheet0.addMergedRegion(new CellRangeAddress(2, 2, 7, 8));
- cell2_7.setCellValue("本期证券购入");
- HSSFCell cell2_9 = row2.createCell((short)9);
- sheet0.addMergedRegion(new CellRangeAddress(2, 2, 9, 10));
- cell2_9.setCellValue("本期证券出售");
- HSSFCell cell2_11 = row2.createCell((short)11);
- sheet0.addMergedRegion(new CellRangeAddress(2, 2, 11, 12));
- cell2_11.setCellValue("期末结余");
- //设置第四行数据
- HSSFRow row3 = sheet0.createRow((short)3);
- HSSFCell cell3_5 = row3.createCell((short)5);
- cell3_5.setCellValue("期初金额");
- HSSFCell cell3_6 = row3.createCell((short)6);
- cell3_6.setCellValue("期初数量");
- HSSFCell cell3_7 = row3.createCell((short)7);
- cell3_7.setCellValue("证券购入金额");
- HSSFCell cell3_8 = row3.createCell((short)8);
- cell3_8.setCellValue("证券购入数量");
- HSSFCell cell3_9 = row3.createCell((short)9);
- cell3_9.setCellValue("证券出售数量");
- HSSFCell cell3_10 = row3.createCell((short)10);
- cell3_10.setCellValue("证券出售金额");
- HSSFCell cell3_11 = row3.createCell((short)11);
- cell3_11.setCellValue("期末结余数量");
- HSSFCell cell3_12 = row3.createCell((short)12);
- cell3_12.setCellValue("期末结余金额");
- //设置副标题的格式
- CellStyle subTitleCellStyle = workBook.createCellStyle();
- subTitleCellStyle.setFont(fontContent);
- subTitleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER) ;
- subTitleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- cell2_0.setCellStyle(subTitleCellStyle) ;
- cell2_1.setCellStyle(subTitleCellStyle) ;
- cell2_2.setCellStyle(subTitleCellStyle) ;
- cell2_3.setCellStyle(subTitleCellStyle) ;
- cell2_4.setCellStyle(subTitleCellStyle) ;
- cell2_5.setCellStyle(subTitleCellStyle) ;
- cell2_7.setCellStyle(subTitleCellStyle) ;
- cell2_9.setCellStyle(subTitleCellStyle) ;
- cell2_11.setCellStyle(subTitleCellStyle) ;
- cell3_5.setCellStyle(subTitleCellStyle) ;
- cell3_6.setCellStyle(subTitleCellStyle) ;
- cell3_7.setCellStyle(subTitleCellStyle) ;
- cell3_8.setCellStyle(subTitleCellStyle) ;
- cell3_9.setCellStyle(subTitleCellStyle) ;
- cell3_10.setCellStyle(subTitleCellStyle) ;
- cell3_11.setCellStyle(subTitleCellStyle) ;
- cell3_12.setCellStyle(subTitleCellStyle) ;
- //**************************************下面利用response对象利用输出流输出到客户端********************************//
- //通过Response把数据以Excel格式保存
- response.reset();
- //设置response流信息的头类型,MIME码
- response.setContentType("application/msexcel;charset=UTF-8");
- try {
- response.addHeader("Content-Disposition", "attachment;filename=\""
- + new String(("短期投资上报信息导出表" +new Date().toString()+ ".xls").getBytes("GBK"),
- "ISO8859_1") + "\"");
- //创建输出流对象
- OutputStream out = response.getOutputStream();
- //将创建的Excel对象利用二进制流的形式强制输出到客户端去
- workBook.write(out);
- //强制将数据从内存中保存
- out.flush();
- out.close();
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- 五、保存之后 发布项目 点击按钮导出按钮,程序实现思想是利用POI在服务器端完成Excel对象的建立,然后过输出流的对象将处理过的Excel workBook给输出到客户端去,通过配置response对象的头信息,使客户端用户利用MIME进行识别,打开Excel对象完成操作,这样远端用户就可以完成数据的现在保存和下载了。
Apache-POI实现Excel文档的导出下载功能
最新推荐文章于 2024-06-29 07:45:00 发布