前言
Apache POI是用Java编写的免费开源的跨平台的Java API,Apache POI提供API给Java程 序对Microsoft Office格式档案读和写的功能,其中使用最多的就是使用POI操作Excel文 件。
导入maven坐标
1 2 3 4 5 6 7 8
| <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> </dependency>
|
POI结构说明
- HSSF提供读写Microsoft Excel XLS格式档案的功能。
- XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。
- HWPF提供读写Microsoft Word DOC格式档案的功能。
- HSLF提供读写Microsoft PowerPoint格式档案的功能。
- HDGF提供读Microsoft Visio格式档案的功能。
- HPBF提供读Microsoft Publisher格式档案的功能。
- HSMF提供读Microsoft Outlook格式档案的功能。
POI-Excel写入(Writer)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
|
public class ExcelWriterTest { public static void main(String[] args) throws IOException{ HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet1 = workbook.createSheet("sheet1"); HSSFRow row = sheet1.createRow(0); row.createCell(0).setCellValue("姓名"); row.createCell(1).setCellValue("年龄"); for (int i = 1; i < 5; i++) { HSSFRow rowi = sheet1.createRow(i); rowi.createCell(0).setCellValue("第"+i+"姓名"); rowi.createCell(1).setCellValue("第"+i+"年龄"); }
FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\0\\Desktop\\POI.xls"); workbook.write(fileOutputStream); fileOutputStream.flush(); fileOutputStream.close(); System.out.println("Excel创建成功"); } }
|
POI-Excel读取(Reader)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
|
public class ExcelReaderTest { public static void main(String[] args) throws IOException{ HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("C:\\Users\\0\\Desktop\\POI.xls")); HSSFSheet sheet1 = workbook.getSheet("sheet1"); for (int i = 0; i <sheet1.getLastRowNum(); i++) { HSSFRow rowi = sheet1.getRow(i); String row1 = rowi.getCell(0).getStringCellValue(); String row2 = rowi.getCell(1).getStringCellValue(); System.out.println(row1+"|"+row2); } } }
|
POI-Excel操作工具类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134
|
public class POIUtils { private static final String xls = "xls";
private static final String xlsx = "xlsx";
private static final String DATE_FORMAT = "yyyy/MM/dd";
public static List<String[]> readExcel(MultipartFile file) throws IOException{ checkFile(file); Workbook workBook = getWorkBook(file); List<String[]> list = new ArrayList<>(); if (workBook != null) { for (int sheetNum = 0; sheetNum < workBook.getNumberOfSheets(); sheetNum++) { Sheet sheet = workBook.getSheetAt(sheetNum); if (sheet == null) { continue; } int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) { Row row = sheet.getRow(rowNum); if (row == null) { continue; } int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getPhysicalNumberOfCells(); String[] cells = new String[row.getPhysicalNumberOfCells()]; for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) { Cell cell = row.getCell(cellNum); cells[cellNum] = getCellValue(cell); } list.add(cells); } } workBook.close(); } return list; }
public static void checkFile(MultipartFile file) throws IOException{ if (null == file) { throw new FileNotFoundException("文件不存在!"); } String fileName = file.getOriginalFilename(); if (!fileName.endsWith(xls) && !fileName.endsWith(xlsx)) { throw new IOException(fileName + "不是excel文件"); } }
public static Workbook getWorkBook(MultipartFile file){ String fileName = file.getOriginalFilename(); Workbook workbook = null; try { InputStream is = file.getInputStream(); if (fileName.endsWith(xls)) { workbook = new HSSFWorkbook(is); } else if (fileName.endsWith(xlsx)) { workbook = new XSSFWorkbook(is); } } catch (IOException e) { e.printStackTrace(); } return workbook; }
public static String getCellValue(Cell cell){ String cellValue = ""; if (cell == null) { return cellValue; } String dataFormatString = cell.getCellStyle().getDataFormatString(); if (dataFormatString.equals("m/d/yy")) { cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue()); return cellValue; } if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { cell.setCellType(Cell.CELL_TYPE_STRING); } switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cellValue = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cellValue = String.valueOf(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cellValue = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_BLANK: cellValue = ""; break; case Cell.CELL_TYPE_ERROR: cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } return cellValue; } }
|
1.POI官网
2.POI常用API中文查询