EXCEL自动化上的问题 - 编程论坛

#2

HVB62015-01-31 06:38

回复楼主 wube

木有附件？

#3

风吹过b2015-01-31 08:46

你检查你的单元格的格式，是不是全部设置了为文本格式，或者你写入资料时，代码里指定了文本格式。

看你的第二张图，所有的数据前面都有一个绿色三角形，表示这是文本格式保存的数字。

#4

lianyicq2015-02-02 09:27

我测试了，EXCEL表格内可以进行计算。代码如下：

程序代码：

Private Sub Command1_Click()
Dim EXAPP As Excel.Application
Dim WB As Excel.Workbook
Dim sht As Excel.Worksheet
Dim temp() As String
Set EXAPP = CreateObject("excel.application")
Set WB = EXAPP.Workbooks.Open("c:\test.xlsx")
Set sht = WB.Worksheets("Sheet1")
temp = Split("012,324.45,adbc,030.32", ",")
sht.Range(cells(1, 1), cells(1, 1 + UBound(temp))).Value = temp
WB.Save
WB.Close
Set sht = Nothing
Set WB = Nothing
Set EXAPP = Nothing
End Sub

即使把E1设置为“文本”，excel计算如下图

只有本站会员才能查看附件，请登录

再找找其它原因
不知道itemlistdata(i)中字符串如何，我在split函数的源字符串中加入不可见字符，比如vbtab后，就不能计算了。

[ 本帖最后由 lianyicq 于 2015-2-2 09:32 编辑 ]

#5

wube2015-02-02 14:16

以下是引用风吹过b在2015-1-31 08:46:16的发言：

你检查你的单元格的格式，是不是全部设置了为文本格式，或者你写入资料时，代码里指定了文本格式。

看你的第二张图，所有的数据前面都有一个绿色三角形，表示这是文本格式保存的数字。

1.EXCEL档案是自动产生的，应该没有办法事先设定单元格.
2.写入资料时，代码里并无指定了文本格式(或是说我也不知道怎么去指定).
3.EXCEL绿色三角型的确是表示文本格式保存的纪录(但是我也不知道是怎么来的).

关于EXCEL的代码 :

程序代码：

Private Sub Transform(sh As Integer)
Dim xlApp As New EXCEL.Application
Dim xlBook As EXCEL.Workbook
Dim xlsheet As EXCEL.Worksheet
Dim xlRang As EXCEL.Range
Dim SheetNum As Integer, i As Integer
Dim File_name As String

    File_name = Mid$(FileNameArray(0), 1, InStrRev(FileNameArray(0), "_")) & Format(Now, "yyyymmddhhmmss") & ".xlsx"
    Text1.Text = Mid$(File_name, InStrRev(File_name, "\") + 1): Text1.ToolTipText = File_name

    If SheetNum = 0 Then
        Set xlApp = CreateObject("Excel.Application")
'        xlApp.Visible = True
        xlApp.Visible = False
        Set xlBook = xlApp.Workbooks.Add
        Set xlBook = xlApp.Workbooks.Item("Book1")
        If UBound(FileNameArray) > 0 Then
            For i = 1 To ((UBound(FileNameArray) + 1) + sh)
                If i > 3 Then
                    Set xlsheet = xlBook.Sheets.Add
                Else
                    Set xlsheet = xlBook.Sheets(i)
                End If
                With xlsheet
                    .Cells.Font.Name = "Gulim"
                    .Cells.Font.Size = 10
                    .Cells.ColumnWidth = 4
                    .Select
                    .Cells.Borders.LineStyle = xlContinuous
                    .Cells.Borders.Weight = xlThin
                    .Cells.Borders.ColorIndex = 15
                    xlApp.ActiveWindow.Zoom = 75
                End With
                DoEvents
            Next i
        End If
    End If

    Call ToEXCELItemList(1, xlBook)
    Call ToEXCELBinMap(2, xlBook)
    Call ToEXCELTestInfo(4, xlBook)
    If sh <> 0 Then
        Call ToEXCELEveryItem(5, sh, xlBook)
    End If

    If IsFileExist(File_name) = False Then
        xlApp.ActiveWorkbook.SaveAs (File_name)
    Else
        MsgBox "Error!!!"
    End If

    xlApp.Visible = True
    xlApp.UserControl = True

    Set xlRang = Nothing
    Set xlsheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing

    ProgressBar1.Value = 100

End Sub

Private Sub ToEXCELItemList(SheetNumber As Integer, xlBook As Object)
Dim xlsheet As EXCEL.Worksheet
Dim i As Integer, j As Integer, StartNum As Integer, StartNum1 As Integer
Dim Temp() As String, iMax As Double, iMin As Double

    Set xlsheet = xlBook.Sheets(SheetNumber)

    xlsheet.Name = "TestItemList"
    xlsheet.Activate
    xlsheet.Cells.HorizontalAlignment = xlCenter

    StartNum = 1                                                ''行
    StartNum1 = 1                                               ''列

    With xlsheet
        For i = 0 To UBound(ItemListData)
            Temp = Split(ItemListData(i), ",")
            .Range(.Cells(StartNum + i, StartNum1), .Cells(StartNum + i, StartNum1 + UBound(Temp))).Value = Temp
            If i > 4 Then
                iMax = 0: iMin = 0
                For j = 0 To UBound(Temp)
                    If j > 8 Then
                        If ((Trim$(Temp(1)) <> "") And (Trim$(Temp(j)) <> "")) Then
                            iMax = CDbl(Temp(4)): iMin = CDbl(Temp(3))
                            If Trim$(Temp(2)) <> "F" Then
                                If ((CDbl(Temp(j)) < iMin) Or (CDbl(Temp(j) > iMax))) Then
                                    .Cells(StartNum + i, StartNum1 + j).Font.Color = vbRed
                                End If
                            End If
                        End If
                    End If
                    DoEvents
                Next j
            End If
            DoEvents
            Erase Temp
            ProgressBar1.Value = (i / UBound(ItemListData)) * 100
        Next i

        .Columns.AutoFit
        .Rows.AutoFit
        .Range("J6").Activate
        ActiveWindow.FreezePanes = True
        .Columns("I:C").Group

    End With
End Sub

Private Sub ToEXCELBinMap(SheetNumber As Integer, xlBook As Object)
Dim xlsheet As EXCEL.Worksheet
Dim iSheet As Integer, i As Integer, j As Integer, StartNum As Integer, StartNum1 As Integer
Dim Temp() As String

    For iSheet = 0 To 1
        Set xlsheet = xlBook.Sheets(SheetNumber + iSheet)
        xlsheet.Name = IIf(i = 0, "HW", "SW")
        xlsheet.Activate
        xlsheet.Cells.HorizontalAlignment = xlCenter

        StartNum = 1                                                ''行
        StartNum1 = 1                                               ''列

        With xlsheet
            For i = 0 To UBound(HWBinMapData)
                Temp = IIf(iSheet = 0, Split(HWBinMapData(i), ","), Split(SWBinMapData(i), ","))
                .Range(.Cells(StartNum + i, StartNum1), .Cells(StartNum + i, StartNum1 + UBound(Temp))).Value = Temp

                If Color = True Then
                    If i > 3 Then
                        For j = 0 To UBound(Temp)
                            If j > 0 Then
                                If Temp(j) <> "" Then
                                    If CheckBinData(2, Temp(j)) = True Then
                                        .Cells(StartNum + i, StartNum + j).Interior.Color = &HFF00&
                                    Else
                                        .Cells(StartNum + i, StartNum + j).Interior.Color = &H8080FF
                                    End If
                                End If
                            End If
                        Next j
                    End If
                End If
                DoEvents
                Erase Temp
            Next i
            .Columns.AutoFit
            .Rows.AutoFit
        End With
    Next iSheet

End Sub

Private Sub ToEXCELTestInfo(SheetNumber As Integer, xlBook As Object)
Dim xlsheet As EXCEL.Worksheet
Dim i As Integer, j As Integer, StartNum As Integer, StartNum1 As Integer
Dim Temp() As String

    Set xlsheet = xlBook.Sheets(SheetNumber)
    xlsheet.Name = "TestInfo"
    xlsheet.Activate
    xlsheet.Cells.HorizontalAlignment = xlCenter

    StartNum = 1                                                ''行
    StartNum1 = 1                                               ''列

    With xlsheet

        For i = 0 To UBound(TestDataInfo)
            Temp = Split(TestDataInfo(i), ",")
            If (UBound(Temp) = -1) Then
                .Range(.Cells(StartNum + i, StartNum1), .Cells(StartNum + i, StartNum1 + 0)).Value = ""
            Else
                .Range(.Cells(StartNum + i, StartNum1), .Cells(StartNum + i, StartNum1 + UBound(Temp))).Value = Temp
            End If
            DoEvents
            Erase Temp
        Next i
        .Columns.AutoFit
        .Rows.AutoFit
        .Columns("A:C").HorizontalAlignment = xlGeneral
    End With

End Sub

Private Sub ToEXCELEveryItem(SheetNumber As Integer, LastSheetNumber As Integer, xlBook As Object)
Dim xlsheet As EXCEL.Worksheet
Dim i As Integer, j As Integer
Dim Tmp As String

    For i = 0 To UBound(SelectItem, 2)
        If SelectItem(2, 0) <> "" Then
            If ((Trim$(SelectItem(2, i)) <> "") And (InStr(SelectItem(2, i), ",") <> 0)) Then

                Set xlsheet = xlBook.Sheets(SheetNumber + i)

                Tmp = Trim$(Mid$(SelectItem(2, i), InStr(SelectItem(2, i), ",") + 1))
                Tmp = Trim$(Mid$(Tmp, InStrRev(Tmp, "-") + 1))

                xlsheet.Name = Tmp & "_Chart"
                xlsheet.Activate
                xlsheet.Cells.HorizontalAlignment = xlCenter

                Call GenerateChart(xlsheet, i, xlsheet.Name)
            End If
        End If

        DoEvents

        If UBound(SelectItem, 2) <> 0 Then
            ProgressBar2.Value = CInt((i / UBound(SelectItem, 2)) * 100)
        End If
    Next i

    ProgressBar2.Value = 100

End Sub

Private Sub GenerateChart(xlsheet As Object, iCount As Integer, SheetName As String)
Dim oChart As EXCEL.Chart
Dim MyCharts As EXCEL.ChartObjects
Dim MyCharts1 As EXCEL.ChartObject

Dim j As Integer, StartNum As Integer, StartNum1 As Integer
Dim Temp() As String, Temp1() As String, Temp2() As String, Temp3() As String, Tmp() As String
Dim TempString1 As String, TempString2 As String, TempString3 As String
Dim Max As Double, Min As Double, ICNum As Integer

    StartNum = 1                                                ''行
    StartNum1 = 1                                               ''列

    With xlsheet

        Temp = Split(SelectItem(2, iCount), ",")

        If UBound(Temp) = 1 Then

            TempString1 = ItemListData(2)
            Temp1 = Split(TempString1, ",")

            TempString2 = ItemListData(3)
            Temp2 = Split(TempString2, ",")

            TempString3 = ItemListData(iCount + 5)
            Temp3 = Split(TempString3, ",")

            ICNum = UBound(Temp3)

            For j = 8 To UBound(Temp3)

                If Trim$(Temp3(j)) <> "" Then
                    If j = 8 Then
                        .Cells(StartNum, StartNum1 + 0).HorizontalAlignment = xlCenter
                        .Cells(StartNum, StartNum1 + 0) = "X"
                        .Cells(StartNum, StartNum1 + 0).Interior.ColorIndex = 40
                        .Cells(StartNum, StartNum1 + 1).HorizontalAlignment = xlCenter
                        .Cells(StartNum, StartNum1 + 1) = "Y"
                        .Cells(StartNum, StartNum1 + 1).Interior.ColorIndex = 40
                        .Cells(StartNum, StartNum1 + 2).HorizontalAlignment = xlCenter
                        .Cells(StartNum, StartNum1 + 2) = "Site"
                        .Cells(StartNum, StartNum1 + 2).Interior.ColorIndex = 40
                        .Cells(StartNum, StartNum1 + 3).HorizontalAlignment = xlCenter
                        .Cells(StartNum, StartNum1 + 3) = "HW | SW"
                        .Cells(StartNum, StartNum1 + 3).Interior.ColorIndex = 40
                        .Cells(StartNum, StartNum1 + 4).HorizontalAlignment = xlCenter
                        .Cells(StartNum, StartNum1 + 4) = "Result"
                        .Cells(StartNum, StartNum1 + 4).Interior.ColorIndex = 40

                        .Range("A1:R1").Borders.LineStyle = xlContinuous

                        .Cells(StartNum, StartNum1 + 7) = "MAX = " & Format(Temp3(6), "0.000000")
                        Max = CDbl(Temp3(6))
                        .Cells(StartNum, StartNum1 + 7).HorizontalAlignment = xlLeft
                        .Cells(StartNum, StartNum1 + 9) = "MIN = " & Format(Temp3(7), "0.000000")
                        Min = CDbl(Temp3(7))
                        .Cells(StartNum, StartNum1 + 9).HorizontalAlignment = xlLeft
                        .Cells(StartNum, StartNum1 + 11) = "AVG = " & Format(Temp3(8), "0.000000")
                        .Cells(StartNum, StartNum1 + 11).HorizontalAlignment = xlLeft
                        .Cells(StartNum, StartNum1 + 13) = "Unit = " & Temp3(5)
                        .Cells(StartNum, StartNum1 + 13).HorizontalAlignment = xlLeft
                    Else
                        Tmp = Split(Temp1(j), "|")
                        .Cells(StartNum + j - 8, StartNum1 + 0) = Tmp(0)                                'X
                        .Cells(StartNum + j - 8, StartNum1 + 1) = Tmp(1)                                'Y
                        .Cells(StartNum + j - 8, StartNum1 + 2) = Tmp(2)                                'Site
                        .Cells(StartNum + j - 8, StartNum1 + 3) = Temp2(j)                              'Bin
                        .Cells(StartNum + j - 8, StartNum1 + 4) = Format(Temp3(j), ".000000")           'Result
                        Erase Tmp
                    End If
                End If
                DoEvents
            Next j

            Erase Temp1, Temp2, Temp3

        End If

        .Columns.AutoFit
        .Rows.AutoFit

        Erase Temp

        Set MyCharts = .ChartObjects
        Set MyCharts1 = MyCharts.Add(200, 28, 700, 600)

        Set oChart = MyCharts1.Chart
'        Debug.Print xlsheet.Cells(StartNum + 1, StartNum1 + 5)
        With oChart
            .ChartType = xlXYScatterSmooth
            .SetSourceData xlsheet.Range(xlsheet.Cells(StartNum + 1, StartNum1 + 4), xlsheet.Cells(StartNum + ICNum - 9, StartNum1 + 4)), xlColumns
            .Location xlLocationAsObject, SheetName
        End With

        With oChart

            .Axes(xlValue).Select
            With .Axes(xlValue)
                .MinimumScale = Min
                .MaximumScale = Max
                .MinorUnitIsAuto = True
                .MajorUnitIsAuto = True
                .Crosses = xlAutomatic
                .ReversePlotOrder = False
                .ScaleType = xlLinear
                .DisplayUnit = xlNone
            End With

            .Axes(xlCategory).Select

            With .Axes(xlCategory)
                .MinimumScale = 0
                .MaximumScale = ICNum
                .MinorUnitIsAuto = True
                .MajorUnitIsAuto = True
                .Crosses = xlAutomatic
                .ReversePlotOrder = False
                .ScaleType = xlLinear
                .DisplayUnit = xlNone
            End With

        End With

    End With

End Sub

不知道是哪边的问题?

#6

wube2015-02-02 14:20

来源档案:

只有本站会员才能查看附件，请登录

#7

wube2015-02-02 14:24

Private Sub ToEXCELBinMap(SheetNumber As Integer, xlBook As Object)
此行之后的代码都是其他EXCEL页面的...

#8

wube2015-02-02 23:33

小朋友不要学喔，叔叔有试过，若要产生150张图，速度超级慢，

有考虑要把运算部分九成转给VC去做，VB只负责做CSV转EXCEL。

若VC TO EXCEL能试出来，可能会直接跳过VB了.