Excel读写操作

Excel导入导出简单示例，希望可以帮助到你。如果你的业务更复杂一点的话，可以在此基础上稍作修改。实现的方式有很多，如果有时间可以自己再研究其他实现方式。

谈谈我的看法：主要理解工作薄、工作表、行、列（单元格）之间的关系；

项目结构截图：

pom.xml引入的依赖

<dependencies>
  	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi</artifactId>
	    <version>4.1.2</version>
	</dependency>
	
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml</artifactId>
	    <version>4.1.2</version>
	</dependency>
	
	<!-- https://mvnrepository.com/artifact/joda-time/joda-time -->
	<dependency>
	    <groupId>joda-time</groupId>
	    <artifactId>joda-time</artifactId>
	    <version>2.10.5</version>
	</dependency>
  	
  </dependencies>

工具类：ExcelSuffix.java

public class ExcelSuffix {
	/**
	 * excel 03版本后缀
	 */
	public static final String EXCEL03PREFIX = ".xls";
	
	/**
	 * excel 07版本后缀
	 */
	public static final String EXCEL07PREFIX = ".xlsx";
	
	/**
	 * 文件路径
	 */
	public static final String PATH = "C:\\Desktop\\";
}

Excel读操作：

/**
 * Excel 读数据
 */
public class ExcelRead {

	/**
	 * 03版本的读取文件指定单元格内容
	 */
	@Test
	public void excelRead03V() throws Exception {
		FileInputStream fStream = new FileInputStream(ExcelSuffix.PATH + "excel03" + ExcelSuffix.EXCEL03PREFIX);
		Workbook workbook = new HSSFWorkbook(fStream);
		Sheet sheet = workbook.getSheetAt(0);
		Row row = sheet.getRow(0);
		Cell cell = row.getCell(0);
		String cellValue = cell.getStringCellValue();
		System.out.println("获取第一个单元格的内容 : " + cellValue);
		fStream.close();
	}

	/**
	 * 07版本的读取文件指定单元格内容
	 */
	@Test
	public void excelRead07V() throws Exception {
		FileInputStream fStream = new FileInputStream(ExcelSuffix.PATH + "excel07" + ExcelSuffix.EXCEL07PREFIX);
		Workbook workbook = new XSSFWorkbook(fStream);
		Sheet sheet = workbook.getSheetAt(0);
		Row row = sheet.getRow(0);
		Cell cell = row.getCell(0);
		String cellValue = cell.getStringCellValue();
		System.out.println("获取第一个单元格的内容 : " + cellValue);
		fStream.close();
	}

	/**
	 * 读取文件数据并判断类型
	 */
	@Test
	public void excelRead() throws Exception {
		// 加载文件数据
		FileInputStream inputStream = new FileInputStream(ExcelSuffix.PATH + "03明细" + ExcelSuffix.EXCEL03PREFIX);
		Workbook workbook = new HSSFWorkbook(inputStream);// 创建工作薄
		Sheet sheet = workbook.getSheetAt(0);// 获取工作表
		
		// 获取标题
		Row rowTitle = sheet.getRow(0);
		int cellCount = 0;
		if (rowTitle != null) {
			cellCount = rowTitle.getPhysicalNumberOfCells();//获取有多少列
			for (int cellNumi = 0; cellNumi < cellCount; cellNumi++) {
				Cell cell = rowTitle.getCell(cellNumi);//获取列
				if (cell != null) {
					String cellValue = cell.getStringCellValue();//获取单元格的值
					System.out.print(cellValue + " | ");
				}
			}
			System.out.println();
		}
		
		int rowCount = sheet.getPhysicalNumberOfRows();// 获取行数
		for (int rowNum = 1; rowNum < rowCount; rowNum++) {
			Row row = sheet.getRow(rowNum);
			if (row != null) {
				for (int i = 0; i < cellCount; i++) {
					Cell cell = row.getCell(i);
					if (cell != null) {
						CellType cellType = cell.getCellType();
						String cellValue = "";
						switch (cellType) {
						case STRING:// 字符串 
							cellValue = cell.getStringCellValue();
							System.out.print(cellValue + " | ");
							break;
						case NUMERIC:// 数字(日期、普通数字)
							if (DateUtil.isCellDateFormatted(cell)) {// true说明是日期 
								Date date = cell.getDateCellValue();
								cellValue = new DateTime(date).toString("yyyy-MM-dd");
							} else {//数字的话转字符串
								cell.setCellType(CellType.STRING);
								cellValue = cell.getStringCellValue();
							}
							System.out.print(cellValue + " | ");
							break;
						case BOOLEAN://布尔类型
							cellValue = String.valueOf(cell.getBooleanCellValue());
							System.out.println(cellValue);
							break;
						case BLANK://空
							break;
						default:
							break;
						}
					}
				}
			}
			System.out.println();
		}
	}
}

Excel写操作：

/**
 * excel 写操作的基本使用
 */
public class BaseExcelWrite {

	/**
	 * 03版本的excel的基本写操作示例
	 */
	@Test
	public void excel03V() throws Exception{
		Workbook workbook = new HSSFWorkbook();//创建工作薄
		Sheet sheet = workbook.createSheet("sheet03");//创建工作表
		Row row = sheet.createRow(0);//创建行
		Cell cell = row.createCell(0);//创建列
		cell.setCellValue("03版本cell1");//填写内容
		Cell cell2 = row.createCell(1);
		cell2.setCellValue("03版本cell2");
		//创建流
		FileOutputStream outputStream = new FileOutputStream(ExcelSuffix.PATH + "excel03" + ExcelSuffix.EXCEL03PREFIX);
		workbook.write(outputStream);//写操作
		//关闭流
		outputStream.close();
	}
	
	/**
	 * 07版本的excel的基本写操作示例
	 */
	@Test
	public void excel07V() throws Exception{
		Workbook workbook = new XSSFWorkbook();//创建工作薄
		Sheet sheet = workbook.createSheet("sheet07");//创建工作表
		Row row = sheet.createRow(0);//创建行
		Cell cell = row.createCell(0);//创建列
		cell.setCellValue("07版本cell1");//填写内容
		Cell cell2 = row.createCell(1);
		cell2.setCellValue("07版本cell2");
		//创建流
		FileOutputStream outputStream = new FileOutputStream(ExcelSuffix.PATH + "excel07" + ExcelSuffix.EXCEL07PREFIX);
		workbook.write(outputStream);//写操作
		//关闭流
		outputStream.close();
	}
}

---------------------------------我是分割线----------------------------------------------------
/**
 * excel 大数据量的操作
 */
public class BigExcelWrite {

	/**
	 * HSSF<br/>
	 * 缺点:最多只能处理65535行数据,否则会报如下异常:
	 * java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
	 * 优点:过程中写入缓存,不操作磁盘,最后一次性写入到文件中,速度相对较快
	 */
	@Test
	public void excelWrite03V() throws Exception{
		long beginTime = System.currentTimeMillis();//开始时间
		Workbook workbook = new HSSFWorkbook();//创建工作薄
		Sheet sheet = workbook.createSheet("03excel_bigdata");//创建工作表
		for (int rowNum = 0; rowNum < 65536; rowNum++) {
			Row row = sheet.createRow(rowNum);//创建行
			for (int cellNumj = 0; cellNumj < 10; cellNumj++) {
				Cell cell = row.createCell(cellNumj);//创建列
				cell.setCellValue("data" + cellNumj);//填写内容
			}
		}
		//创建流
		FileOutputStream outputStream = new FileOutputStream(ExcelSuffix.PATH + "03bigdata" + ExcelSuffix.EXCEL03PREFIX);
		workbook.write(outputStream);//写操作
		//关闭流
		outputStream.close();
		long endTime = System.currentTimeMillis();
		System.out.println((double)(endTime - beginTime)/1000);//3.268
	}
	
	/**
	 * XSSF<br/>
	 * 缺点:写数据时,速度非常慢,非常耗内存,也会发生内存溢出,如100w条
	 * 优点:可以写较大的数据量,如20w条
	 */
	@Test
	public void excelWrite07V() throws Exception{
		long beginTime = System.currentTimeMillis();//开始时间
		Workbook workbook = new XSSFWorkbook();//创建工作薄
		Sheet sheet = workbook.createSheet("07excel_bigdata");
		for (int rowNum = 0; rowNum < 65536; rowNum++) {
			Row row = sheet.createRow(rowNum);//创建行
			for (int cellNumj = 0; cellNumj < 10; cellNumj++) {
				Cell cell = row.createCell(cellNumj);//创建列
				cell.setCellValue("data" + cellNumj);//填写内容
			}
		}
		//创建流
		FileOutputStream outputStream = new FileOutputStream(ExcelSuffix.PATH + "07bigdata" + ExcelSuffix.EXCEL07PREFIX);
		workbook.write(outputStream);//写操作
		//关闭流
		outputStream.close();
		long endTime = System.currentTimeMillis();//接收时间
		System.out.println((double)(endTime - beginTime)/1000);//22.104
	}
	
	/**
	 * SXSSF<br/>
	 * 优点:可以写非常大的数据量,写数据速度快,占用更少的内存
	 * 注意:过程总会产生临时文件,需要清理临时文件;默认由100条记录被保存在内存中,如果超过这个数量,则最
	 * 前面的数据被写入临时文件,如果想要自定义内存中的数据的数量,可以使用new SXSSFWorkbook(数量)
	 * 有些事情不是绝对的,也有可能占用大量内存
	 */
	@Test
	public void excelWriteBigData() throws Exception {
		long beginTime = System.currentTimeMillis();//开始时间
		Workbook workbook = new SXSSFWorkbook();//创建工作薄
		Sheet sheet = workbook.createSheet("bigData");//创建工作表
		for(int rowNum = 0;rowNum < 1000000; rowNum ++) {
			Row row = sheet.createRow(rowNum);
			for (int cellNum = 0; cellNum < 10; cellNum++) {
				Cell cell = row.createCell(cellNum);
				cell.setCellValue("bigData" + cellNum);
			}
		}
		//创建流
		FileOutputStream fStream = new FileOutputStream(ExcelSuffix.PATH + "bigData" + ExcelSuffix.EXCEL07PREFIX);
		//写操作
		workbook.write(fStream);
		//关闭流
		fStream.close();
		//清除临时文件
		((SXSSFWorkbook) workbook).dispose();
		long endTime = System.currentTimeMillis();
		System.out.println((double)(endTime-beginTime)/1000);//30.02
	}
}

Excel基本读取：

/**
 * Excel基本读取
 */
public class ReadExcel {
	
	public static void main(String[] args) throws Exception {
		
		FileInputStream fStream = new FileInputStream(ExcelSuffix.PATH + "03明细" + ExcelSuffix.EXCEL03PREFIX);
		Object object = excelReader(fStream,0);
	}

	/**
	 * Excel 读取方法
	 * @param fStream 文件流
	 * @param sheetIndex 工作表下标索引
	 */
	public static Object excelReader(FileInputStream fStream, int sheetIndex) throws IOException {
		Workbook wk = new HSSFWorkbook(fStream);// 创建工作薄并将文件流加载到内存中
		int sheets = wk.getNumberOfSheets();// 获取所有工作表的个数
		int sheetAt = 0;//工作表下标索引
		if(sheetIndex <= sheets) sheetAt = sheetIndex;//从第几个工作表开始
		for (; sheetAt < sheets; sheetAt++) {// 循环工作表
			Sheet sheet = wk.getSheetAt(sheetAt);// 获取具体的工作表
			int rows = sheet.getPhysicalNumberOfRows();// 获取工作表的行数
			for (int i = 0; i < rows; i++) {// 循环行
				Row row = sheet.getRow(i);// 获取具体的行
				if (row != null) {// 判断行对象是否为空
					int cells = row.getPhysicalNumberOfCells();// 获取行中的列(单元格)个数
					for (int j = 0; j < cells; j++) {// 循环单元格,准备获取内容了
						Cell cell = row.getCell(j);// 获取具体的单元格
						if (cell != null) {// 判断单元格是否为空
							//CellType cellType = cell.getCellType();// 获取单元格内容的类型
							String cellValue = handleValueByCellType(cell);
							System.out.println(cellValue);
							//TODO 在这里做"坏事"
						}
					}
				}
			}
		}

		return null;//TODO 这里需要返回结果
	}

	/**
	 * 根据单元格类型处理单元格内容
	 * @param cell 单元格对象
	 * @deprecated 如果case中类型没有,可自行添加上
	 */
	public static String handleValueByCellType(Cell cell) {
		String cellValue = "";
		CellType cellType = cell.getCellType();
		switch (cellType) {
		case STRING:
			cellValue = cell.getStringCellValue();
			break;
		case NUMERIC:// 数字(日期、普通数字)
			if (DateUtil.isCellDateFormatted(cell)) {// true说明是日期
				Date date = cell.getDateCellValue();
				cellValue = new DateTime(date).toString("yyyy-MM-dd");
			} else {// 数字的话转字符串
				cell.setCellType(CellType.STRING);
				cellValue = cell.getStringCellValue();
			}
			break;
		case BOOLEAN://布尔类型
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		case BLANK://空
			break;
		default:
			break;
		}
		return cellValue;
	}
}

Excel读取到封装对象：

public class ExcelReader {

	private long row;// 行

	private List<Cell> cells = new ArrayList<Cell>();//列

	public long getRow() {
		return row;
	}

	public void setRow(long row) {
		this.row = row;
	}

	public List<Cell> getCells() {
		return cells;
	}

	public void setCells(Cell cell) {
		this.cells.add(cell);
	}

	@Override
	public String toString() {
		return "ExcelReader [row=" + row + ", cells=" + cells + "]\r\n";
	}
}
-------------------------------------------我是分割线---------------------------------------
/**
 * 读操作升级版
 */
public class ReadExcelPlus {

	@Test
	public void test() throws Exception {
		// 创建文件流
		FileInputStream fStream = new FileInputStream(ExcelSuffix.PATH + "03明细" + ExcelSuffix.EXCEL03PREFIX);
		// 调用读取表格的数据
		List<List<ExcelReader>> readExcel = ReadExcel(fStream, 0, 1);
		System.out.println(readExcel);// 输出打印
		// 关闭流
		fStream.close();
	}

	/**
	 * 读取excel
	 * 
	 * @param fiStream   文件流
	 * @param sheetIndex 工作表索引;从哪个工作表中开始读
	 * @param rowIndex   行索引;需要读取标题传0;不要标题传1
	 * @return
	 * @throws Exception
	 */
	public static List<List<ExcelReader>> ReadExcel(FileInputStream fiStream, int sheetIndex, 
			int rowIndex) throws Exception {
		List<List<ExcelReader>> excels = new ArrayList<List<ExcelReader>>();// 封装的对象
		Workbook wk = new HSSFWorkbook(fiStream);// 创建工作薄并将文件流加载到内存中
		int sheets = wk.getNumberOfSheets();// 获取所有工作表的个数
		int sheetAt = 0;// 工作表下标索引
		if (sheetIndex <= sheets)
			sheetAt = sheetIndex;// 从第几个工作表开始
		for (; sheetAt < sheets; sheetAt++) {// 循环工作表
			Sheet sheet = wk.getSheetAt(sheetAt);
			if (sheet.getPhysicalNumberOfRows() == 0)
				continue; // 工作表为空,跳过本次循环进入下一次循环
			List<ExcelReader> currentSheetExcels = new ArrayList<ExcelReader>();// 当前工作表的数据
			if (sheet != null) {// 这里也可以判断一下工作表是否为空
				int rows = sheet.getPhysicalNumberOfRows();// 获取行数获取工作表的行数
				int i = 0;
				if (rowIndex <= rows)
					i = rowIndex;// 从第几行开始读,如果标题不要,可以传1过来
				for (; i < rows; i++) {
					Row row = sheet.getRow(i);// 获取行
					int cells = row.getPhysicalNumberOfCells();// 获取当前行的所有列
					ExcelReader er = new ExcelReader();// 工作表读取对象
					er.setRow(i);// 设置行
					for (int j = 0; j < cells; j++) {
						Cell cell = row.getCell(j);// 获取每一个单元格
						if (cell != null)
							er.setCells(cell);// 设置列
					}
					if (er.getCells() != null && er.getCells().size() > 0) {// 判断列集合中是否有数据
						currentSheetExcels.add(er);// 将数据加入到当前工作表中
					}
				}
			}
			excels.add(currentSheetExcels);
		}
		return excels;
	}
}

Excel写操作：通过自定义注解和反射实现

/**
* @interface<br/>
* 
* @Target 表示我们的注解可以用在哪些地方
*     ElementType.TYPE  类/接口
*     ElementType.FIELD  字段声明
*     ElementType.METHOD  方法声明
*     ElementType.PACKAGE  包
* 
* @Retention 表示我们自定义的注解在什么阶段还有效
*     RetentionPolicy.RUNTIME 运行级别
*   RetentionPolicy.CLASS  字节码级别
*   RetentionPolicy.SOURCE  源码级别
* 
* 运行级别: RUNTIME > CLASS > SOURCE
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelHead {
	String value();
	
	int[] freeze() default {};
}
------------------------------------------------我是分割线-----------------------------------------
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
	String value();
}
------------------------------------------------我是分割线-----------------------------------------
/**
 * 学生类
 */
@ExcelHead(value = "学生信息",freeze = {0,3})
public class Student {

	@ExcelField(value = "姓名")
	private String name;
	
	@ExcelField(value = "性别")
	private String gender;
	
	@ExcelField(value = "手机号码")
	private String tel;
	
	@ExcelField(value = "生日")
	private String birthday;
	
	@ExcelField(value = "年龄")
	private int age;
	
	@ExcelField(value = "地址")
	private String address;
	
	@ExcelField(value = "真假")
	private boolean flag;

	//构造方法、getter、setter、toString方法省略
	
}

------------------------------------------------我是分割线-----------------------------------------
/**
 * 测试数据初始化以及测试
 */
public class ExcelWrite {
	List<Student> list = Arrays.asList(new Student("王祖贤", "女", "18763372970", "1992-05-21", 18, "HK", false),
			new Student("陈乔恩", "女", "18763372768", "1991-05-21", 18, "TW", true),
			new Student("黄蓉", "女", "18863372970", "1990-05-21", 18, "HK", false));

	@Test
	public void test() throws Exception {
		Class<Student> clazz = Student.class;
		String sheetName = clazz.getName();
		ExcelHead excelHead = clazz.getAnnotation(ExcelHead.class);
		sheetName = excelHead.value();

		Workbook wb = new XSSFWorkbook();// 创建工作薄
		Sheet sheet = wb.createSheet(sheetName);// 创建工作表

		Field[] fields = clazz.getDeclaredFields();// 获取类中声明的属性

		Row titleRow = sheet.createRow(0);// 创建标题行
		for (int i = 0; i < fields.length; i++) {
			ExcelField excelField = fields[i].getAnnotation(ExcelField.class);// 获取注解内容
			Cell cell = titleRow.createCell(i);// 创建单元格
			cell.setCellValue(excelField.value());// 设置值
		}
		
		String fieldName = null;
		String getMethodName = null;
		Object value = null;
		for (int i = 0; i < list.size(); i++) {
			Row row = sheet.createRow(i + 1);
			for (int j = 0; j < fields.length; j++) {
				Cell cell = row.createCell(j);
				fieldName = fields[j].getName();//字段名称
				getMethodName =  "get" + fieldName.substring(0, 1).toUpperCase()
						+ fieldName.substring(1);
				Method getMethod = clazz.getMethod(getMethodName, new Class[]{});
				value = getMethod.invoke(list.get(i), new Object[] {});
				//判断类型,然后做相应的转换
				if(value instanceof Boolean){
					cell.setCellValue((Boolean)value);
				}else {
					cell.setCellValue(value.toString());
				}
			}
		}

		FileOutputStream fis = new FileOutputStream(ExcelSuffix.PATH + sheetName + ExcelSuffix.EXCEL07PREFIX);
		wb.write(fis);
		fis.close();
	}
}

加油吧！

代码