（POI）Excel格式转Html格式

最新推荐文章于 2023-02-20 10:23:21 发布
最新推荐文章于 2023-02-20 10:23:21 发布
阅读量530 收藏 2
点赞数
文章标签： java 人工智能
原文链接：http://www.cnblogs.com/LemonFive/p/8990852.html
版权

Demo结构和引用的Jar包

 

源代码(TestDemo.java)

POI中将Excel转换为HTML方法仅能转换HSSFWorkBook类型（即03版xls），故可以先将读取的xlsx文件转换成xls文件再调用该方法统一处理

package test;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.hssf.converter.ExcelToHtmlConverter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;

public class TestDemo {

	final static String path = "D:\\EclipseWorkspace\\ExcelToHtmlDemo\\ExcelToHtml\\";
	final static String file = "TestExcel.xlsx";
        private static final String EXCEL_XLS = "xls";  
        private static final String EXCEL_XLSX = "xlsx";  
	     
	public static void main(String[] args)
	{
		try{
	        InputStream input = new FileInputStream(path +"/"+ file);  
	        HSSFWorkbook excelBook = new HSSFWorkbook();
	        //判断Excel文件将07+版本转换为03版本
	        if(file.endsWith(EXCEL_XLS)){  //Excel 2003  
	        	excelBook = new HSSFWorkbook(input);  
	        }
	        else if(file.endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
	        	Transform xls = new Transform();    
	        	XSSFWorkbook workbookOld = new XSSFWorkbook(input); 
	            　　 xls.transformXSSF(workbookOld, excelBook);
	        }  
	        
	        ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument()); 	        
	        //去掉Excel头行  
	        excelToHtmlConverter.setOutputColumnHeaders(false);  
	        //去掉Excel行号  
	        excelToHtmlConverter.setOutputRowNumbers(false);  
	          
	        excelToHtmlConverter.processWorkbook(excelBook); 
	          
	        Document htmlDocument = excelToHtmlConverter.getDocument();  
	  
	        ByteArrayOutputStream outStream = new ByteArrayOutputStream();  
	        DOMSource domSource = new DOMSource(htmlDocument);  
	        StreamResult streamResult = new StreamResult(outStream);  
	        TransformerFactory tf = TransformerFactory.newInstance();  
	        Transformer serializer = tf.newTransformer();  
	          	        
	        serializer.setOutputProperty(OutputKeys.ENCODING, "gb2312");  
	        serializer.setOutputProperty(OutputKeys.INDENT, "yes");  
	        serializer.setOutputProperty(OutputKeys.METHOD, "html");  
	          
	        serializer.transform(domSource, streamResult);  
	        outStream.close();  
	  
	        //Excel转换成Html
	        String content = new String(outStream.toByteArray());  
                System.out.println(content);
		}
		catch(Exception e) {
			e.printStackTrace();			
		}
	}
}

　

源代码(Transform.java)　将xlsx文件转换成xls文件。（可以处理合并单元格，边框等格式问题！！！）

package test;

import java.util.HashMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Transform {  
   
    private int lastColumn = 0;  
    private HashMap<Integer, HSSFCellStyle> styleMap = new HashMap();  
   
    public void transformXSSF(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew) {   
        HSSFSheet sheetNew;  
        XSSFSheet sheetOld;  

        workbookNew.setMissingCellPolicy(workbookOld.getMissingCellPolicy());  
  
        for (int i = 0; i < workbookOld.getNumberOfSheets(); i++) {  
            sheetOld = workbookOld.getSheetAt(i);  
            sheetNew = workbookNew.getSheet(sheetOld.getSheetName());  
            sheetNew = workbookNew.createSheet(sheetOld.getSheetName());  
            this.transform(workbookOld, workbookNew, sheetOld, sheetNew);  
        }    
    }  
  
    private void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            XSSFSheet sheetOld, HSSFSheet sheetNew) {  
  
        sheetNew.setDisplayFormulas(sheetOld.isDisplayFormulas());  
        sheetNew.setDisplayGridlines(sheetOld.isDisplayGridlines());  
        sheetNew.setDisplayGuts(sheetOld.getDisplayGuts());  
        sheetNew.setDisplayRowColHeadings(sheetOld.isDisplayRowColHeadings());  
        sheetNew.setDisplayZeros(sheetOld.isDisplayZeros());  
        sheetNew.setFitToPage(sheetOld.getFitToPage());  
        
        sheetNew.setHorizontallyCenter(sheetOld.getHorizontallyCenter());  
        sheetNew.setMargin(Sheet.BottomMargin,  
                sheetOld.getMargin(Sheet.BottomMargin));  
        sheetNew.setMargin(Sheet.FooterMargin,  
                sheetOld.getMargin(Sheet.FooterMargin));  
        sheetNew.setMargin(Sheet.HeaderMargin,  
                sheetOld.getMargin(Sheet.HeaderMargin));  
        sheetNew.setMargin(Sheet.LeftMargin,  
                sheetOld.getMargin(Sheet.LeftMargin));  
        sheetNew.setMargin(Sheet.RightMargin,  
                sheetOld.getMargin(Sheet.RightMargin));  
        sheetNew.setMargin(Sheet.TopMargin, sheetOld.getMargin(Sheet.TopMargin));  
        sheetNew.setPrintGridlines(sheetNew.isPrintGridlines());  
        sheetNew.setRightToLeft(sheetNew.isRightToLeft());  
        sheetNew.setRowSumsBelow(sheetNew.getRowSumsBelow());  
        sheetNew.setRowSumsRight(sheetNew.getRowSumsRight());  
        sheetNew.setVerticallyCenter(sheetOld.getVerticallyCenter());  
  
        HSSFRow rowNew;  
        for (Row row : sheetOld) {  
            rowNew = sheetNew.createRow(row.getRowNum());  
            if (rowNew != null)  
                this.transform(workbookOld, workbookNew, (XSSFRow) row, rowNew);  
        }  
  
        for (int i = 0; i < this.lastColumn; i++) {  
            sheetNew.setColumnWidth(i, sheetOld.getColumnWidth(i));  
            sheetNew.setColumnHidden(i, sheetOld.isColumnHidden(i));  
        }  
  
        for (int i = 0; i < sheetOld.getNumMergedRegions(); i++) {  
            CellRangeAddress merged = sheetOld.getMergedRegion(i);  
            sheetNew.addMergedRegion(merged);  
        }  
    }  
  
    private void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            XSSFRow rowOld, HSSFRow rowNew) {  
        HSSFCell cellNew;  
        rowNew.setHeight(rowOld.getHeight());  
 
        for (Cell cell : rowOld) {  
            cellNew = rowNew.createCell(cell.getColumnIndex(),  
                    cell.getCellType());  
            if (cellNew != null)  
                this.transform(workbookOld, workbookNew, (XSSFCell) cell,  
                        cellNew);  
        }  
        this.lastColumn = Math.max(this.lastColumn, rowOld.getLastCellNum());  
    }  
  
    private void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            XSSFCell cellOld, HSSFCell cellNew) {  
        cellNew.setCellComment(cellOld.getCellComment());  
  
        Integer hash = cellOld.getCellStyle().hashCode();  
        if (this.styleMap != null && !this.styleMap.containsKey(hash)) {  
            this.transform(workbookOld, workbookNew, hash,  
                    cellOld.getCellStyle(),  
                    (HSSFCellStyle) workbookNew.createCellStyle());  
        }  
        cellNew.setCellStyle(this.styleMap.get(hash));  
  
        switch (cellOld.getCellType()) {  
        case Cell.CELL_TYPE_BLANK:  
            break;  
        case Cell.CELL_TYPE_BOOLEAN:  
            cellNew.setCellValue(cellOld.getBooleanCellValue());  
            break;  
        case Cell.CELL_TYPE_ERROR:  
            cellNew.setCellValue(cellOld.getErrorCellValue());  
            break;  
        case Cell.CELL_TYPE_FORMULA:  
            cellNew.setCellValue(cellOld.getCellFormula());  
            break;  
        case Cell.CELL_TYPE_NUMERIC:  
            cellNew.setCellValue(cellOld.getNumericCellValue());  
            break;  
        case Cell.CELL_TYPE_STRING:  
            cellNew.setCellValue(cellOld.getStringCellValue());  
            break;  
        default:  
            System.out.println("transform: Unbekannter Zellentyp "  
                    + cellOld.getCellType());  
        }  
    }  
  
    private void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            Integer hash, XSSFCellStyle styleOld, HSSFCellStyle styleNew) {  
        styleNew.setAlignment(styleOld.getAlignment());  
        styleNew.setBorderBottom(styleOld.getBorderBottom());  
        styleNew.setBorderLeft(styleOld.getBorderLeft());  
        styleNew.setBorderRight(styleOld.getBorderRight());  
        styleNew.setBorderTop(styleOld.getBorderTop());  
        styleNew.setDataFormat(this.transform(workbookOld, workbookNew,  
                styleOld.getDataFormat()));  
        styleNew.setFillBackgroundColor(styleOld.getFillBackgroundColor());  
        styleNew.setFillForegroundColor(styleOld.getFillForegroundColor());  
        styleNew.setFillPattern(styleOld.getFillPattern());  
        styleNew.setFont(this.transform(workbookNew,  
                (XSSFFont) styleOld.getFont()));  
        styleNew.setHidden(styleOld.getHidden());  
        styleNew.setIndention(styleOld.getIndention());  
        styleNew.setLocked(styleOld.getLocked());  
        styleNew.setVerticalAlignment(styleOld.getVerticalAlignment());  
        styleNew.setWrapText(styleOld.getWrapText());  
        this.styleMap.put(hash, styleNew);  
    }  
  
    private short transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            short index) {  
        DataFormat formatOld = workbookOld.createDataFormat();  
        DataFormat formatNew = workbookNew.createDataFormat();  
        return formatNew.getFormat(formatOld.getFormat(index));  
    }  
  
    private HSSFFont transform(HSSFWorkbook workbookNew, XSSFFont fontOld) {  
        HSSFFont fontNew = workbookNew.createFont();  
        fontNew.setBoldweight(fontOld.getBoldweight());  
        fontNew.setCharSet(fontOld.getCharSet());  
        fontNew.setColor(fontOld.getColor());  
        fontNew.setFontName(fontOld.getFontName());  
        fontNew.setFontHeight(fontOld.getFontHeight());  
        fontNew.setItalic(fontOld.getItalic());  
        fontNew.setStrikeout(fontOld.getStrikeout());  
        fontNew.setTypeOffset(fontOld.getTypeOffset());  
        fontNew.setUnderline(fontOld.getUnderline());  
        return fontNew;  
    }   
}

　　

网盘链接：https://pan.baidu.com/s/1I7ZH4gXrTMPR-_zIjCpGCg 密码：z3gj

转载于:https://www.cnblogs.com/LemonFive/p/8990852.html

Trl13919
关注
基于poi3.14做的excel(.xlsx)转html的功能
lylhjh的博客
09-05 9779
实现POi深度解析转换html。
poi excel html,ExcelToHtmlConverter
weixin_39702649的博客
06-16 595
Method Detailmainpublic staticvoidmain(java.lang.String[]args)throws java.lang.ExceptionJava main() interface to interact with ExcelToHtmlConverterUsage: ExcelToHtmlConverter infile outfileWhere in...
poi操作Excel转Html
CSDN已弃用
10-18 1942
package com.app.util; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; impor...
POI 把 excel 转 html
a1127671368的博客
06-01 3044
1.导入 maven项目 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${poi.version}</version> ...
使用poi将excel转换为html,适用本身有导出excel的而现在需要添加网页打印的功能
hellogril前端库
01-04 975
PoiExcelToHtmlUtil.java import org.apache.poi.POIXMLDocumentPart; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org....
JAVA POI Excel转Html
03-06
JAVA POI Excel转Html，代码和所需的jar都在压缩包，项目在线预览需求，实现后分享下
poi excel转html
12-29
poi提供了excel转html的代码，但是吧，有好多问题。当有合并单元格时边框获取不到，单元格内的字体样式也转换不了，现在对poi提供的Tohtml.java做了一些修改，修复了这些问题，记录下，以备之后用到
POI实现将excel表格转换成html代码
05-07
统计报表功能有excel转html的场景，读取指定位置的excel，将其转换成html形式的字符串。考虑到Apache成熟的Office文档处理工具POI的广受欢迎，POI提供了强大的excel表格处理的能力，不论是读还是写，都对开发人员...
poiexcel导出html格式
03-25
在 POI 3.10 及以后的版本中，引入了对将 Excel 工作簿转换为 HTML 格式的支持，这极大地扩展了其在数据导出和网页展示中的应用。以下是对这个功能的详细讲解： 1. **Apache POI 概述**： Apache POI 是 Java 平台...
poi实现excel转html
u010385090的博客
04-16 3195
使用poi将excel转为html，如果需要表格可编辑，可以让你们前端给提供样式设置到代码里，码云代码库位置poiexample，欢迎来丰富它的功能 poi版本 4.1.2 pom依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId...
poi解析excel，转变为html（支持excel03和07）
05-17
POI 解析excel，转成html页面（支持excel03和07）包括style
基于poi实现word/excel转换为HTML（且兼容.doc.docx.xls.xlsx）
03-17
基于poi实现word/excel转换为HTML（且兼容.doc.docx.xls.xlsx） 对于简单的word Excel 转换 足够。excel转换时存在火狐浏览器不兼容乱码问题，希望多多交流。
org.apache.poi.hssf.converter，office转html所需包
02-28
excel转html word转html ppt转html 所需jar包 用于excel、word、ppt在线预览功能的实现
poi把xlsx转为html字符串，用于发邮件正文,调用python生成邮件图片
kingwin28的博客
02-20 207
poi把xlsx转为html字符串，用于发邮件正文,调用python生成邮件图片
Java - poi将Excel转化为Html，支持图片
y_j_zhang的博客
10-11 2711
需求：将Excel转化为HTML，支持图片，支持多Sheet。 要求：1. 第三方插件免费开源；2. 支持跨平台 设计方案： 1. 采用Apache POI； 2. 多个Sheet时，下个Sheet内容向下对齐HTML。 3. 图片以文件的形式单独存储，或者以二进制字符串的形式保存在HTML文件中； 开发难点： 1. HTML中图片位置应该和Excel保持一致（图片向下对齐比较简单，...
利用POI将Excel文档转换为Html
David
08-27 8089
excel文档转换为html其实和word转html相类似，优点也很明显。请参考 word转html         public static void convertExcel2Html(String excelFilePath,String htmlFilePath) throws IOException,ParserConfigurationException,Transforme
poi xlsx转换html,POI Word DOC格式转Html
weixin_42134338的博客
05-31 152
package com.office;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStreamWriter;import javax.xml.parsers.DocumentBuil...
java excel to html,POI 3.9 将excel转换成html显示 ExcelToHtmlConverter
weixin_28689193的博客
03-13 225
POI 3.9 新特性 Cover， 可直接将excel 2003 文件 转换成 html 文件， 不用再一一分析处理。当然，方便的同时也有弊端，那就是格式固定，处理多sheet时兼容性不好。try{Document doc = ExcelToHtmlConverter.process( new File( args[0] ) );FileWriter out = new FileWriter( ...
java 使用POI 将excel转换为html 日期格式
最新发布
06-07
在使用POI将Excel转换为HTML时，日期格式的转换需要注意一些问题。下面是一个示例代码，演示如何使用POI将Excel中的日期格式转换为HTML中的日期格式： ```java import java.io.FileOutputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.Date; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelToHtmlConverter { public static void main(String[] args) throws IOException { // 读取Excel文件 Workbook workbook = WorkbookFactory.create(ExcelToHtmlConverter.class.getResourceAsStream("test.xlsx")); // 创建HTML文件 FileOutputStream fos = new FileOutputStream("test.html"); // 输出HTML头部 StringBuilder sb = new StringBuilder(); sb.append("<html>"); sb.append("<head>"); sb.append("<meta charset=\"UTF-8\">"); sb.append("</head>"); sb.append("<body>"); sb.append("<table>"); // 遍历每个Sheet for (int i = 0; i < workbook.getNumberOfSheets(); i++) { // 获取当前Sheet String sheetName = workbook.getSheetName(i); sb.append("<tr>"); sb.append("<td colspan=\"100%\" style=\"font-size: 20px; font-weight: bold; text-align: center;\">"); sb.append(sheetName); sb.append("</td>"); sb.append("</tr>"); // 遍历每行数据 for (int j = 0; j < workbook.getSheetAt(i).getPhysicalNumberOfRows(); j++) { sb.append("<tr>"); // 遍历每列数据 for (int k = 0; k < workbook.getSheetAt(i).getRow(j).getLastCellNum(); k++) { Cell cell = workbook.getSheetAt(i).getRow(j).getCell(k); if (cell != null) { CellAddress cellAddress = cell.getAddress(); DataFormatter dataFormatter = new DataFormatter(); String cellValue = dataFormatter.formatCellValue(cell); switch (cell.getCellType()) { case STRING: sb.append("<td>"); sb.append(cellValue); sb.append("</td>"); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String dateString = sdf.format(date); sb.append("<td>"); sb.append(dateString); sb.append("</td>"); } else { sb.append("<td>"); sb.append(cell.getNumericCellValue()); sb.append("</td>"); } break; case BOOLEAN: sb.append("<td>"); sb.append(cell.getBooleanCellValue()); sb.append("</td>"); break; case FORMULA: sb.append("<td>"); sb.append(cell.getCellFormula()); sb.append("</td>"); break; default: sb.append("<td>"); sb.append(""); sb.append("</td>"); break; } } else { sb.append("<td>"); sb.append(""); sb.append("</td>"); } } sb.append("</tr>"); } } // 输出HTML尾部 sb.append("</table>"); sb.append("</body>"); sb.append("</html>"); // 写入HTML文件 fos.write(sb.toString().getBytes()); fos.close(); // 关闭workbook workbook.close(); } } ``` 在上面的代码中，我们使用了POI库的DataFormatter类来获取单元格中的值，并判断单元格是否为日期格式。如果单元格为日期格式，则使用SimpleDateFormat类将日期格式转换为字符串格式。最后，我们将Excel中的数据输出为HTML文件。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值