java 导入dbf和excel解析 导出dbf和excel
<p><img src="https://img2024.cnblogs.com/blog/1212244/202507/1212244-20250715101441997-23586322.jpg"></p><p>导入dbf和excel解析:</p>
<details>
<summary>点击查看代码</summary>
<pre><code> package com.test.server.utils;
import com.linuxense.javadbf.DBFReader;
import lombok.extern.slf4j.Slf4j;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @description: dbf工具类
*/
@Slf4j
public class DBFUtils {
public static List<Map<String, Object>> readDbf(byte[] bytes) {
List<Map<String, Object>> list = new ArrayList<>();
try {
//读取dbf文件,防止中文乱码
DBFReader reader = new DBFReader(new ByteArrayInputStream(bytes), StandardCharsets.ISO_8859_1);
list = readDbfStream(reader);
} catch (Exception e) {
log.error("读取dbf文件失败", e);
}
return list;
}
//读取dbf文件转换为List<Map<String, Object>>
public static List<Map<String, Object>> readDbf(String filePath) {
List<Map<String, Object>> list = new ArrayList<>();
try {
//读取dbf文件
DBFReader reader = new DBFReader(new FileInputStream(filePath));
list = readDbfStream(reader);
} catch (Exception e) {
log.error("读取dbf文件失败", e);
}
return list;
}
private static List<Map<String, Object>> readDbfStream(DBFReader reader) {
List<Map<String, Object>> list = new ArrayList<>();
try {
//获取字段数量
int fieldCount = reader.getFieldCount();
//获取记录数量
int recordCount = reader.getRecordCount();
//遍历记录
for (int i = 0; i < recordCount; i++) {
//获取记录
Object[] record = reader.nextRecord();
//创建map
Map<String, Object> map = new HashMap<>();
//遍历字段
for (int j = 0; j < fieldCount; j++) {
//获取字段名
String fieldName = reader.getField(j).getName();
//获取字段值
Object fieldValue = record;
//添加到map
map.put(fieldName, fieldValue);
}
//添加到list
list.add(map);
}
} catch (Exception e) {
log.error("读取dbf文件失败", e);
}
return list;
}
}
excel工具类
package com.test.server.utils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelTool<T> {
private HSSFWorkbook workbook;//excel 对象
private String title; //表格标题
private int colWidth = 20; //单元格宽度
private int rowHeight = 20;//单元格行高度
private HSSFCellStyle styleHead; //表头样式
private HSSFCellStyle styleBody; //主体样式
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //日期格式化,默认yyyy-MM-dd HH:mm:ss
/**
* 无参数 初始化 对象
*/
public ExcelTool(){
this.title="sheet1";
this.workbook = new HSSFWorkbook();
init(0);
}
/**
* 有参数 初始化 对象
* @param title
* @param colWidth
* @param rowHeight
* @param dateFormat
*/
public ExcelTool(String title,int colWidth,int rowHeight,String dateFormat){
this.colWidth = colWidth;
this.rowHeight = rowHeight;
this.title = title;
this.workbook = new HSSFWorkbook();
this.sdf = new SimpleDateFormat(dateFormat);
init(0);
}
public ExcelTool(String title,int colWidth,int rowHeight){
this.colWidth = colWidth;
this.rowHeight = rowHeight;
this.title = title;
this.workbook = new HSSFWorkbook();
init(0);
}
public ExcelTool(String title,int colWidth,int rowHeight,int flag){
this.colWidth = colWidth;
this.rowHeight = rowHeight;
this.title = title;
this.workbook = new HSSFWorkbook();
init(flag);
}
public ExcelTool(String title){
this.title = title;
this.workbook = new HSSFWorkbook();
init(0);
}
/**ExcelTool 属性 get、set 方法 开始*/
public int getColWidth() {
return colWidth;
}
public void setColWidth(int colWidth) {
this.colWidth = colWidth;
}
public int getRowHeight() {
return rowHeight;
}
public void setRowHeight(int rowHeight) {
this.rowHeight = rowHeight;
}
public HSSFWorkbook getWorkbook() {
return this.workbook;
}
public void setWorkbook(HSSFWorkbook workbook) {
this.workbook = workbook;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public HSSFCellStyle getStyleHead() {
return styleHead;
}
public void setStyleHead(HSSFCellStyle styleHead) {
this.styleHead = styleHead;
}
public HSSFCellStyle getStyleBody() {
return styleBody;
}
public void setStyleBody(HSSFCellStyle styleBody) {
this.styleBody = styleBody;
}
/**ExcelTool 属性 get、set 方法 结束*/
//内部统一调用的样式初始化
private void init(int styleFlag){
this.styleHead = this.workbook.createCellStyle();
this.styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
this.styleHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
this.styleHead.setRightBorderColor(HSSFColor.BLACK.index);
this.styleHead.setBottomBorderColor(HSSFColor.BLACK.index);
if (styleFlag == 1) {
this.styleBody = this.workbook.createCellStyle();
this.styleBody.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中ALIGN_CENTER
this.styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
this.styleBody.setRightBorderColor(HSSFColor.BLACK.index);
this.styleBody.setBottomBorderColor(HSSFColor.BLACK.index);
this.styleBody.setBorderRight((short) 1);// 边框的大小
this.styleBody.setBorderBottom((short) 1);// 边框的大小
} else {
this.styleBody = this.workbook.createCellStyle();
this.styleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中ALIGN_CENTER
this.styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
this.styleBody.setRightBorderColor(HSSFColor.BLACK.index);
this.styleBody.setBottomBorderColor(HSSFColor.BLACK.index);
this.styleBody.setBorderRight((short) 1);// 边框的大小
this.styleBody.setBorderBottom((short) 1);// 边框的大小
}
}
/**
* 导出表格 无返回
* @param listTpamscolumn 表头数据
* @param datas 行内数据
* @param FilePath 保存路径
* @param flag
* @param rowFlag
* @throws Exception
*/
public HSSFWorkbook exportExcel(List<Column> listTpamscolumn, List<T> datas, String FilePath, boolean flag, boolean rowFlag) throws Exception{
splitDataToSheets(datas, listTpamscolumn,flag,rowFlag);
save(this.workbook,FilePath);
return this.workbook;
}
/**
* 返回workbook
* @param listTpamscolumn 表头数据
* @param datas 行内数据
* @param flag 是否写入行内数据
* @return
* @throws Exception
*/
public HSSFWorkbook exportWorkbook(List<Column> listTpamscolumn, List<T> datas , boolean flag) throws Exception{
splitDataToSheets(datas, listTpamscolumn,flag,false);
return this.workbook;
}
/**
* 导出表格 有返回值
* @param listTpamscolumn 表头数据
* @param datas 行内数据
* @param flag只输出表头数据
* @param rowFlag
* @return
* @throws Exception
*/
public InputStream exportExcel(List<Column> listTpamscolumn, List<T> datas, boolean flag, boolean rowFlag) throws Exception {
splitDataToSheets(datas, listTpamscolumn,flag,rowFlag);
return save(this.workbook);
}
/**
* 导出Excel,适用于web导出excel
* @param sheet excel
* @param data 行内数据
* @param listTpamscolumn 表头数据
* @param flag 只输出表头数据
* @param rowFlag 输出展示数据的结构(表头下面行的数据)
* @throws Exception
*/
private void writeSheet(HSSFSheet sheet, List<T> data, List<Column> listTpamscolumn, boolean flag, boolean rowFlag) throws Exception {
sheet.setDefaultColumnWidth(colWidth);
sheet.setDefaultRowHeightInPoints(rowHeight);
sheet = createHead(sheet, listTpamscolumn.get(0).getTotalRow(), listTpamscolumn.get(0).getTotalCol());
createHead(listTpamscolumn,sheet,0);
if(flag)//控制是否 bug修复：每次写入行数据时，总是漏第一个条数据 rowIndex 错误
writeSheetContent(listTpamscolumn,data,sheet, listTpamscolumn.get(0).getTotalRow()+1,rowFlag);
}
/**
*拆分sheet，因为每个sheet不能超过65535，否则会报异常
* @param data 行内数据
* @param listTpamscolumn 表头数据
* @param flag 只输出表头数据
* @param rowFlag输出展示数据的结构(表头下面行的数据)
* @throws Exception
*/
private void splitDataToSheets(List<T> data, List<Column> listTpamscolumn, boolean flag, boolean rowFlag)throws Exception{
int dataCount = data.size();
int maxColumn = 65535;
int pieces = dataCount/maxColumn;
for(int i = 1; i <= pieces;i++){
HSSFSheet sheet = this.workbook.createSheet(this.title+i);
List<T> subList = data.subList((i-1)*maxColumn, i*maxColumn);
writeSheet(sheet,subList, listTpamscolumn,flag,rowFlag);
}
HSSFSheet sheet = this.workbook.createSheet(this.title+(pieces+1));
writeSheet(sheet, data.subList(pieces*maxColumn, dataCount), listTpamscolumn,flag,rowFlag);
}
/**
* 把数据写入到单元格
* @param listTpamscolumn 表头数据
* @param datas 行内数据
* @param sheet 工作表（excel分页）
* @throws Exception
* void
*/
private void writeSheetContent(List<Column> listTpamscolumn, List<T> datas, HSSFSheet sheet, int rowIndex, boolean rowFlag) throws Exception{
HSSFRow row = null;
List<Column>listCol=new ArrayList<>();
rowFlag=false;
if(rowFlag){//暂时没有用 后面扩展用
for (int i = 0, index = rowIndex; i < datas.size(); i++, index++) {
row = sheet.createRow(index);//创建行
for (int j = 0; j < listTpamscolumn.size(); j++) {
createColl(row,j,listTpamscolumn.get(j).getFieldName() ,datas.get(i));
}
}
}
else {
getColumnList(listTpamscolumn, listCol);
for (int i = 0, index = rowIndex; i < datas.size(); i++, index++) {
row = sheet.createRow(index);//创建行
for (int j = 0; j < listCol.size(); j++) {
Column c = listCol.get(j);
createCol(row, c, datas.get(i));
}
}
}
}
/**
* 根据list 来创建单元格 暂时没有用
* @param row
* @param j
* @param finame
* @param t
*/
private void createColl(HSSFRow row, int j, String finame, T t) {
HSSFCell cell = row.createCell(j);//创建单元格
cell.setCellStyle(this.styleBody); //设置单元格样式
String text="";
if(t instanceof List){
List<Map> temp= (List<Map>)t;
if(j>=temp.size()) return;
text=String.valueOf(temp.get(j).get(finame)==null?"": temp.get(j).get(finame));
}
HSSFRichTextString richString = new HSSFRichTextString(text);
cell.setCellValue(richString);
}
/**
* 把column的columnList整理成一个list<column> 过滤表头的脏数据
* @param list 表头数据
* @param listCol 返回新的list
* @return
* List<column>
*/
private void getColumnList(List<Column> list, List<Column> listCol){
for(int i = 0; i < list.size(); i++){
if(list.get(i).getFieldName() != null){
listCol.add(list.get(i));
}
List<Column> listChilren = list.get(i).getListTpamscolumn();
if(listChilren.size() > 0){
getColumnList( listChilren, listCol);
}
}
}
/**
* 保存Excel到InputStream，此方法适合web导出excel
* @param workbook
* @return
*/
private InputStream save(HSSFWorkbook workbook) {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
try {
workbook.write(bos);
InputStream bis = new ByteArrayInputStream(bos.toByteArray());
return bis;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 保存excel到本机指定的路径
* @param workbook
* @param filePath
* @throws IOException
*/
private void save(HSSFWorkbook workbook, String filePath){
File file = new File(filePath);
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
FileOutputStream fOut = null;
try {
fOut = new FileOutputStream(file);
workbook.write(fOut);
fOut.flush();
} catch (Exception e) {
e.printStackTrace();
}
try {if(null!=fOut) fOut.close();} catch (Exception e1) { }
}
/**
* 创建行
* @param row Excel对应的行
* @param tpamscolumn 当前单元格属性
* @param v
* @param j
* @return
* @throws Exception
*/
public int createRowVal(HSSFRow row, Column tpamscolumn, T v, int j) throws Exception{
//遍历标题
if(tpamscolumn.getListTpamscolumn() != null && tpamscolumn.getListTpamscolumn().size() > 0){
for(int i = 0; i < tpamscolumn.getListTpamscolumn().size(); i++){
createRowVal(row, tpamscolumn.getListTpamscolumn().get(i),v,j);
}
}else{
createCol(row, tpamscolumn,v);
}
return j;
}
/**
* 创建单元格
* @param rowExcel对应的行
* @param tpamscolumn 当前单元格对象
* @param v
* @throws Exception
*/
public void createCol(HSSFRow row, Column tpamscolumn, T v) throws Exception{
HSSFCell cell = row.createCell( tpamscolumn.getCol());//创建单元格
cell.setCellStyle(this.styleBody); //设置单元格样式
final Object[] value = {null};
if(v instanceof Map){
Map m=(Map)v;
m.forEach((k, val) -> {
if(k.equals(tpamscolumn.getFieldName()) && !tpamscolumn.isHasChilren()){
value =val;
}
});
}else {
Class<?> cls = v.getClass();// 拿到该类
Field[] fields=cls.getDeclaredFields();// 获取实体类的所有属性，返回Field数组
for(int i=0;i<fields.length;i++){
Field f = fields;
f.setAccessible(true); // 设置些属性是可以访问的
if(tpamscolumn.getFieldName().equals(f.getName()) && !tpamscolumn.isHasChilren() )// && !tpamscolumn.isHasChilren()
value =f.get(v);
if(value instanceof Date)
value = parseDate((Date) value);
}
}
if(value!=null) {
HSSFRichTextString richString = new HSSFRichTextString(value.toString());
cell.setCellValue(richString);
}
}
/**
* 时间转换
* @param date
* @return
* String
*/
private String parseDate(Date date){
String dateStr = "";
try{
dateStr = this.sdf.format(date);
} catch (Exception e){
e.printStackTrace();
}
return dateStr;
}
/**
* 根据数据的行数和列数，在excel创建单元格cell
* @param sheetCo excel分页
* @param r excel 行数
* @param c excel 列数
* @return
*/
public HSSFSheet createHead(HSSFSheet sheetCo, int r, int c){
for(int i = 0; i <r; i++){
HSSFRow row = sheetCo.createRow(i);
for(int j = 0; j <c; j++){
HSSFCell cell=row.createCell(j);
}
}
return sheetCo;
}
/**
* 使用递归 在excel写入表头数据 支持单级，多级表头的创建
* @param listTpamscolumn 表头数据
* @param sheetCo 哪个分页
* @param rowIndex 当前Excel的第几行
*/
public void createHead(List<Column> listTpamscolumn, HSSFSheet sheetCo, int rowIndex){
HSSFRow row = sheetCo.getRow(rowIndex);
// if(row == null)row = sheetCo.createRow(rowIndex);
int len = listTpamscolumn.size();//当前行 有多少列
for(int i = 0; i <len; i++){//i是headers的索引，n是Excel的索引 多级表头
Column tpamscolumn = listTpamscolumn.get(i);
//创建这一行的第几列单元格
int r = tpamscolumn.getRow();
int rLen = tpamscolumn.getrLen();
int c = tpamscolumn.getCol();
int cLen = tpamscolumn.getcLen();
int endR = r+rLen;
int endC = c+cLen;
if(endC > c){
endC--;
}
HSSFCell cell = row.getCell(c);
// if( null == cell)cell = row.createCell(c);
HSSFRichTextString text = new HSSFRichTextString(tpamscolumn.getContent());
cell.setCellStyle(this.styleHead); //设置表头样式
cell.setCellValue(text);
// 合并单元格
CellRangeAddress cra = new CellRangeAddress(r,endR,c,endC);
sheetCo.addMergedRegion(cra);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, cra,sheetCo,this.workbook); // 下边框
RegionUtil.setBorderLeft(1, cra, sheetCo,this.workbook); // 左边框
RegionUtil.setBorderRight(1, cra, sheetCo,this.workbook); // 有边框
if(tpamscolumn.isHasChilren()){
rowIndex=r+1;
createHead( tpamscolumn.getListTpamscolumn(), sheetCo,rowIndex);
}
}
}
/**
* 转换成column对象
*支持List<T>的数据结构:map String ，只能是单级的数据
* @param list 需要转换的数据
* @return
*/
public List<Column> columnTransformer(List<T>list){
List<Column> lc=new ArrayList<>();
if(list.get(0)instanceof Map) {
final int[] i = {1};
for (Map<String, String> m :(List<Map<String, String>>)list ) {
m.forEach((k, val) -> {
Column tpamscolumn = new Column();
tpamscolumn.setId(String.valueOf(i));
tpamscolumn.setPid("0");
tpamscolumn.setContent(k);
tpamscolumn.setFieldName(val);
lc.add(tpamscolumn);
i++;
});
}
} else {
int i = 1;
for(String s:(List<String>)list) {
Column tpamscolumn = new Column();
tpamscolumn.setId(String.valueOf(i));
tpamscolumn.setPid("0");
tpamscolumn.setContent(s);
tpamscolumn.setFieldName(null);
lc.add(tpamscolumn);
i++;
}
}
setParm(lc,"0");//处理一下
List<Column> s = TreeTool.buildByRecursive(lc,"0");
setColNum(lc,s,s);
return s;
}
/**
* 转换成column对象 返回tree数据结构
* 支持：List<map>、某个具体对象（entity）数据的转换
* @param list 需要转换的数据
* @param id 当前节点id 字段的名称主键
* @param pid 父节点id 字段的名称
* @param content 填写表头单元格内容的 字段名称
* @param fielName 填写行内数据对的 字段名称
* @param rootidrootid的值
* @return
* @throws Exception
*/
public List<Column> columnTransformer(List<T> list , String id, String pid, String content, String fielName,String rootid) throws Exception {
List<Column> lc=new ArrayList<>();
if(list.get(0) instanceof Map){
for(Map m:(List<Map>)list) {
Column tpamscolumn = new Column();
m.forEach((k, val) -> {//java8 以上的遍历方式
if (id.equals(k))
tpamscolumn.setId(String.valueOf(val));
if (pid.equals(k)) tpamscolumn.setPid((String) val);
if (content.equals(k)) tpamscolumn.setContent((String) val);
if (fielName.equals(k) && fielName != null) tpamscolumn.setFieldName((String) val);
});
lc.add(tpamscolumn);
}
}else {
for (T t : list) {//反射
Column tpamscolumn = new Column();
Class cls = t.getClass();
Field[] fs=cls.getDeclaredFields();
for (int i = 0; i < fs.length; i++) {
Field f = fs;
f.setAccessible(true); // 设置些属性是可以访问的
if (id.equals(f.getName()) && f.get(t) != null)
tpamscolumn.setId(f.get(t).toString());
if (pid.equals(f.getName()) && f.get(t) != null)
tpamscolumn.setPid(f.get(t).toString());
// if (pid.equals(f.getName()) && ( f.get(t) == null || "".equals(f.get(t)))) tpamscolumn.setPid("0");
if (content.equals(f.getName()) && f.get(t) != null)
tpamscolumn.setContent(f.get(t).toString());
if ( f.get(t) != null&& fielName!=null && fielName.equals(f.getName()))
tpamscolumn.setFieldName(f.get(t).toString());
}
lc.add(tpamscolumn);
}
}
setParm(lc,rootid);//处理一下
List<Column> s = TreeTool.buildByRecursive(lc,rootid);
setColNum(lc,s,s);
return s;
}
/**
* 设置基础的参数
* @param list
*/
public static void setParm(List<Column> list,String rootid){
int row = 0;//excel第几行
int rLen = 0; //excel 跨多少行
int totalRow = TreeTool.getMaxStep(list);
int totalCol = TreeTool.getDownChilren(list,rootid);
for(int i=0;i<list.size();i++){
Column poit= list.get(i);
int tree_step = TreeTool.getTreeStep(list,poit.getPid(),0);//往上遍历tree
poit.setTree_step(tree_step);
poit.setRow(tree_step);//设置第几行
//判断是否有节点
boolean hasCh = TreeTool.hasChild( list,poit);
poit.setHasChilren(hasCh);
if(hasCh){
poit.setrLen(0);//设置跨多少行
}else{
if(tree_step < totalRow){
rLen = totalRow - tree_step;
}
poit.setrLen(rLen);
}
// boolean flag=false;//控制只有root 节点才有总的行数信息
// if(rootid == null && rootid == poit.getId() )flag = true;
// if(rootid != null && rootid.equals(poit.getId()))flag = true;
// if(flag){
//
// }
poit.setTotalRow(totalRow);
poit.setTotalCol(totalCol);
}
}
/**
* 设置基础的参数
* @param list 所有list数据，一条一条
* @param treeList 转成tree结构的list
*/
public static void setColNum(List<Column> list, List<Column> treeList,List<Column> flist){
// int col = pcIndex;//excel第几列
// int cLen ;//xcel跨多少列
List<Column> new_list = new ArrayList<>();//新的遍历list
for(int i = 0;i < treeList.size();i++){
Column poit= treeList.get(i);
// String temp_id = TreeTool.getStepFid(list,poit.getId() ,1);
int col = TreeTool.getFCol(list,poit.getPid()).getCol();
int brotherCol = TreeTool.getBrotherChilNum(list,poit);
poit.setCol(col+brotherCol);
int cLen = TreeTool.getDownChilren(list,poit.getId());
if(cLen<=1)cLen=0;
// elsecLen--;
poit.setcLen(cLen);//设置跨多少列
if(poit.getListTpamscolumn().size()>0){
new_list.addAll(poit.getListTpamscolumn());
}
}
if(new_list.size() > 0){
setColNum( list,new_list,flist);
}
}
//========上部分是导出excel的使用（生成excel），下部分是解析excel，由于excel导入==================================================================================================================================
/**
* 根据HSSFCell类型设置数据
* @param cell 单元格
* @return
*/
public static String getCellFormatValue(Cell cell) {
String cellvalue = "";
if (cell != null) {
switch (cell.getCellType()) { // 判断当前Cell的Type
case HSSFCell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
else { // 如果是纯数字
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case HSSFCell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRIN
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
default:// 默认的Cell值
cellvalue = "";
}
} else {
cellvalue = "";
}
return cellvalue;
}
/**
* 描述：根据文件后缀，自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public static Workbook getWorkbookType(InputStream inStr, String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(".xls".equals(fileType)){
wb = new HSSFWorkbook(inStr);//2003-
}else if(".xlsx".equals(fileType)){
wb = new XSSFWorkbook(inStr);//2007+
}else{
throw new Exception("导入格式错误");
}
return wb;
}
/**
* 获取单元格数据内容为字符串类型的数据
* @param cell Excel单元格
* @return String 单元格数据内容
*/
public static String getStringCellValue(Cell cell) {
String strCell = "";
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue().trim();
break;
case Cell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue()).trim();
break;
case Cell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue()).trim();
break;
case Cell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
return strCell;
}
/**
* 判断指定的单元格是否是合并单元格
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
public static 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;
}
/**
* 获取合并单元格的值
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
public static String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getStringCellValue(fCell);
}
}
}
return "";
}
/**
* 获取excel的值 返回的 List<List<String>>的数据结构
* @paramfileUrl 文件路径
* @paramsheetNum 工作表（第几分页）
* @returnList<List<String>>
*/
publicList<List<String>> getExcelValues(String fileUrl, int sheetNum) throws Exception{
List<List<String>> values = new ArrayList<List<String>>();
File file = new File(fileUrl);
InputStream is = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(is);
int sheetCount = sheetNum-1; //workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页
//int sheetCount1= workbook.getNumberOfSheets();
Sheet sheet = workbook.getSheetAt(sheetCount); //读取第几个工作表sheet
int rowNum = sheet.getLastRowNum();//有多少行
for (int i = 1; i <= rowNum; i++) {
Row row = sheet.getRow(i);//第i行
if (row == null) {//过滤空行
continue;
}
List<String> list = new ArrayList<>();
int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
for (int j = 0; j < colCount; j++) {//第j列://+1是因为最后一列是空 也算进去
Cell cell = row.getCell(j);
String cellValue;
boolean isMerge = false;
if (cell != null) {
isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
}
//判断是否具有合并单元格
if (isMerge) {
cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
} else {
cellValue = getStringCellValue(cell);
}
list.add(cellValue);
}
values.add(list);
}
return values;
}
/**
*判断整行是否为空
*@param rowexcel得行对象
*@parammaxRow 有效值得最大列数
*/
private static boolean CheckRowNull(Row row, int maxRow) {
int num = 0;
for (int j = 0; j < maxRow; j++) {
Cell cell=row.getCell(j);
if (cell==null||cell.equals("")||cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
num++;
}
}
if(maxRow==num) return true;
return false;
}
/**
* 根据sheet数获取excel的值 返回List<List<Map<String,String>>>的数据结构
* @paramsheetNum工作表（第几分页）
* @return List<List<Map<String,String>>>
*/
public static List<Map<String,Object>> getExcelMapVal(InputStream is, int sheetNum) throws Exception{
Workbook workbook = WorkbookFactory.create(is);
int sheetCount = sheetNum - 1; //workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页
//int sheetCount1= workbook.getNumberOfSheets();
Sheet sheet = workbook.getSheetAt(sheetCount); //读取第几个工作表sheet
int rowNum = sheet.getLastRowNum();//有多少行
Row rowTitle = sheet.getRow(0);//第i行
int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
List<Map<String,Object>> list = new ArrayList<>();
for (int i = 1; i <= rowNum; i++) {
Row row = sheet.getRow(i);//第i行
if (row == null || CheckRowNull(row,colCount)) {//过滤空行
continue;
}
Map<String,Object> map=new HashMap<>();
for (int j = 0; j < colCount; j++) {//第j列://+1是因为最后一列是空 也算进去
Cell cell = row.getCell(j);
Cell cellTitle = rowTitle.getCell(j);
String cellValue;
String cellKey=getStringCellValue(cellTitle);
boolean isMerge = false;
if (cell != null) {
isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
}
//判断是否具有合并单元格
if (isMerge) {
cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
} else {
cellValue = getStringCellValue(cell);
}
map.put(cellKey,cellValue);
}
list.add(map);
}
return list;
}
/**
* 根据sheet数获取excel的值 返回List<List<Map<String,String>>>的数据结构
* @paramfileUrl文件路径
* @paramsheetNum工作表（第几分页）
* @return List<List<Map<String,String>>>
*/
publicList<List<Map<String,String>>> getExcelMapVal(String fileUrl, int sheetNum) throws Exception{
List<List<Map<String,String>>> values = new ArrayList<List<Map<String,String>>>();
File file = new File(fileUrl);
InputStream is = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(is);
int sheetCount = sheetNum - 1; //workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页
//int sheetCount1= workbook.getNumberOfSheets();
Sheet sheet = workbook.getSheetAt(sheetCount); //读取第几个工作表sheet
int rowNum = sheet.getLastRowNum();//有多少行
Row rowTitle = sheet.getRow(0);//第i行
int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
for (int i = 1; i <= rowNum; i++) {
Row row = sheet.getRow(i);//第i行
if (row == null || CheckRowNull(row,colCount)) {//过滤空行
continue;
}
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
for (int j = 0; j < colCount; j++) {//第j列://+1是因为最后一列是空 也算进去
Map<String,String> map=new HashMap<>();
Cell cell = row.getCell(j);
Cell cellTitle = rowTitle.getCell(j);
String cellValue;
String cellKey=getStringCellValue(cellTitle);
boolean isMerge = false;
if (cell != null) {
isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
}
//判断是否具有合并单元格
if (isMerge) {
cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
} else {
cellValue = getStringCellValue(cell);
}
map.put(cellKey,cellValue);
list.add(map);
}
values.add(list);
}
return values;
}
/**
* 获取当前excel的工作表sheet总数
* @param fileUrl
* @return
* @throws Exception
*/
public int hasSheetCount(String fileUrl)throws Exception{
File file = new File(fileUrl);
InputStream is = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(is);
int sheetCount= workbook.getNumberOfSheets();
return sheetCount;
}
}
Column.java
package com.test.utils;
import java.util.ArrayList;
import java.util.List;
public class Column {
//单元格内容
private String content;
//字段名称，用户导出表格时反射调用
private String fieldName;
//这个单元格的集合
private List<Column> listTpamscolumn = new ArrayList<Column>();
int totalRow;
int totalCol;
int row;//excel第几行
int col;//excel第几列
int rLen; //excel 跨多少行
int cLen;//excel跨多少列
private boolean HasChilren;//是否有子节点
private int tree_step;//树的级别 从0开始
private String id;
private String pid;
public Column(){};
public Column(String content, String fieldName){
this.content = content;
this.fieldName = fieldName;
}
public Column(String fieldName, String content, int tree_step) {
this.tree_step = tree_step;
this.fieldName = fieldName;
this.content = content;
}
public int getTotalRow() {
return totalRow;
}
public void setTotalRow(int totalRow) {
this.totalRow = totalRow;
}
public int getTotalCol() {
return totalCol;
}
public void setTotalCol(int totalCol) {
this.totalCol = totalCol;
}
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public boolean isHasChilren() {
return HasChilren;
}
public void setHasChilren(boolean hasChilren) {
HasChilren = hasChilren;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public List<Column> getListTpamscolumn() {
return listTpamscolumn;
}
public void setListTpamscolumn(List<Column> listTpamscolumn) {
this.listTpamscolumn = listTpamscolumn;
}
public int getTree_step() {
return tree_step;
}
public void setTree_step(int tree_step) {
this.tree_step = tree_step;
}
public int getRow() {
return row;
}
public void setRow(int row) {
this.row = row;
}
public int getCol() {
return col;
}
public void setCol(int col) {
this.col = col;
}
public int getrLen() {
return rLen;
}
public void setrLen(int rLen) {
this.rLen = rLen;
}
public int getcLen() {
return cLen;
}
public void setcLen(int cLen) {
this.cLen = cLen;
}
}
Constants.java
package com.test.server.utils;
/**
* @description: 一些常量
*/
public class Constants {
// 系统密码默认值
public static final String DEFAULT_PASSWORD= "Fxxy123456@@";
// 系统明文加密字符串
public static final String DEFAULT_SALT= "fxxy";
private static final String YJS_KEY = "yjs:";
private static final String BASE_STUDENT = YJS_KEY+"student:";
private static final String BASE_SYSTEM = YJS_KEY+"system:";
private static final String OLD_BASE_USER = "auth:";
private static final String OLD_USER = OLD_BASE_USER + "userBaseInfo:";
public static final String YES = "1";
public static final String NO = "0";
public enum RedisKey {
/**
* 学校基本信息
*/
TABLE_FIELD_VALUE(BASE_STUDENT + "tableFieldValue:"),
/**
* 新生信息维护时间
*/
NEW_STUDENT_INFORMATION_TIME(BASE_STUDENT + "newStudentInformationTime"),
/**
* 字段参数数据list
*/
FIELD_PARAM_LIST(BASE_STUDENT + "fieldParamList"),
/**
* 业务类型代码查询下面的流程
*/
BUSINESS_TYPE_PROCESS(BASE_SYSTEM + "businessTypeProcess"),
//学生字段权限
STUDENT_STATUS_FIELD_PERMISSIONS(BASE_STUDENT + "studentStatusFieldPermissions"),
//用户缓存信息
USER_INFO(OLD_USER),
//学生菜单权限
STUDENT_MENU_PERMISSION(BASE_STUDENT + "studentMenuPermissions");
private String key;
RedisKey(String key) {
this.key = key;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
@Override
public String toString() {
return key;
}
}
/**
* 表名
*/
public enum TableNames {
/**
* 新生信息表
*/
NEW_STUDENT_TABLE_NAME("T_XXSGL_XSXX"),
/**
* 学籍信息表
*/
STUDENT_STATUS_TABLE_NAME("T_XSGL_XSXX_XJXX");
private String key;
TableNames(String key) {
this.key = key;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
@Override
public String toString() {
return key;
}
}
/**
* 字段参数,引用值类型
*/
public enum ReferenceValueType {
/**
* 代码编号
*/
CODE_NUMBER("1"),
/**
* 业务表名
*/
BUSINESS_TABLE_NAME("2"),
/**
* 视图
*/
VIEW("3"),
/**
* 自定义json字符串
*/
CUSTOM_JSON_STRING("4");
private String key;
ReferenceValueType(String key) {
this.key = key;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
@Override
public String toString() {
return key;
}
}
/**
* 新生信息维护方式
*/
public enum NewStuMaintenanceStyle {
/**
* 页面修改
*/
PAGE_MODIFY("0"),
/**
* 流程修改
*/
PROCESS_MODIFY("1");
private String key;
NewStuMaintenanceStyle(String key) {
this.key = key;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
@Override
public String toString() {
return key;
}
}
/**
* 用户类型
*/
public enum UserType {
/**
* 学生
*/
STUDENT("0"),
/**
* 教师
*/
TEACHER("1"),
/**
* 临时用户
*/
TEMP_USER("2");
private String key;
UserType(String key) {
this.key = key;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
@Override
public String toString() {
return key;
}
}
/*
* 学生字段权限类型
*/
public enum StudentPermissionType {
/**
* 查看
*/
VIEW("0"),
/**
* 编辑
*/
EDIT("1");
private String key;
StudentPermissionType(String key) {
this.key = key;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
@Override
public String toString() {
return key;
}
}
//流程号
public enum ProcessCode {
/**
* 新生信息确认
*/
NEW_STUDENT_CONFIRM("XSXXQR");
private String key;
ProcessCode(String key) {
this.key = key;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
@Override
public String toString() {
return key;
}
}
}
Common.java
package com.test.utils;
import com.ly.education.commons.exception.ServiceException;
import com.ly.education.commons.exception.SimpleErrorCode;
import com.ly.spring.boot.pagehelper.dto.PageQueryParam;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.util.CollectionUtils;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.text.DateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Slf4j
public class Common {
public static String OS = System.getProperty("os.name").toLowerCase();
//判空
public static void isNotNull(Object obj, String str) {
if (obj instanceof String) {
if (StringUtils.isBlank((String) obj)) {
throw new ServiceException(2, str.concat("不能为空！"));
}
} else if (obj instanceof Integer) {
if (null == obj) {
throw new ServiceException(2, str.concat("不能为空！"));
}
} else {
if (null == obj) {
throw new ServiceException(2, str.concat("不能为空！"));
}
}
}
/**
* 分页对象的param参数如果是空的，则赋值
*
* @param dto 分页对象
* @param tClass PageQueryParam的 param 属性类对象
* @param <T> PageQueryParam的 param 属性类
*/
public static <T> void pageQueryParamSetter(PageQueryParam dto, Class<T> tClass) {
if (dto.getParam() == null) {
try {
dto.setParam(tClass.newInstance());
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
/**
* 判断插入数量是否大于0
*
* @param count
*/
public static void validInsertCount(int count) {
validInsertCount(count, "保存失败.");
}
/**
* 判断插入数量是否大于0
*
* @param count
*/
public static void validInsertCount(int count, String msg) {
if (count < 1) {
log.warn(msg);
throw new ServiceException(SimpleErrorCode.SaveFailure.getErrorCode(), msg);
}
}
/**
* 判断更新数量是否大于0
*
* @param count
*/
public static void validUpdateCount(int count) {
validUpdateCount(count, "更新失败.");
}
/**
* 判断更新数量是否大于0
*
* @param count
*/
public static void validUpdateCount(int count, String msg) {
if (count < 1) {
log.warn(msg);
throw new ServiceException(SimpleErrorCode.ModifyFailure.getErrorCode(), msg);
}
}
/**
* 判断删除数量是否大于0
*
* @param count
*/
public static void validDeleteCount(int count) {
validDeleteCount(count, "删除失败.");
}
/**
* 判断删除数量是否大于0
*
* @param count
*/
public static void validDeleteCount(int count, String msg) {
if (count < 1) {
log.warn(msg);
throw new ServiceException(SimpleErrorCode.DeleteFailure.getErrorCode(), msg);
}
}
/**
* 集合必须是空的
*
* @param collection
*/
public static void isNull(Collection<?> collection) {
isNull(collection, "数据已存在.");
}
/**
* 集合必须是空的
*
* @param collection
*/
public static void isNull(Collection<?> collection, String msg) {
if (!CollectionUtils.isEmpty(collection)) {
log.warn(msg);
throw new ServiceException(SimpleErrorCode.ParamsError.getErrorCode(), msg);
}
}
/**
* 对象必须是空的
*
* @param object
*/
public static void isNull(Object object) {
isNull(object, "数据已存在.");
}
/**
* 对象必须是空的
*
* @param object
*/
public static void isNull(Object object, String msg) {
if (object != null) {
log.warn(msg);
throw new ServiceException(SimpleErrorCode.ParamsError.getErrorCode(), msg);
}
}
/**
* 集合非空
*
* @param collection
*/
public static void notNull(Collection<?> collection) {
notNull(collection, "数据不存在.");
}
/**
* 集合非空
*
* @param collection
*/
public static void notNull(Collection<?> collection, String msg) {
if (CollectionUtils.isEmpty(collection)) {
log.warn(msg);
throw new ServiceException(SimpleErrorCode.ParamsError.getErrorCode(), msg);
}
}
/**
* 对象非空
*
* @param object
*/
public static void notNull(Object object) {
notNull(object, "数据不存在.");
}
/**
* 对象非空
*
* @param object
*/
public static void notNull(Object object, String msg) {
if (object == null) {
log.warn(msg);
throw new ServiceException(SimpleErrorCode.ParamsError.getErrorCode(), msg);
}
}
/**
* 结果为真
*/
public static void isTrue(Boolean flag, String msg) {
if (!flag) {
log.warn(msg);
throw new ServiceException(SimpleErrorCode.ParamsError.getErrorCode(), msg);
}
}
public static boolean isLinux() {
return OS.indexOf("linux") >= 0;
}
public static char[] fileToCharArray(File file) throws IOException {
try (BufferedReader reader = new BufferedReader(new FileReader(file))) {
StringBuilder stringBuilder = new StringBuilder();
char[] buffer = new char;
int length;
while ((length = reader.read(buffer)) != -1) {
stringBuilder.append(buffer, 0, length);
}
return stringBuilder.toString().toCharArray();
}
}
/**
* 拼接sql参数
* @param configuration mybatis配置
* @param boundSql mybatis绑定sql
* @return 拼接后的sql
*/
public static String showSql(Configuration configuration, BoundSql boundSql){
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (parameterMappings.size() > 0 && parameterObject != null) {
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
} else {
MetaObject metaObject = configuration.newMetaObject(parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
String propertyName = parameterMapping.getProperty();
if (metaObject.hasGetter(propertyName)) {
Object obj = metaObject.getValue(propertyName);
sql = sql.replaceFirst("\\?", getParameterValue(obj));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
Object obj = boundSql.getAdditionalParameter(propertyName);
sql = sql.replaceFirst("\\?", getParameterValue(obj));
}
}
}
}
return sql;
}
private static String getParameterValue(Object obj) {
String value;
if (obj instanceof String) {
value = "'" + obj.toString() + "'";
} else if (obj instanceof Date) {
DateFormat formatter = DateFormat.getDateTimeInstance();
value = "'" + formatter.format(new Date()) + "'";
} else {
if (obj != null) {
value = obj.toString();
} else {
value = "";
}
}
return value;
}
/**
* 解析 SQL 语句，获取字段名和字段值
* @param sql SQL 语句
* @return 字段名和字段值的 Map
*/
public static Map<String, String> parseUpdateSQL(String sql) {
Map<String, String> fieldValueMap = new HashMap<>();
// 正则表达式匹配字段名和字段值
String regex = "(\\w+)\\s*=\\s*'([^']*)'";
Pattern pattern = Pattern.compile(regex);
Matcher matcher = pattern.matcher(sql);
// 遍历匹配结果，放入 Map 中
while (matcher.find()) {
String fieldName = matcher.group(1);
String fieldValue = matcher.group(2);
fieldValueMap.put(fieldName, fieldValue);
}
return fieldValueMap;
}
//获取update的sql语句中的where条件
public static String getWhereSql(String sql) {
String whereSql = "";
if (sql.contains("WHERE")) {
whereSql = sql.substring(sql.indexOf("WHERE") + 5);
}
//由于都是大小所以返回的where要使用UPPER转换
Map<String,String> fieldValueMap = parseUpdateSQL(whereSql);
StringBuilder whereSqlBuilder = new StringBuilder();
fieldValueMap.forEach((k,v) -> {
whereSqlBuilder.append("UPPER(").append(k).append(") = '").append(v).append("' AND ");
});
whereSqlBuilder.delete(whereSqlBuilder.length() - 5, whereSqlBuilder.length());
return whereSqlBuilder.toString();
}
}
package com.test.utils;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
public class TreeTool {
/**
* 传入的id 必须存在list集合里
* 获取某节点的深度
* @param list
* @param id 根节点
* @param step
* @return
*/
public static int getTreeStep(List<Column> list, String id, int step) {
if("".equals(id) || null == id) return step;
for (Column cc : list) {
if (id.equals(cc.getId())) {
int temp = step + 1;
return getTreeStep(list, cc.getPid(), temp);
}
}
return step;
}
/**
* 遍历所有数据 获取树最大的深度
* @param list
* @return
*/
public static int getMaxStep(List<Column> list){
List<Integer> nums=new ArrayList<Integer>();
for(Column cc:list){
nums.add( getTreeStep( list,cc.getId(),0));
}
returnCollections.max(nums);
}
/**
* 获取最底部子节点的个数 所有叶子节点个数
* @param list
* @param did
* @return
*/
public static int getDownChilren(List<Column> list, String did){
int sum=0;
for(Column cc:list){
if(did.equals(cc.getPid())){
sum++;
//判断该节点 是否有子节点
if(hasChild(list,cc)) {
sum+= getDownChilren(list, cc.getId())-1;
}
}
}
return sum;
}
/**
* 获取父节点
* @param list 所有的list数据，一条一条
* @param did 当前节点id
* @return
*/
public static Column getFCol(List<Column> list, String did){
for(Column cc:list) {
if (did !=null && did.equals(cc.getId())) {
return cc;
}
if (did ==null && did == cc.getId()) {
return cc;
}
}
return new Column(){{setCol(0);setRow(0);}};
}
/**
* 获取兄弟节点个数 这个必须是有排序的
* @param list 所有的list数据，一条一条
* @param column 当前节点信息
* @return
*/
public static int getBrotherChilNum(List<Column> list, Column column ){
int sum=0;
for(Column cc:list){
if(column.getId().equals(cc.getId()))break;
if(!column.getPid().equals(cc.getPid()))continue;
int temp = getDownChilren(list, cc.getId());
if(temp == 0 || temp == 1)
sum++;
else
sum += temp;
}
return sum;
}
/**
* 根据某节点的第几层的父节点id
* @param list 所有的list数据，一条一条
* @param id 当前节点id
* @param step 第几层(深度 从零开始)
* @return
*/
public static String getStepFid(List<Column> list, String id, int step){
String f_id = null;
for (Column cc : list) {
if (id.equals(cc.getId())) {
int cstep= getTreeStep( list,cc.getId(), 0);
if(step == cstep){
return id;
}
int fstep= getTreeStep( list,cc.getPid(), 0);
if(step == fstep){
f_id = cc.getPid();break;
}else {
getStepFid( list,cc.getPid(), step);
}
}
}
return f_id;
}
/**
* 判断是否有子节点
* @param list 遍历的数据
* @param node 某个节点
* @return
*/
public static boolean hasChild(List<Column> list, Column node) {
return getChildList(list, node).size() > 0 ? true : false;
}
/**
* 得到子节点列表
* @param list 遍历的数据
* @param node 某个节点
* @return
*/
public static List<Column> getChildList(List<Column> list, Column node) {
List<Column> nodeList = new ArrayList<Column>();
Iterator<Column> it = list.iterator();
while (it.hasNext()) {
Column n = (Column) it.next();
if (n.getPid()!=null && n.getPid().equals( node.getId())) {
nodeList.add(n);
}
}
return nodeList;
}
/**
* 使用递归方法建树
* @param treeNodes
* @return
*/
public static List<Column> buildByRecursive(List<Column> treeNodes, String rootID) {
List<Column> trees = new ArrayList<>();
boolean flag = false;
boolean sflag = false;
for (Column treeNode : treeNodes) {
if ((rootID == null && rootID == treeNode.getId()) ) {
flag = true;
}
if( rootID != null && rootID.equals(treeNode.getId())){
flag = true;
}
if(flag) {
trees.add(findChildren(treeNode, treeNodes));
flag = false;
}
}
if(trees.size() <= 0) {
for (Column treeNode : treeNodes) {
if ((rootID == null && rootID == treeNode.getPid()) ) {
sflag = true;
}
if( rootID != null && rootID.equals(treeNode.getPid())){
sflag = true;
}
if(sflag){
trees.add(findChildren(treeNode,treeNodes));
sflag = false;
}
}
}
return trees;
}
/**
* 递归查找子节点
* @param treeNodes
* @return
*/
public static Column findChildren(Column treeNode, List<Column> treeNodes) {
for (Column it : treeNodes) {
if(treeNode.getId().equals(it.getPid())) {
if (treeNode.getListTpamscolumn() == null) {
treeNode.setListTpamscolumn(new ArrayList<Column>());
}
treeNode.getListTpamscolumn().add(findChildren(it,treeNodes));
}
}
return treeNode;
}
}
resource接口
@RequestMapping(value = "/importDbf", method=RequestMethod.POST)
Result<?> importDbf(@ApiParam(value = "dbf文件", required = true) @RequestBody MultipartFile file,
@RequestParam(value = "genNo") String genNo,
@RequestParam(value = "importType") String importType);
@RequestMapping(value = "/importExcel", method=RequestMethod.POST)
Result<?> importExcel(@ApiParam(value = "excel文件", required = true) @RequestBody MultipartFile file,
@RequestParam(value = "genNo") String genNo,
@RequestParam(value = "importType") String importType);
controller类
@Autowired
private NewStudentImportSettingService newStudentImportSettingService;
@Autowired
private HttpServletRequest request;
@Override
public Result<?> importDbf(MultipartFile file,@RequestParam(value = "genNo")String genNo,@RequestParam(value = "importType") String importType) {
if (file == null) {
throw new ServiceException(SimpleErrorCode.ParamsError);
}
List<Map<String, Object>> list = new ArrayList<>();
try {
list = DBFUtils.readDbf(file.getBytes());
} catch (Exception e) {
log.error("读取文件失败", e);
throw new ServiceException(2,"读取文件失败");
}
if (CollectionUtils.isEmpty(list)) {
throw new ServiceException(2,"文件内无数据");
}
ImportMessageVo resultVo = new ImportMessageVo();
resultVo = newStudentImportSettingService.importNewStuData(list,genNo,importType);
StringBuilder resultMessages = new StringBuilder(); // 导入结果信息
String message = "导入数据完成，总处理数据" + resultVo.getTotalCount() + "条，成功" + resultVo.getSuccessCount() + "条，失败" + resultVo.getFailCount() + "条;";
resultMessages.append(message).append("\r\n").append(resultVo.getImportFailMessages());
return Result.buildSuccessResult(resultMessages);
}
@Override
public Result<?> importExcel(MultipartFile file,
@RequestParam(value = "genNo")String genNo,
@RequestParam(value = "importType") String importType) {
if (file == null) {
throw new ServiceException(SimpleErrorCode.ParamsError);
}
List<Map<String, Object>> list = new ArrayList<>();
try {
list = ExcelTool.getExcelMapVal(file.getInputStream(),1);
} catch (Exception e) {
log.error("读取文件失败",e);
throw new ServiceException(2,"读取文件失败");
}
if (CollectionUtils.isEmpty(list)) {
throw new ServiceException(2,"文件内无数据");
}
ImportMessageVo resultVo = new ImportMessageVo();
resultVo = newStudentImportSettingService.importNewStuData(list,genNo,importType);
StringBuilder resultMessages = new StringBuilder(); // 导入结果信息
String message = "导入数据完成，总处理数据" + resultVo.getTotalCount() + "条，成功" + resultVo.getSuccessCount() + "条，失败" + resultVo.getFailCount() + "条;";
resultMessages.append(message).append("\r\n").append(resultVo.getImportFailMessages());
return Result.buildSuccessResult(resultMessages);
}
service接口
ImportMessageVo importNewStuData(List<Map<String, Object>> data,String genNo,String importType);
service实现类
@Override
public ImportMessageVo importNewStuData(List<Map<String, Object>> data,String genNo,String importType) {
//读取dbf映射配置
NewStudentImportSettingDto searchDto = new NewStudentImportSettingDto();
searchDto.setImportType(importType);
searchDto.setTableName(Constants.TableNames.NEW_STUDENT_TABLE_NAME.getKey());
List<NewStudentImportSettingVo> newStudentImportSettingVoList = newStudentImportSettingMapper.select(searchDto);
if (CollectionUtils.isEmpty(newStudentImportSettingVoList)) {
throw new ServiceException(2,"请先配置导入字段映射");
}
Map<String,String> mappingMap = newStudentImportSettingVoList.stream().collect(
java.util.stream.Collectors.toMap(
NewStudentImportSettingVo::getFileFieldCode,
NewStudentImportSettingVo::getLibraryTableCode
)
);
ImportMessageVo importMessageVo = new ImportMessageVo();
int totalCount = data.size();
AtomicInteger successCount = new AtomicInteger(0);
AtomicInteger insertSuccessCount = new AtomicInteger(0);
AtomicInteger updateSuccessCount = new AtomicInteger(0);
AtomicInteger failCount = new AtomicInteger();
AtomicReference<StringBuilder> importFailMessages = new AtomicReference<>(new StringBuilder());
AtomicInteger currentRow = new AtomicInteger(1);
for (Map<String, Object> map : data) {
StringBuilder sql = new StringBuilder("insert into " + Constants.TableNames.NEW_STUDENT_TABLE_NAME.getKey() + " (");
StringBuilder updateSql = new StringBuilder("update " + Constants.TableNames.NEW_STUDENT_TABLE_NAME.getKey() + " set ");
Boolean updateStuFlag = false;// 是否修改 否
try {
Map<String,String> studentNoFilterMap = new HashMap<>();
// 判断是否已经存在
Boolean kshValNull = false;
String ksh = "";
Boolean sfzjhValNull = false;
String sfzjh = "";
for (Map.Entry<String, Object> entry : map.entrySet()) {
if (mappingMap.containsKey(entry.getKey()) ){
studentNoFilterMap.put(mappingMap.get(entry.getKey()).trim(), entry.getValue().toString().trim());
String val =entry.getValue().toString().trim();
if("KSH".equals(mappingMap.get(entry.getKey()))){
if(StringUtils.isBlank(val)) {
kshValNull = true;
continue;
}else{
ksh = val;
}
}
if( "SFZJH".equals(mappingMap.get(entry.getKey())) ){
if(StringUtils.isBlank(val)){
sfzjhValNull = true;
continue;
}else{
sfzjh = val;
}
}
}
}
// 对比数据库
if (!studentNoFilterMap.containsKey("KSH")) {
failCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因：excel中必须包含 导入字段映射到考生编号(KSBH)字段的这一列").append("\n");
currentRow.getAndIncrement();
continue;
}
if (!studentNoFilterMap.containsKey("SFZJH")) {
failCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因：excel中必须包含 导入字段映射到身份证件号(SFZJH)字段的这一列").append("\n");
currentRow.getAndIncrement();
continue;
}
if(kshValNull ){
failCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因： 映射到考生编号(KSBH)这一列的值不能为空").append("\n");
currentRow.getAndIncrement();
continue;
}
if( sfzjhValNull ){
failCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因： 映射到身份证件号(SFZJH)这一列的值不能为空").append("\n");
currentRow.getAndIncrement();
continue;
}
// 判断数据库是否已经存在考生号
List<NewStudentInformationVo> countExist = newStudentImportSettingMapper.selectBySFZH(sfzjh);
if(!CollectionUtils.isEmpty(countExist) ){
if(countExist.size()>1) {
failCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因： 系统已存在" + countExist.size() + "条该身份证相同的数据，导入失败").append("\n");
currentRow.getAndIncrement();
continue;
}else if(countExist.size()==1){
if(!ksh.equals(countExist.get(0).getKsH())){
failCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因： 系统已存在" + countExist.size() + "条该身份证相同的,但考生号不同数据，导入失败").append("\n");
currentRow.getAndIncrement();
continue;
}else{
// 拼接修改
updateStuFlag = true;
}
}
} else{
}
int res = 0;
if (!updateStuFlag) {
for (Map.Entry<String, Object> entry : map.entrySet()) {
//只有映射的字段才会插入
if (mappingMap.containsKey(entry.getKey())){
sql.append(mappingMap.get(entry.getKey()).trim()).append(",");
}
}
sql = new StringBuilder(sql.substring(0, sql.length() - 1));
sql.append(") values (");
for (Map.Entry<String, Object> entry : map.entrySet()) {
if (mappingMap.containsKey(entry.getKey()) ){
String val =entry.getValue().toString().trim();
sql.append("'").append(val).append("',");
}
}
sql.deleteCharAt(sql.lastIndexOf(","));
sql.append(")");
res =publicMapper.executeSQLInsert(sql.toString());
if (res == 0){
failCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因：插入数据失败").append("\n");
currentRow.getAndIncrement();
}else{
successCount.getAndIncrement();
insertSuccessCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入成功").append("\n");
currentRow.getAndIncrement();
}
}else{
// 修改
for (Map.Entry<String, Object> entry : map.entrySet()) {
if (mappingMap.containsKey(entry.getKey()) ){
String val =entry.getValue().toString().trim();
updateSql.append(mappingMap.get(entry.getKey()).trim()).append("=") ;
updateSql.append("'").append(val).append("',");
}
}
updateSql.deleteCharAt(updateSql.lastIndexOf(","));
updateSql.append(" where SFZJH = ")
.append("'").append(sfzjh).append("'");
res = publicMapper.executeSQLInsert(updateSql.toString());
if (res == 0){
failCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因：插入数据失败").append("\n");
currentRow.getAndIncrement();
}else{
successCount.getAndIncrement();
updateSuccessCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入修改成功").append("\n");
currentRow.getAndIncrement();
}
}
} catch (Exception e) {
log.error("导入数据失败", e);
failCount.getAndIncrement();
importFailMessages.get().append("第").append(currentRow.get()).append("行导入失败，失败原因：").append(e.getMessage()).append("\n");
}
}
importMessageVo.setTotalCount(totalCount);
importMessageVo.setSuccessCount(successCount.get());
importMessageVo.setUpdateSuccessCount(updateSuccessCount.get());
importMessageVo.setFailCount(failCount.get());
if(insertSuccessCount.get()>=1){
importFailMessages.get().append("\n").append("其中新增了"+insertSuccessCount+"条");
}
if(updateSuccessCount.get()>=1){
importFailMessages.get().append("\n").append("其中修改了"+updateSuccessCount+"条");
}
importMessageVo.setImportFailMessages(importFailMessages.get());
return importMessageVo;
}
xml
<insert id="executeSQLInsert">
${sql}
</insert>
</code></pre>
</details>
<p>导出dbf和excel</p>
<details>
<summary>点击查看代码</summary>
<pre><code>
前端的columnDto参数:
[
{
title: item.libraryTableName,
dataIndex: item.libraryTableName,
key: item.fileFieldCode
}
]
resource接口
@RequestMapping(value = "/exportExcelTemplate", method=RequestMethod.POST)
void exportExcelTemplate(@ApiParam(value = "传输对象", required = true) ExportDto<PageQueryParam<NewStudentImportSettingDto>, NewStudentImportSettingVo> exportDto);
@RequestMapping(value = "/exportDbfTemplate", method=RequestMethod.POST)
void exportDbfTemplate(@ApiParam(value = "传输对象", required = true) ExportDto<PageQueryParam<NewStudentImportSettingDto>, NewStudentImportSettingVo> exportDto);
工具类 exportDto.java
package com.test.dto;
import java.io.Serializable;
import java.util.List;
public class ExportDto<T, O> implements Serializable {
private static final long serialVersionUID = -9003609292510835997L;
private T pageQueryParam;
private List<ColumnsDto> columns;
private List<String> rowKeys;
private List<O> rows;
public ExportDto() {
}
public T getPageQueryParam() {
return this.pageQueryParam;
}
public List<ColumnsDto> getColumns() {
return this.columns;
}
public List<String> getRowKeys() {
return this.rowKeys;
}
public List<O> getRows() {
return this.rows;
}
public void setPageQueryParam(T pageQueryParam) {
this.pageQueryParam = pageQueryParam;
}
public void setColumns(List<ColumnsDto> columns) {
this.columns = columns;
}
public void setRowKeys(List<String> rowKeys) {
this.rowKeys = rowKeys;
}
public void setRows(List<O> rows) {
this.rows = rows;
}
}
ColumnsDto.java类
package com.test.dto;
import java.io.Serializable;
public class ColumnsDto implements Serializable {
private static final long serialVersionUID = -9003609292510835997L;
private String title;
private String dataIndex;
private String key;
private String align;
private String width;
public ColumnsDto() {
}
public String getTitle() {
return this.title;
}
public String getDataIndex() {
return this.dataIndex;
}
public String getKey() {
return this.key;
}
public String getAlign() {
return this.align;
}
public String getWidth() {
return this.width;
}
public void setTitle(String title) {
this.title = title;
}
public void setDataIndex(String dataIndex) {
this.dataIndex = dataIndex;
}
public void setKey(String key) {
this.key = key;
}
public void setAlign(String align) {
this.align = align;
}
public void setWidth(String width) {
this.width = width;
}
public boolean equals(Object o) {
if (o == this) {
return true;
} else if (!(o instanceof ColumnsDto)) {
return false;
} else {
ColumnsDto other = (ColumnsDto)o;
if (!other.canEqual(this)) {
return false;
} else {
label71: {
Object this$title = this.getTitle();
Object other$title = other.getTitle();
if (this$title == null) {
if (other$title == null) {
break label71;
}
} else if (this$title.equals(other$title)) {
break label71;
}
return false;
}
Object this$dataIndex = this.getDataIndex();
Object other$dataIndex = other.getDataIndex();
if (this$dataIndex == null) {
if (other$dataIndex != null) {
return false;
}
} else if (!this$dataIndex.equals(other$dataIndex)) {
return false;
}
label57: {
Object this$key = this.getKey();
Object other$key = other.getKey();
if (this$key == null) {
if (other$key == null) {
break label57;
}
} else if (this$key.equals(other$key)) {
break label57;
}
return false;
}
Object this$align = this.getAlign();
Object other$align = other.getAlign();
if (this$align == null) {
if (other$align != null) {
return false;
}
} else if (!this$align.equals(other$align)) {
return false;
}
Object this$width = this.getWidth();
Object other$width = other.getWidth();
if (this$width == null) {
if (other$width == null) {
return true;
}
} else if (this$width.equals(other$width)) {
return true;
}
return false;
}
}
}
protected boolean canEqual(Object other) {
return other instanceof ColumnsDto;
}
public int hashCode() {
int PRIME = true;
int result = 1;
Object $title = this.getTitle();
int result = result * 59 + ($title == null ? 43 : $title.hashCode());
Object $dataIndex = this.getDataIndex();
result = result * 59 + ($dataIndex == null ? 43 : $dataIndex.hashCode());
Object $key = this.getKey();
result = result * 59 + ($key == null ? 43 : $key.hashCode());
Object $align = this.getAlign();
result = result * 59 + ($align == null ? 43 : $align.hashCode());
Object $width = this.getWidth();
result = result * 59 + ($width == null ? 43 : $width.hashCode());
return result;
}
public String toString() {
return "ColumnsDto(title=" + this.getTitle() + ", dataIndex=" + this.getDataIndex() + ", key=" + this.getKey() + ", align=" + this.getAlign() + ", width=" + this.getWidth() + ")";
}
}
controller.java类
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import com.ly.spring.boot.uuid.UUIDStringGenerator;
@Autowired
private HttpServletRequest request;
@Autowired
private HttpServletResponse response;
@Autowired
private UUIDStringGenerator uuidStringGenerator;
@Override
public void exportExcelTemplate(@RequestBody ExportDto<PageQueryParam<NewStudentImportSettingDto>, NewStudentImportSettingVo> exportDto) {
String studentType = exportDto.getPageQueryParam().getParam().getStudentType();
String fileName = "bs".equals(studentType)?"博士":"ss".equals(studentType)?"硕士":"tdxl".equals(studentType)?"同等学力":"";
try {
String characterEncoding = request.getCharacterEncoding();
response.setHeader("content-type", "application/octet-stream");
response.setHeader("content-disposition",
"attachment;fileName=" + URLEncoder.encode(fileName+"新生信息模板导出"+ DatetimeUtils.getCurrentDateFromFormat("`yyyyMMddhhmm`") +".xls", characterEncoding));
response.setContentType( "application/msexcel" );
ExcelTool excelTool = new ExcelTool(fileName+"新生信息模板");
List<Map<String, String>> titleList=new ArrayList<>();
for (ColumnsDto columnsDto : exportDto.getColumns()) {
Map<String, String> titleMap=new HashMap<String, String>();
titleMap.put("id",uuidStringGenerator.nextUUID());
titleMap.put("pid","0");
// titleMap.put("content",columnsDto.getTitle());
titleMap.put("content",columnsDto.getKey());
titleMap.put("fileName",columnsDto.getKey());
titleList.add(titleMap);
}
List<Column> columns = excelTool.columnTransformer(titleList,"id","pid","content","fileName","0");
if(!CollectionUtils.isEmpty(columns)){
columns.stream().forEach(item->{
item.setrLen(0);// 设置表头不合并多行
});
}
HSSFWorkbook workbook = excelTool.exportWorkbook(columns,new ArrayList(),true);
workbook.write(response.getOutputStream());
}catch (Exception e) {
log.error("导出学生信息失败",e);
throw new RuntimeException("导出学生信息失败");
}
}
@Override
public void exportDbfTemplate(@RequestBody ExportDto<PageQueryParam<NewStudentImportSettingDto>, NewStudentImportSettingVo> exportDto) {
OutputStream fos = null;
String studentType = exportDto.getPageQueryParam().getParam().getStudentType();
String fileNameType = "bs".equals(studentType)?"博士":"ss".equals(studentType)?"硕士":"tdxl".equals(studentType)?"同等学力":"";
try {
response.setContentType("application/x-dbf");
response.setCharacterEncoding("UTF-8");
String filename = fileNameType +"新生信息dbf模板导出.dbf";
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
} catch (Exception e) {
log.error("导出失败", e);
}
try{
int fieldLength = exportDto.getColumns().size();
// 创建表头 写表头
DBFField[] field = new DBFField;
for (int i = 0 ; i < field.length; i++) {
field = new DBFField();
}
for (int i = 0 ; i < fieldLength; i++ ) {
field.setName(exportDto.getColumns().get(i).getKey());
field.setType(DBFDataType.CHARACTER);
field.setLength(200);
}
fos = response.getOutputStream();
DBFWriter writer = new DBFWriter(fos, Charset.forName("GBK"));
writer.setFields(field);
// 写数据内容
List<NewStudentInformationVo> dataContent=new ArrayList<>();
for (int j = 0; j < dataContent.size(); j++) {
Object[] objects = new Object;
Object[] rowData = new Object;
for (int i = 0 ; i < fieldLength; i++ ) {
objects = "";
// objects = dataContent.get(j).getKsH();
}
for (int z = 0; z < fieldLength; z++) {
if (field.getType() == DBFDataType.NUMERIC || field.getType() == DBFDataType.FLOATING_POINT) {
if (objects != null) {
rowData = Double.parseDouble(objects.toString());
} else {
// 插入数字为null,指定为0
rowData = Double.parseDouble("0");
}
} else {
rowData = objects;
}
}
writer.addRecord(rowData);
}
writer.close();
} catch (Exception e){
e.printStackTrace();
} finally {
try {
fos.flush();
fos.close();
}catch (Exception e){
}
}
}
</code></pre>
</details><br><br>
来源：https://www.cnblogs.com/heavenTang/p/18985183
頁:
[1]