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

[ROSTAM2]

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


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

[ROSTAM2]

پیدا کردم مشکلش چیه
ازونجایی که ریفرنسش
Microsoft Excel 14.0 Object Libraryهست باید نسخه .Net Framework 4 برای پروژه انتخاب بشه...

[ROSTAM2]

سلام مجدد

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

[ROSTAM2]

ی مشکل دیگه ای که بود این بود که اگر اندازه متن از خانه 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 (https://stackoverflow.com/questions/19359866/excel-auto-fit-column-in-vb-net)

[ROSTAM2]

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

[ROSTAM2]

سلام مجدد
نرم افزار برای استخراج به فایل اکسل در سیستم رایانه کاربر خطا برمیگردونه و نصب کننده 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