java处理csv/xlsx

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