将数据库数据导出至Excel表格

将数据库数据导出至Excel表格

所用技术（poi）
controller层代码

 public void upExcel(String fileName,HttpServletRequest request,HttpServletResponse response){
            OutputStream outputStream = null;
            //创建输出文件目录
            String excelPath = "E:\\file/" + fileName + ".xls";
            try {
                //创建输出流
                outputStream = new FileOutputStream(excelPath);
                //创建文件
                HSSFWorkbook workbook = new HSSFWorkbook();
                //创建工作表
                HSSFSheet sheet = workbook.createSheet(fileName);
                //获取表中字段集合
                List<Map> cols = this.proRequireService.findCols();//此处调用service层方法
                //创建表头
                HSSFRow row = sheet.createRow(0);
                //合并单元格
                CellRangeAddress cellRangeAddress = new CellRangeAddress(0,0,0,cols.size()-1);//第一行第一列分别记作0,0，表示将第0行到第0行，第0列到总列数减一列处合并
                //将合并的单元格放入文件中并写入数据（将文件名放进表头处）
                sheet.addMergedRegion(cellRangeAddress);
                row.createCell(0).setCellValue(fileName);
                //创见表头
                row = sheet.createRow(1);
                ArrayList<String> cnlist = new ArrayList();
                HashMap cn_en_map = new HashMap();
                //将数据写入表头
                for (Map map : cols) {
                    cnlist.add(map.get("COLUMN_NAME").toString());
                    cn_en_map.put(map.get("COLUMN_NAME").toString(),
                            map.get("COMMENTS").toString());
                }
                for (int i = 0; i < cnlist.size(); i++) {
                    System.out.println(cnlist.get(i).toString());
                    HSSFCell cell = row.createCell(i);
                    cell.setCellValue(cn_en_map.get(cnlist.get(i).toString()).toString());
                }
                //给表中写数据
                List<Map> datas = this.proRequireService.getDatas();
                for (int i = 0; i < datas.size(); i++) {
                    Map data = datas.get(i);
                    row = sheet.createRow(i + 2);
                    for (int j = 0; j < cnlist.size(); j++) {
                        row.createCell(j).setCellValue(data.get(cnlist.get(j).toString())==null?"":data.get(cnlist.get(j).toString()).toString());
                    }
                }
                workbook.write(outputStream);
                System.out.println("end");
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                try {
                    outputStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
       }