文章目录

配置mavenCSVxlsx

配置maven

使用POI进行分析。

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>WEEK6_excel</groupId> <artifactId>WEEK6_excel</artifactId> <version>0.0.1-SNAPSHOT</version> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> <repositories> <repository> <id>central</id> <url>http://maven.aliyun.com/nexus/content/groups/public/</url> </repository> </repositories> <pluginRepositories> <pluginRepository> <id>central</id> <url>http://maven.aliyun.com/nexus/content/groups/public/</url> </pluginRepository> </pluginRepositories> <dependencies> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>ooxml-schemas</artifactId> <version>1.3</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>2.6.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-examples</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-csv</artifactId> <version>1.6</version> </dependency> </dependencies> </project>

CSV

import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; import java.io.Reader; import java.time.LocalDate; import org.apache.commons.csv.CSVFormat; import org.apache.commons.csv.CSVPrinter; import org.apache.commons.csv.CSVRecord; public class CSVTest { public static void main(String[] args) throws Exception { readCSVWithIndex(); System.out.println("===========分割线1==================="); readCSVWithName(); System.out.println("===========分割线2==================="); writeCSV(); System.out.println("write done"); } public static void readCSVWithIndex() throws Exception { Reader in = new FileReader("c:/temp/score.csv"); Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(in); for (CSVRecord record : records) { System.out.println(record.get(0)); //0代表第一列 } } public static void readCSVWithName() throws Exception { Reader in = new FileReader("c:/temp/score.csv"); Iterable<CSVRecord> records = CSVFormat.RFC4180.withHeader("Name", "Subject", "Score").parse(in); for (CSVRecord record : records) { System.out.println(record.get("Subject")); } } public static void writeCSV() throws Exception { try (CSVPrinter printer = new CSVPrinter(new FileWriter("person.csv"), CSVFormat.EXCEL)) { printer.printRecord("id", "userName", "firstName", "lastName", "birthday"); printer.printRecord(1, "john73", "John", "Doe", LocalDate.of(1973, 9, 15)); printer.println(); //空白行 printer.printRecord(2, "mary", "Mary", "Meyer", LocalDate.of(1985, 3, 29)); } catch (IOException ex) { ex.printStackTrace(); } } }

xlsx

import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.util.Iterator; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ReadWriteExcelFile { public static void readXLSFile() throws IOException { InputStream ExcelFileToRead = new FileInputStream("C:/Test.xls"); HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { cell = (HSSFCell) cells.next(); if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { System.out.print(cell.getStringCellValue() + " "); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { System.out.print(cell.getNumericCellValue() + " "); } else { // U Can Handel Boolean, Formula, Errors } } System.out.println(); } } public static void writeXLSFile() throws IOException { String excelFileName = "C:/Test.xls";// name of excel file String sheetName = "Sheet1";// name of sheet HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(sheetName); // iterating r number of rows for (int r = 0; r < 5; r++) { HSSFRow row = sheet.createRow(r); // iterating c number of columns for (int c = 0; c < 5; c++) { HSSFCell cell = row.createCell(c); cell.setCellValue("Cell " + r + " " + c); } } FileOutputStream fileOut = new FileOutputStream(excelFileName); // write this workbook to an Outputstream. wb.write(fileOut); fileOut.flush(); fileOut.close(); } public static void readXLSXFile() throws IOException { InputStream ExcelFileToRead = new FileInputStream("Test.xlsx"); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { System.out.print(cell.getStringCellValue() + " "); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { System.out.print(cell.getNumericCellValue() + " "); } else { // U Can Handel Boolean, Formula, Errors } } System.out.println(); } } public static void writeXLSXFile() throws IOException { String excelFileName = "Test.xlsx";// name of excel file String sheetName = "Sheet1";// name of sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName); // iterating r number of rows for (int r = 0; r < 5; r++) { XSSFRow row = sheet.createRow(r); // iterating c number of columns for (int c = 0; c < 5; c++) { XSSFCell cell = row.createCell(c); cell.setCellValue("Cell " + r + " " + c); } } FileOutputStream fileOut = new FileOutputStream(excelFileName); // write this workbook to an Outputstream. wb.write(fileOut); fileOut.flush(); fileOut.close(); } public static void main(String[] args) throws IOException { writeXLSFile(); readXLSFile(); writeXLSXFile(); readXLSXFile(); } }