Apache POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目．
下载地址：http://poi.apache.org/download.html
解压包把所有相关jar拷到项目lib下面．
操作excel表的方法如下：
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author zhanjun
* @date 2010-9-25
* @version 1.0
* @description POI excel操作工具类
*
*/
public class POIExcelUtil {
public static void main(String[] args) throws IOException {
//List<Map<String, String>> listmap =ExcelUtil.readExcelFile("D:\\OrderFinish99.xls", 0);
List<Map<String, String>> listmap = POIExcelUtil.readExcelFile("C:\\bi\\test.xls", 0);
// List<Map<String, String>> listmap = ExcelUtil.readExcelFile("D:\\OrderFinish88.xlsx", 0);
// List<Map<String, String>> listmap = ExcelUtil.readExcel("D:\\88.xls", 0);
for (Map<String, String> map : listmap) {
System.out.println(map.get("0") + "- " + map.get("1") + " - " + map.get("2"));
}
List<Map<String, String>> listmap1 = new ArrayList();
Map mp1 = new HashMap();
mp1.put("0", "今年");
mp1.put("1", "18");
Map mp11 = new HashMap();
mp11.put("0", "明22年");
mp11.put("1", "1911");
listmap1.add(mp1);
listmap1.add(mp11);
String[] header = new String[] { "姓名", "年龄" };
String[] header2 = new String[] { "姓名2", "年龄3" };
Workbook wb = writeExcelFile(null, "sheet1", "d:\\ds.xls", header, listmap1);
wb = writeExcelFile(wb, "sheet2", "d:\\ds.xls", header2, listmap1);//2003支持多sheet的建立
Workbook wb2 = writeExcelFile(null, "sheet1", "D:/ds1.xlsx", header, listmap1); // wb2 =
wb2=writeExcelFile(wb2, "sheet1", "D:/ds1.xlsx", header2, listmap1);//2007不支持多sheet的建立
}
/**
* 获取sheet表中的数据
* @param sheet
* @return map 格式以0.1.2....列标做为key
*/
private static List<Map<String, String>> getData(Sheet sheet) {
List<Map<String, String>> ls = new ArrayList<Map<String, String>>();
/*
* Pattern pattern = Pattern.compile("([\\d]+)"); Matcher matcher;
*/
Iterator<Row> rit = sheet.iterator();
if (rit.hasNext()) {
rit.next();//过滤第一行
}
Map<String, String> lineMap = null;
while (rit.hasNext()) {
Row row = rit.next();
lineMap = new ConcurrentHashMap<String, String>(); // 用于接收每列的数据。
for (int i = 0; i <= row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
String k = ""; // 用于接收每个单元格的数据。
if (cell == null) {
lineMap.put(String.valueOf((i)), k); // 赋值。
continue;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
k = "";
break;
case Cell.CELL_TYPE_ERROR:
k = Byte.toString(cell.getErrorCellValue());
break;
case Cell.CELL_TYPE_STRING:
k = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
k = sdf.format(cell.getDateCellValue());
} else {
k = Integer.toString((int) cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
k = Boolean.toString(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
k = cell.getCellFormula();
break;
default:
k = "";
}
if ((k != null) && (!"".equals(k))) {
lineMap.put(String.valueOf((i)), k); // 赋值。
} else {
lineMap.put(String.valueOf((i)), ""); // 赋值。
}
}
if (!lineMap.isEmpty()) { // 判断是不是为空
ls.add(lineMap);
}
}
return ls;
}
/**
* 读取excel文件，可以是2003,2007
* @param filePath
* @param sheetNum
* @return
*/
public static List<Map<String, String>> readExcelFile(String filePath, int sheetNum) {
InputStream ins = null;
Workbook book = null;
List<Map<String, String>> list = null;
try {
ins = new FileInputStream(filePath);
book = WorkbookFactory.create(ins);
list = getData(book.getSheetAt(sheetNum));
ins.close();
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (ins != null) {
try {
ins.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return list;
}
/**
*
* 写excel 文件
*
* @param filePath
* 文件路径
*
* @param sheetNum
* 活动的sheet编号，编号从0开始
*
* @return
*/
public static Workbook writeExcelFile(Workbook wb, String sheetName, String filePath, String[] header,
List<Map<String, String>> sdata) {
try {
if (filePath.endsWith("xls")) {
if (wb == null) {
wb = new HSSFWorkbook();
}
} else if (filePath.endsWith("xlsx")) {
if (wb == null) {
wb = new XSSFWorkbook();
}
}
Sheet sheet = wb.createSheet(sheetName);
Row row = sheet.createRow(0);
for (short i = 0; i < header.length; i++) {
Cell cell = row.createCell(i);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(header[i]);
}
for (int j = 0; j < sdata.size(); j++) {
Row rows = sheet.createRow(j + 1);
Map mp = sdata.get(j);
for (short index = 0; index < mp.size(); index++) {
Cell cell = rows.createCell(index);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(mp.get(index + "").toString());
}
}
OutputStream fileOut = new FileOutputStream(filePath);
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return wb;
}
/**
*
* 写excel 2007 文件
*
* @param filePath
* 文件路径
*
* @param sheetNum
* 活动的sheet编号，编号从0开始
*
* @return
*/
public static XSSFWorkbook writeXSSFWorkbook(XSSFWorkbook wb, String sheetName, String[] header,
List<Map<String, String>> sdata) {
try {
if (wb == null) {
wb = new XSSFWorkbook();
}
// CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet(sheetName);
Row row = sheet.createRow(0);
for (short i = 0; i < header.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(header[i]);
}
for (int j = 0; j < sdata.size(); j++) {
Row rows = sheet.createRow(j + 1);
Map mp = sdata.get(j);
for (short index = 0; index < mp.size(); index++) {
Cell cell = rows.createCell(index);
if (mp.get(index + "") != null)
cell.setCellValue(mp.get(index + "").toString());
else
cell.setCellValue("");
}
}
/*
* FileOutputStream fileOut = new FileOutputStream(filePath);
* wb.write(fileOut); fileOut.close();
*/
} catch (Exception e) {
e.printStackTrace();
}
return wb;
}
/**
*
* 写excel 97-2003
*
* @param filePath
* 文件路径
*
* @param sheetNum
* 活动的sheet编号，编号从0开始
*
* @return
*/
public static HSSFWorkbook writeHSSFWorkbook(HSSFWorkbook wb, String sheetName, String[] header,
List<Map<String, String>> sdata) {
try {
if (wb == null) {
wb = new HSSFWorkbook();
}
//CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet(sheetName);
Row row = sheet.createRow(0);
for (short i = 0; i < header.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(header[i]);
}
for (int j = 0; j < sdata.size(); j++) {
Row rows = sheet.createRow(j + 1);
Map mp = sdata.get(j);
for (short index = 0; index < mp.size(); index++) {
Cell cell = rows.createCell(index);
if (mp.get(index + "") != null)
cell.setCellValue(mp.get(index + "").toString());
else
cell.setCellValue("");
}
}
/*
* FileOutputStream fileOut = new FileOutputStream(filePath);
* wb.write(fileOut); fileOut.close();
*/
} catch (Exception e) {
e.printStackTrace();
}
return wb;
}
}
分享到：
相关推荐
利用poi3.6 解析excel2003文件的程序代码.
收集了poi3.6跟poi3.7解析excel2007用到的包
poi3.6 兼容office 2003,2007
poi3.6官方帮助文档 poi3.6官方帮助文档 poi3.6官方帮助文档
excel poi3.6 excel poi3.6 excel poi3.6
poi读写excel，poi总结； poi读写excel，poi总结 poi读写excel，poi总结
poi读写excelpoi读写excelpoi读写excelpoi读写excelpoi读写excelpoi读写excelpoi读写excelpoi读写excelpoi读写excel
poi-3.6-20091214.jar
poi 3.6 zuixinde API chm格式
poi3.9读写EXCEL 支持2007
网上找了很久 poi3.6 excel2007操作 使用event api的例子
Java 操作Excel poi读写excel，所需要的jar包 https://blog.csdn.net/u014646662/article/details/83217382
POI工具包可用来读写Excel，在java开发中可用来创建、导出信息到excel表格中，还可读取excel中的信息使用到应用程序中。
apache POI,文件读写 ,excel 对于使用apache poi 解析微软excel的一些文件
POI-3.6 poi解析Excel(2003\2010)的jar集合,支持office2003\2010
使用poi读写微软的excel文件和WPS的et文件，poi3.1读写报表文件,此包是代码需要下载poi3.1jar
poi3.9读写excel完美修订版本 java源码 兼容03和07 可运行 带详细注释 GX了
poi-3.6-20091214.jar poi-contrib-3.6-20091214.jar poi-examples-3.6-20091214.jar poi-ooxml-3.6-20091214.jar poi-ooxml-schemas-3.6-20091214.jar poi-scratchpad-3.6-20091214.jar xmlbeans-2.3.0.jar
java 使用POI框架读写excel doc
poi excel poi excel poi excel poi excel poi excel poi excel poi excel poi excel