- <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>