如题，使用wps表格自带的JSA工具，调用百度的接口，实现发票的自动识别和统计。
文档里面附了我自己的百度key，供调试使用。大量调用的请自行申请一个key，免费的额度基本够小企业使用了。
// Excel中今天的数字
function today() {
let 日期 = new Date();
let 起始日期 = new Date(1900, 0, 0);
let 时间差 = 日期 - 起始日期;
let 天数 = Math.floor(时间差 / (1000 * 60 * 60 * 24)) + 1; // 加1是因为Excel中1900-01-01为1
return 天数;
}
// 选择文件夹
function getFilePath() {
let fd = Application.FileDialog(msoFileDialogFilePicker);
fd.Filters.Clear();
fd.Filters.Add('Excel文件', '*.pdf');
fd.InitialFileName = ActiveWorkbook.Path;
fd.Title = '请选择发票文件';
let rs = fd.Show()
if (rs != -1) {
alert('请选择发票文件！')
return false;
}
let paths = [...fd.SelectedItems];
Application.Cursor = xlDefault;
return paths;
}
// 格式调整
function formatter(sh) {
sh.Range("1:1").HorizontalAlignment = xlCenter;
sh.Range("a:a").NumberFormat = "@";
sh.Range("a:a").ColumnWidth = 21;
sh.Range("b:b").NumberFormat = "YYYY/MM/DD";
sh.Range("b:b").ColumnWidth = 11;
sh.Range("c:d").ColumnWidth = 30;
sh.Range("e:f").ColumnWidth = 23;
sh.Range("g:h,k:k").ColumnWidth = 5;
sh.Range("i:j,l:m").NumberFormat = "###,##0.00";
sh.Range("i:j,l:m").ColumnWidth = 15;
}
// 获取 access_token 的函数
async function getAccessToken() {
if (Range("b4").Value2 > today()) {
return Range("b3").Value2;
} else {
let API_KEY = Range("b1").Value2;
let SECRET_KEY = Range("b2").Value2;
const tokenUrl = `https://aip.baidubce.com/oauth/2.0/token?grant_type=client_credentials&client_id=${API_KEY}&client_secret=${SECRET_KEY}`;
try {
const response = await fetch(tokenUrl);
const data = await response.json();
const accessToken = data.access_token;
const expiresIn = data.expires_in;
Range("b3").Value2 = accessToken;
Range("b4").Value2 = today() + expiresIn / 60 / 60 / 24;
return accessToken;
} catch (error) {
p('获取 access_token 失败:', error);
return null;
}
}
}
// 识别图片，返回json
async function callVATInvoiceOCR_image(imageBase64) {
const accessToken = await getAccessToken();
if (!accessToken) {
return;
}
const ocrUrl = `https://aip.baidubce.com/rest/2.0/ocr/v1/vat_invoice?access_token=${accessToken}`;
const headers = {
'Content-Type': 'application/x-www-form-urlencoded'
};
const body = `image=${encodeURIComponent(imageBase64)}`;
try {
const response = await fetch(ocrUrl, {
method: 'POST',
headers: headers,
body: body
});
const data = await response.json();
return data;
} catch (error) {
p('调用增值税发票识别 API 失败:', error);
return null;
}
}
// 识别PDF，返回json
async function callVATInvoiceOCR_pdf(pdfBase64) {
const accessToken = await getAccessToken();
if (!accessToken) {
return;
}
const ocrUrl = `https://aip.baidubce.com/rest/2.0/ocr/v1/vat_invoice?access_token=${accessToken}`;
const headers = {
'Content-Type': 'application/x-www-form-urlencoded'
};
const body = `pdf_file=${encodeURIComponent(pdfBase64)}`;
try {
const response = await fetch(ocrUrl, {
method: 'POST',
headers: headers,
body: body
});
const data = await response.json();
return data;
} catch (error) {
p('调用增值税发票识别 API 失败:', error);
return null;
}
}
// 测试图片识别
async function test_image() {
// 读取图片文件
var img1 = FileSystem.ReadFileAsArrayBuffer(Env.GetDesktopPath() + '/工作空间/1.png');
img1 = Array.from(img1).map(x => String.fromCharCode(x)).join("");
const b64img = encodeURI(base64.btoa(img1));
// 识别
let data = await callVATInvoiceOCR_image(b64img);
}
// 测试PDF识别
async function test_pdf() {
// 读取PDF文件
var pdf = FileSystem.ReadFileAsArrayBuffer(Env.GetDesktopPath() + '/工作空间/1.pdf');
pdf = Array.from(pdf).map(x => String.fromCharCode(x)).join("");
const b64pdf = base64.btoa(pdf);
// 识别
let data = await callVATInvoiceOCR_pdf(b64pdf);
}
// PDF识别主函数
async function 发票识别() {
try {
Application.ScreenUpdating = false;
// 获取文件路径
const pdfPaths = getFilePath();
if (!getFilePath) {
return;
} else {
var pdfExecuted = [];
}
// 逐个处理PDF文件
const results = [];
for (const pdfPath of pdfPaths) {
// 读取文件并转换为Base64
const pdfBuffer = await FileSystem.ReadFileAsArrayBuffer(pdfPath);
const pdfArray = Array.from(pdfBuffer);
const pdfString = pdfArray.map(num => String.fromCharCode(num)).join("");
const base64Pdf = base64.btoa(pdfString);
// 调用OCR识别API
const result = await callVATInvoiceOCR_pdf(base64Pdf);
if (!result.error_code) {
results.push(result);
pdfExecuted.push([pdfPath.split(/[\\/]/).at(-1)]);
} else {
pdfExecuted.push(["Error:" + pdfPath.split(/[\\/]/).at(-1)]);
}
}
// 数据处理
let data = [];
data.push(["发票号码", "开票日期", "销售方", "购买方", "项目名称", "规格型号", "单位", "数量", "单价", "金额", "税率", "税额", "价税合计"]);
for (result of results) {
let n = result.words_result.CommodityName.length;
for (i = 0; i < n; i++) {
let dataI = [];
dataI.push(result.words_result.InvoiceNum);
dataI.push(result.words_result.InvoiceDate);
dataI.push(result.words_result.SellerName);
dataI.push(result.words_result.PurchaserName);
dataI.push(result.words_result.CommodityName[i].word);
if (result.words_result.CommodityType[i]) {
dataI.push(result.words_result.CommodityType[i].word);
} else {
dataI.push("");
}
if (result.words_result.CommodityUnit[i]) {
dataI.push(result.words_result.CommodityUnit[i].word);
} else {
dataI.push("");
}
if (result.words_result.CommodityNum[i]) {
dataI.push(result.words_result.CommodityNum[i].word);
} else {
dataI.push("");
}
if (result.words_result.CommodityPrice[i]) {
dataI.push(result.words_result.CommodityPrice[i].word);
} else {
dataI.push("");
}
dataI.push(result.words_result.CommodityAmount[i].word);
dataI.push(result.words_result.CommodityTaxRate[i].word);
dataI.push(result.words_result.CommodityTax[i].word);
dataI.push(Number(result.words_result.CommodityAmount[i].word) + Number(result.words_result.CommodityTax[i].word));
data.push(dataI);
}
}
// 输出（导入页）
Range("j:k").ClearContents();
Range("j1").Value2 = "发票列表";
Range("j2").Resize(pdfExecuted.length, 1).Value2 = pdfExecuted;
// 输出（发票页）
let sh = Sheets.Add(undefined, Sheets.Item(Sheets.Count));
sh.Name = "发票" + Date.now().toString(36).slice(4); // 随机命名
formatter(sh); // 格式调整
sh.Range("a1").Resize(data.length, 13).Value2 = data; // 填入数据
// 结束
Sheets.Item("导入").Select();
Application.ScreenUpdating = true;
MsgBox("导入完成！");
} catch (error) {
Application.ScreenUpdating = true;
p("发票识别过程中出错:", error);
throw error;
}
}
// 汇总数据更新函数
function 汇总数据更新() {
let sh0 = Sheets.Item("统计");
formatter(sh0);
sh0.Columns("a:m").ClearContents();
sh0.Columns("a:m").Hidden = true;
let title = ["发票号码", "开票日期", "销售方", "购买方", "项目名称", "规格型号", "单位", "数量", "单价", "金额", "税率", "税额", "价税合计"];
sh0.Range("a1").Resize(1, 13).Value2 = title;
let r = 2;
for (sh of Sheets) {
if (/发票.*/.test(sh.Name)) {
n = sh.Range("a9999").End(xlUp).Row - 1;
if (n > 0) {
sh0.Range("a" + r).Resize(n, 13).Value2 = sh.Range("a2").Resize(n, 13).Value2;
}
r += n;
}
}
r -= 1;
// 数据透视表
if (!sh0.Range("a2")()) {
sh0.UsedRange.ClearContents();
return 0;
}
let pvtCache = ActiveWorkbook.PivotCaches().Create(xlDatabase, sh0.Range("a1").Resize(r, 13), xlPivotTableVersion15);
if (sh0.PivotTables().Count === 0) {
let pvtTable = pvtCache.CreatePivotTable(sh0.Range("o5"), "pivot1");
pvtTable.RowAxisLayout(xlTabularRow);
pvtTable.RepeatAllLabels(xlRepeatLabels);
pvtTable.AddFields(["销售方", "发票号码", "项目名称"]);
pvtTable.AddDataField(pvtTable.PivotFields("价税合计"));
for (let pivotField of pvtTable.RowFields()) {
pivotField.Subtotals(1, false);
}
for (let pivotField of pvtTable.DataFields()) {
if (["单价", "金额", "税额", "价税合计"].includes(pivotField.SourceName)) {
pivotField.NumberFormat = "###,##0.00";
}
}
} else {
let pvtTable = sh0.PivotTables(1);
pvtTable.ChangePivotCache(pvtCache);
}
}
function Application_SheetActivate(Sh)
{
if(Sh.Name === "统计") 汇总数据更新();
}
// 打印调试工具
function p(x, ...rest) {
// x是打印内容
let regex = /\[object .*\]/
let out = "";
if (x === null) {
console.log("null");
for (let i in rest) { p(rest[i]); }
return;
} else if(x === undefined) {
console.log("undefined");
for (let i in rest) { p(rest[i]); }
return;
} else if(typeof(x) === "number" && isNaN(x)) {
console.log("NaN");
for (let i in rest) { p(rest[i]); }
return;
} else if (typeof(x) === "string" || typeof(x) === "number" || typeof(x) === "boolean") {
out = x;
} else if ("toString" in x && !regex.test(x.toString()) && !Array.isArray(x)) {
out = x.toString();
} else {
out = JSON.stringify(x);
}
if (out.length > 20) {
try {
out = JSON.stringify(JSON.parse(out), null, "\t");
} catch {}
}
console.log(out);
for (let i in rest) { p(rest[i]); }
}
// base64转码工具
class base64 {
static btoa(str) {
const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=';
let output = '';
for (let block = 0, charCode, i = 0, map = chars;
str.charAt(i | 0) || (map = '=', i % 1);
output += map.charAt(63 & block >> 8 - i % 1 * 8)) {
charCode = str.charCodeAt(i += 3 / 4);
if (charCode > 0xFF) {
throw new Error("'btoa' failed: The string to be encoded contains characters outside of the Latin1 range.");
}
block = block << 8 | charCode;
}
return output;
}
static atob(str) {
const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=';
str = String(str).replace(/[=]+$/, '');
if (str.length % 4 === 1) {
throw new Error("'atob' failed: The string to be decoded is not correctly encoded.");
}
let output = '';
for (let bc = 0, bs = 0, buffer, i = 0;
buffer = str.charAt(i++);
~buffer && (bs = bc % 4 ? bs * 64 + buffer : buffer,
bc++ % 4) ? output += String.fromCharCode(255 & bs >> (-2 * bc & 6)) : 0
) {
buffer = chars.indexOf(buffer);
}
return output;
}
}
发表于 2026-5-22 16:31
|
发表于 2026-5-22 17:09
