配置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();
}
}