java实现文件导入导出

网友投稿 208 2023-07-19


java实现文件导入导出

文件导入导出必须代码

ExportExcel.java

/**

* Copyright © 2012-2014 JeeSite All rights reserved.

*/

package com.thinkgem.jeesite.common.utils.excel;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.text.DecimalFormat;

import java.util.Collections;

import java.util.Comparator;

import java.util.Date;

import java.util.HashMap;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Comment;

import org.apache.poi.ss.usermodel.DataFormat;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.IndexedColors;

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.ss.util.CellRangeAddress;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFClientAnchor;

import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import com.google.common.collect.Lists;

import com.thinkgem.jeesite.common.utils.DateUtils;

import com.thinkgem.jeesite.common.utils.Encodes;

import com.thinkgem.jeesite.common.utils.Reflections;

import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;

import com.thinkgem.jeesite.modules.sys.utils.DictUtils;

/**

* 导出Excel文件(导出“XLSX”格式,支持大数据量导出 @see org.apache.poi.ss.SpreadsheetVersion)

* @author ThinkGem

* @version 2013-04-21

*/

public class ExportExcel {

private static Logger log = LoggerFactory.getLogger(ExportExcel.class);

/**

* 工作薄对象

*/

private SXSSFWorkbook wb;

/**

* 工作表对象

*/

private Sheet sheet;

/**

* 样式列表

*/

private Map styles;

/**

* 当前行号

*/

privateGsBIZXxP int rownum;

GsBIZXxP

/**

* 注解列表(Object[]{ ExcelField, Field/Method })

*/

List annotationList = Lists.newArrayList();

/**

* 字段map

*/

LinkedHashMap fieldMap;

HashMap dictTypes;

/**

* 构造函数

* @param title 表格标题,传“空值”,表示无标题

* @param cls 实体对象,通过annotation.ExportField获取标题

*/

public ExportExcel(String title, Class> cls){

this(title, cls, 1);

}

/**

* 构造函数

* @param title 表格标题,传“空值”,表示无标题

* @param fieldMap,获取要导出的字段和字段标题

*/

public ExportExcel(String title, LinkedHashMap fieldMap){

this.fieldMap = fieldMap;

dictTypes = new HashMap();

// Initialize

int colunm = 0;

List headerList = Lists.newArrayList();

for (String key : fieldMap.keySet()){

String t = fieldMap.get(key);

HashMap map = com.thinkgem.jeesite.common.utils.StringUtils.toMap(t, ";", "=", false);

if(map.get("name") != null){

t = map.get("name");

}

if(map.get("dictType") != null){

dictTypes.put(""+(colunm), map.get("dictType"));

}

colunm++;

headerList.add(t);

}

initialize(title, headerList);

}

/**

* 构造函数

* @param title 表格标题,传“空值”,表示无标题

* @param cls 实体对象,通过annotation.ExportField获取标题

* @param type 导出类型(1:导出数据;2:导出模板)

* @param groups 导入分组

*/

public ExportExcel(String title, Class> cls, int type, int... groups){

// Get annotation field

Field[] fs = cls.getDeclaredFields();

for (Field f : fs){

ExcelField ef = f.getAnnotation(ExcelField.class);

if (ef != null && (ef.type()==0 || ef.type()==type)){

if (groups!=null && groups.length>0){

boolean inGroup = false;

for (int g : groups){

if (inGroup){

break;

}

for (int efg : ef.groups()){

if (g == efg){

inGroup = true;

annotationList.add(new Object[]{ef, f});

break;

}

}

}

}else{

annotationList.add(new Object[]{ef, f});

}

}

}

// Get annotation method

Method[] ms = cls.getDeclaredMethods();

for (Method m : ms){

ExcelField ef = m.getAnnotation(ExcelField.class);

if (ef != null && (ef.type()==0 || ef.type()==type)){

if (groups!=null && groups.length>0){

boolean inGroup = false;

for (int g : groups){

if (inGroup){

break;

}

for (int efg : ef.groups()){

if (g == efg){

inGroup = true;

annotationList.add(new Object[]{ef, m});

break;

}

}

}

}else{

annotationList.add(new Object[]{ef, m});

}

}

}

// Field sorting

Collections.sort(annotationList, new Comparator() {

public int compare(Object[] o1, Object[] o2) {

return new Integer(((ExcelField)o1[0]).sort()).compareTo(

new Integer(((ExcelField)o2[0]).sort()));

};

});

// Initialize

List headerList = Lists.newArrayList();

for (Object[] os : annotationList){

String t = ((ExcelField)os[0]).title();

// 如果是导出,则去掉注释

if (type==1){

String[] ss = StringUtils.split(t, "**", 2);

if (ss.length==2){

t = ss[0];

}

}

headerList.add(t);

}

initialize(title, headerList);

}

/**

* 构造函数

* @param title 表格标题,传“空值”,表示无标题

* @param headers 表头数组

*/

public ExportExcel(String title, String[] headers) {

initialize(title, Lists.newArrayList(headers));

}

/**

* 构造函数

* @param title 表格标题,传“空值”,表示无标题

* @param headerList 表头列表

*/

public ExportExcel(String title, List headerList) {

initialize(title, headerList);

}

/**

* 初始化函数

* @param title 表格标题,传“空值”,表示无标题

* @param headerList 表头列表

*/

private void initialize(String title, List headerList) {

this.wb = new SXSSFWorkbook(500);

this.sheet = wb.createSheet("Export");

this.styles = createStyles(wb);

// Create title

if (StringUtils.isNotBlank(title)){

Row titleRow = sheet.createRow(rownum++);

titleRow.setHeightInPoints(30);

Cell titleCell = titleRow.createCell(0);

titleCell.setCellStyle(styles.get("title"));

titleCell.setCellValue(title);

sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),

titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));

}

// Create header

if (headerList == null){

throw new RuntimeException("headerList not null!");

}

Row headerRow = sheet.createRow(rownum++);

headerRow.setHeightInPoints(16);

for (int i = 0; i < headerList.size(); i++) {

Cell cell = headerRow.createCell(i);

cell.setCellStyle(styles.get("header"));

String[] ss = StringUtils.split(headerList.get(i), "**", 2);

if (ss.length==2){

cell.setCellValue(ss[0]);

Comment comment = this.sheet.createDrawingPatriarch().createCellComment(

new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));

comment.setString(new XSSFRichTextString(ss[1]));

cell.setCellComment(comment);

}else{

cell.setCellValue(headerList.get(i));

}

sheet.autoSizeColumn(i);

}

for (int i = 0; i < headerList.size(); i++) {

int colWidth = sheet.getColumnWidth(i)*2;

sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);

}

log.debug("Initialize success.");

}

/**

* 创建表格样式

* @param wb 工作薄对象

* @return 样式列表

*/

private Map createStyles(Workbook wb) {

Map styles =http:// new HashMap();

CellStyle style = wb.createCellStyle();

style.setAlignment(CellStyle.ALIGN_CENTER);

style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

Font titleFont = wb.createFont();

titleFont.setFontName("Arial");

titleFont.setFontHeightInPoints((short) 16);

titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

style.setFont(titleFont);

styles.put("title", style);

style = wb.createCellStyle();

style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

style.setBorderRight(CellStyle.BORDER_THIN);

style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

style.setBorderLeft(CellStyle.BORDER_THIN);

style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

style.setBorderTop(CellStyle.BORDER_THIN);

style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

style.setBorderBottom(CellStyle.BORDER_THIN);

style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

Font dataFont = wb.createFont();

dataFont.setFontName("Arial");

dataFont.setFontHeightInPoints((short) 10);

style.setFont(dataFont);

styles.put("data", style);

style = wb.createCellStyle();

style.cloneStyleFrom(styles.get("data"));

style.setAlignment(CellStyle.ALIGN_LEFT);

styles.put("data1", style);

style = wb.createCellStyle();

style.cloneStyleFrom(styles.get("data"));

style.setAlignment(CellStyle.ALIGN_CENTER);

styles.put("data2", style);

style = wb.createCellStyle();

style.cloneStyleFrom(styles.get("data"));

style.setAlignment(CellStyle.ALIGN_RIGHT);

styles.put("data3", style);

style = wb.createCellStyle();

style.cloneStyleFrom(styles.get("data"));

// style.setWrapText(true);

style.setAlignment(CellStyle.ALIGN_CENTER);

style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());

style.setFillPattern(CellStyle.SOLID_FOREGROUND);

Font headerFont = wb.createFont();

headerFont.setFontName("Arial");

headerFont.setFontHeightInPoints((short) 10);

headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

headerFont.setColor(IndexedColors.WHITE.getIndex());

style.setFont(headerFont);

styles.put("header", style);

return styles;

}

/**

* 添加一行

* @return 行对象

*/

public Row addRow(){

return sheet.createRow(rownum++);

}

/**

* 添加一个单元格

* @param row 添加的行

* @param column 添加列号

* @param val 添加值

* @return 单元格对象

*/

public Cell addCell(Row row, int column, Object val){

return this.addCell(row, column, val, 0, Class.class);

}

/**

* 添加一个单元格

* @param row 添加的行

* @param column 添加列号

* @param val 添加值

* @param align 对齐方式(1:靠左;2:居中;3:靠右)

* @return 单元格对象

*/

public Cell addCell(Row row, int column, Object val, int align, Class> fieldType){

Cell cell = row.createCell(column);

CellStyle style = styles.get("data"+(align>=1&&align<=3?align:""));

try {

if (val == null){

cell.setCellValue("");

} else if (val instanceof String) {

cell.setCellValue((String) val);

} else if (val instanceof Integer) {

cell.setCellValue((Integer) val);

} else if (val instanceof Long) {

cell.setCellValue((Long) val);

} else if (val instanceof Double) {

cell.setCellValue(new DecimalFormat(".#####").format(val));

} else if (val instanceof Float) {

cell.setCellValue((Float) val);

} else if (val instanceof Date) {

DataFormat format = wb.createDataFormat();

style.setDataFormat(format.getFormat("yyyy-MM-dd"));

cell.setCellValue(DateUtils.formatDateTime((Date)val));

} else {

if (fieldType != Class.class){

cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));

}else{

cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),

"fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));

}

}

} catch (Exception ex) {

log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());

cell.setCellValue(val.toString());

}

cell.setCellStyle(style);

return cell;

}

/**

* 添加数据(通过annotation.ExportField添加数据)

* @return list 数据列表

*/

public ExportExcel setDataList(List list){

for (E e : list){

int colunm = 0;

Row row = this.addRow();

StringBuilder sb = new StringBuilder();

if(e instanceof Map){

@SuppressWarnings("unchecked")

Map map = (Map)e;

for(String key : fieldMap.keySet()){

Object value = map.get(key);

String columnDictType = dictTypes.get(colunm+"");

if (StringUtils.isNotBlank(columnDictType)){

value = DictUtils.getDictLabel(value==null?"":value.toString(), columnDictType, "");

}

this.addCell(row, colunm++, value == null ? "" : value.toString(), 0, String.class);

sb.append(value + ", ");

}

}

else{

for (Object[] os : annotationList){

ExcelField ef = (ExcelField)os[0];

Object val = null;

// Get entity value

try{

if (StringUtils.isNotBlank(ef.value())){

val = Reflections.invokeGetter(e, ef.value());

}else{

if (os[1] instanceof Field){

val = Reflections.invokeGetter(e, ((Field)os[1]).getName());

}else if (os[1] instanceof Method){

val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});

}

}

// If is dict, get dict label

if (StringUtils.isNotBlank(ef.dictType())){

val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");

}

}catch(Exception ex) {

// Failure to ignore

log.info(ex.toString());

val = "";

}

this.addCell(row, colunm++, val, ef.align(), ef.fieldType());

sb.append(val + ", ");

}

log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());

}

}

return this;

}

/**

* 输出数据流

* @param os 输出数据流

*/

public ExportExcel write(OutputStream os) throws IOException{

wb.write(os);

return this;

}

/**

* 输出到客户端

* @param fileName 输出文件名

*/

public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{

response.reset();

response.setContentType("application/octet-stream; charset=utf-8");

response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));

write(response.getOutputStream());

return this;

}

/**

* 输出到文件

* @param fileName 输出文件名

*/

public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{

FileOutputStream os = new FileOutputStream(name);

this.write(os);

return this;

}

/**

* 清理临时文件

*/

public ExportExcel dispose(){

wb.dispose();

return this;

}

}

导出测试

public static void main(String[] args) throws Throwable {

List headerList = Lists.newArrayList();

for (int i = 1; i <= 10; i++) {

headerList.add("表头"+i);

}

List dataRowList = Lists.newArrayList();

for (int i = 1; i <= headerList.size(); i++) {

dataRowList.add("数据"+i);

}

List> dataList = Lists.newArrayList();

for (int i = 1; i <=1000000; i++) {

dataList.add(dataRowList);

}

ExportExcel ee = new ExportExcel("表格标题", headerList);

for (int i = 0; i < dataList.size(); i++) {

Row row = ee.addRow();

for (int j = 0; j < dataList.get(i).size(); j++) {

ee.addCell(row, j, dataList.get(i).get(j));

}

}

ee.writeFile("target/export.xlsx");

ee.dispose();

log.debug("Export success.");

}

ImportExcel.java

/**

* Copyright © 2012-2014 JeeSite All rights reserved.

*/

package com.thinkgem.jeesite.common.utils.excel;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.util.Collections;

import java.util.Comparator;

import java.util.Date;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.DateUtil;

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.XSSFWorkbook;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.web.multipart.MultipartFile;

import com.google.common.collect.Lists;

import com.thinkgem.jeesite.common.utils.Reflections;

import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;

import com.thinkgem.jeesite.modules.sys.utils.DictUtils;

/**

* 导入Excel文件(支持“XLS”和“XLSX”格式)

* @author ThinkGem

* @version 2013-03-10

*/

public class ImportExcel {

private static Logger log = LoggerFactory.getLogger(ImportExcel.class);

/**

* 工作薄对象

*/

private Workbook wb;

/**

* 工作表对象

*/

private Sheet sheet;

/**

* 标题行号

*/

private int headerNum;

/**

* 构造函数

* @param path 导入文件,读取第一个工作表

* @param headerNum 标题行号,数据行号=标题行号+1

* @throws InvalidFormatException

* @throws IOException

*/

public ImportExcel(String fileName, int headerNum)

throws InvalidFormatException, IOException {

this(new File(fileName), headerNum);

}

/**

* 构造函数

* @param path 导入文件对象,读取第一个工作表

* @param headerNum 标题行号,数据行号=标题行号+1

* @throws InvalidFormatException

* @throws IOException

*/

public ImportExcel(File file, int headerNum)

throws InvalidFormatException, IOException {

this(file, headerNum, 0);

}

/**

* 构造函数

* @param path 导入文件

* @param headerNum 标题行号,数据行号=标题行号+1

* @param sheetIndex 工作表编号

* @throws InvalidFormatException

* @throws IOException

*/

public ImportExcel(String fileName, int headerNum, int sheetIndex)

throws InvalidFormatException, IOException {

this(new File(fileName), headerNum, sheetIndex);

}

/**

* 构造函数

* @param path 导入文件对象

* @param headerNum 标题行号,数据行号=标题行号+1

* @param sheetIndex 工作表编号

* @throws InvalidFormatException

* @throws IOException

*/

public ImportExcel(File file, int headerNum, int sheetIndex)

throws InvalidFormatException, IOException {

this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);

}

/**

* 构造函数

* @param file 导入文件对象

* @param headerNum 标题行号,数据行号=标题行号+1

* @param sheetIndex 工作表编号

* @throws InvalidFormatException

* @throws IOException

*/

public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)

throws InvalidFormatException, IOException {

this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);

}

/**

* 构造函数

* @param path 导入文件对象

* @param headerNum 标题行号,数据行号=标题行号+1

* @param sheetIndex 工作表编号

* @throws InvalidFormatException

* @throws IOException

*/

public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)

throws InvalidFormatException, IOException {

if (StringUtils.isBlank(fileName)){

throw new RuntimeException("导入文档为空!");

}else if(fileName.toLowerCase().endsWith("xls")){

this.wb = new HSSFWorkbook(is);

}else if(fileName.toLowerCase().endsWith("xlsx")){

this.wb = new XSSFWorkbook(is);

}else{

throw new RuntimeException("文档格式不正确!");

}

if (this.wb.getNumberOfSheets()

throw new RuntimeException("文档中没有工作表!");

}

this.sheet = this.wb.getSheetAt(sheetIndex);

this.headerNum = headerNum;

log.debug("Initialize success.");

}

/**

* 获取行对象

* @param rownum

* @return

*/

public Row getRow(int rownum){

return this.sheet.getRow(rownum);

}

/**

* 获取数据行号

* @return

*/

public int getDataRowNum(){

return headerNum+1;

}

/**

* 获取最后一个数据行号

* @return

*/

public int getLastDataRowNum(){

return this.sheet.getLastRowNum()+headerNum;

}

/**

* 获取最后一个列号

* @return

*/

public int getLastCellNum(){

return this.getRow(headerNum).getLastCellNum();

}

/**

* 获取单元格值

* @param row 获取的行

* @param column 获取单元格列号

* @return 单元格值

*/

public Object getCellValue(Row row, int column){

Object val = "";

try{

Cell cell = row.getCell(column);

if (cell != null){

if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

val = cell.getNumericCellValue();

}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){

val = cell.getStringCellValue();

}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){

val = cell.getCellFormula();

}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){

val = cell.getBooleanCellValue();

}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){

val = cell.getErrorCellValue();

}

}

}catch (Exception e) {

return val;

}

return val;

}

/**

* 获取导入数据列表

* @param cls 导入对象类型

* @param groups 导入分组

*/

public List getDataList(Class cls, int... groups) throws InstantiationException, IllegalAccessException{

List annotationList = Lists.newArrayList();

// Get annotation field

Field[] fs = cls.getDeclaredFields();

for (Field f : fs){

ExcelField ef = f.getAnnotation(ExcelField.class);

if (ef != null && (ef.type()==0 || ef.type()==2)){

if (groups!=null && groups.length>0){

boolean inGroup = false;

for (int g : groups){

if (inGroup){

break;

}

for (int efg : ef.groups()){

if (g == efg){

inGroup = true;

annotationList.add(new Object[]{ef, f});

break;

}

}

}

}else{

annotationList.add(new Object[]{ef, f});

}

}

}

// Get annotation method

Method[] ms = cls.getDeclaredMethods();

for (Method m : ms){

ExcelField ef = m.getAnnotation(ExcelField.class);

if (ef != null && (ef.type()==0 || ef.type()==2)){

if (groups!=null && groups.length>0){

boolean inGroup = false;

for (int g : groups){

if (inGroup){

break;

}

for (int efg : ef.groups()){

if (g == efg){

inGroup = true;

annotationList.add(new Object[]{ef, m});

break;

}

}

}

}else{

annotationList.add(new Object[]{ef, m});

}

}

}

// Field sorting

Collections.sort(annotationList, new Comparator() {

public int compare(Object[] o1, Object[] o2) {

return new Integer(((ExcelField)o1[0]).sort()).compareTo(

new Integer(((ExcelField)o2[0]).sort()));

};

});

//log.debug("Import column count:"+annotationList.size());

// Get excel data

List dataList = Lists.newArrayList();

for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {

E e = (E)cls.newInstance();

int column = 0;

Row row = this.getRow(i);

StringBuilder sb = new StringBuilder();

for (Object[] os : annotationList){

Object val = this.getCellValue(row, column++);

if (val != null){

ExcelField ef = (ExcelField)os[0];

// If is dict type, get dict value

if (StringUtils.isNotBlank(ef.dictType())){

val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");

//log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);

}

// Get param type and type cast

Class> valType = Class.class;

if (os[1] instanceof Field){

valType = ((Field)os[1]).getType();

}else if (os[1] instanceof Method){

Method method = ((Method)os[1]);

if ("get".equals(method.getName().substring(0, 3))){

valType = method.getReturnType();

}else if("set".equals(method.getName().substring(0, 3))){

valType = ((Method)os[1]).getParameterTypes()[0];

}

}

//log.debug("Import value type: ["+i+","+column+"] " + valType);

try {

if (valType == String.class){

String s = String.valueOf(val.toString());

if(StringUtils.endsWith(s, ".0")){

val = StringUtils.substringBefore(s, ".0");

}else{

val = String.valueOf(val.toString());

}

}else if (valType == Integer.class){

val = Double.valueOf(val.toString()).intValue();

}else if (valType == Long.class){

val = Double.valueOf(val.toString()).longValue();

}else if (valType == Double.class){

val = Double.valueOf(val.toString());

}else if (valType == Float.class){

val = Float.valueOf(val.toString());

}else if (valType == Date.class){

val = DateUtil.getJavaDate((Double)val);

}else{

if (ef.fieldType() != Class.class){

val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());

}else{

val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),

"fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());

}

}

} catch (Exception ex) {

log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());

val = null;

}

// set entity value

if (os[1] instanceof Field){

Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);

}else if (os[1] instanceof Method){

String mthodName = ((Method)os[1]).getName();

if ("get".equals(mthodName.substring(0, 3))){

mthodName = "set"+StringUtils.substringAfter(mthodName, "get");

}

Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});

}

}

sb.append(val+", ");

}

dataList.add(e);

log.debug("Read success: ["+i+"] "+sb.toString());

}

return dataList;

}

}

导入测试

public static void main(String[] args) throws Throwable {

ImportExcel ei = new ImportExcel("target/export.xlsx", 1);

for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {

Row row = ei.getRow(i);

for (int j = 0; j < ei.getLastCellNum(); j++) {

Object val = ei.getCellValue(row, j);

System.out.print(val+", ");

}

System.out.print("\n");

}

}

throw new RuntimeException("文档中没有工作表!");

}

this.sheet = this.wb.getSheetAt(sheetIndex);

this.headerNum = headerNum;

log.debug("Initialize success.");

}

/**

* 获取行对象

* @param rownum

* @return

*/

public Row getRow(int rownum){

return this.sheet.getRow(rownum);

}

/**

* 获取数据行号

* @return

*/

public int getDataRowNum(){

return headerNum+1;

}

/**

* 获取最后一个数据行号

* @return

*/

public int getLastDataRowNum(){

return this.sheet.getLastRowNum()+headerNum;

}

/**

* 获取最后一个列号

* @return

*/

public int getLastCellNum(){

return this.getRow(headerNum).getLastCellNum();

}

/**

* 获取单元格值

* @param row 获取的行

* @param column 获取单元格列号

* @return 单元格值

*/

public Object getCellValue(Row row, int column){

Object val = "";

try{

Cell cell = row.getCell(column);

if (cell != null){

if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

val = cell.getNumericCellValue();

}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){

val = cell.getStringCellValue();

}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){

val = cell.getCellFormula();

}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){

val = cell.getBooleanCellValue();

}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){

val = cell.getErrorCellValue();

}

}

}catch (Exception e) {

return val;

}

return val;

}

/**

* 获取导入数据列表

* @param cls 导入对象类型

* @param groups 导入分组

*/

public List getDataList(Class cls, int... groups) throws InstantiationException, IllegalAccessException{

List annotationList = Lists.newArrayList();

// Get annotation field

Field[] fs = cls.getDeclaredFields();

for (Field f : fs){

ExcelField ef = f.getAnnotation(ExcelField.class);

if (ef != null && (ef.type()==0 || ef.type()==2)){

if (groups!=null && groups.length>0){

boolean inGroup = false;

for (int g : groups){

if (inGroup){

break;

}

for (int efg : ef.groups()){

if (g == efg){

inGroup = true;

annotationList.add(new Object[]{ef, f});

break;

}

}

}

}else{

annotationList.add(new Object[]{ef, f});

}

}

}

// Get annotation method

Method[] ms = cls.getDeclaredMethods();

for (Method m : ms){

ExcelField ef = m.getAnnotation(ExcelField.class);

if (ef != null && (ef.type()==0 || ef.type()==2)){

if (groups!=null && groups.length>0){

boolean inGroup = false;

for (int g : groups){

if (inGroup){

break;

}

for (int efg : ef.groups()){

if (g == efg){

inGroup = true;

annotationList.add(new Object[]{ef, m});

break;

}

}

}

}else{

annotationList.add(new Object[]{ef, m});

}

}

}

// Field sorting

Collections.sort(annotationList, new Comparator() {

public int compare(Object[] o1, Object[] o2) {

return new Integer(((ExcelField)o1[0]).sort()).compareTo(

new Integer(((ExcelField)o2[0]).sort()));

};

});

//log.debug("Import column count:"+annotationList.size());

// Get excel data

List dataList = Lists.newArrayList();

for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {

E e = (E)cls.newInstance();

int column = 0;

Row row = this.getRow(i);

StringBuilder sb = new StringBuilder();

for (Object[] os : annotationList){

Object val = this.getCellValue(row, column++);

if (val != null){

ExcelField ef = (ExcelField)os[0];

// If is dict type, get dict value

if (StringUtils.isNotBlank(ef.dictType())){

val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");

//log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);

}

// Get param type and type cast

Class> valType = Class.class;

if (os[1] instanceof Field){

valType = ((Field)os[1]).getType();

}else if (os[1] instanceof Method){

Method method = ((Method)os[1]);

if ("get".equals(method.getName().substring(0, 3))){

valType = method.getReturnType();

}else if("set".equals(method.getName().substring(0, 3))){

valType = ((Method)os[1]).getParameterTypes()[0];

}

}

//log.debug("Import value type: ["+i+","+column+"] " + valType);

try {

if (valType == String.class){

String s = String.valueOf(val.toString());

if(StringUtils.endsWith(s, ".0")){

val = StringUtils.substringBefore(s, ".0");

}else{

val = String.valueOf(val.toString());

}

}else if (valType == Integer.class){

val = Double.valueOf(val.toString()).intValue();

}else if (valType == Long.class){

val = Double.valueOf(val.toString()).longValue();

}else if (valType == Double.class){

val = Double.valueOf(val.toString());

}else if (valType == Float.class){

val = Float.valueOf(val.toString());

}else if (valType == Date.class){

val = DateUtil.getJavaDate((Double)val);

}else{

if (ef.fieldType() != Class.class){

val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());

}else{

val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),

"fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());

}

}

} catch (Exception ex) {

log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());

val = null;

}

// set entity value

if (os[1] instanceof Field){

Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);

}else if (os[1] instanceof Method){

String mthodName = ((Method)os[1]).getName();

if ("get".equals(mthodName.substring(0, 3))){

mthodName = "set"+StringUtils.substringAfter(mthodName, "get");

}

Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});

}

}

sb.append(val+", ");

}

dataList.add(e);

log.debug("Read success: ["+i+"] "+sb.toString());

}

return dataList;

}

}

导入测试

public static void main(String[] args) throws Throwable {

ImportExcel ei = new ImportExcel("target/export.xlsx", 1);

for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {

Row row = ei.getRow(i);

for (int j = 0; j < ei.getLastCellNum(); j++) {

Object val = ei.getCellValue(row, j);

System.out.print(val+", ");

}

System.out.print("\n");

}

}


版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:api风险检测,让AI驱动API安全风险检测与运营
下一篇:微服务网关的架构设计,微服务网关调用怎么做
相关文章

 发表评论

暂时没有评论,来抢沙发吧~