一,POI中提供的常用方法
Workbook类提供的方法 createSheet("员工信息表") 创建某张表getSheetAt(0)根据工作簿索引获取工作表getSheetName(0) 获取第一张表的表名getSheet("表名")获取某个表的工作表getNumberOfSheets() 获得工作薄中工作表的个数使用for循环获取每张表Sheet接口提供的方法 createRow(0) 创建第一列getSheetName() 获取Sheet的名称getLastRowNum() 获取最后一行的索引(最后一行行号-1)getRow(0) 获取Sheet表中第1行(索引为0)Row对象Row提供的方法getLastCellNum() 获取某一行的最后列号,例如E列是第5列getCell(0) 获取某一行的第一个单元格cell提供的方法getStringCellValue() 获取单元格数据setCellValue() 设置单元格数据setCellType() 设置单元格数据类型
二,Excel数据的写入和读取
public class ExcelUtils2 {//向Excel总写入数据public static void main(String[] args) throws IOException {List<Person> list=new ArrayList<>();Person person1=new Person(1,"赵一","男",12,new Date());Person person2=new Person(2,"杨二","女",22,new Date());Person person3=new Person(3,"张三","男",88,new Date());Person person4=new Person(4,"李四","女",42,new Date());Person person5=new Person(5,"王五","男",28,new Date());list.add(person1);list.add(person2);list.add(person3);list.add(person4);list.add(person5);//创建一个工作簿HSSFWorkbook hssfWorkbook = new HSSFWorkbook();//创建一个工作表HSSFSheet sheet = hssfWorkbook.createSheet("员工信息表");//创建行,第一行表头String[] title={"编号","姓名","性别","年龄","生日"};HSSFRow row = sheet.createRow(0);for (int i = 0; i < title.length; i++) {HSSFCell cell = row.createCell(i);cell.setCellValue(title[i]);}//把list中数据放进去for (int i = 0; i < list.size(); i++) {HSSFRow row1 = sheet.createRow(i + 1);Person person = list.get(i);HSSFCell cell1 = row1.createCell(0);cell1.setCellValue(person.getId());HSSFCell cell2 = row1.createCell(1);cell2.setCellValue(person.getName());HSSFCell cell3 = row1.createCell(2);cell3.setCellValue(person.getSex());HSSFCell cell4 = row1.createCell(3);cell4.setCellValue(person.getAge());HSSFCell cell5 = row1.createCell(4);SimpleDateFormat sdf = new SimpleDateFormat("yyyy年dd月MM日");String stringDate = sdf.format(person.getBirthday());cell5.setCellValue(stringDate);}//把数据输出到硬盘中File file = new File("G:\\project\\POIexample2.xls");OutputStream outputStream=new FileOutputStream(file);hssfWorkbook.write(outputStream);outputStream.close();}//将Excel中的数据提取出来public static void findBook() throws IOException {//获取工作簿Workbook workbook = new XSSFWorkbook("G:\\project\\POIexample.xlsx");//获取工作表Sheet sheet = workbook.getSheetAt(0);//获取表中的最后一行的索引int lastRowNum = sheet.getLastRowNum();//获取单元格列数Row row1 = sheet.getRow(0);int lastCellNum = row1.getLastCellNum();XSSFCell cell = null;//遍历表中行for (int i = 0; i <= lastRowNum; i++) {Row sheetRow = sheet.getRow(i);//遍历表中行的单元格for (Cell cell1 : sheetRow) {//设置单元格的类型cell1.setCellType(Cell.CELL_TYPE_STRING);String value = cell1.getStringCellValue();System.out.println(value);}}//关闭对象workbook.close();}//从Excel中提取数据public static void readExcel() throws Exception{InputStream is = new FileInputStream(new File(fileName));Workbook hssfWorkbook = null;if (fileName.endsWith("xlsx")){hssfWorkbook = new XSSFWorkbook(is);//Excel }else if (fileName.endsWith("xls")){hssfWorkbook = new HSSFWorkbook(is);//Excel }// HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);// XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);User student = null;List<User> list = new ArrayList<User>();// 循环工作表Sheetfor (int numSheet = 0; numSheet <hssfWorkbook.getNumberOfSheets(); numSheet++) {//HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);if (hssfSheet == null) {continue;}// 循环行Rowfor (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {//HSSFRow hssfRow = hssfSheet.getRow(rowNum);Row hssfRow = hssfSheet.getRow(rowNum);if (hssfRow != null) {student = new User();//HSSFCell name = hssfRow.getCell(0);//HSSFCell pwd = hssfRow.getCell(1);Cell name = hssfRow.getCell(0);Cell pwd = hssfRow.getCell(1);//这里是自己的逻辑student.setUserName(name.toString());student.setPassword(pwd.toString());list.add(student);}}}}
