apache POI 读取 Excel

xuxiaohui9216

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
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;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class ExcelReader {
private POIFSFileSystem fs;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private HSSFRow row;
private FileInputStream input;
private String[] excleTitle;
public String[] readExcelTitle(String excelPath) {// 读取Excel表格表头的内容
try {
input = new FileInputStream(new File(excelPath));// excelPath,Excel
// 文件 的绝对路径
fs = new POIFSFileSystem(input);
wb = new HSSFWorkbook(fs);
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);// 得到标题的内容对象。
int colNum = row.getPhysicalNumberOfCells(); // 得到标题总列数
excleTitle = new String[colNum];
for (int i = 0; i < colNum; i++) {
excleTitle = getStringCellValue(row.getCell((short) i));
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (input != null) {
input.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return excleTitle;
}
public Map<Integer, String> readExcelContent(String excelPath) {// 读取Excel数据内容
Map<Integer, String> content = new HashMap<Integer, String>();
String excelStr = "";// excel 内容
try {
input = new FileInputStream(new File(excelPath));
fs = new POIFSFileSystem(input);
wb = new HSSFWorkbook(fs);
sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum(); // 得到总行数
row = sheet.getRow(0);// 得到标题的内容对象。
int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。
for (int i = 1; i < rowNum; i++) { // 正文内容应该从第二行开始,第一行为表头的标题
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
excelStr += getStringCellValue(row.getCell((short) j)).trim()+ "&";
j++;
}
content.put(i, excelStr);
excelStr = "";
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (input != null) {
input.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return content;
}
private String getStringCellValue(HSSFCell cell) {// 获取单元格数据内容为字符串类型的数据
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
private String getDateCellValue(HSSFCell cell) {// 获取单元格数据内容为日期类型的数据
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
System.out.println("日期格式不正确!");
e.printStackTrace();
}
return result;
}
}