实现Excel文件的到入

Spring实现excel文件的导入

在这里把我最近的做的一些东西share一下，以便帮助和我一样的朋友，希望他们能取的进步。为了更简单的开始，大家可以参考“利用java操作excel文件”一文，这个小模块的主要功能是实现对Excel文件的解析并把需要的数据插入到数据库里

，当然了，在上传文件的时候要做一些判断，下面的excel文件

（注：在解析excel文件的时候我们都要针对具体的文件来解析，看了利用java操作excel文件就会知道）好了，下面来看具体的代码

InsideDataUploadAction.java(这段代码的主要功能是将取到的数据插入数据库里，它里面调用了解析excel文件的程序BudSortUploadBean.java)

import java.io.*;

import java.util.List;

 

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.upload.FormFile;

import org.springframework.web.context.WebApplicationContext;

 

import com.atools.intendance.dao.*;

import com.atools.intendance.form.*;

import com.atools.intendance.business.bean.*;

/**

 * @author hh

 *

 */

public class InsideDataUploadAction extends BaseAction {

   String pd="";

   public ActionForward execute(ActionMapping mapping,ActionForm form,HttpServletRequest request,HttpServletResponse response)

   throws Exception

   {

      ActionForward forward=null;

      WebApplicationContext factory=this.GetWAC();

      BUDGETDao budgetDao=(BUDGETDao)factory.getBean("budgetDao");

      SORTDao sortDao=(SORTDao)factory.getBean("sortDao");

      BUDSORTDao budsortDao=(BUDSORTDao)factory.getBean("budsortDao");

      INSIDEDATADao insideData=(INSIDEDATADao)factory.getBean("insideDataDao");

      System.out.println("***************程序开始执行");

//    取得文件集合

      FileUploadForm theForm = (FileUploadForm) form;   

//    获得导入文件的信息

      FormFile fileone=theForm.getFile1();

      File filetwo=new File("temp.xls");

//    得到上传文件的名称

      String filename=fileone.getFileName();

      filename=filename.substring(filename.lastIndexOf(".")+1).toUpperCase();

      InputStream in=fileone.getInputStream();

          FileOutputStream fos = new FileOutputStream(filetwo);

          int byteSum = 0;

          int byteRead = 0;

          byte[] buffer = new byte[1444];

          while ((byteRead = in.read(buffer)) != -1) {

                 byteSum += byteRead;

                 fos.write(buffer);

          }           

      try{

//           budform.init(request.getSession(),"0");

             BudSortUploadBean bsupload=new BudSortUploadBean();

             filename=bsupload.validate_File(filename,pd);

           int k=0;

           List list;

             if(filename.equals("true"))

             {

//                  得到表格里的年度和月份

                    String AccYear=bsupload.getYear(filetwo);

                    String Month=bsupload.getMonth(filetwo);

                    BUDGET budget=new BUDGET();

                    SORT sort=new SORT();

                    BUDSORT bud_sort=new BUDSORT();

                    INSIDEDATA insidedata=new INSIDEDATA();

                    

                    list=bsupload.getCodelist(filetwo,k);

                    for(int i=0;list.size()>i;i++){

                           BudSortDetail budsort=new BudSortDetail();

                           budsort=(BudSortDetail)list.get(i);

 

                           String budcode=budsort.getBUDCODE();

                           String sortcode=budsort.getSORTCODE();

                        String jine=budsort.getJINE();

                        System.out.println("the year is "+AccYear+" the budcode is "+budcode+" the sortcode id "+sortcode);

                           if(budcode.equals("")||sortcode.equals(""))

                           {

                                  forward=mapping.findForward("error");

                           }else{

                                  /*  以下代码的主要设计思想是调用getYear()方法得到年度值，然后调用budgetDao.FindByCondition_budget()

                                       方法返回一个From，然后从form里得到budno，同样得到sortno，根据这两个值确定一个bsno，得到以后将其插入到insideData

                               **/

//                   得到budno

                                         budget.setACCYEAR(AccYear);

                                         budget.setBUDCODE(budcode);

                                         BudGetForm budgetform=budgetDao.FindByCondition_budget(budget);

//                                    得到sortno

                                         sort.setACCYEAR(AccYear);

                                         sort.setSORTCODE(sortcode);

                                         SortForm sortform=sortDao.FindByCondition_Sort(sort);

//                                    根据budno和sortno得到bsno

                                         bud_sort.setBUDNO(budgetform.getBUDNO());

                                         System.out.println("预算口径的预算编号为 "+budgetform.getBUDNO());

                                         bud_sort.setSORTNO(sortform.getSORTNO());

                                         System.out.println("分类口径的分类编号为 "+sortform.getSORTNO());

                                         BudSortForm budsotrform=budsortDao.FindByCondition_BudSort(bud_sort);  

//                                    对预算口径和内部口径数据导入表的记录进行比较，如果在内部口径数据表里有记录，征求用户介意，   

                                         insidedata.setBSNO(budsotrform.getBSNO());

                                         String bsno=budsotrform.getBSNO();

                                         

                                         insideData.Delete_InsideData(bsno);

                                         insidedata.setBSNO(budsotrform.getBSNO());

                                         insidedata.setACCYEAR(AccYear);

                                         insidedata.setACCMONTH(Month);

                                         insidedata.setJINE(jine);

                                         insideData.Insert_InsideData(insidedata);

                                         System.out.println("进入。。。。。，插入数据完成");

                                         forward=mapping.findForward("success");  

                           }         

                    }

             }else{

                    forward=mapping.findForward("false");

             }

                 in.close();

                 fos.close();

      }catch(Exception ex){

             this.dealException(mapping,request,ex);

      }

      return forward;

   }

 

}

 

BudSortUploadBean.java

 

import jxl.Workbook;

import jxl.Sheet;

import jxl.Cell;

import java.util.*;

import java.io.*;

import jxl.read.biff.BiffException;

import jxl.DateCell;

import java.text.*;

import com.atools.intendance.dao.*;

//import com.atools.intendance.dao.*;

public class BudSortUploadBean extends BaseExcelBean{

   protected Workbook workbook = null;

// 从Excel里得到budcode的值

   public List getCodelist(File file,int type)throws Exception

   {

          BudSortDetail detail=new BudSortDetail();

          try {

              ArrayList ret = new ArrayList();

              Workbook wb = Workbook.getWorkbook(file);

              Sheet sheet = wb.getSheet(0);

              int rows=sheet.getRows();

              for(int i=3;i<rows;i++)

              {

                   detail=(BudSortDetail)getIndexList(sheet, i);

                   if(detail!=null)

                   ret.add(detail);

              }

              return ret;

          } catch (Exception e) {

              throw e;

          }

   }

//

    private BudSortDetail getIndexList(Sheet sheet,int indexRow){

             BudSortDetail detail=new BudSortDetail();

          Cell cell=sheet.getCell(0,indexRow);

           if(!cell.getContents().equals(""))

            {

              detail.setBUDCODE(cell.getContents());   

            }else

              detail=null;

            Cell cell2=sheet.getCell(2,indexRow);

            if(!cell2.getContents().equals(""))

            {

              detail.setSORTCODE(cell2.getContents());

            }else

              detail=null;

            Cell cell3=sheet.getCell(4,indexRow);

            if(!cell3.getContents().equals(""))

            {

               detail.setJINE(cell3.getContents());

            }else

              detail=null;

            return detail;

         

   }

//   得到年度

    public String getYear(File file) throws BiffException, IOException{

           String year="";

           workbook=Workbook.getWorkbook(file);

         Sheet sheet=workbook.getSheet(0);

         int column=sheet.getColumns();

         System.out.println("进入解析文件，正在读取year。。。。。。。。。。。。。。。");

         for(int i=0;i<column;i++){

             Cell cell=sheet.getCell(i,1);

             if(cell.getContents().equals("日期"))

             {

               Cell cellone=sheet.getCell(i+1,1);

               DateCell date=(DateCell)cellone;

               SimpleDateFormat sdf = new SimpleDateFormat("yyyy", Locale.CHINA);

               year=sdf.format(date.getDate());

             }

          }

           return year;

    }

// 得到月份

    public String  getMonth(File file) throws BiffException, IOException {

              String month="";

              workbook=Workbook.getWorkbook(file);

              Sheet sheet=workbook.getSheet(0);

              int column=sheet.getColumns();

              for(int i=0;i<column;i++){

                Cell cell=sheet.getCell(i,1);

                if(cell.getContents().equals("日期"))

                {

                  Cell cellone=sheet.getCell(i+1,1);

                  DateCell date=(DateCell)cellone;

                  SimpleDateFormat sdf = new SimpleDateFormat("MM", Locale.CHINA);

                  month=sdf.format(date.getDate());

                }

             }

             return month;

          }

}

类似BUDGET的是一些基础类提供get()和set()方法，类似BUDGETDao的都是Spring框架中的一些接口，有不懂的地方还请大家多看看spring方面的书，这里就不再累赘了。

由于个人水平的原因，明知道有好多地方应该去重构，使程序更容易读懂，更容易维护，但是力不从心，只希望自己的水平尽快的提高.

 注意:其实这里并没有太多的涉及到spring方面的东西,就是对excel文件的解析,只不过当初是在spring下做的这个功能,标题就那样吧,我也懒 的改了!