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();
	}
}