package com.odianyun.manage.util;
import com.alibaba.fastjson.JSON;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.util.*;
public class ExcelUtils {
/**
* Excel 导入
*
* @param file 文件
* @param keys 数据顺序
*/
public static List<Map<String, Object>> importExcel(MultipartFile file, String[] keys) throws Exception {
Workbook wb = null;
String fileName = file.getOriginalFilename();
if (fileName.endsWith("xls")) {
POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
wb = new HSSFWorkbook(pois);
} else if (fileName.endsWith("xlsx")) {
wb = new XSSFWorkbook(file.getInputStream());
} else {
//不是excel文件
throw new PackageException(PackageEnum.EXCEL_FILE_EXCEPTION);
}
//获取第一页数据
Sheet sheet = wb.getSheetAt(0);
//第一页行数
int rowCount = sheet.getPhysicalNumberOfRows();
//检测模板和传入header是否数量一致
if (sheet.getRow(0).getPhysicalNumberOfCells() != keys.length) {
throw new RuntimeException("导入的Excel和模板的列不匹配");
}
//用于返回的结果
List<Map<String, Object>> result = new ArrayList<>();
for (int i = 0; i < rowCount - 1; i++) {
//数据行,跳过第一行表头
Row row = sheet.getRow(i + 1);
Map<String, Object> tmp = new HashMap<>();
for (int j = 0; j < keys.length; j++) {
Cell cell = row.getCell(j);
if (cell != null) {
// 把类型转行String
cell.setCellType(CellType.STRING);
tmp.put(keys[j], cell.getStringCellValue());
} else {
tmp.put(keys[j], "");
}
}
result.add(tmp);
}
return result;
}
/**
* 获得Excel文件的第一个Sheet
* @param file
* @return
*/
public static Sheet getFirstSheet(MultipartFile file) throws Exception {
try(Workbook workBook = WorkbookFactory.create(file.getInputStream())) {
return workBook.getSheetAt(0);
} catch (Exception e) {
throw new Exception("上传文件异常",e); // 文件上传出错
}
}
public static class ExcelHeaderMetaData {
private List<String> dataList;
private String placeholder;
private Short color;
public ExcelHeaderMetaData(List<String> dataList, String placeholder, short color) {
this.dataList = dataList;
this.placeholder = placeholder;
this.color = color;
}
public List<String> getDataList() {
return dataList;
}
public void setDataList(List<String> dataList) {
this.dataList = dataList;
}
public String getPlaceholder() {
return placeholder;
}
public void setPlaceholder(String placeholder) {
this.placeholder = placeholder;
}
public Short getColor() {
return color;
}
public void setColor(Short color) {
this.color = color;
}
}
/**
* 转换模板为表格标题
* @param template 模板
* @return
*/
public static List<String> transformTemplateToTitle(String template) {
List<LinkedHashMap> maps = JSON.parseArray(template, LinkedHashMap.class);
List<String> result = new ArrayList<>();
for (Map map : maps) {
for (Object o : map.entrySet()) {
Map.Entry entry = (Map.Entry) o;
result.add(String.valueOf(entry.getKey()));
}
}
return result;
}
/**
* 创建只带表头的模板
* @param template 模板
* @param excelHeaderMetaDataList 表头特殊单元格对象
* @return
*/
public static Workbook createWorkBook(String template, List<ExcelHeaderMetaData> excelHeaderMetaDataList) {
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet(ImportCreateMpEnum.SHEET_NAME_MP.getCode());
Row row = sheet.createRow(0);
// 1 构建占位符 - metadata键值对
Map<String, ExcelHeaderMetaData> placeholder2MetaData = new HashMap<>();
if (CollectionUtils.isNotEmpty(excelHeaderMetaDataList)) {
for (ExcelHeaderMetaData headerMetaData : excelHeaderMetaDataList) {
if (StringUtils.isNotBlank(headerMetaData.getPlaceholder())) {
placeholder2MetaData.put(headerMetaData.getPlaceholder(), headerMetaData);
}
}
}
// 2 创建表头
int cellIndex = 0;
List<String> titles = ExcelUtils.transformTemplateToTitle(template);
for (String title : titles) {
ExcelHeaderMetaData metaData = placeholder2MetaData.get(title);
if (metaData == null) {
Cell cell = row.createCell(cellIndex);
cell.setCellValue(title);
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
if (title.indexOf("*") == 0) {// 首位为*号设置单元格字体为红色
font.setColor(Font.COLOR_RED);
}
cellIndex++;
} else {
if (CollectionUtils.isEmpty(metaData.getDataList())) {
continue;
}
for (String data : metaData.getDataList()) {
Cell cell = row.createCell(cellIndex);
cell.setCellValue(data);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(metaData.getColor() == null ? IndexedColors.WHITE.getIndex() : metaData.getColor());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillBackgroundColor(metaData.getColor() == null ? IndexedColors.WHITE.getIndex() : metaData.getColor());
Font font = workbook.createFont();
font.setBold(true);
cellStyle.setFont(font);
if (data.indexOf("*") == 0) {// 首位为*号设置单元格字体为红色
font.setColor(Font.COLOR_RED);
}
cell.setCellStyle(cellStyle);
cellIndex++;
}
}
}
return workbook;
}
}
导入表格的工具类
最新推荐文章于 2022-11-25 09:34:25 发布