一、excel 文件的导入:
1、介绍:
使用alibaba 的 easyExcel 组建实现。
2、实现过程:
1)pom文件引入jar包依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>LATEST</version>
</dependency>
2)根据要导入的excel的内容,写一个接收数据的对象。
注意,@ExcelProperty 注解内的值要和excel的列头一模一样,最好是粘贴复制,否则读出来的数据有可能是null。
@Data
public class ExcelImpCsDetailDto {
@ExcelProperty(value = "A")
private String csCode;
@ExcelProperty(value = "B")
private String code;
@ExcelProperty(value = "C")
private String name;
}
3)利用刚建立的对象写一个监听类实现 AnalysisEventListener<T> 类,(解析出的每条数据的返回)
@Slf4j
public class ExcelImpCsDetailListener extends AnalysisEventListener<ExcelImpCsDetailDto> {
private List<ExcelImpCsDetailDto> importList = new ArrayList<>();
@Autowired
private StdTestDetailMapper stdTestDetailMapper;
//返回结果
private List<StdTestDetail> dtoList = new ArrayList<>();
public ExcelImpCsDetailListener(StdTestDetailMapper stdTestDetailMapper) {
this.stdTestDetailMapper = stdTestDetailMapper;
}
@Override
public void invoke(ExcelImpCsDetailDto importCs, AnalysisContext analysisContext) {
importList.add(importCs);
}
@SneakyThrows
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
for (ExcelImpCsDetailDto impModel : importList) {
StdTestDetail det = new StdTestDetail();
det.setId(ScUtil.getUUID());
det.setCode(impModel.getCode());
det.setName(impModel.getName());
det.setDes(impModel.getName());
det.setCsCode(impModel.getCsCode());
SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
det.setStartDate(f.parse("2022-01-01 00:00:00"));
det.setEndDate(f.parse("2022-12-31 23:59:59"));
dtoList.add(det);
}
}
//返回导入结果 交互
public List<StdTestDetail> getImportResult() {
return dtoList;
}
}
这个监听类需要写一个 ExcelImpCsDetailListener 构造函数,便于把保存数据的mapper注入进来。还要重写该父类的 invoke 以及 doAfterAllAnalysed 方法,把识别到的excel行数据转化为我们熟系的 ExcelImpCsDetailDto 对象集合。拿到数据之后就可以进行保存,筛选等操作。
4)可以在监听类里写保存数据的代码。此处没保存是因为由于excel数据量过多,mybatis的动态sql一次性导入过多的数据会报sql太长,需要分步执行,我选择把解析的数据返回到server层,由注入到server层的mybatis的 SqlSessionTemplate 类里,利用这个类可以分步执行。
/**
*导入excel数据
*/
@Override
@Transactional(propagation= Propagation.REQUIRED,rollbackFor= Exception.class)
public void importExcel(MultipartFile file) throws IOException {
//实例化EASYEXCEL监听器
ExcelImpCsDetailListener impListener = new ExcelImpCsDetailListener(stdTestDetailMapper);
//执行导入过程
EasyExcel.read(file.getInputStream(), ExcelImpCsDetailDto.class, impListener).sheet().doRead();
List<StdTestDetail> list = impUserListener.getImportResult();
batchInsert(list);
}
//大批量插入数据
private int batchInsert( List<StdTestDetail> list) {
SqlSession session = null;
//通过新的session获取mapper
int result=0;
try {
session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
int num = 50;//每次提交50条
int loop = (int) Math.ceil(list.size() / (double) num);
List<StdTestDetail> tempList = new ArrayList<StdTestDetail>(num);
int start, stop=0;
for (int i = 0; i < loop; i++) {
tempList.clear();
start = i * num;
stop = Math.min(i * num + num - 1, list.size() - 1);
for (int j = start; j <= stop; j++) {
tempList.add(list.get(j));
}
session.insert("insertExcel", tempList);
session.clearCache();
}
session.commit();
result=stop;
} catch (Exception e) {
e.printStackTrace();
session.rollback();
result=0;
} finally {
if (session != null) {
session.close();
}
}
return result;
}
5)对于文件,接收的参数 用 MultipartFile,
/**
* 导入excel
*/
@PostMapping("/importExcel")
@ApiOperation("导入excel")
@Log
public ReturnData importExcel(@ApiParam(value = "excel", required = true) MultipartFile file) throws IOException
{
stdCsDetailService.importExcel(file);
return ReturnDataUtil.success();
}
6)easyExcel比 poi更直接更简单,依对象直接匹配,少了很多步骤。
二、导入图片
1、图片一般保存在文件服务器 fastdfs,上传成功 fastdfs 会返回存放的地址,便于我们下载。
fastdfs 里 有存放数据的 storage 和 用于通信的 tracker,storage 里有很多的空间以00- FF命名,并且每一个里面还有一层 00- ff 命名的空间,确保每个空间不会存太多而导致查找缓慢的问题。根据传入的组名 group ,tracker 会有一个负载均衡的过程,将上传的图片保存在某一个文件夹下。
2、正常和之前的一样,使用 MultipartFile ,接收参数,然后在后台转为base64数组,便可以传输到文件服务器上。
@PostMapping("/uploadfiles")
@ApiOperation("上传文件")
public Object uploadTest(@RequestParam("files") MultipartFile[] files)
{
UploudResultToPageDto result = billBusinessInvoiceService.uploadfiles(files) ;
return ReturnDataUtil.success("上传成功",result);
}
@Override
@Transactional(rollbackFor = Exception.class)
public UploudResultToPageDto uploadfiles(MultipartFile[] files) {
if (ArrayUtils.isEmpty(files))
throw new BaseException("上传的文件为空!");
for (MultipartFile multipartFile : files) {
String base64string = null;
try {
//获取byte数组文件的后缀名
String extName = getExtName(multipartFile.getBytes());
// 以字节数组形式返回文件的内容 文件太大需要压缩
byte[] baos = zoomByteArray(multipartFile.getBytes(),extName ,0.8);
} catch (Exception e1) {
e1.printStackTrace();
throw new BaseException("文件转化为字节数组时异常!");
}
// 保存到文件表
GapFile gapFile = saveGapFile4Upload();
// 上传到文件服务器
fileServerService.uploadFile(multipartFile, gapFile);
}
}
1)获取文件后缀名:
/**
* 获取文件btye数组的后缀名
* @param downloadFile
* @return
*/
public static String getExtName(byte[] downloadFile){
byte[] b = new byte[3];
try {
ByteArrayInputStream in = new ByteArrayInputStream(downloadFile);
in.read(b, 0, b.length);
in.close();
} catch (IOException e) {
e.printStackTrace();
throw new BaseException("获取byte头文件失败!");
}
String extName = checkType(bytesToHexString(b).toUpperCase());
return extName;
}
/**
*根据头文件确定 后缀名
*/
public static String checkType(String headString) {
/**
常用文件的文件头如下:(以前六位为准)
JPEG (jpg),文件头:FFD8FF
PNG (png),文件头:89504E47
GIF (gif),文件头:47494638
TIFF (tif),文件头:49492A00
Windows Bitmap (bmp),文件头:424D
CAD (dwg),文件头:41433130
Adobe Photoshop (psd),文件头:38425053
Rich Text Format (rtf),文件头:7B5C727466
XML (xml),文件头:3C3F786D6C
HTML (html),文件头:68746D6C3E
Email [thorough only] (eml),文件头:44656C69766572792D646174653A
Outlook Express (dbx),文件头:CFAD12FEC5FD746F
Outlook (pst),文件头:2142444E
MS Word/Excel (xls.or.doc),文件头:D0CF11E0
MS Access (mdb),文件头:5374616E64617264204A
WordPerfect (wpd),文件头:FF575043
Postscript (eps.or.ps),文件头:252150532D41646F6265
Adobe Acrobat (pdf),文件头:255044462D312E
Quicken (qdf),文件头:AC9EBD8F
Windows Password (pwl),文件头:E3828596
ZIP Archive (zip),文件头:504B0304
RAR Archive (rar),文件头:52617221
Wave (wav),文件头:57415645
AVI (avi),文件头:41564920
Real Audio (ram),文件头:2E7261FD
Real Media (rm),文件头:2E524D46
MPEG (mpg),文件头:000001BA
MPEG (mpg),文件头:000001B3
Quicktime (mov),文件头:6D6F6F76
Windows Media (asf),文件头:3026B2758E66CF11
MIDI (mid),文件头:4D546864
*/
switch (headString) {
case "FFD8FF":
return "jpg";
case "89504E":
return "png";
case "474946":
return "jif";
case "47494638":
return "gif";
case "255044462D312E":
return "pdf";
case "255044":
return "pdf";
default:
return "0000";
}
}
2) 文件太大压缩:
/**
* byte[] 数组 压缩
*
* @param bytes
* @param newRate 压缩比例
*/
public static byte[] zoomByteArray(byte[] bytes, String pageType, double newRate) {
log.info("-- 压缩后图片开始 --");
DecimalFormat df = new DecimalFormat("0.00");// 设置保留位数
double rate = 0.4;
long sourceLh = bytes.length;
log.info("原始图片大小:" + df.format((float) sourceLh / mb) + "MB");
int i = 1;
BufferedImage image = null;
// 如果首次压缩还大于8MB则继续处理
while ((float) sourceLh / mb >= newRate) {
log.info("压缩后图片大小:" + sourceLh);
// rate = rate - 0.05;// 暂定按照0.03频率压缩
log.info(i + " rate=" + rate);
// BufferedImage
ByteArrayInputStream in = new ByteArrayInputStream(bytes);
BufferedImage srcImage = null;
try {
srcImage = ImageIO.read(in);
in.close();
int WIDTH = (int) (srcImage.getWidth() * rate);
int HEIGHT = (int) (srcImage.getHeight() * rate);
image = new BufferedImage(WIDTH, HEIGHT, BufferedImage.TYPE_INT_RGB);
Graphics g = image.getGraphics();
g.drawImage(srcImage, 0, 0, WIDTH, HEIGHT, null);
// 缩小
ByteArrayOutputStream out = new ByteArrayOutputStream();
ImageIO.write(image, pageType, out);
i++;
byte[] barray = out.toByteArray();
sourceLh = barray.length;
out.close();
} catch (IOException e) {
log.info("压缩出错:" + e.getMessage());
e.printStackTrace();
}
log.info("压缩次数:" + i);
}
// BufferedImage to byte[]
byte[] barray = new byte[0];
ByteArrayOutputStream out = new ByteArrayOutputStream();
try {
ImageIO.write(image, pageType, out);//png 为要保存的图片格式
barray = out.toByteArray();
log.info("处理后的大小" + barray.length);
log.info("缩小之后大小:" + df.format(barray.length / mb) + "MB");
out.close();
} catch (IOException e) {
log.info("ImageBuffer转换byte[]时出错:" + e.getMessage());
e.printStackTrace();
}
log.info("-- 压缩后图片结束 --");
return barray;
}
3)上传服务器:
/**
*上传文件 上传成功 刷新表里的路径
*/
@Override
public String uploadFile(MultipartFile file, GapFile gapFile) {
try {
// 获取文件名
String fileName = file.getOriginalFilename().trim();
// 获取文件后缀
String extName = fileName.substring(fileName.lastIndexOf(".") + 1);
GapFileServer oneByRegion = fileServerMapper.findOneByRegion(gapFile.getUserId());
FastDFSClient fastDFSClient = new FastDFSClient(conf,
new String[]{oneByRegion.getIp() + ":" + oneByRegion.getPort()});
String filePath = fastDFSClient.uploadFile(oneByRegion.getGroup_name(), file.getBytes(), extName, null);
gapFile.setFileFullPath(filePath);
gapFile.setUpdateTime(DateUtil.getTime());
gapFile.setFileName(fileName);
gapFile.setFileFormat(extName);
gapFile.setHashcode(BaseUtil.getMd5ByFile(file.getBytes()));
gapFile.setFileSize(file.getSize());
gapFile.setGroupName(oneByRegion.getGroup_name());
fileMapper.update(gapFile);
return filePath;
} catch (Exception e) {
e.printStackTrace();
throw new BaseException("上传文件到服务器失败 cause by:" + e.getMessage());
}
}
/**
*fastdfs 操作类
/
public class FastDFSClient {
private TrackerClient trackerClient = null;
private TrackerServer trackerServer = null;
private StorageServer storageServer = null;
private StorageClient1 storageClient = null;
public FastDFSClient(String conf, String ips[]) throws Exception {
this.getClass().getResource("/").getPath());
init(conf,ips);
trackerClient = new TrackerClient();
trackerServer = trackerClient.getConnection();
storageServer = null;
storageClient = new StorageClient1(trackerServer, storageServer);
//如果客户端服务启动,成功链接到dfs服务器,如果在network_timeout时间内,
// dfs服务没有接收到任何请求信息,会吧这个客户端踢掉! 所以加个test
ProtoCommon.activeTest(trackerServer.getSocket());
}
/**
* 重载java客户端初始化方法(初始化tracker,不同地区的文件服务器(storage)数据库中指定)
* @param conf_filename
* @param ips
* @throws IOException
* @throws MyException
*/
public void init(String conf_filename,String[] ips) throws IOException, MyException {
IniFileReader iniReader;
String[] szTrackerServers;
String[] parts;
iniReader = new IniFileReader(conf_filename);
ClientGlobal.g_connect_timeout = iniReader.getIntValue("connect_timeout", ClientGlobal.DEFAULT_CONNECT_TIMEOUT);
if (ClientGlobal.g_connect_timeout < 0) {
ClientGlobal.g_connect_timeout = ClientGlobal.DEFAULT_CONNECT_TIMEOUT;
}
ClientGlobal.g_connect_timeout *= 1000; //millisecond
ClientGlobal.g_network_timeout = iniReader.getIntValue("network_timeout", ClientGlobal.DEFAULT_NETWORK_TIMEOUT);
if (ClientGlobal.g_network_timeout < 0) {
ClientGlobal.g_network_timeout = ClientGlobal.DEFAULT_NETWORK_TIMEOUT;
}
ClientGlobal.g_network_timeout *= 1000; //millisecond
ClientGlobal.g_charset = iniReader.getStrValue("charset");
if (ClientGlobal.g_charset == null || ClientGlobal.g_charset.length() == 0) {
ClientGlobal.g_charset = "ISO8859-1";
}
szTrackerServers = ips;
if (szTrackerServers == null) {
throw new MyException("item \"tracker_server\" in " + conf_filename + " not found");
}
InetSocketAddress[] tracker_servers = new InetSocketAddress[szTrackerServers.length];
for (int i = 0; i < szTrackerServers.length; i++) {
parts = szTrackerServers[i].split("\\:", 2);
if (parts.length != 2) {
throw new MyException("the value of item \"tracker_server\" is invalid, the correct format is host:port");
}
tracker_servers[i] = new InetSocketAddress(parts[0].trim(), Integer.parseInt(parts[1].trim()));
}
ClientGlobal.g_tracker_group = new TrackerGroup(tracker_servers);
ClientGlobal.g_tracker_http_port = iniReader.getIntValue("http.tracker_http_port", 80);
ClientGlobal.g_anti_steal_token = iniReader.getBoolValue("http.anti_steal_token", false);
if (ClientGlobal.g_anti_steal_token) {
ClientGlobal.g_secret_key = iniReader.getStrValue("http.secret_key");
}
}
/**
* 关闭资源
*/
public void close() {
try {
if(null != trackerServer){
trackerServer.close();
}
if(null != storageServer){
storageServer.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 上传文件方法
* <p>Title: uploadFile</p>
* <p>Description: </p>
* @param fileName 文件全路径
* @param extName 文件扩展名,不包含(.)
* @param metas 文件扩展信息
* @return
* @throws Exception
*/
public String uploadFile(String fileName, String extName, NameValuePair[] metas) throws Exception {
String result = storageClient.upload_file1(fileName, extName, null);
return result;
}
public String uploadFile(String fileName) throws Exception {
return uploadFile(fileName, null, null);
}
public String uploadFile(String fileName, String extName) throws Exception {
return uploadFile(fileName, extName, null);
}
/**
* 上传文件方法
* <p>Title: uploadFile</p>
* <p>Description: </p>
* @param fileContent 文件的内容,字节数组
* @param extName 文件扩展名
* @param metas 文件扩展信息
* @return
* @throws Exception
*/
public String uploadFile(byte[] fileContent, String extName, NameValuePair[] metas) throws Exception {
String result = storageClient.upload_file1(fileContent, extName, null);
return result;
}
/**
* 上传文件方法(指定分组)
* <p>Title: uploadFile</p>
* <p>Description: </p>
* @param fileContent 文件的内容,字节数组
* @param extName 文件扩展名
* @param metas 文件扩展信息
* @return
* @throws Exception
*/
public String uploadFile(String groupName,byte[] fileContent, String extName, NameValuePair[] metas) throws Exception {
String result = storageClient.upload_file1(groupName,fileContent, extName, metas);
return result;
}
public String uploadFile(byte[] fileContent) throws Exception {
return uploadFile(fileContent, null, null);
}
public String uploadFile(byte[] fileContent, String extName) throws Exception {
return uploadFile(fileContent, extName, null);
}
/**
* 下载文件(指定分组)
* <p>Title: uploadFile</p>
* <p>Description: </p>
* @param groupName 分组
* @param remoteFileName 服务器上的文件地址
* @return
* @throws Exception
*/
public byte[] downloadFile(String groupName, String remoteFileName) throws Exception {
byte[] result = storageClient.download_file(groupName, remoteFileName);
return result;
}
/**
* 删除文件(指定分组)
* <p>Title: uploadFile</p>
* <p>Description: </p>
* @param groupName 分组
* @param remoteFileName 服务器上的文件地址
* @return
* @throws Exception
*/
public int deleteFile(String groupName, String remoteFileName) throws Exception {
int result = storageClient.delete_file(groupName, remoteFileName);
return result;
}
public TrackerServer getTrackerServer() {
return trackerServer;
}
public void setTrackerServer(TrackerServer trackerServer) {
this.trackerServer = trackerServer;
}
public StorageServer getStorageServer() {
return storageServer;
}
public void setStorageServer(StorageServer storageServer) {
this.storageServer = storageServer;
}
public TrackerClient getTrackerClient() {
return trackerClient;
}
public void setTrackerClient(TrackerClient trackerClient) {
this.trackerClient = trackerClient;
}
public StorageClient1 getStorageClient() {
return storageClient;
}
public void setStorageClient(StorageClient1 storageClient) {
this.storageClient = storageClient;
}
}
文件服务器的存放的数据:
三、上传 json 文件:
也可以使用 MultipartFile 接收,然后 转为json,构建对象吧json转化为对象,就可以存储了。
JSONArray jsonArray = JSONObject.parseArray(stringBuffer.toString());