读取excel 工具类
来源:互联网 发布:网络监控服务器 编辑:程序博客网 时间:2024/06/13 22:32
package com.shs.framework.web.utls;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
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;
/**
* <P>Description: 读取excel</P>
* @ClassName: ExcelReader
* @author wangmingyu 2015-4-22 下午05:05:28
* @see poi-3.9 jar
*/
public class ExcelReader {
/**
* @Fields filePath :文件路径
*/
private String filePath;
/**
* @Fields sheetName : sheet名
*/
private String sheetName;
/**
* @Fields workBook :内容对象
*/
private Workbook workBook;
private Sheet sheet;
private List<String> columnHeaderList;
private List<List<String>> listData;
private List<Map<String, String>> mapData;
private boolean flag;
public ExcelReader(String filePath, String sheetName) {
this.filePath = filePath;
this.sheetName = sheetName;
this.flag = false;
this.load();
}
private void load() {
FileInputStream inStream = null;
try {
inStream = new FileInputStream(new File(filePath));
workBook = WorkbookFactory.create(inStream);
sheet = workBook.getSheet(sheetName);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (inStream != null) {
inStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* <p>Title: getCellValue</p>
* <p>Description:获取行内容</p>
* @param cell
* @return
* @author wangmingyu 2015-4-22 下午05:06:27
*/
private String getCellValue(Cell cell) {
String cellValue = "";
DataFormatter formatter = new DataFormatter();
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = formatter.formatCellValue(cell);
} else {
double value = cell.getNumericCellValue();
int intValue = (int) value;
cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "";
break;
default:
cellValue = cell.toString().trim();
break;
}
}
return cellValue.trim();
}
private void getSheetData() {
listData = new ArrayList<List<String>>();
mapData = new ArrayList<Map<String, String>>();
columnHeaderList = new ArrayList<String>();
int numOfRows = sheet.getLastRowNum() + 1;
for (int i = 0; i < numOfRows; i++) {
Row row = sheet.getRow(i);
Map<String, String> map = new HashMap<String, String>();
List<String> list = new ArrayList<String>();
if (row != null) {
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if (i == 0) {
columnHeaderList.add(getCellValue(cell));
} else {
map.put(columnHeaderList.get(j), this.getCellValue(cell));
}
list.add(this.getCellValue(cell));
}
}
if (i > 0) {
mapData.add(map);
}
listData.add(list);
}
flag = true;
}
/**
* <p>Title: getCellData</p>
* <p>Description: 根据 行下标、列下标 获取 内容</p>
* @param row
* @param col
* @return
* @author wangmingyu 2015-4-22 下午05:07:08
*/
public String getCellData(int row, int col) {
if (row <= 0 || col <= 0) {
return null;
}
if (!flag) {
this.getSheetData();
}
if (listData.size() >= row && listData.get(row - 1).size() >= col) {
return listData.get(row - 1).get(col - 1);
} else {
return null;
}
}
public String getCellData(int row, String headerName) {
if (row <= 0) {
return null;
}
if (!flag) {
this.getSheetData();
}
if (mapData.size() >= row && mapData.get(row - 1).containsKey(headerName)) {
return mapData.get(row - 1).get(headerName);
} else {
return null;
}
}
}
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
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;
/**
* <P>Description: 读取excel</P>
* @ClassName: ExcelReader
* @author wangmingyu 2015-4-22 下午05:05:28
* @see poi-3.9 jar
*/
public class ExcelReader {
/**
* @Fields filePath :文件路径
*/
private String filePath;
/**
* @Fields sheetName : sheet名
*/
private String sheetName;
/**
* @Fields workBook :内容对象
*/
private Workbook workBook;
private Sheet sheet;
private List<String> columnHeaderList;
private List<List<String>> listData;
private List<Map<String, String>> mapData;
private boolean flag;
public ExcelReader(String filePath, String sheetName) {
this.filePath = filePath;
this.sheetName = sheetName;
this.flag = false;
this.load();
}
private void load() {
FileInputStream inStream = null;
try {
inStream = new FileInputStream(new File(filePath));
workBook = WorkbookFactory.create(inStream);
sheet = workBook.getSheet(sheetName);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (inStream != null) {
inStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* <p>Title: getCellValue</p>
* <p>Description:获取行内容</p>
* @param cell
* @return
* @author wangmingyu 2015-4-22 下午05:06:27
*/
private String getCellValue(Cell cell) {
String cellValue = "";
DataFormatter formatter = new DataFormatter();
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = formatter.formatCellValue(cell);
} else {
double value = cell.getNumericCellValue();
int intValue = (int) value;
cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "";
break;
default:
cellValue = cell.toString().trim();
break;
}
}
return cellValue.trim();
}
private void getSheetData() {
listData = new ArrayList<List<String>>();
mapData = new ArrayList<Map<String, String>>();
columnHeaderList = new ArrayList<String>();
int numOfRows = sheet.getLastRowNum() + 1;
for (int i = 0; i < numOfRows; i++) {
Row row = sheet.getRow(i);
Map<String, String> map = new HashMap<String, String>();
List<String> list = new ArrayList<String>();
if (row != null) {
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if (i == 0) {
columnHeaderList.add(getCellValue(cell));
} else {
map.put(columnHeaderList.get(j), this.getCellValue(cell));
}
list.add(this.getCellValue(cell));
}
}
if (i > 0) {
mapData.add(map);
}
listData.add(list);
}
flag = true;
}
/**
* <p>Title: getCellData</p>
* <p>Description: 根据 行下标、列下标 获取 内容</p>
* @param row
* @param col
* @return
* @author wangmingyu 2015-4-22 下午05:07:08
*/
public String getCellData(int row, int col) {
if (row <= 0 || col <= 0) {
return null;
}
if (!flag) {
this.getSheetData();
}
if (listData.size() >= row && listData.get(row - 1).size() >= col) {
return listData.get(row - 1).get(col - 1);
} else {
return null;
}
}
public String getCellData(int row, String headerName) {
if (row <= 0) {
return null;
}
if (!flag) {
this.getSheetData();
}
if (mapData.size() >= row && mapData.get(row - 1).containsKey(headerName)) {
return mapData.get(row - 1).get(headerName);
} else {
return null;
}
}
}
0 0
- 读取Excel工具类
- 读取excel 工具类
- 读取Excel工具类ExcelUtils
- java读取Excel工具类
- poi读取excel的工具类
- Java基于POI读取Excel工具类
- 读取Excel文档的内容工具类
- 自用Excel读取工具
- 读取excel工具类自适应两种excel版本
- 屏蔽字工具类 Java读取Excel工具类 读取到内存中
- 打造自己的读取Excel数据的工具类
- 打造自己的读取Excel数据的工具类
- Java 读取Excel格式xls、xlsx数据工具类
- Java 使用最新POI Lib 将Excel转换成Txt读取Excel内容工具类
- 封装poi读取excel的超强工具类,支持一行代码获取excel内容
- 读取Excel数据和写入txt文件以及读取配置文件工具类
- java 使用工具读取Excel文件
- Badboy自动化测试工具 读取Excel
- iOS 实现倒计时
- 从零开始nodejs系列文章
- perl 编程基础
- 关于右值引用的一个错误。
- 银联在线支付---利用测试案例代码模拟支付应用(修改)
- 读取excel 工具类
- Android f_rndis 分析笔记
- RFID系统二进制树型搜索算法是如何解决碰撞的?简述其实现步骤
- 散列(2)线性探测法和双重散列法
- Qt for Android - ANT_HOME is set incorrectly or ant could not be located
- Wireless Network(POJ-2236)(并查集)
- 第2章 8
- 树的遍历、平衡二叉树实现
- robotframework如何提取失败的测试,以便下次运行