为满足公司供应链部门对Excel处理的高要求，封装了一个通用自定义读取Excel工具类。支持xlsx格式，具备读取全部数据、自定义读取、按标题读取等功能，并能处理公式值及读取图片。
为什么为封装此工具类？
由于公司供应链部门业务需要，对Excel处理这块有较为严苛的要求。为了提高开发效率，从实际项目出发封装了通用自定义读取Excel工具类。
功能概述
- 支持读取全部excel数据
- 支持自定义读取（自定义读取行、列）数据
- 支持读取以title为基准的数据（只读取title下的行）
- 支持读取图片（返回图片的位置和流）
- 支持设置sheet index读取
- 支持读取函数值
- 支持annation读取列表值
使用注意点
由于操作Excel文件一般较大，建议对JVM调优，调大堆内存空间，将大对象直接放在老年代，具体调优方式，网上资料很多。
核心代码
/**
* 读取excel文件内容
* 仅支持xlsx格式
* @author Vachel.Wang
* @date 2016年6月16日 下午5:10:18
* @version V1.2
*/
public class ReadExcel {
private XSSFWorkbook workbook = null;
private XSSFSheet sheet;
private XSSFRow row;
private int sheetIndex = 0 ;
private int rowIndex = 0 ;
private int cellIndex = 0 ;
public static ReadExcel me(InputStream s) throws IOException{
return new ReadExcel(s);
}
public ReadExcel(InputStream s) throws IOException{
workbook = new XSSFWorkbook(s);
setSheetIndex(0);
}
public <T> List<T> readCoustomTemplet(Class<T> clazz) throws InstantiationException, IllegalAccessException {
List<T> list = new ArrayList<T>();
XSSFRow titleRow = sheet.getRow(rowIndex-1);
ArrayList<String> rowArr = new ArrayList<String>();
int cells = titleRow.getPhysicalNumberOfCells();
for (int j = cellIndex; j < cells; j++) {
rowArr.add(getStringCellValue(titleRow.getCell(j)));
}
List<Field> allFields = getMappedFiled(clazz, null);
Map<String, Field> fieldsMap = new HashMap<String, Field>();//定义一个map用于存放列的序号和field.
for (Field field : allFields) {
if (field.isAnnotationPresent(ExcelField.class)) {
ExcelField attr = field.getAnnotation(ExcelField.class);
field.setAccessible(true);
fieldsMap.put(attr.title(), field);
}
}
//row = sheet.getRow(rowIndex);
for (int i = rowIndex ; i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
T entity = null;
for (int j = cellIndex; j < cells; j++) {
String c = getStringCellValue(row.getCell(j));
entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
Field field = fieldsMap.get(rowArr.get(j));// 从map中得到对应列的field.
if (field==null) {
continue;
}
Class<?> fieldType = field.getType();
if (String.class == fieldType) {
field.set(entity, String.valueOf(c));
} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
field.set(entity, Integer.parseInt(c));
} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
field.set(entity, Long.valueOf(c));
} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
field.set(entity, Float.valueOf(c));
} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
field.set(entity, Short.valueOf(c));
} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
field.set(entity, Double.valueOf(c));
} else if (Character.TYPE == fieldType) {
if ((c != null) && (c.length() > 0)) {
field.set(entity, Character.valueOf(c.charAt(0)));
}
}
}
if (entity != null) {
list.add(entity);
}
}
return list;
}
private List<Field> getMappedFiled(Class clazz, List<Field> fields) {
if (fields == null) {
fields = new ArrayList<Field>();
}
Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段
// 得到所有field并存放到一个list中.
for (Field field : allFields) {
if (field.isAnnotationPresent(ExcelField.class)) {
fields.add(field);
}
}
if (clazz.getSuperclass() != null && !clazz.getSuperclass().equals(Object.class)) {
getMappedFiled(clazz.getSuperclass(), fields);
}
return fields;
}
/**
* 读取自定义读取方式数据
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public List<ArrayList<String>> readCoustom() throws FileNotFoundException, IOException{
List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
for (int i = rowIndex ; i < sheet.getPhysicalNumberOfRows(); i++) {
ArrayList<String> rowArr = new ArrayList<String>();
row = sheet.getRow(i);
for (int j = cellIndex; j < row.getPhysicalNumberOfCells(); j++) {
rowArr.add(getStringCellValue(row.getCell(j)));
}
excelData.add(rowArr);
}
return excelData;
}
/**
* 以title列为标准读取自定义数据
* @param titleIndex
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public List<ArrayList<String>> readCoustomByTitle(int titleIndex) throws FileNotFoundException, IOException{
XSSFRow titleRow = sheet.getRow(titleIndex);
List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
for (int i = rowIndex ; i < sheet.getPhysicalNumberOfRows(); i++) {
ArrayList<String> rowArr = new ArrayList<String>();
row = sheet.getRow(i);
for (int j = cellIndex; j < titleRow.getPhysicalNumberOfCells(); j++) {
rowArr.add(getStringCellValue(row.getCell(j)));
}
excelData.add(rowArr);
}
return excelData;
}
/**
* 读取自定义读取非空数据
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public List<ArrayList<String>> readCoustomExcludeNull() throws FileNotFoundException, IOException{
List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
for (int i = rowIndex ; i < sheet.getPhysicalNumberOfRows(); i++) {
ArrayList<String> rowArr = new ArrayList<String>();
row = sheet.getRow(i);
for (int j = cellIndex; j < row.getPhysicalNumberOfCells(); j++) {
String cellValue = getStringCellValue(row.getCell(j));
if(!cellValue.equals(""))
rowArr.add(cellValue);
}
excelData.add(rowArr);
}
return excelData;
}
/**
* 读取所有
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public List<ArrayList<String>> readAll() throws FileNotFoundException, IOException{
List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
for (int i = 0 ; i < sheet.getPhysicalNumberOfRows(); i++) {
ArrayList<String> rowArr = new ArrayList<String>();
row = sheet.getRow(i);
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
rowArr.add(getStringCellValue(row.getCell(j)));
}
excelData.add(rowArr);
}
return excelData;
}
/**
* 以title行为标准来读其它所有取列值
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public List<ArrayList<String>> readAllByTitle(int titleIndex) throws FileNotFoundException, IOException{
XSSFRow titleRow = sheet.getRow(titleIndex);
List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
for (int i = 0 ; i < sheet.getPhysicalNumberOfRows(); i++) {
ArrayList<String> rowArr = new ArrayList<String>();
row = sheet.getRow(i);
if(row==null)
continue;
for (int j = 0; j < titleRow.getPhysicalNumberOfCells(); j++) {
rowArr.add(getStringCellValue(row.getCell(j)));
}
excelData.add(rowArr);
}
return excelData;
}
/**
* 读取title行下的所有数据
* @param titleIndex
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public List<ArrayList<String>> readNextByTitle(int titleIndex) throws FileNotFoundException, IOException{
XSSFRow titleRow = sheet.getRow(titleIndex);
List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
for (int i = titleIndex+1 ; i < sheet.getPhysicalNumberOfRows(); i++) {
ArrayList<String> rowArr = new ArrayList<String>();
row = sheet.getRow(i);
if(row==null){
continue;
}
for (int j = 0; j < titleRow.getPhysicalNumberOfCells(); j++) {
rowArr.add(getStringCellValue(row.getCell(j)));
}
excelData.add(rowArr);
}
return excelData;
}
/**
* 读取非空数据
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public List<ArrayList<String>> readAllExcludeNull() throws FileNotFoundException, IOException{
List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
for (int i = 0 ; i < sheet.getPhysicalNumberOfRows(); i++) {
ArrayList<String> rowArr = new ArrayList<String>();
row = sheet.getRow(i);
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
String cellValue = getStringCellValue(row.getCell(j));
if(!cellValue.equals(""))
rowArr.add(cellValue);
}
excelData.add(rowArr);
}
return excelData;
}
/**
* 读取行的所有列的数据
* @param rowIndex
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public ArrayList<String> readRowData(int rowIndex) throws FileNotFoundException, IOException{
ArrayList<String> rowDataArr = new ArrayList<String>();
row = sheet.getRow(rowIndex);
for (int i = 0 ; i < row.getPhysicalNumberOfCells(); i++) {
String cellStr = getStringCellValue(row.getCell(i));
rowDataArr.add(cellStr);
}
return rowDataArr;
}
/**
* 读取行的所有列的非空数据
* @param rowIndex
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public ArrayList<String> readRowExcludeNullData(int rowIndex) throws FileNotFoundException, IOException{
ArrayList<String> rowDataArr = new ArrayList<String>();
row = sheet.getRow(rowIndex);
for (int i = 0 ; i < row.getPhysicalNumberOfCells(); i++) {
String cellValue = getStringCellValue(row.getCell(i));
if(!cellValue.equals(""))
rowDataArr.add(cellValue);
}
return rowDataArr;
}
/**
* 读取读列的数据
* 空为 ""
* @param rowIndex
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public String readCellData(int rowIndex , int cellIndex) throws FileNotFoundException, IOException{
row = sheet.getRow(rowIndex);
return getStringCellValue(row.getCell(cellIndex));
}
/**
* 获取所有图片
* @return
*/
public List<XSSFPictureData> readPictures() {
return workbook.getAllPictures();
}
/**
* 读取单元格内容，支持获取函数内容
* @param cell
* @return
*/
public static String getStringCellValue(Cell cell) {
String strCell = "";
if(cell==null) return strCell;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING: // get String data
strCell = cell.getRichStringCellValue().getString().trim();
break;
case Cell.CELL_TYPE_NUMERIC: // get date or number data
if (DateUtil.isCellDateFormatted(cell)) {
strCell = com.gjw.utils.DateUtil.formatYYYYMMDDHHMMSS(cell.getDateCellValue());
} else {
BigDecimal numberic=new BigDecimal(cell.getNumericCellValue());
strCell = numberic.toPlainString();
}
break;
case Cell.CELL_TYPE_BOOLEAN: // get boolean data
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: // get expression data
FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
evaluator.evaluateFormulaCell(cell);
CellValue cellValue = evaluator.evaluate(cell);
if(StringUtil.isNotBlank(cellValue.getStringValue())){
strCell = String.valueOf(cellValue.getStringValue()) ;
}else{
strCell = String.valueOf(cellValue.getNumberValue()) ;
}
break;
default:
strCell = "";
}
return strCell;
}
/**
* 设置读取sheet下标，默认为0
* @param sheetIndex
* @return
*/
public ReadExcel setSheetIndex(int sheetIndex){
this.sheetIndex = sheetIndex ;
sheet = workbook.getSheetAt(sheetIndex);
return this;
}
/**
* 设置读取sheet下标，默认为0
* @param rowIndex
* @return
*/
public ReadExcel setRowIndex(int rowIndex){
this.rowIndex = rowIndex ;
return this;
}
/**
* 设置读取cell下标，默认为0
* @param rowIndex
* @return
*/
public ReadExcel setCellIndex(int cellIndex){
this.cellIndex = cellIndex ;
return this;
}
/**
* 获取sheet总数
* @return
*/
public int getSheetCount(){
return this.workbook.getNumberOfSheets();
}
/**
* 根据sheet下标获取sheet名称
* @param index
* @return
*/
public String getSheetNameByIndex(int index){
return this.workbook.getSheetAt(index).getSheetName();
}
public String getCurrentSheetName(){
return this.workbook.getSheetAt(sheetIndex).getSheetName();
}
/**
* 获取Excel2007图片
* @param sheetNum 当前sheet编号
* @param sheet 当前sheet对象
* @param workbook 工作簿对象
* @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
*/
public static Map<String, PictureData> getSheetPictrues(int sheetNum,
XSSFSheet sheet, XSSFWorkbook workbook) {
Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
for (POIXMLDocumentPart dr : sheet.getRelations()) {
if (dr instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) dr;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture pic = (XSSFPicture) shape;
XSSFClientAnchor anchor = pic.getPreferredSize();
CTMarker ctMarker = anchor.getFrom();
String picIndex = String.valueOf(sheetNum) + "_"
+ ctMarker.getRow() + "_" + ctMarker.getCol();
sheetIndexPicMap.put(picIndex, pic.getPictureData());
}
}
}
return sheetIndexPicMap;
}
public static void printImg(List<Map<String, XSSFPictureData>> sheetList) throws IOException {
for (Map<String, XSSFPictureData> map : sheetList) {
Object key[] = map.keySet().toArray();
for (int i = 0; i < map.size(); i++) {
// 获取图片流
XSSFPictureData pic = map.get(key[i]);
// 获取图片索引
String picName = key[i].toString();
// 获取图片格式
String ext = pic.suggestFileExtension();
byte[] data = pic.getData();
FileOutputStream out = new FileOutputStream("D:\\pic" + picName + "." + ext);
out.write(data);
out.close();
}
}
}
public XSSFWorkbook getWorkbook() {
return workbook;
}
public XSSFSheet getSheet() {
return sheet;
}
public int getSheetIndex() {
return sheetIndex;
}
public static void main(String[] args) throws IOException {
/*InputStream inputStream = new FileInputStream("d:\\success3.xlsx");
try {
List<ScmInvoiceImportLog> list = ReadExcel.me(inputStream).setSheetIndex(0).setRowIndex(1).readCoustomTemplet(ScmInvoiceImportLog.class);
System.out.println(list);
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
// list all demo
/*List<ArrayList<String>> list = ReadExcel.me(inputStream).setSheetIndex(0).readAllByTitle(4);
for(ArrayList<String> rowArray : list){
for(String string : rowArray){
System.out.print(string+"\t");
}
System.out.println();
}*/
// read picture demo
/*List<XSSFPictureData> readPictures = ReadExcel.me(inputStream).readPictures();
for(XSSFPictureData pictureData:readPictures){
int type = pictureData.getPictureType();
if(type == Workbook.PICTURE_TYPE_JPEG){
File file = new File("d:\\"+System.currentTimeMillis()+".jpg");
OutputStream outputStream = new FileOutputStream(file);
outputStream.write(pictureData.getData());
outputStream.flush();
outputStream.close();
}
}*/
/*ReadExcel readExcel = ReadExcel.me(inputStream).setSheetIndex(0);
Map<String, PictureData> dataMap = readExcel.getSheetPictrues(readExcel.getSheetIndex(),readExcel.getSheet(),readExcel.getWorkbook());
for(Map.Entry<String,PictureData> entry : dataMap.entrySet()){
System.out.println("key = "+entry.getKey()+" , value = "+entry.getValue());
}*/
}
}
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
/**
* 导出字段名（默认调用当前字段的“get”方法，如指定导出字段为对象，请填写“对象名.对象属性”，例：“area.name”、“office.name”）
*/
String value() default "";
/**
* 导出字段标题（需要添加批注请用“**”分隔，标题**批注，仅对导出模板有效）
*/
String title();
/**
* 字段类型（0：导出导入；1：仅导出；2：仅导入）
*/
int type() default 0;
/**
* 导出字段对齐方式（0：自动；1：靠左；2：居中；3：靠右）
*/
int align() default 0;
/**
* 导出字段字段排序（升序）
*/
int sort() default 0;
/**
* 如果是字典类型，请设置字典的type值
*/
String dictType() default "";
/**
* 反射类型
*/
Class<?> fieldType() default Class.class;
/**
* 字段归属组（根据分组导出导入）
*/
int[] groups() default {};
}
扫一扫
2591
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
