Apache POI工具对excel文件操作的简单使用

今天帮一个同学，处理txt文件数据，最后把数据持久化到excel表格中，其中用到了开源工具包POI，稍微学习了下，mark。。。

1、首先是一个创建xls文件的demo

package test;

import java.io.FileOutputStream;
import java.io.IOException;

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;

public class CreateXL {

public static void main(String argv[]) throws IOException {
String outputFile = "E:/hgl/test/test.xls";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet1 = workbook.createSheet("sheet1");
System.out.println(sheet1.getLastRowNum());
HSSFRow row = sheet1.createRow(0);
HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue("增加值");

HSSFSheet sheet2 = workbook.createSheet("sheet2");
sheet2.createRow(0).createCell(0).setCellValue("bababab");

FileOutputStream fOut = new FileOutputStream(outputFile);
workbook.write(fOut);
fOut.flush();
fOut.close();
workbook.close();
}
}

2、这个是根据自己需求的数据处理demo。（获取文件夹下每个文件的若干特定行（根据第一列确定）的若干特定列（自己制定）的数据）

package test;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileFilter;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.util.Arrays;

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;

public class XlsUtil {

public static void main(String[] args) throws IOException {
int count = 0;
// 文件输出流
FileOutputStream fOut = new FileOutputStream("e:/hgl/test/result.xls");
// 工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 遍历文件夹中的文件
File fileDir = new File("e:/hgl/test/");
File[] files = fileDir.listFiles(new FileFilter() {
@Override
public boolean accept(File pathname) {
return pathname.getAbsolutePath().endsWith(".TXT");
}
});
// 按名称排序
Arrays.sort(files);
for (File file : files) {
System.out.println("开始处理第" + (++count) + "个文件：" + file.getName());
BufferedReader br = new BufferedReader(new FileReader(file));
HSSFSheet sheet = null;
HSSFRow row = null;
String[] arrStr = null;
String line = null;
while ((line = br.readLine()) != null) {
arrStr = line.split("\\s+");
String sheetName = arrStr[0];
if (sheetName.equals("54916") || sheetName.equals("57083") || sheetName.equals("57290")
|| sheetName.equals("57297") || sheetName.equals("58027") || sheetName.equals("58040")
|| sheetName.equals("58102") || sheetName.equals("58221") || sheetName.equals("58251")
|| sheetName.equals("58314")) {
// 新建或获取相应工作表sheet
if (workbook.getSheet(sheetName) == null) {
sheet = workbook.createSheet(sheetName);
} else {
sheet = workbook.getSheet(sheetName);
}
// 设置行数据
row = sheet.createRow(sheet.getLastRowNum() + 1);
row.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue(arrStr[4]);
row.createCell(1, HSSFCell.CELL_TYPE_STRING).setCellValue(arrStr[5]);
row.createCell(2, HSSFCell.CELL_TYPE_STRING).setCellValue(arrStr[6]);
row.createCell(3, HSSFCell.CELL_TYPE_STRING).setCellValue(arrStr[9]);
}
}
br.close();
}
workbook.write(fOut);
fOut.flush();
fOut.close();
workbook.close();
}
}