package com.joie.core.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ImportExcelUtils {
public static List<List<Object>> importExcel(File file) throws IOException {
String fileName = file.getName();
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
if ("xls".equals(extension)) {
return read2003Excel(file);
} else if ("xlsx".equals(extension)) {
return read2007Excel(file);
} else {
throw new IOException("不支持的文件类型");
}
}
public static List<List<Object>> importExcel(InputStream in ,String fileName) throws IOException {
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
if ("xls".equals(extension)) {
return read2003Excel(in);
} else if ("xlsx".equals(extension)) {
return read2007Excel(in);
} else {
throw new IOException("不支持的文件类型");
}
}
private static List<List<Object>> read2003Excel(InputStream in) throws IOException {
List<List<Object>> list = new LinkedList<List<Object>>();
HSSFWorkbook hwb = new HSSFWorkbook(in);
HSSFSheet sheet = hwb.getSheetAt(0);
Object value = null;
HSSFRow row = null;
HSSFCell cell = null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
List<Object> linked = new LinkedList<Object>();
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null) {
continue;
}
DecimalFormat df = new DecimalFormat("0");// 格式化 number String
// 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0");// 格式化数字
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
// System.out.println(i+"行"+j+" 列 is String type");
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
// System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
// System.out.println(i+"行"+j+" 列 is Boolean type");
value = cell.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
// System.out.println(i+"行"+j+" 列 is Blank type");
value = "";
break;
default:
// System.out.println(i+"行"+j+" 列 is default type");
value = cell.toString();
}
if (value == null || "".equals(value)) {
continue;
}
linked.add(value);
}
list.add(linked);
}
return list;
}
/**
* 读取 office 2003 excel
*
* @throws IOException
* @throws FileNotFoundException
*/
private static List<List<Object>> read2003Excel(File file) throws IOException {
InputStream in = null;
List<List<Object>> dataList = null;
try {
in = new FileInputStream(file);
dataList = read2003Excel(in);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (in != null) {
in.close();
}
}
return dataList;
}
private static List<List<Object>> read2007Excel(InputStream in) throws IOException {
List<List<Object>> list = new LinkedList<List<Object>>();
// 构造 XSSFWorkbook 对象,strPath 传入文件路径
XSSFWorkbook xwb = new XSSFWorkbook(in);
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(0);
Object value = null;
XSSFRow row = null;
XSSFCell cell = null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
List<Object> linked = new LinkedList<Object>();
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null) {
continue;
}
DecimalFormat df = new DecimalFormat("0");// 格式化 number String
// 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0");// 格式化数字
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
// System.out.println(i+"行"+j+" 列 is String type");
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
// System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
// System.out.println(i+"行"+j+" 列 is Boolean type");
value = cell.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
// System.out.println(i+"行"+j+" 列 is Blank type");
value = "";
break;
default:
// System.out.println(i+"行"+j+" 列 is default type");
value = cell.toString();
}
if (value == null || "".equals(value)) {
continue;
}
linked.add(value);
}
list.add(linked);
}
return list;
}
/**
* 读取Office 2007 excel
*/
private static List<List<Object>> read2007Excel(File file) throws IOException {
InputStream in = null;
List<List<Object>> dataList = null;
try {
in = new FileInputStream(file);
dataList = read2007Excel(in);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (in != null) {
in.close();
}
}
return dataList;
}
public static void main(String[] args) throws IOException {
// excel 导入数据demo
File file = new File("F:\\666.xlsx");
List<List<Object>> dataList = importExcel(file);
for (int i = 1; i < dataList.size(); i++) {
for (int j = 1; j < dataList.get(i).size(); j++) {
System.out.println(dataList.get(i).get(j));
}
System.out.println("------------------");
}
}
}
没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
收起资源包目录
myExcel.rar (59个子文件)
myExcel
pom.xml 4KB
pro
myBatis_example
target
test-classes
m2e-jee
web-resources
META-INF
maven
com.joie.sample
myExcel
pom.xml 4KB
pom.properties 209B
MANIFEST.MF 114B
classes
log4j.properties 2KB
com
joie
core
util
ExportExcelUtils.class 7KB
ImportExcelUtils.class 7KB
vo
ResultVO.class 5KB
SheetVo.class 1KB
config
springs
spring-mvc.xml 2KB
spring.xml 679B
excel
web
controller
ExcelImportController.class 3KB
ExcelExportController.class 4KB
service
IExcelService.class 469B
impl
ExcelServiceImpl.class 3KB
测试地址 91B
.settings
com.genuitec.eclipse.migration.prefs 221B
com.genuitec.eclipse.j2eedt.core.prefs 1KB
org.eclipse.m2e.core.prefs 121B
org.eclipse.wst.jsdt.ui.superType.name 6B
org.eclipse.wst.common.project.facet.core.xml 397B
org.eclipse.core.resources.prefs 155B
org.eclipse.wst.common.component 708B
org.eclipse.wst.common.project.facet.core.prefs.xml 161B
org.eclipse.wst.jsdt.ui.superType.container 49B
.jsdtscope 508B
org.eclipse.jdt.core.prefs 664B
src
test
java
main
webapp
css
cola.css 211KB
semantic.css 656KB
META-INF
MANIFEST.MF 39B
WEB-INF
web.xml 2KB
view
index.jsp 824B
error.jsp 821B
success.jsp 824B
classes
lib
model
excel
importExcel.jsp 2KB
exportExcel.jsp 2KB
js
jquery.js 93KB
semantic.js 645KB
model
exportExcel.js 2KB
importExcel.js 4KB
3rd.js 126KB
cola.js 964KB
xlsx.core.min.js 419KB
index.jsp 2KB
resources
log4j.properties 2KB
com
joie
config
springs
spring-mvc.xml 2KB
spring.xml 679B
测试地址 91B
java
com
joie
core
util
ImportExcelUtils.java 7KB
ExportExcelUtils.java 6KB
vo
ResultVO.java 7KB
SheetVo.java 800B
excel
web
controller
ExcelExportController.java 2KB
ExcelImportController.java 2KB
service
IExcelService.java 525B
impl
ExcelServiceImpl.java 3KB
.project 2KB
.mymetadata 302B
.classpath 1KB
共 59 条
- 1
资源评论
joie2015
- 粉丝: 1
- 资源: 18
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- tensorflow-2.7.3-cp39-cp39-manylinux2010-x86-64.whl
- tensorflow-2.7.2-cp39-cp39-manylinux2010-x86-64.whl
- Python版本快速排序源代码
- Python 语言版的快速排序算法实现
- 450815388207377安卓_base.apk
- 超微主板 X9DRE-TF+ bios 支持 nvme启动
- 基于Python通过下载气象数据和插值拟合离散数据曲线实现对寒潮过程的能量分析
- 健身房系统的设计与实现论文Java项目
- 使用TCP实现的搜索可用服务器
- 使用贪心算法解决会议时间安排问题的 Java 示例代码
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功