java实现文件导入导出

文件导入导出必须代码

ExportExcel.java

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;
 

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

  
  private SXSSFWorkbook wb;
   
  
  private Sheet sheet;
   
  
  private Map styles;
   
  
  private int rownum;
   
  
  List annotationList = Lists.newArrayList();
   
  
  linkedHashMap fieldMap;
   
  HashMap dictTypes;
   
  
  public ExportExcel(String title, Class cls){
    this(title, cls, 1);
  }
   
  
  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);
  }
   
  
  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);
  }
   
  
  public ExportExcel(String title, String[] headers) {
    initialize(title, Lists.newArrayList(headers));
  }
   
  
  public ExportExcel(String title, List headerList) {
    initialize(title, 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.");
  }
   
  
  private Map createStyles(Workbook wb) {
    Map styles = 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;
  }
 
  
  public Row addRow(){
    return sheet.createRow(rownum++);
  }
   
 
  
  public Cell addCell(Row row, int column, Object val){
    return this.addCell(row, column, val, 0, Class.class);
  }
   
  
  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;
  }
  
  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;
  }
   
  
  public ExportExcel write(OutputStream os) throws IOException{
    wb.write(os);
    return this;
  }
   
  
  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;
  }
   
  
  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

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;
 

public class importExcel {
   
  private static Logger log = LoggerFactory.getLogger(importExcel.class);

  
  private Workbook wb;
   
  
  private Sheet sheet;
   
  
  private int headerNum;
   
  
  public importExcel(String fileName, int headerNum) 
      throws InvalidFormatException, IOException {
    this(new File(fileName), headerNum);
  }
   
  
  public importExcel(File file, int headerNum) 
      throws InvalidFormatException, IOException {
    this(file, headerNum, 0);
  }
 
  
  public importExcel(String fileName, int headerNum, int sheetIndex) 
      throws InvalidFormatException, IOException {
    this(new File(fileName), headerNum, sheetIndex);
  }
   
  
  public importExcel(File file, int headerNum, int sheetIndex) 
      throws InvalidFormatException, IOException {
    this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
  }
   
  
  public importExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) 
      throws InvalidFormatException, IOException {
    this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
  }
 
  
  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() 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");
   }
   
 }