Jsp+struts实现把excel表格数据导入数据库

   我们在用jsp+strust开发系统的时候会遇到如何将Excel表格的数据批量导入到数据库的情况。网上有很多版本实现将excel数据导入到数据库中，但方法都不怎样，不是将excel文件另存为csv文件再利用命令实现导入到数据库中就是利用PHP来实现，笔者自己利用了structs和hibernate技术写了一些方法，利用POI实现了将excel数据导入到数据库中，今天就跟大家分享一下。

第一：添加POI jar包到项目的lib目录下

请到网站http://www.apache.org/dyn/closer.cgi/poi/右击超链接2.5.1.zip下载压缩包poi-bin-2.5.1-final-20040804.zip（或下载最新的这种压缩包），解压缩后得到如图2.1所示的目录结构。我们主要用到poi-2.5.1-final-20040804.jar这个库文件。请把poi-2.5.1-final-20040804.jar这个文件的路径添加到系统环境变量classpath中，否则无法编译下面的示例程序。

第二：首先搭建ssh框架（这儿不做介绍）；

第三： 新创建一个jsp页面如（import.jsp）,在页面上添加如下代码：

其中action可以自己编写，

<html:formaction="importExcel.do?method=importExcelAction" enctype="MULTIPART/FORM-DATA"  method="post" >

      <TABLE cellSpacing=1 cellPadding=1 width="98%" border=0>

        <TBODY>

        <TR>

          <TD height=30>导入数据：</TD>

          <TD style="TEXT-ALIGN: left">

          <html:file property="excpath"  size="30" >

          </html:file>

          </TD>

        </TR>

       <TR>

          <TD height=60>&nbsp;</TD>

          <TD align="left">

          <INPUT class="coolbg np" type=submit value=" 保存信息" name=Submit>

          </TD>

       </TR>

     </TBODY>

    </TABLE>

</html:form>

第四：struts-config.xml编写

 <action attribute="ImportExcelForm"

        input="/importExcel.jsp"

        name="ImportExcelForm"

        parameter="method"

        path="/importExcel"

        scope="request"

        type="com.struts.action.importExcelAction"  validate="true">

       <forward name="toImportExcel" path="/importExcelsucsess.jsp" />

     </action>

第五：java代码如下

在importExcelAction中添加

package com.struts.action;

import java.io.IOException;

import javax.servlet.ServletException;

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.action.DynaActionForm;

import org.apache.struts.upload.FormFile;

import org.hibernate.HibernateException;

import com.base.ExcelWorkSheet;

import com.struts.form.ImportExcelForm;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.util.ArrayList;

import java.util.List;

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 java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

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;

public class importExcelAction extends BaseAction {

public ActionForward importExcelAction(ActionMapping mapping, ActionForm form,

                            HttpServletRequest request, HttpServletResponse response) throws Exception {

                                   ImportDAO dbo=new ImportDAO();

                                   ImportExcelForm editorForm = (ImportExcelForm) form;

                                  String dd=""+d;

                                   /** 存放文件的文件夹 */

                                   String dir=null;

                                     /** 文件名 */

                                   String newname=null;

                                    String pname = null;

                                      String picturedirs=null;

                                    FormFile picture1 = editorForm.getExcpath();

                                    dir = request.getRealPath("/excel/Inportexl");

                                          picturedirs="/excel/Inportexl"+"/";

                                           File fdir=new File(dir);

                                          if(!fdir.exists())fdir.mkdirs();

                                          pname = picture1.getFileName();

                                          if(picture1!=null&&picture1.getFileName().length()>0){

                                          pname = picture1.getFileName();

                                          String newname1=pname.substring(pname.length()-3,pname.length());    

                                          newname="InporteExcelks"+"."+newname1;

                                          }

                                           /** 输入流对象（用于读取上传的文件数据） */

                                       InputStream in = null;

                                      /** 输出流对象(用于将读取到的文件数据输出到服务端的另一个文件中) */

                                    OutputStream out = null;

                                   try{

                                 if (picture1==null||pname.length()<1){

                                       }else{    

                                                 in = picture1.getInputStream();

                                                 out = new FileOutputStream(dir+newname);

                                                 int readed = 0;

                                                 byte[] buffer= new byte[1024];

                                                 while ((readed=in.read(buffer,0,1024))!=-1){

                                                          out.write(buffer,0,readed);

                                                 }

                                          }

                                  String filePath = dir+newname;

                                  HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));

                                     // 在Excel文档中，第一张工作表的缺省索引是0

                                      // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);

                                      HSSFSheet sheet = wookbook.getSheet("Sheet1");

                                       //获取到Excel文件中的所有行数

                                       int rows = sheet.getPhysicalNumberOfRows();

                                       //遍历行

                                       for (int i = 1; i < rows; i++) {

                                             // 读取左上端单元格

                                             HSSFRow row = sheet.getRow(i);

                                              // 行不为空

                                             if (row != null) {

                                                    //获取到Excel文件中的所有的列

                                                   int cells = row.getPhysicalNumberOfCells();

                                                   String value = "";   

                                                   //遍历列

                                                    for (int j = 0; j < cells; j++) {

                                                         //获取到列的值

                                                         HSSFCell cell = row.getCell((short) j);

                                                          if (cell != null) {

                                                                switch (cell.getCellType()) {

                                                                     case HSSFCell.CELL_TYPE_FORMULA:

                                                                     break;

                                                                      case HSSFCell.CELL_TYPE_NUMERIC:

                                                                           value += cell.getNumericCellValue() + ",";

                                                                      break; 

                                                                     case HSSFCell.CELL_TYPE_STRING:

                                                                           value += cell.getStringCellValue() + ",";

                                                                      break;

                                                                      default:

                                                                            value += "0";

                                                                      break;

                                                         }

                                                   }     

                                              }

                                              // 将数据插入到mysql数据库中

                                              String[] val = value.split(",");

                                            Zsbz student = new Zsbz();

                                             student.setZsbzid(1);

                                          System.out.print("111="+val[0]);

                                          student.setZsbzpay(val[1]);

                                          student.setPaixuid((int)(Double.parseDouble(val[2])));

                                   System.out.print("113="+(int)(Double.parseDouble(val[2])));

                                          //System.out.print("113="+Integer.parseInt(val[2]));

                                       dbo.addzsbz(student);

     }

      }

                            }catch(Exception ex){

                                          ex.printStackTrace();

                                   }

                                   return mapping.findForward("toImportExcel");  

                                   }

}  

 

 

免责声明：本站所有资讯内容搜集整理于互联网或者网友提供，并不代表本网赞同其观点，仅供学习与交流使用，如果不小心侵犯到你的权益，如果你对文章内容、图片和版权等问题存在异议，请及时联系我们删除该信息。