excel工具类

发布时间：2020-12-14 06:22:39 所属栏目：Java 来源：网络整理

导读：来自：https://github.com/SargerasWang/ExcelUtil.git span style="color: #0000ff;"import span style="color: #000000;" org.apache.commons.beanutils.BeanComparator; span style="color: #0000ff;"import span style="color: #000000;" org.apache.com

来自：https://github.com/SargerasWang/ExcelUtil.git

import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.ComparatorUtils;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.apache.commons.collections.comparators.ComparatorChain;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellReference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**

The ExcelUtil 与 {@link ExcelCell}搭配使用
@author sargeras.wang
@version 1.0,Created at 2013年9月14日
*/
public class ExcelUtil {

private static Logger LG = LoggerFactory.getLogger(ExcelUtil.class);

/**
- 用来验证excel与Vo中的类型是否一致
- Map<栏位类型,只能是哪些Cell类型>
 */
 private static Map<Class<?>,Integer[]> validateMap = new HashMap<Class<?>,Integer[]>();
static {
validateMap.put(String[].class,new Integer[]{Cell.CELL_TYPE_STRING});
validateMap.put(Double[].class,new Integer[]{Cell.CELL_TYPE_NUMERIC});
validateMap.put(String.class,new Integer[]{Cell.CELL_TYPE_STRING});
validateMap.put(Double.class,new Integer[]{Cell.CELL_TYPE_NUMERIC});
validateMap.put(Date.class,new Integer[]{Cell.CELL_TYPE_NUMERIC,Cell.CELL_TYPE_STRING});
validateMap.put(Integer.class,new Integer[]{Cell.CELL_TYPE_NUMERIC});
validateMap.put(Float.class,new Integer[]{Cell.CELL_TYPE_NUMERIC});
validateMap.put(Long.class,new Integer[]{Cell.CELL_TYPE_NUMERIC});
validateMap.put(Boolean.class,new Integer[]{Cell.CELL_TYPE_BOOLEAN});
}

/**
- 获取cell类型的文字描述
- @param cellType
- Cell.CELL_TYPE_BLANK
- Cell.CELL_TYPE_BOOLEAN
- Cell.CELL_TYPE_ERROR
- Cell.CELL_TYPE_FORMULA
- Cell.CELL_TYPE_NUMERIC
- Cell.CELL_TYPE_STRING
- @return
 */
 private static String getCellTypeByInt(int cellType) {
 switch (cellType) {
 case Cell.CELL_TYPE_BLANK:
 return "Null type";
 case Cell.CELL_TYPE_BOOLEAN:
 return "Boolean type";
 case Cell.CELL_TYPE_ERROR:
 return "Error type";
 case Cell.CELL_TYPE_FORMULA:
 return "Formula type";
 case Cell.CELL_TYPE_NUMERIC:
 return "Numeric type";
 case Cell.CELL_TYPE_STRING:
 return "String type";
 default:
 return "Unknown type";
 }
 }
/**
- 获取单元格值
- @param cell
- @return
 */
 private static Object getCellValue(Cell cell) {
 if (cell == null
 || (cell.getCellType() == Cell.CELL_TYPE_STRING && StringUtils.isBlank(cell
 .getStringCellValue()))) {
 return null;
 }
 int cellType = cell.getCellType();
 switch (cellType) {
 case Cell.CELL_TYPE_BLANK:
 return null;
 case Cell.CELL_TYPE_BOOLEAN:
 return cell.getBooleanCellValue();
 case Cell.CELL_TYPE_ERROR:
 return cell.getErrorCellValue();
 case Cell.CELL_TYPE_FORMULA:
 return cell.getNumericCellValue();
 case Cell.CELL_TYPE_NUMERIC:
 return cell.getNumericCellValue();
 case Cell.CELL_TYPE_STRING:
 return cell.getStringCellValue();
 default:
 return null;
 }
 }
/**
- 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
- 用于单个sheet
- @param
- @param headers 表格属性列名数组
- @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
- javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
- @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
 */
 public static void exportExcel(String[] headers,Collection dataset,OutputStream out) {
 exportExcel(headers,dataset,out,null);
 }
/**
- 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
- 用于单个sheet
- @param
- @param headers 表格属性列名数组
- @param dataset 需要显示的数据集合,Double[]
- @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
- @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
 */
 public static void exportExcel(String[] headers,OutputStream out,String pattern) {
 // 声明一个工作薄
 HSSFWorkbook workbook = new HSSFWorkbook();
 // 生成一个表格
 HSSFSheet sheet = workbook.createSheet();
 
 write2Sheet(sheet,headers,pattern);
 try {
 workbook.write(out);
 } catch (IOException e) {
 LG.error(e.toString(),e);
 }
 }
public static void exportExcel(String[][] datalist,OutputStream out) {
try {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
```
 ( i = 0; i < datalist.length; i++== ( j = 0; j < r.length; j++=
 (r[j].length() > 32767= "--此字段过长(超过32767),已被截断--" += r[j].substring(0,32766
 (datalist.length > 0 colcount = datalist[0 ( i = 0; i < colcount; i++/**
- 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
- 用于多个sheet
- @param
- @param sheets {@link ExcelSheet}的集合
- @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
 */
 public static void exportExcel(List<ExcelSheet> sheets,OutputStream out) {
 exportExcel(sheets,null);
 }
/**
- 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
- 用于多个sheet
- @param
- @param sheets {@link ExcelSheet}的集合
- @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
- @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
 */
 public static void exportExcel(List<ExcelSheet> sheets,String pattern) {
 if (CollectionUtils.isEmpty(sheets)) {
 return;
 }
 // 声明一个工作薄
 HSSFWorkbook workbook = new HSSFWorkbook();
 for (ExcelSheet sheet : sheets) {
 // 生成一个表格
 HSSFSheet hssfSheet = workbook.createSheet(sheet.getSheetName());
 write2Sheet(hssfSheet,sheet.getHeaders(),sheet.getDataset(),pattern);
 }
 try {
 workbook.write(out);
 } catch (IOException e) {
 LG.error(e.toString(),e);
 }
 }
/**
- 每个sheet的写入
- @param sheet 页签
- @param headers 表头
- @param dataset 数据集合
- @param pattern 日期格式
 */
 private static void write2Sheet(HSSFSheet sheet,String[] headers,String pattern) {
 // 产生表格标题行
 HSSFRow row = sheet.createRow(0);
 for (int i = 0; i < headers.length; i++) {
 HSSFCell cell = row.createCell(i);
 HSSFRichTextString text = new HSSFRichTextString(headers[i]);
 cell.setCellValue(text);
 }
 
 // 遍历集合数据，产生数据行
 Iterator it = dataset.iterator();
 int index = 0;
 while (it.hasNext()) {
 index++;
 row = sheet.createRow(index);
 T t = (T) it.next();
 try {
 if (t instanceof Map) {
 @SuppressWarnings("unchecked")
 Map<String,Object> map = (Map<String,Object>) t;
 int cellNum = 0;
 for (String k : headers) {
 if (map.containsKey(k) == false) {
 LG.error("Map 中不存在 key [" + k + "]");
 continue;
 }
 Object value = map.get(k);
 HSSFCell cell = row.createCell(cellNum);
 cell.setCellValue(String.valueOf(value));
 cellNum++;
 }
 } else {
 List fields = sortFieldByAnno(t.getClass());
 int cellNum = 0;
 for (int i = 0; i < fields.size(); i++) {
 HSSFCell cell = row.createCell(cellNum);
 Field field = fields.get(i).getField();
 field.setAccessible(true);
 Object value = field.get(t);
 String textValue = null;
 if (value instanceof Integer) {
 int intValue = (Integer) value;
 cell.setCellValue(intValue);
 } else if (value instanceof Float) {
 float fValue = (Float) value;
 cell.setCellValue(fValue);
 } else if (value instanceof Double) {
 double dValue = (Double) value;
 cell.setCellValue(dValue);
 } else if (value instanceof Long) {
 long longValue = (Long) value;
 cell.setCellValue(longValue);
 } else if (value instanceof Boolean) {
 boolean bValue = (Boolean) value;
 cell.setCellValue(bValue);
 } else if (value instanceof Date) {
 Date date = (Date) value;
 SimpleDateFormat sdf = new SimpleDateFormat(pattern);
 textValue = sdf.format(date);
 } else if (value instanceof String[]) {
 String[] strArr = (String[]) value;
 for (int j = 0; j < strArr.length; j++) {
 String str = strArr[j];
 cell.setCellValue(str);
 if (j != strArr.length - 1) {
 cellNum++;
 cell = row.createCell(cellNum);
 }
 }
 } else if (value instanceof Double[]) {
 Double[] douArr = (Double[]) value;
 for (int j = 0; j < douArr.length; j++) {
 Double val = douArr[j];
 // 资料不为空则set Value
 if (val != null) {
 cell.setCellValue(val);
 }
```
 (j != douArr.length - 1++=
 String empty == field.getAnnotation(ExcelCell. (anno != == value == ? (textValue != = ++// 设定自动宽度
 for (int i = 0; i < headers.length; i++) {
 sheet.autoSizeColumn(i);
 }
 }
/**
- 把Excel的数据封装成voList
- @param clazz vo的Class
- @param inputStream excel输入流
- @param pattern 如果有时间数据，设定输入格式。默认为"yyy-MM-dd"
- @param logs 错误log集合
- @param arrayCount 如果vo中有数组类型,那就按照index顺序,把数组应该有几个值写上.
- @return voList
- @throws RuntimeException
 */
 @SuppressWarnings("unchecked")
 public static Collection importExcel(Class clazz,InputStream inputStream,String pattern,ExcelLogs logs,Integer... arrayCount) {
 HSSFWorkbook workBook = null;
 try {
 workBook = new HSSFWorkbook(inputStream);
 } catch (IOException e) {
 LG.error(e.toString(),e);
 }
 List list = new ArrayList();
 HSSFSheet sheet = workBook.getSheetAt(0);
 Iterator rowIterator = sheet.rowIterator();
 try {
 List logList = new ArrayList();
 // Map<title,index>
 Map<String,Integer> titleMap = new HashMap<>();
```
 = (row.getRowNum() == 0 (clazz == Map.
 Iterator<Cell> cellIterator == 0=++
 allRowIsNull = <Cell> cellIterator == (cellValue != = "Excel row " + row.getRowNum() + " all row value is null!"= = (clazz == Map.<String,Object> map = HashMap<String,Object>=== arrayIndex = 0;
 cellIndex = 0;
 List<FieldForSortting> fields ==== (field.getType().equals(String[].= 
 value = ( i = 0; i < count; i++===";"++++=== 
 (field.getType().equals(Date.&amp;&amp; cell.getCellType() ==== ="the cell [{0}] can not be converted to a date "=
 ExcelCell annoCell = field.getAnnotation(ExcelCell. (value String &amp;&amp; !field.getType().equals(String.&amp;&amp;=";"++ ExcelLog(t,log.toString(),row.getRowNum() + 1catch (InstantiationException e) {
 throw new RuntimeException(MessageFormat.format("can not instance class:{0}",clazz.getSimpleName()),e);
 } catch (IllegalAccessException e) {
 throw new RuntimeException(MessageFormat.format("can not instance class:{0}",e);
 }
 return list;
 }
/**
- 驗證Cell類型是否正確
- @param cell cell單元格
- @param field 欄位
- @param cellNum 第幾個欄位,用於errMsg
- @return
 */
 private static String validateCell(Cell cell,Field field,int cellNum) {
 String columnName = CellReference.convertNumToColString(cellNum);
 String result = null;
 Integer[] integers = validateMap.get(field.getType());
 if (integers == null) {
 result = MessageFormat.format("Unsupported type [{0}]",field.getType().getSimpleName());
 return result;
 }
 ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
 if (cell == null
 || (cell.getCellType() == Cell.CELL_TYPE_STRING && StringUtils.isBlank(cell
 .getStringCellValue()))) {
 if (annoCell != null && annoCell.valid().allowNull() == false) {
 result = MessageFormat.format("the cell [{0}] can not null",columnName);
 }
 ;
 } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK && annoCell.valid().allowNull()) {
 return result;
 } else {
 List cellTypes = Arrays.asList(integers);
```
 
 (!||&amp;&amp; cell.getCellType() === ( i = 0; i < cellTypes.size(); i++= (i != cellTypes.size() - 1","="the cell [{0}] type must [{1}]"
 (annoCell.valid().in().length != 0 &amp;&amp; cell.getCellType() ==== isIn = = (!= MessageFormat.format("the cell [{0}] value must in {1}"
 (cell.getCellType() == cellValue =
 (! (!(cellValue <="the cell [{0}] value must less than [{1}]"
 (! (!(cellValue >="the cell [{0}] value must greater than [{1}]"
 (! (!(cellValue <=="the cell [{0}] value must less than or equal [{1}]"
 (! (!(cellValue >=="the cell [{0}] value must greater than or equal [{1}]"return result;
 }
/**
- 根据annotation的seq排序后的栏位
- @param clazz
- @return
 */
 private static List sortFieldByAnno(Class<?> clazz) {
 Field[] fieldsArr = clazz.getDeclaredFields();
 List fields = new ArrayList();
 List annoNullFields = new ArrayList();
 for (Field field : fieldsArr) {
 ExcelCell ec = field.getAnnotation(ExcelCell.class);
 if (ec == null) {
 // 没有ExcelCell Annotation 视为不汇入
 continue;
 }
 int id = ec.index();
 fields.add(new FieldForSortting(field,id));
 }
 fields.addAll(annoNullFields);
 sortByProperties(fields,true,false,"index");
 return fields;
 }
@SuppressWarnings("unchecked")
private static void sortByProperties(List<? extends Object> list,boolean isNullHigh,boolean isReversed,String... props) {
if (CollectionUtils.isNotEmpty(list)) {
Comparator<?> typeComp = ComparableComparator.getInstance();
if (isNullHigh == true) {
typeComp = ComparatorUtils.nullHighComparator(typeComp);
} else {
typeComp = ComparatorUtils.nullLowComparator(typeComp);
}
if (isReversed) {
typeComp = ComparatorUtils.reversedComparator(typeComp);
}
```
 List<Object> sortCols = ArrayList<Object> (props != (sortCols.size() > 0<Object> sortChain = <span style="color: #000000;"&gt; ComparatorChain(sortCols);
 Collections.sort(list,sortChain);
 }
 }
```
}

}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!