首页 > 代码库 > Java读写Excel
Java读写Excel
Java读写Excel
工作中经常需要对Excel进行读写操作,java操作excel文件比较流行的是apache poi包,excel分为xls(2003)和xlsx(2007)两种格式,操作这两种格式的excel需要不同的poi包。
xls格式
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.11-beta1</version> </dependency>
xlsx格式
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.11-beta1</version> </dependency>
读xls
File file = new File("src/test/resources/test.xls"); POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem); HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); int rowstart = hssfSheet.getFirstRowNum(); int rowEnd = hssfSheet.getLastRowNum(); for(int i=rowstart;i<=rowEnd;i++) { HSSFRow row = hssfSheet.getRow(i); if(null == row) continue; int cellStart = row.getFirstCellNum(); int cellEnd = row.getLastCellNum(); for(int k=cellStart;k<=cellEnd;k++) { HSSFCell cell = row.getCell(k); if(null==cell) continue; System.out.print("" + k + " "); //System.out.print("type:"+cell.getCellType()); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数字 System.out.print(cell.getNumericCellValue() + " "); break; case HSSFCell.CELL_TYPE_STRING: // 字符串 System.out.print(cell.getStringCellValue() + " "); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean System.out.println(cell.getBooleanCellValue() + " "); break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 System.out.print(cell.getCellFormula() + " "); break; case HSSFCell.CELL_TYPE_BLANK: // 空值 System.out.println(" "); break; case HSSFCell.CELL_TYPE_ERROR: // 故障 System.out.println(" "); break; default: System.out.print("未知类型 "); break; } } System.out.print("\n"); }
读xlsx
File file = new File("src/test/resources/test.xlsx"); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file); XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); int rowstart = xssfSheet.getFirstRowNum(); int rowEnd = xssfSheet.getLastRowNum(); for(int i=rowstart;i<=rowEnd;i++) { XSSFRow row = xssfSheet.getRow(i); if(null == row) continue; int cellStart = row.getFirstCellNum(); int cellEnd = row.getLastCellNum(); for(int k=cellStart;k<=cellEnd;k++) { XSSFCell cell = row.getCell(k); if(null==cell) continue; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数字 System.out.print(cell.getNumericCellValue() + " "); break; case HSSFCell.CELL_TYPE_STRING: // 字符串 System.out.print(cell.getStringCellValue() + " "); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean System.out.println(cell.getBooleanCellValue() + " "); break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 System.out.print(cell.getCellFormula() + " "); break; case HSSFCell.CELL_TYPE_BLANK: // 空值 System.out.println(" "); break; case HSSFCell.CELL_TYPE_ERROR: // 故障 System.out.println(" "); break; default: System.out.print("未知类型 "); break; } } System.out.print("\n"); }
写xls
HSSFWorkbook workbook = null; workbook = new HSSFWorkbook(); //获取参数个数作为excel列数 int columeCount = 6; //获取List size作为excel行数 int rowCount = 20; HSSFSheet sheet = workbook.createSheet("sheet name"); //创建第一栏 HSSFRow headRow = sheet.createRow(0); String[] titleArray = {"id", "name", "age", "email", "address", "phone"}; for(int m=0;m<=columeCount-1;m++) { HSSFCell cell = headRow.createCell(m); cell.setCellType(HSSFCell.CELL_TYPE_STRING); sheet.setColumnWidth(m, 6000); HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); short color = HSSFColor.RED.index; font.setColor(color); style.setFont(font); //填写数据 cell.setCellStyle(style); cell.setCellValue(titleArray[m]); } int index = 0; //写入数据 for(RowEntity entity : pRowEntityList) { //logger.info("写入一行"); HSSFRow row = sheet.createRow(index+1); for(int n=0;n<=columeCount-1;n++) row.createCell(n); row.getCell(0).setCellValue(entity.getId()); row.getCell(1).setCellValue(entity.getName()); row.getCell(2).setCellValue(entity.getAge()); row.getCell(3).setCellValue(entity.getEmail()); row.getCell(4).setCellValue(entity.getAddress()); row.getCell(5).setCellValue(entity.getPhone()); index++; }
继续阅读->
Java读写Excel
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。