package cn.timer.api.utils;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
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.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.multipart.MultipartFile;
public class ExcelUtil<T> {
/**
* HSSFWorkbook excel 的文档对象 HSSFSheet excel 的表单 HSSFRow excel 的行 HSSFCell excel
* 的格子单元
*/
/**
* 导出xls文件
*
* @param title sheet标题
* @param headers 第一行标题
* @param dataset 集合数据/表单内容
* @param out
* @param pattern
*/
public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {
// 创建工作簿
// SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建SHEET页/表格页，title-表格标题
HSSFSheet sheet = (HSSFSheet) workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 创建行，从0开始
HSSFRow row = (HSSFRow) sheet.createRow(0);
// 创建单元格样式对象(设置样式)
HSSFCellStyle ztStyle = workbook.createCellStyle();
// 创建字体对象
ztStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
ztStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
ztStyle.setFillForegroundColor((short) 42);// 前景填充色
// ztStyle.setFillForegroundColor(IndexedColors.YELLOW.index);//前景填充色-黄色
ztStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 设置前景填充样式
// 边框
ztStyle.setBorderBottom(BorderStyle.THIN);// 下边框
ztStyle.setBorderLeft(BorderStyle.THIN);// 左边框
ztStyle.setBorderRight(BorderStyle.THIN);// 右边框
ztStyle.setBorderTop(BorderStyle.THIN); // 上边框
HSSFCellStyle namestyle = workbook.createCellStyle();
namestyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
namestyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
namestyle.setBorderBottom(BorderStyle.THIN);// 下边框
namestyle.setBorderLeft(BorderStyle.THIN);// 左边框
namestyle.setBorderRight(BorderStyle.THIN);// 右边框
namestyle.setBorderTop(BorderStyle.THIN); // 上边框
for (short i = 0; i < headers.length; i++) {
// 创建单元格
HSSFCell cell = row.createCell(i);
row.setHeightInPoints(30); // 设置行高
sheet.createFreezePane(0, 1);// 冻结第1行
cell.setCellValue(headers[i]);// 设置每一列的值
cell.setCellStyle(ztStyle);// 样式应用到该单元格上
/**
* 四个参数的含义：
*
* ａ表示要冻结的列数；
*
* ｂ表示要冻结的行数；
*
* ｃ表示右边区域[可见]的首列序号；
*
* ｄ表示下边区域[可见]的首行序号；
*/
// sheet.createFreezePane(a,b,c,d);
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);// 创建第 index 行
T t = it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
// 动态拼接getXxx获取属性值方法 如：TUserLand类 loginname 动态获取 -》 getLoginname 这个方法名
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Class<? extends Object> tclass = t.getClass();
Method getMethod = tclass.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
String textValue = null;
if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else if (fieldName.length() >= 4
&& "Time".equalsIgnoreCase(fieldName.substring(fieldName.length() - 4))) {
textValue = DateFormatUtils.getStringByTimestampAndParm((Long) value, "YYYY-MM-DD");
} else {
// 其它数据类型都当作字符串简单处理
textValue = value != null ? value.toString() : "";
}
// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null) {
Pattern p = Pattern.compile("^//d+(//.//d+)?$");// 正则表达式
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
cell.setCellValue(textValue);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 清理资源
}
row.setHeightInPoints(25);// 设置行高为25(猜测单位为px)
cell.setCellStyle(namestyle); // 渲染单元格
}
}
try {
workbook.write(out);
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
@SuppressWarnings("unused")
public Collection<T> importExcel(File file, Collection<T> dataset) throws Exception {
// 装载流
POIFSFileSystem fs = new POIFSFileSystem(((MultipartFile) file).getInputStream());
HSSFWorkbook hw = new HSSFWorkbook(fs);
// 获取第一个sheet页
HSSFSheet sheet = hw.getSheetAt(0);
// 创建容器
//List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();
dataset = new ArrayList<T>(sheet.getLastRowNum()-1);
Iterator<T> it = dataset.iterator();
// 遍历行 从下标第一行开始（去除标题）
for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
T t = it.next();
Field[] fields = t.getClass().getDeclaredFields();
HSSFRow row = sheet.getRow(i);
if (row != null) {
// 装载obj
for (int k = 0; k < row.getLastCellNum(); k++) {
HSSFCell cell = row.getCell(k);
Field field = fields[k];
String fieldName = field.getName();
//动态拼接getXxx获取属性值方法 如：TUserLand类 loginname 动态获取 -》 setLoginname 这个方法名
String getMethodName = "set"+ fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1);
Class<? extends Object> tclass = t.getClass();
Method getMethod = tclass.getMethod(getMethodName,new Class[] {});
getMethod.invoke(t, ExcelUtil.cellFormat(cell));
String textValue = null;
}
//ret.add(dataObj(dataset, row));
}
}
hw.close();
return dataset;
}
@SuppressWarnings("deprecation")
private static String cellFormat(HSSFCell cell){
String result = new String();
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
String cellValue = new DecimalFormat("0").format(cell.getNumericCellValue());
if (cellValue.lastIndexOf("E") > -1) {
cell.setCellValue(new DecimalFormat("#").format(cell.getNumericCellValue()));
}
String str = String.valueOf(cell);
if (str.indexOf(".0") != -1 || str.indexOf("-") != -1) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyyMMdd");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
cell.setCellValue(result);
} else if (cell.getCellStyle().getDataFormat() == 176) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
result = DateFormatUtils.tranDate(date, "yyyyMMdd");
cell.setCellValue(result);
} else if (cell.getCellStyle().getDataFormat() == 14
|| cell.getCellStyle().getDataFormat() == 31) {
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
result = DateFormatUtils.tranDate(date, "yyyyMMdd");
cell.setCellValue(result);
}
System.out.println(result);
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
// System.out.print(cell.getStringCellValue()
// + " ");
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
// System.out.println(cell.getBooleanCellValue()
// + " ");
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
// System.out.print(cell.getCellFormula() + " ");
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
// System.out.println(" ");
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
// System.out.println(" ");
break;
default:
// System.out.print("未知类型 ");
break;
}
return result;
}
return null;
}
@SuppressWarnings("unused")
private static Map<String, Object> dataObj(Object obj, HSSFRow row) throws Exception {
Class<?> rowClazz = obj.getClass();
Field[] fields = FieldUtils.getAllFields(rowClazz);
if (fields == null || fields.length < 1) {
return null;
}
// 容器
Map<String, Object> map = new HashMap<String, Object>();
// 注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）
for (int j = 0; j < fields.length; j++) {
map.put(fields[j].getName(), getVal(row.getCell(j)));
}
return map;
}
@SuppressWarnings("deprecation")
public static String getVal(HSSFCell hssfCell) {
if (hssfCell != null) {
hssfCell.setCellType(Cell.CELL_TYPE_STRING);
return hssfCell.getStringCellValue();
} else {
return "";
}
}
}