利用jxl实现excel导入

来源：互联网发布：怎么开淘宝店手机淘宝编辑：程序博客网时间：2024/05/07 09:03

【前言】

不知网上是不是有这样的文章，在此仅想把自己的内容记下来

【jxl和poi】

参考文章：

http://zorro.blog.51cto.com/2139862/658167

jxl貌似比poi小巧好用点，

没用过poi，只是瞎猜。

【jxl】

官网：http://www.andykhan.com/jexcelapi/index.html

下载：

进去官网之后找到Download JExcelApi进行下载

【struts2的一个例子】

需要的jar：

jxl.jar

commons-fileupload-1.1.1.jar

commons-io-1.1.jar

jxl.jar在下载的压缩包中可以找到

后两个可以在struts2中的lib中找到，版本号可能不同

jsp：

<form action="demoAction!importExcel.action" enctype="multipart/form-data" method="post"><table><tr><td>Excel文件：</td><td><input type="file" name="theFile" /></td><td><input type="submit" value="导入" /></td></tr></table><hr><p style="color:red;">请先下载任务信息的<a href="<%=basePath%>/jsp/task/task.xls">Excel模版</a>，填写完毕之后再导入！</p></form>

action：

        public void importExcel(){Json json = new Json();InputStream is;try {is = new FileInputStream(theFile);String str = taskService.importExcel(is,getSessionInfo().getSysUserId());String sucNum = str.split("-")[0];String totalNum = str.split("-")[1];if(str.equals("0-0")){json.setMsg("Excel表中无数据");}else if(sucNum.equals(totalNum)){json.setSuccess(true);json.setMsg("学员信息导入成功！");}else{json.setMsg("导入失败，共有" + totalNum + "条数据,导入成功" + sucNum + "条数据！");}} catch (Exception e) {json.setMsg("导入失败");}writeJson(json);}private File theFile;private TaskServiceI taskService;public File getTheFile() {return theFile;}public void setTheFile(File theFile) {this.theFile = theFile;}public TaskServiceI getTaskService() {return taskService;}@Autowiredpublic void setTaskService(TaskServiceI taskService) {this.taskService = taskService;}

service：

public String importExcel(InputStream is,Long id){int sucNum = 0;int beginRow = 1;int totalRow = 0;try {//workbookWorkbook book = Workbook.getWorkbook(is);//sheetSheet sheet = book.getSheet(0);//rowCell[] cells;totalRow = sheet.getRows();//taskTaskEntity task;for(int i=beginRow; i<totalRow; i++){cells = sheet.getRow(i);task = new TaskEntity();task.setTaskTitle(cells[0].getContents());task.setTaskDesc(cells[1].getContents());task.setStandardTime(Long.parseLong(cells[2].getContents()));task.setTaskStatus(TaskConstants.TASK_STATUS_STANDARD);task.setTaskFileName(cells[3].getContents());task.setTaskFileFloder(cells[4].getContents());task.setCreateUserId(id);task.setCreatedt(new Date());task.setTuser(null);task.setTypeObids(cells[5].getContents());task.setExerciseAnswer(cells[6].getContents());taskEntityDao.save(task);sucNum++;}} catch (Exception e) {e.printStackTrace();} finally {if(is != null){try {is.close();} catch (IOException e1) {e1.printStackTrace();}}}return sucNum + "-" + (totalRow-1);}

【struts的例子】

仅仅取得文件流的方式不同

jsp：

<%@ page language="java" import="java.util.*"%><%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><%@ taglib uri="/WEB-INF/tld/struts-html.tld" prefix="html" %><%@ taglib uri="/WEB-INF/tld/struts-logic.tld" prefix="logic" %><%@ page isELIgnored="false" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><title>demo-jxl</title></head><body><html:form action="/jxl.do?m=im" method="post" enctype="multipart/form-data"><html:file property="file"></html:file><html:submit value="导入"></html:submit></html:form></body></html>

form：

package com.uikoo9.action.form;import org.apache.struts.action.ActionForm;import org.apache.struts.upload.FormFile;@SuppressWarnings("serial")public class JXLForm extends ActionForm {private FormFile file;public FormFile getFile() {return file;}public void setFile(FormFile file) {this.file = file;}}

action：

package com.uikoo9.action;import java.io.InputStream;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.struts.action.ActionForm;import org.apache.struts.action.ActionForward;import org.apache.struts.action.ActionMapping;import org.apache.struts.actions.DispatchAction;import org.apache.struts.upload.FormFile;import com.uikoo9.action.form.JXLForm;import com.uikoo9.service.JXLService;public class JXLAction extends DispatchAction {@Overridepublic ActionForward execute(ActionMapping arg0, ActionForm arg1,HttpServletRequest arg2, HttpServletResponse arg3) throws Exception {// TODO Auto-generated method stubreturn super.execute(arg0, arg1, arg2, arg3);}public ActionForward im(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response) throws Exception {//得到excel文件JXLForm jxlForm = (JXLForm)form;FormFile formFile = jxlForm.getFile();InputStream in = formFile.getInputStream();JXLService.importExcel(in);return mapping.findForward("index");}}

service：

package com.uikoo9.service;import java.io.InputStream;import com.uikoo9.dao.DemoDAO;import com.uikoo9.model.Demo;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;public class JXLService {public static void importExcel(InputStream in){try {//得到工作簿Workbook book = Workbook.getWorkbook(in);//得到sheetSheet sheet = book.getSheet(0);//开始行，0为首行int beginRow = 1;//得到总行数int totalRows = sheet.getRows();//得到某一行Cell[] cells;//modelDemo demo;for(int i=beginRow; i<totalRows; i++){cells = sheet.getRow(i);demo = new Demo();demo.setCode(cells[0].getContents());demo.setMajor(cells[1].getContents());demo.setLevel(cells[2].getContents());demo.setName(cells[3].getContents());demo.setId(cells[4].getContents());demo.setNo(cells[5].getContents());demo.setMoney(Integer.valueOf(cells[6].getContents()));DemoDAO.printDemo(demo);}} catch (Exception e) {e.printStackTrace();}}}