Excel(2) : xls转 html

最新推荐文章于 2025-03-03 10:49:30 发布

楽焫旒璡

最新推荐文章于 2025-03-03 10:49:30 发布

阅读量436

点赞数

分类专栏： # Excel 文章标签： excel xls html xls转html jxl

Excel 专栏收录该内容

19 篇文章

订阅专栏

参考 : https://www.cnblogs.com/mfrbuaa/p/5204461.html

maven 坐标

        <!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

import java.io.*;
import java.util.HashMap;
import java.util.Map;

import jxl.Cell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;

/**
 * 获取Excel文件转换为HTML后的字符串，仅仅支持后缀为xls的Excel（主调方法）
 */
public class ExcelTransformHtml {

    public static void main(String[] args) throws Exception {
        System.err.println(xlsToHtml("/Users/leyili/Desktop/poi/test.html", "/Users/leyili/Desktop/poi/test.xls", 1));
    }

    /**
     * xls 转 html
     * @param htmlPath      生成的 html 路径
     * @param xlsPath       xls 路径
     * @param sheetNum      第 n 个工作表(从0开始)
     * @return
     * @throws Exception
     */
    public static String xlsToHtml(String htmlPath, String xlsPath, int sheetNum) throws Exception {

        BufferedWriter bw = new BufferedWriter(new FileWriter(htmlPath));

        String str = getExcelInfo(new File(xlsPath), sheetNum);

        str = "<html><head><meta charset=\"utf-8\"><title></title></head><body>" + str;
        
        str += "</body></html>";
        
        bw.write(str);

        bw.close();
        
        return htmlPath;
    }


    /**
     * 获取Excel文件转换为HTML后的字符串，仅仅支持后缀为xls的Excel（主调方法）
     *
     * @param sourcefile <span style="font-family: Arial, Helvetica, sans-serif;"> </span>Excel文件
     * @return 返回Excel转换后的HTML字符串
     * @throws Exception
     */
    public static String getExcelInfo(File sourcefile, int sheetNum) throws Exception {
        StringBuffer sb = new StringBuffer();
        InputStream is = new FileInputStream(sourcefile);
        Workbook rwb = Workbook.getWorkbook(is);
        Sheet sheet = rwb.getSheet(sheetNum);
        int colnum = sheet.getColumns();
        int rownum = sheet.getRows();
        Map<String, String> map[] = getRowSpanColSpanMap(sheet);
        sb.append("<table border='1' cellspacing='0'>");
        for (int row = 0; row < rownum; row++) {
            sb.append("<tr>");
            for (int col = 0; col < colnum; col++) {
                Cell cell = sheet.getCell(col, row);
                String content = cell.getContents();
                CellFormat cellFormat = cell.getCellFormat();
                if (map[0].containsKey(row + "," + col)) {
                    String pointString = map[0].get(row + "," + col);
                    map[0].remove(row + "," + col);
                    int bottomeRow = Integer.valueOf(pointString.split(",")[0]);
                    int bottomeCol = Integer.valueOf(pointString.split(",")[1]);
                    int rowSpan = bottomeRow - row + 1;
                    int colSpan = bottomeCol - col + 1;
                    sb.append("<td rowspan= '" + rowSpan + "' colspan= '" + colSpan + "' ");
                } else if (map[1].containsKey(row + "," + col)) {
                    map[1].remove(row + "," + col);
                    continue;
                } else {
                    sb.append("<td ");
                }
                if (cellFormat != null) {
                    Alignment alignment = cellFormat.getAlignment();
                    sb.append("align='" + convertToHtmlGrammer(alignment) + "' ");
                    VerticalAlignment verticalAlignment = cellFormat.getVerticalAlignment();
                    sb.append("valign='" + convertToHtmlGrammer(verticalAlignment) + "' ");
                    // sb.append("style='color:" + convertToHtmlGrammer(cellFormat.getFont().getColour()) + ";");  //
                    Colour bgcolour = cellFormat.getBackgroundColour();
                    // sb.append("background-color:" + convertToHtmlGrammer(bgcolour) + ";");  //
                    // Colour bottomColour = cellFormat.getBorderColour(Border.BOTTOM);  //
                    // Colour leftColour = cellFormat.getBorderColour(Border.LEFT);
                    // Colour rightColour = cellFormat.getBorderColour(Border.RIGHT);
                    // Colour topColour = cellFormat.getBorderColour(Border.TOP);

                    // System.out.print("bottomColour:" +
                    // bottomColour.getDescription());
                    // System.out.print(" leftColour:" +
                    // leftColour.getDescription());
                    // System.out.print(" rightColour:" +
                    // rightColour.getDescription());
                    // System.out.println(" topColour:" +
                    // topColour.getDescription());

                    // sb.append("border-color:" + convertToHtmlGrammer(bottomColour) + ";");  // 
                    sb.append("' ");
                }
                sb.append(">");
                if (content == null || "".equals(content.trim())) {
                    sb.append("   ");
                } else {
                    sb.append(content);
                }
                sb.append("</td>");
            }
            sb.append("</tr>");
        }
        sb.append("</table>");
        rwb.close();
        is.close();
        return sb.toString();
    }

    @SuppressWarnings("unchecked")
    private static Map<String, String>[] getRowSpanColSpanMap(Sheet sheet) {
        Map<String, String> map0 = new HashMap<String, String>();
        Map<String, String> map1 = new HashMap<String, String>();
        Range[] range = sheet.getMergedCells();
        for (int i = 0; i < range.length; i++) {
            Cell topCell = range[i].getTopLeft();
            Cell bottomCell = range[i].getBottomRight();
            int topRow = topCell.getRow();
            int topCol = topCell.getColumn();
            int bottomRow = bottomCell.getRow();
            int bottomCol = bottomCell.getColumn();
            map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol);
            int tempRow = topRow;
            while (tempRow <= bottomRow) {
                int tempCol = topCol;
                while (tempCol <= bottomCol) {
                    map1.put(tempRow + "," + tempCol, "");
                    tempCol++;
                }
                tempRow++;
            }
            map1.remove(topRow + "," + topCol);
        }
        Map[] map = {map0, map1};
        return map;
    }

    private static String convertToHtmlGrammer(Alignment alignment) {
        String align = "left";
        switch (alignment.getValue()) {
            case 1:
                align = "left";
                break;
            case 2:
                align = "center";
                break;
            case 3:
                align = "right";
                break;
            case 5:
                align = "justify";
                break;
            default:
                break;
        }
        return align;
    }

    private static String convertToHtmlGrammer(VerticalAlignment verticalAlignment) {
        String valign = "middle";
        switch (verticalAlignment.getValue()) {
            case 1:
                valign = "middle";
                break;
            case 2:
                valign = "bottom";
                break;
            case 3:
                valign = "top";
                break;
            default:
                break;
        }
        return valign;
    }

	/*private static String convertToHtmlGrammer(Colour colour) {
		StringBuffer sb = new StringBuffer("");
		if (colour != null && !"default background".equalsIgnoreCase(colour.getDescription())) {
			sb.append("#");
			sb.append(fillWithZero(Integer.toHexString(colour.getDefaultRGB().getRed())));
			sb.append(fillWithZero(Integer.toHexString(colour.getDefaultRGB().getGreen())));
			sb.append(fillWithZero(Integer.toHexString(colour.getDefaultRGB().getBlue())));
		}
		return sb.toString();
	}*/

    private static String fillWithZero(String str) {
        if (str != null && str.length() < 2) {
            return "0" + str;
        }
        return str;
    }
}