Kodi:
Imports System.IO
Imports System.Data.OleDb
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\Artikujt.accdb")
cn.Open()
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT ArtikulliID, Shifra, Pershkrimi, CmimiFur, Hyrje FROM Hyrje ORDER BY Data", cn)
Dim table As New DataTable()
adapter.Fill(table)
table.Columns.Add("Shuma e Daljeve")
table.Columns.Add("Mbetja")
table.Columns.Add("Vlera e Mbetjes")
cn.Close()
DataGridView1.DataSource = table
DataGridView1.Update()
dalja()
End Sub
Private Sub dalja()
Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\Artikujt.accdb")
cn.Open()
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT ArtikulliID,Dalje FROM Dalje ORDER BY Data", cn)
Dim table As New DataTable()
adapter.Fill(table)
For Each myRow In table.Rows
Kalkulo(myRow(0), myRow(1))
Next
cn.Close()
End Sub
Private Sub Kalkulo(ByVal art As String, ByVal dalja As Integer)
For Each DataGridViewRow In DataGridView1.Rows
Dim artikulliId As Integer = CInt(DataGridView1(0, DataGridViewRow.Index).Value)
Dim cmimiFur As Double = CDbl(DataGridView1(3, DataGridViewRow.Index).Value)
Dim hyrje As Integer = CInt(DataGridView1(4, DataGridViewRow.Index).Value)
If artikulliId = art Then
If dalja >= hyrje Then
DataGridView1(5, DataGridViewRow.Index).Value = hyrje
DataGridView1(6, DataGridViewRow.Index).Value = hyrje - DataGridView1(5, DataGridViewRow.Index).Value
DataGridView1(7, DataGridViewRow.Index).Value = DataGridView1(6, DataGridViewRow.Index).Value * cmimiFur
dalja = dalja - hyrje
ElseIf dalja < hyrje And dalja <> 0 Then
DataGridView1(5, DataGridViewRow.Index).Value = dalja
DataGridView1(6, DataGridViewRow.Index).Value = hyrje - dalja
DataGridView1(7, DataGridViewRow.Index).Value = DataGridView1(6, DataGridViewRow.Index).Value * cmimiFur
dalja = hyrje - dalja
dalja = 0
ElseIf dalja = 0 Then
DataGridView1(5, DataGridViewRow.Index).Value = 0
DataGridView1(6, DataGridViewRow.Index).Value = hyrje
DataGridView1(7, DataGridViewRow.Index).Value = DataGridView1(6, DataGridViewRow.Index).Value * cmimiFur
End If
End If
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Fshij_Raportin()
Krijo_Raportin()
Try
Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\Artikujt.accdb")
cn.Open()
For Each DataGridViewRow In DataGridView1.Rows
Dim sqlStr = "INSERT INTO Raporti (ArtikulliID , Shifra, Pershkrimi, CmimiFur, Hyrje, ShumaeDaljeve, Mbetja, VleraeMbetjes) " _
& "VALUES (@ArtikulliID , @Shifra, @Pershkrimi, @CmimiFur, @Hyrje, @ShumaeDaljeve, @Mbetja, @VleraeMbetjes)"
Dim cmd As New OleDbCommand(sqlStr, cn)
cmd.Parameters.AddWithValue("@ArtikulliID", DataGridView1(0, DataGridViewRow.Index).Value)
cmd.Parameters.AddWithValue("@Shifra", DataGridView1(1, DataGridViewRow.Index).Value)
cmd.Parameters.AddWithValue("@Pershkrimi", DataGridView1(2, DataGridViewRow.Index).Value)
cmd.Parameters.AddWithValue("@CmimiFur", DataGridView1(3, DataGridViewRow.Index).Value)
cmd.Parameters.AddWithValue("@Hyrje", DataGridView1(4, DataGridViewRow.Index).Value)
cmd.Parameters.AddWithValue("@ShumaeDaljeve", DataGridView1(5, DataGridViewRow.Index).Value)
cmd.Parameters.AddWithValue("@Mbetja", DataGridView1(6, DataGridViewRow.Index).Value)
cmd.Parameters.AddWithValue("@VleraeMbetjes", DataGridView1(7, DataGridViewRow.Index).Value)
cmd.ExecuteNonQuery()
Next
cn.Close()
Catch
End Try
MessageBox.Show("Raporti u ruajt ne Databaze!")
End Sub
Private Sub Krijo_Raportin()
Try
Dim objCmd As New OleDbCommand
Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\Artikujt.accdb")
cn.Open()
Dim Sql = "CREATE TABLE Raporti ([Id] COUNTER, [ArtikulliID] INT, [Shifra] TEXT(13), [Pershkrimi] TEXT(32), [CmimiFur] TEXT(13), [Hyrje] INT, [ShumaeDaljeve] INT, [Mbetja] INT, [VleraeMbetjes] TEXT(13))"
objCmd = New OleDbCommand(Sql, cn)
objCmd.ExecuteNonQuery()
cn.Close()
Catch
End Try
End Sub
Private Sub Fshij_Raportin()
Try
Dim objCmd As New OleDbCommand
Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\Artikujt.accdb")
cn.Open()
Dim Sql = "DELETE FROM Raporti"
objCmd = New OleDbCommand(Sql, cn)
objCmd.ExecuteNonQuery()
cn.Close()
Catch
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim permbajtja As String = ""
permbajtja += ";;;;SHEMBULLI NR 1;;;;" & vbNewLine
permbajtja += ";;Data;;Emri shitesit;;;;" & vbNewLine
permbajtja += ";;;;;;;;" & vbNewLine
permbajtja += "Nr;ArtikulliID;Shifra;Pershkrimi;CmimiFur;Hyrje;Dalje;Mbetja;Vlera" & vbNewLine
Dim x As Integer = 1
For Each DataGridViewRow In DataGridView1.Rows
permbajtja += (x).ToString() + ";"
For i As Integer = 0 To DataGridView1.Columns.Count
Try
permbajtja += DataGridView1(i, DataGridViewRow.Index).Value.ToString() + ";"
Catch
End Try
Next
permbajtja += vbNewLine
x += 1
Next
Dim vlera As Double
For Each DataGridViewRow In DataGridView1.Rows
vlera += DataGridView1(7, DataGridViewRow.Index).Value
Next
permbajtja += ";;;;;;;;" & vlera & vbNewLine
Dim fs As FileStream = New FileStream("Shembull1.csv", FileMode.Create, FileAccess.Write)
Dim sw As StreamWriter = New StreamWriter(fs)
sw.Write(permbajtja)
sw.Close()
fs.Close()
MessageBox.Show("Raporti u ruajt ne Excel[CSV]!")
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Int16, j As Int16
Dim mostraXls As String = Application.StartupPath & "\mostra.xlsx"
Dim saveXls As String = Application.StartupPath & "\raporti.xlsx"
xlApp = New Excel.ApplicationClass
If My.Computer.FileSystem.FileExists(mostraXls) = True Then
xlWorkBook = xlApp.Workbooks.Open(mostraXls)
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
Else
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
End If
xlWorkSheet.Cells(1, 1) = "Data:"
xlWorkSheet.Cells(1, 2) = Now()
xlWorkSheet.Cells(1, 4) = "Shitesi:"
xlWorkSheet.Cells(1, 5) = "Kosova"
For Each header As DataGridViewColumn In Me.DataGridView1.Columns
xlWorkSheet.Cells(3, header.Index + 1) = header.HeaderText.ToString
Next
For i = 0 To DataGridView1.RowCount - 2
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 4, j + 1) = DataGridView1(j, i).Value.ToString()
Next
Next
Dim vlera As Double
For Each DataGridViewRow In DataGridView1.Rows
vlera += DataGridView1(7, DataGridViewRow.Index).Value
Next
xlWorkSheet.Cells(i + 4, j) = vlera
xlWorkSheet.SaveAs(saveXls)
xlWorkBook.Close(True, misValue, misValue)
xlApp.Quit()
releaseObject(xlWorkSheet)
releaseObject(xlWorkBook)
releaseObject(xlApp)
MessageBox.Show("Datagrid u eksportua ne Excel!")
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
MessageBox.Show("Exception Occured while releasing object " + ex.ToString())
Finally
GC.Collect()
End Try
End Sub
End Class
Projektin po e vendos pa databaze per shkak te limitit per bashkangjitje.
e ket tekstin "Kosova"
Pėrgjigju Duke Cituar
Krijoni Kontakt