本项目excel在线预览利用OpenOffice实现

1. 后台实现(前台调用在前面的word转html中)

                                if(fileVO.getFileName().indexOf(".xls") > -1||fileVO.getFileName().indexOf(".xlsx") > -1
					||fileVO.getFileName().indexOf(".ppt") > -1||fileVO.getFileName().indexOf(".pptx") > -1)
					{
				URL url = new URL(Global.imgServer + "?file=" + fileVO.getFilePath() + "&name="
						+ URLEncoder.encode(fileVO.getFileName(), "utf-8"));
				HttpURLConnection conn = (HttpURLConnection) url.openConnection();
				//保存pdf之前，先调用解密方法
				DataInputStream input = new DataInputStream(conn.getInputStream());
				String filename = fileVO.getFileName();
				Boolean flag = request.getHeader("User-Agent").indexOf("like Gecko") > 0;
				if (request.getHeader("User-Agent").toLowerCase().indexOf("msie") > 0 || flag) {
					filename = URLEncoder.encode(filename, "UTF-8");// IE浏览器
				} else {
					// 先去掉文件名称中的空格,然后转换编码格式为utf-8,保证不出现乱码,
					// 这个文件名称用于浏览器的下载框中自动显示的文件名
					filename = new String(filename.replaceAll(" ", "").getBytes("UTF-8"), "ISO8859-1");
				}
				filename=filename.replaceAll("\\+","%20");
				byte[] buffer = new byte[1024];
				BufferedInputStream bis = null;
				File file=new  File(LawConfig.officeHtml+fileVO.getFileName());
				FileOutputStream out=new FileOutputStream(file,true);
				try {
					bis = new BufferedInputStream(input);
					int i = bis.read(buffer);
					while (i != -1) {
						out.write(buffer, 0, i);
						i = bis.read(buffer);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (bis != null) {
						try {
							bis.close();
						} catch (IOException e) {
							e.printStackTrace();
						}
					}
					if (input != null) {
						try {
							input.close();
						} catch (IOException e) {
							e.printStackTrace();
						}
					}
					out.close();
				}
				System.setProperty("user.dir", "解密接口地址");
				InteKey mInteKey = new InteKey();
				System.out.println("开始验证，文件是:"+LawConfig.officeHtml + fileVO.getFileName());
				int ia = mInteKey.Ia(LawConfig.officeHtml + fileVO.getFileName());
				System.out.println("验证结果：" + ia);
				if (ia == 0) {     
					// 加密文件，需要做解密处理
					System.out.println("开始解密");
					int da = mInteKey.Da(LawConfig.officeHtml + fileVO.getFileName(), LawConfig.officeHtml + fileVO.getFileName());
					System.out.println("解密结果：" + da);
				}
				InputStream is = new FileInputStream(LawConfig.officeHtml + fileVO.getFileName());
				//项目路径
				String path = request.getSession().getServletContext().getRealPath("/").replaceAll("\\\\", "/");
				String targetPath = path + "/page/mobile_html/";
				//类型
				String type =  fileVO.getFileName().substring(fileVO.getFileName().lastIndexOf(".")+1);
				//文件名
				String fileName = fileVO.getFilePath().substring(0, fileVO.getFilePath().lastIndexOf("."));
				//这里将方法写到了工具类中
				String returnPath = file2HtmlUtil.file2Html(is,LawConfig.officeHtml,targetPath,type,fileName);
				FileInputStream fis = null;
				OutputStream os = null;
				fis = new FileInputStream(targetPath+returnPath);
				os = response.getOutputStream();
				int count = 0;
				while ((count = fis.read(buffer)) != -1) {
					os.write(buffer, 0, count);
					os.flush();
				}
				fis.close();
				os.close();
				

工具类

package com.daorigin.law.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.ConnectException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.MissingResourceException;
import java.util.ResourceBundle;

import com.artofsolving.jodconverter.DocumentConverter;
import com.artofsolving.jodconverter.openoffice.connection.OpenOfficeConnection;
import com.artofsolving.jodconverter.openoffice.connection.SocketOpenOfficeConnection;
import com.artofsolving.jodconverter.openoffice.converter.OpenOfficeDocumentConverter;

public class file2HtmlUtil {

	public static String file2Html(InputStream fromFileInputStream, String sourcePath, String targetPath, String type, String fileName) throws IOException {
	    System.out.println("----------------------------------------------sourcePath:"+sourcePath);
	    System.out.println("----------------------------------------------targetPath:"+targetPath);
	    System.out.println("----------------------------------------------fileName:"+fileName);
	    String docFileName = null;
	    String htmFileName = null;
	     if("xls".equals(type)){
	        docFileName = fileName + ".xls";
	        htmFileName = fileName + ".html";
	    }else if("xlsx".equals(type)){
	        docFileName = fileName + ".xlsx";
	        htmFileName = fileName + ".html";
	    }else if("ppt".equals(type)){
	        docFileName = fileName + ".ppt";
	        htmFileName = fileName + ".html";
	    }else if("pptx".equals(type)){
	        docFileName = fileName + ".pptx";
	        htmFileName = fileName + ".html";
	    }else if("txt".equals(type)){
	        docFileName = fileName + ".odt";
	        htmFileName = fileName + ".html";
	    }else{
	        return null;
	    }

	    File htmlOutputFile = new File( targetPath + File.separatorChar + htmFileName);
	    System.out.println("++++++++++++++++++++++++++++++html路径："+htmlOutputFile);
	    File docInputFile = new File(sourcePath + File.separatorChar + docFileName);
	    System.out.println("++++++++++++++++++++++++++++++doc路径："+docInputFile);
	    if (htmlOutputFile.exists())
	        htmlOutputFile.delete();
	    File parentOut = htmlOutputFile.getParentFile();  // 获取父文件
	    if( !parentOut.exists() )  parentOut.mkdirs();  //创建所有父文件夹
	    htmlOutputFile.createNewFile();
	    if (docInputFile.exists())
	        docInputFile.delete();
	    File parentIn = docInputFile.getParentFile();  // 获取父文件
	    if( !parentIn.exists() )  parentIn.mkdirs();  //创建所有父文件夹
	    docInputFile.createNewFile();
	    /**
	     * 由fromFileInputStream构建输入文件
	     */
	    try {
	            OutputStream os = new FileOutputStream(docInputFile);
	            int bytesRead = 0;
	            byte[] buffer = new byte[1024 * 8];
	            while ((bytesRead = fromFileInputStream.read(buffer)) != -1) {
	                os.write(buffer, 0, bytesRead);
	      

	            os.close();
	            fromFileInputStream.close();
	        	
	        } catch (IOException e) {
	    }
            //OpenOffice默认端口是8100
	    OpenOfficeConnection connection = new SocketOpenOfficeConnection(8100);
	    try {
	        connection.connect();
	    } catch (ConnectException e) {
	        System.err.println("文件转换出错，请检查OpenOffice服务是否启动。");
	    }
	    // convert
	    DocumentConverter converter = new OpenOfficeDocumentConverter(connection,new DefaultDocumentFormatRegistry());
	    converter.convert(docInputFile, htmlOutputFile);
	    connection.disconnect();
	    // 转换完之后删除word文件
	    docInputFile.delete();
	    
	    return htmFileName;
	}
}

我在做excel转换的时候xls没有问题,xlsx转换失败,后来发现是默认定义的excel中没有xlsx格式,这里自己通过集成对应类的方式自定义了xlsx格式

package com.daorigin.law.util;

import com.artofsolving.jodconverter.DocumentFamily;
import com.artofsolving.jodconverter.DocumentFormat;

public class DefaultDocumentFormatRegistry extends com.artofsolving.jodconverter.DefaultDocumentFormatRegistry {

	public DefaultDocumentFormatRegistry() {
		super();
		//这个是自己加的
		final DocumentFormat xls = new DocumentFormat("Microsoft Excel", DocumentFamily.SPREADSHEET, "application/vnd.ms-excel", "xlsx");
		xls.setExportFilter(DocumentFamily.SPREADSHEET, "MS Excel 97");
		addDocumentFormat(xls);
		
	}
	
}

1 背景 

      当前有个需求，需要实时读取excel的内容，并显示在页面上。

       收到需求的时候， java后端能不能生成个临时文件，然后展示到前台页面，前台只要能请求到后端，预览就生效了。

2 Do IT

       2.1 引入pom文件

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.9</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.9</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-scratchpad</artifactId>
			<version>3.9</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.9</version>
		</dependency>
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.5</version>
		</dependency>
		<dependency>
			<groupId>org.jsoup</groupId>
			<artifactId>jsoup</artifactId>
			<version>1.10.3</version>
		</dependency>

 2.2 直接上代码

@GetMapping(value = "/showexcel")
    public void showexcel(HttpServletResponse response) throws Exception {
        String path = "C:\\Users\\Administrator\\Desktop\\临时\\";
        String file = "2021-03-11_2021年3月11日差异报表.xls";
        InputStream input = new FileInputStream(path + file);
        HSSFWorkbook excelBook = new HSSFWorkbook(input);
        ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
        excelToHtmlConverter.processWorkbook(excelBook);
        List pics = excelBook.getAllPictures();
        if (pics != null) {
            for (int i = 0; i < pics.size(); i++) {
                Picture pic = (Picture) pics.get(i);
                try {
                    pic.writeImageContent(new FileOutputStream(path + pic.suggestFullFileName()));
                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                }
            }
        }
        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, "utf-8");
        serializer.setOutputProperty(OutputKeys.INDENT, "yes");
        serializer.setOutputProperty(OutputKeys.METHOD, "html");
        serializer.transform(domSource, streamResult);
        outStream.close();
        String content = new String(outStream.toByteArray());
        String fileName =path + "exportExcel.html";
        FileUtils.writeStringToFile(new File(fileName), content, "utf-8");
        InputStream inputStream = new FileInputStream(fileName);
        FileInputStream fileIs = null;
        try {
            fileIs = (FileInputStream) inputStream;
        } catch (Exception e) {
            return;
        }
        //得到文件大小
        byte data[] = new byte[fileIs.available()];
        //读数据
        int count = 0;
         fileIs.read(data);
        //设置返回的文件类型
        response.setContentType("image.png");
        //得到向客户端输出二进制数据的对象
        OutputStream outStreams = response.getOutputStream();
        //输出数据
        outStreams.write(data);
        outStreams.flush();
        outStreams.close();
        fileIs.close();
    }

3 查看效果

4 相关参考

     部分代码参考了如下博客，虽然他们只讲了一半，实操不行，但是还是有可取之处。

     java实现在线预览--poi实现word、excel、ppt转html