package com.toltech.mczhdj.commons.utils;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
import com.toltech.boot.common.enums.BasicStatu;
import com.toltech.boot.common.enums.Boolflag;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import com.toltech.mczhdj.entity.group.Personnel;
public class ExcelUtils {
private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
/**
* 通用模板下载
*
* @param title
* @param properties
* @return
* @throws IOException
*/
public static byte[] commonExcelr(String title, List<String> properties) throws IOException {
byte[] byteArray = null;
try (ByteArrayOutputStream os = new ByteArrayOutputStream()) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(title);
HSSFRow row = sheet.createRow(0);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
HSSFCell cell = null;
for (int i = 0; i < properties.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(properties.get(i));
}
wb.write(os);
wb.close();
byteArray = os.toByteArray();
return byteArray;
}
}
/**
* 党员人员上传
* @param file
* @param msg
* @param
* @return
* @throws Exception
*/
public static List<Personnel> readPersonnels(MultipartFile file, List<String> msg) throws Exception {
if (file == null || file.getSize() == 0)
return null;
List<Personnel> personnels = new ArrayList<Personnel>();
Date date = new Date();
// 文件名
String fileName = file.getOriginalFilename();
if (fileName.endsWith(".xlsx") || fileName.endsWith(".xls")) {
// 获取Excel文件对象
Workbook wb = WorkbookFactory.create(file.getInputStream());
// 获取文件指定工作表,默认第一个
Sheet sheet = wb.getSheetAt(0);
// 遍历行数(行记录,从0开始,首行记录数+1)
w: for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
// 每一行对应一个list
List<String> list = new ArrayList<String>();
// 创建一个行对象
Row row = sheet.getRow(i);
if (row == null || row.getLastCellNum() == -1) {
break w;
}
// 遍历一行中的每个字段
int max = sheet.getRow(1).getLastCellNum();
for (int j = 0; j < max; j++) {
// 为每一个字段创建一个单元格对象
Cell cell = row.getCell((short) (j));
if (cell == null) {
list.add("empty");
msg.add("第" + (i) + "行" + "第" + j + "列为空");
}else{
// 获取cell数据
String data = getCellType(cell);
if(StringUtils.isNotBlank(data)){
list.add(data);
}else{
list.add("empty");
msg.add("第" + (i) + "行" + "第" + j + "列为空");
}
}
}
Personnel personnel = new Personnel();
for (int j = 0; j < row.getLastCellNum(); j++) {
String data = list.get(j);
if(!data.equals("empty")){
switch (j) {
case 0:
personnel.setSname(data);
break;
case 1:
Date backupTime = DateUtils.addYears(date, -(Integer.parseInt(list.get(1))));
personnel.setBirthAt(backupTime);
break;
case 2:
personnel.setJoinPartyAt(DateUtils.parseDate(list.get(2),"yyyy-MM-dd hh:mm:ss"));
break;
case 3:
personnel.setMobile(data);
break;
case 4:
personnel.setOutline(data);
break;
default:
break;
}
}
}
personnel.setUuid(UUID.randomUUID().toString());
personnel.setStatus(BasicStatu.NORMAL);
personnel.setHasMove(Boolflag.FALSE);
personnels.add(personnel);
}
wb.close();
}
return personnels;
}
/**
* EXCEL中的CELL值转换
*
* @param cell
* @return
*/
public static String getCellType(Cell cell) {
logger.debug("cell type >>>>>>> {}", cell.getCellType());
String result = null;
switch (cell.getCellType()) {
case NUMERIC: // 数字类型,日期类型
result = dealNum(cell);
break;
case STRING: // 字符串类型
result = cell.getStringCellValue();
result = dealStr(result);
break;
case FORMULA: // 公式
result = cell.getCellFormula();
break;
default:
result = "";
break;
}
return result;
}
/**
* 处理数值类型
*
* @param cell
* @return
*/
public static String dealNum(Cell cell) {
String str = "";
if (!DateUtil.isCellDateFormatted(cell)) {
str = new DecimalFormat("0").format(cell.getNumericCellValue());
}else {
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
str = formater.format(d);
}
return str;
}
/**
* 处理字符串
*
* @param str
* @return
*/
public static String dealStr(String str) {
if (StringUtils.isBlank(str)) {
str = "";
}
return str;
}
}