import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import com.alibaba.fastjson.JSON;
import org.apache.commons.collections.map.ListOrderedMap;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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;
/**
* Excel工具类
*
* @author Juno
*
*/
@SuppressWarnings("unchecked")
public class ExcelUtil {
private ExcelUtil() {
}
;
private static List<String> columns;//要解析excel中的列名
private static int sheetNum = 0;//要解析的sheet下标
private static boolean dynamicColumn = false;//是否
private static Map<String, String> dynamicMapConfig = new ListOrderedMap();//動態列配置文件
private static String pattern;// 日期格式
/**
* poi读取excle
*
* @return
*/
public static String readExcel(File file) throws Exception {
StringBuilder retJson = new StringBuilder();
InputStream inStream = null;
try {
inStream = new FileInputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook(inStream);
HSSFSheet sheet = workbook.getSheetAt(sheetNum);//获得表
int lastRowNum = sheet.getLastRowNum();//最后一行
retJson.append("[");
for (int i = 0; i < lastRowNum; i++) {
HSSFRow row = sheet.getRow(i + 1);//获得行
String rowJson = readExcelRow(row);
retJson.append(rowJson);
if (i < lastRowNum - 1) {
retJson.append(",");
}
}
retJson.append("]");
} catch (Exception e) {
try {
inStream = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(inStream);
XSSFSheet sheet = workbook.getSheetAt(sheetNum);
int lastRowNum = sheet.getLastRowNum();//最后一行
retJson.append("[");
for (int i = 0; i < lastRowNum; i++) {
XSSFRow row = sheet.getRow(i + 1);//获得行
String rowJson = readExcelRow(row);
retJson.append(rowJson);
if (i < lastRowNum - 1) {
retJson.append(",");
}
}
retJson.append("]");
} catch (IOException e1) {
e1.printStackTrace();
throw e1;
}
} finally {
close(null, inStream);
}
return retJson.toString();
}
/**
* poi读取excle 生成实体集合
*
* @param <E>
* @return
*/
public static <E> List<E> readExcel(File file, Class<E> clazz) throws Exception {
if (columns == null) {
setColumns(clazz);
}
InputStream inStream = null;
List<E> eList = new ArrayList<E>();
try {
inStream = new FileInputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook(inStream);
HSSFSheet sheet = workbook.getSheetAt(sheetNum);//获得表
int lastRowNum = sheet.getLastRowNum();//最后一行
if (dynamicColumn) {
//动态列處理
HSSFRow header = sheet.getRow(0);//表頭
List<String> rList = readRow(header);
setDynamicColumn(rList);
}
for (int i = 0; i < lastRowNum; i++) {
HSSFRow row = sheet.getRow(i + 1);//获得行
String rowJson = readExcelRow(row);
E _e = JSON.parseObject(rowJson, clazz);
eList.add(_e);
}
} catch (Exception e) {
try {
inStream = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(inStream);
XSSFSheet sheet = workbook.getSheetAt(sheetNum);
int lastRowNum = sheet.getLastRowNum();//最后一行
if (dynamicColumn) {
//动态列處理
XSSFRow header = sheet.getRow(0);
List<String> rList = readRow(header);
setDynamicColumn(rList);
}
for (int i = 0; i < lastRowNum; i++) {
XSSFRow row = sheet.getRow(i + 1);//获得行
String rowJson = readExcelRow(row);
E _e = JSON.parseObject(rowJson, clazz);
eList.add(_e);
}
} catch (Exception e1) {
e1.printStackTrace();
throw e1;
}
} finally {
close(null, inStream);
}
return eList;
}
/**
* 动态列配置
*/
private static void setDynamicColumn(List<String> headlist) throws Exception {
List<String> tempcolumns = new ArrayList<String>();
for (Iterator<String> iterator = headlist.iterator(); iterator.hasNext();) {
String value = iterator.next();
value = value.replace("（", "(");
value = value.replace("）", ")");
value = value.trim();
String key = findKey(value);
if (key == null) {
throw new RuntimeException("请上传合法excle！");
}
tempcolumns.add(findKey(value));
}
setColumns(tempcolumns);//重新设置column
}
/**
* 根据value动态找到key值
*
* @return
*/
private static String findKey(String value) {
for (Map.Entry<String, String> entryconfig : dynamicMapConfig.entrySet()) {
String keyc = entryconfig.getKey();
String valc = entryconfig.getValue();
if (valc.equals(value)) {
return keyc;
}
}
return null;
}
/**
* poi读取excle 生成实体集合
*
* @param file
* @param clazz
* @param exceptscolumns
* @return
*/
public static <E> List<E> readExcel(File file, Class<E> clazz, String[] exceptscolumns) throws Exception {
setColumns(clazz, exceptscolumns);
return readExcel(file, clazz);
}
/**
* 读取excle多个sheet到多个对象（对象的顺序固定）
*
* @param file
* @param clazz
* @return
*/
public static <E> List<List<E>> readExcel(File file, Class<E>[] clazz) throws Exception {
List<List<E>> eliLists = new ArrayList<List<E>>();//[clazz.length];
int i = 0;
for (Class<E> cls : clazz) {
setColumns(null, null);
setSheetNum(i++);
List<E> eList = readExcel(file, cls);
eliLists.add(eList);
}
return eliLists;
}
/**
* 读取行值
*
* @return
*/
private static String readExcelRow(HSSFRow row) {
StringBuilder rowJson = new StringBuilder();
int lastCellNum = ExcelUtil.columns.size();//最后一个单元格
rowJson.append("{");
for (int i = 0; i < lastCellNum; i++) {
HSSFCell cell = row.getCell(i);
String cellVal = getCellValueToString(cell);
rowJson.append(toJsonItem(columns.get(i), cellVal));
if (i < lastCellNum - 1) {
rowJson.append(",");
}
}
rowJson.append("}");
return rowJson.toString();
}
/**
* 读取行值
*
* @return
*/
private static String readExcelRow(XSSFRow row) {
StringBuilder rowJson = new StringBuilder();
int lastCellNum = ExcelUtil.columns.size();//最后一个单元格
rowJson.append("{");
for (int i = 0; i < lastCellNum; i++) {
XSSFCell cell = row.getCell(i);
String cellVal = getCellValueToString(cell);
rowJson.append(toJsonItem(columns.get(i), cellVal));
if (i < lastCellNum - 1) {
rowJson.append(",");
}
}
rowJson.append("}");
return rowJson.toString();
}
/**
* 读取行生称list
*
* @return
*/
private static List<String> readRow(XSSFRow row) {
List<String> ret = new ArrayList<String>();
int cellcount = row.getLastCellNum();
for (int i = 0; i < cellcount; i++) {
XSSFCell cell = row.getCell(i);
String cellval = getCellValueToString(cell);
if (cellval.trim().length() > 0) {
ret.add(cellval);
}
}
return ret;
}
/**
* 读取行生称list
*
* @return
*/
private static List<String> readRow(HSSFRow row) {
List<String> ret = new ArrayList<String>();
int cellcount = row.getLastCellNum();
for (int i = 0; i < cellcount; i++) {
HSSFCell cell = row.getCell(i);
String cellval = getCellValueToString(cell);
if (cellval.trim().length() > 0) {
ret.add(cellval);
}
}
return ret;
}
/**
* 读取单元格的值
*
* @param hssfCell
* @return
*/
@SuppressWarnings("static-access")
private static String getCellValueToString(Cell cell) {
String strCell = "";
if (cell == null) {
return null;
}
switch (cell.getCellType()) {
case BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (pattern != null) {
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
strCell = sdf.format(date);
} else {
strCell = date.toString();
}
break;
}
// 不是日期格式，则防止当数字过长时以科学计数法显示
//cell.setCellType(HSSFCell.CELL_TYPE_STRING);
strCell = cell.toString();
break;
case STRING:
strCell = cell.getStringCellValue();
break;
default:
strCell = "";
break;
}
return strCell;
}
/**
* 转换为json对
*
* @return
*/
private static String toJsonItem(String name, String val) {
return "\"" + name + "\":\"" + val + "\"";
}
/**
* 关闭io流
*
* @param fos
* @param fis
*/
private static void close(OutputStream out, InputStream in) {
if (in != null) {
try {
in.close();
} catch (IOException e) {
System.out.println("InputStream关闭失败");
e.printStackTrace();
}
}
if (out != null) {
try {
out.close();
} catch (IOException e) {
System.out.println("OutputStream关闭失败");
e.printStackTrace();
}
}
}
/**
* 填出数据到excel book中
*
* @param book
* @param data
* @param sheetname
* @param titles
* @param columns
*/
public static void data2Book(Workbook book, List<? extends Object> data, String sheetname, String[] titles, String[] columns) throws Exception {
Sheet sheet = book.createSheet(sheetname);
Row th = sheet.createRow((short) 0);//标题行
for (int i = 0; i < titles.length; i++) {
Cell cell = th.createCell(i);
cell.setCellValue(titles[i]);
}
Object _d = data.get(0);
if (_d instanceof Map) {
//Map集合
for (int j = 0; j < data.size(); j++) {
Map _dm = (Map) data.get(j);
Row tr = sheet.createRow((short) (j + 1));//内容行
for (int k = 0; k < columns.length; k++) {
Cell cell = tr.createCell(k);
Object val = _dm.get(columns[k]);
String value = val == null ? "" : val.toString();
cell.setCellValue(value);
}
}
} else {
//Bean集合
for (int j = 0; j < data.size(); j++) {
Object _do = data.get(j);
Row tr = sheet.createRow((short) (j + 1));//内容行
for (int k = 0; k < columns.length; k++) {
String column = columns[k];
Method method = getTargetGetMethod(_do, column);//获取目标方法
try {
Cell cell = tr.createCell(k);
Object val = method.invoke(_do);
String value = val == null ? "" : val.toString();
value = value.replace("00:00:00.0", "");//处理时间中非法字符
cell.setCellValue(value);
} catch (Exception e) {
e.printStackTrace();
throw e;
}
}
}
}
}
/**
* 获取bean的指定getter方法
*
* @param o
* @param name
* @return
*/
private static Method getTargetGetMethod(Object o, String name) throws Exception {
try {
String mname = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
return o.getClass().getMethod(mname);
} catch (Exception e) {
e.printStackTrace();
throw e;
}
}
/**
* 将bean所有属性放入map中
*/
private static <E> void beanProp2List(Class<E> clazz, List<String> excepts) {
Field[] fields = clazz.getDeclaredFields();
columns = new ArrayList<String>();//顺序固定可重复
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
String fieldName = field.getName();
if (excepts != null && excepts.contains(fieldName)) {
continue;
}
columns.add(fieldName);
}
}
public static List<String> getColumns() {
return ExcelUtil.columns;
}
public static void setColumns(List<String> columns) {
ExcelUtil.columns = columns;
}
public static void setColumns(Class<?> clazz) {
beanProp2List(clazz, null);
}
/**
* 设置列，不包括excepts指定的字段
*
* @param clazz
* @param excepts
*/
public static void setColumns(Class<?> clazz, String[] excepts) {
beanProp2List(clazz, Arrays.asList(excepts));
}
public static int getSheetNum() {
return sheetNum;
}
public static void setSheetNum(int sheetNum) {
ExcelUtil.sheetNum = sheetNum;
}
public static boolean isDynamicColumn() {
return dynamicColumn;
}
public static void setDynamicColumn(boolean dynamicColumn) {
ExcelUtil.dynamicColumn = dynamicColumn;
}
public static Map<String, String> getDynamicMapConfig() {
return dynamicMapConfig;
}
public static void setDynamicMapConfig(Map<String, String> dynamicMapConfig) {
ExcelUtil.dynamicMapConfig = dynamicMapConfig;
}
public void setPattern(String pattern) {
this.pattern = pattern;
}
}
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
<type>jar</type>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
<type>jar</type>
</dependency>
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>3.2.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.60</version>
</dependency>
相关推荐
POI Excel 工具类POI Excel 工具类POI Excel 工具类POI Excel 工具类POI Excel 工具类
Poi导出Excel工具类 支持大数据量 多sheet页方式 已经封装好,直接拿来调用里面的方法传入参数就行 很简单
Excel日常操作的一些工具类，简化开发，如有其他需求可以联系我，该工具类可满足日常开发的常见需求。
java使用POI导出 Excel+图片工具类 ，里面含有poi jar包，只调用接口即可直接保存Excel。使用的时候需先把数据封装，具体包装需根据实际导出数据进行处理。文件demo中只提供包装格式。
基于poi的excel导入导出封装，poi版本 <groupId>org.apache.poi <artifactId>poi <version>4.1.0 <groupId>org.apache.poi <artifactId>poi-ooxml <version>4.1.0 </dependency>
食用指南 https://blog.csdn.net/weixin_44067399/article/details/107974673 本工具为基于POI封装的一个工具类，旨在提高开发效率，供学习交流用 使用本工具的前提是安装了POI
poi excel poi excel poi excel poi excel poi excel poi excel poi excel poi excel
将excel数据转换成javabean对象，支持二级javabean转换！ 内包含测试代码和测试文件以及所需要的jar包！
POI导出Excel工具类，自动设置标题 列名 文件名，可插入图片，合并单元格
为更方便的使用POI的API来操作Excel（2003）文件，对POI中针对Excel文件的读写进行了简单封装。此类中包含以下功能： 1.根据模板创建Excel文件 2.获取及更新Excel文件内容 3.创建、复制Sheet 4.设置Sheet名称 ... ...
概述：Java poi导入导出EXCEL工具类（兼容各版本） 一、功能说明 允许同时导入或导出多个sheet，同一sheet可同时存在多个数据块，按数据块划分处理数据。 二、配置文件示例及详细说明 1、导入xml配置示例如下（见...
java的poi的导入工具类，只需要传入两个arrayList，一个是execl的标题，一个是ececl的数据，就可以直接导出到execl里面，支持最新的execl，全新封装的，让每一个人都会使用poi导出execl！
POI 操作Excel工具类;
基于poi开发的excel导出工具类，一种无样式优化导出，一种自适应cell宽度导出
基于反射的Excel操作工具类，可以灵活设置表头以及内容，可根据数据类型匹配成POJO，支持正则表达式等
java 基于poi的excel操作工具类，包含导入excel，导出excel，直接存储到服务器，或者返回流
String outputFile = "D:\\excel\\excel.xlsx"; OutputStream outputStream = new FileOutputStream(outputFile); UtilExcel utilExcel = new UtilExcel(); String titles = "所属区域,所属车间,当前处理人,描述...
java 导入导出excel工具类，别人3积分，我1积分，嘿嘿
java导入excel的工具类。支持单个对象的赋值，列表对象的赋值，指定坐标的赋值。主要利用的是poi的技术，java反射和自定义注解
poi导出excel工具类，亲测可用