How to Read and Write Excel Files in Java

Previously, I talked about how to check PageRank in Java. In this article, I will combine PageRank with Excel files. To operate Excel files in Java, I used Apache POI – the Java API for Microsoft Documents. Apparently, you can download the API package, and follow the relevant tutorials to learn how to use POI. When I first downloaded the Java library, I was astonished by how many jar files there were. I had no idea which ones would be useful for my project. Fortunately, I managed to filter out what I want.

You can save your time to just load the libraries as follows:

  • poi-3.10-FINAL-20140208.jar
  • poi-ooxml-3.10-FINAL-20140208.jar
  • poi-ooxml-schemas-3.10-FINAL-20140208.jar
  • dom4j-1.6.1.jar
  • xmlbeans-2.3.0.jar

My project configuration is shown in figure 1.

eclipse configuration

Figure 1

In the following source code, I hard-coded the input data in the first column and the output data in the second column.

Read Excel file in Java

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.Iterator;

import main.java.google.pagerank.PageRank;

import org.apache.poi.ss.usermodel.Row;
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;

import com.common.Utils;

public class ExcelOperator extends Operator {

	public ExcelOperator(String fileName) {
		super(fileName);
	}

	@Override
	public void getPageRank() {
		// TODO Auto-generated method stub		
		try {
                        // read Excel file in Java
			InputStream excelFile = new FileInputStream(mFileName); 
			XSSFWorkbook wb = new XSSFWorkbook(excelFile);
			XSSFSheet sheet = wb.getSheetAt(0);
			XSSFRow row;
			XSSFCell cell;

			Iterator<Row> rows = sheet.rowIterator();

			int col = 0, colPR = 1;
			int pageRank = 0;
			String url = null;

			while (rows.hasNext()) {
				row = (XSSFRow) rows.next();
				url = row.getCell(col).getStringCellValue();
				if (url.matches(Utils.REGEX)) { // check whether URL is valid
					System.out.println(url);
					pageRank = PageRank.get(url); // check page rank

					// write PageRank to excel
					cell = row.createCell(colPR);
					cell.setCellValue(pageRank);

					System.out.println("PR = " + pageRank);
				}
				else {
					System.out.println("URL not valid");
				}

				System.out.println("--------------------------");
			}

			FileOutputStream out = new FileOutputStream(mFileName);
	        wb.write(out);
	        out.flush();
	        out.close();
		}
		catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

Write Excel file in Java

public void saveToExcelFile(HistoryData data) {
		try {
                        // write data to Excel file
			File file = new File(mFile); 
			XSSFWorkbook wb;
			XSSFSheet sheet;
			XSSFRow row;
			XSSFCell cell;
			int rowIndex = 0;

			if (file.exists()) {
				InputStream excelFile = new FileInputStream(file);
				wb = new XSSFWorkbook(excelFile);
				sheet = wb.getSheetAt(0);
				rowIndex = sheet.getLastRowNum();
				rowIndex += 1;

				row = sheet.createRow(rowIndex);
			}
			else {
				wb = new XSSFWorkbook();
				sheet = wb.createSheet();
				row = sheet.createRow(0);
				cell = row.createCell(0);
				cell.setCellValue("URL");
				cell = row.createCell(1);
				cell.setCellValue("PageRank");
				cell = row.createCell(2);
				cell.setCellValue("AlexaRank");

				row = sheet.createRow(1);
			}

			CellStyle cs = wb.createCellStyle();
			cs.setFillForegroundColor(HSSFColor.GREEN.index);
			cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			cell = row.createCell(0);
			cell.setCellValue(data.getURL());
			cell = row.createCell(1);
			if (data.getPageRank() >= 5)
				cell.setCellStyle(cs);
			cell.setCellValue(data.getPageRank());
			cell = row.createCell(2);
			cell.setCellValue(data.getAlexaRank());

			FileOutputStream out = new FileOutputStream(mFile);
	        wb.write(out);
	        out.flush();
	        out.close();
		}
		catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

Source Code

https://github.com/yushulx/RW-Excel-in-Java