Excel读写操作

谁借莪１个温暖的怀抱￠ 2023-03-12 12:27 6阅读 0赞

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

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

项目结构截图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NTI3NjU3MTM4_size_16_color_FFFFFF_t_70][]

pom.xml引入的依赖

<dependencies>
      	
    	<dependency>
    	    <groupId>org.apache.poi</groupId>
    	    <artifactId>poi</artifactId>
    	    <version>4.1.2</version>
    	</dependency>
    	
    	
    	<dependency>
    	    <groupId>org.apache.poi</groupId>
    	    <artifactId>poi-ooxml</artifactId>
    	    <version>4.1.2</version>
    	</dependency>
    	
    	
    	<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();
    	}
    }

加油吧！

[代码][Link 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NTI3NjU3MTM4_size_16_color_FFFFFF_t_70]: /images/20230312/9267ad71cefc4bcb8c1bd8d8096b6b81.png
[Link 1]: https://gitee.com/Azzan/excel/tree/master/java8