package com.zte.xh.fund.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
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.ss.usermodel.Cell;
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 com.mysql.jdbc.StringUtils;
/**
* 读取excell的工具类
*
* @author Jay_Lee
*
*/
public class ExcellUtil {
private static XSSFWorkbook xssfWorkbook;
private static HSSFWorkbook hssfWorkbook;
/**
* 向外提供的调用接口,根据后缀名判断不同的excell文件调用不同方法
*
* @param path
* @throws IOException
*/
public static List<Map<String, String>> startRead(String path)
throws IOException {
String fileType = path.substring(path.lastIndexOf(".") + 1,
path.length());
List<Map<String, String>> result = new ArrayList<Map<String, String>>();
if (fileType.equalsIgnoreCase("XLSX")) {
result = readXlsx(path, findXlsxStartEnd(path));
} else {
result = readXls(path, findXlsStartEnd(path));
}
return result;
}
/**
* 找到xls文件的excell中有效数据的起始位置
*
* @param path
* @throws IOException
*/
private static Map<String, Integer> findXlsStartEnd(String path)
throws IOException {
InputStream is = new FileInputStream(path);
hssfWorkbook = new HSSFWorkbook(is);
Map<String, Integer> resultNum = new HashMap<String, Integer>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
// 循环列Cell
for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
HSSFCell hssfCell = hssfRow.getCell(cellNum);
if (hssfCell == null) {
continue;
}
String value = getValueHssf(hssfCell).trim();
// 如果这里读取出了账号,那么记录它的cell在每行的位置,以后直接读取下一行的此处
if (value.equals("账号")) {
resultNum.put("startRow", rowNum + 1);
resultNum.put("startCell", cellNum);
} else if (value.equals("说明")) {
resultNum.put("endCell", cellNum);
return resultNum;
}
}
}
}
return null;
}
/**
* 读取xls的excell文件
*
* @param path
* @return
* @throws IOException
*/
private static List<Map<String, String>> readXls(String path, Map startMap)
throws IOException {
List<Map<String, String>> accounts = new ArrayList<Map<String, String>>();
Map<String, String> tempMap = null;
int startRow = (int) startMap.get("startRow");
int startCell = (int) startMap.get("startCell");
int endCell = (int) startMap.get("endCell");
InputStream is = new FileInputStream(path);
hssfWorkbook = new HSSFWorkbook(is);
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = startRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
tempMap = new HashMap<String, String>();
// 循环列Cell
for (int cellNum = startCell; cellNum <= endCell; cellNum++) {
HSSFCell hssfCell = hssfRow.getCell(cellNum);
String value = getValueHssf(hssfCell).trim();
tempMap.put(String.valueOf(cellNum),
StringUtils.isNullOrEmpty(value) ? "" : value);
}
accounts.add(tempMap);
}
}
return accounts;
}
/**
* 读取cell中的不同类型的数据
*
* @param hssfCell
* @return
*/
private static String getValueHssf(HSSFCell hssfCell) {
if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
/**
* 找到Xlsx文件的excell中有效数据的起始位置
*
* @param path
* @return
* @throws IOException
*/
private static Map<String, Integer> findXlsxStartEnd(String path)
throws IOException {
xssfWorkbook = new XSSFWorkbook(path);
Map<String, Integer> resultNum = new HashMap<String, Integer>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
// 循环列Cell
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
if (xssfCell == null) {
continue;
}
String value = getValueXssf(xssfCell).trim();
// 如果这里读取出了账号,那么记录它的cell在每行的位置,以后直接读取下一行的此处
if (value.equals("账号")) {
resultNum.put("startRow", rowNum + 1);
resultNum.put("startCell", cellNum);
} else if (value.equals("说明")) {
resultNum.put("endCell", cellNum);
return resultNum;
}
}
}
}
return null;
}
/**
* 通过起始位置读取xlsx中的数据
*
* @param path
* @param startMap
* @return
* @throws IOException
*/
private static List<Map<String, String>> readXlsx(String path, Map startMap)
throws IOException {
List<Map<String, String>> accounts = new ArrayList<Map<String, String>>();
Map<String, String> tempMap = null;
InputStream is = new FileInputStream(path);
xssfWorkbook = new XSSFWorkbook(path);
int startRow = (int) startMap.get("startRow");
int startCell = (int) startMap.get("startCell");
int endCell = (int) startMap.get("endCell");
// 循环工作表Sheet
// 循环工作表Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = startRow; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
tempMap = new HashMap<String, String>();
// 循环列Cell
for (int cellNum = startCell; cellNum <= endCell; cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
String value = getValueXssf(xssfCell).trim();
tempMap.put(String.valueOf(cellNum),
StringUtils.isNullOrEmpty(value) ? "" : value);
}
accounts.add(tempMap);
}
}
return accounts;
}
@SuppressWarnings("static-access")
private static String getValueXssf(XSSFCell xssfCell) {
if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfCell.getNumericCellValue());
} else {
return String.valueOf(xssfCell.getStringCellValue());
}
}
/**
* 从Excel中读取管理员信息的格式化方法
*
* @param xssfCell
* @return
*/
@SuppressWarnings("static-access")
public static String getValueXssfFormat(XSSFCell xssfCell) {
DecimalFormat df2 = new DecimalFormat("#");
if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(df2.format(xssfCell.getNumericCellValue()));
} else {
return String.valueOf(xssfCell.getStringCellValue());
}
}
/**
* 从Excel中读取管理员信息
*
* @param path
* @return
* @throws IOException
*/
public static List<String> readXlsxFormat(String path) throws IOException {
List<String> list = new ArrayList<String>();
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(path);
// 循环工作表Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
// 循环列Cell
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
if (xssfCell == null) {
continue;
}
list.add(getValueXssfFormat(xssfCell));
System.out.print(" " + getValueXssfFormat(xssfCell));
}
System.out.println();
}
}
return list;
}
/**
* 测试方法
*
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
String path = "f:" + File.separator + "checkAccount.xls";
List<Map<String, String>> lists = startRead(path);
Map<String, String> tem = lists.get(0);
System.out.println(tem.toString());
System.out.println(lists.size());
}
}
分享到:
相关推荐
C# 编写的操作Excel读取/写入动态库(COM/NPOI两种方式),个人推荐使用NPOI方式,因为COM方式存在无法关闭Excel进程的情况(个人体验COM方式效率有点低)。。。已设置0分,资源自取。
AutoLISP例程:读取excel文件.doc
通过读取Excel后,通过选择对应表会显示对应表类容
vc 直接读取EXCELL的内容 vc 直接读取EXCELL的内容
qt读取excel文件,且封装成动态库。以Qtcreator编译的。开发环境Qtcreator+Qt5.5.1
详细的从Excel读取数据导入到程序中,考虑了不同的Excell版本情况。
php操作excel 将excel的数据读取成sql语句 或者将 php的数组放入excel
用sql server读取和写入excel文件,读取和写入数据非常方便,大批量数据导入与导出非常快
java读取excel表格的数据,并将其保存!已经调试过了,可以使用!
本vi用于读取excel文件并将所需数据做快速傅里叶变换(FFT)处理。
本程序采用CS架构,读取EXCEL表格中的数据,在读取时,系统会自己遍历文件夹下面的所有文件。在将数据写入数据库时,会自动检测数据库是否存在,如果未有数据库,系统会提示用户是否新建数据库,如果新建,会提供...
EXCEL 从串口接收数据。单片机AVR从串口发送数据。
iOS 内部解析.xlsx 文件内容,使用简单,导入xcode即可使用
Java读取Excel所需jar包 (poi.jar等)Java读取Excel所需jar包 (poi.jar等)
解决c#和excell的接口问题,很简单的操作!
里面包括完整的vs2015+qt5源码,并有可执行的软件。实现了导入导出Excel表、增加、(多行)删除功能,并通过qss文件实现了美化
Labview 读取office Excel文件内容
java对Excel表格数据处理并以List集合返回(只要传Excel文件路径即可)
先把excel的文件读出来,转化为Json结构。注:第一行标题作为key,其它行作为value,具体参考demo Sheet1 = ( { "\U626b\U63cf\U65e5\U671f" = "2017-06-06 18:40:18"; "\U6536/\U6d3e\U4ef6\U5458" = "\U6c49\U...
jeecg导入excel 想学的速度了