本例用到以下Class,完成对Excel的基本读写。我用的Excel是2003
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
package net.tianyu.study.poi;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
public class DiffXls {
private static final String KEY_MARK = "key";
private static final String END_MARK = "end";
private static final String UPDATE_DATA_MARK = "Updated";
private static final int MAX_LINES = 3000;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private List<Integer> keyColumns = new ArrayList<Integer>();
private List<KeyData> orgKeyList = new ArrayList<KeyData>();
private int dataStartRow;
private int dataEndColumn;
private FileOutputStream out = null;
private FileInputStream in = null;
private short updateColor = IndexedColors.LIGHT_YELLOW.getIndex();
private short insertColor = IndexedColors.LIGHT_GREEN.getIndex();
private short deleteColor = IndexedColors.GREY_25_PERCENT.getIndex();
public void open(String inputFileName, String outputFileName) throws IOException {
in = new FileInputStream(inputFileName);
out = new FileOutputStream(outputFileName);
POIFSFileSystem filein = new POIFSFileSystem(in);
wb = new HSSFWorkbook(filein);
sheet = wb.getSheetAt(0);
}
public void run() throws IOException {
initKeyColumn();
initOrgKeyList();
initDataStartRow();
startDiff();
format();
wb.write(out);
}
public void close() throws IOException {
out.close();
in.close();
}
private void initKeyColumn() {
HSSFRow markRow = sheet.getRow(0);
for (int i = 0; i < MAX_LINES; i++) {
HSSFCell cell = markRow.getCell(i);
if (cell != null && KEY_MARK.equals(cell.getStringCellValue())) {
keyColumns.add(i);
System.out.println(i);
}
if (cell != null && END_MARK.equals(cell.getStringCellValue())) {
dataEndColumn = i;
System.out.println("dataEndColumn :" + dataEndColumn);
break;
}
}
}
private void initOrgKeyList() {
for (int i = 2; i < MAX_LINES; i++) {
HSSFRow dataRow = sheet.getRow(i);
if (dataRow == null) {
break;
}
KeyData key = new KeyData();
key.setRow(i);
for (Integer keyColumn : keyColumns) {
HSSFCell cell = dataRow.getCell(keyColumn);
if (cell != null) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
key.addValue(cell.getStringCellValue());
}
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
key.addValue(Double.toString(cell.getNumericCellValue()));
}
}
}
System.out.println(key.toString());
orgKeyList.add(key);
}
}
private void format() {
for (int i = 0; i < dataEndColumn; i++) {
sheet.autoSizeColumn(i);
int width = sheet.getColumnWidth(i);
if (width > 256 * 100) {
sheet.setColumnWidth(i, 256 * 100);
}
}
}
private void initDataStartRow() {
for (int i = 1; i < MAX_LINES; i++) {
HSSFRow dataRow = sheet.getRow(i);
if (dataRow == null) {
continue;
}
HSSFCell cell = dataRow.getCell(0);
if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING
&& UPDATE_DATA_MARK.equals(cell.getStringCellValue())) {
dataStartRow = i + 2;
break;
}
if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING
&& END_MARK.equals(cell.getStringCellValue())) {
break;
}
}
System.out.println(dataStartRow);
}
private int matchKeyRow(KeyData key) {
for (KeyData orgKey : orgKeyList) {
if (orgKey.equals(key)) {
orgKey.setHasSameData(true);
return orgKey.getRow();
}
}
return 0;
}
private boolean isSameData(int orgRow, int targetRow) {
boolean result = true;
for (int i = 0; i < dataEndColumn; i++) {
HSSFRow orgData = sheet.getRow(orgRow);
HSSFRow targetData = sheet.getRow(targetRow);
if (orgData == null || targetData == null) {
result = false;
}
HSSFCell orgCell = orgData.getCell(i);
HSSFCell targetCell = targetData.getCell(i);
if (orgCell == null && targetCell == null) {
continue;
}
if (orgCell == null && targetCell != null) {
setCellColor(orgCell, updateColor);
setCellColor(targetCell, updateColor);
result = false;
}
if (targetCell == null && orgCell != null) {
setCellColor(orgCell, updateColor);
setCellColor(targetCell, updateColor);
result = false;
}
if (orgCell.getCellType() != targetCell.getCellType()) {
setCellColor(orgCell, updateColor);
setCellColor(targetCell, updateColor);
result = false;
}
if (orgCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
if (!orgCell.getStringCellValue().equals(targetCell.getStringCellValue())) {
setCellColor(orgCell, updateColor);
setCellColor(targetCell, updateColor);
result = false;
}
}
if (orgCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
if (orgCell.getNumericCellValue() != targetCell.getNumericCellValue()) {
setCellColor(orgCell, updateColor);
setCellColor(targetCell, updateColor);
result = false;
}
}
}
return result;
}
private void startDiff() {
for (int i = dataStartRow; i < MAX_LINES; i++) {
HSSFRow dataRow = sheet.getRow(i);
if (dataRow == null) {
break;
}
HSSFCell cell = dataRow.getCell(0);
if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING
&& END_MARK.equals(cell.getStringCellValue())) {
break;
}
KeyData key = new KeyData();
key.setRow(i);
for (Integer keyColumn : keyColumns) {
cell = dataRow.getCell(keyColumn);
if (cell != null) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
key.addValue(cell.getStringCellValue());
}
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
key.addValue(Double.toString(cell.getNumericCellValue()));
}
}
}
int orgRow = matchKeyRow(key);
if (orgRow != 0) {
System.out.println("updateRow : " + i + " orgRow : " + orgRow + " " + isSameData(i, orgRow));
}
else {
setRowColor(i, insertColor);
}
}
setDeleteColor();
}
private void setDeleteColor() {
for (KeyData orgKey : orgKeyList) {
if (!orgKey.isHasSameData()) {
setRowColor(orgKey.getRow(), deleteColor);
}
}
}
private void setRowColor(int row, short color) {
HSSFRow dataRow = sheet.getRow(row);
for (int i = 0; i < dataEndColumn; i++) {
HSSFCell cell = dataRow.getCell(i);
if (cell == null) {
cell = dataRow.createCell(i);
}
setCellColor(cell, color);
}
}
private void setCellColor(HSSFCell cell, short color) {
CellStyle style = wb.createCellStyle();
style.cloneStyleFrom(cell.getCellStyle());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(color);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
cell.setCellStyle(style);
}
}