李華忠

(安徽省宿州市勘測規(guī)劃設(shè)計(jì)院，安徽宿州234000)

對沉降觀測數(shù)據(jù)格式的探究

李華忠

(安徽省宿州市勘測規(guī)劃設(shè)計(jì)院，安徽宿州234000)

對高層建筑物進(jìn)行全面的、系統(tǒng)的、長期的沉降監(jiān)測，發(fā)現(xiàn)異常，即時(shí)分析，有必要規(guī)范數(shù)據(jù)格式，使數(shù)據(jù)滿足不同需求，也是數(shù)據(jù)生產(chǎn)過程當(dāng)中一個(gè)不可欠缺的環(huán)節(jié)。

沉降觀測；數(shù)據(jù)格式；探究

1 概述

沉降數(shù)據(jù)進(jìn)行輸出打印前，需進(jìn)行整理一下，但在數(shù)據(jù)多的情況下，進(jìn)行人工整理非常浪費(fèi)時(shí)間。本人編寫的沉降數(shù)據(jù)處理程序，有一個(gè)把沉降數(shù)據(jù)直接調(diào)整為打印格式的功能，使繁雜的人工操作，轉(zhuǎn)為批量的、自動(dòng)的一鍵操作。

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 定義單元格字體、字號及小數(shù)點(diǎn)位數(shù)

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 對每個(gè)單元格進(jìn)行處理

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 使用效果及結(jié)論

調(diào)整為打印格式是沉降監(jiān)測處理程序的一個(gè)模塊，極大提高了工作效率。此軟件已在工作中使用很長時(shí)間了，迄今為止運(yùn)行穩(wěn)定，性能、效果良好。

[1]賽奎春,李俊民.Visual Basic函數(shù)參考大全[M].人民郵電出版社,2007.

[2]顧孝烈.測量學(xué)[M].4版.同濟(jì)大學(xué)出版社,2012.

[3]黃聲享.變形監(jiān)測數(shù)據(jù)處理[M].武漢大學(xué)出版社,2010.

TP311

A

1009-3044(2017)24-0009-02

2017-07-01