导出到excel表格

亦凉 2022-08-06 16:18 295阅读 0赞

<span style="font-size:18px;">/**
     * @param sql  查询语句
     * @param contentTitle excel表格标题名称
     * @param filepath   生成excel表格文件的路径
     * @param filename  excel表格的名称
     * @return 生成excel表格文件的路径
     */
    public String ToExcel(String sql,String contentTitle,String filepath,String filename) {
    		WritableWorkbook workbook = null;
    		try{
    			
    			/**
    			 * 执行sql语句，查询数据库结果
    			 */
    			List<Map> list = null;
    			//获取结果中的字段
    			if(list!=null && list.isEmpty()){
    				//获取导出excel表格中所有的列字段名称
    				Map<String,String> columnMap = list.get(0);
    				Set<String> keySet = columnMap.keySet();
    				
    				/*
    				 * 1，创建excel文件
    				 */
    				File file = new File(filepath);
    				if (!file.exists()) {
    					file.mkdirs();
    				}
    				//获取文件的完整路径
    				filename =file+"/"+filename+ ".xls";
    				//创建excel文件
    				File excelfile = new File(filename);
    				excelfile.createNewFile();
    				workbook = Workbook.createWorkbook(new FileOutputStream(excelfile));
    				
    				/*
    				 * 2，写入excel文件标题和工作簿名称
    				 */
    			
    				WritableSheet sheet = workbook.createSheet("sheet名称", 0);
    				Label nL = null;
    				jxl.write.WritableFont headerFont = new jxl.write.WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
    				jxl.write.WritableCellFormat headerFormat = new jxl.write.WritableCellFormat(headerFont);
    				jxl.write.WritableFont titleFont = new jxl.write.WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
    				jxl.write.WritableCellFormat titleFormat = new jxl.write.WritableCellFormat(titleFont);
    
    				/*
    				 * 3，写入标题
    				 */
    				nL = new Label(0, 0, contentTitle, headerFormat);
    				sheet.addCell(nL);
    				/*
    				 * 4，写入内容
    				 */
    				Label label;
    				int index = 0;
    				for(String key:keySet){
    					nL = new Label(index, 1, key, titleFormat);
    			    	sheet.addCell(nL);
    					for (int j = 0; j < list.size(); j++) {
    						//得到单元格的列所对应的值
    						String value = (String) list.get(j).get(key);
    						//把值放入单元格中
    						Label obj = new Label(index, j + 2, value);
    						sheet.addCell(obj);
    					}
    					index++;
    				}
    				workbook.write();
    				workbook.close();
    			}
    		}catch(Exception e){
    			e.printStackTrace();
    		}
    		return filename;
    	}</span>