Struts POIによるExcelのインポートエクスポート

Userinfo.java

public class Userinfo {
	private Integer id;   
    private String name;   
    private String pass;   
    private String lastname;   
    private String addres;   
    private String remark;
//...
}

 ExcelWorkSheet.java

package com.excel;

import java.util.ArrayList;
import java.util.List;

public class ExcelWorkSheet<T> {
	private String sheetName;   
    private List<T> data = new ArrayList<T>();  //    
    private List<String> columns; //    
    public String getSheetName() {   
        return sheetName;   
    }   
    public void setSheetName(String sheetName) {   
        this.sheetName = sheetName;   
    }   
    public List<T> getData() {   
        return data;   
    }   
    public void setData(List<T> data) {   
        this.data = data;   
    }   
    public List<String> getColumns() {   
        return columns;   
    }   
    public void setColumns(List<String> columns) {   
        this.columns = columns;   
    }          
}

 UserAction.java

package com.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.opensymphony.xwork2.ActionSupport;

public class UserAction extends ActionSupport{
//	private IUserBiz userBiz;   
//    public void setUserBiz(IUserBiz userBiz) {   
//        this.userBiz = userBiz;   
//    }     
    private File excelFile; //    
    private String excelFileFileName; //    
    // Excel    
    private ExcelWorkSheet<Userinfo> excelWorkSheet;   
    public File getExcelFile() {   
        return excelFile;   
    }   
    public void setExcelFile(File excelFile) {   
        this.excelFile = excelFile;   
    }   
    public String getExcelFileFileName() {   
        return excelFileFileName;   
    }   
    public void setExcelFileFileName(String excelFileFileName) {   
        this.excelFileFileName = excelFileFileName;   
    }   
   
    public ExcelWorkSheet<Userinfo> getExcelWorkSheet() {   
        return excelWorkSheet;   
    }   
    public void setExcelWorkSheet(ExcelWorkSheet<Userinfo> excelWorkSheet) {   
        this.excelWorkSheet = excelWorkSheet;   
    }   
    //    
    public Workbook createWorkBook(InputStream is) throws IOException{   
        if(excelFileFileName.toLowerCase().endsWith("xls")){   
            return new HSSFWorkbook(is);   
        }   
        if(excelFileFileName.toLowerCase().endsWith("xlsx")){   
            return new XSSFWorkbook(is);   
        }   
        return null;   
    }    
    public String execute() throws Exception{   
        Workbook book = createWorkBook(new FileInputStream(excelFile));   
        //book.getNumberOfSheets();   Excel sheet   
        Sheet sheet =  book.getSheetAt(0);     
        excelWorkSheet = new ExcelWorkSheet<Userinfo>();   
        //    
        Row firstRow = sheet.getRow(0);   
        Iterator<Cell> iterator = firstRow.iterator();   
           
        //    
        List<String> cellNames = new ArrayList<String>();   
        while (iterator.hasNext()) {   
            cellNames.add(iterator.next().getStringCellValue());   
        }   
        excelWorkSheet.setColumns(cellNames);   
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {   
            Row ros = sheet.getRow(i);   
            Userinfo user = new Userinfo();   
            user.setId((int)ros.getCell(0).getNumericCellValue());   
            user.setName(ros.getCell(1).getStringCellValue());   
            user.setPass(ros.getCell(2).getStringCellValue());   
            user.setLastname(ros.getCell(3).getStringCellValue());   
            user.setAddres(ros.getCell(4).getStringCellValue());   
            user.setRemark(ros.getCell(5).getStringCellValue());   
            System.out.println(user);
            excelWorkSheet.getData().add(user);   
        }   
        for (int i = 0; i < excelWorkSheet.getData().size(); i++) {   
            Userinfo info = excelWorkSheet.getData().get(i);   
            System.out.println(info.getLastname());   
        }   
        return SUCCESS;   
    }       
}

 OutAction.java

package com.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.interceptor.ServletResponseAware;

import com.opensymphony.xwork2.ActionSupport;

public class OutAction extends ActionSupport implements ServletResponseAware{
	private static final long serialVersionUID = 1L;     
//    private IUserBiz biz ;   
//    public void setBiz(IUserBiz biz) {   
//        this.biz = biz;   
//    }   
    private String format = "xls";   
    private HttpServletResponse response;   
    private String fileName = " .xls";      
    public String execute(){      
        //@SuppressWarnings("all")   
        //List la = biz.getAllSql();       
        setResponseHeader();   
        try {   
            exportExcel(response.getOutputStream());   
            response.getOutputStream().flush();   
            response.getOutputStream().close();   
        } catch (IOException e) {   
            e.printStackTrace();   
        }   
        return null;   
    }     
    /**  */   
    public void setResponseHeader(){   
        try{   
//          response.setContentType("application/msexcel;charset=UTF-8");  //    
            response.setContentType("application/octet-stream;charset=UTF-8");   
            response.setHeader("Content-Disposition", "attachment;filename="   
                    +java.net.URLEncoder.encode(this.fileName, "UTF-8"));   
            //    
            response.addHeader("Pargam", "no-cache");   
            response.addHeader("Cache-Control", "no-cache");   
        }catch(Exception ex){   
            ex.printStackTrace();   
        }   
    }     
    /** */   
    private void exportExcel(OutputStream os) throws IOException{   
        Workbook book = new HSSFWorkbook();   
        Sheet sheet = book.createSheet(" ");   
        Row row = sheet.createRow(0);   
        row.createCell(0).setCellValue(" ");   
        row.createCell(1).setCellValue(" ");   
        row.createCell(2).setCellValue(" ");   
        row.createCell(3).setCellValue(" ");   
        row.createCell(4).setCellValue(" ");   
        row.createCell(5).setCellValue(" ");   
        CellStyle sty = book.createCellStyle();   
        List<Userinfo> list = new ArrayList<Userinfo>();// biz.getAll(); 
        list.add(new Userinfo(1, "umgsai1", "123", "s", " ", "1000"));
        list.add(new Userinfo(2, "umgsai2", "123", "s", " ", "1000"));
        list.add(new Userinfo(3, "umgsai3", "123", "s", " ", "1000"));
        list.add(new Userinfo(4, "umgsai4", "123", "s", " ", "1000j"));
        for (int i = 0; i < list.size(); i++) {   
            Userinfo user = list.get(i);   
            row = sheet.createRow(i + 1);   
            Cell newCell = row.createCell(0);
            newCell.setCellType(Cell.CELL_TYPE_STRING);
            newCell.setCellValue(user.getId());  
            row.createCell(1).setCellValue(user.getName());
            
            newCell = row.createCell(2);
            newCell.setCellType(Cell.CELL_TYPE_STRING);
            newCell.setCellValue(user.getPass());  
            
            row.createCell(3).setCellValue(user.getLastname());   
            row.createCell(4).setCellValue(user.getAddres()); 
            
            newCell = row.createCell(5);
            newCell.setCellType(Cell.CELL_TYPE_STRING);
            newCell.setCellValue(user.getRemark()); 
        }   
        try{   
            book.write(os);   
        }catch(Exception ex){   
            ex.printStackTrace();   
        }finally{
        	book.close();
        }
    }    
    public String getFormat() {   
        return format;   
    }   
    public void setFormat(String format) {   
        this.format = format;   
        this.fileName = " .xls";   
    }   
    public String getFileName() {   
        return fileName;   
    }   
    public void setFileName(String fileName) {   
        this.fileName = fileName;   
    }   
    /** set */   
    public void setServletResponse(HttpServletResponse response) {   
        this.response = response;   
    }   
}

 struts.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
	"-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
	"http://struts.apache.org/dtds/struts-2.3.dtd">
<struts>
	<constant name="struts.enable.DynamicMethodInvocation" value="false" />
	<constant name="struts.devMode" value="true" />

	<package name="default" namespace="/" extends="struts-default">
		<action name="userInfo" class="com.excel.UserAction">
			<result>InputExcel.jsp</result>
		</action>
		<action name="outPut" class="com.excel.OutAction">
			<result>InputExcel.jsp</result>
		</action>
	</package>
</struts>

 InputExcel.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@taglib prefix="s" uri="/struts-tags"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1><s:property value="excelWorkSheet.sheetName" /> </h1>   
  <p>   
    <s:iterator value="excelWorkSheet.columns">   
        <s:property />  ||    
    </s:iterator>   
  </p>   
  <s:iterator var="user" value="excelWorkSheet.data">   
    <p>   
        <s:property value="#user.id"/>      
        <s:property value="#user.name"/>      
        <s:property value="#user.pass"/>      
        <s:property value="#user.lastname"/>      
        <s:property value="#user.addres"/>       
        <s:property value="#user.remark"/>       
    </p>   
  </s:iterator>  
</body>
</html>

 OutExcel.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@taglib prefix="s" uri="/struts-tags"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
	<s:form action="userInfo" method="post" enctype="multipart/form-data">    
     Excel ：<s:file name="excelFile"></s:file>
		<br />
		<s:submit value=" "></s:submit>
	</s:form>
</body>
</html>

使用するjarパッケージ
asm-3.3.jar
asm-commons-3.3.jar
asm-tree-3.3.jar
commons-fileupload-1.3.1.jar
commons-io-2.2.jar
commons-lang3-3.1.jar
commons-logging-1.1.3.jar
freemarker-2.3.19.jar
javassist-3.11.0.GA.jar
log4j-1.2.17.jar
ognl-3.0.6.jar
poi-3.11-beta2-20140822.jar
poi-examples-3.11-beta2-20140822.jar
poi-excelant-3.11-beta2-20140822.jar
poi-ooxml-3.11-beta2-20140822.jar
poi-ooxml-schemas-3.11-beta2-20140822.jar
poi-scratchpad-3.11-beta2-20140822.jar
struts2-core-2.3.16.3.jar
xwork-core-2.3.16.3.jar