poi读写Excel

Poi可操作Excel2003 xls、Excel2007 xlsx。

下载地址：http://poi.apache.org/download.html

导入下载后的相关包到工程。

poi读写示例代码：

 

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.pm.commons.FinalData;
import com.pm.commons.Format;
import com.pm.report.bean.LiquidationBean;

import de.schlichtherle.io.FileInputStream;
import de.schlichtherle.io.FileOutputStream;

/**
 * Excel Poi工具类
 * 
 * @author dwen 2014-8-18
 */
public class ExcelPoiUtils {

	private static Logger logger = LoggerFactory.getLogger(ExcelPoiUtils.class);
public static final String[] LIQUIDATION_COLUMN_NAME = new String[]{
        "标题",
        "标题",
        "标题",
        "标题",
        "标题",
        "标题",
        "标题",
        "标题",
        "标题",
        "标题",
        "标题"
    };
	/** 列名 */
	private static void initKuaiQianLabel(XSSFSheet sheet) {
		// 标题行
		XSSFRow row = sheet.createRow(0);
		for (int x = 0; x < LIQUIDATION_COLUMN_NAME.length; x++) {
			XSSFCell cell = row.createCell(x);
			cell.setCellType(XSSFCell.CELL_TYPE_STRING);// 文本格式
			cell.setCellValue(LIQUIDATION_COLUMN_NAME[x]);// 写入内容
		}
	}

	/**
	 * 读xlsx
	 * 
	 * @param filePath
	 * @return
	 */
	public static List<LiquidationBean> readKuaiQianXlsx(String filePath,int s) {
		if (!filePath.endsWith("xlsx")) {
			return null;
		}
		List<LiquidationBean> list = new ArrayList<LiquidationBean>();
		try {
			InputStream input = new FileInputStream(filePath);
			XSSFWorkbook wb = new XSSFWorkbook(input);
			XSSFSheet sheet = wb.getSheetAt(s);// 获得第一个表单
			for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
				LiquidationBean bean = new LiquidationBean();
				XSSFRow row = sheet.getRow(i);
				String cell = row.getCell(6).toString();// 交易金额
				bean.setAmountStr(cell);
				String cell_2 = row.getCell(14).toString();// 外部跟踪编号
				bean.setLocallogno(cell_2.substring(cell_2.length() - 6,
						cell_2.length()));
				list.add(bean);
			}
		} catch (FileNotFoundException e) {
			logger.error(e.getMessage());
		} catch (IOException e) {
			logger.error(e.getMessage());
		}
		return list;
	}

	/**
	 * 写xlsx
	 * @param filePath
	 * @param czFailureList
	 * @param czSuccessList
	 * @param czList
	 * @param sList
	 * @param fList
	 */
	public static boolean writeKuaiQianXlsx(String filePath,List<LiquidationBean> czFailureList,
			List<LiquidationBean> czSuccessList, List<LiquidationBean> czList,
			List<LiquidationBean> sList, List<LiquidationBean> fList) {
		try {
			FileOutputStream output = new FileOutputStream(new File(filePath)); // 读取的文件路径
			XSSFWorkbook wb = new XSSFWorkbook();
			XSSFSheet sheet = wb.createSheet("测试1");
			// 始化报表标题Label
			initKuaiQianLabel(sheet);
			// 写数据到excel单元格中
			addCell(sheet, czFailureList);
			
			XSSFSheet sheet2 = wb.createSheet("测试2");
			// 始化报表标题Label
			initKuaiQianLabel(sheet2);
			// 写数据到excel单元格中
			addCell(sheet2, czSuccessList);
			
			XSSFSheet sheet3 = wb.createSheet("测试3");
			// 始化报表标题Label
			initKuaiQianLabel(sheet3);
			// 写数据到excel单元格中
			addCell(sheet3, czList);
			
			XSSFSheet sheet4 = wb.createSheet("测试4");
			// 始化报表标题Label
			initKuaiQianLabel(sheet4);
			// 写数据到excel单元格中
			addCell(sheet4, sList);
			
			XSSFSheet sheet5 = wb.createSheet("测试5");
			// 始化报表标题Label
			initKuaiQianLabel(sheet5);
			// 写数据到excel单元格中
			addCell(sheet5, fList);
			
			wb.write(output);
			output.close();
		} catch (FileNotFoundException e) {
			logger.error(e.getMessage());
			return false;
		} catch (IOException e) {
			logger.error(e.getMessage());
			return false;
		}
		return true;
	}

	/**
	 * 添加单元格内容
	 * 
	 * @param sheet
	 * @param beans
	 */
	private static void addCell(XSSFSheet sheet, List<LiquidationBean> beans) {
		for (int i = 0; i < beans.size(); i++) {
			XSSFRow row2 = sheet.createRow(i + 1);
			LiquidationBean bean = beans.get(i);
			// 服务代码(服务名称)
			XSSFCell cell_0 = row2.createCell(0);
			cell_0.setCellValue(bean.getServcode());

			XSSFCell cell_1 = row2.createCell(1);
			cell_1.setCellValue(bean.getTradecode());

			XSSFCell cell_2 = row2.createCell(2);
			cell_2.setCellValue(bean.getLocaldate());

			XSSFCell cell_3 = row2.createCell(3);
			cell_3.setCellValue(bean.getLocallogno());

			XSSFCell cell_4 = row2.createCell(4);
			cell_4.setCellValue(bean.getCustomerid());

			XSSFCell cell_5 = row2.createCell(5);
			cell_5.setCellValue(bean.getMobileNo());

			XSSFCell cell_6 = row2.createCell(6);
			cell_6.setCellValue(bean.getAccount());

			XSSFCell cell_7 = row2.createCell(7);
			cell_7.setCellValue(bean.getAmountStr());

			XSSFCell cell_8 = row2.createCell(8);
			cell_8.setCellValue(bean.getStatus());
			XSSFCell cell_9 = row2.createCell(9);
			cell_9.setCellValue(bean.getPaytag());
			XSSFCell cell_10 = row2.createCell(10));
			cell_10.setCellValue(Format.fty(String.valueOf(bean.getFee())));
		}
	}

}