原样导出html页面表格的方法
在开发应用系统的时候，难免遇到导出页面上表格到Excel文件中。该方法适合任何的页面表格导成Excel文件的处理
采用javascript获取表格中的每一个单元格的信息；拼装成一定格式的字符串，提供给后台解析：
下面的代码包含每个单元格产生的Excel中的批注信息，可以根据需要进行取舍。
var hostname = location.hostname; //主机名/** ******** 导出功能函数 ****** *//** * 浏览器判断 */var Sys = {};var ua = navigator.userAgent.toLowerCase();if (window.ActiveXObject)Sys.ie = ua.match(/msie ([\d.]+)/)[1];else if (document.getBoxObjectFor)Sys.firefox = ua.match(/firefox\/([\d.]+)/)[1];function containsArray(array, obj) {for (var i = 0; i < array.length; i++) {if (array[i] == obj) {return i;break;}}return -1;}Array.prototype.contains = function(obj) {return containsArray(this, obj);}function PrintTableToExcel(tableId) {var offsetLeftArray = new Array();var cell;// 单元格Domvar col;// 单元格实际所在列var cellStr;// 每个cell以row,col,rowSpan,colSpan,value,background形式var cellStrArray = [];var objTab = document.getElementById(tableId);// 遍历第一次取出offsetLeft集合for (var i = 0; i < objTab.rows.length; i++) {for (var j = 0; j < objTab.rows[i].cells.length; j++) {cell = objTab.rows[i].cells[j];if (offsetLeftArray.contains(cell.offsetLeft) == -1)offsetLeftArray.push(cell.offsetLeft);}}offsetLeftArray.sort(function(x, y) { return parseInt(x) - parseInt(y); });//alert("offsetLeft集合:" + offsetLeftArray.join(','));// 遍历第二次生成cellStrArrayfor (var i = 0; i < objTab.rows.length; i++) {for (var j = 0; j < objTab.rows[i].cells.length; j++) {cell = objTab.rows[i].cells[j];var project ;//该对象为获得批注信息，可根据需要取舍if(cell.id){project = getProjectById(cell.id.split("-")[2]);//alert(project.projectCode +"|"+project.projectName +"|"+project.projectManager+"|"+project.projectType+"|"+project.meetingAddress);}col = offsetLeftArray.contains(cell.offsetLeft);if(cell.id){cellStr = i + '^' + col + '^' + cell.rowSpan + '^' + cell.colSpan + "^" + (Sys.firefox?cell.textContent:cell.innerText)+"^"+($(cell).css("background")==undefined? '-1':$(cell).css("background"))+"^"+project.projectCode +"^"+project.projectName +"^"+project.managerNames+"^"+(project.projectKind==null? '':project.projectKind) +"^"+project.meetingAddress;}else{cellStr = i + '^' + col + '^' + cell.rowSpan + '^' + cell.colSpan + "^" + (Sys.firefox?cell.textContent:cell.innerText)+"^"+($(cell).css("background")==undefined? '-1':$(cell).css("background"));}//alert(cellStr);cellStrArray.push(cellStr);}}var str = cellStrArray.join('@');var url = 'url' //你要要提交表单的地址$("#exportStr").attr("value",str);$("#exportfullStr").attr("value",getfullMonthProjectsString);document.exportform.action = url;document.exportform.method = "post";document.exportform.submit();}/** * @return */function getfullMonthProjectsString(){var projects = allProjects.delRepeat();var len = projects.length;var fullString = []for (var i=0 ; i <len ; i++){if (projects[i].fullMoon == '1'){fullString.push(projects[i].projectName +":" + projects[i].managerNames);}}return fullString.join("@");}public class ExportExcel {private List<ExcelContent> parseStrToData(String contentstr) {List<ExcelContent> contents = new ArrayList<ExcelContent>();String[] contentStrs = contentstr.split("@");for (String str : contentStrs) {ExcelContent ec = new ExcelContent();ExcelComments ecm = new ExcelComments();String[] c = str.split("\\^");if (StringUtils.isNotBlank(c[0])) {ec.setRowNo(Integer.valueOf(c[0]));}if (StringUtils.isNotBlank(c[1])) {ec.setColNo(Integer.valueOf(c[1]));}if (StringUtils.isNotBlank(c[2])) {ec.setRowSpan(Integer.valueOf(c[2]));}if (StringUtils.isNotBlank(c[3])) {ec.setColSpan(Integer.valueOf(c[3]));}if (StringUtils.isNotBlank(c[4])) {ec.setCellContent(c[4].split("/")[0]);//ecm.setProjectName(c[4].split("/")[1]);}if (StringUtils.isNotBlank(c[5])) {ec.setCellBackgroundColor(c[5]);}if (c.length > 6){if(StringUtils.isNotBlank(c[6])){ecm.setProjectCode(c[6]);}if(StringUtils.isNotBlank(c[7])){ecm.setProjectName(c[7]);}if(StringUtils.isNotBlank(c[8])){ecm.setProjectManage(c[8]);}if(StringUtils.isNotBlank(c[9])){ecm.setProjectType(c[9]);}if(StringUtils.isNotBlank(c[10])){ecm.setMeettingAddress(c[10]);}ec.setExcelComments(ecm);}contents.add(ec);}return contents;}public HSSFWorkbook generateExcel(String str,String loginUserName,String fullStr) throws Exception {List<ExcelContent> contents = parseStrToData(str);HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet();HSSFRow row = null;HSSFPatriarch drawing =sheet.createDrawingPatriarch();//一个Excle共享一个对象HSSFClientAnchor anchor = null;HSSFComment comment = null;int maxRow=0;int count = 1;for (ExcelContent ec : contents) {if (contents.size() == count){maxRow = ec.getRowNo();}count++;row = sheet.getRow(ec.getRowNo());if (row == null) {row = sheet.createRow(ec.getRowNo());}if (StringUtils.isNotBlank(ec.getCellContent())) {HSSFCell cell = row.createCell(ec.getColNo());CellRangeAddress cellRangeAddress = new CellRangeAddress(ec.getRowNo(), ec.getRowNo() + ec.getRowSpan() - 1, ec.getColNo(), ec.getColNo() + ec.getColSpan() - 1);sheet.addMergedRegion(cellRangeAddress);cell.setCellValue(new HSSFRichTextString(ec.getCellContent()));StringBuffer cmContent = new StringBuffer(100);if (ec.getExcelComments()!=null){CreationHelper factory = workbook.getCreationHelper();anchor = new HSSFClientAnchor(0, 1, 0, 1,(short) cell.getColumnIndex(), row.getRowNum(), (short) (cell.getColumnIndex()+6) , row.getRowNum()+6);comment = drawing.createComment(anchor);cmContent.append("xxxxx： "+ec.getExcelComments().getProjectCode() +"\n");cmContent.append("xxxxxxxx：").append(ec.getExcelComments().getProjectName()).append("\n");cmContent.append("xxxx：").append(ec.getExcelComments().getProjectManage()).append("\n");cmContent.append("xx：").append(ec.getExcelComments().getProjectType()).append("\n");cmContent.append("xxxx:").append(ec.getExcelComments().getMeettingAddress()).append("\n");RichTextString string = factory.createRichTextString(cmContent.toString());comment.setString(string);comment.setAuthor("NETWOKER SERVICE");cell.setCellComment(comment);}//comment.setHSSFCellStyle style = sheet.getWorkbook().createCellStyle();style.setAlignment(CellStyle.VERTICAL_CENTER);style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);style.setBottomBorderColor(HSSFColor.BLACK.index);if (StringUtils.isNotBlank(ec.getCellBackgroundColor())) {String color = ec.getCellBackgroundColor().toUpperCase();if (!("-1".equals(color))) {short shortColor = ColorConstant.COLOR_EXCEL.get(color);style.setFillForegroundColor(shortColor);style.setFillPattern(CellStyle.SOLID_FOREGROUND);}}cell.setCellStyle(style);}}addFullMonthProject(sheet, maxRow,fullStr);return workbook;}public void addFullMonthProject(HSSFSheet sheet,int maxRow,String fullStr){HSSFRow row = sheet.createRow(maxRow+1);HSSFCell cell = row.createCell(0);cell.setCellValue(new HSSFRichTextString("xxxxx： "));List<FullMonthProject> list = parseFullMonthProjectStr(fullStr);int len = list.size();for (int i=0 ; i<len ; i++){row = sheet.createRow(maxRow+1 +(i+1));HSSFCell cell0 = row.createCell(0);cell = row.createCell(1);CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex()+8);sheet.addMergedRegion(cellRangeAddress);HSSFCell cell2 = row.createCell(cell.getColumnIndex()+9);String pname = list.get(i).getProName();String pusers = list.get(i).getProUsers();cell0.setCellValue(i+1);cell.setCellValue(new HSSFRichTextString(pname));cell2.setCellValue(new HSSFRichTextString(pusers));}}public List<FullMonthProject> parseFullMonthProjectStr(String fullStr){List<FullMonthProject> fullMonthProjects = new ArrayList<FullMonthProject>();if(StringUtils.isNotBlank(fullStr)){String [] full = fullStr.split("@");for (String s :full){FullMonthProject fp = new FullMonthProject();fp.setProName(s.split(":")[0]);fp.setProUsers(s.split(":")[1]);fullMonthProjects.add(fp);}}return fullMonthProjects;}class FullMonthProject{private String proName;private String proUsers;public String getProName() {return proName;}public void setProName(String proName) {this.proName = proName;}public String getProUsers() {return proUsers;}public void setProUsers(String proUsers) {this.proUsers = proUsers;}}}public class ColorConstant{/** * 系统中项目的颜色和POI中颜色对应关系的工具类 * 方便Excle的导出，单元格的着色 */public static final Map<String,Short> COLOR_EXCEL = new HashMap<String,Short>();static {COLOR_EXCEL.put("#FFFF00", Short.valueOf(IndexedColors.YELLOW.getIndex()));COLOR_EXCEL.put("#FF00FF", Short.valueOf(IndexedColors.PINK.getIndex()));COLOR_EXCEL.put("#FF0000", Short.valueOf(IndexedColors.RED.getIndex()));COLOR_EXCEL.put("#0000FF", Short.valueOf(IndexedColors.BLUE.getIndex()));COLOR_EXCEL.put("#00FF00", Short.valueOf(IndexedColors.GREEN.getIndex()));COLOR_EXCEL.put("#FFD700", Short.valueOf(IndexedColors.GOLD.getIndex()));COLOR_EXCEL.put("#EE82EE", Short.valueOf(IndexedColors.VIOLET.getIndex()));COLOR_EXCEL.put("#008080", Short.valueOf(IndexedColors.TEAL.getIndex()));COLOR_EXCEL.put("#eeeeee", Short.valueOf(IndexedColors.BROWN.getIndex()));COLOR_EXCEL.put("#fff",Short.valueOf(IndexedColors.WHITE.getIndex()));}}