首页 > 代码库 > 有时候我们在项目中需要录入大量的数据,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操作
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。