package com.framework.util.excel;
import com.framework.util.MathUtil;
import com.framework.util.PropertiesLoader;
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.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipOutputStream;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Created by cohesion on 2016/9/26.
*/
public class ExcelUtils {
public static Logger LG = LoggerFactory.getLogger(ExcelUtil.class);
public final static String excel_format_xls = ".xls";
public final static String excel_format_xlsx = ".xlsx";
//默认导出方法
public static <T> void exportExcel(String[] headers, List<Collection<T>> excels, HttpServletRequest request, HttpServletResponse response) {
exportExcel(null, headers, excels, null, request, response);
}
//带有表格样式的导出方法（仅限第一行）
public static <T> void exportExcel(String[] headers, List<Collection<T>> excels, Map<String, Object> colorMap, HttpServletRequest request, HttpServletResponse response) {
exportExcel(null, headers, excels, colorMap, request, response);
}
//带有表格样式的导出方法（仅限第一行）
public static String exportExcel(String[] headers, String fileName, List<Map<String, Object>> excel, Map<String, Object> colorMap) {
return exportExcel(null, headers, fileName, excel, colorMap);
}
//带有文件类型的导出方法
public static <T> void exportExcel(String pathName , String fileType, String[] headers, List<Collection<T>> excels, HttpServletRequest request, HttpServletResponse response) {
exportExcel(pathName , fileType, headers, excels, null, request, response);
}
//生成目录
public static <T> void exportExcel(String fileType, String[] headers, List<Collection<T>> excels, Map<String, Object> colorMap, HttpServletRequest request, HttpServletResponse response) {
//String ctxPath = request.getSession().getServletContext().getRealPath("");
String uploadPath = PropertiesLoader.get("UPLOAD_PATH");
//String zipReturnPath = "dd/zip/" + UUID.randomUUID().toString() + ".zip";
List<String> temporaryFilePaths = new ArrayList<>();
String uploadFilePath = "";
if (org.springframework.util.CollectionUtils.isEmpty(excels)) {
return;
}
for (Collection<T> collection : excels) {
String path = "/";
path += System.currentTimeMillis();
File file = new File(uploadPath + path);
file.getParentFile().mkdirs();
// 声明一个工作薄
if ("xls".equals(fileType)) {
path += excel_format_xls;
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsSheet(sheet, headers, collection, null, colorMap);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
} else {
path += excel_format_xlsx;
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsxSheet(sheet, headers, collection, null, colorMap);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//uploadFilePath
uploadFilePath = uploadPath + path;
//temporaryFilePaths
temporaryFilePaths.add(uploadFilePath);
}
//压缩
if (excels.size() > 1) {
uploadFilePath = uploadPath + "/zip" + UUID.randomUUID().toString() + ".zip";
temporaryFilePaths = zipFiles(uploadFilePath, uploadPath, temporaryFilePaths);
}
//下载
downloadFile(uploadFilePath, request, response);
//删除临时文件
deleteSheet(temporaryFilePaths);
}
public static <T> void exportExcel(String pathName,String fileType, String[] headers, List<Collection<T>> excels, Map<String, Object> colorMap, HttpServletRequest request, HttpServletResponse response) {
//String ctxPath = request.getSession().getServletContext().getRealPath("");
String uploadPath = PropertiesLoader.get("UPLOAD_PATH");
//String zipReturnPath = "dd/zip/" + UUID.randomUUID().toString() + ".zip";
List<String> temporaryFilePaths = new ArrayList<>();
String uploadFilePath = "";
if (org.springframework.util.CollectionUtils.isEmpty(excels)) {
return;
}
for (Collection<T> collection : excels) {
String path = "";
path += pathName;
File file = new File(uploadPath + path);
file.getParentFile().mkdirs();
// 声明一个工作薄
if ("xls".equals(fileType)) {
path += excel_format_xls;
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsSheet(sheet, headers, collection, null, colorMap);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
} else {
path += excel_format_xlsx;
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsxSheet(sheet, headers, collection, null, colorMap);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//uploadFilePath
uploadFilePath = uploadPath + path;
//temporaryFilePaths
temporaryFilePaths.add(uploadFilePath);
}
//压缩
if (excels.size() > 1) {
uploadFilePath = uploadPath + "/zip" + UUID.randomUUID().toString() + ".zip";
temporaryFilePaths = zipFiles(uploadFilePath, uploadPath, temporaryFilePaths);
}
//下载
downloadFile(uploadFilePath, request, response);
//删除临时文件
deleteSheet(temporaryFilePaths);
}
public static String exportExcel(String fileType, String[] headers, String fileName, List<Map<String, Object>> excel, Map<String, Object> colorMap) {
String uploadPath = PropertiesLoader.get("UPLOAD_PATH");
String url = "";
if (org.springframework.util.CollectionUtils.isEmpty(excel)) {
return url;
}
String path = "";
if (StringUtils.isNotEmpty(fileName)) {
path += fileName;
} else {
path += System.currentTimeMillis();
}
File file = new File(uploadPath + path);
file.getParentFile().mkdirs();
// 声明一个工作薄
if ("xls".equals(fileType)) {
path += excel_format_xls;
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsSheet(sheet, headers, excel, null, colorMap);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
} else {
path += excel_format_xlsx;
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsxSheet(sheet, headers, excel, null, colorMap);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//生成文件地址
url = uploadPath + path;
return url;
}
//压缩文件
public static List<String> zipFiles(String uploadFilePath, String ctxPath, List<String> temporaryFilePaths) {
File zipFile = new File(uploadFilePath);
zipFile.getParentFile().mkdirs();
try {
zipFile.createNewFile();
ZipEntry ze = null;
ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(uploadFilePath));
int readLen = 0;
byte[] buf = new byte[1024 * 8];
for (String excelPath : temporaryFilePaths) {
File f = new File(excelPath);//已生成的excel文件
if (f.exists()) {
ze = new ZipEntry(f.getName());//创建压缩条目
ze.setSize(f.length());
ze.setTime(f.lastModified());
zos.putNextEntry(ze);
InputStream is = new BufferedInputStream(new FileInputStream(excelPath));//读取excel文件内容到流
while ((readLen = is.read(buf, 0, 1024 * 8)) != -1) {
zos.write(buf, 0, readLen);
}
is.close();
}
}
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
//zip临时文件
temporaryFilePaths.add(uploadFilePath);
return temporaryFilePaths;
}
//删除临时文件
public static void deleteSheet(List<String> filePaths) {
if (org.springframework.util.CollectionUtils.isEmpty(filePaths)) {
return;
}
for (String filePath : filePaths) {
File file = new File(filePath);
if (file.isFile() && file.exists()) {
file.delete();
}
}
}
//下载文件
public static void downloadFile(String uploadFilePath, HttpServletRequest request, HttpServletResponse response) {
try {
File file = new File(uploadFilePath);
BufferedInputStream bis;
BufferedOutputStream bos;
//设置文件输出类型
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment; filename="
+ new String(file.getName().getBytes("utf-8"), "ISO8859-1"));
//设置输出长度
response.setHeader("Content-Length", String.valueOf(file.length()));
//获取输入流
bis = new BufferedInputStream(new FileInputStream(file));
//输出流
bos = new BufferedOutputStream(response.getOutputStream());
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
//关闭流
bis.close();
bos.close();
} catch (IOException e) {
LG.error(e.toString(), e);
}
}
/**
* 每个xlsSheet的写入
*
* @param sheet 页签
* @param headers 表头
* @param dataset 数据集合
* @param pattern 日期格式
*/
public static <T> void write2XlsSheet(HSSFSheet sheet, String[] headers, Collection<T> dataset,
String pattern, Map<String, Object> colorMap) {
// 产生表格标题行
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);
HSSFFont font = sheet.getWorkbook().createFont();
//font.setFontName("Consolas");
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 14);//设置字体大小
HSSFCellStyle style = sheet.getWorkbook().createCellStyle();
style.setFont(font);
if (colorMap != null && (colorMap.get(text.toString()) != null)) {
style.setFillForegroundColor((short) colorMap.get(text.toString()));
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
}
cell.setCellStyle(style);
}
// 遍历集合数据，产生数据行
Iterator<T> 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((value == null || "null".equals(String.valueOf(value))) ? "" : String.valueOf(value));
cellNum++;
}
} else {
List<FieldForSortting> 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);
}
if (j != douArr.length - 1) {
cellNum++;
cell = row.createCell(cellNum);
}
}
} else {
// 其它数据类型都当作字符串简单处理
String empty = StringUtils.EMPTY;
ExcelCell anno = field.getAnnotation(ExcelCell.class);
if (anno != null) {
empty = anno.defaultValue();
}
textValue = value == null ? empty : value.toString();
}
if (textValue != null) {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
cellNum++;
}
}
} catch (Exception e) {
LG.error(e.toString(), e);
}
}
// 设定自动宽度
for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}
}
private static <T> void write2XlsSheet(String[] header,HSSFSheet sheet, List<Map<String,Object>> dataset) {
if(CollectionUtils.isEmpty(dataset)){
return;
}
//行
int rowNo = 1;
for(Map<String,Object> map:dataset){
HSSFRow row = sheet.createRow(rowNo);
// 遍历集合数据，产生数据行
for(int i=0;i<header.length;i++){
HSSFCell cell = row.createCell(i);
cell.setCellValue(map.get(header[i])==null?"":map.get(header[i]).toString());
if(i==14){
cell.setCellFormula("L"+String.valueOf(rowNo + 1)+"*M"+String.valueOf(rowNo+1));
}
}
rowNo++;
}
}
private static <T> void write2XlsxSheet(String[] header,XSSFSheet sheet, List<Map<String,Object>> dataset) {
if(CollectionUtils.isEmpty(dataset)){
return;
}
//行
int rowNo = 1;
for(Map<String,Object> map:dataset){
XSSFRow row = sheet.createRow(rowNo);
// 遍历集合数据，产生数据行
for(int i=0;i<header.length;i++){
XSSFCell cell = row.createCell(i);
cell.setCellValue(map.get(header[i])==null?"":map.get(header[i]).toString());
if(i==14){
cell.setCellFormula("L"+String.valueOf(rowNo + 1)+"*M"+String.valueOf(rowNo+1));
}
}
rowNo++;
}
}
/**
* 每个xlsSheet的写入
*
* @param sheet 页签
* @param headers 表头
* @param dataset 数据集合
* @param pattern 日期格式
*/
public static <T> void write2XlsxSheet(XSSFSheet sheet, String[] headers, Collection<T> dataset,
String pattern, Map<String, Object> colorMap) {
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
XSSFFont font = sheet.getWorkbook().createFont();
font.setFontHeightInPoints((short) 14);//设置字体大小
XSSFCellStyle style = sheet.getWorkbook().createCellStyle();
if (colorMap != null && (colorMap.get(text.toString()) != null)) {
style.setFillForegroundColor((short) colorMap.get(text.toString()));
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
}
style.setFont(font);
cell.setCellStyle(style);
}
// 遍历集合数据，产生数据行
Iterator<T> 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);
XSSFCell cell = row.createCell(cellNum);
cell.setCellValue((value == null || "null".equals(String.valueOf(value))) ? "" : String.valueOf(value));
cellNum++;
}
} else {
List<FieldForSortting> fields = sortFieldByAnno(t.getClass());
int cellNum = 0;
for (int i = 0; i < fields.size(); i++) {
XSSFCell 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);
}
if (j != douArr.length - 1) {
cellNum++;
cell = row.createCell(cellNum);
}
}
} else {
// 其它数据类型都当作字符串简单处理
String empty = StringUtils.EMPTY;
ExcelCell anno = field.getAnnotation(ExcelCell.class);
if (anno != null) {
empty = anno.defaultValue();
}
textValue = value == null ? empty : value.toString();
}
if (textValue != null) {
XSSFRichTextString richString = new XSSFRichTextString(textValue);
cell.setCellValue(richString);
}
cellNum++;
}
}
} catch (Exception e) {
LG.error(e.toString(), e);
}
}
// 设定自动宽度
for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}
}
@SuppressWarnings("unchecked")
public static <T> Collection<T> importExcels(File file) {
ExcelLogs logs = new ExcelLogs();
return importExcel(file, logs);
}
@SuppressWarnings("unchecked")
public static <T> Collection<T> importExcel(Class<T> clazz, File file, Integer... arrayCount) {
ExcelLogs logs = new ExcelLogs();
return importExcel(clazz, file, "yyyy-MM-dd", logs, arrayCount);
}
@SuppressWarnings("unchecked")
public static <T> Collection<T> importExcel(Class<T> clazz, File file) {
ExcelLogs logs = new ExcelLogs();
return importExcel(clazz, file, "yyyy-MM-dd", logs, 0);
}
@SuppressWarnings("unchecked")
public static <T> Collection<T> importExcels(Class<T> clazz, File file, Integer rows, Integer[] cells) {
ExcelLogs logs = new ExcelLogs();
return importExcels(clazz, file, "yyyy-MM-dd", logs, rows, cells, 0);
}
/**
* 把Excel的数据封装成voList
*
* @param clazz vo的Class
* @param pattern 如果有时间数据，设定输入格式。默认为"yyy-MM-dd"
* @param logs 错误log集合
* @param arrayCount 如果vo中有数组类型,那就按照index顺序,把数组应该有几个值写上.
* @return voList
* @throws RuntimeException
*/
@SuppressWarnings("unchecked")
public static <T> Collection<T> importExcel(Class<T> clazz, File file,
String pattern, ExcelLogs logs, Integer... arrayCount) {
if (!file.exists()) {
return new ArrayList<>();
}
InputStream inputStream = null;
try {
inputStream = new FileInputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
//判断文件类型
String fileName = file.getName();
if (fileName.endsWith(excel_format_xls)) {
HSSFWorkbook workBook = null;
try {
workBook = new HSSFWorkbook(inputStream);
} catch (IOException e) {
LG.error(e.toString(), e);
}
List<T> list = new ArrayList<T>();
HSSFSheet sheet = workBook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
try {
List<ExcelLog> logList = new ArrayList<ExcelLog>();
// Map<title,index>
Map<String, Integer> titleMap = new HashMap<>();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
if (clazz == Map.class) {
// 解析map用的key,就是excel标题行
Iterator<Cell> cellIterator = row.cellIterator();
Integer index = 0;
while (cellIterator.hasNext()) {
String value = cellIterator.next().getStringCellValue();
titleMap.put(value, index);
index++;
}
}
continue;
}
// 整行都空，就跳过
boolean allRowIsNull = true;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Object cellValue = getCellValue(cellIterator.next());
if (cellValue != null) {
allRowIsNull = false;
break;
}
}
if (allRowIsNull) {
LG.warn("Excel row " + row.getRowNum() + " all row value is null!");
continue;
}
T t = null;
StringBuilder log = new StringBuilder();
if (clazz == Map.class) {
Map<String, Object> map = new HashMap<String, Object>();
for (String k : titleMap.keySet()) {
Integer index = titleMap.get(k);
String value = getCellValues(row.getCell(index));
map.put(k, value);
}
list.add((T) map);
} else {
t = clazz.newInstance();
int arrayIndex = 0;// 标识当前第几个数组了
int cellIndex = 0;// 标识当前读到这一行的第几个cell了
List<FieldForSortting> fields = sortFieldByAnno(clazz);
for (FieldForSortting ffs : fields) {
Field field = ffs.getField();
field.setAccessible(true);
if (field.getType().isArray()) {
Integer count = arrayCount[arrayIndex];
Object[] value = null;
if (field.getType().equals(String[].class)) {
value = new String[count];
} else {
// 目前只支持String[]和Double[]
value = new Double[count];
}
for (int i = 0; i < count; i++) {
Cell cell = row.getCell(cellIndex);
String errMsg = validateCell(cell, field, cellIndex);
if (StringUtils.isBlank(errMsg)) {
value[i] = getCellValue(cell);
} else {
log.append(errMsg);
log.append(";");
logs.setHasError(true);
}
cellIndex++;
}
field.set(t, value);
arrayIndex++;
} else {
Cell cell = row.getCell(cellIndex);
String errMsg = validateCell(cell, field, cellIndex);
if (StringUtils.isBlank(errMsg)) {
Object value = null;
// 处理特殊情况,Excel中的String,转换成Bean的Date
if (field.getType().equals(Date.class)
&& cell.getCellType() == Cell.CELL_TYPE_STRING) {
Object strDate = getCellValue(cell);
try {
value = new SimpleDateFormat(pattern).parse(strDate.toString());
} catch (ParseException e) {
errMsg =
MessageFormat.format("the cell [{0}] can not be converted to a date ",
CellReference.convertNumToColString(cell.getColumnIndex()));
}
} else {
value = getCellValue(cell);
// 处理特殊情况,excel的value为String,且bean中为其他,且defaultValue不为空,那就=defaultValue
ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
if (value instanceof String && !field.getType().equals(String.class)
&& StringUtils.isNotBlank(annoCell.defaultValue())) {
value = annoCell.defaultValue();
}
}
field.set(t, value);
}
if (StringUtils.isNotBlank(errMsg)) {
log.append(errMsg);
log.append(";");
logs.setHasError(true);
}
cellIndex++;
}
}
list.add(t);
logList.add(new ExcelLog(t, log.toString(), row.getRowNum() + 1));
}
}
logs.setLogList(logList);
} catch (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}",
clazz.getSimpleName()), e);
}
return list;
} else if (fileName.endsWith(excel_format_xlsx)) {
XSSFWorkbook workBook = null;
try {
workBook = new XSSFWorkbook(inputStream);
} catch (IOException e) {
LG.error(e.toString(), e);
}
List<T> list = new ArrayList<T>();
XSSFSheet sheet = workBook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
try {
List<ExcelLog> logList = new ArrayList<ExcelLog>();
// Map<title,index>
Map<String, Integer> titleMap = new HashMap<>();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
if (clazz == Map.class) {
// 解析map用的key,就是excel标题行
Iterator<Cell> cellIterator = row.cellIterator();
Integer index = 0;
while (cellIterator.hasNext()) {
String value = cellIterator.next().getStringCellValue();
titleMap.put(value, index);
index++;
}
}
continue;
}
// 整行都空，就跳过
boolean allRowIsNull = true;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Object cellValue = getCellValue(cellIterator.next());
if (cellValue != null) {
allRowIsNull = false;
break;
}
}
if (allRowIsNull) {
LG.warn("Excel row " + row.getRowNum() + " all row value is null!");
continue;
}
T t = null;
StringBuilder log = new StringBuilder();
if (clazz == Map.class) {
Map<String, Object> map = new HashMap<String, Object>();
for (String k : titleMap.keySet()) {
Integer index = titleMap.get(k);
String value = getCellValues(row.getCell(index));
map.put(k, value);
}
list.add((T) map);
} else {
t = clazz.newInstance();
int arrayIndex = 0;// 标识当前第几个数组了
int cellIndex = 0;// 标识当前读到这一行的第几个cell了
List<FieldForSortting> fields = sortFieldByAnno(clazz);
for (FieldForSortting ffs : fields) {
Field field = ffs.getField();
field.setAccessible(true);
if (field.getType().isArray()) {
Integer count = arrayCount[arrayIndex];
Object[] value = null;
if (field.getType().equals(String[].class)) {
value = new String[count];
} else {
// 目前只支持String[]和Double[]
value = new Double[count];
}
for (int i = 0; i < count; i++) {
Cell cell = row.getCell(cellIndex);
String errMsg = validateCell(cell, field, cellIndex);
if (StringUtils.isBlank(errMsg)) {
value[i] = getCellValue(cell);
} else {
log.append(errMsg);
log.append(";");
logs.setHasError(true);
}
cellIndex++;
}
field.set(t, value);
arrayIndex++;
} else {
Cell cell = row.getCell(cellIndex);
String errMsg = validateCell(cell, field, cellIndex);
if (StringUtils.isBlank(errMsg)) {
Object value = null;
// 处理特殊情况,Excel中的String,转换成Bean的Date
if (field.getType().equals(Date.class)
&& cell.getCellType() == Cell.CELL_TYPE_STRING) {
Object strDate = getCellValue(cell);
try {
value = new SimpleDateFormat(pattern).parse(strDate.toString());
} catch (ParseException e) {
errMsg =
MessageFormat.format("the cell [{0}] can not be converted to a date ",
CellReference.convertNumToColString(cell.getColumnIndex()));
}
} else {
value = getCellValue(cell);
// 处理特殊情况,excel的value为String,且bean中为其他,且defaultValue不为空,那就=defaultValue
ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
if (value instanceof String && !field.getType().equals(String.class)
&& StringUtils.isNotBlank(annoCell.defaultValue())) {
value = annoCell.defaultValue();
}
}
field.set(t, value);
}
if (StringUtils.isNotBlank(errMsg)) {
log.append(errMsg);
log.append(";");
logs.setHasError(true);
}
cellIndex++;
}
}
list.add(t);
logList.add(new ExcelLog(t, log.toString(), row.getRowNum() + 1));
}
}
logs.setLogList(logList);
} catch (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}",
clazz.getSimpleName()), e);
}
return list;
} else {
return new ArrayList<>();
}
}
/**
* Excel的数据封装
*/
@SuppressWarnings("unchecked")
public static <T> Collection<T> importExcel(File file, ExcelLogs logs) {
if (!file.exists()) {
return new ArrayList<>();
}
InputStream inputStream = null;
try {
inputStream = new FileInputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
//判断文件类型
String fileName = file.getName();
List<Map<String, Object>> sheetList = new ArrayList<>();
if (fileName.endsWith(excel_format_xls)) {
HSSFWorkbook workBook = null;
try {
workBook = new HSSFWorkbook(inputStream);
} catch (IOException e) {
LG.error(e.toString(), e);
}
int sheetNum = workBook.getNumberOfSheets();
if (sheetNum > 0) {
for (int i = 0; i < sheetNum; i++) {
HSSFSheet sheet = workBook.getSheetAt(i);
Map<String, Object> sheetMap = new HashMap<>();
Iterator<Row> rowIterator = sheet.rowIterator();
List<ExcelLog> logList = new ArrayList<ExcelLog>();
List<T> list = new ArrayList<T>();
// Map<title,index>
Map<String, Integer> titleMap = new HashMap<>();
while (rowIterator.hasNext()) {
//标题行（第一行）
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
// 解析map用的key,就是excel标题行
Iterator<Cell> cellIterator = row.cellIterator();
Integer index = 0;
while (cellIterator.hasNext()) {
String value = cellIterator.next().getStringCellValue();
titleMap.put(value, index);
index++;
}
continue;
}
// 整行都空，就跳过
boolean allRowIsNull = true;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Object cellValue = getCellValue(cellIterator.next());
if (cellValue != null) {
allRowIsNull = false;
break;
}
}
if (allRowIsNull) {
LG.warn("Excel row " + row.getRowNum() + " all row value is null!");
continue;
}
T t = null;
StringBuilder log = new StringBuilder();
Map<String, Object> map = new HashMap<String, Object>();
for (String k : titleMap.keySet()) {
Integer index = titleMap.get(k);
String value = getCellValues(row.getCell(index));
map.put(k, value);
}
list.add((T) map);
}
//sheetName
sheetMap.put("sheetName", sheet.getSheetName());
sheetMap.put("sheetData", list);
logs.setLogList(logList);
sheetList.add(sheetMap);
}
}
return (Collection) sheetList;
} else if (fileName.endsWith(excel_format_xlsx)) {
XSSFWorkbook workBook = null;
try {
workBook = new XSSFWorkbook(inputStream);
} catch (IOException e) {
LG.error(e.toString(), e);
}
int sheetNum = workBook.getNumberOfSheets();
if (sheetNum > 0) {
for (int i = 0; i < sheetNum; i++) {
XSSFSheet sheet = workBook.getSheetAt(i);
Map<String, Object> sheetMap = new HashMap<>();
Iterator<Row> rowIterator = sheet.rowIterator();
List<ExcelLog> logList = new ArrayList<ExcelLog>();
List<T> list = new ArrayList<T>();
// Map<title,index>
Map<String, Integer> titleMap = new HashMap<>();
while (rowIterator.hasNext()) {
//标题行（第一行）
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
// 解析map用的key,就是excel标题行
Iterator<Cell> cellIterator = row.cellIterator();
Integer index = 0;
while (cellIterator.hasNext()) {
String value = cellIterator.next().getStringCellValue();
titleMap.put(value, index);
index++;
}
continue;
}
// 整行都空，就跳过
boolean allRowIsNull = true;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Object cellValue = getCellValue(cellIterator.next());
if (cellValue != null) {
allRowIsNull = false;
break;
}
}
if (allRowIsNull) {
LG.warn("Excel row " + row.getRowNum() + " all row value is null!");
continue;
}
T t = null;
StringBuilder log = new StringBuilder();
Map<String, Object> map = new HashMap<String, Object>();
for (String k : titleMap.keySet()) {
Integer index = titleMap.get(k);
String value = getCellValues(row.getCell(index));
map.put(k, value);
}
list.add((T) map);
}
//sheetName
sheetMap.put("sheetName", sheet.getSheetName());
sheetMap.put("sheetData", list);
logs.setLogList(logList);
sheetList.add(sheetMap);
}
}
return (Collection) sheetList;
} else {
return new ArrayList<>();
}
}
/**
* 单张表格导出
*
* @param fileType
* @param headers
* @param excelData
* @param response
* @param <T>
*/
public static <T> void exportExcel(String fileType,String fileName, String[] headers, Collection<T> excelData, Map<String, Object> colorMap, HttpServletResponse response) {
String uploadPath = PropertiesLoader.get("UPLOAD_PATH");
List<String> temporaryFilePaths = new ArrayList<>();
String uploadFilePath = "";
if (org.springframework.util.CollectionUtils.isEmpty(excelData)) {
return;
}
String path = "/";
if(StringUtils.isNotEmpty(fileName)){
path+=fileName;
}else {
path += System.currentTimeMillis();
}
File file = new File(uploadPath + path);
file.getParentFile().mkdirs();
// 声明一个工作薄
if ("xls".equals(fileType)) {
path += excel_format_xls;
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsSheet(sheet, headers, excelData, null, colorMap);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
} else {
path += excel_format_xlsx;
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsxSheet(sheet, headers, excelData, null, colorMap);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//uploadFilePath
uploadFilePath = uploadPath + path;
//temporaryFilePaths
temporaryFilePaths.add(uploadFilePath);
//下载
downloadFile(uploadFilePath, null, response);
//删除临时文件
deleteSheet(temporaryFilePaths);
}
/**
* 单张表格导出
*/
public static <T> void exportExcel(String[] header,String filePath,List<Map<String,Object>> excelData, HttpServletResponse response) {
if(StringUtils.isEmpty(filePath)){
LG.error("Have No FilePath");
return;
}
File file = new File(filePath);
if(!file.exists()){
LG.error("File Not Exist");
return;
}
String[] fileTypes =file.getName().split("\\.");
String fileType = fileTypes[fileTypes.length-1];
String rootPath = PropertiesLoader.get("UPLOAD_PATH");
String newPath = rootPath+file.getName();
File newFile = new File(newPath);
// 声明一个工作薄
if ("xls".equals(fileType)) {
InputStream inputStream=null;
HSSFWorkbook workbook = null;
try {
inputStream = new FileInputStream(file);
workbook = new HSSFWorkbook(inputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
}catch (IOException e) {
e.printStackTrace();
}
// 生成一个表格
HSSFSheet sheet = workbook.getSheetAt(0);
//写入数据
write2XlsSheet(header,sheet, excelData);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(newPath);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
} else {
InputStream inputStream=null;
XSSFWorkbook workbook = null;
try {
inputStream = new FileInputStream(file);
workbook = new XSSFWorkbook(inputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
}catch (IOException e) {
e.printStackTrace();
}
// 生成一个表格
XSSFSheet sheet = workbook.getSheetAt(0);
//写入数据
write2XlsxSheet(header,sheet, excelData);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(newPath);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//下载
downloadFile(newPath, null, response);
//删除临时文件
if(newFile.exists()){
newFile.delete();
}
}
/**
* 根据annotation的seq排序后的栏位
*
* @param clazz
* @return
*/
public static List<FieldForSortting> sortFieldByAnno(Class<?> clazz) {
Field[] fieldsArr = clazz.getDeclaredFields();
List<FieldForSortting> fields = new ArrayList<FieldForSortting>();
List<FieldForSortting> annoNullFields = new ArrayList<FieldForSortting>();
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 = new ArrayList<Object>();
if (props != null) {
for (String prop : props) {
sortCols.add(new BeanComparator(prop, typeComp));
}
}
if (sortCols.size() > 0) {
Comparator<Object> sortChain = new ComparatorChain(sortCols);
Collections.sort(list, sortChain);
}
}
}
/**
* 获取单元格值
*
* @param cell
* @return
*/
public 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:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return dateFormat.format(cell.getDateCellValue());
} else {
return hexadecimalConversion(String.valueOf(cell.getNumericCellValue()));
}
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
default:
return null;
}
}
static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**
* 获取单元格值
*
* @param cell
* @return
*/
public static String getCellValues(Cell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return dateFormat.format(cell.getDateCellValue());
} else {
return hexadecimalConversion(String.valueOf(cell.getNumericCellValue()));
}
}
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case Cell.CELL_TYPE_FORMULA:
try{
return String.valueOf(cell.getNumericCellValue());
}catch (Exception e){
return cell.getStringCellValue();
}
case Cell.CELL_TYPE_BLANK:
return "";
case Cell.CELL_TYPE_BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case Cell.CELL_TYPE_ERROR:
return String.valueOf(cell.getErrorCellValue());
default:
return "";
}
}
/**
* 验证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<Integer> cellTypes = Arrays.asList(integers);
// 如果类型不在指定范围內,并且沒有默认值
if (!(cellTypes.contains(cell.getCellType()))
|| StringUtils.isNotBlank(annoCell.defaultValue())
&& cell.getCellType() == Cell.CELL_TYPE_STRING) {
StringBuilder strType = new StringBuilder();
for (int i = 0; i < cellTypes.size(); i++) {
Integer intType = cellTypes.get(i);
strType.append(getCellTypeByInt(intType));
if (i != cellTypes.size() - 1) {
strType.append(",");
}
}
result =
MessageFormat.format("the cell [{0}] type must [{1}]", columnName, strType.toString());
} else {
// 类型符合验证,但值不在要求范围内的
// String in
if (annoCell.valid().in().length != 0 && cell.getCellType() == Cell.CELL_TYPE_STRING) {
String[] in = annoCell.valid().in();
String cellValue = cell.getStringCellValue();
boolean isIn = false;
for (String str : in) {
if (str.equals(cellValue)) {
isIn = true;
}
}
if (!isIn) {
result = MessageFormat.format("the cell [{0}] value must in {1}", columnName, in);
}
}
// 数字型
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
double cellValue = cell.getNumericCellValue();
// 小于
if (!Double.isNaN(annoCell.valid().lt())) {
if (!(cellValue < annoCell.valid().lt())) {
result =
MessageFormat.format("the cell [{0}] value must less than [{1}]", columnName,
annoCell.valid().lt());
}
}
// 大于
if (!Double.isNaN(annoCell.valid().gt())) {
if (!(cellValue > annoCell.valid().gt())) {
result =
MessageFormat.format("the cell [{0}] value must greater than [{1}]", columnName,
annoCell.valid().gt());
}
}
// 小于等于
if (!Double.isNaN(annoCell.valid().le())) {
if (!(cellValue <= annoCell.valid().le())) {
result =
MessageFormat.format("the cell [{0}] value must less than or equal [{1}]",
columnName, annoCell.valid().le());
}
}
// 大于等于
if (!Double.isNaN(annoCell.valid().ge())) {
if (!(cellValue >= annoCell.valid().ge())) {
result =
MessageFormat.format("the cell [{0}] value must greater than or equal [{1}]",
columnName, annoCell.valid().ge());
}
}
}
}
}
return result;
}
/**
* 用来验证excel与Vo中的类型是否一致 <br>
* 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 <pre>
* Cell.CELL_TYPE_BLANK
* Cell.CELL_TYPE_BOOLEAN
* Cell.CELL_TYPE_ERROR
* Cell.CELL_TYPE_FORMULA
* Cell.CELL_TYPE_NUMERIC
* Cell.CELL_TYPE_STRING
* </pre>
* @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 fileType
* @param headers
* @param excels
* @param request
* @param response
* @return
*/
public static <T> String exportExcelss(String[] headers, List<Object> excels, HttpServletRequest request, HttpServletResponse response,String filename) {
String uploadPath = PropertiesLoader.get("UPLOAD_PATH");
String uploadFilePath = "";
if (org.springframework.util.CollectionUtils.isEmpty(excels)) {
return "";
}
String path = "";
path += filename;
File file = new File(uploadPath + path);
file.getParentFile().mkdirs();
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsSheet(sheet, headers, excels, null, null);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
//uploadFilePath
return path;
}
/**
* 科学计数法转换普通字符
*
* @param specialStr
* @return
*/
private static String hexadecimalConversion(String specialStr) {
try {
if (StringUtils.isNotEmpty(specialStr)) {
BigDecimal db = new BigDecimal(specialStr);
return db.toPlainString();
}
} catch (NumberFormatException nfx) {
return specialStr;
}
return "";
}
/**
* 把Excel的数据封装成voList
*
* @param clazz vo的Class
* @param pattern 如果有时间数据，设定输入格式。默认为"yyy-MM-dd"
* @param logs 错误log集合
* @param rows 从某行开始
* @param arrayCount 如果vo中有数组类型,那就按照index顺序,把数组应该有几个值写上.
* @return voList
* @throws RuntimeException
*/
@SuppressWarnings("unchecked")
public static <T> Collection<T> importExcels(Class<T> clazz, File file,
String pattern, ExcelLogs logs, Integer rows, Integer[] cells, Integer... arrayCount) {
if (!file.exists()) {
return new ArrayList<>();
}
InputStream inputStream = null;
try {
inputStream = new FileInputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
//判断文件类型
String fileName = file.getName();
if (fileName.endsWith(excel_format_xls)) {
HSSFWorkbook workBook = null;
try {
workBook = new HSSFWorkbook(inputStream);
} catch (IOException e) {
LG.error(e.toString(), e);
}
List<T> list = new ArrayList<T>();
HSSFSheet sheet = workBook.getSheetAt(0);
List<ExcelLog> logList = new ArrayList<ExcelLog>();
// Map<title,index>
Map<String, Integer> titleMap = new HashMap<>();
for (int i = 9; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row.getRowNum() == 9) {
for (Integer cell : cells) {
titleMap.put(row.getCell(cell).getStringCellValue(), cell);
}
} else {
Map<String, Object> map = new HashMap<String, Object>();
for (String k : titleMap.keySet()) {
Integer index = titleMap.get(k);
String value = getCellValues(row.getCell(index));
map.put(k, value);
}
list.add((T) map);
}
}
return list;
} else if (fileName.endsWith(excel_format_xlsx)) {
XSSFWorkbook workBook = null;
try {
workBook = new XSSFWorkbook(inputStream);
} catch (IOException e) {
LG.error(e.toString(), e);
}
List<T> list = new ArrayList<T>();
XSSFSheet sheet = workBook.getSheetAt(0);
List<ExcelLog> logList = new ArrayList<ExcelLog>();
// Map<title,index>
Map<String, Integer> titleMap = new HashMap<>();
for (int i = 9; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row.getRowNum() == 9) {
for (Integer cell : cells) {
int columnIndex = row.getCell(cell).getColumnIndex();
titleMap.put(row.getCell(cell).getStringCellValue(), columnIndex);
}
} else {
Map<String, Object> map = new HashMap<String, Object>();
for (String k : titleMap.keySet()) {
Integer index = titleMap.get(k);
String value = getCellValues(row.getCell(index));
map.put(k, value);
}
list.add((T) map);
}
}
return list;
} else {
return new ArrayList<>();
}
}
/**
* 个人加,返回路径
*
* @param fileType
* @param headers
* @param excels
* @return
*/
public static <T> String exportExcels(String fileType, String[] headers, List<Collection<T>> excels) {
//String ctxPath = request.getSession().getServletContext().getRealPath("");
String uploadPath = PropertiesLoader.get("UPLOAD_PATH");
//String zipReturnPath = "dd/zip/" + UUID.randomUUID().toString() + ".zip";
List<String> temporaryFilePaths = new ArrayList<>();
String uploadFilePath = "";
if (org.springframework.util.CollectionUtils.isEmpty(excels)) {
return "";
}
for (Collection<T> collection : excels) {
String path = "/";
path += System.currentTimeMillis();
File file = new File(uploadPath + path);
file.getParentFile().mkdirs();
// 声明一个工作薄
if ("xls".equals(fileType)) {
path += excel_format_xls;
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsSheet(sheet, headers, collection, null, null);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
} else {
path += excel_format_xlsx;
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsxSheet(sheet, headers, collection, null, null);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//uploadFilePath
uploadFilePath = uploadPath + path;
}
return uploadFilePath;
}
/**
*
* @param headers
* @param dataList
* @param filename
* @return
*/
@SuppressWarnings("resource")
public static String exportExcels(HttpServletRequest requests,String[] headers, List<Map<String, String>> dataList,String filename) {
String uploadPath = PropertiesLoader.get("UPLOAD_PATH");
String uploadFilePath = "";
if (org.springframework.util.CollectionUtils.isEmpty(dataList)) {
return "";
}
String path = "";
path += filename;
File file = new File(uploadPath + path);
file.getParentFile().mkdirs();
HSSFWorkbook workbook=null;
try {
InputStream ips=new FileInputStream(requests.getSession().getServletContext().getRealPath("/")+"/excel/original.xls");
workbook = new HSSFWorkbook(ips);
} catch (Exception e) {
e.printStackTrace();
}
/*XSSFWorkbook workbook = new XSSFWorkbook();*/
// 生成一个表格
HSSFSheet sheet = workbook.getSheetAt(0);
//写入数据
writeXlsxSheet(sheet, headers, dataList);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
}
//uploadFilePath
uploadFilePath = uploadPath + path;
return uploadFilePath;
}
public static void writeXlsxSheet(HSSFSheet sheet, String[] headers, List<Map<String, String>> dataList) {
/*//创建第一行
HSSFRow row0 = sheet.createRow(0);
for (int i = 0; i < 7; i++) {
row0.createCell(i);
}
sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));
row0.getCell(0).setCellValue("Master Documentation Audit");
//创建标题行
XSSFRow row1 = sheet.createRow(1);
row1.setHeight((short) 800);
XSSFCellStyle style = sheet.getWorkbook().createCellStyle();
for (int i = 0; i < headers.length; i++) {
XSSFCell cell = row1.createCell(i);
style.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY);
style.setWrapText(true);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellStyle(style);
cell.setCellValue(text);
}*/
//数据行
for (int i = 0; i < dataList.size(); i++) {
Map<String, String> map = dataList.get(i);
HSSFRow row2 = sheet.createRow(i+4);
Integer cell = 0 ;
for (String header : headers) {
//创建单元格
HSSFCell cells = row2.createCell(cell);
//为前三列赋值
if(cell<3){
String value = map.get(header);
cells.setCellValue(value);
//为三四列赋值
}else if(cell>=3&&cell<=4){
}//第五行公式
else if(cell==5){
//得到当前行的坐标(1开始)
int j = i+5;
cells.setCellFormula("IF(SUM(D" +j+ ":E" +j+ ")>=1,1,0)");
cells.setCellValue("");
}//六七行
else if(cell>=6&&cell<=7){
cells.setCellValue("");
}
cell++;
}
}
Integer total = 4+dataList.size();
HSSFRow row3 = sheet.createRow(total);
row3.createCell(0).setCellValue("total");
row3.createCell(5).setCellFormula("SUM(F5:F"+total+")");
row3.getCell(5).setCellValue("");
HSSFRow row4 = sheet.createRow(total+1);
Integer temp = total+1;
Integer totalsize = dataList.size()+4;
row4.createCell(2).setCellValue("Master DOC Retention Non-Compliance");
row4.createCell(5).setCellFormula("F"+temp+"/A"+totalsize+"%");
row4.getCell(5).setCellValue("");
}
//添加路径
/**
*
* @param filetype
* @param headers
* @param excelList
* @param request
* @param response
* @param filename 文件目录
*/
public static void exportExcels(String[] headers, List<Object> dataList, HttpServletRequest request,
HttpServletResponse response, String filename) {
String uploadPath = PropertiesLoader.get("UPLOAD_PATH");
List<String> temporaryFilePaths = new ArrayList<>();
String uploadFilePath = "";
if (org.springframework.util.CollectionUtils.isEmpty(dataList)) {
return;
}
String path = "";
path += filename;
File file = new File(uploadPath + path);
file.getParentFile().mkdirs();
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
//写入数据
write2XlsSheet(sheet, headers, dataList, null, null);
//生成文件
try {
FileOutputStream fo = new FileOutputStream(uploadPath + path);
workbook.write(fo);
fo.close();
} catch (IOException e) {
e.printStackTrace();
//uploadFilePath
uploadFilePath = uploadPath + path;
//temporaryFilePaths
temporaryFilePaths.add(uploadFilePath);
}
//下载
uploadFilePath = uploadPath + path;
downloadFile(uploadFilePath, request, response);
//删除临时文件
deleteSheet(temporaryFilePaths);
}
}