(POI) Excel format to HTML format

Demo structure and the referenced jar package

Source Code (Testdemo.java)

The conversion of Excel to HTML method in POI can only convert the Hssfworkbook type (that is, version 03 xls), so it is possible to convert the read xlsx file into an XLS file and then call the method to process it uniformly

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 ();           Determine if the EXCEL file converts 07 + version to version 03 if (File.endswith (Excel_xls)) {//excel 2003Excelbook = new Hssfworkbook (input);} else if (File.endswith (excel_xlsx)) {//EXCEL 2007/2010Transform xls = new Transform ();             　　 Xssfworkbook workbookold = new Xssfworkbook (input); XLS.TRANSFORMXSSF (Workbookold, excelbook);} exceltohtmlconverter Exceltohtmlconverter = new Exceltohtmlconverter (documentbuilderfactory.newinstance                 (). Newdocumentbuilder (). NewDocument ());          Remove Excel header row Exceltohtmlconverter.setoutputcolumnheaders (false); Remove the Excel line number 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 converts HTML string content = new String (Outstream.tobytearray ()); SYSTEM.OUT.PRINTLN (content);} catch (Exception e) {e.printstacktrace ();}}} 

　

The

Source code (Transform.java) converts an xlsx file to an XLS file. (You can handle formatting problems such as merging cells, borders, etc.!!) )

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 Sheeto          LD, 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.getcolumnwidt              H (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, HS          Sfrow 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, C          Ellnew);      } This.lastcolumn = Math.max (This.lastcolumn, Rowold.getlastcellnum ()); } private void Transform (Xssfworkbook workbookold, Hssfworkbook workbooknew, Xssfcell cellold, Hssfcel            L cellnew) {cellnew.setcellcomment (cellold.getcellcomment ());          Integer hash = Cellold.getcellstyle (). Hashcode (); if (This.stylemap! = null &&!this.stylemap.containskey (hash)) {This.transform (workbookold, workbook New, 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, XSSF CellStyle 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;   } }  

　　

NET disk Link: https://pan.baidu.com/s/1I7ZH4gXrTMPR-_zIjCpGCg Password: z3gj

(POI) Excel format to HTML format