好友
阅读权限30
听众
最后登录1970-1-1
|
本帖最后由 lostlosin 于 2025-12-1 13:53 编辑
原文链接https://www.52pojie.cn/thread-2020893-1-1.html
在此基础上调整按钮顺序及部分字符，增加或修复部分功能
1、工作表、表头、参照列可选
2、修复跨行显示问题（原文件如文件删除第5行后另存，跨行对比，则第5行及后续都显示为不匹配的红色）
3、历史记录可删除
蓝奏云https://wwazg.lanzoub.com/iwTBY3ckfk2d
[JavaScript] 纯文本查看 复制代码 // 收集所有唯一的参照列值
const allRefValues = new Set();
// 收集data1中的参照列值
for (let row = 0; row < data1.length; row++) {
if (row !== headerRowIndex1) {
const refVal = data1[row][referenceColumn1] !== undefined ? data1[row][referenceColumn1] : '';
if (refVal !== '') {
allRefValues.add(String(refVal).trim());
}
}
}
// 收集data2中的参照列值
for (let row = 0; row < data2.length; row++) {
if (row !== headerRowIndex2) {
const refVal = data2[row][referenceColumn2] !== undefined ? data2[row][referenceColumn2] : '';
if (refVal !== '') {
allRefValues.add(String(refVal).trim());
}
}
}
[JavaScript] 纯文本查看 复制代码 // 为每个唯一的参照列值创建一行
for (const refValue of refValuesArray) {
const tr1 = document.createElement('tr');
const tr2 = document.createElement('tr');
// 添加行号
const rowHeader1 = document.createElement('th');
const rowHeader2 = document.createElement('th');
rowHeader1.textContent = displayRow + 1;
rowHeader2.textContent = displayRow + 1;
tr1.appendChild(rowHeader1);
tr2.appendChild(rowHeader2);
// 在data1中查找匹配行
const matchingRow1Index = data1.findIndex((row, idx) => {
if (idx === headerRowIndex1) return false;
const refVal = row[referenceColumn1] !== undefined ? row[referenceColumn1] : '';
return String(refVal).trim() === refValue;
});
// 在data2中查找匹配行
const matchingRow2Index = data2.findIndex((row, idx) => {
if (idx === headerRowIndex2) return false;
const refVal = row[referenceColumn2] !== undefined ? row[referenceColumn2] : '';
return String(refVal).trim() === refValue;
});
// 获取匹配行的数据
const rowData1 = matchingRow1Index !== -1 ? data1[matchingRow1Index] : [];
const rowData2 = matchingRow2Index !== -1 ? data2[matchingRow2Index] : [];
for (let col = 0; col < maxCols; col++) {
const td1 = document.createElement('td');
const td2 = document.createElement('td');
let val1 = '';
let val2 = '';
// 获取单元格值
if (col < rowData1.length) {
val1 = rowData1[col] !== undefined ? rowData1[col] : '';
}
if (col < rowData2.length) {
val2 = rowData2[col] !== undefined ? rowData2[col] : '';
}
// 基于表头映射获取对应列的值
if (col in columnMap) {
const mappedCol = columnMap[col];
if (mappedCol < rowData2.length) {
val2 = rowData2[mappedCol] !== undefined ? rowData2[mappedCol] : '';
}
}
td1.textContent = formatCellValue(val1);
td2.textContent = formatCellValue(val2);
// 使单元格可编辑
td1.contentEditable = true;
td2.contentEditable = true;
// 添加编辑事件监听
td1.addEventListener('blur', function() {
const newValue = this.textContent;
if (!window.editableData1[matchingRow1Index]) {
window.editableData1[matchingRow1Index] = [];
}
window.editableData1[matchingRow1Index][col] = newValue;
// 重新计算并更新整个表格的差异标记
displayComparisonResults(window.editableData1, window.editableData2,
document.getElementById('sheet-select1').options[currentSheetIndex1].text,
document.getElementById('sheet-select2').options[currentSheetIndex2].text);
displayComparisonSummary(window.editableData1, window.editableData2);
});
td2.addEventListener('blur', function() {
const newValue = this.textContent;
if (!window.editableData2[matchingRow2Index]) {
window.editableData2[matchingRow2Index] = [];
}
window.editableData2[matchingRow2Index][col] = newValue;
// 重新计算并更新整个表格的差异标记
displayComparisonResults(window.editableData1, window.editableData2,
document.getElementById('sheet-select1').options[currentSheetIndex1].text,
document.getElementById('sheet-select2').options[currentSheetIndex2].text);
displayComparisonSummary(window.editableData1, window.editableData2);
});
// 比较单元格值
const compareMode = document.getElementById('compare-mode').value;
if (compareMode === 'row-by-row') {
// 逐行对比模式
if (String(val1) !== String(val2)) {
td1.classList.add('diff-cell');
td2.classList.add('diff-cell');
diffCount++;
} else {
td1.classList.add('match-cell');
td2.classList.add('match-cell');
matchCount++;
}
} else {
// 跨行对比模式
// 根据表头一致性决定颜色标记
if (col in columnMap) {
// 如果该列有映射关系（表头一致）
// 默认不添加任何类（蓝色默认状态）
// 检查数据是否一致
if (String(val1) !== String(val2)) {
// 数据不一致，标记为不匹配（红色）
td1.classList.add('diff-cell');
td2.classList.add('diff-cell');
diffCount++;
} else {
// 数据一致，保持默认蓝色状态
// 不添加任何类（蓝色默认状态）
matchCount++;
}
} else {
// 如果该列没有映射关系（表头不一致）
td1.classList.add('match-cell');
td2.classList.add('match-cell');
matchCount++;
// 检查数据是否一致
if (val1 !== '' && val2 !== '' && String(val1) !== String(val2)) {
// 数据不一致，标记为不匹配（红色）
td1.classList.remove('match-cell');
td2.classList.remove('match-cell');
td1.classList.add('diff-cell');
td2.classList.add('diff-cell');
matchCount--;
diffCount++;
}
}
}
tr1.appendChild(td1);
tr2.appendChild(td2);
}
tbody1.appendChild(tr1);
tbody2.appendChild(tr2);
displayRow++;
}
[JavaScript] 纯文本查看 复制代码 // 准备工作表选择器
function prepareSheetSelector() {
console.log('=== prepareSheetSelector called ===');
const sheetSelector = document.getElementById('sheet-selector');
const sheetSelect1 = document.getElementById('sheet-select1');
const sheetSelect2 = document.getElementById('sheet-select2');
const referenceColumnSelect1 = document.getElementById('reference-column1');
const referenceColumnSelect2 = document.getElementById('reference-column2');
const headerRowInput1 = document.getElementById('header-row1');
const headerRowInput2 = document.getElementById('header-row2');
if (!sheetSelector) {
console.log('Sheet selector container not found');
return;
}
if (!sheetSelect1 || !sheetSelect2) {
console.log('Sheet selectors not found in prepareSheetSelector');
return;
}
sheetSelect1.innerHTML = '';
sheetSelect2.innerHTML = '';
// 清空参照列选项
if (referenceColumnSelect1) {
referenceColumnSelect1.innerHTML = '';
}
if (referenceColumnSelect2) {
referenceColumnSelect2.innerHTML = '';
}
// 获取两个工作簿的工作表名称
const sheets1 = file1Workbook.SheetNames;
const sheets2 = file2Workbook.SheetNames;
console.log('Available sheets:', { sheets1, sheets2 });
// 添加选项到第一个下拉框（保持原始名称，包括空格）
sheets1.forEach((sheet, index) => {
const option = document.createElement('option');
option.value = index;
option.textContent = sheet; // 保持原始名称
sheetSelect1.appendChild(option);
});
// 添加选项到第二个下拉框（保持原始名称，包括空格）
sheets2.forEach((sheet, index) => {
const option = document.createElement('option');
option.value = index;
option.textContent = sheet; // 保持原始名称
sheetSelect2.appendChild(option);
});
// 设置默认选中第一项
sheetSelect1.selectedIndex = 0;
sheetSelect2.selectedIndex = 0;
// 设置默认表头行
if (headerRowInput1) {
headerRowInput1.value = 1;
}
if (headerRowInput2) {
headerRowInput2.value = 1;
}
// 更新全局变量
currentSheetIndex1 = 0;
currentSheetIndex2 = 0;
headerRow1 = 1;
headerRow2 = 1;
referenceColumn1 = 0;
referenceColumn2 = 0;
console.log('Sheet selectors prepared, triggering updateReferenceColumnOptions');
// 显示工作表选择器
sheetSelector.style.display = 'block';
// 更新参照列选项
setTimeout(updateReferenceColumnOptions, 100);
console.log('=== prepareSheetSelector completed ===');
}
[JavaScript] 纯文本查看 复制代码 // 更新参照列选项
function updateReferenceColumnOptions() {
console.log('=== updateReferenceColumnOptions called ===');
const referenceColumnSelect1 = document.getElementById('reference-column1');
const referenceColumnSelect2 = document.getElementById('reference-column2');
const sheetSelect1 = document.getElementById('sheet-select1');
const sheetSelect2 = document.getElementById('sheet-select2');
const headerRowInput1 = document.getElementById('header-row1');
const headerRowInput2 = document.getElementById('header-row2');
console.log('DOM Elements:', {
referenceColumnSelect1: referenceColumnSelect1 ? 'Found' : 'Not found',
referenceColumnSelect2: referenceColumnSelect2 ? 'Found' : 'Not found',
sheetSelect1: sheetSelect1 ? 'Found' : 'Not found',
sheetSelect2: sheetSelect2 ? 'Found' : 'Not found',
headerRowInput1: headerRowInput1 ? 'Found' : 'Not found',
headerRowInput2: headerRowInput2 ? 'Found' : 'Not found'
});
// 确保所有元素可见
ensureElementsVisible();
// 确保两个文件都已选择且有工作表
if (!file1Workbook || !file2Workbook) {
console.log('File workbooks not loaded:', { file1Workbook: !!file1Workbook, file2Workbook: !!file2Workbook });
return;
}
// 检查工作表选择器是否存在
if (!sheetSelect1 || !sheetSelect2) {
console.log('Sheet selectors not found');
return;
}
// 如果工作表选择器还没有选项，稍后再试
if (sheetSelect1.options.length === 0 || sheetSelect2.options.length === 0) {
console.log('Sheet selectors have no options, retrying...', {
sheet1Options: sheetSelect1.options.length,
sheet2Options: sheetSelect2.options.length
});
// 延迟执行，等待工作表选项加载完成
setTimeout(updateReferenceColumnOptions, 100);
return;
}
console.log('Sheet selectors have options:', {
sheet1Options: sheetSelect1.options.length,
sheet2Options: sheetSelect2.options.length,
sheet1SelectedIndex: sheetSelect1.selectedIndex,
sheet2SelectedIndex: sheetSelect2.selectedIndex
});
// 安全地获取当前选择的工作表名称并去除空格
let sheetName1, sheetName2;
try {
if (sheetSelect1.selectedIndex >= 0 && sheetSelect1.selectedIndex < sheetSelect1.options.length) {
sheetName1 = sheetSelect1.options[sheetSelect1.selectedIndex].text.trim();
} else {
sheetName1 = sheetSelect1.options[0].text.trim();
sheetSelect1.selectedIndex = 0;
}
if (sheetSelect2.selectedIndex >= 0 && sheetSelect2.selectedIndex < sheetSelect2.options.length) {
sheetName2 = sheetSelect2.options[sheetSelect2.selectedIndex].text.trim();
} else {
sheetName2 = sheetSelect2.options[0].text.trim();
sheetSelect2.selectedIndex = 0;
}
} catch (error) {
console.error('Error getting sheet names:', error);
return;
}
console.log('Attempting to get sheets:', { sheetName1, sheetName2 });
console.log('Available sheet names in workbooks:', {
file1SheetNames: file1Workbook.SheetNames,
file2SheetNames: file2Workbook.SheetNames
});
// 检查工作表是否存在（使用trim处理）
let actualSheetName1 = null;
let actualSheetName2 = null;
// 查找匹配的工作表名称（忽略空格差异）
for (const name of file1Workbook.SheetNames) {
if (name.trim() === sheetName1) {
actualSheetName1 = name;
break;
}
}
for (const name of file2Workbook.SheetNames) {
if (name.trim() === sheetName2) {
actualSheetName2 = name;
break;
}
}
if (!actualSheetName1) {
console.log('Sheet 1 not found in workbook:', sheetName1);
console.log('Available sheets in file1Workbook:', file1Workbook.SheetNames);
return;
}
if (!actualSheetName2) {
console.log('Sheet 2 not found in workbook:', sheetName2);
console.log('Available sheets in file2Workbook:', file2Workbook.SheetNames);
return;
}
const sheet1 = file1Workbook.Sheets[actualSheetName1];
const sheet2 = file2Workbook.Sheets[actualSheetName2];
console.log('Selected sheets:', { actualSheetName1, actualSheetName2 });
if (!sheet1 || !sheet2) {
console.log('Sheets not found or invalid');
return;
}
// 转换为JSON格式
const json1 = XLSX.utils.sheet_to_json(sheet1, { header: 1 });
const json2 = XLSX.utils.sheet_to_json(sheet2, { header: 1 });
console.log('JSON data length:', {
json1Length: json1.length,
json2Length: json2.length
});
// 获取表头行数据（使用输入框的当前值）
let currentHeaderRow1 = headerRow1;
let currentHeaderRow2 = headerRow2;
if (headerRowInput1) {
currentHeaderRow1 = parseInt(headerRowInput1.value) || headerRow1;
}
if (headerRowInput2) {
currentHeaderRow2 = parseInt(headerRowInput2.value) || headerRow2;
}
console.log('Header rows:', { currentHeaderRow1, currentHeaderRow2 });
const headerRowIndex1 = currentHeaderRow1 - 1;
const headerRowIndex2 = currentHeaderRow2 - 1;
const headerRowData1 = json1[headerRowIndex1] || [];
const headerRowData2 = json2[headerRowIndex2] || [];
console.log('Header row data:', {
headerRowData1: headerRowData1,
headerRowData2: headerRowData2,
headerRowIndex1: headerRowIndex1,
headerRowIndex2: headerRowIndex2
});
// 清空现有选项
if (referenceColumnSelect1) {
referenceColumnSelect1.innerHTML = '';
}
if (referenceColumnSelect2) {
referenceColumnSelect2.innerHTML = '';
}
// 确定最大列数
const maxCols1 = headerRowData1.length;
const maxCols2 = headerRowData2.length;
console.log('Max columns:', { maxCols1, maxCols2 });
// 添加列选项，使用表头名称作为显示文本
if (referenceColumnSelect1) {
for (let i = 0; i < maxCols1; i++) {
const option = document.createElement('option');
option.value = i;
const headerName = headerRowData1[i];
if (headerName !== undefined && headerName !== '') {
option.textContent = `${String.fromCharCode(65 + i)} - ${headerName}`;
} else {
option.textContent = String.fromCharCode(65 + i);
}
referenceColumnSelect1.appendChild(option);
}
// 默认选择第一列
if (referenceColumnSelect1.options.length > 0) {
referenceColumnSelect1.selectedIndex = 0;
}
console.log('Reference column 1 options count:', referenceColumnSelect1.options.length);
}
if (referenceColumnSelect2) {
for (let i = 0; i < maxCols2; i++) {
const option = document.createElement('option');
option.value = i;
const headerName = headerRowData2[i];
if (headerName !== undefined && headerName !== '') {
option.textContent = `${String.fromCharCode(65 + i)} - ${headerName}`;
} else {
option.textContent = String.fromCharCode(65 + i);
}
referenceColumnSelect2.appendChild(option);
}
// 默认选择第一列
if (referenceColumnSelect2.options.length > 0) {
referenceColumnSelect2.selectedIndex = 0;
}
console.log('Reference column 2 options count:', referenceColumnSelect2.options.length);
}
console.log('=== updateReferenceColumnOptions completed ===');
}
|
免费评分
-
查看全部评分
|
[复制链接]
发表于 2025-12-1 10:35
发表于 2025-12-1 13:07
