关于excle导数据的一些代码笔记

package com.bonc.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;

public class CopyExcelTest {

	/** 
    * 复制单个文件 
    * @param oldPath String 原文件路径 如：c:/fqf.txt 
    * @param newPath String 复制后路径 如：f:/fqf.txt 
    * @return boolean 
    */ 
  public static void copyFile(String newPath) { 
      try { 
          int bytesum = 0; 
          int byteread = 0; 
          File oldfile = new File("D:/aa/modul2.xls"); 
          if (oldfile.exists()) {                  //文件存在时 
              InputStream inStream = new FileInputStream("D:/aa/modul2.xls");      //读入原文件 
              FileOutputStream fs = new FileOutputStream(newPath); 
              byte[] buffer = new byte[1444]; 
              int length; 
              while ( (byteread = inStream.read(buffer)) != -1) { 
                  bytesum += byteread;            //字节数 文件大小  
                  fs.write(buffer, 0, byteread); 
              } 
              inStream.close();
              fs.close();
          } 
      }  catch (Exception e) { 
          System.out.println("复制单个文件操作出错"); 
          e.printStackTrace(); 
      } 
  } 
  
  public static void main(String[] args) {
	  copyFile("D:/aa/new2.xls");
  }
  
}

 

package com.bonc.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.bonc.bean.Huzhu;
import com.bonc.bean.Person;


/**
 * 操作Excel表格的功能类
 */
public class ExcelReader {
	
	public static List<Huzhu> showExcel(String path) throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(path)));
        HSSFSheet sheet = null;
       
        List<Huzhu> huzhuList = new ArrayList<>();
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
            sheet = workbook.getSheetAt(i);
            for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {// getLastRowNum，获取最后一行的行标
                HSSFRow row = sheet.getRow(j);
                if (row != null) {
                	if (row.getCell(1).toString().equals("a户主")){
                		Huzhu huzhu = new Huzhu(row.getCell(0).toString(), row.getCell(1).toString(),
                				row.getCell(2).toString(), row.getCell(3).toString(),
                				 row.getCell(4).toString()+row.getCell(5).toString(),j);
                		huzhuList.add(huzhu);
                	}
                }
                //System.out.println(""); // 读完一行后换行
            }
            //System.out.println(sheet.getLastRowNum());
            //System.out.println("读取sheet表：" + workbook.getSheetName(i) + " 完成");
        }
        
        return huzhuList;
    }
	
	public static List<Huzhu> getPerson(List<Huzhu> huzhuLists,String path) throws FileNotFoundException, IOException{
		
		
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(path)));
		HSSFSheet sheet = null;
		sheet = workbook.getSheetAt(0);
		
		int size = huzhuLists.size();
		int []nums = new int[size+1];
		for (int i = 0; i < nums.length - 1; i++) {
			Huzhu huzhu = huzhuLists.get(i);
			nums[i] = huzhu.getIndex();
		}
		nums[size] = sheet.getLastRowNum();
		int[] sumTotle = sumTotle(nums);
//		System.out.println("共户：" + size);
//		System.out.println("计算出：" + sumTotle.length);
		List<Huzhu> huzhuListAndPerson = new ArrayList<>();
		for (int i = 0; i < size; i++) {
			List<Person> personList = new ArrayList<>();
			Huzhu huzhu = huzhuLists.get(i);
			int huzhuIndex = huzhu.getIndex();
			int personNum = sumTotle[i];
			for (int j = 1; j <= personNum; j++) {
				HSSFRow row = sheet.getRow(huzhuIndex + j);
				 Person person = new Person(row.getCell(1).toString(), row.getCell(2).toString(), 
						 row.getCell(3).toString(), row.getCell(4).toString()+row.getCell(5).toString());
				 personList.add(person);
			}
			huzhu.setPerson(personList);
			huzhuListAndPerson.add(huzhu);
		}	
		return huzhuListAndPerson;
	}
	
	private static int[] sumTotle(int[] s){
		
		int []temp = new int[s.length-1];
		for (int i = 0; i < s.length - 1; i++) {
			temp[i] = s[i+1] - s[i] - 1;
		}
		return temp;
		
	}
	
	public static void main(String[] args) throws IOException {
		
		int []temp = new int[3];
		temp[0] = 2;
		temp[1] = 48;
		temp[2] = 1851;
		int[] is = sumTotle(temp);
		System.out.println(Arrays.toString(is));
	
	}
}

 

package com.bonc.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class SetValues {
	
	
	private String path;
	
	private HSSFSheet sheet;
	
	public String getPath() {
		return path;
	}
	public void setPath(String path) {
		this.path = path;
	}
	
	private static HSSFWorkbook wb = null;
	
	private static FileOutputStream os = null;
	
	public SetValues(String path) throws FileNotFoundException, IOException{
		this.wb = new HSSFWorkbook(new FileInputStream(path));
		this.os = new FileOutputStream(path);
		this.sheet = this.wb.getSheetAt(0);
	}

	public static void main(String[] args) throws FileNotFoundException, IOException {
		String filePath = "D:\\aa\\new2.xls";
		SetValues setValues = new SetValues(filePath);
		setValues.setValue( 15, 4, new HSSFRichTextString("汉族1"));
		//setValues.setValue( 16, 2, new HSSFRichTextString("汉族"));
		//setValues.setValue(filePath, 3, 12, new HSSFRichTextString("高亚伟"));
		//new SetValues().setValue(filePath, 14, 5, new HSSFRichTextString("高亚2伟"));
		setValues.setValue(4, 1, new HSSFRichTextString("高亚1伟"));
		setValues.flush();
		setValues.shutDown();
	}

	public  HSSFWorkbook getWb() {
		return wb;
	}
	public void setWb(HSSFWorkbook wb) {
		SetValues.wb = wb;
	}

	public  FileOutputStream getOs() {
		return os;
	}

	public  void setOs(FileOutputStream os) {
		SetValues.os = os;
	}

	public void setValue(int x, int y, HSSFRichTextString val) {
		try {
			HSSFRow row = sheet.getRow(x);
			HSSFCell cell = row.getCell((short) y);
			cell.setCellValue(val);
			wb.setForceFormulaRecalculation(true);	
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	public void flush(){
		try {
			wb.write(os);
			os.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public void shutDown() throws IOException{
		if (os != null) {
			os.close();
		}
	}


}