java将html表格导出成Excel表格

2020-05-27 走过路过 1452 0

java导出Excel是常用的功能,常用的jar包有jxl与poi,最近遇到要将一个html表格导出成excel,我选择了jxl来实现。

1. 看名字就知道jxl是以xml格式来解析的,所以如果html代码里有单标记,是会解析报错的,就需要先用jsoup这个jar包来处理掉所有的单标记,例子代码如下:
public static String getHtml(String html) {
	StringBuffer htmlBuf = new StringBuffer();
	Document doc = Jsoup.parse(html);
	for (Element item : doc.getElementsByTag("body")) {
		handleSingleTag(item);
		htmlBuf.append(item.html());
	}
	return htmlBuf.toString();
}

private static void handleSingleTag(Element item) {
	if ((item.children() != null) && (item.children().size() > 0)) {
		for (Element subItem : item.children()) {
			if (subItem.childrenSize() == 0) {
				subItem.prependText("");
			}
			handleSingleTag(subItem);
		}
	}
}
2. 我写了个JxlUtil来处这个html的table导出成excel。
首先html的table代码必须带colgroup,这样在生成excel表格时才能计算表格的宽度,
例子如下:
<colgroup><col width='100'></col><col width='100'></col></colgroup>
该工具类写了简单的获取样式,比如字体大小,再结合字的数量计算行的高度,代码虽然写的简陋,但已基本满足我目前的需求,如有类似需求的同学,可以拿去修改下。希望对你们有帮助。
代码如下:
import java.awt.Font;
import java.awt.FontMetrics;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;
import javax.swing.JComponent;
import javax.swing.JLabel;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.Node;

import com.aomc.manager.util.FileUtil;
import com.aomc.manager.util.StringUtil;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import sun.misc.BASE64Decoder;

public class JxlUtil {
	
	public static void write(WritableSheet sheet, String contentHtml) throws Exception{
		Document doc = DocumentHelper.parseText(contentHtml);
		Element root = doc.getRootElement();
		List<Integer> colsWidthList = getColsWidthList(root);
		setColWidth(sheet, colsWidthList);
		writeSheetData(sheet, root, 0, colsWidthList);
	}
	
	public static void write(HttpServletResponse response, String reportName, String contentHtml) {
		OutputStream output = null;
		WritableWorkbook book = null;
		//
		if(StringUtil.isEmpty(reportName)) {
			reportName = "导出数据.xls";
		}
		//
		try {
			response.reset();
			response.setContentType("application/x-download; charset=utf-8");
			response.setHeader("Content-Disposition", "attachment; filename=" + new String(reportName.getBytes("utf-8"), "iso8859-1"));
			output = new BufferedOutputStream(response.getOutputStream());
			book = Workbook.createWorkbook(output);
			//
			WritableSheet sheet = book.createSheet("导出数据", 0);
			//
			Document doc = DocumentHelper.parseText(contentHtml.replaceAll("", " ").replaceAll("&nbsp;", " ").replaceAll(" ", " ").replaceAll("&", "&"));
			Element root = doc.getRootElement();
			List<Integer> colsWidthList = getColsWidthList(root);
			setColWidth(sheet, colsWidthList);
			writeSheetData(sheet, root, 0, colsWidthList);
			//
			book.write();
			book.close();
			response.flushBuffer();
			output.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			FileUtil.closeOutputStream(output);
		}
	}
	
	/**
	 * 设置列宽
	 */
	private static void setColWidth(WritableSheet sheet, List<Integer> colsWidthList) {
		//
		for(int i=0,size=colsWidthList.size();i<size;i++){
			sheet.setColumnView(i, (int)(colsWidthList.get(i) / 6.5));
		}
	}
	
	/**
	 * 写表格数据
	 */
	private static void writeSheetData(WritableSheet sheet, Element element, int rowNum, List<Integer> colsWidthList) throws Exception{
		List<Element> items = element.elements();
		if(items != null) {
			Iterator<Element> it = items.iterator();
			while(it.hasNext()) {
				Element item = it.next();
				//
				if("table".equals(item.getName())) {
					Map<Integer, Integer> colsWidthMap = getTableColsWidthMap(item);
					String tableTextAlign = getTextAlign(item);
					//row
					List<Element> rowItems = item.elements();
					if(rowItems != null && rowItems.size() > 0) {
						Map<String, Integer> rowSpanMap = new HashMap<>();
						//
						for(int i=0,rowSize=rowItems.size();i<rowSize;i++) {
							Element rowItem = rowItems.get(i);
							//
							if("tr".equals(rowItem.getName())) {
								String rowTextAlign = getTextAlign(rowItem);
								//row
								int rowHeight = 0;
								String backgroundColor = getStyleBackgroundColor(rowItem);
								//
								List<Element> colItems = rowItem.elements();
								if(colItems != null && colItems.size() > 0) {
									int colIndex = 0;
									for(int j=0,colSize=colItems.size();j<colSize;j++) {
										Element colItem = colItems.get(j);
										//
										if("th".equals(colItem.getName()) || "td".equals(colItem.getName())) {
											if(rowSpanMap.get(colIndex+"_"+rowNum) != null) {
												colIndex++;
											}
											//
											String colBackgroundColor = getStyleBackgroundColor(colItem);
											//
											int lineNum = 0;
											int lineHeight = 0;
											//
											String colTextAlign = getTextAlign(colItem);
											String colVerticalAlign = getStyleValue(colItem, "vertical-align:");
											String fontSize = getFontSize(colItem);
											//col
											WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
													UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
											if("th".equals(colItem.getName())) {
												wf.setBoldStyle(WritableFont.BOLD);
											}
											if(!StringUtil.isEmpty(fontSize)) {
												wf.setPointSize(Integer.parseInt(fontSize));
											}
											//
											lineHeight = wf.getPointSize() * 26;
											//
											WritableCellFormat cellFormat = new WritableCellFormat(wf);
											if(item.attributeValue("noExportBorder") == null) {
												cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
											}
											cellFormat.setWrap(true);
											if("top".equals(colVerticalAlign)) {
												cellFormat.setVerticalAlignment(VerticalAlignment.TOP);
											} else	if("center".equals(colVerticalAlign)) {
												cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
											} else	if("bottom".equals(colVerticalAlign)) {
												cellFormat.setVerticalAlignment(VerticalAlignment.BOTTOM);
											} else {
												cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
											}
											if("center".equals(colTextAlign) || (colTextAlign == null && "center".equals(rowTextAlign)) 
													|| (colTextAlign == null && rowTextAlign == null && "center".equals(tableTextAlign))) {
												cellFormat.setAlignment(Alignment.CENTRE);
											}
											if(backgroundColor != null) {
												cellFormat.setBackground(Colour.GRAY_25);
											}
											if(colBackgroundColor != null) {
												//Color color = Color.decode(colBackgroundColor);
												cellFormat.setBackground(Colour.YELLOW);
											}
											String value = getCellContent(colItem);
											sheet.addCell(new Label(colIndex, rowNum, value, cellFormat));
											//
											String rowspanStr = colItem.attributeValue("rowspan");
											if(rowspanStr != null) {
												int rowspan = Integer.parseInt(rowspanStr);
												if(rowspan > 1) {
													sheet.mergeCells(colIndex, rowNum, colIndex, rowNum-1+rowspan);
													for(int k=1;k<rowspan;k++) {
														sheet.addCell(new Label(colIndex, rowNum+k, "", cellFormat));
														rowSpanMap.put(colIndex+"_"+(rowNum+k), rowspan);
													}
												}
											}
											//
											String width = getStyleWidth(colItem);
											if(!StringUtil.isEmpty(width)) {
												int w = Integer.parseInt(width);
												//
												String lineArr[] = value.split("\n");
												if(value.endsWith("\n")) {
													lineNum++;
												}
												Font font = new Font(wf.getName(), wf.getBoldWeight(), wf.getPointSize());
												JComponent jc = new JLabel();
												FontMetrics fm = jc.getFontMetrics(font);
												for(int m=0;m<lineArr.length;m++) {
													int lineWidth = fm.stringWidth(lineArr[m]);
													if(lineWidth == 0) {
														lineNum++;
													} else {
														lineNum += lineWidth/((12*w)/20);
														if(lineWidth%((12*w)/20) > 0) {
															lineNum++;
														}
													}
												}
												//
												int count = 0;
												for(int k=colIndex;k<colsWidthList.size();k++) {
													if(w >= colsWidthList.get(k)) {
														w -= colsWidthList.get(k);
														count++;
													} else {
														break;
													}
													//
													if(w == 0) {
														break;
													}
												}
												//
												if(count > 1) {
													sheet.mergeCells(colIndex, rowNum, colIndex-1+count, rowNum);
												}
												colIndex += count;
												//
												int tempRowHeight = lineNum * lineHeight;
												if(tempRowHeight > rowHeight) {
													rowHeight = tempRowHeight;
												}
											}
										}
									}
								}
								//
								String height = getStyleHeight(rowItem);
								if(!StringUtil.isEmpty(height)) {
									int setHeight = Integer.parseInt(height)*15;
									sheet.setRowView(rowNum, rowHeight > setHeight ? rowHeight : setHeight);
								} else {
									sheet.setRowView(rowNum, rowHeight);  
								}
								//
								rowNum++;
							}
						}
					}
				} else {
					writeSheetData(sheet, item, rowNum, colsWidthList);
				}
			}
		}
	}
	
	/**
	 * 获得列宽列表
	 */
	private static List<Integer> getColsWidthList(Element element) {
		List<Map<Integer, Integer>> colsMapList = new ArrayList<>();
		getTableColsWidthMapList(element, colsMapList);
		//
		List<Integer> colsWidthList = new ArrayList<>();
		for(Map<Integer, Integer> itemMap : colsMapList) {
			if(colsWidthList.size() == 0) {
				for(int i=0,size=itemMap.keySet().size();i<size;i++) {
					colsWidthList.add(itemMap.get(i));
				}
			} else {
				Integer index = 0;
				for(int i=0,size=itemMap.keySet().size();i<size;i++) {
					Integer width = itemMap.get(i);
					for(int j=index,length=colsWidthList.size();j<length;j++) {
						Integer wh = colsWidthList.get(j);
						if(width < wh) {
							colsWidthList.remove(j);
							colsWidthList.add(j, wh-width);
							colsWidthList.add(j, width);
							index = j+1;
							break;
						} else if(width > wh){
							width -= wh;
						} else {
							index = j+1;
							break;
						}
					}
				}
			}
		}
		return colsWidthList;
	}
	
	/**
	 * 获得table的列宽map列表
	 */
	private static void getTableColsWidthMapList(Element element, List<Map<Integer, Integer>> colsMapList) {
		List<Element> items = element.elements();
		if(items != null) {
			Iterator<Element> it = items.iterator();
			while(it.hasNext()) {
				Element item = it.next();
				//
				if("table".equals(item.getName())) {
					Map<Integer, Integer> map = getTableColsWidthMap(item);
					setColWidth(item, map);
					colsMapList.add(map);
				} else {
					getTableColsWidthMapList(item, colsMapList);
				}
			}
		}
	}
	
	/**
	 * 高置列的宽度
	 */
	private static void setColWidth(Element element, Map<Integer, Integer> map) {
		List<Element> rowItems = element.elements();
		if(rowItems != null && rowItems.size() > 0) {
			int rowNum = 0;
			Map<String, Integer> rowSpanMap = new HashMap<>();
			for(int i=0,rowSize=rowItems.size();i<rowSize;i++) {
				Element rowItem = rowItems.get(i);
				//
				if("tr".equals(rowItem.getName())) {
					List<Element> colItems = rowItem.elements();
					if(colItems != null && colItems.size() > 0) {
						int colIndex = 0;
						Iterator<Element> colIt = colItems.iterator();
						while(colIt.hasNext()) {
							Element colItem = colIt.next();
							//
							if("th".equals(colItem.getName()) || "td".equals(colItem.getName())) {
								String rowspanStr = colItem.attributeValue("rowspan");
								if(rowspanStr != null) {
									int rowspan = Integer.parseInt(rowspanStr);
									if(rowspan > 1) {
										for(int k=1;k<rowspan;k++) {
											rowSpanMap.put(colIndex+"_"+(rowNum+k), rowspan);
										}
									}
								}
								//
								if(rowSpanMap.get(colIndex+"_"+rowNum) != null) {
									colIndex++;
								}
								//
								String colspanStr = colItem.attributeValue("colspan");
								int width = 0;
								if(colspanStr == null) {
									width = map.get(colIndex);
									colIndex++;
								} else {
									int colspan = Integer.parseInt(colspanStr);
									for(int k=colIndex;k<colspan+colIndex;k++) {
										width += map.get(k);
									}
									colIndex += colspan;
								}
								setStyleWidth(colItem, width);
							}
						}
					}
					rowNum++;
				}
			}
		}
	}
	
	/**
	 * 获得table的列宽map key: 列位置 value: 宽度
	 */
	private static Map<Integer, Integer> getTableColsWidthMap(Element element) {
		Map<Integer, Integer> map = new HashMap<>();
		List<Element> colGroupList = element.elements("colgroup");
		if(colGroupList != null && colGroupList.size() > 0) {
			List<Element> colList = colGroupList.get(0).elements("col");
			if(colList != null && colList.size() > 0) {
				for(int i=0,size=colList.size();i<size;i++) {
					Element colItem = colList.get(i);
					String width = colItem.attributeValue("width");
					map.put(i, Integer.parseInt(width));
				}
				return map;
			}
		}
		//
		Map<String, Integer> colSpanMap = new HashMap<>();
		//row
		List<Element> rowItems = element.elements();
		if(rowItems != null && rowItems.size() > 0) {
			for(int i=0,rowSize=rowItems.size();i<rowSize;i++) {
				Element rowItem = rowItems.get(i);
				//
				if("tr".equals(rowItem.getName())) {
					List<Element> colItems = rowItem.elements();
					if(colItems != null && colItems.size() > 0) {
						int j = 0;
						Iterator<Element> colIt = colItems.iterator();
						while(colIt.hasNext()) {
							Element colItem = colIt.next();
							//
							if("th".equals(colItem.getName()) || "td".equals(colItem.getName())) {
								//
								String colspanStr = colItem.attributeValue("colspan");
								String width = getStyleWidth(colItem);
								if(colspanStr == null) {
									if(width != null) {
										map.put(j, Integer.parseInt(width));
									} else if(!map.keySet().contains(j)) {
										map.put(j, null);
									}
									j++;
								} else {
									Integer colspan = Integer.parseInt(colspanStr);
									String key = j + "-" + (j - 1 + colspan);
									if(width != null) {
										colSpanMap.put(key, Integer.parseInt(width));
									} 
									for(int k=j;k<colspan;k++) {
										if(!map.keySet().contains(k)) {
											map.put(k, null);
										}
									}
									j += colspan;
								}
								
								
							}
						}
					}
				}
			}
		}
		//根据colspan计算宽度
		for(Integer index : map.keySet()) {
			Integer width = map.get(index);
			if(width == null) {
				for(String key : colSpanMap.keySet()) {
					String indexArr[] = key.split("-");
					int startIndex = Integer.parseInt(indexArr[0]);
					int endIndex = Integer.parseInt(indexArr[1]);
					if(index >= startIndex && index <= endIndex) {
						Integer colSpanWidth = colSpanMap.get(key);
						boolean flag = true;
						for(int i=startIndex;i<=endIndex;i++) {
							if(i != index) {
								Integer itemWidth = map.get(i);
								if(itemWidth == null) {
									flag = false;
									break;
								}
								colSpanWidth -= map.get(i);
							}
						}
						//
						if(flag) {
							map.put(index, colSpanWidth);
						}
					}
				}
			}
		}
		//根据table宽度计算列宽度
		String tableWidthStr = getStyleWidth(element);
		tableWidthStr = tableWidthStr == null ? element.attributeValue("width") : tableWidthStr;
		if(tableWidthStr != null) {
			Integer pindex = null;
			boolean flag = true;
			int totalWidth = Integer.parseInt(tableWidthStr.replaceAll("px", "").trim());
			for(Integer index : map.keySet()) {
				Integer width = map.get(index);
				if(width == null) {
					if(pindex != null) {
						flag = false;
						pindex = null;
						break;
					}
					pindex = index;
				} else {
					totalWidth -= width;
				}
			}
			//
			if(flag && pindex != null) {
				map.put(pindex, totalWidth);
			}
		}
		
		return map;
	}
	
	/**
	 * 获得水平对齐方式
	 */
	private static String getStyleValue(Element element, String key) {
		String value = null;
		String style = element.attributeValue("style");
		//
		if(!StringUtil.isEmpty(style)) {
			String styleArr[] = style.split(";");
			for(String sItem : styleArr) {
				String prop = sItem.trim();
				if(prop.startsWith(key)) {
					value = prop.replaceAll(key, "").trim();
				}
			}
		}
		return value;
	}
	
	/**
	 * 获得水平对齐方式
	 */
	private static String getFontSize(Element element) {
		String fontSize = null;
		String style = element.attributeValue("style");
		//
		if(!StringUtil.isEmpty(style)) {
			String styleArr[] = style.split(";");
			for(String sItem : styleArr) {
				String prop = sItem.trim();
				if(prop.startsWith("font-size:")) {
					fontSize = prop.replaceAll("font-size:", "").replaceAll("px", "").trim();
				}
			}
		}
		return fontSize;
	}
	
	/**
	 * 获得水平对齐方式
	 */
	private static String getTextAlign(Element element) {
		String textAlign = null;
		String style = element.attributeValue("style");
		//
		if(!StringUtil.isEmpty(style)) {
			String styleArr[] = style.split(";");
			for(String sItem : styleArr) {
				String prop = sItem.trim();
				if(prop.startsWith("text-align:")) {
					textAlign = prop.replaceAll("text-align:", "").trim();
				}
			}
		}
		return textAlign;
	}
	
	/**
	 * 获得背景颜色
	 */
	private static String getStyleBackgroundColor(Element element) {
		String backgroundColor = null;
		String style = element.attributeValue("style");
		//
		if(!StringUtil.isEmpty(style)) {
			String styleArr[] = style.split(";");
			for(String sItem : styleArr) {
				String prop = sItem.trim();
				if(prop.startsWith("background-color:")) {
					backgroundColor = prop.replaceAll("background-color:", "").trim();
				}
			}
		}
		return backgroundColor;
	}
	
	/**
	 * 设置样式宽度
	 */
	private static void setStyleWidth(Element element, Integer width) {
		String style = element.attributeValue("style");
		StringBuffer buffer = new StringBuffer();
		//
		if(!StringUtil.isEmpty(style)) {
			String styleArr[] = style.split(";");
			for(String sItem : styleArr) {
				String prop = sItem.trim();
				if(!prop.startsWith("width:")) {
					buffer.append(prop);
					buffer.append(";");
				}
			}
		}
		buffer.append("width:");
		buffer.append(width.toString());
		buffer.append("px;");
		//
		element.addAttribute("style", buffer.toString());
	}
	
	/**
	 * 获得样式宽度
	 */
	private static String getStyleWidth(Element element) {
		String width = null;
		String style = element.attributeValue("style");
		//
		if(!StringUtil.isEmpty(style)) {
			String styleArr[] = style.split(";");
			for(String sItem : styleArr) {
				String prop = sItem.trim();
				if(prop.startsWith("width:")) {
					width = prop.replaceAll("width:", "").replaceAll("px", "").trim();
				}
			}
		}
		return width;
	}
	
	/**
	 * 获得样式高度
	 */
	private static String getStyleHeight(Element element) {
		String height = null;
		String style = element.attributeValue("style");
		//
		if(!StringUtil.isEmpty(style)) {
			String styleArr[] = style.split(";");
			for(String sItem : styleArr) {
				String prop = sItem.trim();
				if(prop.startsWith("height:")) {
					height = prop.replaceAll("height:", "").replaceAll("px", "").trim();
				}
			}
		}
		return height;
	}
	
	/**
	 * 是否显示内容
	 */
	private static boolean isDispaly(Element element) {
		String style = element.attributeValue("style");
		if(!StringUtil.isEmpty(style)) {
			String styleArr[] = style.split(";");
			for(String prop : styleArr) {
				String propArr[] = prop.split(":");
				if(propArr.length == 2 && "display".equals(propArr[0].trim()) && 
						"none".equals(propArr[1].trim())) {
					return false;
				}
			}
		}
		return true;
	}
	
	/**
	 * 是否流布局
	 */
	private static boolean isFloatLayout(Element element) {
		String style = element.attributeValue("style");
		if(!StringUtil.isEmpty(style)) {
			String styleArr[] = style.split(";");
			for(String prop : styleArr) {
				String propArr[] = prop.split(":");
				if(propArr.length == 2 && "float".equals(propArr[0].trim())) {
					return true;
				}
			}
		}
		return false;
	}
	
	/**
	 * 获得单元格内容
	 */
	private static String getCellContent(Element element) {
		if(!isDispaly(element)) {
			return " ";
		}
		//
		StringBuffer buffer = new StringBuffer();
		if(element.elements() == null || element.elements().size() == 0){
			if(element.getText().length() == 0) {
				buffer.append(" ");
			} else {
				buffer.append(element.getText());
			}
		} 
		//
		List<Element> items = element.elements();
		//
		if(items != null) {
			boolean isFirst = true;
			for(int i=0,size=items.size();i<size;i++){
				Element item = items.get(i);
				if(item.attributeValue("noExport") == null) {
					boolean floatLayout = isFloatLayout(item);
					if(!isFirst) {
						if(floatLayout) {
							buffer.append("     ");
						} else if (!floatLayout) {
							buffer.append("");
						} 
					}
					//
					getSubContent(item, buffer);
					//
					if(item.attributeValue("newLine") != null) {
						buffer.append("\n");
					}
					isFirst = false;
				}
			}
		}
		return buffer.toString();
	}
	
	/**
	 * 获得单元格内容
	 */
	private static void getSubContent(Element element, StringBuffer buffer) {
		if(element.getName().equalsIgnoreCase("xmp")) {
			String content = element.asXML();
			buffer.append(content.substring(5, content.length() - 6));
			return;
		}
		//
		if(!isDispaly(element)) {
			return;
		}
		//
		if(element.elements() == null || element.elements().size() == 0){
			if(element.getText().length() == 0) {
				if("br".equals(element.getName())) {
					buffer.append("\n");
				} else if("img".equalsIgnoreCase(element.getName())) {
					/*String imgSrc = element.attributeValue("src");
					if(!StringUtil.isEmpty(imgSrc) && imgSrc.startsWith("data:image")) {
						GenerateImage(imgSrc.split(",")[1]);
					}*/
				} else {
					buffer.append("");
				}
			} else {
				buffer.append(element.getText());
			}
		} else {
			Iterator<Node> it = element.nodeIterator();
			while(it.hasNext()) {
				Node item = it.next();
				if(item.getName() == null) {
					buffer.append(item.getText());
				} else {
					Element itemEl = (Element)item;
					if(itemEl.attributeValue("noExport") == null) {
						getSubContent(itemEl, buffer);
					}
				}
			}
		}
	}
	
	/**
	 * 关闭
	 */
	public static void closeWritableWorkbook(WritableWorkbook book) {
		if(book != null) {
			try{
				book.close();
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
	
}

相关推荐
全部评论 最新评论 最早评论
还没有用户评论

联系我们