用存储过程将excel转置导出

来源：互联网发布：可爱p图软件编辑：程序博客网时间：2024/05/29 15:56

运用存储过程将excel转置导出

下面是存储过程

DELIMITER $$USE `cstzjusf`$$DROP PROCEDURE IF EXISTS `getTaskInfo`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `getTaskInfo`(IN Task_id INT)    READS SQL DATABEGINSET @sql = NULL;SET @Task_id = Task_id;SELECT  GROUP_CONCAT(DISTINCT    CONCAT(      'MAX(IF(cti.Taskinfo_name = ''',      cti.Taskinfo_name,      ''', ctu.value, NULL)) AS ''',      cti.Taskinfo_name, ''''    )  ) INTO @sqlFROM cst_task_info cti WHERE cti.Task_id = @Task_id;SET @sql = CONCAT('Select DISTINCT(ct.Task_id),ct.Task_name,cu.student_id,cu.student_name,', @sql,                         ' From cst_taskinfo_user  ctu                        LEFT JOIN cst_task ct ON ctu.Task_id = ct.Task_id    LEFT JOIN cst_task_info cti ON ctu.taskInfo_id = cti.Taskinfo_id      LEFT JOIN cst_users cu ON ctu.user_id = cu.student_id                          ');   SET @sql = CONCAT(@sql,'Where ct.Task_id = ''',@Task_id,''' '); SET @sql = CONCAT(@sql, ' Group by cu.student_id');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;    END$$DELIMITER ;

在java代码中运用如下，进行调用

 /**     * 获取数据库连接     * @return Connection对象     */    public Connection getConnection()    {        Connection conn = null;   //数据库连接        try        {            Class.forName("com.mysql.jdbc.Driver"); //加载数据库驱动，注册到驱动管理器            /*数据库链接地址*/            String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8";            String username = "root";            String password = "root";            /*创建Connection链接*/            conn = DriverManager.getConnection(url, username, password);                     }        catch (ClassNotFoundException e){                        e.printStackTrace();        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return conn;  //返回数据库连接            }    /**     * 通过存储过程查询数据     * @return List<Book>     */    public List findTaskInfoByTaskId(String TaskId)     {      //  List <CstTaskinfoUserEx> list = new ArrayList<CstTaskinfoUserEx>(); //实例化List对象    List list = new ArrayList();        Connection conn = getConnection();  //创建数据库连接        try        {            //调用存储过程            CallableStatement cs = conn.prepareCall("{call getTaskInfo(?)}");            cs.setInt(1, Integer.parseInt(TaskId));            ResultSet rs = cs.executeQuery(); //执行查询操作，并获取结果集            ResultSetMetaData md = rs.getMetaData();            int columnCount = md.getColumnCount();            while(rs.next())            {             Map<String,Object> rowData = new HashMap<String,Object>();            for(int i = 1; i <= columnCount;i++){            rowData.put(md.getColumnName(i), rs.getObject(i));            }            list.add(rowData);            }                    }catch(Exception e)        {            e.printStackTrace();        }                return list;     //返回list    }@Overridepublic void exportExcel2(String TaskId,OutputStream out) {HashMap map = new HashMap<>();List<String> listOfTitle = new ArrayList<String>();  List<String> listOfFinal = new ArrayList<String>();listOfTitle.add("任务编号");  listOfTitle.add("任务名称");  listOfTitle.add("学号");  listOfTitle.add("学生姓名");  listOfFinal.add("Task_id");listOfFinal.add("Task_name");listOfFinal.add("student_id");listOfFinal.add("student_name");//map.put("queryQarameterSQL", this.setQueryValue(cstTask));//List<CstTaskinfoUserEx> list = cstTaskinfoUserMapperEx.selectByExample2(map);List<CstTaskinfoUserEx> list = this.findTaskInfoByTaskId(TaskId);List listKey = new ArrayList();Map mapResult = (Map)list.get(0);Set mapKeySet = mapResult.keySet();String listHead = "";String keyOfListMapKey = "";Iterator iteratorKey = mapKeySet.iterator();String listValue = "";while(iteratorKey.hasNext()){listHead = iteratorKey.next()+"";listKey.add(listHead);}for(int i = 0;i < listKey.size();i ++){if(!"Task_name".equals(listKey.get(i))&& !"student_name".equals(listKey.get(i)) && !"student_id".equals(listKey.get(i)) && !"Task_id".equals(listKey.get(i))){listOfTitle.add((String) listKey.get(i));listOfFinal.add((String) listKey.get(i));//listOfFinal.add(list.get(i).);}//listOfTitle.add(list.get(i));//listOfFinal.add("value");}String[] headTitle = listOfTitle.toArray(new String[listOfTitle.size()]);String[] finalValue = listOfFinal.toArray(new String[listOfFinal.size()]);ExportExcel<CstTaskinfoUserEx> exportExcel = new ExportExcel<CstTaskinfoUserEx>();exportExcel.exportExcel1("导出正在进行任务信息", headTitle,finalValue, list, out,"yyyy-MM-dd");}

下面是导出函数

/**    * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上    *    * @param title    *            表格标题名    * @param headers    *            表格属性列名数组    * @param fieldNames    *定义需要导出的javabean的属性数组             * @param dataset    *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的    *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)    * @param out    *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中    * @param pattern    *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"    */   @SuppressWarnings("unchecked")   public void exportExcel1(String title, String[] headers,String[] fieldNames,         List dataset, OutputStream out, String pattern) {      // 声明一个工作薄      HSSFWorkbook workbook = new HSSFWorkbook();      // 生成一个表格      HSSFSheet sheet = workbook.createSheet(title);      // 设置表格默认列宽度为15个字节      sheet.setDefaultColumnWidth((short) 15);          // 生成一个样式      HSSFCellStyle style = workbook.createCellStyle();      HSSFFont font3 = workbook.createFont();      font3.setColor(HSSFColor.BLUE.index);      // 设置这些样式      style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);      style.setBorderRight(HSSFCellStyle.BORDER_THIN);      style.setBorderTop(HSSFCellStyle.BORDER_THIN);      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);      // 生成一个字体      HSSFFont font = workbook.createFont();      font.setColor(HSSFColor.VIOLET.index);      font.setFontHeightInPoints((short) 12);      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);            // 把字体应用到当前的样式      style.setFont(font);      // 生成并设置另一个样式      HSSFCellStyle style2 = workbook.createCellStyle();      style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);      style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);      style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);      style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);      style2.setBorderRight(HSSFCellStyle.BORDER_THIN);      style2.setBorderTop(HSSFCellStyle.BORDER_THIN);      style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);      style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);      style2.setWrapText(true);//设置自动换行      // 生成另一个字体      HSSFFont font2 = workbook.createFont();      font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);      // 把字体应用到当前的样式      style2.setFont(font2);           // 声明一个画图的顶级管理器      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();      // 定义注释的大小和位置,详见文档      HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));      // 设置注释内容      comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));      // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.      comment.setAuthor("leno");       //产生表格标题行      HSSFRow row = sheet.createRow(0);      for (short i = 0; i < headers.length; i++) {         HSSFCell cell = row.createCell(i);                  cell.setCellStyle(style);         HSSFRichTextString text = new HSSFRichTextString(headers[i]);         cell.setCellValue(text);      }      //遍历集合数据，产生数据行      Iterator<T> it = dataset.iterator();      int index = 0;      int flag = 0;      int size = dataset.size();      Map mapResult = null;      while (it.hasNext()) {         index++;                 row = sheet.createRow(index);         T t = (T) it.next();         if(flag<size){          mapResult = (Map)dataset.get(flag++);         }       //  T t = (T) it.next();         //利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值//         Field[] fields = t.getClass().getDeclaredFields();                  for (short i = 0; i < fieldNames.length; i++) {            HSSFCell cell = row.createCell(i);            cell.setCellStyle(style2);//            Field field = fields[i];           /* String fieldName = fieldNames[i];            String getMethodName = "get"                   + fieldName.substring(0, 1).toUpperCase()                   + fieldName.substring(1);*/            try {            Object value = mapResult.get(fieldNames[i]);              /*  Class tCls = t.getClass();                Method getMethod = tCls.getMethod(getMethodName,                      new Class[] {});                Object value = getMethod.invoke(t, new Object[] {});*/                //判断值的类型后进行强制类型转换                String textValue = null;//              if (value instanceof Integer) {//                 int intValue = (Integer) value;//                 cell.setCellValue(intValue);//              } else if (value instanceof Float) {//                 float fValue = (Float) value;//                 textValue = new HSSFRichTextString(//                       String.valueOf(fValue));//                 cell.setCellValue(textValue);//              } else if (value instanceof Double) {//                 double dValue = (Double) value;//                 textValue = new HSSFRichTextString(//                       String.valueOf(dValue));//                 cell.setCellValue(textValue);//              } else if (value instanceof Long) {//                 long longValue = (Long) value;//                 cell.setCellValue(longValue);//              }                if (value instanceof Boolean) {                   boolean bValue = (Boolean) value;                   textValue = "男";                   if (!bValue) {                      textValue ="女";                   }                } else if (value instanceof Date) {                   Date date = (Date) value;                   SimpleDateFormat sdf = new SimpleDateFormat(pattern);                    textValue = sdf.format(date);                }  else if (value instanceof byte[]) {                   // 有图片时，设置行高为60px;                   row.setHeightInPoints(60);                   // 设置图片所在列宽度为80px,注意这里单位的一个换算                   sheet.setColumnWidth(i, (short) (35.7 * 80));                   // sheet.autoSizeColumn(i);                   byte[] bsValue = (byte[]) value;                   HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,                         1023, 255, (short) 6, index, (short) 6, index);                   anchor.setAnchorType(2);                   patriarch.createPicture(anchor, workbook.addPicture(                         bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));                } else{                   //其它数据类型都当作字符串简单处理                   textValue = value == null?"":value.toString();                }                //如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成                if(textValue!=null){                   Pattern p = Pattern.compile("^//d+(//.//d+)?$");                     Matcher matcher = p.matcher(textValue);                   if(matcher.matches()){                      //是数字当作double处理                      cell.setCellValue(Double.parseDouble(textValue));                   }else{                      HSSFRichTextString richString = new HSSFRichTextString(textValue);                      richString.applyFont(font3);                      cell.setCellValue(richString);                   }                }            } catch (SecurityException e) {                // TODO Auto-generated catch block                e.printStackTrace();           /* } catch (NoSuchMethodException e) {                // TODO Auto-generated catch block                e.printStackTrace();*/            } catch (IllegalArgumentException e) {                // TODO Auto-generated catch block                e.printStackTrace();         /*   } catch (IllegalAccessException e) {                // TODO Auto-generated catch block                e.printStackTrace();            } catch (InvocationTargetException e) {                // TODO Auto-generated catch block                e.printStackTrace();*/            } finally {                //清理资源            }         }       }      try {         workbook.write(out);      } catch (IOException e) {         // TODO Auto-generated catch block         e.printStackTrace();      }    }


以上

阅读全文

0 0