完整版excel上传导入读写批量数据并将反馈结果写入远程exel中

package com.XXXX.XXXXX.utils; import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List; import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ResourceUtils; import com.sun.javafx.scene.control.skin.TreeTableRowSkin;
import com.xxx.cloud.common.utils.DateFormatUtil;
import com.xxx.cloud.common.utils.StringUtil; public class ReadExcelUtil { private static final String EXCEL_XLS = ".xls";
private static final String EXCEL_XLSX = ".xlsx"; /**
*读取excel数据
* @throws Exception
*
*/
public static List<List<String>> readExcelInfo(String url) throws Exception{
// 支持excel2003、2007
File excelFile = new File(url);//创建excel文件对象
InputStream is = new FileInputStream(excelFile);//创建输入流对象
checkExcelVaild(excelFile);
Workbook workbook = getWorkBook(is, excelFile);
// Workbook workbook = WorkbookFactory.create(is);//同时支持2003、2007、2010
// 获取Sheet数量
int sheetNum = workbook.getNumberOfSheets();
sheetNum = 1;//限制模板只在一个工作簿上操作
// 创建二维数组保存所有读取到的行列数据，外层存行数据，内层存单元格数据
List<List<String>> dataList = new ArrayList<List<String>>();
// 遍历工作簿中的sheet,第一层循环所有sheet表
for(int index = 0;index<sheetNum;index++){
Sheet sheet = workbook.getSheetAt(index);
if(sheet==null){
continue;
}
// 如果当前行没有数据跳出循环，第二层循环单sheet表中所有行
for(int rowIndex=0;rowIndex<=sheet.getLastRowNum();rowIndex++){
System.out.println(sheet.getLastRowNum()+"====");
Row row = sheet.getRow(rowIndex);
if(row==null){
continue;
}
// 遍历每一行的每一列，第三层循环行中所有单元格
List<String> cellList = new ArrayList<String>();
for(int cellIndex=0;cellIndex<row.getLastCellNum();cellIndex++){
Cell cell = row.getCell(cellIndex);
System.out.println(cellIndex);
cellList.add(getCellValue(cell));
}
dataList.add(cellList);
} }
is.close();
return dataList;
}
/**
*获取单元格的数据,暂时不支持公式
*
*
*/
public static String getCellValue(Cell cell){
CellType cellType = cell.getCellTypeEnum();
if(cellType==null){
return null;
}
String cellValue = "";
if(cell==null || cell.toString().trim().equals("")){
return null;
} if(cellType==CellType.STRING){
cellValue = cell.getStringCellValue().trim();
return cellValue = StringUtil.isEmpty(cellValue)?"":cellValue;
}
if(cellType==CellType.NUMERIC){
if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断日期类型
cellValue = DateFormatUtil.formatDurationYMD(cell.getDateCellValue().getTime());
} else { //否
cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
}
return cellValue;
}
if(cellType==CellType.BOOLEAN){
cellValue = String.valueOf(cell.getBooleanCellValue());
return cellValue;
}
return null; }
/**
*判断excel的版本，并根据文件流数据获取workbook
* @throws IOException
*
*/
public static Workbook getWorkBook(InputStream is,File file) throws Exception{ Workbook workbook = null;
if(file.getName().endsWith(EXCEL_XLS)){
workbook = new HSSFWorkbook(is);
}else if(file.getName().endsWith(EXCEL_XLSX)){
workbook = new XSSFWorkbook(is);
} return workbook;
}
/**
*校验文件是否为excel
* @throws Exception
*
*
*/
public static void checkExcelVaild(File file) throws Exception {
String message = "该文件是EXCEL文件！";
if(!file.exists()){
message = "文件不存在！";
throw new Exception(message);
}
if(!file.isFile()||((!file.getName().endsWith(EXCEL_XLS)&&!file.getName().endsWith(EXCEL_XLSX)))){
message = "文件不是Excel";
throw new Exception(message);
}
}
/**
*校验上传的excel模板是否正确
*
*
*
*/
public static boolean checkExcelTemplate(String url){
try {
List<List<String>> list = ReadExcelUtil.readExcelInfo(url);
for(int i=0;i<list.size();i++){
if(i==0){
if(!list.get(i).get(0).trim().equals("公司ID")||!list.get(i).get(1).trim().equals("产品ID")||!list.get(0).get(2).trim().equals("设备类型")
||!list.get(i).get(3).trim().equals("设备型号")||!list.get(i).get(4).trim().equals("设备名称")){
return false;
}
}
if(i==2){
if(!list.get(i).get(0).trim().equals("设备ID")||!list.get(i).get(1).trim().equals("结果")){
return false;
}
}
}
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return true; }
/**
* 将反馈结果写入excel中
* */
public static void writeExcelResult(String url,List<Integer> result) throws Exception{
// 支持excel2003、2007
File excelFile = new File(url);//创建excel文件对象
InputStream is = new FileInputStream(excelFile);//创建输入流对象
checkExcelVaild(excelFile);
Workbook workbook = getWorkBook(is, excelFile);
// Workbook workbook = WorkbookFactory.create(is);//同时支持2003、2007、2010
// 获取Sheet数量
int sheetNum = workbook.getNumberOfSheets();
sheetNum = 1;//限制模板只在一个工作簿上操作
// 遍历工作簿中的sheet,第一层循环所有sheet表
for(int index = 0;index<sheetNum;index++){
Sheet sheet = workbook.getSheetAt(index);
if(sheet==null){
continue;
}
// 如果当前行没有数据跳出循环，第二层循环单sheet表中所有行
for(int rowIndex=3;rowIndex<=sheet.getLastRowNum();rowIndex++){
Row row = sheet.getRow(rowIndex);
row.createCell(1).setCellValue(result.get(rowIndex-3));
} }
FileOutputStream outputStream = new FileOutputStream(url);
workbook.write(outputStream);
outputStream.close();
}
/**
* 根据地址获得客户上传的excel字节流
* @param fileUrl 网络连接地址
* @return
*/
public static byte[] getExcelFromAliyun(String fileUrl){
try {
URL url = new URL(fileUrl);
HttpURLConnection conn = (HttpURLConnection)url.openConnection();
conn.setRequestMethod("GET");
conn.setConnectTimeout(5 * 1000);
InputStream inStream = conn.getInputStream();//通过输入流获取excelFile数据
byte[] excelFile = readInputStream(inStream);//得到excelFile的二进制数据
return excelFile;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 从网上得到的输入流中获取数据转换为二进制数据
* @param inStream 输入流
* @return
* @throws Exception
*/
public static byte[] readInputStream(InputStream inStream) throws Exception{
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int len = 0;
while( (len=inStream.read(buffer)) != -1 ){
outStream.write(buffer, 0, len);
}
inStream.close();
return outStream.toByteArray();
}
/**
* 将文件写入到目标目录中
* @param excel 文件数据流
* @param fileName 文件保存时的名称
*/
public static void writeFileToDest(byte[] excelFile, File dest){
try {
FileOutputStream out = new FileOutputStream(dest);
out.write(excelFile);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/***
*
* 在项目中创建临时文件
* @throws IOException
* */
public static File createTempFile(String fileName) throws IOException{
File path = new File(ResourceUtils.getURL("classpath:").getPath());
if(!path.exists()) path = new File("");
File upload = new File(path.getAbsolutePath(),"static/images/upload/");
if(!upload.exists()) upload.mkdirs();
File tempFile = new File(upload+"/"+fileName);
if(!tempFile.getParentFile().exists()){
tempFile.getParentFile().mkdirs();//创建父级文件路径
tempFile.createNewFile();//创建文件
}
return tempFile;
}
}

@PostMapping("/addBatchDevice")
public ResponseObj addBatchDevice(@RequestBody JSONObject obj) throws IOException{
logger.info("导入批量设备："+obj.toJSONString());
ResponseObj response = new ResponseObj();
String id = obj.getString("id");
BatchRecord batchRecord = deviceService.selectBatchRecordById(id);
String path = aliConstants.aliyunHostOuter+"/"+batchRecord.getFilePath();
// 将该该文件下载出来保存到项目中
byte[] excelFile = ReadExcelUtil.getExcelFromAliyun(path);
File tempFile = ReadExcelUtil.createTempFile(batchRecord.getFileName());
ReadExcelUtil.writeFileToDest(excelFile, tempFile);
String url = tempFile.getAbsolutePath();
String companyId = null;
String productId = null;
Integer deviceType = null;
String model = null;
String deviceName = null;
boolean flag = ReadExcelUtil.checkExcelTemplate(url);
if(!flag){
response.setData(Defined.STATUS_ERROR);
response.setMessage("文件有误，请根据模板上传文件。");
return response;
}
List<Integer> result = new ArrayList<Integer>();//存放反馈信息
try {
List<List<String>> list = ReadExcelUtil.readExcelInfo(url);
for(int i=0;i<list.size();i++){
if(i==0||i==2){
continue;//跳过模板第1,3行
}
if(i==1){
companyId = list.get(i).get(0);
productId = list.get(i).get(1);
deviceType = Integer.valueOf(list.get(i).get(2));
model = list.get(i).get(3);
deviceName = list.get(i).get(4);
}
if(i>2){
// new一个对象按照相应的字段设置进去就可以了，这里省略对象设置值，字段如下：
Device device = new Device();
String deviceId = IdGen.uuid();
device.setId(deviceId);
//省略部分业务代码
DeviceFields deviceFields = new DeviceFields();
deviceFields.setId(IdGen.uuid());
deviceFields.setDeviceId(deviceId);
//省略部分业务代码
Gateway gateway = new Gateway();
gateway.setId(IdGen.uuid());
//省略部分业务代码
if(!deviceService.checkDeviceUidRepeat(uid)){
// 重复，返回sheet行号，并写入sheet表单中
result.add(1);
continue;
}
// 关联表一个事务处理
boolean flg = deviceService.addDeviceEtc(device, deviceFields, gateway);
if(!flg){
result.add(1);
}else{
result.add(0);
}
}
}
// 将反馈结果写入文件0-成功，1-失败
ReadExcelUtil.writeExcelResult(url, result);
AliYunFileSetting setting = new AliYunFileSetting();
setting.setAccessKeyId(aliConstants.accessKeyId);
setting.setAccessKeySecret(aliConstants.accessKeySecret);
setting.setBucketName(aliConstants.bucketName);
setting.setEndpoint(aliConstants.endpoint);
AliyunFileManager manager = AliyunFileManager.getInstance(setting);
InputStream is = new FileInputStream(tempFile);
String relativePath =aliConstants.excelFilePath;//相对路径
boolean fg = manager.upload(is, relativePath, batchRecord.getFileName());//上传文件与客户上传后生成的文件名相同。
if(fg){
logger.info("反馈已经成功写入文件中！");
// 更新批量记录
batchRecord.setUpdateTime(DateUtil.getNowTimestamp());
deviceService.updateBatchRecordByPrimaryKey(batchRecord);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setMessage("批量导入设备成功！");
response.setStatus(Defined.STATUS_SUCCESS);
return response;
}
@PostMapping("uploadExcel")
public ResponseObj uploadExcel(@RequestParam("excelFile") MultipartFile file,@RequestParam("companyId") String companyId,
@RequestParam("productId") String productId,HttpServletRequest request) throws Exception {
ResponseObj response = new ResponseObj();
response.setData(Defined.STATUS_SUCCESS);
response.setMessage("文件上传成功！");
ResponseObj resp = new ResponseObj();
resp.setData(Defined.STATUS_ERROR);
resp.setMessage("不是文件！");
AliYunFileSetting setting = new AliYunFileSetting();
setting.setAccessKeyId(aliConstants.accessKeyId);
setting.setAccessKeySecret(aliConstants.accessKeySecret);
setting.setBucketName(aliConstants.bucketName);
setting.setEndpoint(aliConstants.endpoint);
AliyunFileManager manager = AliyunFileManager.getInstance(setting);
if(file.isEmpty()){
response.setData(Defined.STATUS_ERROR);
response.setMessage("不是文件！");
return response;
}
String fileName = file.getOriginalFilename();
long fileSize = file.getSize();
File tempFile = ReadExcelUtil.createTempFile(fileName);
String relativePath =aliConstants.excelFilePath;//相对路径
String dir = aliConstants.aliyunHostOuter+"/"+relativePath;//云端绝对路径
File dest = new File(dir);
if(!dest.exists()){ //判断文件目录是否存在
dest.mkdir();//
}
try {
file.transferTo(tempFile);
InputStream is = new FileInputStream(tempFile);
String suffix=fileName.substring(fileName.lastIndexOf("."));//获取原始文件后缀.xlxs(含点)
String newFileName = IdGen.uuid()+suffix;
boolean result = manager.upload(is, relativePath, newFileName);//上传文件，并建立随机文件名。
// boolean result = manager.upload(is, dir, fileName);//上传阿里云，固定文件名
if(result){
response.setData(dir+"/"+newFileName);//返回新建文件名的绝对路径
// 数据库存入相对路径
BatchRecord batchRecord = new BatchRecord();
batchRecord.setFileName(newFileName);
batchRecord.setFilePath(relativePath+"/"+newFileName);
batchRecord.setFileSize(fileSize);
batchRecord.setIsDelete((byte) 0);
batchRecord.setStatus((byte) 0);
batchRecord.setId(IdGen.uuid());
batchRecord.setCreateTime(DateUtil.getNowTimestamp());
// batchRecord.setUpdateTime(DateUtil.getNowTimestamp());
batchRecord.setCompanyId(companyId);
batchRecord.setProductId(productId);
Integer resultNum = deviceService.addBatchRecord(batchRecord);
if(resultNum>0){
tempFile.delete();
return response;
}
return resp;
}else{
resp.setMessage("文件上传失败！");
return resp;
}
} catch (IllegalStateException e) {
// TODO Auto-generated catch block
e.printStackTrace();
resp.setMessage("文件上传异常！");
return resp;
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
resp.setMessage("文件上传异常！");
return resp;
}
}