Java read and write Excel combat fully resolved

This article is launched on the wechat public account “AndroidTraveler”.

background

It’s graduation season, when many graduates are entering the workforce for the first time.

So, here are some Android technical points for beginners.

For example, the last article on Android development is a list of things you need to know to avoid pitfalls for new Android developers.

At the same time, it is also the summer vacation, so college students are on vacation.

This passage is mainly from the question of a college student.

Therefore, I would like to share my personal ideas and methods to solve the problem, hoping to inspire him.

Welcome to exchange and share.

The title

Language used :JAVA

Requirement: Read data from one Excel table (e.g., name + score), reorder it (by score), and output it to another Excel table.

Analysis of the

We generally take a disjunctive approach to requirements. Break a big problem into small problems. When the small problems are solved, the whole big problem is solved.

This requirement is clear and requires solving three problems:

Read Excel table data
Sort data
Write the data to another Excel table

We require the Java language, and one of the most important aspects of the Java language is object orientation.

So the first thing we need to think about is what classes we need to create in this problem.

You can probably imagine needing the following classes:

Read data types: ExcelReader write data types: ExcelWriter data sorting categories: because the Java API bring, so don’t need to be repeated rolling data model classes: StudentScore start classes: ParserStart, with the main method

The general UML diagram is as follows:

At this point we can write the v0.1 code: excelreader.java:

import java.util.List;
public class ExcelReader {
	public List<StudentScore> read(String fileName) {
		//TODO
		return null; }}Copy the code

ExcelWriter.java:

import java.util.List;
public class ExcelWriter {
	public void write(String fileName, List<StudentScore> list) {
		//TODO}}Copy the code

StudentScore.java:

public class StudentScore {
	private String name;
	private int score;
	
	public StudentScore(String name, int score) {
		super(a);this.name = name;
		this.score = score;
	}
	public String getName(a) {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getScore(a) {
		return score;
	}
	public void setScore(int score) {
		this.score = score; }}Copy the code

ParserStart.java:

import java.util.List;

public class ParserStart {
	public static void main(String[] args) {
		// Step 1: Read the data
		List<StudentScore> dataList = new ExcelReader().read("input.xls");
		// Step 2: Sort
		//TODO
		// Write data
		new ExcelWriter().write("output.xls", dataList); }}Copy the code

All right, the basic framework is in place. Let’s implement our method step by step.

V0.2 code: Improve ExcelReader’s read method

There are third-party libraries for reading Excel methods, so we don’t need to write them ourselves. We are using a POI library provided by a third party, Apache. Download link address: poi.apache.org/download.ht… The version used for this article is 4.1.0, and you can unpack it and introduce the JAR package into the Eclipse project.

The next step is to actually write the code, as detailed in the comments.

The following is an example of a file we will read:

ExcelReader.java:

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelReader {
	public List<StudentScore> read(String fileName) throws EncryptedDocumentException, IOException {
		if (fileName == null) return null;
		
		File xlsFile = new File(fileName);
		if(! xlsFile.exists())return null;
		
		/ / worksheet
		Workbook workbook = WorkbookFactory.create(xlsFile);
		/ / table number
		int numberOfSheets = workbook.getNumberOfSheets();
// System.out.println(numberOfSheets);
		if (numberOfSheets <= 0) return null;
		
		List<StudentScore> list = new ArrayList<>();
		// Our requirement only needs to process one table, so no traversal is required
		Sheet sheet = workbook.getSheetAt(0);
		/ / the number of rows
		int rowNumbers = sheet.getLastRowNum() + 1;
// System.out.println(rowNumbers);
		StudentScore score;
		// Read the data, the second line begins reading
		for (int row = 1; row < rowNumbers; row++) {
			Row r = sheet.getRow(row);
// System.out.println(r.getPhysicalNumberOfCells());
			// We only need the first two columns
			if (r.getPhysicalNumberOfCells() >= 2) {
				score = new StudentScore(r.getCell(0).toString(), (int) Double.parseDouble(r.getCell(1).toString())); list.add(score); }}returnlist; }}Copy the code

V0.3 code: after reading the data to do sorting processing

In V0.2, we successfully read the data, but we read the data in the order in Excel, so we need to do sorting. The Java function library has methods for sorting collections. However, we need to do extra processing on the Model and add collation rules. Because you can sort from smallest to largest, or you can sort from largest to smallest.

StudentScore.java:

public class StudentScore implements Comparable<StudentScore>{
	
	private String name;
	private int score;
	
	public StudentScore(String name, int score) {
		super(a);this.name = name;
		this.score = score;
	}
	public String getName(a) {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getScore(a) {
		return score;
	}
	public void setScore(int score) {
		this.score = score;
	}
	
	@Override
	public String toString(a) {
		return "StudentScore [name=" + name + ", score=" + score + "]";
	}
	
	@Override
	public int compareTo(StudentScore o) {
		return o.score - this.score; }}Copy the code

ParserStart.java:

import java.util.Collections;
import java.util.List;

public class ParserStart {

	public static void main(String[] args) throws Exception{
		// Step 1: Read the data
		List<StudentScore> dataList = new ExcelReader().read("resource/input.xls");
		System.out.println(dataList);
		// Step 2: Sort
		Collections.sort(dataList);
		System.out.println(dataList);
		// Write data
// new ExcelWriter().write("output.xls", dataList);}}Copy the code

V0.4 code: write sorted data to another Excel table

In v0.3, we finished sorting the data, and then we need to write the sorted data to output.xls.

ExcelWriter.java

import java.io.File;
import java.io.IOException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelWriter {

	public void write(String fileName, List<StudentScore> list)  {
		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = workbook.createSheet("StudentScore");

		// Create Excel header row, first line
		HSSFRow headRow = sheet.createRow(0);
		headRow.createCell(0).setCellValue("Name");
		headRow.createCell(1).setCellValue("Score");

		// Write data to an Excel table
		for (StudentScore studentScore : list) {
			createCell(studentScore, sheet);
		}

		File xlsFile = new File(fileName);
		try {
			Workbook.write (new FileOutputStream(xlsFile));
			workbook.write(xlsFile);
		} catch (IOException e) {
		    // TODO
		} finally {
			try {
				workbook.close();
			} catch (IOException e) {
			    // TODO}}}// Create a row of Excel data.
	private void createCell(StudentScore studentScore, HSSFSheet sheet) {
		HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
		dataRow.createCell(0).setCellValue(studentScore.getName());
		dataRow.createCell(1).setCellValue(studentScore.getScore()); }}Copy the code

ParserStart.java

import java.util.Collections;
import java.util.List;

public class ParserStart {

	public static void main(String[] args) throws Exception {
		// Step 1: Read the data
		List<StudentScore> dataList = new ExcelReader().read("resource/input.xls");
		System.out.println(dataList);
		// Step 2: Sort
		Collections.sort(dataList);
		System.out.println(dataList);
		// Write data
		new ExcelWriter().write("resource/output.xls", dataList); }}Copy the code

At this point, after several iterations, our requirements are fulfilled.

NOTE: In this project, input.xls is placed under the Resource folder. So the path passed to the final version is resource/input.xls. In addition, it was found that Eclipse did not display output.xls when outputting, so we need to refresh it. In addition, I may need to modify the JRE when downloading my project to run validation. Also, don’t import jar packages in the wrong place:

Of course, there are a few points that need to be addressed:

There is no validity check on the input table data, such as whether there is a need to do some prompt operations if the score is negative.
If the file does not exist, return NULL. And it does not determine whether the file is an Excel file. I’ll leave it up to you. And there is no exception processing, throws directly.
I’m not abstracting it because it’s simple. But considering that you may need to read and write Word or PDF or other files, consider introducing inheritance and polymorphism. Extract the base class.
Organize folders and name them properly.

And let’s talk about the application scenarios. Actually, there are.

If you want to know the knowledge point, welcome the public account message private message, maybe the next pick is you.

Source code: github.com/nesger/Java…

Java reads and parses Excel data based on Apache POI (1) Java reads and parses Excel data based on Apache POI (2) Java exports data rows to Excel tables based on Apache POI

Java read and write Excel combat fully resolved

background

The title

Analysis of the

Related Posts

Introduction to accessibility development for Android apps and games

First acquaintance with Kotlin, the new Android language

WMS is responsible for launching an Activity.