| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 757 人关注过本帖
标题:EXCEL自动化上的问题
取消只看楼主 加入收藏
wube
Rank: 12Rank: 12Rank: 12
等 级:贵宾
威 望:23
帖 子:1820
专家分:3681
注 册:2011-3-24
结帖率:97.66%
收藏
已结贴  问题点数:20 回复次数:4 
EXCEL自动化上的问题
我代码上只下了.Range()写入资料,
图片附件: 游客没有浏览图片的权限,请 登录注册

但是资料格式却变成文字,
图片附件: 游客没有浏览图片的权限,请 登录注册

一做运算就会异常
图片附件: 游客没有浏览图片的权限,请 登录注册

怎样才可以写入EXCEL资料,资料不被变动,又能在EXCEL内对资料做数值运算的方法?
除了自己在VB内先算好再写入外.
搜索更多相关主题的帖子: EXCEL 资料 
2015-01-30 18:29
wube
Rank: 12Rank: 12Rank: 12
等 级:贵宾
威 望:23
帖 子:1820
专家分:3681
注 册:2011-3-24
收藏
得分:0 
以下是引用风吹过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

不知道是哪边的问题?

不要選我當版主
2015-02-02 14:16
wube
Rank: 12Rank: 12Rank: 12
等 级:贵宾
威 望:23
帖 子:1820
专家分:3681
注 册:2011-3-24
收藏
得分:0 
来源档案:
ItemList.rar (589.39 KB)

不要選我當版主
2015-02-02 14:20
wube
Rank: 12Rank: 12Rank: 12
等 级:贵宾
威 望:23
帖 子:1820
专家分:3681
注 册:2011-3-24
收藏
得分:0 
Private Sub ToEXCELBinMap(SheetNumber As Integer, xlBook As Object)
此行之后的代码都是其他EXCEL页面的...

不要選我當版主
2015-02-02 14:24
wube
Rank: 12Rank: 12Rank: 12
等 级:贵宾
威 望:23
帖 子:1820
专家分:3681
注 册:2011-3-24
收藏
得分:0 
小朋友不要学喔,叔叔有试过,若要产生150张图,速度超级慢,
有考虑要把运算部分九成转给VC去做,VB只负责做CSV转EXCEL。
若VC TO EXCEL能试出来,可能会直接跳过VB了.

不要選我當版主
2015-02-02 23:33
快速回复:EXCEL自动化上的问题
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.075051 second(s), 9 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved