XSSFWorkbook Excel读写工具

API参考：

        XSSFRow  API（英文）：http://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFRow.html

XSSFWorkbook API（英文）：http://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFWorkbook.html

其他参考链接：https://blog.csdn.net/weixin_33773670/article/details/114226032

  https://blog.csdn.net/u014116780/article/details/84139462?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-3.control&dist_request_id=&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-3.control

https://blog.csdn.net/benben574599386/article/details/78661098?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-6.control&dist_request_id=&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-6.control

   参考链接：https://blog.csdn.net/u012325167/article/details/74130010
                     XSSFWorkbook实现对excl读写：https://blog.csdn.net/fgghhfg574/article/details/103343030

 

 

1、所需要的jar包

                <!-- Excel-->
				<dependency>
				    <groupId>org.apache.poi</groupId>
				    <artifactId>poi</artifactId>
				    <version>3.17</version>
				</dependency>
				
				<dependency>
				    <groupId>org.apache.poi</groupId>
				    <artifactId>poi-ooxml</artifactId>
				    <version>3.17</version>
				</dependency>
				
				<dependency>
				    <groupId>net.sourceforge.jexcelapi</groupId>
				    <artifactId>jxl</artifactId>
				    <version>2.6.12</version>
				</dependency>

 

2、读取excel

private static void ReadExcel(String inputFilePath, int rowBegin) throws IOException {
	    FileInputStream fileInput = new FileInputStream(inputFilePath);//创建文件输入流
	    XSSFWorkbook wb = new XSSFWorkbook(fileInput);//由输入流文件得到工作簿对象
	    //System.out.println("xssfWorkbook对象：" + wb);
	    
	    XSSFSheet sheet = wb.getSheetAt(0);//获取第一个sheet
	    int lastRowNum = sheet.getLastRowNum(); //获取表格内容的最后一行的行数

	    //rowBegin代表要开始读取的行号，下面这个循环的作用是读取每一行内容
	    for (int i = rowBegin; i <= lastRowNum; ++i) {
	        XSSFRow row = sheet.getRow(i);//获取每一行
	        int columnNum = row.getLastCellNum();//获取每一行的最后一列的列号，即总列数
	        for (int j=0; j<columnNum; ++j) {
	            XSSFCell cell = row.getCell(j);//获取每个单元格
	            //设置单元格类型
	            cell.setCellType(CellType.STRING);
	            //获取单元格数据
	            String cellValue = cell.getStringCellValue();
	            System.out.print(cellValue+"\t");
	            
	        }
	        System.out.println();
	    }
	    wb.close();
	    fileInput.close();
	}
	

 

3、写入excel

private static void WriteExcel() throws IOException {
		//在内存中创建一个Excel文件
		XSSFWorkbook workbook = new XSSFWorkbook();
		//创建工作表，指定工作表名称
		XSSFSheet sheet = workbook.createSheet("写入测试");
		
		//创建行，0表示第一行
		XSSFRow row = sheet.createRow(0);
		//创建单元格，0表示第一个单元格
		row.createCell(0).setCellValue("编号");
		row.createCell(1).setCellValue("姓名");
		row.createCell(2).setCellValue("年龄");

		XSSFRow row1 = sheet.createRow(1);
		row1.createCell(0).setCellValue("1");
		row1.createCell(1).setCellValue("小明");
		row1.createCell(2).setCellValue("10");

		XSSFRow row2 = sheet.createRow(2);
		row2.createCell(0).setCellValue("2");
		row2.createCell(1).setCellValue("小王");
		row2.createCell(2).setCellValue("20");

		//通过输出流将workbook对象下载到磁盘
		FileOutputStream out = new 
        FileOutputStream("C:\\Users\\Administrator\\Desktop\\itcast.xlsx");
		workbook.write(out);
		out.flush();//刷新
		out.close();//关闭
		workbook.close();
	
	}
	

 

3、修改excel

private void ModifyExcel(String inputFilePath) throws IOException {
		 FileInputStream fileInput = new FileInputStream(inputFilePath);   //创建文件输入流
		 XSSFWorkbook wb = new XSSFWorkbook(fileInput);    //由输入流文件得到工作簿对象
		 
		 XSSFSheet sheet = wb.getSheetAt(0);         //获取第一个sheet
		 int lastRowNum = sheet.getLastRowNum();    //获取表格内容的最后一行的行数
		 
         //锁定要修改的单元格：先找到行，再找到列
		 XSSFRow row1 = sheet.getRow(1);
		 row1.createCell(0).setCellValue("3");
		 
		//通过输出流将workbook对象下载到磁盘
		FileOutputStream out = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\itcast2.xlsx");
		wb.write(out);
		out.flush();//刷新
		out.close();//关闭
		wb.close();
	
	}

4、按列读取

private void ReadIndex(String inputFilePath) throws IOException{
		 //创建excel对象
		 FileInputStream fileInput = new FileInputStream(inputFilePath);   
		 XSSFWorkbook wb = new XSSFWorkbook(fileInput);
		 
		 //选择要获取的sheet页
		 XSSFSheet sheet = wb.getSheetAt(0);
		 
	    //读取第5列的第2-10行数据
	    for (int i = 2; i <= 10; ++i) {
	        XSSFRow row = sheet.getRow(i);//获取每一行
	        XSSFCell cell = row.getCell(1);//获取2-10行每一行的第5列数据
	        //设置单元格类型
            cell.setCellType(CellType.STRING);
            //获取单元格数据
            String cellValue = cell.getStringCellValue();
            System.out.println(cellValue+"\t");
	    }
	    wb.close();
	    fileInput.close();
	
	}

 

5、按列写入

private void WriteIndex(String inputFilePath) throws IOException{	
		 FileInputStream fileInput = new FileInputStream(inputFilePath);   //创建文件输入流
		 XSSFWorkbook wb = new XSSFWorkbook(fileInput);    //由输入流文件得到工作簿对象
		 
		 XSSFSheet sheet = wb.getSheetAt(0);         //获取第一个sheet
		 
		 //写入第3列，第2-10行
		    for (int i = 2; i <= 10; ++i) {
		        XSSFRow row = sheet.getRow(i);//获取每一行
		        row.createCell(0).setCellValue("3");   //给第一列赋值=3
		    }
		 		 
		//通过输出流将workbook对象下载到磁盘
		FileOutputStream out = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\itcast2.xlsx");
		wb.write(out);
		out.flush();//刷新
		out.close();//关闭
		wb.close();
		
	}