需要导入excel的样子
代码：说明 最多支持5级目录导入
package net.parim.spark.unicom.provider.careercenter.service;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import net.parim.spark.core.system.entity.UserGroup;
import net.parim.spark.core.system.security.UserToken;
import net.parim.spark.core.system.service.UserGroupService;
import net.parim.spark.unicom.provider.careercenter.entity.CareerTeachMaterial;
public class TeachingMaterialImport {
public static final String UPLOADTEACHEXCEL ="TEACHEXCEL";
public Workbook readFile(InputStream inputStream,String fileName){
Workbook wb = null;
Map<String,Object> map = new HashMap<>();
boolean isE2007 = false; //判断是否是excel2007格式
if(fileName.endsWith("xlsx")){
isE2007 = true;
}
try {
InputStream input = inputStream; //建立输入流
//根据文件格式(2003或者2007)来初始化
if(isE2007){
wb = new XSSFWorkbook(input);
}else{
wb = new HSSFWorkbook(input);
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return wb;
}
/**
* 校检表头
*/
public Map<String,Object> inspectHead(Sheet sheet){
//所有的单元格信息
List<CellRangeAddress> cras = getCombineCell(sheet);
//存储行信息
List<Map<String,Object>> irs = new ArrayList<>();
//表头只有2行
for(int i = 0; i < 2;i++){
Row row = sheet.getRow(i); //获取行
Map<String,Object> map1 = new HashMap<String,Object>();
//存储列信息
List<Map<Integer,Object>> items = new ArrayList<>();
if(isMergedRegion(sheet,i,0)){
int lastRow = getRowNum(cras,sheet.getRow(i).getCell(0),sheet);
for(;i<=lastRow;i++){
row = sheet.getRow(i);
int cellNum = row.getLastCellNum();//列数
Map<Integer,Object> map2 = new HashMap<Integer,Object>();
for(int ce=0;ce<cellNum;ce++){
map2.put(ce,getCellValue(row.getCell(ce)));
}
items.add(map2);
}
i--;
}else{
row = sheet.getRow(i);
int cellNum = row.getLastCellNum();//列数
Map<Integer,Object> map3 = new HashMap<Integer,Object>();
for(int ce=0;ce<cellNum;ce++){
map3.put(ce,getCellValue(row.getCell(ce)));
}
items.add(map3);
}
map1.put("cell",items);
irs.add(map1);
}
//获取目录个数，工种等级id
List<Map<String,Object>> list = (ArrayList)irs.get(0).get("cell");//获取list的个数
int catalogCount=0;
Map<String,Object> save = new HashMap<>();
Map<String,Object> saveGrade = new HashMap<>();
for(int s=0;s<list.get(1).size();s++){
if(list.get(1).get(s).toString().equals("")){
catalogCount++;
}else{
saveGrade.put(s+"", list.get(1).get(s).toString());
}
}
save.put("saveGrade", saveGrade);
//取得工种父级
save.put("catalogCount", catalogCount);
String gzName = (String)list.get(0).get(catalogCount++);
save.put("gzName", gzName);
return save;
}
/**
* 获取内部内容
*/
public List<Map<Integer,Object>> getBody(Sheet sheet){
List<Map<Integer,Object>> all = new ArrayList<>();
//所有的单元格信息
List<CellRangeAddress> cras = getCombineCell(sheet);
//存储行信息
List<Map<String,Object>> irs = new ArrayList<>();
int count = sheet.getLastRowNum()+1;//总行数
//表头只有2行
for(int i = 2; i < count;i++){
Row row = sheet.getRow(i); //获取行
Map<String,Object> map1 = new HashMap<String,Object>();
//存储列信息
List<Map<Integer,Object>> items = new ArrayList<>();
if(isMergedRegion(sheet,i,0)){
int lastRow = getRowNum(cras,sheet.getRow(i).getCell(0),sheet);
for(;i<=lastRow;i++){
row = sheet.getRow(i);
int cellNum = row.getLastCellNum();//列数
Map<Integer,Object> map2 = new HashMap<Integer,Object>();
for(int ce=0;ce<cellNum;ce++){
map2.put(ce,getCellValue(row.getCell(ce)));
}
items.add(map2);
}
i--;
}else{
row = sheet.getRow(i);
int cellNum = row.getLastCellNum();//列数
Map<Integer,Object> map3 = new HashMap<Integer,Object>();
for(int ce=0;ce<cellNum;ce++){
map3.put(ce,getCellValue(row.getCell(ce)));
}
items.add(map3);
}
map1.put("cell",items);
irs.add(map1);
}
for(int a=0;a<irs.size();a++){
List<Map<Integer,Object>> list = (ArrayList)irs.get(a).get("cell");//获取list的个数
if(list.size()>0){
for(int b=0;b<list.size();b++){
all.add(list.get(b));
}
}
}
return all;
}
/**
* 获取sheet中合并的单元格个数，并返回单元格list
* @param sheet
* @return List<CellRangeAddress>
*/
public List<CellRangeAddress> getCombineCell(Sheet sheet)
{
List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
//获得一个 sheet 中合并单元格的数量
int sheetmergerCount = sheet.getNumMergedRegions();
//遍历所有的合并单元格
for(int i = 0; i<sheetmergerCount;i++)
{
//获得合并单元格保存进list中
CellRangeAddress ca = sheet.getMergedRegion(i);
list.add(ca);
}
return list;
}
/**
* 判断指定的单元格是否是合并单元格
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
private boolean isMergedRegion(Sheet sheet,int row ,int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
return true;
}
}
}
return false;
}
private int getRowNum(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet){
int xr = 0;
int firstC = 0;
int lastC = 0;
int firstR = 0;
int lastR = 0;
for(CellRangeAddress ca:listCombineCell)
{
//获得合并单元格的起始行, 结束行, 起始列, 结束列
firstC = ca.getFirstColumn();
lastC = ca.getLastColumn();
firstR = ca.getFirstRow();
lastR = ca.getLastRow();
if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
{
if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
{
xr = lastR;
}
}
}
return xr;
}
/**
* 获取单元格的值
* @param cell
* @return
*/
public String getCellValue(Cell cell){
if(cell == null) return "";
if(cell.getCellType() == Cell.CELL_TYPE_STRING){
return cell.getStringCellValue();
}
return "";
}
/**
* 填充数据
* @param list
*/
public List<Map<Integer,Object>> fillData(List<Map<Integer,Object>> list){
for(int i=0;i<list.size();i++){
int ii=i;
if(i==0){
ii=i;
}else{
ii--;
}
for(int j=0;j<5;j++){
if(list.get(i).get(j).toString().equals("")){
list.get(i).remove(j);
list.get(i).put(j, list.get(ii).get(j));
}else{
break;
}
}
}
return list;
}
/**
* 遍历list数组，实现数据拆分
* @param map
* @param list
*/
public List<Map<String,Object>> splitData(Map<String,Object> map,List<Map<Integer,Object>> list){
List<Map<String,Object>> all = new ArrayList<>();
//获取总共的列数
int colNum = list.get(0).size();
//获取工种之前的列数
Map<String,Object> catalogCount = (HashMap<String,Object>)map.get("saveGrade");
int count = colNum-catalogCount.size();
//1.循环list
for(int i=0;i<list.size();i++){
for(int j=0;j<count;j++){
if(!list.get(i).get(j).toString().equals("")){
int jj=j;
Map<String,Object> col = new HashMap<>();
if((!list.get(i).get(j).toString().equals(""))&&j==0){
col.put("parent", 1);
}else{
col.put("parent", list.get(i).get(jj-1).toString());
}
col.put("name", list.get(i).get(j));
for(int a=(colNum-catalogCount.size());a<colNum;a++){
col.put(a+"", list.get(i).get(a));
}
all.add(col);
}
}
}
return all;
}
/**
* 去重重复行
* @param list
*/
public List<Map<String,Object>> duplicateRemoval(List<Map<String,Object>> list){
List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
result.add(list.get(0));
for(int i=0;i<list.size();i++){
int f=0;
for(int j=0;j<result.size();j++){
String first= result.get(j).get("name").toString();
String second= list.get(i).get("name").toString();
if(first.equals(second)){
f=0;
break;
}else{
f++;
}
if(f==result.size()){
result.add(list.get(i));
}
}
}
return result;
}
/**
* 得到根目录
* @param list
*/
public String getRootCategory(List<Map<Integer,Object>> list){
String add = "";
Set<String> set = new LinkedHashSet<String>();
for(int i=0;i<list.size();i++){
set.add(list.get(i).get(0).toString());
}
for (String str : set) {
add+="'"+str+"',";
}
return add.subSequence(0, add.length()-1).toString();
}
/**
* 封装工种数据
* @param list
*/
public List<Map<String,Object>> packingIdentity(Map<String,Object> gz,Map<String,Object> map,List<Map<String,Object>> list){
Map<String,Object> iscompulsoory = new HashMap<>();
iscompulsoory.put("熟练掌握", 1);
iscompulsoory.put("掌握", 1);
iscompulsoory.put("熟悉", 0);
iscompulsoory.put("了解", 0);
iscompulsoory.put("不需要", 0);
Map<String,Object> grade = (HashMap<String,Object>)map.get("saveGrade");
for(int i=0;i<list.size();i++){
List<Long> list1 = new ArrayList<>();
for (String key : list.get(i).keySet()) {
if(grade.containsKey(key)){
if(gz.containsKey(grade.get(key))){
list1.add(Long.valueOf(gz.get(grade.get(key)).toString()));
}
}
}
List<Long> list2 = new ArrayList<>();
int f=0;
for (Object key : list.get(i).values()) {
if(iscompulsoory.containsKey(key)){
list2.add(Long.valueOf(iscompulsoory.get(key).toString()));
}
}
list.get(i).put("GZID", list1);//存储工种id
list.get(i).put("GZIS", list2);//是否必修
}
return list;
}
/**
* 将list解析成bean,进行存储信息（教材、工种）
*/
public void analysisList2bean(List<Map<String,Object>> list,CareerTeachManageService categoryService,UserToken userToken,UserGroupService userGroupService,Long userGroupId){
//map 用来存储父类
Map<String,Object> parent = new HashMap<>();
for(int i=0;i<list.size();i++){
CareerTeachMaterial ctm = new CareerTeachMaterial();
ctm.setName(list.get(i).get("name").toString());//教材名称
ctm.setSite(userToken.getCurrentSite());
//如果父类为1，则是等级目录
if(list.get(i).get("parent").toString().equals("1")){
CareerTeachMaterial parent1 = categoryService.findOne(1L);
ctm.setParent(parent1);
}else{
if(parent.containsKey(list.get(i).get("parent").toString())){
Long parentId = Long.valueOf(parent.get(list.get(i).get("parent").toString()).toString());
CareerTeachMaterial parent2 = categoryService.findOne(parentId);
ctm.setParent(parent2);
}
}
UserGroup userGroup=userGroupService.findOne(userGroupId);
ctm.setUserGroup(userGroup);
Long maxSortId = null;
if (ctm.getParent() == null) {
} else {
maxSortId = categoryService.hasParentFindMaxOne(ctm.getParent().getId(),
ctm.getUserGroup().getId());
}
if (maxSortId == null) {
ctm.setSortId((long) 1);
} else {
ctm.setSortId(maxSortId + 1);
}
//调用save方法
CareerTeachMaterial teach = categoryService.save(ctm);
//返回id值
parent.put(list.get(i).get("name").toString(), teach.getId());
//保存工种
List<Long> gzid = (ArrayList<Long>)list.get(i).get("GZID");
List<Long> gzis = (ArrayList<Long>)list.get(i).get("GZIS");
for(int j=0;j<gzid.size();j++){
Map<String,Object> isCom = new HashMap<>();
isCom.put("cace_material_id_com", teach.getId());
isCom.put("career_identiey_id", gzid.get(j));
isCom.put("is_compulsoory", gzis.get(j));
categoryService.insertIdentityIsCompulsory(isCom);
}
}
}
/**
* 检查重复
* @param sheet
* @return
*/
public String checkRepeat(Sheet sheet){
TeachingMaterialImport ti = new TeachingMaterialImport();
//1.获取body内容
List<Map<Integer,Object>> bodyList = ti.getBody(sheet);
//2.将body中的null就行数据填充
List<Map<Integer,Object>> fillList = ti.fillData(bodyList);
return ti.getRootCategory(fillList);
}
public void executeAllMethod(Sheet sheet,Map<String,Object> head,Map<String,Object> gz,CareerTeachManageService categoryService,UserToken userToken,UserGroupService userGroupService,Long userGroupId){
TeachingMaterialImport ti = new TeachingMaterialImport();
//1.获取body内容
List<Map<Integer,Object>> bodyList = ti.getBody(sheet);
//2.将body中的null就行数据填充
List<Map<Integer,Object>> fillList = ti.fillData(bodyList);
//3.拆分表格中的数据
List<Map<String,Object>> splitList = ti.splitData(head,fillList);
//4.去除重复行
List<Map<String,Object>> duplicateList = ti.duplicateRemoval(splitList);
//5.封装其中包含的工种id及是否必修
List<Map<String,Object>> packingList =ti.packingIdentity(gz,head, duplicateList);
//6.将list封装成bean就行保存
ti.analysisList2bean(packingList,categoryService,userToken,userGroupService,userGroupId);
}
public static void main(String[] args) throws FileNotFoundException {
File file=new File("d://111.xlsx");
InputStream is = new FileInputStream(file);
TeachingMaterialImport ti = new TeachingMaterialImport();
Workbook wb = ti.readFile(is, "111.xlsx");
Sheet sheet = wb.getSheetAt(0);
Map<String,Object> map = ti.inspectHead(sheet);
List<Map<Integer,Object>> list = ti.getBody(sheet);
List<Map<Integer,Object>> fillList = ti.fillData(list);
// Set<String> set = ti.getRootCategory(fillList);
List<Map<String,Object>> splitList = ti.splitData(map,fillList);
List<Map<String,Object>> duplicateList = ti.duplicateRemoval(splitList);
// for(int i=0;i<fillList.size();i++){
// System.out.println(fillList.get(i));
// }
// List<Map<String,Object>> list2 = ti.splitData(map,ti.fillData(list));
// List<Map<String,Object>> list3 =ti.duplicateRemoval(list2);
//List<Map<String,Object>> list4 =ti.packingIdentity(map, list3);
// for(int i=0;i<list4.size();i++){
// System.out.println(list4.get(i));
// }
//ti.analysisList2bean(list4);
}
}效果图
有喜欢的朋友可以关注下头条号《老徐聊技术》
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
