（POI）Excel格式转Html格式

程序员文章站 2022-04-09 21:03:05

Demo结构和引用的Jar包源代码(TestDemo.java) POI中将Excel转换为HTML方法仅能转换HSSFWorkBook类型（即03版xls），故可以先将读取的xlsx文件转换成xls文件再调用该方法统一处理源代码(Transform.java)　将xlsx文件转换成xls文件。 ......

Demo结构和引用的Jar包

（POI）Excel格式转Html格式

源代码(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

上一篇：爆笑儿童，两个老爸

下一篇：这小朋友太萌太幽默

（POI）Excel格式转Html格式

Python实现将HTML转换成doc格式文件的方法示例

swf格式如何转flv格式？swf格式转flv格式的方法

举例详解HTML5中使用JSON格式提交表单

itudou怎么转格式？itudou转换格式教程

python 3.6 群发附件 html格式邮件

Word、Excel、PowerPoint 2007 文件格式兼容包下载

Excel 中无法批量完成对数字的格式设置但Word 可以

Excel小技巧之秒选神器条件格式应用实例(选择+上色)

EXCEL使用文本导入向导打开含有 0开头的CSV格式的文件

通过Excel快速分析对图表格式、图表、汇总等进行快速分析

（POI）Excel格式转Html格式

Python实现将HTML转换成doc格式文件的方法示例

swf格式如何转flv格式？swf格式转flv格式的方法

举例详解HTML5中使用JSON格式提交表单

itudou怎么转格式？itudou转换格式教程

python 3.6 群发 附件 html格式 邮件

Word、Excel、PowerPoint 2007 文件格式兼容包下载

Excel 中无法批量完成对数字的格式设置但Word 可以

Excel小技巧之秒选神器条件格式应用实例(选择+上色)

EXCEL使用文本导入向导打开含有 0开头的CSV格式的文件

通过Excel快速分析对图表格式、图表、汇总等进行快速分析

python 3.6 群发附件 html格式邮件