java實現(xiàn)文件導入導出
更新時間：2016年04月10日 09:49:22 作者：黃宇晨
這篇文章主要介紹了java實現(xiàn)文件導入導出的方法和具體示例代碼，非常的簡單實用，有需要的小伙伴可以參考下
文件導入導出必須代碼
ExportExcel.java
/**
* Copyright © 2012-2014 <a >JeeSite</a> 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”格式，支持大數(shù)據(jù)量導出 @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<String, CellStyle> styles;
/**
* 當前行號
*/
private int rownum;
/**
* 注解列表（Object[]{ ExcelField, Field/Method }）
*/
List<Object[]> annotationList = Lists.newArrayList();
/**
* 字段map
*/
LinkedHashMap<String,String> fieldMap;
HashMap<String, String> dictTypes;
/**
* 構(gòu)造函數(shù)
* @param title 表格標題，傳“空值”，表示無標題
* @param cls 實體對象，通過annotation.ExportField獲取標題
*/
public ExportExcel(String title, Class<?> cls){
this(title, cls, 1);
}
/**
* 構(gòu)造函數(shù)
* @param title 表格標題，傳“空值”，表示無標題
* @param fieldMap，獲取要導出的字段和字段標題
*/
public ExportExcel(String title, LinkedHashMap<String,String> fieldMap){
this.fieldMap = fieldMap;
dictTypes = new HashMap<String,String>();
// Initialize
int colunm = 0;
List<String> headerList = Lists.newArrayList();
for (String key : fieldMap.keySet()){
String t = fieldMap.get(key);
HashMap<String, String> 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);
}
/**
* 構(gòu)造函數(shù)
* @param title 表格標題，傳“空值”，表示無標題
* @param cls 實體對象，通過annotation.ExportField獲取標題
* @param type 導出類型（1:導出數(shù)據(jù)；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<Object[]>() {
public int compare(Object[] o1, Object[] o2) {
return new Integer(((ExcelField)o1[0]).sort()).compareTo(
new Integer(((ExcelField)o2[0]).sort()));
};
});
// Initialize
List<String> 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);
}
/**
* 構(gòu)造函數(shù)
* @param title 表格標題，傳“空值”，表示無標題
* @param headers 表頭數(shù)組
*/
public ExportExcel(String title, String[] headers) {
initialize(title, Lists.newArrayList(headers));
}
/**
* 構(gòu)造函數(shù)
* @param title 表格標題，傳“空值”，表示無標題
* @param headerList 表頭列表
*/
public ExportExcel(String title, List<String> headerList) {
initialize(title, headerList);
}
/**
* 初始化函數(shù)
* @param title 表格標題，傳“空值”，表示無標題
* @param headerList 表頭列表
*/
private void initialize(String title, List<String> 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.");
}
/**
* 創(chuàng)建表格樣式
* @param wb 工作薄對象
* @return 樣式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
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;
}
/**
* 添加數(shù)據(jù)（通過annotation.ExportField添加數(shù)據(jù)）
* @return list 數(shù)據(jù)列表
*/
public <E> ExportExcel setDataList(List<E> list){
for (E e : list){
int colunm = 0;
Row row = this.addRow();
StringBuilder sb = new StringBuilder();
if(e instanceof Map){
@SuppressWarnings("unchecked")
Map<String,Object> map = (Map<String,Object>)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;
}
/**
* 輸出數(shù)據(jù)流
* @param os 輸出數(shù)據(jù)流
*/
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<String> headerList = Lists.newArrayList();
for (int i = 1; i <= 10; i++) {
headerList.add("表頭"+i);
}
List<String> dataRowList = Lists.newArrayList();
for (int i = 1; i <= headerList.size(); i++) {
dataRowList.add("數(shù)據(jù)"+i);
}
List<List<String>> 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 <a >JeeSite</a> 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;
/**
* 構(gòu)造函數(shù)
* @param path 導入文件，讀取第一個工作表
* @param headerNum 標題行號，數(shù)據(jù)行號=標題行號+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum);
}
/**
* 構(gòu)造函數(shù)
* @param path 導入文件對象，讀取第一個工作表
* @param headerNum 標題行號，數(shù)據(jù)行號=標題行號+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum)
throws InvalidFormatException, IOException {
this(file, headerNum, 0);
}
/**
* 構(gòu)造函數(shù)
* @param path 導入文件
* @param headerNum 標題行號，數(shù)據(jù)行號=標題行號+1
* @param sheetIndex 工作表編號
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum, sheetIndex);
}
/**
* 構(gòu)造函數(shù)
* @param path 導入文件對象
* @param headerNum 標題行號，數(shù)據(jù)行號=標題行號+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);
}
/**
* 構(gòu)造函數(shù)
* @param file 導入文件對象
* @param headerNum 標題行號，數(shù)據(jù)行號=標題行號+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);
}
/**
* 構(gòu)造函數(shù)
* @param path 導入文件對象
* @param headerNum 標題行號，數(shù)據(jù)行號=標題行號+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()<sheetIndex){
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);
}
/**
* 獲取數(shù)據(jù)行號
* @return
*/
public int getDataRowNum(){
return headerNum+1;
}
/**
* 獲取最后一個數(shù)據(jù)行號
* @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;
}
/**
* 獲取導入數(shù)據(jù)列表
* @param cls 導入對象類型
* @param groups 導入分組
*/
public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
List<Object[]> 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<Object[]>() {
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<E> 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");
}
}
相關(guān)文章
Java?將list集合數(shù)據(jù)按照時間字段排序的方法
這篇文章主要介紹了Java?將list集合數(shù)據(jù)按照時間字段排序,本文通過示例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-03-03
在項目開發(fā)中，肯定少不了異常的出現(xiàn)，作為后臺開發(fā)人員，我們總是在不停的寫各種接口提供給前端調(diào)用，然而不可避免的，當后臺出現(xiàn)BUG時，前端總是丑陋的講錯誤信息直接暴露給用戶，這樣的用戶體驗想必是相當差的，本文主要講解異常映射的配置2021-06-06
這篇文章主要為大家詳細介紹了Java實現(xiàn)滑動驗證碼生成功能中的后端工具類部分，文中的示例代碼講解詳細，感興趣的小伙伴可以跟隨小編一起學習一下2022-10-10
這篇文章主要為大家介紹了IntelliJ中高效重構(gòu)的10個快捷方式詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-01-01
@RequestBody,@RequestParam和@Param的區(qū)別說明
這篇文章主要介紹了@RequestBody,@RequestParam和@Param的區(qū)別說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-03-03
在編程中,日期格式化是一個常見的任務,使用不同的格式化選項可能會導致一些意外的結(jié)果,下面我們就來學習一下Java如何避免YYYY引發(fā)的時間異常吧2023-11-11
這篇文章主要介紹了SSpringsecurity登錄過程邏輯詳解,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-04-04
SpringBoot整合WebSocket實現(xiàn)實時通信功能
在當今互聯(lián)網(wǎng)時代,實時通信已經(jīng)成為了許多應用程序的基本需求,而WebSocket作為一種全雙工通信協(xié)議,為開發(fā)者提供了一種簡單、高效的實時通信解決方案,本文將介紹如何使用SpringBoot框架來實現(xiàn)WebSocket的集成,快速搭建實時通信功能,感興趣的朋友可以參考下2023-11-11
這篇文章主要介紹了Java算法之重新排列數(shù)組例題，文章圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，感興趣的小伙伴可以參考一下2022-08-08
Java NIO（New I/O）是Java 7中引入的一項重要特性,旨在提供一種更加靈活和高效的文件處理方式,NIO.2主要通過Path、Paths和Files三個核心組件來實現(xiàn)對文件和目錄的操作,本文給大家介紹java 文件的操作Path、Paths、Files的相關(guān)知識,感興趣的朋友一起看看吧2024-10-10