1.导出excel的工具类模板

1 public class ExportExcelUtil {  2     private HSSFWorkbook wb = null;  3   4     private HSSFSheet sheet = null;  5   6     /**  7      * @param wb  8      * @param sheet  9      */ 10     public ExportExcelUtil(HSSFWorkbook wb, HSSFSheet sheet) { 11         super(); 12         this.wb = wb; 13         this.sheet = sheet; 14     } 15  16     /** 17      * @return the sheet 18      */ 19     public HSSFSheet getSheet() { 20         return sheet; 21     } 22  23     /** 24      * @param sheet 25      *            the sheet to set 26      */ 27     public void setSheet(HSSFSheet sheet) { 28         this.sheet = sheet; 29     } 30  31     /** 32      * @return the wb 33      */ 34     public HSSFWorkbook getWb() { 35         return wb; 36     } 37  38     /** 39      * @param wb 40      *            the wb to set 41      */ 42     public void setWb(HSSFWorkbook wb) { 43         this.wb = wb; 44     } 45  46     /** 47      * 创建通用EXCEL头部 48      *  49      * @param headString 50      *            头部显示的字符 51      * @param colSum 52      *            该报表的列数 53      */ 54     public void createNormalHead(String headString, int colSum) { 55  56         HSSFRow row = sheet.createRow(0); 57  58         // 设置第一行 59         HSSFCell cell = row.createCell(0); 60         row.setHeight((short) 1000); 61  62         // 定义单元格为字符串类型 63         cell.setCellType(HSSFCell.ENCODING_UTF_16); 64         cell.setCellValue(new HSSFRichTextString(headString)); 65  66         // 指定合并区域 67         sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) colSum)); 68  69         HSSFCellStyle cellStyle = wb.createCellStyle(); 70  71         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 72         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 73         cellStyle.setWrapText(true);// 指定单元格自动换行 74  75         // 设置单元格字体 76         HSSFFont font = wb.createFont(); 77         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 78         font.setFontName("宋体"); 79         font.setFontHeight((short) 500); 80         cellStyle.setFont(font); 81  82         cell.setCellStyle(cellStyle); 83     } 84  85     /** 86      * 创建通用报表第二行 87      *  88      * @param params 89      *            统计条件数组 90      * @param colSum 91      *            需要合并到的列索引 92      */ 93     public void createNormalTwoRow(String params,String condition, int colSum) { 94         HSSFRow row1 = sheet.createRow(1); 95         row1.setHeight((short) 400); 96  97         HSSFCell cell2 = row1.createCell(0); 98  99         cell2.setCellType(HSSFCell.ENCODING_UTF_16);100         cell2.setCellValue(new HSSFRichTextString("导出时间：" + params+"    查询条件:"+condition101                 ));102 103         // 指定合并区域104         sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) colSum));105 106         HSSFCellStyle cellStyle = wb.createCellStyle();107         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐108         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐109         cellStyle.setWrapText(true);// 指定单元格自动换行110 111         // 设置单元格字体112         HSSFFont font = wb.createFont();113         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);114         font.setFontName("宋体");115         font.setFontHeight((short) 250);116         cellStyle.setFont(font);117 118         cell2.setCellStyle(cellStyle);119 120     }121 122     /**123      * 设置报表标题124      * 125      * @param columHeader126      *            标题字符串数组127      */128     public void createColumHeader(String[] columHeader) {129 130         // 设置列头131         HSSFRow row2 = sheet.createRow(2);132 133         // 指定行高134         row2.setHeight((short) 800);135 136         HSSFCellStyle cellStyle = wb.createCellStyle();137         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐138         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐139         cellStyle.setWrapText(true);// 指定单元格自动换行140 141         // 单元格字体142         HSSFFont font = wb.createFont();143         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);144         font.setFontName("宋体");145         font.setFontHeight((short) 350);146         cellStyle.setFont(font);147 148         149           cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单无格的边框为粗体150           cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．151           cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);152           cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);153           cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);154           cellStyle.setRightBorderColor(HSSFColor.BLACK.index);155           cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);156           cellStyle.setTopBorderColor(HSSFColor.BLUE.index);157          158 159         // 设置单元格背景色160         cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);161         162         cellStyle.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);163         cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);164 165         HSSFCell cell3 = null;166 167         for (int i = 0; i < columHeader.length; i++) {168             cell3 = row2.createCell(i);169             cell3.setCellType(HSSFCell.ENCODING_UTF_16);170             cell3.setCellStyle(cellStyle);171             cell3.setCellValue(new HSSFRichTextString(columHeader[i]));172         }173 174     }175 176     /**177      * 创建内容单元格178      * 179      * @param wb180      *            HSSFWorkbook181      * @param row182      *            HSSFRow183      * @param col184      *            short型的列索引185      * @param align186      *            对齐方式187      * @param val188      *            列值189      */190     public void cteateCell(HSSFWorkbook wb, HSSFRow row, int col, short align,191             String val) {192         HSSFCell cell = row.createCell(col);193         cell.setCellType(HSSFCell.ENCODING_UTF_16);194         cell.setCellValue(new HSSFRichTextString(val));195         HSSFCellStyle cellstyle = wb.createCellStyle();196         cellstyle.setAlignment(align);197         cell.setCellStyle(cellstyle);198     }199     200     public void cteateCell(HSSFWorkbook wb, HSSFRow row, int col,201             String val,HSSFCellStyle cellstyle) {202         HSSFCell cell = row.createCell(col);203         cell.setCellType(HSSFCell.ENCODING_UTF_16);204         cell.setCellValue(new HSSFRichTextString(val));205         cell.setCellStyle(cellstyle);206     }207 208     /**209      * 创建合计行210      * 211      * @param colSum212      *            需要合并到的列索引213      * @param cellValue214      */215     public void createLastSumRow(int colSum, String[] cellValue) {216 217         HSSFCellStyle cellStyle = wb.createCellStyle();218         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐219         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐220         cellStyle.setWrapText(true);// 指定单元格自动换行221 222         // 单元格字体223         HSSFFont font = wb.createFont();224         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);225         font.setFontName("宋体");226         font.setFontHeight((short) 250);227         cellStyle.setFont(font);228 229         HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));230         HSSFCell sumCell = lastRow.createCell(0);231 232         sumCell.setCellValue(new HSSFRichTextString("合计"));233         sumCell.setCellStyle(cellStyle);234 //        sheet.addMergedRegion(new Region(sheet.getLastRowNum(), (short) 0,235 //                sheet.getLastRowNum(), (short) colSum));// 指定合并区域236 237         for (int i = 1; i < (cellValue.length + 1); i++) {238             sumCell = lastRow.createCell(i);239             sumCell.setCellStyle(cellStyle);240             sumCell.setCellValue(new HSSFRichTextString(cellValue[i - 1]));241 242         }243 244     }245 246     /**247      * 输入EXCEL文件248      * 249      * @param fileName250      *            文件名251      */252     public void outputExcel(String fileName) {253         FileOutputStream fos = null;254         try {255             fos = new FileOutputStream(new File(fileName));256             wb.write(fos);257             fos.close();258         } catch (FileNotFoundException e) {259             e.printStackTrace();260         } catch (IOException e) {261             e.printStackTrace();262         }263     }264 265 266 }

2.导出调用方法

1 public void exportXls(V_itemList item, HttpServletRequest request, HttpServletResponse response) {  2         HSSFWorkbook wb_item = new HSSFWorkbook();  3         HSSFSheet sheet_item = wb_item.createSheet();  4         ExportExcelUtil exportExcel = new ExportExcelUtil(wb_item, sheet_item);  5         //列数  6         int number = 13 ;  7         // 给工作表列定义列宽(实际应用自己更改列数)  8         for (int i = 0; i < number; i++) {  9             sheet_item.setColumnWidth(i,5000); 10         } 11         // 创建单元格样式 12         HSSFCellStyle cellStyle = wb_item.createCellStyle(); 13         // 指定单元格居中对齐 14         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 15         // 指定单元格垂直居中对齐 16         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 17         // 指定当单元格内容显示不下时自动换行 18         cellStyle.setWrapText(true); 19         // 设置单元格字体 20         HSSFFont font = wb_item.createFont(); 21         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 22         font.setFontName("宋体"); 23         font.setFontHeight((short) 200); 24         cellStyle.setFont(font); 25         // 创建报表头部 26         exportExcel.createNormalHead("美容项目列表", number); 27         // 设置第二行 28         Date now = new Date();  29         DateFormat d1 = DateFormat.getDateTimeInstance(); 30         String nowDate = d1.format(now);  31         String excelWhere=getItemExcelWhere(item, request); 32         if (StringUtil.isEmpty(excelWhere)) { 33             excelWhere="无"; 34         } 35         exportExcel.createNormalTwoRow(nowDate,excelWhere, number); 36         HSSFRow row2 = sheet_item.createRow(2); 37          38         HSSFCell cell0 = row2.createCell(0); 39         cell0.setCellStyle(cellStyle); 40         cell0.setCellValue(new HSSFRichTextString("项目编号")); 41          42          43         HSSFCell cell1 = row2.createCell(1); 44         cell1.setCellStyle(cellStyle); 45         cell1.setCellValue(new HSSFRichTextString("项目名称")); 46          47         HSSFCell cell2 = row2.createCell(2); 48         cell2.setCellStyle(cellStyle); 49         cell2.setCellValue(new HSSFRichTextString("项目类型")); 50          51        92         String sql; 93         sql="from V_itemList t"; 94         String sqlWhere = getItemSqlWhere(item,request); 95         if (!sqlWhere.isEmpty()) { 96             sql+=" where "+sqlWhere; 97         } 98         List
     
       itemList= systemService.findByQueryString(sql); 99         // 循环创建中间的单元格的各项的值100                 for (int i =0; i < itemList.size(); i++) {101                     V_itemList dc =  itemList.get(i);102                     HSSFRow row = sheet_item.createRow((short) i + 3);103                     exportExcel.cteateCell(wb_item, row, (short) 0,HSSFCellStyle.ALIGN_CENTER_SELECTION, String .valueOf(dc.getNo()));104                     exportExcel.cteateCell(wb_item, row, (short) 1,HSSFCellStyle.ALIGN_CENTER_SELECTION, String .valueOf(dc.getName()));105                     exportExcel.cteateCell(wb_item, row, (short) 2,HSSFCellStyle.ALIGN_CENTER_SELECTION, String .valueOf(dc.getTypename()));106                   131     }132                         133         exportExcel.outputExcel("d:\\itemList.xls");134         download("d:\\itemList.xls", response); 135     }
     

3.弹出下载框

1 /** 2      * 弹出下载框 3      * @param path 4      * @param response 5      */ 6     private void download(String path, HttpServletResponse response) {   7         try {   8             // path是指欲下载的文件的路径。   9             File file = new File(path);  10             // 取得文件名。  11             String filename = file.getName();  12             // 以流的形式下载文件。  13             InputStream fis = new BufferedInputStream(new FileInputStream(path));  14             byte[] buffer = new byte[fis.available()];  15             fis.read(buffer);  16             fis.close();  17             // 清空response  18             response.reset();  19             // 设置response的Header  20             response.addHeader("Content-Disposition", "attachment;filename="  21                     + new String(filename.getBytes()));  22             response.addHeader("Content-Length", "" + file.length());  23             OutputStream toClient = new BufferedOutputStream(  24                     response.getOutputStream());  25             response.setContentType("application/vnd.ms-excel;charset=gb2312");  26             toClient.write(buffer);  27             toClient.flush();  28             toClient.close();  29         } catch (IOException ex) {  30             ex.printStackTrace();  31         }  32     }