getdataagent.java

运用java语言直接对excel表格进行处理 java

package bookselection;


import jade.core.Agent;
import jade.core.behaviours.OneShotBehaviour;

import java.sql.*;

import jade.core.AID;
import jade.core.behaviours.*;
import jade.lang.acl.ACLMessage;
import jade.lang.acl.MessageTemplate;
import jade.domain.DFService;
import jade.domain.FIPAException;
import jade.domain.FIPAAgentManagement.DFAgentDescription;
import jade.domain.FIPAAgentManagement.ServiceDescription;

import java.io.FileInputStream;

import javax.swing.JOptionPane;

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; 


public class GetDataAgent extends Agent{
	   
	    jdbcConn oc;
	     marc m;
         public static String outputFile = "e:\\marc.xls"; 
		 public static String fileToBeRead = "e:\\marc.xls"; 	
		 /**
		  * 
		  * 读取excel，遍历各个小格获取其中信息，并判断其是否是手机号码，并对正确的手机号码进行显示
		  * 
		  * 
		  * 注意： 1.sheet， 以0开始，以workbook.getNumberOfSheets()-1结束 2.row，
		  * 以0开始(getFirstRowNum)，以getLastRowNum结束 3.cell，
		  * 以0开始(getFirstCellNum)，以getLastCellNum结束, 结束的数目不知什么原因与显示的长度不同，可能会偏长
		  *  
		  */
		 private class readExcel extends OneShotBehaviour {
//		 public  void readExcel() {
			 public void action() { 
			 String [][] marc;
			 marc=new String[500][31];
			 
			 oc=new jdbcConn();
				String Sql1="SELECT * FROM marc1 " ; 
				if(oc.query(oc.con(), Sql1))
				{
					Sql1="DELETE FROM marc1";
					oc.execute(oc.con(), Sql1);
					}
				oc.closeMe();				
		  try {
		   // 创建对Excel工作簿文件的引用
		   HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
		   System.out.println("===SheetsNum===" + workbook.getNumberOfSheets());//获取sheet数
		   for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++)
		   {
		    if (null != workbook.getSheetAt(numSheets)) 
		    {
		     HSSFSheet aSheet = workbook.getSheetAt(numSheets);//获得一个sheet
//		     System.out.println("+++getFirstRowNum+++" +aSheet.getFirstRowNum());
//		     System.out.println("+++getLastRowNum+++" +aSheet.getLastRowNum()); 
		     oc=new jdbcConn();
	  		  String Sql="SELECT * FROM marc1" ;
	  		  if(!oc.query(oc.con(), Sql)||oc.query(oc.con(), Sql))
			{
//				System.out.println("Hello! agent...... ");
				try {
					 oc.rs.beforeFirst();
					 
					 
		     for (int i= 1; i <= aSheet.getLastRowNum(); i++) 
		     {
		      if (null != aSheet.getRow(i)) 
		      {
		       HSSFRow aRow = aSheet.getRow(i); 		       
//		       System.out.println(">>>getFirstCellNum<<<"+aRow.getFirstCellNum());
//		       System.out.println(">>>getLastCellNum<<<"+aRow.getLastCellNum());
//		       String tmp="";		  	   
				for (short j = 0; j < aRow.getLastCellNum(); j++)
		       { 
//		    	    cell[][] String=new String[rowNumOfSheet++][cellNumOfRow++];
		    	   
		        if (null != aRow.getCell(j)) 
		        {
		         HSSFCell aCell = aRow.getCell(j); 
		         int cellType = aCell.getCellType();
//		         System.out.println("cellType="+cellType);
		         switch (cellType) 
		         {/*
		         case 0://Numeric
		          String strCell = df.format(aCell
		            .getNumericCellValue());

		           System.out.println(strCell);
		           
		          break;*/
		         case 2:
		        	 Boolean BooleanCell = aCell.getBooleanCellValue();
//		        	 System.out.println(BooleanCell);
		        	 break;
		         case 1://String
		          String strCell = aCell.getStringCellValue();
//		          System.out.println(strCell);
//			         marc mc=new marc();
//		      System.out.println(writerTable.getValueAt(46, 1));
		          marc[i][j]=strCell;
//		          System.out.println( marc[i][j]);
//		          System.out.println( j);
		          break;
		         default:
		         System.out.println("格式不对不读");//其它格式的数据
		         }
		        }
		        else if(aRow.getCell(j)==null)
		        {
//		        	System.out.println( marc[i][j]);
//		        	System.out.println( j);
		        	marc[i][j]="";
		        }		        
		        }
				/*
				for(int j=0;j<31;j++){
					System.out.println( marc[i][j]);
				}*/				
						if(!oc.rs.next()||oc.rs.next())
						{
//							System.out.println("Hello! agent...... ");
							Sql="insert into marc1 values ('"+marc[i][0]+"','"+marc[i][1]+"','"+marc[i][2]+"','"+marc[i][3]+"','"+marc[i][4]+"','"+marc[i][5]+"','"+marc[i][6]+"','"+marc[i][7]+"','"+marc[i][8]+"','"+marc[i][9]+"','"+marc[i][10]+"','"+marc[i][11]+"','"+marc[i][12]+"','"+marc[i][13]+"','"+marc[i][14]+"','"+marc[i][15]+"','"+marc[i][16]+"','"+marc[i][17]+"','"+marc[i][18]+"','"+marc[i][19]+"','"+marc[i][20]+"','"+marc[i][21]+"','"+marc[i][22]+"','"+marc[i][23]+"','"+marc[i][24]+"','"+marc[i][25]+"','"+marc[i][26]+"','"+marc[i][27]+"','"+marc[i][28]+"','"+marc[i][29]+"','"+marc[i][30]+"')";
							oc.execute(oc.con(),Sql);
							}
		      }
		     }
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
							}
						oc.closeMe();
						}			
		  		  }
		      }
		   JOptionPane.showMessageDialog(null,"录入完毕");
		    
		   } catch (Exception e) {
			   System.out.println("ReadExcelError" + e);
			   } 
			 }
		   } 
		 
  protected  void setup() {
	  System.out.println("Hello! GetData-agent "+getAID().getName()+" is ready.");
      DFAgentDescription dfd = new DFAgentDescription();
      dfd.setName(getAID());
      ServiceDescription sd = new ServiceDescription();
      sd.setType("GetData");
      sd.setName("JADE-book-selection");
      dfd.addServices(sd);
      try {
             DFService.register(this, dfd);
          }
      catch (FIPAException fe) {
        fe.printStackTrace();
      }
      addBehaviour(new readExcel());
//	  myGui = new entryGui(this);
//	  myGui.show();
//		  POItest poi = new POItest();
//		  GetDataAgent poi =new GetDataAgent();
//		  poi.readExcel(); 
		 }
	}

        /**
		 * @param args
		 */