对沉降观测数据格式的探究

李华忠

(安徽省宿州市勘测规划设计院，安徽宿州234000)

对沉降观测数据格式的探究

李华忠

(安徽省宿州市勘测规划设计院，安徽宿州234000)

对高层建筑物进行全面的、系统的、长期的沉降监测，发现异常，即时分析，有必要规范数据格式，使数据满足不同需求，也是数据生产过程当中一个不可欠缺的环节。

沉降观测；数据格式；探究

1 概述

沉降数据进行输出打印前，需进行整理一下，但在数据多的情况下，进行人工整理非常浪费时间。本人编写的沉降数据处理程序，有一个把沉降数据直接调整为打印格式的功能，使繁杂的人工操作，转为批量的、自动的一键操作。

2 定义变量及其变量声明

Dim ChenJiang1 As Excel.Application

Dim ChenJiangbook1 As Excel.Workbook

Dim ChenJiangsheet1 As Excel.Worksheet

Dim RNG As Range

Dim ZongLieShu As Integer

Dim QiShu As Integer

Dim ZongHangShu As Integer

Dim i As Integer

Dim j As Integer

Dim k As Integer

Dim BenCiChenJiangLiang As Double

Dim LeiJiChenJiangLiang As Double

Dim BenCiGaoCheng As Double

Dim ShangQiLeiJi As Double

Dim KaiGuan As Boolean

Dim KaiGuan2 As Boolean

Set ChenJiang1=CreateObject("excel.application")

Set ChenJiangbook1 = ChenJiang1.Workbooks.Open("e：zc2017-02-26-2#.xls")

Set ChenJiangsheet1=ChenJiangbook1.Sheets("2#")

ChenJiangsheet1.Activate

ZongHangShu=Val(Text2.Text)

QiShu=Val(Text3.Text)

ZongLieShu=4*QiShu

ChenJiang1.Visible=True

3 定义单元格字体、字号及小数点位数

KaiGuan=True

KaiGuan2=True

For k=2 To ZongLieShu Step 4

If KaiGuan Then

ChenJiangsheet1.Cells(i,k).Value=Format(Val(ChenJiang⁃sheet1.Cells(i,k).Value),"0.00000")

ChenJiangsheet1.Columns(k-1).Font.Name="宋体"

ChenJiangsheet1.Columns(k-1).Font.Size=10

ChenJiangsheet1.Columns(k-1).AutoFit

ChenJiangsheet1.Columns(k).Font.Name="宋体"

ChenJiangsheet1.Columns(k).Font.Size=10

ChenJiangsheet1.Columns(k).AutoFit

If Val(ChenJiangsheet1.Cells(i,k).Value)＜＞ 0 Then

BenCiGaoCheng= Format(Val(ChenJiangsheet1.Cells(i,k).Value),"0.00000")

KaiGuan=False

End If

Else

ChenJiangsheet1.Cells(i,k).Value=Format(Val(ChenJiang⁃sheet1.Cells(i,k).Value),"0.00000")

ChenJiangsheet1.Columns(k-1).Font.Name="宋体"

ChenJiangsheet1.Columns(k-1).Font.Size=10

ChenJiangsheet1.Columns(k-1).AutoFit

ChenJiangsheet1.Columns(k).Font.Name="宋体"

ChenJiangsheet1.Columns(k).Font.Size=10

ChenJiangsheet1.Columns(k).AutoFit

End If

Next k

4 对每个单元格进行处理

ShangQiLeiJi=0

BenCiChenJiangLiang=0

LeiJiChenJiangLiang=0

For j=3 To ZongLieShu Step 4

If j＞3 Then

If ChenJiangsheet1.Cells(i,j-1).Interior.ColorIndex=xl⁃

None Then

If Val(ChenJiangsheet1.Cells(i,j-1).Value)=0 Then

If Val(ChenJiangsheet1.Cells(i,j-5).Value)=0 Then

LeiJiChenJiangLiang=ChenJiangsheet1.Cells(i,j-3).Value

ChenJiangsheet1.Cells(i,j+1).Value=LeiJiChenJiangLiang

ChenJiangsheet1.Cells(i,j).Value=""

ChenJiangsheet1.Columns(j).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j+1).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j+1).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j-1).Value=Format(Val(ChenJi⁃angsheet1.Cells(i,j-1)),"0.00000")

Else

LeiJiChenJiangLiang=ChenJiangsheet1.Cells(i,j-3).Value

ChenJiangsheet1.Cells(i,j+1).Value=LeiJiChenJiangLiang

ChenJiangsheet1.Cells(i,j).Value=""

ChenJiangsheet1.Columns(j).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j+1).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j+1).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j-1).Value=Format(Val(ChenJi⁃angsheet1.Cells(i,j-1)),"0.00000")

End If

Else

If Val(ChenJiangsheet1.Cells(i,j-5).Value)=0 Then

If KaiGuan2=False Then

ChenJiangsheet1.Cells(i,j+1).Value=(BenCiGaoCheng-Val(ChenJiangsheet1.Cells(i,j-1).Value))*1000+ShangQiLeiJi

Else

ChenJiangsheet1.Cells(i,j+1).Value=(BenCiGaoCheng-Val(ChenJiangsheet1.Cells(i,j-1).Value))*1000

End If

LeiJiChenJiangLiang=ChenJiangsheet1.Cells(i,j+1).Value

ChenJiangsheet1.Cells(i,j).Value=""

ChenJiangsheet1.Columns(j).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j+1).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j+1).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j-1).Value=Format(Val(ChenJi⁃angsheet1.Cells(i,j-1)),"0.00000")

Else

BenCiChenJiangLiang=(Val(ChenJiangsheet1.Cells(i,j-5).Value)-Val(ChenJiangsheet1.Cells(i,j-1).Value))*1000

ChenJiangsheet1.Cells(i,j).Value=BenCiChenJiangLiang

LeiJiChenJiangLiang=ChenJiangsheet1.Cells(i,j-3).Value

LeiJiChenJiangLiang=BenCiChenJiangLiang+LeiJiChenJi⁃angLiang

ChenJiangsheet1.Cells(i,j+1).Value=LeiJiChenJiangLiang

ChenJiangsheet1.Cells(i,j).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j+1).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j+1).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j-1).Value=Format(Val(ChenJi⁃angsheet1.Cells(i,j-1)),"0.00000")

End If

End If

Else

If KaiGuan2 Then

ShangQiLeiJi=Format(Val(ChenJiangsheet1.Cells(i,j-3)),"0.00")

KaiGuan2=False

End If

If Val(ChenJiangsheet1.Cells(i,j-1).Value)=0 Then

If Val(ChenJiangsheet1.Cells(i,j-5).Value)=0 Then

LeiJiChenJiangLiang=ChenJiangsheet1.Cells(i,j-3).Value

ChenJiangsheet1.Cells(i,j+1).Value=LeiJiChenJiangLiang

ChenJiangsheet1.Cells(i,j).Value=""

ChenJiangsheet1.Columns(j).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j+1).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j+1).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j-1).Value=Format(Val(ChenJi⁃angsheet1.Cells(i,j-1)),"0.00000")

Else

LeiJiChenJiangLiang=ChenJiangsheet1.Cells(i,j-3).Value

ChenJiangsheet1.Cells(i,j+1).Value=LeiJiChenJiangLiang

ChenJiangsheet1.Cells(i,j).Value=""

ChenJiangsheet1.Columns(j).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j+1).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j+1).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j-1).Value=Format(Val(ChenJi⁃angsheet1.Cells(i,j-1)),"0.00000")

End If

Else

If Val(ChenJiangsheet1.Cells(i,j-5).Value)=0 Then

LeiJiChenJiangLiang=ChenJiangsheet1.Cells(i,j-3).Value

ChenJiangsheet1.Cells(i,j+1).Value=LeiJiChenJiangLiang

BenCiGaoCheng=Format(Val(ChenJiangsheet1.Cells(i,j-1).Value),"0.00000")

ChenJiangsheet1.Cells(i,j).Value=""

ChenJiangsheet1.Columns(j).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j+1).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j+1).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j-1).Value=Format(Val(ChenJi⁃angsheet1.Cells(i,j-1)),"0.00000")

Else

LeiJiChenJiangLiang=ChenJiangsheet1.Cells(i,j-3).Value

ChenJiangsheet1.Cells(i,j+1).Value=LeiJiChenJiangLiang

BenCiGaoCheng=Format(Val(ChenJiangsheet1.Cells(i,j-1).Value),"0.00000")

ChenJiangsheet1.Cells(i,j).Value=""ChenJiangsheet1.Columns(j).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j+1).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j+1).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j-1).Value=Format(Val(ChenJi⁃angsheet1.Cells(i,j-1)),"0.00000")

End If

End If

End If

Else

ChenJiangsheet1.Cells(i,j).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j+1).NumberFormatLocal="0.00_;[红色]-0.00"

ChenJiangsheet1.Columns(j+1).ColumnWidth=6.3

ChenJiangsheet1.Cells(i,j-1).Value=Format(Val(ChenJi⁃angsheet1.Cells(i,j-1)),"0.00000")

End If Next j

5 使用效果及结论

调整为打印格式是沉降监测处理程序的一个模块，极大提高了工作效率。此软件已在工作中使用很长时间了，迄今为止运行稳定，性能、效果良好。

[1]赛奎春,李俊民.Visual Basic函数参考大全[M].人民邮电出版社,2007.

[2]顾孝烈.测量学[M].4版.同济大学出版社,2012.

[3]黄声享.变形监测数据处理[M].武汉大学出版社,2010.

TP311

A

1009-3044(2017)24-0009-02

2017-07-01