-
Notifications
You must be signed in to change notification settings - Fork 0
/
index.html
216 lines (204 loc) · 6.95 KB
/
index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>小松鼠-js读取Excel演示</title>
<script src="./xlsx.full.min.js"></script>
<style>
/* 添加表格边框样式 */
table {
border-collapse: collapse;
width: 100%;
}
th,
td {
border: 1px solid black;
padding: 8px;
text-align: left;
}
th {
background-color: #f2f2f2;
}
/* 固定表头 */
thead {
position: sticky;
top: 0;
z-index: 1;
background-color: #fff; /* 如果需要,可以添加背景色 */
}
</style>
</head>
<body>
<div>
<input type="file" onchange="readExcel(this,1)" />
<input type="file" onchange="readExcel(this,2)" />
<input type="text" id="inputText" value="" placeholder="请输入对比字段" />
<input type="button" value="读取Excel对比" onclick="compare()" />
<p id="result" style="color: #67c23a">
<span class="excel-result1"></span>
<span class="excel-result2"></span>
<span class="excel-result3" style="color: #409eff"></span>
<span class="excel-result4"></span>
</p>
</div>
<br />
<br />
<div id="tableContainer"></div>
<script>
let fileData1 = [],
fileData2 = [],
theadRedList = [],
newCreatedData = 0;
function readExcel(file_obj, ind) {
var reader = new FileReader();
var file = file_obj.files[0];
reader.readAsBinaryString(file);
reader.onload = function (e) {
var data = e.target.result;
var wb = XLSX.read(data, { type: "binary" });
sheetName = wb.SheetNames[0]; // 获取文档中第一个sheet页签的名字
sheets = wb.Sheets[sheetName]; // 获sheet名页签下的数据
const dataJson = XLSX.utils.sheet_to_json(sheets,{
Header:0,
defval:"空"
});
if (ind === 1) {
fileData1 = dataJson;
document.body.getElementsByClassName(
"excel-result" + ind
)[0].innerHTML = `读取${file.name}成功!共${fileData1.length}条数据`;
} else if (ind === 2) {
fileData2 = dataJson
document.body.getElementsByClassName(
"excel-result" + ind
)[0].innerHTML = `读取${file.name}成功!共${fileData2.length}条数据`;
}
};
}
function compare() {
let data = [];
let inputVal = document.getElementById("inputText").value;
if (fileData1.length == 0 || fileData2.length == 0) {
alert("请先选择文件");
return;
}
if (!inputVal.trim()) {
alert("请输入字段名称");
return;
}
let allKeys = Object.keys(fileData1[0]);
if (!allKeys.includes(inputVal.trim())) {
alert("请输入正确的字段名称");
return;
}
if (fileData1.length > fileData2.length) {
data = findDataDifference(fileData1, fileData2, inputVal.trim());
} else {
data = findDataDifference(fileData2, fileData1, inputVal.trim());
}
document.body.getElementsByClassName(
"excel-result4"
)[0].innerHTML = `对比已结束:共${data.length}条数据`;
// console.log("不一致数据:", data);
if (data.length == 0) {
alert("数据一致");
} else {
createTable(data);
}
}
function findDataDifference(data1, data2, key) {
const result = [];
theadRedList = [];
// 遍历第一组数据
for (const item1 of data1) {
// 查找第二组数据中与当前项相同键值的项
const matchingItem = data2.find((item2) => item2[key] === item1[key]);
// 如果找不到匹配项,说明该项只在第一组数据中出现
if (!matchingItem) {
newCreatedData++;
for (const key in item1) {
if (Object.hasOwnProperty.call(item1, key)) {
item1[key] = item1[key] + "++++add";
}
}
result.push(item1);
} else {
// 如果找到匹配项,比较其他字段值是否相同
let isEqual = true;
for (const field in item1) {
if (item1.hasOwnProperty(field)) {
if (field !== key && item1[field] !== matchingItem[field]) {
item1[field] = item1[field] + "++++red";
if (!theadRedList.includes(field)) {
theadRedList.push(field);
}
isEqual = false;
}
}
}
// 如果其他字段值不同,则将该项加入结果数组
if (!isEqual) {
result.push(item1);
}
}
}
document.body.getElementsByClassName(
"excel-result3"
)[0].innerHTML = `新增${newCreatedData}条数据`;
return result;
}
// 创建表格
const createTable = (data) => {
const tableContainer = document.getElementById("tableContainer");
const table = document.createElement("table");
const tbody = document.createElement("tbody");
// 创建表头
const thead = document.createElement("thead");
const trHead = document.createElement("tr");
for (const key in data[0]) {
const th = document.createElement("th");
if (theadRedList.includes(key)) {
th.style.color = "red";
}
th.textContent = key + "__" + countRedInField(data, key);
trHead.appendChild(th);
}
thead.appendChild(trHead);
table.appendChild(thead);
// 创建表格内容
data.forEach((item) => {
const trBody = document.createElement("tr");
for (const key in item) {
const td = document.createElement("td");
if (String(item[key]).includes("++++red")) {
td.style.color = "red";
td.textContent = item[key].replace("++++red", "");
} else if (String(item[key]).includes("++++add")) {
td.style.color = "#409EFF";
td.textContent = item[key].replace("++++add", "");
} else {
td.textContent = item[key];
}
trBody.appendChild(td);
}
tbody.appendChild(trBody);
});
table.appendChild(tbody);
// 添加表格到容器中
tableContainer.innerHTML = "";
tableContainer.appendChild(table);
};
function countRedInField(data, fieldName) {
let count = 0;
data.forEach((item) => {
if (item[fieldName] && typeof item[fieldName] === "string") {
if (item[fieldName].includes("++++red")) {
count++;
}
}
});
return count;
}
</script>
</body>
</html>