首页 > 代码库 > 有时候我们在项目中需要录入大量的数据,Excel操作

有时候我们在项目中需要录入大量的数据,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=http://www.mamicode.com/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 =http://www.mamicode.com/ 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="http://www.mamicode.com/导入"></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="http://www.mamicode.com/excelWorkSheet.sheetName" />            </h1>        <p>            <s:iterator value="http://www.mamicode.com/excelWorkSheet.columns">        <s:property/></s:iterator>        </p>        <table>        <s:iterator var="stu"  value="http://www.mamicode.com/excelWorkSheet.data" >            <tr>                <td> <s:property value="http://www.mamicode.com/#stu.subjectTitle"/></td>                <td><s:property value="http://www.mamicode.com/#stu.subjectOptionA"/></td>                <td><s:property value="http://www.mamicode.com/#stu.subjectOptionB"/></td>                <td><s:property value="http://www.mamicode.com/#stu.subjectOptionC"/></td>                <td><s:property value="http://www.mamicode.com/#stu.subjectOptionD"/></td>               <td> <s:property value="http://www.mamicode.com/#stu.subjectAnswer"/></td>               <td> <s:property value="http://www.mamicode.com/#stu.subjectParse"/></td>                           </tr>        </s:iterator>        </table>    </center></body></html>

 

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