有时候我们在项目中需要录入大量的数据，Excel操作

有时候我们在项目中需要录入大量的数据，Excel操作

1：首先需要在项目中导入架包：

jxl.jar，

poi-3.6-20091214.jar
poi-3.8-beta5-20111217.jar
poi-examples-3.6-20091214.jar
poi-examples-3.8-beta5-20111217.jar
poi-excelant-3.8-beta5-20111217.jar
poi-ooxml-3.6-20091214.jar
poi-ooxml-3.8-beta5-20111217.jar
poi-ooxml-schemas-3.6-20091214.jar
poi-ooxml-schemas-3.8-beta5-20111217.jar
poi-scratchpad-3.6-20091214.jar
poi-scratchpad-3.8-beta5-20111217.jar

2：需要些一个操作Excel的工具实体类

package com.fxr.po;




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;//列

    /**
     * @return the sheetName
     */
    public String getSheetName() {
        return sheetName;
    }

    /**
     * @param sheetName the sheetName to set
     */
    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    /**
     * @return the data
     */
    public List<T> getData() {
        return data;
    }

    /**
     * @param data the data to set
     */
    public void setData(List<T> data) {
        this.data = data;
    }

    /**
     * @return the columns
     */
    public List<String> getColumns() {
        return columns;
    }

    /**
     * @param columns the columns to set
     */
    public void setColumns(List<String> columns) {
        this.columns = columns;
    }
    
    
}

在action中写具体的逻辑实现

package com.fxr.action;




import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;


import com.fxr.dao.SubjectDAO;
import com.fxr.dao.SubjectDAOImpl;
import com.fxr.po.ExcelWorkSheet;
import com.fxr.po.Subject;


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

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.apache.struts2.ServletActionContext;


public class ImportExcelAction extends ActionSupport{
    
     
    
 

    private File excelFile;//Excel文件
    private String excelFileFileName;//文件的名字
    private ExcelWorkSheet<Subject> excelWorkSheet;//页
    private Workbook createWorkBook(InputStream is) throws Exception{
        if(getExcelFileFileName().toLowerCase().endsWith("xls")){
            return new HSSFWorkbook(is);
        }
        if(getExcelFileFileName().toLowerCase().endsWith("xlsx"))
        {
            return new XSSFWorkbook(is);
        }
        return null;
    }
    
    
    public ImportExcelAction() {
        
    }
    
    @Override
    public String execute() throws Exception {
        HttpServletRequest request = ServletActionContext.getRequest();
         System.out.println(request);
         ServletActionContext.getRequest().setCharacterEncoding("utf-8");
       Workbook workbook=createWorkBook(new FileInputStream(excelFile));
       Sheet sheet=(Sheet) workbook.getSheetAt(0);
       excelWorkSheet=new ExcelWorkSheet<Subject>();
       excelWorkSheet.setSheetName(sheet.getSheetName());
       Row firstRow=sheet.getRow(0);
       Iterator<org.apache.poi.ss.usermodel.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 row=sheet.getRow(i);
            Subject subject=new Subject();

            subject.setSubjectTitle(row.getCell(0).getStringCellValue());
            subject.setSubjectOptionA(row.getCell(1).getStringCellValue());      
            subject.setSubjectOptionB(row.getCell(2).getStringCellValue());
            subject.setSubjectOptionC(row.getCell(3).getStringCellValue());   
            subject.setSubjectOptionD(row.getCell(4).getStringCellValue());
            subject.setSubjectAnswer(row.getCell(5).getStringCellValue()); 
            subject.setSubjectParse(row.getCell(6).getStringCellValue()); 
            
            
            
            
            
            
            
            

            SubjectDAO subjectDao = new SubjectDAOImpl();
            
            subjectDao.addSubject(subject);
            excelWorkSheet.getData().add(subject);
        }
        return SUCCESS;
    }

    /**
     * @return the excelFile
     */
    public File getExcelFile() {
        return excelFile;
    }

    /**
     * @param excelFile the excelFile to set
     */
    public void setExcelFile(File excelFile) {
        this.excelFile = excelFile;
    }

 
    public ExcelWorkSheet<Subject> getExcelWorkSheet() {
        return excelWorkSheet;
    }

    /**
     * @param excelWorkSheet the excelWorkSheet to set
     */
    public void setExcelWorkSheet(ExcelWorkSheet<Subject> excelWorkSheet) {
        this.excelWorkSheet = excelWorkSheet;
    }

    /**
     * @return the excelFileFileName
     */
    public String getExcelFileFileName() {
        return excelFileFileName;
    }

    /**
     * @param excelFileFileName the excelFileFileName to set
     */
    public void setExcelFileFileName(String excelFileFileName) {
        this.excelFileFileName = excelFileFileName;
    }
}

前台JSP

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib  prefix="s" uri="/struts-tags" %>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>试题信息的导入</title>
         <style type="text/css">
        #ge{ height:20px; background-color:#AAEC79;}
        </style>
    </head>
    <body id="ge"><center>
        <h1>试题信息的导入</h1>
        <s:form action="import" method="post" enctype="multipart/form-data">导入excel文件<s:file name="excelFile"/>
            <s:submit value="导入"></s:submit>
        </s:form>
    </center></body>
</html>

插入成功的JSP

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib  prefix="s" uri="/struts-tags" %>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>导入成功</title>
        <style type="text/css">
        #ge{ height:20px; background-color:#AAEC79;}
        </style>
    </head>
    <body id="ge"><center>
        <h1><s:property  value="excelWorkSheet.sheetName" />
            </h1>
        <p>
            <s:iterator value="excelWorkSheet.columns">
        <s:property/></s:iterator>
        </p>
        <table>
        <s:iterator var="stu"  value="excelWorkSheet.data" >
            <tr>
                <td> <s:property value="#stu.subjectTitle"/></td>
                <td><s:property value="#stu.subjectOptionA"/></td>
                <td><s:property value="#stu.subjectOptionB"/></td>
                <td><s:property value="#stu.subjectOptionC"/></td>
                <td><s:property value="#stu.subjectOptionD"/></td>
               <td> <s:property value="#stu.subjectAnswer"/></td>
               <td> <s:property value="#stu.subjectParse"/></td>
                
           </tr>
        </s:iterator>
        </table>
    </center></body>
</html>