Struts2上传和下载CSV与Excel文件

1.对CSV的操作使用的是开源的JavaCSV，使用的Jar包是javacsv.jar，在文章的最后有此架包的下载；对Excel的操作使用的是开源的Java Excel，使用的Jar包是jxl.jar，在文章最后也有此架包的下载。
2.CSV与Excel文件的上传和下载是基于Struts2实现的
首先看一下Web.xml的配置文件对Struts2的配置信息：

        <filter>
		<filter-name>struts2</filter-name>
		<filter-class>
org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter
                </filter-class>
	</filter>
	<filter-mapping>
		<filter-name>struts2</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

3.后台bean的配置文件信息

public class uploadBean {

	private String username;
	
	private String password;
	
	private String town;
	
	private int zip;

	public int getZip() {
		return zip;
	}

	public void setZip(int zip) {
		this.zip = zip;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getTown() {
		return town;
	}

	public void setTown(String town) {
		this.town = town;
	}
}

4.对应的后台Action的代码如下：

public class uploadAction extends ActionSupport {

	//获取上传的文件
	private File file;

	//获取上传文件的名字
	private String fileFileName;

	//获取上传文件的类型
	private String fileContentType;

	//获取上传文件的主题
	private String title;

	//统计上传CSV文件的列数
	private int columnCount = 0;
	
	//用于存放CSV文件的List
	private ArrayList<String[]> csvList;
	
	//用于存放Excel文件的List
	List<uploadBean> uploadList=new ArrayList<uploadBean>();
	
	//用于导出文件的流
	ByteArrayOutputStream baos = null;

	public int getColumnCount() {
		return columnCount;
	}

	public void setColumnCount(int columnCount) {
		this.columnCount = columnCount;
	}

	public ArrayList<String[]> getCsvList() {
		return csvList;
	}

	public void setCsvList(ArrayList<String[]> csvList) {
		this.csvList = csvList;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public File getFile() {
		return file;
	}

	public void setFile(File file) {
		this.file = file;
	}

	public String getFileFileName() {
		return fileFileName;
	}

	public void setFileFileName(String fileFileName) {
		this.fileFileName = fileFileName;
	}

	public String getFileContentType() {
		return fileContentType;
	}

	public void setFileContentType(String fileContentType) {
		this.fileContentType = fileContentType;
	}

	public List<uploadBean> getUploadList() {
		return uploadList;
	}

	public void setUploadList(List<uploadBean> uploadList) {
		this.uploadList = uploadList;
	}

	@Override
	public String execute() throws Exception {

		return SUCCESS;
	}

	/**
	 * Fist test read csv
	 * @return
	 */
	public String readCsv() {
		try {
			ArrayList<String[]> csvList = new ArrayList<String[]>();
			String csvFilePath = "D:\\foo.csv";
			CsvReader reader = new CsvReader(csvFilePath, ',', Charset
					.forName("SJIS"));
			reader.readHeaders();
			while (reader.readRecord()) {
				csvList.add(reader.getValues());
			}
			reader.close();
			for (int row = 0; row < csvList.size(); row++) {
				String cell = csvList.get(row)[0];
				System.out.println(cell);
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return SUCCESS;
	}

	/**
	 * CSVFile Upload and Analyse
	 * 
	 * @return
	 */
	public String readCSVFile() {

		InputStream in = null;
		csvList = new ArrayList<String[]>();
		try {
			in = new FileInputStream(file);
			CsvReader reader = new CsvReader(in, Charset.forName("SJIS"));
			try {
				//读取CSV文件的表头，如果CSV文件没有表头则可以注释 掉
				//reader.readHeaders();
				while (reader.readRecord()) {
					columnCount = reader.getColumnCount();
					csvList.add(reader.getValues());
				}
				reader.close();

				//遍历CSV文件中的信息
				for (int row = 0; row < csvList.size(); row++) {
					for (int i = 0; i < columnCount; i++) {
						String cell = csvList.get(row)[i];
						System.out.println(cell);
					}
				}
			} catch (IOException e1) {
				e1.printStackTrace();
				csvList=null;
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			csvList=null;
		}
		return SUCCESS;
	}

	/**
	 * CSVFile Download
	 * 
	 * @return
	 */
	public String writeCSVFile() {

		try {
			baos = new ByteArrayOutputStream();
			CsvWriter cw = new CsvWriter(baos, ',', Charset.forName("SJIS"));
			String[] contents = { "zhangsan", "123456", "1111", "Shanghai" };
			cw.writeRecord(contents, true);
			cw.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return SUCCESS;
	}

	/**
	 * get the InputStream
	 * @return
	 */
	public InputStream getInputStream() {
		InputStream is = null;
		//将OutputStream转为InputStream
		is = new ByteArrayInputStream(baos.toByteArray());
		return is;
	}

	/**
	 * Read Excel File and Analyse it
	 * @return
	 */
	public String readExcelFile(){
		
		try {
			//获取上传的Excel文件
			Workbook book=Workbook.getWorkbook(file);
			//获取Excel文件的Sheet的数量
			int bookNum=book.getNumberOfSheets();
			for(int j=0;j<bookNum;j++){
			Sheet sheet=book.getSheet(j);
			for(int i=0;i<sheet.getRows();i++){
				uploadBean uploadB=new uploadBean();
				//sheet.getCell(A,B)中的A是第A列，B是第B行
				uploadB.setUsername(sheet.getCell(0, i).getContents());
				uploadB.setPassword(sheet.getCell(1, i).getContents());
				uploadB.setZip(Integer.parseInt(sheet.getCell(2, i).getContents()));
				uploadB.setTown(sheet.getCell(3, i).getContents());
				uploadList.add(uploadB);
			}
			}
			book.close();
			//验证上传文件信息是否正确
			for(int i=0;i<uploadList.size();i++){
				uploadBean upb=(uploadBean)uploadList.get(i);
				System.out.println("username="+upb.getUsername()+",password="+upb.getPassword()+",Zip="+upb.getZip()+",Town="+upb.getTown());
			}
		} catch (BiffException e) {
			e.printStackTrace();
			uploadList=null;
		} catch (IOException e) {
			e.printStackTrace();
			uploadList=null;
		}
		return SUCCESS;
	}
	
	

	/**
	 * Write Excel File
	 * @return
	 */
	public String WriteExcelFile(){
		
		uploadBean upb=new uploadBean();
		upb.setUsername("wanglei");
		upb.setPassword("12345");
		upb.setZip(1111);
		upb.setTown("Shanghai");
		baos = new ByteArrayOutputStream();
		try {
			//创建一个Excel文件
			WritableWorkbook createBook=Workbook.createWorkbook(baos);
			//创建一个sheet
			WritableSheet ws=createBook.createSheet("first", 0);
			
			try {
				Label lb1=new Label(0,0,"wanglei");
				ws.addCell(lb1);
				Label lb2=new Label(1,0,"123456");
				ws.addCell(lb2);
				Label lb3=new Label(2,0,"11111");
				ws.addCell(lb3);
				Label lb4=new Label(3,0,"Shanghai");
				ws.addCell(lb4);
				
				Label lb5=new Label(0,1,"zhangsan");
				ws.addCell(lb5);
				Label lb6=new Label(1,1,"123456");
				ws.addCell(lb6);
				Label lb7=new Label(2,1,"22222");
				ws.addCell(lb7);
				Label lb8=new Label(3,1,"Beijing");
				ws.addCell(lb8);
				//将数据写到Excel文件中
				createBook.write();
				createBook.close();
			} catch (RowsExceededException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
			}
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return SUCCESS;
	}

5.前台的Jsp文件如下：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'index.jsp' starting page</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>
  
  <body> 
      <s:form action="upload.action" method="post" enctype="multipart/form-data" theme="simple">
        <s:a href="downloadExcel.action">submit WriteExcelFile</s:a><br>
        <s:a href="downloadCSV.action">submit writeCSVFile</s:a><br>
        please input the title:<input type="text" name="title" /> <br>
        please choose the file:<s:file name="file" /><br>
        <s:submit method="readExcelFile" value="submit readExcelFile"  /><br>
        <s:submit method="readCSVFile" value="submit readCSVFile" /> <br>
        
      </s:form>
  </body>
</html>

操作成功后的跳转页面：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'success.jsp' starting page</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
  
  <body>
    Success. <br>
    <s:iterator value="csvList" status="csvL" id="csvListValue">
        <s:property /><br>
    </s:iterator><br>
    <s:iterator value="uploadList" status="uplist" id="upId">
        index:<s:property value="#uplist.index"/><br>
        username:<s:property value="username"/><br>
        password:<s:property value="password"/><br>
        zip:<s:property value="zip"/><br>
        town:<s:property value="town"/><br>
    </s:iterator><br>
    <s:iterator value="uploadList" status="uplist" id="upId">
        username:<s:property value="#upId.username"/><br>
        password:<s:property value="#upId.password"/><br>
        zip:<s:property value="#upId.zip"/><br>
        town:<s:property value="#upId.town"/><br>
    </s:iterator><br>
  </body>
</html>

6.Struts.xml的配置信息：

  <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts PUBLIC  
     "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN"  
    "http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
	<package name="default" extends="struts-default" namespace="/">
	    <action name="upload"  class="com.wl.upload.action.uploadAction">
	        <param name="savePath">/upload</param>
	        <result name="success">/success.jsp</result>
	        <result name="login">/Error.jsp</result>
	    </action>
	     <action name="downloadCSV" method="writeCSVFile" class="com.wl.upload.action.uploadAction">
	        <param name="savePath">/upload</param>
	        <result name="success" type="stream">
	           <param name="contentType">text/plain</param>
	           <param name="inputname">inputStream</param>
	           <param name="contentDisposition">attachment;filename="export.csv"</param>
	           <param name="bufferSize">4096</param>
	        </result>
	        <result name="login">/Error.jsp</result>
	    </action>
	    <action name="downloadExcel" method="WriteExcelFile" class="com.wl.upload.action.uploadAction">
	        <param name="savePath">/upload</param>
	        <result name="success" type="stream">
	           <param name="contentType">text/plain</param>
	           <param name="inputname">inputStream</param>
	           <param name="contentDisposition">attachment;filename="export.xls"</param>
	           <param name="bufferSize">4096</param>
	        </result>
	        <result name="login">/Error.jsp</result>
	    </action>
	</package>
</struts>

参考资料：
http://www.cnitblog.com/rd416/archive/2010/07/08/47248.html
http://solodu.iteye.com/blog/483588