原样导出html页面表格的方法_JAVA_编程开发_程序员俱乐部

在开发应用系统的时候，难免遇到导出页面上表格到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;// 单元格Dom
	var 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(','));

	// 遍历第二次生成cellStrArray
	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];
			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("@");
}

后台进行接收表单提交内容，解析，然后导出，生成Excel：

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.set
				HSSFCellStyle 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;
		}
	}
}

注意：如果你的界面的table具有单元格的背景色，请采用poi提供的color颜色，做好html中的color 和POI提供color的对应关系，这个关系我是这样处理的：

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()));
	}
	
}