导入dbf和excel解析:
点击查看代码[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工具类 */@Slf4jpublic class DBFUtils { public static List readDbf(byte[] bytes) { List 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 public static List readDbf(String filePath) { List 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 readDbfStream(DBFReader reader) { List 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 map = new HashMap(); //遍历字段 for (int j = 0; j < fieldCount; j++) { //获取字段名 String fieldName = reader.getField(j).getName(); //获取字段值 Object fieldValue = record[j]; //添加到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 { 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 listTpamscolumn, List 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 listTpamscolumn, List 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 listTpamscolumn, List 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 data, List 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 data, List listTpamscolumn, boolean flag, boolean rowFlag)throws Exception{ int dataCount = data.size(); int maxColumn = 65535; int pieces = dataCount/maxColumn; for(int i = 1; i =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 过滤表头的脏数据 * @param list 表头数据 * @param listCol 返回新的list * @return * List */ private void getColumnList(List list, List listCol){ for(int i = 0; i < list.size(); i++){ if(list.get(i).getFieldName() != null){ listCol.add(list.get(i)); } List 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 row Excel对应的行 * @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[0] =val; } }); }else { Class cls = v.getClass();// 拿到该类 Field[] fields=cls.getDeclaredFields();// 获取实体类的所有属性，返回Field数组 for(int i=0;i= firstRow && row = firstColumn && column = firstRow && row = firstColumn && column |
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜
