How to Read and Write Excel Files in Java
Mar 12, 2014
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.
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();
}
}