java poi导入导出excel公共模块工具类-2018-09-05最新更新

package cn.com.flaginfo.biz.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.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Collections; import java.util.Date; import java.util.List; import java.util.logging.Logger; import java.util.regex.Pattern; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Font; 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.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.LoggerFactory; import com.alibaba.fastjson.JSON; import cn.com.flaginfo.biz.excel.annotation.entity.ImportTableParams; import cn.com.flaginfo.biz.excel.annotation.entity.TableParams; import cn.com.flaginfo.biz.excel.annotation.exception.ExcelException; import cn.com.flaginfo.biz.excel.annotation.util.ExcelField; import cn.com.flaginfo.biz.excel.annotation.util.ExcelImportField; import cn.com.flaginfo.biz.excel.annotation.validate.Alias; import cn.com.flaginfo.biz.excel.annotation.validate.AliasItem; import cn.com.flaginfo.biz.excel.annotation.validate.CustomValidate; import cn.com.flaginfo.biz.excel.annotation.validate.Email; import cn.com.flaginfo.biz.excel.annotation.validate.IdCard; import cn.com.flaginfo.biz.excel.annotation.validate.IsChinese; import cn.com.flaginfo.biz.excel.annotation.validate.IsNum; import cn.com.flaginfo.biz.excel.annotation.validate.IsUrl; import cn.com.flaginfo.biz.excel.annotation.validate.Max; import cn.com.flaginfo.biz.excel.annotation.validate.Min; import cn.com.flaginfo.biz.excel.annotation.validate.NotNull; import cn.com.flaginfo.biz.excel.annotation.validate.Phone; import cn.com.flaginfo.biz.excel.annotation.validate.Range; import cn.com.flaginfo.biz.excel.annotation.validate.Telephone; /** * EXCEL操作工具类 * @author huangjinhui * */ @SuppressWarnings("resource") public class FlagInfoExcelUtils{ private static final org.slf4j.Logger LOGGER = LoggerFactory.getLogger(FlagInfoExcelUtils.class); /*标题*/ private String title; /*标题行高*/ private Integer titleRow; /*当前行的下标*/ private int currRowIndex; /*开始读取的行数*/ private int startLine; /*结束行数*/ private int endLine; /*当前列的下标*/ private int curCollIndex; private Sheet sheet; /*当前列*/ private Cell curCell; /*当前工作簿下标*/ private int currSheetIndex; /*当前行*/ private Row curRow; public Workbook wb; /*标题所在的行*/ private Integer titleLine; @SuppressWarnings("rawtypes") private static UploadDataResultDto uploadDataResultDto; /*获取实例*/ @SuppressWarnings("rawtypes") public static FlagInfoExcelUtils getInstance(){ uploadDataResultDto = new UploadDataResultDto(); return new FlagInfoExcelUtils(); } //==========================<<<<<EXCEL 导出 START>>>>>======================= /** * 不带标题的导出 * @param clz 导出对象的class * @param list 导出集合 * @param isXssf 是否是xlsx * @param sheetName工作簿名称 * @return * @throws ExcelException */ @Deprecated public Workbook exportByObj(Class clz, List list, boolean isXssf,String sheetName) throws ExcelException { return this.exportByObj(clz, list, isXssf,null,null,sheetName); } /** * 带标题的导出 * @param clz 导出对象的class * @param list 导出集合 * @param isXssf 是否是xlsx * @param title 标题 * @param titleRow 标题行高 * @param sheetName工作簿名称 * @return * @throws ExcelException */ public Workbook exportByObj( Class clz, List list, boolean isXssf,TableParams tableparams) throws ExcelException { return this.exportByObj(clz,list,isXssf,tableparams.getTitle(),tableparams.getTitleHeight(),tableparams.getSheetName()); } @Deprecated @SuppressWarnings("rawtypes") public Workbook exportByObj( Class clz, List list, boolean isXssf,String title,Integer titleRow,String sheetName) throws ExcelException { //判断是创建 xlsx 或者 xls if(isXssf){ /*xlsx*/ wb = new XSSFWorkbook(); }else{ /*xls*/ wb = new HSSFWorkbook(); } CellStyle cellStyle = wb.createCellStyle(); Font font = wb.createFont(); /*创建工作簿*/ Sheet sheet = wb.createSheet(sheetName); sheet.autoSizeColumn(1); //sheet.setColumnWidth(columnIndex, width); /*处理标题 设置标题高度*/ if(!StringUtils.isEmpty(title)&& titleRow != null){ this.titleRow = titleRow; this.title = title; } /*通过注解，对导出对象的标题，属性，排序进行设置*/ List<ExcelHeader> headers = getExcelHeaders(clz); /*设置标题*/ setTitle(title,titleRow,sheet,headers,cellStyle,font); /*设置导航标题*/ setHeader(sheet, headers); List<CellStyle> cellStyles = null; /*创建每一列的样式*/ if(!CollectionUtils.isEmpty(headers)){ cellStyles = getStyles(font, headers); } Row row; if(list != null && list.size() >0){ for(int i = 0 ;i < list.size();i++){ row =sheet.createRow(i+currRowIndex); /*开始给每一列进行赋值*/ for(int j = 0; j < headers.size(); j++){ String methodStr = getMethod(headers.get(j)); try { Method method = clz.getMethod(methodStr,null); Object value = method.invoke(list.get(i), null); if (value != null){ Cell createCell = row.createCell(j); //判断是否要加样式 if (cellStyles != null && !CollectionUtils.isEmpty(cellStyles) && cellStyles.get(j) != null){ createCell.setCellStyle(cellStyles.get(j)); } createCell.setCellValue(value.toString()); }else{ } } catch (Exception e) { LOGGER.error("export Excel by Obj ====>>>> Exception：{}",e); throw new ExcelException(e); } } } } return wb; } private List<CellStyle> getStyles(Object o, List<ExcelHeader> headers) { List<CellStyle> cellStyles = new ArrayList<CellStyle>(); for (ExcelHeader header : headers){ CellStyle style = wb.createCellStyle(); //设置样式 //字体颜色 Font font1 = wb.createFont(); font1.setFontName("仿宋_GB2312"); font1.setColor(header.getColor()); style.setFont(font1);//选择需要用到的字体格式 //align style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); if("left".equals(header.getAlign())){ style.setAlignment(CellStyle.ALIGN_LEFT); // }else if("center".equals(header.getAlign())){ style.setAlignment(CellStyle.ALIGN_CENTER); // 居中 }else if("right".equals(header.getAlign())){ style.setAlignment(CellStyle.ALIGN_RIGHT); // } cellStyles.add(style); } return cellStyles; } /** * 根据属性名获取get方法名称 * @param header * @return */ private String getMethod(ExcelHeader header) { String fieldName =header.getFieldName(); fieldName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1); return fieldName; } private void setHeader(Sheet sheet, List<ExcelHeader> headers