neodam
Messages postés9Date d'inscriptionmardi 27 mai 2003StatutMembreDernière intervention10 avril 2008
-
7 févr. 2004 à 17:12
cs_labout
Messages postés1356Date d'inscriptionsamedi 8 décembre 2001StatutMembreDernière intervention23 octobre 2006
-
2 juin 2005 à 19:29
Salut,
j'aimerais ouvrir une feuille excel et la remplir a partir de mon prog vb.net mais je n'arrive pas a déclarer un objet excel !!!!!
je suis novice avec vb.net, alors si ce ke j'ai dit n'est pas tres juste , merci de me le dire.
cs_labout
Messages postés1356Date d'inscriptionsamedi 8 décembre 2001StatutMembreDernière intervention23 octobre 20068 7 févr. 2004 à 18:20
labout
Voici un exemple de fonction qui cree un fichier excel,
écrit dedans, met en forme et sauvegarde
Il suffit de coller le code dans un module
Les paramêtres à passer sont un Recordset, le nom du fichier excel sans extension et une variable BBol si l'on veut un total en bas des colonnes numériques
Function EtatExcel(ByRef rs As ADODB.Recordset, ByRef etat As String, ByRef bBol As Boolean) As Boolean
Dim fichier As String
Dim FrmSt As New FrmStatus ' c'est une fenetre avec un Label contenant Attente
Dim i As Integer
Dim j As Integer
Dim nb As Integer
Dim sCols As String
Dim iFields As Integer
Dim ExcelWasNotRunning As Boolean
Dim iLastcol As Integer
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = _
CType(excelBook.Worksheets(1), Excel.Worksheet)
Dim rng As Excel.Range
' Unlike the Word demo, we'll make the spreadsheet visible so you can see
' the data being entered.
excelApp.Visible = True
On Error GoTo err_Renamed
FrmSt.Show("Création de la feuille Excel en cours." & vbCrLf & "Patientez...")
iFields = rs.Fields.Count - 1
fichier = New String(Chr(0), 100)
'Get the temporary path
GetTempPath(100, fichier)
fichier = CLeft(fichier, InStr(fichier, Chr(0)) - 1)
fichier = Trim(fichier) & etat & ".XLS"
If Dir(fichier) <> "" Then
Kill(fichier)
End If
With excelApp.Application
.Workbooks.Add()
.ActiveWorkbook.SaveAs(fichier)
For i = 0 To iFields
.Cells(1, i + 1) = rs.Fields(i).Name
Next
j = 2
nb = 0
Do While Not rs.EOF
System.Windows.Forms.Application.DoEvents()
nb = nb + 1
For i = 0 To iFields
System.Windows.Forms.Application.DoEvents()
.Cells(j, i + 1) = rs.Fields(i).Value
Next
j = j + 1
rs.MoveNext()
Loop
' on autorise 104 colonnes pour le moment
For i = 1 To iFields + 1
If i <= 26 Then
sCols = Chr(64 + i) & ":" & Chr(64 + i)
Else ' 64+27-26
If i <= 52 Then
sCols = "A" & Chr(64 + i - 26) & ":" & "A" & Chr(64 + i - 26)
Else
If i <= 78 Then
sCols = "A" & Chr(64 + i - 26) & ":" & "B" & Chr(64 + i - 52)
Else
sCols = "A" & Chr(64 + i - 26) & ":" & "C" & Chr(64 + i - 78)
End If
End If
End If
rng = .Range(sCols)
rng.EntireColumn.AutoFit()
Next
If iFields + 1 <= 26 Then
sCols = Chr(65) & "1:" & Chr(64 + iFields + 1) & "1" ' A1: E1
Else
If iFields + 1 <= 52 Then
sCols = Chr(65) & "1:" & "A" & Chr(64 + iFields + 1 - 26) & "1"
Else
If iFields + 1 <= 78 Then
sCols = Chr(65) & "1:" & "B" & Chr(64 + iFields + 1 - 52) & "1"
Else
sCols = Chr(65) & "1:" & "C" & Chr(64 + iFields + 1 - 78) & "1"
End If
End If
End If
.Range(sCols).Select()
rng = .Range(sCols)
With rng.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = 1 'xlContinuous
.Weight = -4138 ' xlMedium
.ColorIndex = -4105 ' xlAutomatic
End With
With rng.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = 1 'xlContinuous
.Weight = -4138 ' xlMedium
.ColorIndex = -4105 ' xlAutomatic
End With
With rng.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = 1 ' xlContinuous
.Weight = -4138 ' xlMedium
.ColorIndex = -4105 ' xlAutomatic
End With
With rng.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = 1 ' xlContinuous
.Weight = -4138 ' xlMedium
.ColorIndex = -4105 ' xlAutomatic
End With
With rng.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
If iFields + 1 <= 26 Then
sCols = Chr(65) & "1:" & Chr(64 + iFields + 1) & nb + 1
Else
If iFields + 1 <= 52 Then
sCols = Chr(65) & "1:" & "A" & Chr(64 + iFields + 1 - 26) & nb + 1
Else
If iFields + 1 <= 78 Then
sCols = Chr(65) & "1:" & "B" & Chr(64 + iFields + 1 - 52) & nb + 1
Else
sCols = Chr(65) & "1:" & "C" & Chr(64 + iFields + 1 - 78) & nb + 1
End If
End If
End If
.Range(sCols).Select()
rng = .Range(sCols)
With rng.Borders(Excel.XlBordersIndex.xlEdgeLeft) ' left
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
With rng.Borders(Excel.XlBordersIndex.xlEdgeTop) ' top
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
With rng.Borders(Excel.XlBordersIndex.xlEdgeBottom) ' bottom
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
With rng.Borders(Excel.XlBordersIndex.xlEdgeRight) ' right
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
With rng.Borders(Excel.XlBordersIndex.xlInsideVertical) ' verticale
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
' total si bBol
If bBol Then
.Cells(nb + 2, 1) = "Total"
'
Select Case etat
Case "statistiques dr-drf"
sCols = Chr(65 + rs.Fields.Count - 1) & "2" & ":" & Chr(65 + rs.Fields.Count - 1) & nb + 1
.Cells(nb + 2, rs.Fields.Count) = "=Somme(" & sCols & ")"
sCols = Chr(65) & nb + 2 & ":" & Chr(65 + rs.Fields.Count - 1) & nb + 2
Case "coutdurisque"
sCols = Chr(65 + rs.Fields.Count - 3) & 2 & ":" & Chr(65 + rs.Fields.Count - 3) & nb + 1 ' &
.Cells(nb + 2, rs.Fields.Count - 2) = "=Somme(" & sCols & ")"
sCols = Chr(65 + rs.Fields.Count - 2) & 2 & ":" & Chr(65 + rs.Fields.Count - 2) & nb + 1
.Cells(nb + 2, rs.Fields.Count - 1) = "=Somme(" & sCols & ")"
Case "prodmensuelle"
sCols = Chr(65 + rs.Fields.Count - 3) & 2 & ":" & Chr(65 + rs.Fields.Count - 3) & nb + 1
.Cells(nb + 2, rs.Fields.Count - 2) = "=Somme(" & sCols & ")"
sCols = Chr(65 + rs.Fields.Count - 4) & 2 & ":" & Chr(65 + rs.Fields.Count - 4) & nb + 1
.Cells(nb + 2, rs.Fields.Count - 3) = "=Somme(" & sCols & ")"
End Select
If iFields <= 26 Then
sCols = Chr(65) & nb + 2 & ":" & Chr(65 + iFields) & nb + 2
Else
If iFields <= 52 Then
sCols = Chr(65) & nb + 2 & ":" & "A" & Chr(65 + iFields - 26) & nb + 2
Else
If iFields <= 78 Then
sCols = Chr(65) & nb + 2 & ":" & "B" & Chr(65 + iFields - 52) & nb + 2
Else
sCols = Chr(65) & nb + 2 & ":" & "C" & Chr(65 + iFields - 78) & nb + 2
End If
End If
End If
.Range(sCols).Select()
rng = .Range(sCols)
With rng.Borders(Excel.XlBordersIndex.xlEdgeTop) ' top
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
With rng.Borders(Excel.XlBordersIndex.xlEdgeBottom) ' bottom
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
With rng.Borders(Excel.XlBordersIndex.xlEdgeRight) ' right
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
With rng.Borders(Excel.XlBordersIndex.xlInsideVertical) ' verticale
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
End If
.Application.ActiveWorkbook.Save()
End With
MsgBox("Pressez une touche et Excel va se fermer Excel" & vbCrLf & "Votre fichier s'appelle " & fichier, MsgBoxStyle.Information)
excelApp.Application.Quit()
Exit Function
err_Renamed:
TraceError("Module DocExcel Fonction EtatExcel", Err.Description, Err.Number, Err.Source)
MsgBox("Erreur: " & Err.Description, MsgBoxStyle.Critical)
Err.Clear()
excelApp.Application.Quit()
excelApp = Nothing
FrmSt.Close()
FrmSt.Dispose()
Exit Function
End Function