Excel的导入与导出

由于公司的很多系统都是好多年前做的，在公司的Office升级完成以后，公司内部的系统对新版本的Excel支持问题就一一浮现出来。越来越多的人在抱怨公司内部对excel2007和2010不支持的问题。最近研究了一下java对excel的操作，发现使用现有的工具对excel2007和2010的支持并不是那么难，现将自己的心得分享出来。

 

所需的jar包： Jxls-core 1.0.5， Poi 3.10， Poi-ooxml 3.10， Poi-ooxml-schemas 3.10， Commons-beanutils 1.9.2， Commons-digister 2.0， Commons-jexl 2.1.1， Commons-logging 1.1.3， Xmlbeans 2.3。

 

公司内部使用的SSH架构。

 

导出：

这里使用jxls包，使用excel template来导出文件。官网：http://jxls.sourceforge.net/index.html

excel template的写法请参照官网，下面是导出excel的代码，导出的excel版本根据你创建template的版本来定：

 

package com.apps.core.struts.dispatcher;

import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.io.PushbackInputStream;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletResponse;

import net.sf.jxls.transformer.XLSTransformer;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.dispatcher.StrutsResultSupport;

import com.opensymphony.xwork2.ActionInvocation;

public class ExcelResult extends StrutsResultSupport {

	private static final long serialVersionUID = 462425636352447077L;
	private static final String contentTypeXls = "application/vnd.ms-excel";
	private static final String contentTypeXlsx = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    private static final String actionKey = "action";

    private boolean multiSheet = false;
    private String listName;
    private String sheetName;
    private String beanName;
    
	@Override
	protected void doExecute(String location,
			ActionInvocation invocation) throws Exception {
        HttpServletResponse response = ServletActionContext.getResponse();

        response.reset();
        
        Workbook workbook;
        InputStream in = null;
        //这里对excel版本的判断借鉴与POI-OOXML包里的方法
        try {
            in = this.getTemplate(location, ServletActionContext.getServletContext());
            //check the file support mark or reset function
			if (!in.markSupported()) {
				in = new PushbackInputStream(in, 8);
			}
			
			XLSTransformer transformer = new XLSTransformer();
            Map<String, Object> beans = PropertyUtils.describe(invocation.getAction());
            beans.put(actionKey, invocation.getAction());
            
            //check input file type
            //for excel 2003
			if (POIFSFileSystem.hasPOIFSHeader(in)) {
		        response.setContentType(contentTypeXls);
	            //we use action name plus timestamp as the filename
	            response.setHeader("Content-Disposition", "attachment; filename=\"" + invocation.getProxy().getActionName() + "_" + System.currentTimeMillis()
	            			+ ".xls\"");
			}
			//for excel 2007,2010
			else if (POIXMLDocument.hasOOXMLHeader(in)) {
		        response.setContentType(contentTypeXlsx);
		        //we use action name plus timestamp as the filename
	            response.setHeader("Content-Disposition", "attachment; filename=\"" + invocation.getProxy().getActionName() + "_" + System.currentTimeMillis()
	            			+ ".xlsx\"");
			}
			//exception
			else{
				throw new Exception("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
			}
			
			//transform workbook
            if (multiSheet) {
                workbook = transformer.transformMultipleSheetsList(in, (List<?>) invocation.getStack().findValue(listName), (List<?>) invocation.getStack()
                        .findValue(sheetName), beanName, beans, 0);
            } else {
                workbook = transformer.transformXLS(in, beans);
            }
            //output
            OutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        } finally {
            if (in != null) {
                in.close();
            }
        }
	}

    protected InputStream getTemplate(String location, ServletContext servletContext) {
        return servletContext.getResourceAsStream(location);
    }


    //getters and setters
    public boolean isMultiSheet() {
        return multiSheet;
    }

    public void setMultiSheet(boolean multiSheet) {
        this.multiSheet = multiSheet;
    }

    public String getBeanName() {
        return beanName;
    }

    public void setBeanName(String beanName) {
        this.beanName = beanName;
    }

    public String getListName() {
        return listName;
    }

    public void setListName(String listName) {
        this.listName = listName;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }
}

 

 

读取：

返回类型是Workbook，得到Workbook后再根据自己的需要读取其中的内容。

 

package com.apps.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReadUtils {

	public Workbook excelRead(InputStream in) {
		
		if (!in.markSupported()) {
			in = new PushbackInputStream(in, 8);
		}
		
        //check input file type
        //for excel 2003
		try {
			if (POIFSFileSystem.hasPOIFSHeader(in)) {
			    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in);
			    return hssfWorkbook;
			}
			//for excel 2007,2010
			else if (POIXMLDocument.hasOOXMLHeader(in)) {
				XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
				return xssfWorkbook;
			}
			//exception
			else{
				throw new Exception("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
			}
		} catch (IOException e) {
			throw new Exception("Open excel file error.");
		}finally{
            if (in != null) {
                try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
            }
		}
	}
	
	public Workbook excelRead(String path){
		InputStream in;
		try {
			in = new FileInputStream(path);
			return excelRead(in);
		} catch (FileNotFoundException e) {
			throw new Exception("The file not exists. Path: "+path);
		}
	}
	
	public Workbook excelRead(File file){
		InputStream in;
		try {
			in = new FileInputStream(file);
			return excelRead(in);
		} catch (FileNotFoundException e) {
			throw new Exception("The file not exists. Path: "+file.getAbsolutePath());
		}
	}
}