چرا این دستور ایجاد فایل Excel در Visual Basic 2010 خطا داره؟!

سلام
چرا خطا دااره؟

Dim oExcel As Object    

Dim oBook As Object    

Dim oSheet As Object 

    

'Start a new workbook in Excel    

Set oExcel = CreateObject("Excel.Application")    

Set oBook = oExcel.Workbooks.Add

      

'Add data to cells of the first worksheet in the new workbook    

Set oSheet = oBook.Worksheets(1)    

oSheet.Range("A1").Value = "Last Name"    

oSheet.Range("B1").Value = "First Name"    

oSheet.Range("A1:B1").Font.Bold = True    

oSheet.Range("A2").Value = "Doe"    

oSheet.Range("B2").Value = "John"     





'Save the Workbook and Quit Excel    

oBook.SaveAs "C:\Book1.xls"    

oExcel.Quit

پیدا کردم مشکلش چیه
ازونجایی که ریفرنسش

Microsoft Excel 14.0 Object Libraryهست باید نسخه .Net Framework 4 برای پروژه انتخاب بشه...

سلام مجدد

آیا راهی هست که این کار (ساخت فایل Excel) با Net Framework 3.5 یا کمتر انجام بشه؟ چرا که نسخه 11 از کتابخانه هم همین مشکل رو داره!

ی مشکل دیگه ای که بود این بود که اگر اندازه متن از خانه Excel بیشتر باشه باید AutoFit بشه:

ضمیمه 156209

With DirectCast(WorkSheet1.Cells(RowNumber, i), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.AutoFit()

            End With

Excel auto fit column in VB.NET - Stack Overflow

من به یک مشکل دیگه برخوردم:
برای لینک کردن یک خانه از اکسل به یک صفحه دیگه از همون WorkBook چه کدی رو باید استفاده کنم؟!

سلام مجدد
نرم افزار برای استخراج به فایل اکسل در سیستم رایانه کاربر خطا برمیگردونه و نصب کننده Net Framework 4.0 هم می گه روی سیستمش نصب هست!
3 تا Reference هست که مربوط به استخراج به فایل اکسل استفاده می شه که توی درایو c هست با چه ابزاری نصب می شن؟!
آیا کپی اونا توی پوشه اصلی شون کافیه؟

نقل قول:

---

نوشته شده توسط ROSTAM2

سلام مجدد
نرم افزار برای استخراج به فایل اکسل در سیستم رایانه کاربر خطا برمیگردونه و نصب کننده Net Framework 4.0 هم می گه روی سیستمش نصب هست!
3 تا Reference هست که مربوط به استخراج به فایل اکسل استفاده می شه که توی درایو c هست با چه ابزاری نصب می شن؟!
آیا کپی اونا توی پوشه اصلی شون کافیه؟

---

برای این منظور نسخه از از Excel که در نرم افزار استفاده شده باید روی سیستم نصب بشه....
گر چه می شه بدون اضافه کردن Refernce با استفاده از Excel از دستورات مطلب شماره 1 استفاده کرد ولی اینکار کدنویسی رو خیلی سخت می کنه:

Imports System.Runtime.InteropServices

Imports Excel = Microsoft.Office.Interop.Excel

Public Class ExcelExport

    Protected Friend Shared WithEvents ExcelApplication As Excel.Application

    Protected Friend Shared WithEvents WorkBook As Excel.Workbook

    Protected Friend Shared WithEvents WorkSheet1 As Excel.Worksheet





    Protected Friend Shared Sub Products()

        ExcelApplication = New Excel.Application

        WorkBook = ExcelApplication.Workbooks.Add

        WorkSheet1 = WorkBook.Worksheets(1)

        With WorkBook

            DirectCast(.Worksheets(2), Excel.Worksheet).Delete()

            DirectCast(.Worksheets(2), Excel.Worksheet).Delete()

        End With

        Dim SortedProducts = Product.Sorted

        With WorkSheet1

            .Name = "لیست محصولات"

            .DisplayRightToLeft = True

            Dim X As Integer = 1

            ' For Column Headers (Caption)

            With DirectCast(.Cells(X, 1), Excel.Range)

                .EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 5

                .EntireRow.Font.Bold = True

                .Value = "ردیف"

            End With

            With DirectCast(.Cells(X, 2), Excel.Range)

                .EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 25

                .Value = "نام محصول"





            End With





            With DirectCast(.Cells(X, 3), Excel.Range)

                .EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 20

                .Value = "قیمت تمام شده"

            End With

            With .Range(.Cells(X, 1), .Cells(X, 3))

                .Interior.Color = Color.Yellow

            End With

            '----------------------------

            For Each P As Products In SortedProducts

                X += 1

                With DirectCast(.Cells(X, 1), Excel.Range)

                    '.EntireColumn.AutoFit()

                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                    .Value = (X - 1).ToString

                End With

                With DirectCast(.Cells(X, 2), Excel.Range)

                    '.EntireColumn.AutoFit()

                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                    .Value = P.Title

                End With

                With DirectCast(.Cells(X, 3), Excel.Range)

                    '.EntireColumn.AutoFit()

                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                    .Value = FormatNumber(Product.MaterialsSum(P.ID) + P.Fee, 0, , , TriState.True)





                End With





            Next

            ExcelApplication.Visible = True

        End With

    End Sub

    Protected Friend Shared Sub SemiFinishedProducts()

        ExcelApplication = New Excel.Application

        WorkBook = ExcelApplication.Workbooks.Add

        WorkSheet1 = WorkBook.Worksheets(1)

        With WorkBook

            DirectCast(.Worksheets(2), Excel.Worksheet).Delete()

            DirectCast(.Worksheets(2), Excel.Worksheet).Delete()

        End With

        Dim SortedProducts = SemiFinishedMaterial.SortedByTitle

        With WorkSheet1

            .Name = "لیست مواد فرآوری شده"

            .DisplayRightToLeft = True

            Dim X As Integer = 1

            ' For Column Headers (Caption)

            With DirectCast(.Cells(X, 1), Excel.Range)

                .EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 5

                .EntireRow.Font.Bold = True

                .Value = "ردیف"

            End With

            With DirectCast(.Cells(X, 2), Excel.Range)

                .EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 25

                .Value = "عنوان"





            End With





            With DirectCast(.Cells(X, 3), Excel.Range)

                .EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 20

                .Value = "قیمت"

            End With





            With .Range(.Cells(X, 1), .Cells(X, 3))

                .Interior.Color = Color.Yellow

            End With

            '----------------------------

            For Each P As Semi_FinishedMaterials In SortedProducts

                X += 1

                With DirectCast(.Cells(X, 1), Excel.Range)

                    '.EntireColumn.AutoFit()

                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                    .Value = (X - 1).ToString

                End With

                With DirectCast(.Cells(X, 2), Excel.Range)

                    '.EntireColumn.AutoFit()

                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                    .Value = P.Title

                End With

                With DirectCast(.Cells(X, 3), Excel.Range)

                    '.EntireColumn.AutoFit()

                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                    Dim A As Double

                    Dim Cost As Decimal =

                     SemiFinishedMaterial.Cost(P, A)

                    .Value = FormatNumber(Cost, 0, , , TriState.True)





                End With





            Next

            ExcelApplication.Visible = True

        End With

    End Sub

    Protected Friend Shared Sub SemiFinishedMaterials(P As Semi_FinishedMaterials, Optional Number As Integer = 1)

        ExcelApplication = New Excel.Application

        WorkBook = ExcelApplication.Workbooks.Add

        WorkSheet1 = WorkBook.Worksheets(1)

        With WorkBook

            DirectCast(.Worksheets(2), Excel.Worksheet).Delete()

            DirectCast(.Worksheets(2), Excel.Worksheet).Delete()

        End With

        With WorkSheet1

            .Name = "لیست مواد مصرفی"

            .DisplayRightToLeft = True

            Dim X As Integer = 1

            ' For Column Headers (Caption)

            With .Range(.Cells(X, 1), .Cells(X, 6))

                .Interior.Color = Color.Chartreuse





                .MergeCells = True

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                '.EntireColumn.ColumnWidth = 5

                .EntireRow.Font.Bold = True

                If Number <= 1 Then

                    .Value = String.Format("{0} {1}", "مواد مصرفی", P.Title)

                Else

                    .Value = String.Format("{0} {1} {2} {3} {4}", "مواد مصرفی", P.Title, "در تعداد", Number, "عدد")

                End If

            End With

            X = 2

            With DirectCast(.Cells(X, 1), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 5

                .EntireRow.Font.Bold = True

                .Value = "ردیف"

            End With

            With DirectCast(.Cells(X, 2), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 25

                .Value = "عنوان"





            End With

            With DirectCast(.Cells(X, 3), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 20

                .Value = "قیمت"

            End With

            With DirectCast(.Cells(X, 4), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 10

                .Value = "مقدار"

            End With

            With DirectCast(.Cells(X, 5), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 10

                .Value = "واحد"

            End With





            With DirectCast(.Cells(X, 6), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 20

                .Value = "جمع"

            End With

            With .Range(.Cells(X, 1), .Cells(X, 6)).Interior

                .Color = Color.Yellow

            End With

            '----------------------------

            Dim RawMaterial As RawMaterials





            Dim PMaterials = Global.PricingStrategy3.SemiFinishedMaterial.Mater  ials(P.ID)

            Dim Title As String = ""

            Dim Price As String = ""

            Dim Count As String = ""

            Dim Sum As String = ""

            Dim Unit As String = ""

            For Each Material As Semi_FinishedMaterials_RawMaterials In PMaterials

                X += 1

                RawMaterial = GetEntry(Of RawMaterials)(Material.RawMaterial)

                Title = RawMaterial.Title

                Price = RawMaterial.Price

                Count = Material.Number * Number

                Unit = RawMaterial.unit

                Sum = (Price * Material.Number) * Number

                SetMaterialRow(WorkSheet1, X, Title, Price, Count, Unit, Sum)

            Next

            ExcelApplication.Visible = True

        End With

    End Sub

    Shared Sub SetMaterialRow(ByRef WS As Excel.Worksheet, Row As Integer,

                              Title As String, Price As Decimal,

                              Count As String, Unit As String, Sum As String)

        With WS

            With DirectCast(.Cells(Row, 1), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = (Row - 2).ToString

            End With

            With DirectCast(.Cells(Row, 2), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = Title

            End With





            ' COST

            With DirectCast(.Cells(Row, 3), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = FormatNumber(Price, 0, , , TriState.True)

            End With

            With DirectCast(.Cells(Row, 4), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = Count

            End With

            With DirectCast(.Cells(Row, 5), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = Unit

            End With





            With DirectCast(.Cells(Row, 6), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = FormatNumber(Sum, 0, , , TriState.True)

            End With

        End With

    End Sub

    Protected Friend Shared Sub Materials(P As Products, Optional Number As Integer = 1)

        ExcelApplication = New Excel.Application

        WorkBook = ExcelApplication.Workbooks.Add

        WorkSheet1 = WorkBook.Worksheets(1)

        With WorkBook

            DirectCast(.Worksheets(2), Excel.Worksheet).Delete()

            DirectCast(.Worksheets(2), Excel.Worksheet).Delete()

        End With

        With WorkSheet1

            .Name = "لیست مواد مصرفی"

            .DisplayRightToLeft = True

            Dim X As Integer = 1

            ' For Column Headers (Caption)

            With .Range(.Cells(X, 1), .Cells(X, 7))

                .Interior.Color = Color.Chartreuse





                .MergeCells = True

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                '.EntireColumn.ColumnWidth = 5

                .EntireRow.Font.Bold = True

                If Number <= 1 Then

                    .Value = String.Format("{0} {1}", "مواد مصرفی", P.Title)

                Else

                    .Value = String.Format("{0} {1} {2} {3} {4}", "مواد مصرفی", P.Title, "در تعداد", Number, "عدد")

                End If

            End With

            X = 2

            With DirectCast(.Cells(X, 1), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 5

                .EntireRow.Font.Bold = True

                .Value = "ردیف"

            End With

            With DirectCast(.Cells(X, 2), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 25

                .Value = "عنوان"





            End With

            With DirectCast(.Cells(X, 3), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 15

                .Value = "نوع"





            End With

            With DirectCast(.Cells(X, 4), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 15

                .Value = "قیمت"

            End With

            With DirectCast(.Cells(X, 5), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 10

                .Value = "مقدار"

            End With

            With DirectCast(.Cells(X, 6), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 10

                .Value = "واحد"

            End With

            With DirectCast(.Cells(X, 7), Excel.Range)

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .EntireColumn.ColumnWidth = 20

                .Value = "جمع"

            End With

            With .Range(.Cells(X, 1), .Cells(X, 7)).Interior

                .Color = Color.Yellow

            End With

            '----------------------------

            Dim RawMaterial As RawMaterials, SemiFinishedProduct As Semi_FinishedMaterials





            Dim PMaterials = Global.PricingStrategy3.Product.Materials(P.ID)

            Dim Title As String = ""

            Dim Kind As String = ""

            'Dim Price As Decimal

            Dim Count As String = ""

            Dim Sum As String = ""

            Dim Unit As String = ""

            Dim Cost As Decimal, A As Double

            For Each Material As Products_Materials In PMaterials

                X += 1

                If Material.MaterialKind = MaterialKinds.Primary Then

                    RawMaterial = GetEntry(Of RawMaterials)(Material.Material)

                    Title = RawMaterial.Title

                    Kind = "اولیه"

                    Cost = RawMaterial.Price

                    Count = String.Format("{0}", Material.Number * Number)

                    Unit = RawMaterial.unit

                    Sum = FormatNumber((Cost * Material.Number) * Number, 0, , , TriState.True)





                ElseIf Material.MaterialKind = MaterialKinds.SemiFinished Then





                    SemiFinishedProduct =

                        GetEntry(Of Semi_FinishedMaterials)(Material.Material)

                    Title = SemiFinishedProduct.Title

                    Kind = "فرآوری شده"

                    'Price = FormatNumber(SemiFinishedMaterial.RawMaterialsSum(  Material.Material) + SemiFinishedProduct.Fee, 0, , , TriState.True)





                    Count = String.Format("{0}", Material.Number * Number)

                    Unit = SemiFinishedProduct.unit

                    Cost = SemiFinishedMaterial.Cost(SemiFinishedProduct, A)

                    Sum = FormatNumber(Cost * Number, 0, , , TriState.True)

                End If

                SetMaterialRow(WorkSheet1, X, Title, Kind, Cost, Count, Unit, Sum)

            Next

            ExcelApplication.Visible = True

        End With

    End Sub

    Shared Sub SetMaterialRow(ByRef WS As Excel.Worksheet, Row As Integer,

                              Title As String, Kind As String, Price As Decimal,

                              Count As String, Unit As String, Sum As String)

        With WS

            With DirectCast(.Cells(Row, 1), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = (Row - 2).ToString

            End With

            With DirectCast(.Cells(Row, 2), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = Title

            End With

            With DirectCast(.Cells(Row, 3), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = Kind

            End With

            ' COST

            With DirectCast(.Cells(Row, 4), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = FormatNumber(Price, 0, , , TriState.True)

            End With

            With DirectCast(.Cells(Row, 5), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = Count

            End With

            With DirectCast(.Cells(Row, 6), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = Unit

            End With

            With DirectCast(.Cells(Row, 7), Excel.Range)

                '.EntireColumn.AutoFit()

                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

                .Value = Sum

            End With

        End With

    End Sub





End Class