Apache POI解析excel文件
import org.apache.poi.POIXMLDocument;import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Created by Donge on 2017/1/3.
*/
public>
static private Workbook wb;
static private Sheet sheet;
static private Row row;
/**
* 读取 Excel 标题
* @param fileName
* @return
*/
public static String[] readExcelTitle(String fileName) {
try {
wb = createWorkbook(new FileInputStream(fileName));
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);// 获取第一行（约定第一行是标题行）
int colNum = row.getLastCellNum();// 获取行的列数
String[]>
for (int i = 0; i <>
>
}
return>
}
/**
* 读取 Excel 内容
* @param fileName
* @return
*/
public static List<Map<String, String>> readExcelContent(String fileName) {
List<Map<String, String>> list = new ArrayList<>();
Map<String, String> content;
try {
wb = createWorkbook(new FileInputStream(fileName));
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum()+1;// 得到总行数
row = sheet.getRow(0);
int colNum = row.getLastCellNum();// 得到总列数
String>
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i < rowNum; i++) {
int j = 0;
row = sheet.getRow(i);
content = new LinkedHashMap<>();
do {
content.put(titles, getCellFormatValue(row.getCell(j)).trim());
j++;
} while (j < colNum);
list.add(content);
}
return list;
}
/**
* 根据Cell类型设置数据
* @param cell
* @return
*/
private static String getCellFormatValue(Cell cell) {
String cellValue = " ";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case Cell.CELL_TYPE_NUMERIC:
case Cell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
cellValue = new SimpleDateFormat("yyyy-MM-dd").format(date);// 时间格式化显示：2012-12-31
} else {
// 如果是纯数字取得当前Cell的数值
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case Cell.CELL_TYPE_STRING:
cellValue = cell.getRichStringCellValue().getString();
break;
default:
// 默认的Cell值
cellValue = " ";
}
}
return cellValue;
}
/**
* 创建 Workbook
* @param is
* @return
* @throws IOException
* @throws InvalidFormatException
*/
public static Workbook createWorkbook(InputStream is) throws IOException,InvalidFormatException {
if (!is.markSupported()) {
is = new PushbackInputStream(is, 8);
}
if (POIFSFileSystem.hasPOIFSHeader(is)) {
return new HSSFWorkbook(is);
}
if (POIXMLDocument.hasOOXMLHeader(is)) {
return new XSSFWorkbook(OPCPackage.open(is));
}
throw new IllegalArgumentException("POI解析不了您当前的Excel版本");
}
/**
* 测试
* @param args
*/
public static void main(String args[]) {
String filePath = "D:\\Test.xls";
List<Map<String, String>> list = readExcelContent(filePath);
Map<String, String> map;
for (int i = 0; i < list.size(); i++) {
map = list.get(i);
System.out.println("**************THE START OF ROW("+(i+1)+")**************");
for (String key : map.keySet()) {
System.out.println(key + " : " + map.get(key));
}
}
}
}
页:
[1]