POI实现excel导入导出
基于原生的POI做的导入导出，为了提高效率，建议使用easyPOI或者easyExcel
1.加入依赖
<!-- Excel文件操作 -->
<!-- 2003的支持 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- 2007及以上版本的支持 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
2.定义注解：
package Cloud.Base.Excel;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @author y
* @create 2020-07-03 14:15
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelField
{
/**
* 标题
*/
public String title();
}
3.定义FieldType
package Cloud.Base.Excel;
import scala.runtime.Long;
import java.lang.reflect.Field;
import java.net.SocketTimeoutException;
import java.util.Date;
/**
* @author
* @create 2020-07-04 10:56
*/
public class FieldType
{
public static final Class STRING = String.class;
public static final Class INTEGER = Integer.class;
public static final Class INT = int.class;
public static final Class SHORT = short.class;
public static final Class BIG_SHORT = Short.class;
public static final Class LONG = long.class;
public static final Class BIG_LONG = Long.class;
public static final Class DOUBLE = double.class;
public static final Class BIG_DOUBLE = Double.class;
public static final Class FLOAT = float.class;
public static final Class BIG_FLOAT = Float.class;
public static final Class BOOLEAN = boolean.class;
public static final Class BIG_BOOLEAN = Boolean.class;
public static final Class DATE = Date.class;
public static void main(String[] args)
{
Field[] fields = TestPO.class.getFields();
for (Field field : fields)
{
System.out.println(field.getType());
if (field.getType() == FieldType.INTEGER){
System.out.println(field.getType());
}
}
}
}
4.POI工具类：
package Cloud.Base.Excel;
import Cloud.Base.DateTime;
import Cloud.Base.Error;
import Cloud.Base.Guid;
import Cloud.Base.ListUtil;
import Cloud.Base.Loger;
import Cloud.Base.ReturnResult;
import Cloud.Base.Storage.OSS.Storage;
import Cloud.Base.StringUtility;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author y
* @create 2020-07-03 09:50
*/
public class ExcelUtil
{
private static String suffix_xls = ".xls";
private static String suffix_xlsx = ".xlsx";
/**
* 获取工作簿
*
* @param In
* @param FileName
* @return
*/
private static Workbook getWorkBook(InputStream In, String FileName)
{
//创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
try
{
if (FileName.endsWith(suffix_xls))
{
//2003
workbook = new HSSFWorkbook(In);
} else if (FileName.endsWith(suffix_xlsx))
{
//2007
workbook = new XSSFWorkbook(In);
}
}
catch (IOException e)
{
Loger.Log.error(e);
return workbook;
}
return workbook;
}
/**
* 获取工作簿
*
* @param FileName 文件名称
* @return
*/
private static Workbook getWorkBook(String FileName, int size)
{
if (FileName.endsWith(suffix_xls) && size < 65535)
{
//2003
return new HSSFWorkbook();
} else if (FileName.endsWith(suffix_xlsx) && size < 100000)
{
//2007 写数据较慢
return new XSSFWorkbook();
} else if (FileName.endsWith(suffix_xlsx) && size > 100000)
{
//快速的写大容量的时候，并且可以防止OOM，缺点是有临时文件，需要清除
return new SXSSFWorkbook();
} else
{
return null;
}
}
/**
* 根据路径到IVS3000中取出文件
*
* @param path
* @return
*/
public static InputStream getInputStream(String path)
{
//获得OSS存储对象
Storage StorageObj = Storage.GetInstance();
if (null == StorageObj)
{
Loger.Log.error(Error.NULL_REFERENCE);
return null;
}
ByteArrayOutputStream OutputStream = new ByteArrayOutputStream();
StorageObj.Load(path, OutputStream);
//将outputStream转换成inputStream
ByteArrayInputStream InputStream = new ByteArrayInputStream(OutputStream.toByteArray());
try
{
OutputStream.close();
}
catch (IOException e)
{
Loger.Log.error(e);
}
return InputStream;
}
/**
* 保存导出文件
*
* @param path
* @param In
* @return
*/
private static ReturnResult SavePath(String path, InputStream In)
{
ReturnResult Result = new ReturnResult();
//获得OSS存储对象
Storage StorageObj = Storage.GetInstance();
if (null == StorageObj)
{
Loger.Log.error(Result.Result = Error.NULL_REFERENCE);
return Result;
}
return StorageObj.Save(path, In);
}
/**
* 获取单元格内容
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell, Workbook workbook)
{
String cellValue = "";
if (cell == null)
{
return cellValue;
}
FormulaEvaluator FormulaEvaluator = null;
if (workbook instanceof HSSFWorkbook)
{
FormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
}
else if (workbook instanceof XSSFWorkbook)
{
FormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
} else
{
FormulaEvaluator = new SXSSFFormulaEvaluator((SXSSFWorkbook) workbook);
}
//判断数据的类型
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC: //数字
if (HSSFDateUtil.isCellDateFormatted(cell))
{
Date date = cell.getDateCellValue();
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue = df.format(date);
} else
{
//不是日期格式，防止数字过长，转换成String格式
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = String.valueOf(cell.getStringCellValue());
}
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
//计算公式,获取公式中的数值
cellValue = FormulaEvaluator.evaluate(cell).formatAsString();
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
/**
* 给对象赋值
*
* @param Field
* @param Instance
* @param Content
*/
private static void setField(Field Field, Object Instance, String Content) throws IllegalAccessException
{
//获取属性的类型
Class Type = Field.getType();
//String
if (FieldType.STRING == Type)
{
Field.set(Instance, Content);
return;
}
//int、Integer
if (FieldType.INT == Type || FieldType.INTEGER == Type)
{
Field.set(Instance, Integer.valueOf(Content));
return;
}
//Long、long
if (FieldType.BIG_LONG == Type || FieldType.LONG == Type)
{
Field.set(Instance, Long.valueOf(Content));
return;
}
//Date
if (FieldType.DATE == Type)
{
Date Date = DateTime.Parse(Content);
Field.set(Instance, Date);
return;
}
//Double、double
if (FieldType.DOUBLE == Type || FieldType.BIG_DOUBLE == Type)
{
Field.set(Instance, Double.valueOf(Content));
return;
}
//Float、float
if (FieldType.FLOAT == Type || FieldType.BIG_FLOAT == Type)
{
Field.set(Instance, Float.valueOf(Content));
return;
}
}
/**
* 获取映射关系
* Title 和属性名一一对应
*
* @param
* @return
*/
public static Map<String, Object> getMap(Class<?> Clazz)
{
HashMap<String, Object> map = new HashMap<>();
if (null == Clazz)
{
Loger.Log.error(Error.INVALID_PARAMETER);
return map;
}
try
{
Field[] fields = Clazz.getDeclaredFields();
for (Field field : fields)
{
ExcelField Annotation = field.getAnnotation(ExcelField.class);
if (null == Annotation)
{
continue;
}
//例如 属性名称作为Key，Title作为Value
map.put(field.getName(), Annotation.title());
}
}
catch (Exception e)
{
Loger.Log.error(e);
}
return map;
}
/**
* 读取Excel文件
*
* @param path
* @param FileName
* @param clazz
* @param sheetIndex
* @param starRow
* @param starCell
* @param titleLine
* @return 返回结果集
*/
public static List<Object> importExcel(String path, String FileName, Class clazz, int sheetIndex, int starRow, int starCell, int titleLine)
{
List<Object> list = new ArrayList<>();
InputStream inputStream = null;
Workbook workBook = null;
if (StringUtility.IsNullOrEmpty(path))
{
Loger.Log.error(Error.INVALID_PATH);
return null;
}
try
{
//关联标题和属性名
Map<String, Object> Map = getMap(clazz);
if (null == Map)
{
Loger.Log.error(Error.UNKNOWN_ERROR);
return null;
}
//根据path获取输入流
inputStream = getInputStream(path);
//获取工作簿
workBook = getWorkBook(inputStream, path);
//工作簿不存在
if (null == workBook)
{
Loger.Log.error(Error.UNKNOWN_ERROR);
return null;
}
//获取制定的sheet
Sheet sheet = workBook.getSheetAt(sheetIndex);
//不存在sheet页
if (null == sheet)
{
Loger.Log.error(Error.UNKNOWN_ERROR);
return null;
}
//获取标题栏
Row TitleRow = sheet.getRow(titleLine);
//不存在标题栏
if (null == TitleRow)
{
Loger.Log.error(Error.UNKNOWN_ERROR);
return null;
}
int firstCellNum = TitleRow.getFirstCellNum();
int lastCellNum = TitleRow.getLastCellNum();
//创建映射
HashMap<Integer, String> Mapping = new HashMap<>();
for (int i = firstCellNum; i < lastCellNum; i++)
{
Cell cell = TitleRow.getCell(i);
String TitleName = getCellValue(cell,workBook);
for (String Key : Map.keySet())
{
Object Value = Map.get(Key);
if (null == Value)
{
continue;
}
if (Value.toString().equals(TitleName.trim()))
{
//处理映射关系
Mapping.put(i, Key);
}
}
}
//获得最大行数
int lastRowNum = sheet.getLastRowNum();
for (int i = starRow; i <= lastRowNum; i++)
{
//获取第i行的数据
Row row = sheet.getRow(i);
if (null == row)
{
continue;
}
//实例对象
Object Instance = clazz.newInstance();
for (int j = firstCellNum; j < lastCellNum; j++)
{
Cell cell = row.getCell(j);
//获取当前cell表格的值
String value = getCellValue(cell,workBook);
//获取该单元格对应的实例对象属性名
String Name = Mapping.get(j);
//得到该对象的
Field field = clazz.getField(Name);
if (null == field)
{
continue;
}
//强制访问
field.setAccessible(true);
//给对象赋属性赋值
setField(field, Instance, value);
}
list.add(Instance);
}
}
catch (Exception e)
{
Loger.Log.error(e);
list = null;
}
finally
{
try
{
if (null != workBook)
{
workBook.close();
}
if (null != inputStream)
{
inputStream.close();
}
}
catch (Exception e)
{
Loger.Log.error(e);
list = null;
}
return list;
}
}
/**
* Excel 对象的导出
*
* @param list 需要导出的数据列表
* @param Clazz 该类的Class对象
* @param Title 标题
* @param StartRow 导出的起始行，如果没给默认从第一行开始导出
* @param path 文件路径
* @param FileName 文件名称
* @return
*/
public static ReturnResult exportExcel(List<Object> list, Class<?> Clazz, String[] Title, int StartRow, String path, String FileName)
{
ReturnResult Result = new ReturnResult();
Workbook Workbook = null;
ByteArrayOutputStream OutputStream = null;
ByteArrayInputStream InputStream = null;
try
{
Map<String, Object> Map = getMap(Clazz);
if (null == Map)
{
Loger.Log.error(Error.UNKNOWN_ERROR);
return null;
}
HashMap<Integer, String> Mapping = new HashMap<>();
OutputStream = new ByteArrayOutputStream();
//数据为空，不允许导出
if (ListUtil.IsNullOrEmpty(list))
{
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
//title为空，不允许导出
if (null == Title)
{
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
//开始行小于0则，默认是0
if (0 >= StartRow)
{
StartRow = 0;
}
//path 非空的判断
if (StringUtility.IsNullOrEmpty(path))
{
path = String.format("%s_%s%s%s", FileName, Guid.NewId(), DateTime.ParseLong(new Date()), ".xls");
}
//获得工作簿
Workbook = getWorkBook(path, list.size());
if (null == Workbook)
{
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
Sheet Sheet = Workbook.createSheet();
Row TitleRow = Sheet.createRow(StartRow);
int StartCell = 0;
int LastCell = Title.length;
//写标题行
for (int i = StartCell; i < LastCell; i++)
{
Cell cell = TitleRow.createCell(i);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(Title[i]);
//处理映射关系
for (String Key : Map.keySet())
{
Object Value = Map.get(Key);
if (null == Value)
{
continue;
}
if (StringUtility.IsSame(Value.toString(), Title[i]))
{
Mapping.put(i, Key);
}
}
}
//写内容
for (int i = 0; i < list.size(); i++)
{
//获取对象
Object Instance = list.get(i);
if (null == Instance)
{
continue;
}
//创建单元格
Row ContentRow = Sheet.createRow(i + StartRow + 1);
for (int j = StartCell; j < LastCell; j++)
{
Cell ContentCell = ContentRow.createCell(j);
String FieldName = Mapping.get(j);
Field field = Clazz.getField(FieldName);
field.setAccessible(true);
//获取对象内容
Object Content = field.get(Instance);
//如果类型是日期，则格式化
if (field.getType().equals(Date.class))
{
Date content = (Date) Content;
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String DateString = format.format(content);
ContentCell.setCellValue(DateString);
} else
{
//否则所有类型均用字符串写出
ContentCell.setCellValue(Content.toString());
}
ContentCell.setCellType(Cell.CELL_TYPE_STRING);
}
}
//存储路径
Workbook.write(OutputStream);
//清除临时文件
if (Workbook instanceof SXSSFWorkbook)
{
((SXSSFWorkbook) Workbook).dispose();
}
//转换成字节数组输入流
InputStream = new ByteArrayInputStream(OutputStream.toByteArray());
Result = SavePath(path, InputStream);
if (Result.Result != Error.SUCCESS)
{
Loger.Log.error(Result.Result);
return Result;
}
}
catch (Exception e)
{
Loger.Log.error(e);
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
finally
{
try
{
if (null != InputStream)
{
InputStream.close();
}
if (null != OutputStream)
{
OutputStream.close();
}
if (null != Workbook)
{
Workbook.close();
}
}
catch (Exception e)
{
Loger.Log.error(e);
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
return Result;
}
}
/**
* Excel 的导出，常用的查询都是报表的形式
*
* @param list 内容列表
* @param Title 标题
* @param StartRow 起始行
* @param path 路径
* @param FileName 文件名
* @param TitleMapping 记录Map中的key的顺序
* @return
*/
public static ReturnResult exportExcelReport(List<Map<String, Object>> list, String[] Title, int StartRow, String path, String FileName, String[] TitleMapping)
{
ReturnResult Result = new ReturnResult();
Workbook Workbook = null;
ByteArrayOutputStream OutputStream = null;
ByteArrayInputStream InputStream = null;
try
{
OutputStream = new ByteArrayOutputStream();
//数据为空，不允许导出
if (ListUtil.IsNullOrEmpty(list))
{
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
//title为空，不允许导出
if (null == Title)
{
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
//开始行小于0则，默认是0
if (0 >= StartRow)
{
StartRow = 0;
}
//path 非空的判断
if (StringUtility.IsNullOrEmpty(path))
{
path = String.format("%s_%s%s%s", FileName, Guid.NewId(), DateTime.ParseLong(new Date()), ".xls");
}
//TitleMapping
if (null == TitleMapping || TitleMapping.length == 0)
{
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
//获得工作簿
Workbook = getWorkBook(path, list.size());
if (null == Workbook)
{
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
Sheet Sheet = Workbook.createSheet();
Row TitleRow = Sheet.createRow(StartRow);
int StartCell = 0;
int LastCell = Title.length;
//写标题行
for (int i = StartCell; i < LastCell; i++)
{
Cell cell = TitleRow.createCell(i);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(Title[i]);
}
//写内容
for (int i = 0; i < list.size(); i++)
{
Map<String, Object> DataMap = list.get(i);
StartCell = 0;
if (null == DataMap)
{
continue;
}
//创建单元格
Row ContentRow = Sheet.createRow(i + StartRow + 1);
for (String Key : TitleMapping)
{
Object Value = DataMap.get(Key);
if (null == Value)
{
break;
}
if (Value instanceof Date)
{
Date DateValue = (Date) Value;
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Value = format.format(DateValue);
}
Cell ContentCell = ContentRow.createCell(StartCell);
ContentCell.setCellType(Cell.CELL_TYPE_STRING);
ContentCell.setCellValue(Value.toString());
StartCell++;
}
}
//存储路径
Workbook.write(OutputStream);
//清除临时文件
if (Workbook instanceof SXSSFWorkbook)
{
((SXSSFWorkbook) Workbook).dispose();
}
//转换成字节数组输入流
InputStream = new ByteArrayInputStream(OutputStream.toByteArray());
Result = SavePath(path, InputStream);
if (Result.Result != Error.SUCCESS)
{
Loger.Log.error(Result.Result);
return Result;
}
}
catch (Exception e)
{
Loger.Log.error(e);
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
finally
{
try
{
if (null != InputStream)
{
InputStream.close();
}
if (null != OutputStream)
{
OutputStream.close();
}
if (null != Workbook)
{
Workbook.close();
}
}
catch (Exception e)
{
Loger.Log.error(e);
Loger.Log.error(Result.Result = Error.INVALID_PARAMETER);
return Result;
}
return Result;
}
}
}
5.定义实体类
package Cloud.Base.Excel;
import java.util.Date;
/**
* @author y
* @create 2020-07-03 15:17
*/
public class TestPO
{
@ExcelField(title = "索引")
public int Index;
@ExcelField(title = "名称")
public String name;
@ExcelField(title = "编码")
public String NO;
@ExcelField(title = "日期")
public Date DateTime ;
@ExcelField(title = "价格")
public Double price;
public Integer age;
public TestPO()
{
}
public TestPO(int index, String name, String NO, Date dateTime, Integer age)
{
Index = index;
this.name = name;
this.NO = NO;
DateTime = dateTime;
this.age = age;
}
@Override
public String toString()
{
return "TestPO{" + "Index=" + Index + ", name='" + name + '\'' + ", NO='" + NO + '\'' + ", DateTime=" + DateTime + ", price=" + price + ", age=" + age + '}';
}
}
6.测试
package Cloud.Base.Excel;
import Cloud.Base.DateTime;
import Cloud.Base.Guid;
import Cloud.Base.MapUtil;
import scala.annotation.target.field;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* @author y
* @create 2020-07-03 15:19
*/
public class Test
{
public static void main(String[] args)
{
long start = System.currentTimeMillis();
List<Object> list = ExcelUtil.importExcel("E:\\test.xlsx", null, TestPO.class, 0, 1, 0, 0);
// System.out.println(list);
List<Map<String,Object>> mapList = new ArrayList<>();
for (Object PO : list)
{
TestPO TestPO = (TestPO) PO;
Map<String, Object> map = MapUtil.Object2Map(TestPO);
mapList.add(map);
}
String[] Title ={"名称","编码","日期","价格"};
String[] TitleMapping ={"name","NO","DateTime","price"};
ExcelUtil.exportExcelReport(mapList, Title, 0, null,"enen" , TitleMapping);
long end = System.currentTimeMillis();
System.out.println(end-start);
}
}
扫一扫
1255
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
