empservicepoiimpl.java

java调用poi实现excel文件的读取和写入

package com.service.impl;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Date;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
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.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import sun.java2d.pipe.Region;

import com.data.Emp;
import com.data.Copyemp;
import com.service.EmpServicePoi;

public class EmpServicePoiImpl implements EmpServicePoi {

	Session session;
	SessionFactory sessionFactory;
	

	
	public void conndb(){
		Configuration config =  new Configuration(); 
		sessionFactory = config.configure().buildSessionFactory();	
	}
	
	
	//EXCEL导入数据库
	public void insertEmpPoi() throws IOException {
		// TODO Auto-generated method stub

		session = sessionFactory.openSession();
		Transaction tx =  session.beginTransaction();
		Emp user = null;
		
		 FileInputStream finput = null;
	     finput = new FileInputStream("e:\\test.xls");
	
		 POIFSFileSystem fs = new POIFSFileSystem( finput );
		  HSSFWorkbook wb = new HSSFWorkbook(fs);
		  HSSFSheet sheet = wb.getSheetAt(0);
		  //读取第一个工作表，宣告其为sheet
		  finput.close();
		  HSSFRow row=null;
		  HSSFCell cell=null;
		  int rowNum;
		  int cellNum;
		  int age = 0;
		  String name = "";
		  String address = "";
		  rowNum = sheet.getLastRowNum();
		  System.out.println("row="+rowNum);
		  for (int i = 1; i <= rowNum; i++) {
			  user  = new Emp();
			   row = sheet.getRow(i);
			   cell = row.getCell((short) 0);
			   if (cell == null){
				   name="";
			   } else {
				   name = cell.getStringCellValue();
			   }
			   user.setName(name);
			   cell = row.getCell((short) 1);
			   if (cell == null){
				   age = 0;
			   } else {
				   age = (int) cell.getNumericCellValue();
			   }
			   user.setAge(age);
			   cell = row.getCell((short) 2);
			   if (cell == null){
				   address = "";
			   } else {
				   address = cell.getStringCellValue();
			   }
			   
			   user.setAddress(address);
			   session.save(user);
			   tx.commit();
			  
			  }
		
		
        
	}
	
	//test
	public void insertEmpPoi1() throws IOException, ParseException {
		// TODO Auto-generated method stub
		SimpleDateFormat df = new SimpleDateFormat("yy-MM-dd");
		Date d;
		session = sessionFactory.openSession();
		Transaction tx =  session.beginTransaction();
		Copyemp user = null;
		
		 FileInputStream finput = null;
	     finput = new FileInputStream("f:\\test1.xls");
	
		 POIFSFileSystem fs = new POIFSFileSystem( finput );
		  HSSFWorkbook wb = new HSSFWorkbook(fs);
		  HSSFSheet sheet = wb.getSheetAt(0);
		  //读取第一个工作表，宣告其为sheet
		  finput.close();
		  HSSFRow row=null;
		  HSSFCell cell=null;
		  int rowNum;
		  int cellNum;
		  int age = 0;
		  String name = "";
		  String address = "";
		  rowNum = sheet.getLastRowNum();
		  System.out.println("row="+rowNum);
		  for (int i = 1; i <= rowNum; i++) {
			  user  = new Copyemp();
			   row = sheet.getRow(i);
			   cell = row.getCell((short) 0);
			   
			     String hql = "FROM Copyemp where cname = "+"'"+toUnicode(cell.getStringCellValue())+"'";
				 Query query = session.createQuery(hql);
				 List<Copyemp> list = new ArrayList();
				 list = query.list();
				 System.out.println("===="); 
			  System.out.println(list.size());
			  System.out.println("====");
			  System.out.println(cell.getStringCellValue());
			  System.out.println("====");
			  
			//   String hql = "FROM Copyemp";
			//	 Query query = session.createQuery(hql);
			//	 List<Copyemp> list = new ArrayList();
			//	 list = query.list();
			//	 for(int j = 0;j<list.size();j++){
			//	 if(list.get(i).getCname().equals(cell.getStringCellValue()))
			//	 {
			//		 System.out.println("====");
			//		 System.out.println(list.get(i).getCname());
			//		 System.out.println("--");
			//  System.out.println(j);
			//  System.out.println("--");
			//  System.out.println(cell.getStringCellValue());
			//  System.out.println("====");
			//	 }else{}
			//	 }
			  
				 if (cell == null){
				   name="";
			   } else {
				   name = cell.getStringCellValue();
			   }
				
			   user.setCname(name);
			   
			   
			   
			   cell = row.getCell((short) 3);
			   
			   d = java.sql.Date.valueOf(cell.getStringCellValue());
			   System.out.println("今天是星期");
			   System.out.println(d.getDay());
			   System.out.println("=========");
			   user.setCaddress(address);
			  // session.save(user);
			  // tx.commit();
			  
			  }
		
		  readEmpPoi1();
        
	}
	
	
	
	

	public  void readEmpPoi() throws FileNotFoundException, IOException {
		     
		 	 session = sessionFactory.openSession();
			 String hql = "FROM Emp";
			 Query query = session.createQuery(hql);
			 List<Emp> list = new ArrayList();
			 list = query.list();
			 String outputFile="D:\\abc.xls";
			 System.out.println("name="+outputFile);
		     HSSFWorkbook workbook = new HSSFWorkbook();
		     HSSFSheet sheet = workbook.createSheet();
			 HSSFCellStyle style = workbook.createCellStyle(); //创建单元格样式 HSSFCellStyle类表示样式   
		     //style.setVerticalAlignment(style.VERTICAL_CENTER); //设置垂直居中  
		     style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中  
		     style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中 
		     
		     //边框颜色设置
		     style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
		     style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		     style.setLeftBorderColor(HSSFColor.BLACK.index);
		     style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		     style.setRightBorderColor(HSSFColor.BLACK.index);
		     style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		     style.setTopBorderColor(HSSFColor.BLACK.index); 
		     style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		     style.setBottomBorderColor(HSSFColor.BLACK.index);
		     
	         // 设置页眉
	         HSSFHeader header = sheet.getHeader();
	         header.setCenter(toUnicode("博源科技考勤基本情况汇总"));
	         header.setLeft(toUnicode(">>>>人事部"));
	         header.setRight(HSSFHeader.date());
	         // 设置页脚
	         HSSFFooter footer = sheet.getFooter();
	         footer.setCenter( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
		     
		     for(int i=0;i<list.size();i++){   
		         HSSFRow row = sheet.createRow(i+2);  //创建一行  HSSFRow表示一行  
		         row.setHeightInPoints(24);   //设置行高   
		         HSSFCell cell0=row.createCell((short)0);   //创建一个单元格  HSSFCell类表示单元格  
		         sheet.setColumnWidth(cell0.getCellNum(), (short) (60 * 50));
		         cell0.setCellValue(list.get(i).getId());   //设置单元格内容   
		         cell0.setCellStyle(style);   //设置单元格样式   
		         HSSFCell cell1=row.createCell((short)1);  
		         sheet.setColumnWidth(cell1.getCellNum(), (short) (150 * 50));
		         cell1.setCellValue(list.get(i).getName());   
		         cell1.setCellStyle(style);   
		         HSSFCell cell2=row.createCell((short)2);  
		         sheet.setColumnWidth(cell2.getCellNum(), (short) (60 * 50));
		         cell2.setCellValue(list.get(i).getAge());   
		         cell2.setCellStyle(style);   
		         HSSFCell cell3=row.createCell((short)3); 
		         sheet.setColumnWidth(cell3.getCellNum(), (short) (150 * 50));
		         cell3.setCellValue(list.get(i).getAddress());   
		         cell3.setCellStyle(style);   
 
  
		     }   
		     try {   
		    	 FileOutputStream fOut = new FileOutputStream(outputFile);
		    	 workbook.write(fOut);
		    	 fOut.flush();
		    	 // 操作结束，关闭文件
                 fOut.close();
		    	 System.out.println("文件生成...");
		     } catch (Exception  e) {   
		    	 System.out.println("已运行 xlCreate() : " + e );
		     } 
	}
	
	
	
	//测试两个表间数据处理
	public  void readEmpPoi1() throws FileNotFoundException, IOException {
	     
	 	 session = sessionFactory.openSession();
	 	 Transaction tx =  session.beginTransaction();
		 String hql = "FROM Copyemp";
		 Query query = session.createQuery(hql);
		 List<Copyemp> list = new ArrayList();
		 list = query.list();
		 Emp user = null;
		  
		 try {  
	     for(int i=0;i<list.size();i++){  
	    	 user  = new Emp();
	      
	       //  cell0.setCellValue(list.get(i).getCid());   //设置单元格内容   
	       if(list.get(i).getCname().equals("风格人")){
	    	   System.out.print("---------->"); 
	       }
	         user.setName(list.get(i).getCname()); 
	         System.out.print(list.get(i).getCname());
	        
	         user.setAge(list.get(i).getCage()); 
	         System.out.print(list.get(i).getCage());
	        
	         user.setAddress(list.get(i).getCaddress());
	         System.out.print(list.get(i).getCaddress());
	         session.save(user);  
	         tx.commit();

	     }   
	     
	    	 
	    	 System.out.println("数据已转移");
	     } catch (Exception  e) {   
	    	 System.out.println("已运行 xlCreate() : " + e );
	     } 
}


    private String toUnicode(String s){
        try{
          //byte[] data=new byte[3+s.length()*2];
          //LittleEndian.putShort(data, 0, (short) s.length()); 
   
          String unicodeString = new String(s.getBytes("Unicode"),"Unicode");
          //StringUtil.putUncompressedUnicode(unicodeString, data,
          //                                           0x3 + 0);
          return unicodeString;
        }catch(Exception ex){
          System.out.println(ex.toString());
          return "aa";
        }
      }

	

}