做设备巡检表的时候我吃过一次亏几十台设备的编号、型号、安装位置要生成二维码贴到机柜上一开始我老老实实打开网页二维码生成器一条一条复制粘贴生成一张图再另存回到Excel里再拖进去折腾了一下午。后来我把这个流程整个搬进Excel里用VBA写了一个一键宏选中单元格点一下按钮炫彩二维码直接出现在旁边内容、尺寸、配色全部可调批量的话直接拖到整列。这篇就把完整方案和代码放出来适合经常和Excel、二维码打交道的运维、仓库、行政还有做物料追溯和资产盘点的朋友。1. 先想清楚Excel里生成二维码到底有没有必要1.1 哪些场景真正值得用Excel生成二维码不是所有场景都适合在Excel里做二维码真正合适的场景有两个特点数据本身就在Excel里且二维码内容很短、很结构化。我实际处理过的场景包括设备巡检标签机柜编号加巡检网址一台机柜贴一张仓库物料追溯料号加批次号加有效期每次来货生成一批活动签到姓名加手机号预填到签到系统固定资产盘点资产编号加责任人加存放位置。这些场景的共同点是数据一列一列摆在那里用网页工具反而要来回切换窗口效率很低。还有一类场景是数据会变。比如批次号更新、物料有效期变更如果每次重新打开网页生成再贴回Excel很容易漏改、贴错。把生成逻辑放在Excel里之后改一行数据重新点一下按钮对应图片自动更新旧图也不会残留。1.2 网页工具和本地工具都解决不了的问题网页工具最大的问题不是功能不行而是和Excel脱节。你要复制内容到网页下载图片再回到Excel插入一套流程少说一分钟。几十条下来眼睛和手都遭罪。而且网页工具没有数据校验能力内容填错了它不提醒只有扫描的时候才发现。Excel方案的优势是可以和表格本身联动。比如先对B列做多条件筛选只保留今天需要入库的物料再一键生成或者用SUMIFS统计生成数量确认没有漏甚至可以用条件格式高亮重复项避免同一批号被生成两次。这些是网页工具完全给不了的能力。1.3 方案的边界什么时候不要用这套方法也得说实话Excel生成二维码不是银弹。二维码内容如果超过两三百个字符比如塞一长串JSON配置二维码会变得非常密集打印出来基本扫不动。中文内容本身占用的字符空间比英文多更要注意控制长度。如果你的数据属于高度敏感类型并且生产环境完全离线隔离那在线接口方案就别用了后面的内容只做技术参考即可。离线环境建议改用本地编码方案我在第二部分会讲到路径虽然代码复杂但至少方向是对的。2. 三种主流实现方式我为什么选了接口彩色化2.1 单元格填充理论上最可控实际成本最高最早我试过单元格填充法。思路很简单二维码本质上是一个由深浅模块组成的矩阵拿到0和1的矩阵数据后用Excel单元格逐个填充背景色黑色模块填深色白色模块填浅色再调节行高列宽让格子变成正方形一张二维码就画出来了。这个方法的好处是绝对可控每个模块是什么颜色都由你说了算不但能做渐变还能做出渐变色、彩虹色、品牌色甚至可以在二维码中央嵌入Logo。问题在于VBA里要实现完整的QR编码标准是一件非常痛苦的事数据分组、Reed-Solomon纠错、掩码计算、版本选择几百行代码打底调试周期以周计。除非你有现成的编码库否则我不建议从零手写。2.2 本地JS库离线能力最强维护成本也不低第二种方案是本地编码库。原理是在Excel里挂一个WebBrowser控件加载一段内嵌的HTML页面页面里放一个编译好的JavaScript二维码库比如qrcode.js。用JS库生成矩阵后可以直接在页面里画出任意颜色的二维码再通过截图或导出图片的方式放回Excel。这个方案支持完全离线使用效果上限也最高想做多炫都行。但实际踩坑也不少不同Office版本对WebBrowser控件的支持程度不一样有的机器上控件显示空白整个JS库需要嵌入工作簿文件体积会变大HTML页面和Excel之间的数据传递也要处理编码问题。整套搞下来适合做一个长期固定的工具不适合临时需求。2.3 在线接口彩色化效果和代价的平衡点我最后采用的是在线接口方案。二维码在线生成接口有很多比如qrserver.com、goqr.me核心用法是在URL里传参数接口直接返回一张图片。关键是接口支持颜色参数可以指定二维码前景色和背景色直接在服务端生成彩色二维码PNG然后通过VBA下载并插入Excel。有人可能担心在线接口不稳定。实际测试下来这类公开接口的可用性整体不错但确实会受网络环境影响所以我在设计里保留了本地缓存机制生成的临时图片先放在本机临时目录插入Excel后再清理避免反复请求。如果你的网络环境特殊比如公司内网限制了外部访问可以把接口地址换成你们自己的服务代码逻辑完全不用改。3. 核心原理与参数彩色二维码为什么能扫出来3.1 二维码怎么读三个定位角、静区和容错想要炫彩二维码能顺利被扫出来得先把二维码的读取逻辑弄清楚。手机扫码时首先要找到三个定位角就是二维码左上、右上、左下角那种回字形大方块找到它们才能确定方向和位置。其次二维码周围至少要留出一圈空白区域叫静区没有静区扫码器会分不清模块边界。最后二维码有四级容错等级L、M、Q、H分别能容忍大约7%、15%、25%、30%的图形损坏或遮挡。在线接口的URL里一般支持ecc参数我推荐用M或H。普通屏幕显示用M足够打印标签、贴纸这类可能被磨损的场景用H更稳。容错等级越高同样的内容生成的二维码模块越密这是需要接受的代价。3.2 对比度是扫描成功的关键很多炫彩二维码扫不出来问题不在颜色本身而在前景色和背景色之间的亮度差不够。扫码算法本质上是在识别深色模块和浅色背景之间的明暗边界如果前景色是金黄色、背景是浅黄色那模块边界就糊掉了。判断配色是否安全可以用一个简化公式计算相对亮度相对亮度约等于0.299乘以红色通道值加0.587乘以绿色通道值加0.114乘以蓝色通道值。前景色和背景色的亮度差值建议大于100这个数值是我实测下来的安全线。比如纯黑亮度接近0纯白亮度接近255差值超过250绝对安全深蓝这类深色系配浅色背景差值普遍在120到180之间也很稳。3.3 尺寸、纠错等级和静区怎么定我给的参数区里设置了三个关键参数尺寸、前景色、背景色。尺寸我建议设置在200到400之间单位是像素。太小了二维码模块容易糊太大了图片体积大、插入Excel也占地方。打印场景特别注意普通标签上二维码实际尺寸不要小于20毫米见方越小越考验打印机精度。静区处理有两条路一是在接口参数里调大图片尺寸让二维码内容区只占图片中央一部分四周自然留白二是插入Excel后用图片对齐功能让图片四周保留几个像素的空隙。推荐第一种因为静区是跟着图片走的打印时不容易被裁掉。3.4 我常用的几组炫彩配色这里给出五组我实测稳定可扫的配色方案前景色和背景色都用十六进制色值直接填到参数区就能用。场景风格前景色背景色亮度差适用场景专业稳重1F4E79DCE6F1约153设备标签、资产盘点复古文化8C1D1DF5F0E6约152文创、活动装饰生态户外2E5D34EDF3E8约148户外巡检、环保项目时尚联名4B2E83EDE7F6约158联名活动、海报商务高定1A1A1AFFF4D6约197名片、高端包装4. 实操两个按钮把炫彩二维码工具做出来4.1 开启宏环境和界面布局打开Excel后先确认功能区有没有开发工具选项卡。没有的话文件-选项-自定义功能区右侧勾选开发工具。然后到信任中心设置里把宏安全性调整为禁用所有宏并发出通知这样打开自己写的工作簿时允许启用宏。注意这是自己电脑调试用的设置公司统一管理环境不要乱改尽量申请白名单。界面布局我做了个很简单的参数区方便日常使用。打开VBA编辑器快捷键AltF11插入一个用户窗体或者直接在工作表里划一块空白区域作为操作台。实际项目中我直接在工作表上排布位置内容说明B2待编码内容手动输入或引用其他单元格B3图片尺寸建议200到400B4前景色十六进制色值不带#号B5背景色十六进制色值不带#号D2二维码图片区生成的图片自动放在这里4.2 核心模块API下载函数与URL编码关键代码是两个部分下载图片的API声明以及URL拼接。VBA里下载文件要用Windows系统的URLDownloadToFile函数它是urlmon.dll提供的系统接口不需要额外装任何库。注意32位和64位Office的声明写法不一样用条件编译可以一次兼容。#If VBA7 Then Private Declare PtrSafe Function URLDownloadToFile Lib urlmon _ (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal szFileName As String, _ ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long #Else Private Declare Function URLDownloadToFile Lib urlmon _ (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, _ ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long #End IfURL拼接时有个大坑二维码内容里如果包含中文、空格、符号直接拼进URL会导致请求失败或截断。所以一定要做URL编码。Excel 2013以上版本可以直接用WorksheetFunction.EncodeURL十分省事。旧版本的话需要自己写UTF-8编码函数逻辑也不复杂网上有很多现成实现。Function BuildQRCodeURL(ByVal content As String, ByVal size As Long, _ ByVal foreColor As String, ByVal bgColor As String) As String Dim encoded As String encoded WorksheetFunction.EncodeURL(content) BuildQRCodeURL https://api.qrserver.com/v1/create-qr-code/? _ size size x size _ data encoded _ color foreColor _ bgcolor bgColor _ eccH End Function4.3 一键生成插入图片并设置炫彩效果单张生成宏的逻辑很简单读取参数区内容拼接URL下载到临时目录插入工作表定位到你指定的单元格最后清理临时文件。这里有个细节值得说明为什么要下载到临时文件而不是直接插入因为Pictures.Insert接口接收的是文件路径如果你给它一个远程URL它会认为你要插入一个外部图片链接后期打印或另存时容易出问题。下载到本地再插入图片就变成工作簿的一部分了。Sub GenerateQRCode() On Error GoTo HandleErr Dim sh As Worksheet Dim content As String Dim size As Long Dim foreColor As String Dim bgColor As String Dim url As String Dim tmpFile As String Dim qrPic As Picture Dim targetRange As Range Set sh ThisWorkbook.Sheets(二维码生成) content sh.Range(B2).Value size sh.Range(B3).Value foreColor sh.Range(B4).Value bgColor sh.Range(B5).Value If content Then MsgBox 请先在B2单元格填写二维码内容 Exit Sub End If url BuildQRCodeURL(content, size, foreColor, bgColor) tmpFile Environ(TEMP) \excel_qr_ Format(Now, yyyymmdd_hhmmss) .png If URLDownloadToFile(0, url, tmpFile, 0, 0) 0 Then MsgBox 图片下载失败请检查网络或URL参数 Exit Sub End If Set targetRange sh.Range(D2) Set qrPic sh.Pictures.Insert(tmpFile) With qrPic .Width targetRange.Width - 10 .Height targetRange.Height - 10 .Left targetRange.Left (targetRange.Width - .Width) / 2 .Top targetRange.Top (targetRange.Height - .Height) / 2 .Placement xlMove End With On Error Resume Next qrPic.Shadow.Visible msoTrue qrPic.Glow.Radius 5 On Error GoTo 0 Kill tmpFile Exit Sub HandleErr: MsgBox 生成失败 Err.Description End Sub代码里最后给二维码图片加了阴影和发光效果这是让二维码看起来炫的关键一步。不同Office版本对图片效果的支持有差异如果报错就把这几行注释掉改为手动操作右键图片-设置图片格式-效果添加阴影、光晕或柔化边缘。效果这个东西见仁见智我实际使用时发现轻微的光晕能明显提升视觉质感但不要加太厚否则会影响对比度。4.4 批量模式整列数据批量生成单张生成只是热身真正省时间的是批量。批量宏的思路是按行循环从数据列第一行开始取单元格内容下载二维码插入到对应行的图片列然后移动到下一行直到数据结束。Sub BatchGenerateQRCode() On Error GoTo HandleErr Dim sh As Worksheet Dim content As String Dim size As Long Dim foreColor As String Dim bgColor As String Dim url As String Dim tmpFile As String Dim qrPic As Picture Dim i As Long Set sh ThisWorkbook.Sheets(二维码生成) size sh.Range(B3).Value foreColor sh.Range(B4).Value bgColor sh.Range(B5).Value i 2 Do While sh.Range(B i).Value content sh.Range(B i).Value url BuildQRCodeURL(content, size, foreColor, bgColor) tmpFile Environ(TEMP) \excel_qr_ i _ Format(Now, hhmmss) .png If URLDownloadToFile(0, url, tmpFile, 0, 0) 0 Then Set qrPic sh.Pictures.Insert(tmpFile) With qrPic .Width sh.Range(D i).Width - 10 .Height sh.Range(D i).Height - 10 .Left sh.Range(D i).Left 5 .Top sh.Range(D i).Top 5 .Placement xlMove End With Kill tmpFile End If DoEvents i i 1 Loop MsgBox 共生成 i - 2 张二维码 Exit Sub HandleErr: MsgBox 批量生成失败行号 i 错误 Err.Description End Sub循环里我特意加了DoEvents作用是让Excel在每生成一张后处理一次界面事件避免大批量生成时界面无响应。临时文件名里带行号和时间戳避免并发或重复执行时文件互相覆盖。整个批量过程跑下来几十行数据也就是几秒钟的事。5. 实测记录单张生成到批量打印的完整流程5.1 单张生成的实测记录我用一批仓库物料数据做测试。工作表B2填写料号ZC-2024-001批次B240618有效期2026-06尺寸设300前景色设1F4E79背景色设DCE6F1点生成按钮。图片在D2出现深蓝底浅蓝背景四个角还带一层阴影放到手机屏幕前扫了一下一秒识别成功。接下来我做了个破坏性测试把B2改成一个带中文、空格和符号的复杂字符串比如批次AB-备件仓 / 三楼再次点击生成。代码里的EncodeURL把空格编码成%20编码成%26接口正常返回图片扫码也不乱。这个测试主要是验证URL编码处理是否可靠因为我见过太多没有编码导致生成失败的案例。5.2 批量打印排版与静区控制批量生成后D列从第2行往下排列图片。直接打印前我习惯先做两个设置一是页面布局-缩放-调整为1页宽避免内容分页导致二维码被中间切断二是设置打印区域只框选包含图片的有效范围防止打印出一堆空白页。静区控制这块比较容易翻车。如果接口图片四周没有预留空白打印时会显得二维码贴边不利于扫描。我测试时直接在接口参数里把图片尺寸加大到350二维码内容本身只占图片中央约八成区域四周留出足够静区打印出来的标签扫起来明显比之前稳。另外打印前在打印预览里用放大镜检查一下最外侧的定位角有没有被裁掉这个检查只要几秒但能省掉一整沓报废标签。5.3 和Excel常规功能的组合打法这个工具配合Excel自带的几个功能特别好用。生成之前我一般先对数据列做去重用条件格式或者Remove Duplicates避免同一串内容生成两张重复二维码。筛选场景下先套用筛选只保留今天入库这个条件再跑批量宏生成的二维码就只覆盖当前需要的记录。数量统计用SUMIFS确认一下生成行数和预期一致。如果B列是公式计算出来的内容比如用连接符把多个单元格拼成一条二维码内容切记公式的结果要能被宏正确读取这没问题Value取到的就是计算后的最终文本。但如果公式结果是数组公式个别旧版本Excel会取不到值这种时候先把公式列复制粘贴成数值再跑宏更保险。6. 常见问题与排查技巧实录6.1 宏和加载项相关的坑打开做好的工作簿提示宏已被禁用这是最常见的问题。先看警告条点启用内容如果按钮是灰的说明信任中心设置不允许到文件-选项-信任中心-宏设置里调整。还有一种情况是VBA工程本身没加载出来开发工具-Visual Basic按钮点了没反应通常是VBE插件冲突可以到文件-选项-加载项-管理COM加载项把可疑项取消勾选。加载项被禁用这件事我遇到过好几次特别是别人电脑上打开我的宏文件时报错。排查思路是先看这个Excel文件是不是xlsm格式老版本xls格式不允许带宏再看本机是否装了第三方Excel插件有些插件会把VBA工程锁住。6.2 图片下载失败与URL编码生成时提示图片下载失败先用浏览器直接打开同样的URL看能否出图。如果浏览器能打开说明代码拼接的URL有问题多半是内容里带了特殊字符没做编码。如果浏览器也打不开就是网络策略或接口本身问题换一个接口服务商或者把临时文件路径改到当前工作簿所在目录再试。临时文件写不进去也偶尔发生比如系统TEMP目录权限被改掉或者杀毒软件拦截了对TEMP目录的写入。解决办法是把临时文件路径改成当前工作簿目录ThisWorkbook.Path \temp_qr.png但记得用完了删掉别在工作目录留下垃圾文件。6.3 扫描不出来时的排查顺序二维码扫不出来我按这个顺序排查第一看亮度差前景色和背景色是不是太接近按前面给的公式算一下第二看静区图片是不是贴边打印了二维码四周有没有足够的空白第三看尺寸成品二维码有没有小于20毫米第四看容错等级如果是H等级还扫不出来那基本就是打印模糊或反光问题。还有一个非常阴间的坑Excel的图片默认会随单元格移动和大小变化如果排序时整行移动二维码图片可能会脱离原来的行。所以我批量插入时特意把Placement设为xlMove图片跟着单元格走排序不会错位。如果是按某列重新排序后再生成二维码建议排序完成后再跑批避免图片和数据行错位。6.4 几组Excel日常疑难杂症的顺手解法实际做这个工具时我顺手把几个常见操作问题处理了。CtrlV失效先按Esc取消当前单元格编辑状态再重新复制粘贴还不行就检查是否被剪贴板工具或输入法占用关掉可疑后台程序再试。下拉复制公式失效如果只有公式复制不出来可能是自动扩展引用区域设置被关闭在文件-选项-高级里重新勾选如果连值都复制不出来检查是否误开了手动计算改成自动计算。公式下拉后结果不更新大概率是计算选项被改成手动在公式选项卡里切回自动。这类问题看起来和二维码无关但做批量工具时经常撞上顺手记在这里。6.5 关闭Excel时残留进程或VB工程提示批量生成大量图片时偶尔会遇到关闭Excel后任务管理器里还有EXCEL进程或者再次打开文件时提示VB工程损坏。这多半是宏还在跑或者图片对象没释放。我的处理习惯是所有宏执行完毕后先清空对象引用再退出必要的时候用Application.OnKey禁用某些快捷键避免用户中途打断。任务管理器里残留进程的处理先把Excel所有窗口关掉再在任务管理器里结束残留进程但不要随便用杀进程工具容易丢未保存的数据。VB工程提示多半是因为宏代码里碰到了不兼容的对象引用比如我在代码里尝试设置Glow效果某些版本不支持就会弹错误。所以代码块里On Error Resume Next不是偷懒是真的需要非核心的视觉效果失败不应该中断主流程。最后分享一个我自己的使用习惯。颜色的选择不要每次临时想直接在Excel参数区旁边放一个色板区把项目常用的几组配色做成下拉列表生成前选一下就换肤。我常备的几组色值是拿拾色器从公司VI里抠出来的线上材料用经典黑色配白底最保险线下打印标签才用彩色既好看又不影响识别。这个工具我用了一年多每逢盘点、巡检、贴标签都能用上改一行跑一次再也没因为二维码的事加过班。
