如何把数据从网页中直接导入到excel中
来源：互联网 发布：手机淘宝网店怎么装修 编辑：程序博客网 时间：2024/05/16 10:07
把数据从网页中直接导入到excel中，下面就上代码。
页面js中导出的方法：
function exportExcel() {var queryPara = sy.serializeObject($('#agendaForm'));var url = BASE_URL+"exportExcel.do?queryPara=" + JSON.stringify(queryPara) ;window.location.href = url;}html页面的代码：<form id="agendaForm" name="agendaForm" method="post"><table id="agendatable" class="table table-bordered"> <tr> <td align="right">开始时间 </td> <td colspan="3"> <input id="aaId" name="aaId" hidden/> <input id="aiId" name="aiId" value="1" hidden/> <input name="starttime" value="starttime" style="width:400px;"> </td> </tr> <tr> <td align="right">结束时间 </td> <td colspan="3"> <input name="endtime" value="endtime" style="width:400px;"> </td> </tr> <tr> <td align="right">活动内容 </td> <td colspan="3"> <input name="content" value="content" style="width:400px;"> </td> </tr> <tr> <td align="right">演讲嘉宾 </td> <td colspan="3"><input name="speaker" value="speaker" style="width:400px;"></td> </tr> </form>controller层
@RequestMapping(value = "/exportExcel.do")public void exportExcel(HttpServletRequest request,HttpServletResponse response) {String queryPara = request.getParameter("queryPara");JSONUtils.getMorpherRegistry().registerMorpher(new DateMorpherEx(new String[] { "yyyy-MM-dd" },(Date) null));JSONObject jo = JSONObject.fromObject(queryPara);ActivityAgenda activityAgenda = (ActivityAgenda)JSONObject.toBean(jo,ActivityAgenda.class);/*if(jo.containsKey("time") && !StringUtils.isEmpty(jo.getString("time")))activityAgenda.setTime(DateHandler.strToDate(jo.getString("time"), "yyyy-MM-dd"));*/ try { String excelFileName = new String((DateHandler.dateToStr(new Date(),"yyyyMMddhhmmss")+".xls").getBytes("gb2312"), "ISO8859-1" ) ; response.setContentType("octets/stream"); response.addHeader("Content-Disposition", "attachment;filename="+excelFileName);// OutputStream out = response.getOutputStream(); agendaService.exportExcel(activityAgenda, out); out.close(); System.out.println("excel导出成功！"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); }}service层：public void exportExcel(ActivityAgenda activityAgenda, OutputStream os) {// TODO Auto-generated method stubMap keyMap = new HashMap();keyMap.put("queryQarameterSQL",this.setQueryQarameterSQL(activityAgenda));List<ActivityAgenda> list = this.getListByKeyMap(keyMap);ExportExcel<ActivityAgenda> exportExcel = new ExportExcel<ActivityAgenda>();exportExcel.exportExcel("导出议程信息", CnscecmsConst.ExcelConst.HEAD_AGENDA,CnscecmsConst.ExcelConst.FIELDNAMES_AGENDA, list, os,"yyyy-MM-dd");}private String setQueryQarameterSQL(ActivityAgenda activityAgenda) {// TODO Auto-generated method stubString queryQarameterSQL = "AI_ID = " + activityAgenda.getAiId() +" ";//keyMap.put("activityId", activityAttendPersonsEx.getAiId());if (!StringUtils.isEmpty(activityAgenda.getStarttime())) {queryQarameterSQL += " and starttime like '%"/*+ activityAgenda.getContent()*/ + "%' ";}if (!StringUtils.isEmpty(activityAgenda.getEndtime())) {queryQarameterSQL += " and endtime like '%"/*+ activityAgenda.getContent()*/ + "%' ";}if (!StringUtils.isEmpty(activityAgenda.getContent())) {queryQarameterSQL += " and content like '%"/*+ activityAgenda.getContent()*/ + "%' ";}if (!StringUtils.isEmpty(activityAgenda.getSpeaker())) {queryQarameterSQL += " and speaker like '%"/*+ activityAgenda.getSpeaker()*/ + "%' ";}if (!StringUtils.isEmpty(activityAgenda.getGuestIntroduction())) {queryQarameterSQL += " and guest_Introduction like '%"/*+ activityAgenda.getGuestIntroduction()*/ + "%' ";}return queryQarameterSQL;}参数设置：
public static final String[] HEAD_AGENDA = {"Id","开始时间","结束时间","活动内容","演讲嘉宾","嘉宾介绍"};public static final String[] FIELDNAMES_AGENDA = {"aaId","starttime","endtime","content","speaker","guestIntroduction"};exportExcel /** * 这是一个通用的方法，利用了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 exportExcel(String title, String[] headers,String[] fieldNames, Collection<T> dataset, OutputStream out, String pattern) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 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("dean"); //产生表格标题行 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; while (it.hasNext()) { index++; row = sheet.createRow(index); 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 { 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); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLUE.index); 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(); } } 1 0
- 如何把数据从网页中直接导入到excel中
- 从sqlserver2000直接把数据导入到MySQL中
- 如何从Excel中把数据导入到SharePoint List(Import Excel data to SharePoint List)
- 如何从Excel中把数据导入到SharePoint List(Import Excel data to SharePoint List)
- 在.net中如何把数据导入到Excel
- 如何把EXCEL的数据导入到ORACLE数据库中
- 在.net中如何把数据导入到Excel
- 如何把excel数据导入到mysql数据库中
- 如何把EXCEL数据导入到SQL SERVER数据库中
- 如何从sqlserver中把表数据导入到oracle？
- 如何从sqlserver中把表数据导入到oracle？ .
- 把excel数据导入到sql中
- 把Excel数据导入到MySQL中
- 如何把excel导入到数据库中！
- 从excel中导入数据到ds
- 从excel 中导入数据到oracle
- 从Excel中导入数据到Neo4j
- pb 如何从excel中导入数据
- java文件的读写
- 树2——List Leaves
- Ubuntu系统中安装RPM格式包的方法
- while(1) 什么意思 while(i--)什么意思？
- JavaWeb开发之六：HttpServletRequest对象
- 如何把数据从网页中直接导入到excel中
- Matlab 球体相交面绘制
- 用Direct2D和DWM来做简单的动画效果
- DOM之概述
- 《leetCode》：Compare Version Numbers
- DOM之常用对象
- 自己的Editor快捷键操作习惯配置
- DOM之节点增删
- Go 实现lamda、匿名函数和内联函数
