企业官网建设流程全解析

一、VBA 宏编辑

1. 一键转换净值与清除#N/A格式

下面集合了通过对B,C,D,E四列的格式转换，如把格式为空及公式匹配为#N/A的转换为空，将公式转换的值转为净值的操作，同时包含有对转换后标题样式及内容区域样式的设置，如标题是微软雅黑加粗11号字体且垂直居中，区域内容为微软雅黑不加粗10号字体且垂直居中。

Sub 一键处理JKLM()Dim ws As Worksheet Set ws=ActiveSheet Dim lastRowB As Long, lastRowC As Long Dim lastRowD As Long, lastRowE As Long lastRowB=ws.Cells(ws.Rows.Count,"B").End(xlUp).Row lastRowC=ws.Cells(ws.Rows.Count,"C").End(xlUp).Row lastRowD=ws.Cells(ws.Rows.Count,"D").End(xlUp).Row lastRowE=ws.Cells(ws.Rows.Count,"E").End(xlUp).Row If lastRowB<2Then lastRowB=2If lastRowC<2Then lastRowC=2If lastRowD<2Then lastRowD=2If lastRowE<2Then lastRowE=2' ===== 列标题 ===== With ws.Range("J1:M1") .Value = Array("物料编码", "转化SKU", "转化项目号", "转化95码") .Font.Name = "Microsoft YaHei" .Font.Size = 11 .Font.Bold = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With '=====设置列宽=====ws.Columns("J:M").ColumnWidth=12.8' ===== J 列（依赖 B）===== With ws.Range("J2:J" & lastRowB) .Formula = "=IF(ISNA(B2),"""",IF(B2="""","""",B2))" .Value = .Value End With '=====K 列（依赖 C）=====With ws.Range("K2:K"&lastRowC).Formula="=IF(ISNA(C2),"""",IF(C2="""","""",C2))".Value=.Value End With' ===== L 列（依赖 D）===== With ws.Range("L2:L" & lastRowD) .Formula = "=IF(ISNA(D2),"""",IF(LEN(D2)=4,D2&""J"",LEFT(D2,5)))" .Value = .Value End With '=====M 列（依赖 E）=====With ws.Range("M2:M"&lastRowE).Formula="=IF(ISNA(E2),"""",IF(E2="""","""",E2))".Value=.Value End With '=====内容区域样式=====With ws.Range("J2:M"&Application.Max(lastRowB, lastRowC, lastRowD, lastRowE)).Font.Name="Microsoft YaHei".Font.Size=10.Font.Bold=False .HorizontalAlignment=xlCenter .VerticalAlignment=xlCenter End With End Sub

2. 一键表格为三线表(带内部虚线版)

2.1. VB宏编辑器

Sub 企业级三线表()Dim tbl As Table For Each tbl In ActiveDocument.Tables On Error Resume Next'===================== '自动适应页面宽度'===================== tbl.AutoFitBehavior wdAutoFitWindow '=====================' 尝试平均分配列宽 '=====================tbl.AllowAutoFit=False tbl.Columns.DistributeWidth'===================== '水平居中'===================== tbl.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter '=====================' 垂直居中 '=====================tbl.Range.Cells.VerticalAlignment=wdCellAlignVerticalCenter'===================== '行高自动'===================== tbl.Rows.HeightRule = wdRowHeightAuto '=====================' 清除所有边框 '=====================tbl.Borders.Enable=False'===================== '顶线（1.5磅）'===================== With tbl.Borders(wdBorderTop) .LineStyle = wdLineStyleSingle .LineWidth = wdLineWidth150pt End With '=====================' 底线（1.5磅） '=====================With tbl.Borders(wdBorderBottom).LineStyle=wdLineStyleSingle .LineWidth=wdLineWidth150pt End With'===================== '内部横线（点状虚线）'===================== With tbl.Borders(wdBorderHorizontal) .LineStyle = wdLineStyleDot .LineWidth = wdLineWidth050pt End With '=====================' 内部竖线（点状虚线） '=====================With tbl.Borders(wdBorderVertical).LineStyle=wdLineStyleDot .LineWidth=wdLineWidth050pt End With'===================== '去掉左右边框'===================== tbl.Borders(wdBorderLeft).LineStyle = wdLineStyleNone tbl.Borders(wdBorderRight).LineStyle = wdLineStyleNone '=====================' 栏目线（第一行下边框） '=====================With tbl.Rows(1).Borders(wdBorderBottom).LineStyle=wdLineStyleSingle .LineWidth=wdLineWidth075pt End With On Error GoTo0Next tbl MsgBox"企业报告表格样式处理完成！", vbInformation End Sub

2.2. WPS宏编辑器

function标准三线表(){for(let 表格 of ActiveDocument.Tables){// 自动适应页面 表格.AutoFitBehavior(2);// 平均分布列宽 表格.Columns.DistributeWidth();// 水平居中 表格.Range.ParagraphFormat.Alignment=1;// 垂直居中 表格.Range.Cells.VerticalAlignment=1;// 行高自动 表格.Rows.Height=0;// 字体 表格.Range.Font.Name="宋体";表格.Range.Font.Size=10.5;// 表头加粗if(表格.Rows.Count>=1){表格.Rows(1).Range.Bold=true;}// 清除所有边框 表格.Borders.Enable=false;//=========顶线（1.5磅）=========表格.Borders.Item(-1).LineStyle=1;表格.Borders.Item(-1).LineWidth=12;//=========底线（1.5磅）=========表格.Borders.Item(-3).LineStyle=1;表格.Borders.Item(-3).LineWidth=12;//=========栏目线（0.75磅）=========if(表格.Rows.Count>=1){表格.Rows(1).Borders.Item(-3).LineStyle=1;表格.Rows(1).Borders.Item(-3).LineWidth=6;}// 去除左右边框 表格.Borders.Item(-2).LineStyle=0;表格.Borders.Item(-4).LineStyle=0;}}