前言
在本次项目中,有一个需求,导入本地Excel文件,对比数据库差异数据,再将差异数据导出Excel
项目使用前后端分离,所有这里采用前后端分离思想
一、思路
- 读取本地Excel文件,获取其中的数据
- 通过获取的参数查询数据库数据
- 对比差异数据
- 导出差异数据Excel表格
二、使用步骤
1.引入库
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!--如有需要处理大量数据则导入-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2.读取上传的Excel数据并对比差异数据接口
/**
* 对比上传的Exccel的差异数据
*/
public JSONObject dataComparison(JSONObject jsonobject) {
// 请求文件路径地址
String url = jsonobject.getString("url");
Assert.isBlank(url, "清选择文件");
String fullFileName = url;
List<JSONObject> list;
try {
InputStream is = new FileInputStream(fullFileName);
list = read2007Xlsx(is);
} catch (Exception e) {
e.getMessage();
throw new BaseException(400, "读取文件错误");
}
// 匹配数据库数据
JSONObject params = new JSONObject();
params.put("list", list);
JSONObject objectList = matchDatabaseData(params);
return objectList ;
}
/**
* 读取excel文件
* XSSF -- 提供读写Microsoft Excel OOXML格式档案的功能
* XSSFWorkbook:是操作Excel2007(以上)的版本,扩展名是.xlsx
* @param in
* @return
* @throws IOException
*/
public static List<JSONObject> read2007Xlsx(InputStream in) throws IOException {
List<JSONObject> list = new ArrayList<>();
XSSFWorkbook xWorkbook = new XSSFWorkbook(in);
// Read the Sheet
XSSFSheet xssfSheet = xWorkbook.getSheetAt(0);
// Read the Row
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
// 设置读取格式
xssfRow.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
xssfRow.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
xssfRow.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
xssfRow.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
JSONObject params = new JSONObject();
params.put("receivedLinkman", xssfRow.getCell(0).getStringCellValue());
params.put("receivedTelephone", xssfRow.getCell(1).getStringCellValue());
// 开始时间
String _startTime = xssfRow.getCell(2).getStringCellValue();
Date startTime = HSSFDateUtil.getJavaDate(Double.valueOf(_startTime));
params.put("startTime", testTime(startTime));
String _endTime = xssfRow.getCell(3).getStringCellValue();
Date endTime = HSSFDateUtil.getJavaDate(Double.valueOf(_endTime));
params.put("endTime", testTime(endTime));
list.add(params);
}
}
xWorkbook.close();
return list;
}
/**
* 匹配数据库数据
* key = "list"
*
* @param params
*/
private BasePageInfo<JSONObject> matchDatabaseData(JSONObject params) {
if (params == null && params.getString("list") == null && params.getJSONArray("list").isEmpty()) {
return null;
}
JSONArray array = params.getJSONArray("list");
JSONObject jsonObject = new JSONObject();
if (array != null && array.size() > 0) {
jsonObject = array.getJSONObject(0);
jsonObject.put("page", params.getString("page"));
jsonObject.put("rows", params.getString("rows"));
}
// 查询相关订单数据
BasePageInfo<JSONObject> objectList = iOrderDao.queryOrderByExcel(jsonObject);
JSONArray list = params.getJSONArray("list");
// 处理差异数据
List<JSONObject> returnList = processingDifferenceData(list, objectList.getList());
objectList.setList(returnList);
return objectList;
}
3.获取差异数据并导出Excel
前面通过上一个接口获取到差异数据,将差异数据写入Excel表格
public JSONObject exportExcel(JSONObject jsonObject) {
// 导出差异数据
JSONArray list = jsonObject.getJsonArray("list");
if (list == null) {
throw new BaseException(400, "缺少导出数据");
}
// 获取桌面路径
String path = getDesktopPath();
// 填充导出内容
fillExportContent(path, list);
return Output.out(Message.OK, Message.EXPORT_EXCEL);
}
/**
* 获取桌面路径
*
* @return
*/
private static String getDesktopPath() {
// 获取桌面
FileSystemView view = FileSystemView.getFileSystemView();
File file = view.getHomeDirectory();
String path = file.getPath();
path.replace("\\", "/");
return file.getPath();
}
/**
* 填充导出内容
*
* @param path 导出路径
* @param list 导出内容
*/
private void fillExportContent(String path, JSONArray list) {
if (list == null && list.size() <= 0) {
return;
}
//创建工作薄对象
HSSFWorkbook workbook = new HSSFWorkbook();//这里也可以设置sheet的Name
//创建工作表对象
HSSFSheet sheet = workbook.createSheet();
//创建工作表的行
HSSFRow row = sheet.createRow(0);//设置第一行,从零开始
//第一行第一列
row.createCell(0).setCellValue("订单号");
//第一行第二列
row.createCell(1).setCellValue("收货人");
//第一行第三列
row.createCell(2).setCellValue("收货人电话");
//第一行第四列 settledAmount
row.createCell(3).setCellValue("订单结算金额");
row.createCell(4).setCellValue("送货人");
row.createCell(5).setCellValue("送货人电话");
row.createCell(6).setCellValue("送货地址");
// (1wap移动 2pc 3微信小程序 4百度小程序 5第三方平台)
row.createCell(7).setCellValue("订单来源");
// 转单平台(0未转 1花娃 2花大侠 3无需转单 4转单宝)
row.createCell(8).setCellValue("转单平台");
row.createCell(9).setCellValue("创建时间");
row.createCell(10).setCellValue("支付类型");
// 设置表体内容
for (int i = 0; i < list.size(); i++) {
JSONObject listJSONObject = list.getJSONObject(i);
row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(listJSONObject.getString("orderCode"));
row.createCell(1).setCellValue(listJSONObject.getString("receivedLinkman"));
row.createCell(2).setCellValue(listJSONObject.getString("receivedTelephone"));
row.createCell(3).setCellValue(listJSONObject.getString("settledAmount"));
row.createCell(4).setCellValue(listJSONObject.getString("sendLinkman"));
row.createCell(5).setCellValue(listJSONObject.getString("sendTelephone"));
row.createCell(6).setCellValue(listJSONObject.getString("address"));
String platform = listJSONObject.getString("platform");
switch (platform) {
case "1":
platform = "移动端";
break;
case "2":
platform = "pc端";
break;
case "3":
platform = "微信小程序";
break;
case "4":
platform = "百度小程序";
break;
case "5":
platform = "第三方平台";
break;
}
row.createCell(7).setCellValue(platform);
String deferentPlatform = listJSONObject.getString("deferentPlatform");
switch (deferentPlatform) {
case "0":
deferentPlatform = "未转";
break;
case "1":
deferentPlatform = "花娃";
break;
case "2":
deferentPlatform = "花大侠";
break;
case "3":
deferentPlatform = "无需转单";
break;
case "4":
deferentPlatform = "转单宝";
break;
}
row.createCell(8).setCellValue(deferentPlatform);
row.createCell(9).setCellValue(listJSONObject.getString("createTime"));
row.createCell(10).setCellValue(listJSONObject.getString("payName"));
}
// workbook.setSheetName(0,"sheet的Name");//设置sheet的Name
//文档输出
try {
FileOutputStream out = new FileOutputStream(path + "/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() + ".xls");
workbook.write(out);
out.close();
} catch (Exception e) {
e.getMessage();
throw new BaseException(400, "导出错误");
}
}
总结
- 导入导出注意文件格式
- 获取路径的时候使用 “/”