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