油猴脚本: 查询SharePoint用户
Tags: /计算机文档/软件应用技巧/
Date Created:
在使用SharePoint.com的过程当中，我们常常需要查询某些用户的具体信息，如查看用户名对应的邮箱，方便去公司内部查看对应的人员。因为有重名的用户，光从用户名是无法看出来对应哪个用户的。此外如何想查看某个用户所有的文章，也是很麻烦的事情，本文的插件可以实现相关的功能。
如果要查看详细的SharePoint信息，可以使用 SP Insider 插件:。
利用下面的脚本，可以方便查询对应用户的详细信息：
// ==UserScript==
// @name SharePoint 用户查询
// @namespace http://tampermonkey.net/
// @version 1.4
// @license GPL-3.0-only
// @description 输入关键字查询SharePoint用户详细信息和查看该用户所有文章，方便确认作者身份。按 Ctrl+Q 激活功能。
// @author Kingron
// @match https://*.sharepoint.com/sites/*
// @match https://*.sharepoint.cn/sites/*
// @icon https://www.google.com/s2/favicons?sz=64&domain=sharepoint.com
// @run-at document-start
// @grant unsafeWindow
// @grant GM_xmlhttpRequest
// @grant GM_addStyle
// ==/UserScript==
(function() {
'use strict';
GM_addStyle(`
td.ms-vb2 {
max-width: 400px !important;
}
`);
GM_addStyle(`
.a_f_cb6f7c2e:not(.b_f_cb6f7c2e):not(.z_f_cb6f7c2e) {
margin: 0px 0 !important;
}
`);
GM_addStyle(`
.ms-list-TitleLink {
min-width: 400px !important;
}
`);
function convertToCSV(items) {
if (items.length === 0) return '';
// 获取表头
const headers = Object.keys(items[0]);
// 构建 CSV 内容
let csv = headers.join(',') + '\r\n';
items.forEach(item => {
const row = headers.map(header => {
let value = item[header] !== undefined ? item[header] : '';
// 将值转为字符串
if (value === null || value === undefined || header === 'burnDownChart') {
value = '';
} else if (typeof value === 'object') {
value = JSON.stringify(value, null, 2);
} else {
value = String(value);
}
// 处理包含特殊字符的情况：逗号、换行符、双引号
if (value.includes(',') || value.includes('\n') || value.includes('\r') || value.includes('"')) {
// 转义双引号（" -> ""）
value = value.replace(/"/g, '""');
// 用双引号包裹整个值
value = `"${value}"`;
}
return value;
});
csv += row.join(',') + '\r\n';
});
return csv;
}
function downloadCSV(csvData, filename = 'data.csv') {
const blob = new Blob([csvData], { type: 'text/csv;charset=utf-8;' });
const link = document.createElement('a');
const url = URL.createObjectURL(blob);
link.setAttribute('href', url);
link.setAttribute('download', filename);
link.style.visibility = 'hidden';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
// 监听所有资源加载
(new PerformanceObserver((list) => {
const div = document.querySelector('div[data-automation-id="personaDetails"] div.peopleName');
list.getEntries().forEach((entry) => {
if (div?.innerText.indexOf(">") < 0 && (entry.name.includes("SP.UserProfiles.") || entry.name.includes("/users/"))) {
let mail = entry.name.split("membership%7C")[1]?.split("%27")[0] || "";
if (mail === "") {
mail = entry.name.split("/users/")[1]?.split("?")[0] || ""
}
mail = decodeURIComponent(mail);
if (mail !== "") {
console.log("获取到作者信息: ", mail, "url: ", entry.name);
div.innerText += "<" + mail + ">";
return;
}
}
if (entry.name.includes("/SyncFlowInstances")) {
getWikiAuthor();
getRejectInfo();
}
});
})).observe({ entryTypes: ["resource"] });
function getUserInfo(id) {
const siteName = getSiteNameFromURL();
const apiUrl = `/sites/${siteName}/_api/web/GetUserById(${id})`;
const headers = {
"Accept": "application/json"
};
return new Promise((resolve, reject) => {
GM_xmlhttpRequest({
method: "GET",
url: apiUrl,
headers: headers,
onload: function(response) {
const data = JSON.parse(response.responseText);
resolve(data);
},
onerror: function(error) {
reject(error);
}
});
});
}
function getWikiAuthor() {
const siteName = getSiteNameFromURL();
const apiUrl = `/sites/${siteName}/_api/web/getfilebyserverrelativeurl('${window.location.pathname}')/ListItemAllFields`
const headers = {
"Accept": "application/json"
};
GM_xmlhttpRequest({
method: "GET",
url: apiUrl,
headers: headers,
onload: function(response) {
const data = JSON.parse(response.responseText);
const user = data.AuthorId;
getUserInfo(user).then(user => {
const element = document.querySelector('h1#pageTitle.ms-core-pageTitle');
if (element && user.LoginName && user.Email) {
const node = document.createElement('div');
element.appendChild(node);
node.outerHTML = `<div style="float: right;font-size:20px;position: relative;transform: translateY(50%);" title="${user.LoginName}">Author: ${user.Title}<${user.Email}></div>`;
}
}).catch(err => {
console.error("请求失败：", err);
});
const node = document.evaluate("//button[@data-automation-id='pageCommandBarStatus']//span[@class='ms-Button-label ms-ButtonShim-label']", document).iterateNext();
if (node) {
node.addEventListener('click', function() {
window.open(data.test_x0020_Approved?.Url, '_blank');
});
}
},
onerror: function(error) {
alert("请求失败！请稍后再试。");
}
});
}
function getRejectInfo() {
console.log("获取审批拒绝信息");
const labelElement = document.evaluate("//button[@data-automation-id='pageCommandBarStatus']//span[@class='ms-Button-label ms-ButtonShim-label' and text()='Rejected']", document).iterateNext();
if (!labelElement) {
console.log("未定位到 Reject 节点");
return;
}
let title = document.getElementById('title_text').innerText.trim().replaceAll("-", " ");
getRejectList().then(data => {
let firstMatch = data.find(item => item.Title.replaceAll("-", " ").includes(title));
if (!firstMatch) {
title = decodeURI(window.location.pathname.split('/').pop()).split(".")[0]?.replaceAll("-", " ");
firstMatch = data.find(item => item.Title.replaceAll("-", " ").includes(title));
}
if (firstMatch) {
labelElement.title = firstMatch.Body.replaceAll("<br>", "\n").replaceAll("<p>", "").replaceAll("</p>", "").replaceAll(":", ":");
} else {
debugger;
console.log("匹配审批拒绝信息失败");
}
});
}
function getRejectList() {
const siteName = getSiteNameFromURL();
const baseUrl = `/sites/${siteName}/_layouts/15/inplview.aspx?List={3F33E292-D971-4298-99D5-2DABF87EFFBC}&IsXslView=TRUE&IsCSR=TRUE&FilterField1=TaskOutcome&FilterValue1=Rejected`;
const headers = {
"Accept": "application/json"
};
return new Promise(async (resolve, reject) => {
try {
let allData = [];
let firstRow = 0;
let hasMoreData = true;
while (hasMoreData) {
const currentUrl = `${baseUrl}&FirstRow=${firstRow}`;
const response = await new Promise((innerResolve, innerReject) => {
GM_xmlhttpRequest({
method: "POST",
url: currentUrl,
headers: headers,
onload: function(response) {
innerResolve(response);
},
onerror: function(error) {
innerReject(error);
}
});
});
const data = JSON.parse(response.responseText);
if (data.Row && data.Row.length > 0) {
allData = allData.concat(data.Row);
}
// Check if we've reached the end of the data
if (!data.LastRow || (data.LastRow + 1) < firstRow + data.RowLimit) {
hasMoreData = false;
} else {
firstRow += data.RowLimit; // Move to the next page
}
}
resolve(allData);
} catch (error) {
reject(error);
}
});
}
function getArticleList(query) {
console.log("查询请求:", query);
const siteName = getSiteNameFromURL();
const baseUrl = (siteName === "DC3/KB") ?
`/sites/${siteName}/_api/web/GetListUsingPath(DecodedUrl='/sites/${siteName}/Shared Documents')/RenderListDataAsStream?InplaceSearchQuery=${query}`:
`/sites/${siteName}/_api/web/GetListUsingPath(DecodedUrl='/sites/${siteName}/SitePages')/RenderListDataAsStream?InplaceSearchQuery=${query}`;
const headers = {
"Accept": "application/json"
};
return new Promise(async (resolve, reject) => {
try {
let allData = [];
let hasMoreData = true;
let currentUrl = `${baseUrl}&PageFirstRow=1`
while (hasMoreData) {
console.log("分页查询:", currentUrl);
const response = await new Promise((innerResolve, innerReject) => {
GM_xmlhttpRequest({
method: "POST",
url: currentUrl,
headers: headers,
onload: function(response) {
innerResolve(response);
},
onerror: function(error) {
innerReject(error);
}
});
});
const data = JSON.parse(response.responseText);
if (data.Row && data.Row.length > 0) {
allData = allData.concat(data.Row);
}
if (data.NextHref) {
currentUrl = baseUrl + data.NextHref.replace("?", "&");
} else {
hasMoreData = false;
}
}
allData.sort((a, b) => new Date(a.Created) - new Date(b.Created));
console.log("本次查询总文章数量: ", allData.length);
if (allData.length >= 500) {
alert("查询 " + query + " 结果总数量超过500，请分为更细的粒度查询");
}
resolve(allData);
} catch (error) {
reject(error);
}
});
}
function formatDate(date) {
const y = date.getFullYear();
const m = String(date.getMonth() + 1).padStart(2, '0');
const d = String(date.getDate()).padStart(2, '0');
return `${y}-${m}-${d}`;
}
async function getAllArticleList() {
const result = [];
const startDate = new Date('2023-01-01');
const endDate = new Date(); // 当前时间
let cursor = new Date(startDate);
while (cursor < endDate) {
const sliceStart = formatDate(cursor);
// +6 个月
const next = new Date(cursor);
next.setMonth(next.getMonth() + 6);
// 防止超过当前时间
const sliceEndDate = next > endDate ? endDate : next;
const sliceEnd = formatDate(sliceEndDate);
const query = [
`Created:${sliceStart}..${sliceEnd}`,
'AND NOT ContentType:Folder'
].join(' ');
console.log(`[Slice] ${sliceStart} ~ ${sliceEnd}`);
const list = await getArticleList(query);
if (Array.isArray(list) && list.length > 0) {
result.push(...list);
}
cursor = next;
}
return result;
}
// 监听 Ctrl+Q 快捷键
window.addEventListener('keydown', function(event) {
if (event.ctrlKey && event.key === 'q') {
event.preventDefault(); // 阻止默认行为
const keyword = prompt("请输入关键字进行搜索：");
if (keyword) {
const siteName = getSiteNameFromURL(); // 获取当前 SharePoint 站点名称
console.log("SharePoint站点名称: ", siteName);
if (siteName) {
searchUsers(siteName, keyword);
} else {
alert("无法从 URL 获取 SharePoint 站点名！");
}
}
}
});
function generateQueryForCurrentQuarter() {
const now = new Date();
const year = now.getFullYear();
const month = now.getMonth();
const quarter = Math.floor(month / 3);
const startMonth = quarter * 3;
const endMonth = startMonth + 2;
const startDate = new Date(year, startMonth, 1);
const endDate = new Date(year, endMonth + 1, 0); // 本季度最后一天
const format = (d) => {
const yyyy = d.getFullYear();
const mm = String(d.getMonth() + 1).padStart(2, '0');
const dd = String(d.getDate()).padStart(2, '0');
return `${yyyy}-${mm}-${dd}`;
};
return `Created:${format(startDate)}..${format(endDate)} AND NOT ContentType:Folder`;
}
// 监听 Alt+Q 快捷键
window.addEventListener('keydown', function(event) {
if (event.altKey && event.key === 'q') {
event.preventDefault(); // 阻止默认行为
const keyword = prompt("请输入关键字进行搜索，输入 * 列出所有内容：", generateQueryForCurrentQuarter());
if (!keyword) return;
if (keyword === "*") {
getAllArticleList().then(data => {
console.log("获取到文章列表: ", data.length);
displayArticles(data);
});
} else {
getArticleList(keyword).then(data => {
console.log("获取到文章列表: ", data.length);
displayArticles(data);
});
}
}
});
// 从当前 URL 中提取 SharePoint 站点名称，支持 .com 和 .cn 域名
function getSiteNameFromURL() {
// 优先匹配 sharepoint.com/sites/xxx/bbb/.../SitePages 或 sharepoint.cn/sites/xxx/bbb/.../SitePages
let regex = /https:\/\/.*\.sharepoint\.(com|cn)\/sites\/(.*)\/(SitePages|_layouts|Lists|Shared%20Documents|SiteAssets|Pages)/;
let match = window.location.href.match(regex);
// 如果能匹配到 SitePages 结构，则返回匹配到的 xxx/bbb/... 部分
if (match) {
return match[2]; // match[2] 为 sites/后面到 /SitePages 之前的所有内容
}
regex = /https:\/\/.*\.sharepoint\.(com|cn)\/sites\/([^\/?]+)/;
match = window.location.href.match(regex);
return match ? match[2] : null;
}
function copyTable(tableElement) {
const range = document.createRange();
const selection = window.getSelection();
selection.removeAllRanges();
range.selectNode(tableElement);
selection.addRange(range);
try {
document.execCommand('copy');
alert('表格已复制到剪切板！');
} catch (err) {
alert('复制失败:' + err);
}
selection.removeAllRanges();
}
// 调用 SharePoint API 获取用户数据
function searchUsers(siteName, keyword) {
const apiUrl = `/sites/${siteName}/_api/Web/SiteUsers`;
const headers = {
"Accept": "application/json"
};
GM_xmlhttpRequest({
method: "GET",
url: apiUrl,
headers: headers,
onload: function(response) {
const data = JSON.parse(response.responseText);
const users = data.value || [];
// 过滤匹配关键字的用户
const regex = new RegExp(keyword, 'i'); // 'i' 表示不区分大小写
const filteredUsers = users.filter(user => {
return regex.test(user.Title) || regex.test(user.Email) || regex.test(user.UserPrincipalName);
});
if (filteredUsers.length === 0) {
alert("没有找到匹配的用户。");
} else {
displayResults(siteName, filteredUsers);
}
},
onerror: function(error) {
alert("请求失败！请稍后再试。");
}
});
}
// 在页面上展示搜索结果
function displayArticles(articles) {
const container = document.createElement('div');
container.innerHTML = `
<div tabindex=0 style="max-width:90%;min-width:800px;width:80%;max-height:70%;position:fixed; top:50%; left:50%; transform:translate(-50%, -50%); overflow:hidden; background-color:#fff; border:1px solid #ccc; border-radius:8px; box-shadow:0 0 10px rgba(0, 0, 0, 0.3); padding:10px; z-index: 9999; display:flex; flex-direction:column;}">
<div style="flex: 1; overflow-y: auto">
<style>
#_rl_article td { padding: 5px; border-bottom: 1px solid #ddd; }
#_rl_article a { text-decoration: none; }
</style>
<table id="_rl_article" style="width:100%; border-collapse: collapse; background-color: #fff; border: 1px solid #ddd; border-radius: 5px;">
<thead style="position: sticky; top: 0; background-color: #f8f8f8;">
<tr>
<th>文章</th>
<th width=120>作者</th>
<th width=120>编辑</th>
<th width=140>创建时间</th>
<th width=140>编辑时间</th>
<th width=50>点赞数</th>
<th width=250>提交备注</th>
<th width=80>工作流</th>
<th width=150>邮件通知</th>
</tr>
</thead>
<tbody>
${articles.map(item => `
<tr>
<td><a href="${item.FileRef}">${item.FileLeafRef}</a></td>
<td><a href="mailto:${item.Author[0]?.email}">${item.Author[0]?.title}</a></td>
<td><a href="mailto:${item.Editor[0]?.email}">${item.Editor[0]?.title}</a></td>
<td>${item.Created || ""}</td>
<td>${item.Modified}</td>
<td>${item._LikeCount || ""}</td>
<td>${item._CheckinComment}</td>
<td><a href="${item.test_x0020_Approved || ""}">${item._ModerationStatus || ""}</a></td>
<td>${item.ApproveStatus || ""}</td>
</tr>
`).join('')}
</tbody>
</table>
</div>
<div style="padding-top:10px; display: flex; align-items: center; justify-content: flex-end">按 Esc 关闭
<div id="divCounter"></div>
<button id="copyButton" style="padding: 10px 10px; background-color: #3CB371; color: white; border: none; border-radius: 5px; cursor: pointer; font-size: 14px;outline: none;">导出</button>
<button id="closeButton" style="padding: 10px 10px; background-color: #ff4d4f; color: white; border: none; border-radius: 5px; cursor: pointer; font-size: 14px;outline: none;">关闭</button>
</div>
</div>`;
document.body.appendChild(container);
document.getElementById("divCounter").innerText = "总数量: " + articles.length + " 篇 ";
const copyButton = container.querySelector('#copyButton');
copyButton.addEventListener('click', function() {
const list = [];
articles.forEach(user => {
list.push({
文章: user.FileRef,
作者: user.Author[0]?.title,
作者邮箱: user.Author[0]?.email,
编辑: user.Editor[0]?.title,
编辑邮箱: user.Editor[0]?.email,
创建时间: user.Created,
创建时间: user.Modified,
点赞数: user._LikeCount || "",
提交备注: user._CheckinComment,
审批状态: user._ModerationStatus,
邮件通知: user.ApproveStatus || "",
});
});
const csvData = convertToCSV(list);
downloadCSV(csvData);
});
const closeButton = container.querySelector('#closeButton');
closeButton.addEventListener('click', function() {
document.body.removeChild(container);
});
container.addEventListener('keydown', function(event) {
if (event.key === 'Escape') { document.body.removeChild(container); };
});
closeButton.focus();
}
// 在页面上展示搜索结果
function displayResults(site, users) {
const container = document.createElement('div');
container.innerHTML = `
<div tabindex=0 style="max-width:90%; max-height:70%;position:fixed; top:50%; left:50%; transform:translate(-50%, -50%); overflow:hidden; background-color:#fff; border:1px solid #ccc; border-radius:8px; box-shadow:0 0 10px rgba(0, 0, 0, 0.3); padding:10px; z-index: 9999; display:flex; flex-direction:column;}">
<div style="flex: 1; overflow-y: auto">
<style>
#_rl_table td { padding: 5px; border-bottom: 1px solid #ddd; }
#_rl_table a { text-decoration: none; }
</style>
<table id="_rl_table" style="width: 100%; border-collapse: collapse; background-color: #fff; border: 1px solid #ddd; border-radius: 5px;">
<thead style="position: sticky; top: 0; background-color: #f8f8f8;">
<tr>
<th>Title</th>
<th>Email</th>
<th>UserPrincipalName</th>
<th>Files</th>
<th>Wiki</th>
</tr>
</thead>
<tbody>
${users.map(user => `
<tr>
<td><a href="/sites/${site}/SitePages/Forms/AllPages.aspx?view=7&q=author:%20*&useFiltersInViewXml=0&viewpath=/sites/${site}/SitePages/Forms/AllPages.aspx&FilterField1=Editor&FilterValue1=${user.Title}&FilterField1=DocIcon&FilterValue1=aspx&FilterType1=Computed&FilterOp1=In&FilterField2=Author&FilterValue2=${user.Title}&FilterType2=User&FilterOp2=In" target="_blank">${user.Title}</a></td>
<td><a href="mailto:${user.Email}">${user.Email}</a></td>
<td><a href="${user['odata.id']}" target="_blank">${user.UserPrincipalName}</a></td>
<td><a href='/sites/${site}/_layouts/15/search.aspx/siteall?oobRefiners={"FileType":["pptx","docx","xlsx","one","pdf","video","html"]}&q=author:${user.Title}&scope=site' target="_blank">List</a></td>
<td><a href='/sites/DC3/Wiki/SitePages/Forms/AllPages.aspx?viewid=744ee345-265a-4d68-85da-89bc5f89b297&view=7&q=author:*&useFiltersInViewXml=0&FilterField1=Author&FilterValue1=${user.Title}&FilterType1=User&viewpath=/sites/DC3/Wiki/SitePages/Forms/AllPages.aspx&FilterField2=DocIcon&FilterValue2=aspx&FilterType2=Computed&FilterOp2=In' target="_blank">Wiki</a></td>
</tr>
`).join('')}
</tbody>
</table>
</div>
<div style="padding-top:10px; display: flex; align-items: center; justify-content: flex-end">按 Esc 关闭
<button id="copyButton" style="padding: 10px 10px; background-color: #3CB371; color: white; border: none; border-radius: 5px; cursor: pointer; font-size: 14px;outline: none;">复制表格</button>
<button id="closeButton" style="padding: 10px 10px; background-color: #ff4d4f; color: white; border: none; border-radius: 5px; cursor: pointer; font-size: 14px;outline: none;">关闭</button>
</div>
</div>
`;
document.body.appendChild(container);
const copyButton = container.querySelector('#copyButton');
copyButton.addEventListener('click', function() {
copyTable(document.getElementById('_rl_table'));
});
const closeButton = container.querySelector('#closeButton');
closeButton.addEventListener('click', function() {
document.body.removeChild(container);
});
container.addEventListener('keydown', function(event) {
if (event.key === 'Escape') { document.body.removeChild(container); };
});
closeButton.focus();
}
})();
使用方法：
打开 sharepoint.com 或 sharepoint.cn 对应的网址，然后 按 Ctrl+Q，输入关键字，即可查询。点击用户名，可以打开新窗口，查看该用户创建的所有文章。
按Alt+Q，输入类似查询内容： Created:2026-04-01..2026-06-30 AND NOT ContentType:Folder 即可查询指定范围内容的所有文章列表。
由于SharePoint并没有提供查询全部内容的API，所以目前分页查询，请耐心等待分页分批次查询完成。
运行效果：
SharePoint并没有提供类似Redmine的宏指令，能够自动构建所有内容的树形结构目录，因此我们如果要建立树形目录结构，可以用Python脚本，把导出的数据转换为类似下面的目录结构：
导出的数据格式类似下面：
文章,作者,作者邮箱,编辑,编辑邮箱,创建时间,点赞数,提交备注,审批状态,邮件通知
/sites/xxx/Wiki/SitePages/Database/page1.aspx,author1,author1@abc.com,author1,author1@abc.com,5/22/2023 12:50,,,Approved,
/sites/xxx/Wiki/SitePages/Misc/page2.aspx,author2,author2@abc.com,author2,author2@abc.com,9/18/2025 14:22,,,Approved,Approved
/sites/xxx/Wiki/SitePages/Office/page3.aspx,author3,author3@abc.com,author3,author3@abc.com,4/10/2023 10:55,,,Approved,生成目录的 Python 代码 wiki.py：
import csv
from pathlib import Path
from collections import defaultdict
from typing import Dict, List, Tuple
import datetime
def parse_articles_from_csv(csv_file: str) -> List[Dict]:
"""从CSV文件中读取所有文章信息"""
articles = []
with open(csv_file, 'r', encoding='utf-8') as f:
reader = csv.DictReader(f)
for row in reader:
if row.get('文章', '').strip():
path = row['文章'].strip()
likes_str = row.get('点赞数', '').strip()
try:
likes = int(likes_str) if likes_str else 0
except ValueError:
likes = 0
articles.append({
'path': path,
'title': Path(path).stem,
'author': row.get('作者', '').strip(),
'author_email': row.get('作者邮箱', '').strip(),
'create_time': row.get('创建时间', '').strip(),
'likes': likes
})
return articles
def calculate_author_stats(articles: List[Dict]) -> List[Tuple]:
"""计算作者统计数据"""
author_stats = defaultdict(lambda: {'article_count': 0, 'total_likes': 0})
for article in articles:
author = article['author']
if author: # 只统计有作者信息的文章
author_stats[author]['article_count'] += 1
author_stats[author]['total_likes'] += article['likes']
# 转换为列表并排序：先按文章数降序，再按点赞数降序
stats_list = []
for author, stats in author_stats.items():
stats_list.append({
'author': author,
'article_count': stats['article_count'],
'total_likes': stats['total_likes']
})
# 排序规则：文章数 > 点赞数
sorted_stats = sorted(stats_list,
key=lambda x: (-x['article_count'], -x['total_likes']))
return sorted_stats
def generate_author_table(author_stats: List[Dict]) -> str:
"""生成作者统计表格HTML"""
if not author_stats:
return ""
# 只取前100名
top_authors = author_stats[:100]
table_html = """
<h2>👑 作者贡献排行榜 - Top 100</h2>
<table border="1" cellpadding="5" cellspacing="0" style="border-collapse: collapse; margin-bottom: 20px;">
<thead>
<tr style="background-color: #f2f2f2;">
<th width="60">名次</th>
<th>作者</th>
<th width="80">文章数</th>
<th width="80">点赞总数</th>
</tr>
</thead>
<tbody>
"""
medals = ['🥇', '🥈', '🥉', '🏅', '🏅', '🏅', '🏅', '🏅', '🏅', '🏅']
for i, author_info in enumerate(top_authors):
medal = medals[i] if i < len(medals) else '🏅'
# 每行交替背景色
bg_color = "#ffffff" if i % 2 == 0 else "#f9f9f9"
table_html += f"""
<tr style="background-color: {bg_color};">
<td align="center"><b>{medal}</b></td>
<td>{author_info['author']}</td>
<td align="center">{author_info['article_count']}</td>
<td align="center">{author_info['total_likes']}</td>
</tr>
"""
table_html += """
</tbody>
</table>
<hr style="margin: 20px 0;">
"""
return table_html
def format_time(time_str: str) -> str:
"""格式化时间字符串"""
if not time_str:
return ""
time_formats = [
'%Y-%m-%d %H:%M:%S',
'%Y-%m-%d %H:%M',
'%Y/%m/%d %H:%M:%S',
'%Y/%m/%d %H:%M',
'%d/%m/%Y %H:%M:%S',
'%d/%m/%Y %H:%M',
'%m/%d/%Y %H:%M:%S',
'%m/%d/%Y %H:%M'
]
for fmt in time_formats:
try:
dt = datetime.datetime.strptime(time_str, fmt)
return dt.strftime('%Y-%m-%d %H:%M')
except ValueError:
continue
return time_str
def get_absolute_url(relative_path: str) -> str:
"""将相对路径转换为绝对URL"""
return f"https://xxx.sharepoint.com{relative_path}"
def build_tree_structure(articles: List[Dict]) -> Dict:
"""构建树结构，所有根目录文件都作为首页的子节点"""
# 查找首页文章
home_article = None
other_root_articles = []
tree = defaultdict(lambda: {'children': defaultdict(dict), 'articles': [], 'is_dir': True})
for article in articles:
path = article['path']
if not path.startswith('/sites/xxx/Wiki/SitePages/'):
continue
# 移除基础路径
rel_path = path[len('/sites/xxx/Wiki/SitePages/'):]
if '/' not in rel_path:
# 根目录文件
if '首页.aspx' in path:
# 找到首页
home_article = article
else:
# 其他根目录文件
other_root_articles.append(article)
else:
# 有目录结构的文件
parts = rel_path.split('/')
filename = parts[-1]
dir_parts = parts[:-1]
# 构建目录树
current = tree
for i, dir_name in enumerate(dir_parts):
if dir_name not in current:
current[dir_name] = {
'children': defaultdict(lambda: {'children': defaultdict(dict), 'articles': [], 'is_dir': True}),
'articles': [],
'is_dir': True,
'full_path': '/sites/xxx/Wiki/SitePages/' + '/'.join(dir_parts[:i+1]) + '/'
}
if i == len(dir_parts) - 1:
# 最后一层目录，添加文章
current[dir_name]['articles'].append(article)
else:
# 继续深入
current = current[dir_name]['children']
# 创建首页目录结构
home_structure = {
'children': defaultdict(lambda: {'children': defaultdict(dict), 'articles': [], 'is_dir': True}),
'articles': [],
'is_dir': True,
'full_path': '/sites/xxx/Wiki/SitePages/首页.aspx' # 首页本身的路径
}
# 将其他根目录文件作为首页的子节点
for article in other_root_articles:
home_structure['articles'].append(article)
# 将首页结构添加到树中（作为第一个元素）
sorted_tree = {}
# 首页总是第一个
sorted_tree['首页'] = home_structure
# 然后添加其他目录
for dir_name, dir_data in sorted(tree.items(), key=lambda x: x[0].lower()):
sorted_tree[dir_name] = dir_data
return sorted_tree, home_article
def generate_article_html(article: Dict) -> str:
"""生成单个文章的HTML"""
title = article['title']
author = article['author']
author_email = article['author_email']
create_time = format_time(article['create_time'])
likes = article.get('likes', 0)
# 构建文章链接
article_url = get_absolute_url(article['path'])
# 构建作者邮箱链接
author_html = ""
if author_email:
author_html = f'<a href="mailto:{author_email}">{author}</a>'
elif author:
author_html = author
# 构建时间显示（放在最前面）
time_html = f"{create_time}: " if create_time else ""
# 构建点赞数显示
likes_html = ""
try:
likes_int = int(likes)
if likes_int > 10:
likes_html = f", 💖{likes_int}"
elif likes_int > 5:
likes_html = f", ❤️🔥{likes_int}"
elif likes_int > 0:
likes_html = f", 👍{likes_int}"
except (ValueError, TypeError):
pass
# 组合所有部分
parts = []
# 1. 时间（如果有）
if time_html:
parts.append(time_html)
# 2. 文章标题链接
parts.append(f'<a href="{article_url}">{title}</a>')
# 3. 作者（如果有）
if author_html:
parts.append(f' @{author_html}')
# 4. 点赞数（如果有）
if likes_html:
parts.append(likes_html)
return ''.join(parts)
def generate_directory_html(dir_name: str, dir_data: Dict, level: int = 1) -> str:
"""递归生成目录的HTML"""
indent = " " * level
html_parts = []
# 如果是首页，特殊处理
if dir_name == '首页':
# 首页本身也是一个文章链接
if 'full_path' in dir_data:
home_url = get_absolute_url(dir_data['full_path'])
dir_link = f'<a href="{home_url}">{dir_name}</a>'
else:
dir_link = dir_name
else:
# 普通目录链接
if 'full_path' in dir_data:
dir_url = get_absolute_url(dir_data['full_path'])
dir_link = f'<a href="{dir_url}">{dir_name}</a>'
else:
dir_link = dir_name
html_parts.append(f'{indent}<li>{dir_link}')
# 如果有子目录或文章，添加ul
has_content = dir_data['articles'] or dir_data['children']
if has_content:
html_parts.append(f'{indent}<ul>')
# 1. 先处理当前目录下的文章
if dir_data['articles']:
# 对文章按标题排序
sorted_articles = sorted(dir_data['articles'], key=lambda x: format_time(x['create_time']))
for article in sorted_articles:
article_html = generate_article_html(article)
html_parts.append(f'{indent} <li>{article_html}</li>')
# 2. 再处理子目录（递归）
if dir_data['children']:
# 对子目录按名称排序
sorted_children = sorted(dir_data['children'].items(), key=lambda x: x[0].lower())
for child_name, child_data in sorted_children:
child_html = generate_directory_html(child_name, child_data, level + 2)
html_parts.append(child_html)
if has_content:
html_parts.append(f'{indent}</ul>')
html_parts.append(f'{indent}</li>')
return '\n'.join(html_parts)
def generate_html(tree: Dict, home_article: Dict, author_stats: List[Dict], count: int) -> str:
"""生成完整的HTML"""
html_template = """<!DOCTYPE html>
<html>
<head>
<title>Wiki文章目录</title>
<meta charset="utf-8">
</head>
<body>
{author_table}
<h2>📚 文章目录 - 共 {count} 篇</h2>
<ul>
{content}
</ul>
</body>
</html>"""
# 生成作者表格
author_table = generate_author_table(author_stats)
# 生成目录结构
html_parts = []
for dir_name, dir_data in tree.items():
dir_html = generate_directory_html(dir_name, dir_data, 1)
html_parts.append(dir_html)
content = '\n'.join(html_parts)
return html_template.format(author_table=author_table, content=content, count=count)
def main():
# 输入和输出文件
input_csv = 'wiki.csv'
output_html = 'wiki.html'
# 读取CSV文件
print(f"读取CSV文件: {input_csv}")
articles = parse_articles_from_csv(input_csv)
print(f"找到 {len(articles)} 篇文章")
# 计算作者统计数据
print("计算作者统计数据...")
author_stats = calculate_author_stats(articles)
print(f"\n作者统计（前10名）:")
print("名次 | 作者 | 文章数 | 点赞总数")
print("-" * 40)
medals = ['🥇', '🥈', '🥉', '4', '5', '6', '7', '8', '9', '10']
for i, author_info in enumerate(author_stats[:10]):
medal = medals[i] if i < len(medals) else str(i+1)
print(f"{medal} | {author_info['author']} | {author_info['article_count']} | {author_info['total_likes']}")
print(f"\n总共 {len(author_stats)} 位作者")
# 构建树结构
print("\n构建目录树...")
tree, home_article = build_tree_structure(articles)
if home_article:
print(f"找到首页: {home_article['title']}")
else:
print("警告: 未找到首页.aspx文件")
# 统计首页下的文章
if '首页' in tree:
home_articles_count = len(tree['首页']['articles'])
print(f"首页下有 {home_articles_count} 个根目录文件")
print(f"目录总数: {len(tree)} 个")
# 生成HTML
print("\n生成HTML...")
html_content = generate_html(tree, home_article, author_stats, len(articles))
# 保存到文件
with open(output_html, 'w', encoding='utf-8') as f:
f.write(html_content)
print(f"HTML文件已生成: {output_html}")
if __name__ == '__main__':
main()