1.采用的解析库是POI,下载地址http://poi.apache.org/
2.创建工程并引入POI的jar包
3.官网上有开发指南,可以对照开发指南进行开发
以下是我处理实际问题的代码:
package parseExcel;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Scanner;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
/**
* 利用Java从班级通联电子表中剔除部分指定同学之后生成新的Excel表格
* @author matrix
* 2016年10月9日
*/
public class Demo {
public static void main(String[] args) throws IOException {
//加载Excel文件,并获取sheet对象
HSSFWorkbook inFile = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream("2016通联.xls")));
Sheet sheetOfInFile = inFile.getSheetAt(0);
//输创建输出Excel
HSSFWorkbook outFile = new HSSFWorkbook();
Sheet sheetOfOutFile = outFile.createSheet();
//从文件中获取名单列表
ArrayList<String> nameList = new NameList(new FileInputStream("nameList.txt")).getNameList();
//遍历单元格
//注意行标和列标起止大小
for(int inRowIndex = 0, outRowIndex = 0;
inRowIndex <= sheetOfInFile.getLastRowNum(); inRowIndex++){
Row inRow = sheetOfInFile.getRow(inRowIndex);
if(nameList.indexOf(inRow.getCell(1).toString().trim()) < 0){
System.out.println(inRow.getCell(1).toString().trim());
Row outRow = sheetOfOutFile.createRow(outRowIndex);
for(int inCellIndex = 0; inCellIndex < inRow.getLastCellNum(); inCellIndex++){
Cell outCell = outRow.createCell(inCellIndex);
outCell.setCellValue(inRow.getCell(inCellIndex).toString().trim());
}
outRowIndex++;
}
}
//将修改写入新的Excel文件
outFile.write(new FileOutputStream("实训.xls"));
//关闭流
outFile.close();
inFile.close();
}
}
/**
* 从文件中加载名单
* @author matrix
* 2016年10月9日
*/
class NameList{
private ArrayList<String> nameList;
public NameList(FileInputStream finp) {
nameList = parse(finp);
}
private ArrayList<String> parse(FileInputStream finp){
nameList = new ArrayList<String>();
Scanner scan = new Scanner(finp);
while(scan.hasNext()){
nameList.add(scan.nextLine().trim());
}
scan.close();
return nameList;
}
public ArrayList<String> getNameList(){
return nameList;
}
}