POI操作Excel

导包

 实体

package com.qyc.input;

public class Student {
	private String idString;
	private String nameString;
	private int age;
	private double grade;

	public String getIdString() {
		return idString;
	}

	public void setIdString(String idString) {
		this.idString = idString;
	}

	public String getNameString() {
		return nameString;
	}

	public void setNameString(String nameString) {
		this.nameString = nameString;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public double getGrade() {
		return grade;
	}

	public void setGrade(double grade) {
		this.grade = grade;
	}

	@Override
	public String toString() {
		return "Student [idString=" + idString + ", nameString=" + nameString
				+ ", age=" + age + ", grade=" + grade + "]";
	}

	/**
	 * @param idString
	 * @param nameString
	 * @param age
	 * @param grade
	 */
	public Student(String idString, String nameString, int age, double grade) {
		super();
		this.idString = idString;
		this.nameString = nameString;
		this.age = age;
		this.grade = grade;
	}

	public Student() {
		// TODO 自动生成的构造函数存根
	}

}

创建

• 如果名字重复会覆盖

package com.qyc.output;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.qyc.input.Student;

public class OutPut {
	public static void main(String[] args) throws FileNotFoundException {
		List<Student> list = new ArrayList<Student>();
		Student student1 = new Student("001", "qyc001", 20, 100);
		Student student2 = new Student("002", "qyc002", 20, 99);
		Student student3 = new Student("003", "qyc003", 20, 98);
		Student student4 = new Student("004", "qyc004", 20, 97);
		list.add(student1);
		list.add(student2);
		list.add(student3);
		list.add(student4);
		XSSFWorkbook workbook = new XSSFWorkbook();
		// XSSFSheet名字
		XSSFSheet shape = workbook.createSheet("qyc成绩单");
		Row row = shape.createRow(0);
		// 合并单元格 从0开始算
		shape.addMergedRegion(new CellRangeAddress(0, 2, 0, 3));
		// 逻辑第一个
		Cell cell = row.createCell(0);
		cell.setCellValue("成绩单");
		// 样式
		CellStyle cellStyle = workbook.createCellStyle();
		// 水平居中
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		// 垂直
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		// 设置字体
		XSSFFont fout = workbook.createFont();
		fout.setFontName("黑体");
		fout.setFontHeightInPoints((short) 16);
		cellStyle.setFont(fout);
		cell.setCellStyle(cellStyle);

		// 循环
		int i = 3;
		for (Student student : list) {
			row = shape.createRow(i++);
			cell = row.createCell(0);
			cell.setCellValue(student.getIdString());
			cell = row.createCell(1);
			cell.setCellValue(student.getNameString());
			cell = row.createCell(2);
			cell.setCellValue(student.getAge());
			cell = row.createCell(3);
			cell.setCellValue(student.getGrade());
		}

		OutputStream file;
		try {
			file = new FileOutputStream("C:\\Users\\hp\\Desktop\\qyc.xlsx");
			workbook.write(file);
			
			workbook.close();
		} catch (Exception e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}

	}
}

取

• 注意数据类型
•  

package com.qyc.input;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;





import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiInput {
	public static void main(String[] args) throws IOException {
		
		List<Student> list = new ArrayList<Student>();
		
		InputStream inputStream = new FileInputStream("C:\\Users\\hp\\Desktop\\qyc.xlsx");
		//获取工作簿
		XSSFWorkbook  workbook = new XSSFWorkbook(inputStream);
		//获取工作表对象sheet
		XSSFSheet sheet = workbook.getSheetAt(0);
		Iterator<Row> iterable = sheet.iterator();
		int i = 3;
		
		while(iterable.hasNext()){
//			System.out.println(i++);
			//获取行
			Row row = iterable.next();
//			System.out.println(row.getRowNum());
			if(row.getRowNum()==0){
				//获取一个单元格
//				Cell cell = row.getCell(0);
//				Cell cell1 = row.getCell(1);
//				Cell cell2 = row.getCell(2);
//				Cell cell3 = row.getCell(3);
//				System.out.println(cell.getStringCellValue()
//						+"\t"+cell1.getStringCellValue()+
//						"\t"+cell2.getStringCellValue()
//						+"\t"+cell3.getStringCellValue());
			}else if(row.getRowNum()>0){
				Student student = new Student();
				student.setIdString(row.getCell(0).getStringCellValue());
				student.setNameString(row.getCell(1).getStringCellValue());
				student.setAge((int) row.getCell(2).getNumericCellValue());
				student.setGrade((double) row.getCell(3).getNumericCellValue());
				
				list.add(student);
//				for(int j=0;j<4;j++){
//					XSSFCell cell = (XSSFCell) row.getCell(j);
//					row.getCell(j).setCellType(CellType.STRING);
//					System.out.print(cell.getStringCellValue()+"\t");
//				}
				
			}
			
			
		}
		for(Student s:list){
			System.out.println(s.toString());
		}
		
	}
}