XSSFWorkbook转为HSSFWorkbook并显示到页面-程序员宅基地

技术标签: ViewUI  java  前端  人工智能  

package ibp.common.report;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.StringWriter;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class TransToImgUtil {
     public static String convertExceltoHtml(String path) throws IOException,ParserConfigurationException, TransformerException,InvalidFormatException {
         HSSFWorkbook workBook = null;
         String content = null;
         StringWriter writer = null;
         File excelFile = new File(path);
         InputStream is = new FileInputStream(excelFile);
         String suffix = path.substring(path.lastIndexOf("."));
         if(suffix.equals(".xlsx")){
             //将07版转化为03版
             Xssf2Hssf xlsx2xls = new Xssf2Hssf();
             XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(is);
             workBook = new HSSFWorkbook();
             xlsx2xls.transformXSSF(xSSFWorkbook, workBook);
         }else{
             workBook = new HSSFWorkbook(is);
         }
         try {
             ExcelToHtmlConverter converter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
             converter.setOutputColumnHeaders(false);// 不显示列的表头
             converter.setOutputRowNumbers(false);// 不显示行的表头
             converter.processWorkbook(workBook);
 
             writer = new StringWriter();
             Transformer serializer = TransformerFactory.newInstance().newTransformer();
             serializer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
             serializer.setOutputProperty(OutputKeys.INDENT, "yes");
             serializer.setOutputProperty(OutputKeys.METHOD, "html");
             serializer.transform(new DOMSource(converter.getDocument()),
                     new StreamResult(writer));
             content = writer.toString();
             writer.close();
         } finally {
             try {
                 if (is != null) {
                     is.close();
                 }
                 if (writer != null) {
                     writer.close();
                 }
             } catch (IOException e) {
                 e.printStackTrace();
             }
         }
         return content;
     }
}
package ibp.common.report;

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 Xssf2Hssf {  
  
    private int lastColumn = 0;  
    private HashMap<Integer, HSSFCellStyle> styleMap = new HashMap();  
  
    public void transformXSSF(XSSFWorkbook workbookOld,  
            HSSFWorkbook workbookNew) {
        String call = "transform ";  
        System.out.println(call + "Workbook");  
        HSSFSheet sheetNew;  
        XSSFSheet sheetOld;  
        // TODO::workbookNew.setForceFormulaRecalculation(workbookOld.getForceFormulaRecalculation());  
        // workbookNew.setHidden(workbookOld.isHidden()); //[email protected] -  
        // von Apache noch nicht implementiert  
        workbookNew.setMissingCellPolicy(workbookOld.getMissingCellPolicy());  
  
        for (int i = 0; i < workbookOld.getNumberOfSheets(); i++) {  
            sheetOld = workbookOld.getSheetAt(i);  
            sheetNew = workbookNew.getSheet(sheetOld.getSheetName());  
            System.out.println(call + "Sheet Name: " + sheetOld.getSheetName());  
            sheetNew = workbookNew.createSheet(sheetOld.getSheetName());  
            this.transform(workbookOld, workbookNew, sheetOld, sheetNew);  
        }  
        System.out.println(call + "Styles size: " + this.styleMap.size());  
        System.out.println(call + "abgeschlossen");  
    }  
  
    private void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            XSSFSheet sheetOld, HSSFSheet sheetNew) {  
        System.out.println("transform Sheet");  
  
        sheetNew.setDisplayFormulas(sheetOld.isDisplayFormulas());  
        sheetNew.setDisplayGridlines(sheetOld.isDisplayGridlines());  
        sheetNew.setDisplayGuts(sheetOld.getDisplayGuts());  
        sheetNew.setDisplayRowColHeadings(sheetOld.isDisplayRowColHeadings());  
        sheetNew.setDisplayZeros(sheetOld.isDisplayZeros());  
        sheetNew.setFitToPage(sheetOld.getFitToPage());  
        //  
        // TODO::sheetNew.setForceFormulaRecalculation(sheetOld.getForceFormulaRecalculation());  
        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());  
        // TODO::if (rowOld.getRowStyle() != null) {  
        /* 
         * Integer hash = rowOld.getRowStyle().hashCode(); if 
         * (!this.styleMap.containsKey(hash)) 
         * this.transform(workbookOld,workbookNew,hash, 
         * (XSSFCellStyle)rowOld.getRowStyle 
         * (),(HSSFCellStyle)workbookNew.createCellStyle()); 
         * rowNew.setRowStyle(this.styleMap.get(hash)); } 
         */  
        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://www.cnblogs.com/zhouxuan323/p/6044850.html

http://blog.csdn.net/leozhou13/article/details/50371218

转载于:https://www.cnblogs.com/seeusmile-cnblog/p/8027911.html

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_30700099/article/details/99185872

智能推荐

谈谈对服务化的理解-程序员宅基地

文章浏览阅读391次,点赞5次,收藏5次。首先:服务化是什么,为什么要服务化服务化是一种将业务、功能或流程抽象为服务的设计方法,通过服务之间的协作和调用来实现系统的整合和复用。它可以提高系统的可维护性和扩展性,主要用来构建分布式系统。然后:怎样服务化SOA和微服务都是实现了服务化的、比较典型的架构风格。SOA的出现是为了解决功能复用的问题,将一些通用的模块提取出来做成服务。但是SOA对于通用模块的设计没有设计核心原则,因此在对应需求变化等...

关于thinkphp的__construct和_initialize-程序员宅基地

文章浏览阅读128次。先说一下__construct吧,construct是php的构造函数,每次new这个类的时候会运行这个函数,子类继承父类时,如果子类中没有construct的时候,会自动调用父类的,如果有,则调用子类的,如果想调用父类的要加parent::__construct()。_initialize()是thinkphp特有的函数,注意是只有一个下划线_而已!这个函数只会在此class有继承父类的..._call to protected yzh\baseclient::__construct() from context 'user\controlle

aes加密工具类-程序员宅基地

文章浏览阅读5.1k次。import javax.crypto.*;import javax.crypto.spec.SecretKeySpec;import java.io.UnsupportedEncodingException;import java.security.InvalidKeyException;import java.security.NoSuchAlgorithmException;import java.security.SecureRandom;public class AesUtil {_aes加密工具类

enable cef support ffmpeg_cefpython ffmpeg-程序员宅基地

文章浏览阅读1.9k次。refefences:https://bitbucket.org/chromiumembedded/cef/wiki/BranchesAndBuilding这里有源码包列表。如果automate-git.py git下载失败,只能申请海外服务器。 Env precondition:Win 7+, VS2015u3, Win10.0.14393 SDK, Ninja 1...._cefpython ffmpeg

Spring @Scheduled定时任务每次执行两次_@scheduled执行两次-程序员宅基地

文章浏览阅读4.2k次。我所遇到定时任务每次执行两次,是因为:1、在spring配置中定义了bean,例如:<bean id="dyGoodsMQListener" class="com.XX.union.open.push.goods.mq.DYGoodsMQListener"/>2、在对象中又加了注解:@Componentpublic class DYGoodsMQListener ..._@scheduled执行两次

图像缩放双线性插值算法_缩放比例越大 双线性插值越大吗-程序员宅基地

文章浏览阅读3w次。插值算法对于缩放比例较小的情况是完全可以接受的,令人信服的。一般的,缩小0.5倍以上或放大3.0倍以下,对任何图像都是可以接受的。最邻近插值(近邻取样法):  最临近插值的的思想很简单。对于通过反向变换得到的的一个浮点坐标,对其进行简单的取整,得到一个整数型坐标,这个整数型坐标对应的像素值就是目的像素的像素值,也就是说,取浮点坐标最邻近的左上角点(对于DIB是右上角,因为它的扫描行是逆序存储的_缩放比例越大 双线性插值越大吗

随便推点

空格字符和空字符的区别_ascii码中的空格和空字符的区别-程序员宅基地

文章浏览阅读1w次,点赞2次,收藏6次。在开发中看到需求上这样一条:报文头里面,允许输入空值的地方,要使用空格填充,即byte 0x20,不能使用byte 0x00。一开始完全不明白在说什么,经过查阅之后才明白过来:上述的0x20 和0x00指的是字符的ascii码16进制0x20, 十进制32:空格字符 指键盘上长长的那个键产生的字符而16进制0x00, 十进制0 :空字符 指字符串结尾系统自动_ascii码中的空格和空字符的区别

[1126]kerberos认证相关问题_keytab contains no suitable keys for lucanet@nyoco-程序员宅基地

文章浏览阅读2.6k次。文章目录HDP WARN ipc.Client: Exception encountered while connecting to the server : org.apache.hadoop.securikinit: Keytab contains no suitable keys for [email protected] while getting initial credentials原因:可能导致问题:解决方案:测试验证:HDP WARN ipc.Client: Exception encount_keytab contains no suitable keys for [email protected] while getting init

php7 phpize,安装phpize7.3-程序员宅基地

文章浏览阅读646次。1,背景在现有的ubuntu16.04中,装了好几个版本的PHP/etc/php/5.6/ 7.0/ 7.1/ 7.2/ 7.3/whereis phpphp: /usr/bin/php7.3 /usr/bin/php7.0 /usr/bin/php /usr/lib/php /etc/php /usr/include/php/usr/share/php7.3-bcmath /usr/share/..._php7.3 phpize

Mycat高可用负载均衡集群的实现(HAProxy + Keepalived + Mycat)_服务不可用了以后,keepalived会告诉haproxy不再往上转发了吗-程序员宅基地

文章浏览阅读639次。转载请注明出处:http://blog.csdn.net/l1028386804/article/details/76397064本文是在博文《Mycat之——Mycat集群部署(基于HAProxy + Mycat)》的基础上,实现高可用负载均衡,若还没有阅读博文《Mycat之——Mycat集群部署(基于HAProxy + Mycat)》,请先阅读博文《Mycat之——Mycat集群部署(基于..._服务不可用了以后,keepalived会告诉haproxy不再往上转发了吗

Flex 跨域访问解决方案_"<cross-domain-policy> <allow-access-from domain=\-程序员宅基地

文章浏览阅读108次。想让swf 文件在一台服务器上运行并且能够访问另一台服务器上的资源,这时就会遇到跨域问题,请看下面的解决方案。 写一个名字为 crossdomain.xml 的XML文件,这个方法是允许任何Flash都可以调用,放到被调用文件的网站根目录下:view plaincopy to clipboardprint?&lt;?xml version="1.0"?&gt; &lt;!DOCTYPE..._"

Java异常执行顺序(9/5/2018)_java的异常抛出顺序-程序员宅基地

文章浏览阅读722次。public class ExceptionDemo { public static void main(String[] args) throws TestException { try { Test(); System.out.println("无论如何都不执行"); } catch (T..._java的异常抛出顺序

推荐文章

热门文章

相关标签