Import danych z Excela do DataGridView, zapis do Excela

Przedstawię wam sposób na import danych z Excela do DataGridview i eksport danych do pliku Excel. Dany system można wykorzystać dwojako, ja wykorzystałem taki system do importu danych z Excela, następnie ich obróbkę i zapis do bazy danych.

Nasza aplikacja będzie miała możliwość pobierania danych, wyświetlania ich w DataGridView, będziemy mogli poddać dane specjalnej obróbce, a następnie zapisać je w pliku Excel. Wykorzystamy do tego dwie formy, pierwsza będzie głównym oknem naszego programu, druga będzie wyświetlała dostępne arkusze pliku. Aplikacja wykorzystuje bibliotekę ”Microsoft.Office.Interop.Excel”

Którą znajdzie każdy, kto ma zainstalowanego Office. Jeśli nie masz Office i korzystacie z darmowych odpowiedników jak LibreOffice lub OpenOffice nie przejmuj się, musicie doinstalować sobie składnik:

Microsoft Access Database Engine 2016

Gdy go dodamy, będziemy mogli wybrać potrzebny składnik z listy COM:

Zaczniemy standardowo od tabeli elementów:

Forma, w której wybierzemy arkusz, z którego pobierzemy dane:

Zanim zaczniemy omówienie naszej aplikacji, najpierw musimy przygotować sobie class’ę odpowiedzialną za obsługę CheckBox’ów w nagłówkach kolumn. Nie będziemy się skupiać na tym elemencie, gdyż już był on omawiany tutaj: numerowanie-dodawanie-columnheader

dodajemy class’ę 'ColumnsHeaderCheckbox’  wraz z kodem:

Public Class ColumnsHeaderCheckbox

    Public listaCheckBoxow As New List(Of CheckBox)

    Public Sub checkBoxControll(ByVal DGV As DataGridView)
        'dodajemy dynamiczną zmiane położenia naszego checkboxa

        'wychwytuje zmniejszony element po lewej stronie
        Dim pierwszyElement As Boolean = True
        'pętla ustawiająca lokalizację checkboxów
        For i As Integer = 0 To listaCheckBoxow.Count - 1
            'nasz nagłówek
            Dim rect As Rectangle = DGV.GetCellDisplayRectangle(DGV.Columns(i).Index, -1, True)
            'lokalizacja checkboxa
            Dim Pt As New Point
            Pt.Y = 3 'ustawienie z góry
            'pętla sprawdza czy nagłówki są widoczne w całości (zmniejszone o wielkość checkboxu)
            If rect.Width >= DGV.Columns(i).Width - 20 Then
                If rect.Location.X > 20 Then
                    listaCheckBoxow(i).Visible = True
                    listaCheckBoxow(i).BackColor = Color.Red
                    If pierwszyElement = True Then
                        Pt.X = rect.Location.X + rect.Width - 20
                    Else
                        Pt.X = rect.Location.X + DGV.Columns(i).Width - 20
                    End If
                    pierwszyElement = False
                Else
                    listaCheckBoxow(i).Visible = False
                End If
            Else
                If rect.Location.X > 20 Then
                    If pierwszyElement = True Then
                        If rect.Location.X > 0 Then
                            'określa czy pierwszy nagłówek jest większy od wielkości Checkboxa 
                            '(czy się zmieści)
                            If rect.Width > 20 Then
                                listaCheckBoxow(i).Visible = True
                                listaCheckBoxow(i).BackColor = Color.Yellow
                                Pt.X = rect.Location.X + rect.Width - 20
                                pierwszyElement = False
                            Else
                                listaCheckBoxow(i).Visible = False
                            End If
                        End If
                    Else
                        listaCheckBoxow(i).Visible = False
                    End If
                Else
                    listaCheckBoxow(i).Visible = False
                End If
            End If
            'ustawia lokalizację obiektów
            listaCheckBoxow(i).Location = Pt
        Next
    End Sub

    Public Sub addCheckBoxesToColumnHeaders(ByVal DGV As DataGridView)
        For i As Integer = 0 To DGV.ColumnCount - 1
            'dla każdego nagłówka kolumny
            'jeśli nagłówek będzie niewidoczny wtedy rect.x będzie <0
            'a rect.Width=0
            Dim rect As Rectangle = DGV.GetCellDisplayRectangle(DGV.Columns(i).Index, -1, True)
            rect.Y = 3
            rect.X = rect.Location.X + rect.Width - 20
            'tworzymy nowy checkbox i nadajemu mu parametry startowe
            Dim NaglowekKolumny = New CheckBox()
            NaglowekKolumny.BackColor = Color.White
            NaglowekKolumny.Name = DGV.Columns(i).Name.ToString
            NaglowekKolumny.CheckAlign = ContentAlignment.MiddleCenter
            NaglowekKolumny.Size = New Size(18, 18)
            NaglowekKolumny.Checked = False
            'jeśli nie dodamy tej pętli, check boxy pojawią się w dziwnych miejscach
            'wyświetlimy checkboxy tylko dla widocznych kolumn
            If rect.Width = DGV.Columns(i).Width Then
                NaglowekKolumny.Location = rect.Location
            Else
                NaglowekKolumny.Visible = False
            End If
            'dodajemy wszystkie checkboxy do datagridview
            'będziemy zmieniać tylko ich położenie i opcje Visible
            DGV.Controls.Add(NaglowekKolumny)
            'dodajemy mu jeszcze jakąś fonkcjonalność, aby nie było, że nic nie robią
            listaCheckBoxow.Add(NaglowekKolumny)
        Next
    End Sub

End Class

Mamy już przygotowaną formę, zaczniemy od wczytania naszego pliku.

Public Class Form1
    'rodzaj połączenia
    Dim connection As System.Data.OleDb.OleDbConnection
    'lista nazw arkuszy
    Public sheetsList As New List(Of String)
    'ścieżka do pliku z którego pobierzemy dane
    Dim filePath As String
    'lista checkboxów w nagłówkach
    Dim ColumnsHeaderCheckbox As New ColumnsHeaderCheckbox
    'miejsce zapisu nowego pliku excel
    Dim savePath As String

    Private Sub ChooseFile_Click(sender As Object, e As EventArgs) Handles ChooseFile.Click
        'określamy rodzaj pliku, zmniejszy to ilość wyświetlanych elementów
        'wyfiltruje tylko pliki excel
        fileDialog.Filter = "csv files|;*.xls;*.xlsx"
        fileDialog.Title = "Select a Excel file"
        fileDialog.FileName = ""

        Try
            With fileDialog
                If .ShowDialog() = DialogResult.OK Then
                    'przekazujeny ścieżke do pliku, przyda on się w momencie listowania arkuszy
                    filePath = .FileName
                    'definiujemy połączenie
                    connection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                                       filePath + ";Extended Properties=Excel 12.0;")
                    'otwieramy połączenie
                    connection.Open()

                    Dim dtXlsSchema As DataTable = Nothing
                    dtXlsSchema = connection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables,
                      New Object() {Nothing, Nothing, Nothing, "TABLE"})
                    'ładujemy nazwy arkuszy do listy
                    sheetsList.Clear()
                    For i = 0 To dtXlsSchema.Rows.Count - 1
                        sheetsList.Add(dtXlsSchema.Rows(i).Item("Table_Name").ToString)
                    Next
                    'zamykamy połączenie
                    connection.Close()
                    'otwieramy formę odpowiedzialną za wybór arkusza
                    Form2.Show()
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
End Class

Teraz możemy pobrać nasz plik, nic się jednak chwilowo nie stanie, potrzebujemy zdefiniować jeszcze prostą metodę wyboru arkusza w Form2:

Public Class Form2
    Private Sub Form101_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        For i As Integer = 0 To Form1.sheetsList.Count - 1
            SheetsListBox.Items.Add(Form1.sheetsList(i))
        Next
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Form1.addSheet(SheetsListBox.SelectedItem)
        Me.Close()
    End Sub
End Class

Musimy teraz zdefiniować publiczną metodę w Form1 która na podstawie wybranego arkusza pobierze jego dane i wyświetli w DataGridView:

 Public Sub addSheet(ByVal sheetName As String)
        Try
            Dim dataSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

            connection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                               filePath + ";Extended Properties=Excel 12.0;")
            connection.Open()

            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "]", connection)
            dataSet = New System.Data.DataSet
            MyCommand.Fill(dataSet)
            ExcelView.DataSource = dataSet.Tables(0)
            connection.Close()

            ColumnsHeaderCheckbox.addCheckBoxesToColumnHeaders(ExcelView)

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub

Nasza aplikacja będzie zachowywać się tak, jak tego oczekujemy, pobierze dane z wybranego arkusza i załaduje je do DataGridView:

Teraz zajmiemy się naszym przyciskiem „Formatuj dane”, często jest tak, że po zaimportowaniu danych zauważamy wiele pustych wierszy lub kolumn. Wiąże się to z tym, że nie wszystkie transformacje wykonane w Excelu będą wyglądały jednakowo w DataGridView na przykład łączenie komórek.

    'element odpowiedzialny za malowanie checkBoxów w nagłówkach kolumn
    Private Sub ExcelView_CellPainting(sender As Object, e As DataGridViewCellPaintingEventArgs) _
                                       Handles ExcelView.CellPainting

        ColumnsHeaderCheckbox.checkBoxControll(ExcelView)
    End Sub

    Private Sub FormatData_Click(sender As Object, e As EventArgs) Handles FormatData.Click
        'sprawdzamy które checkboxy są wybrane
        For i As Integer = ColumnsHeaderCheckbox.listaCheckBoxow.Count - 1 To 0 Step -1
            If ColumnsHeaderCheckbox.listaCheckBoxow(i).Checked = False Then
                ExcelView.Columns.RemoveAt(i)
            End If
        Next
        'listujemy wiersze
        For l As Integer = ExcelView.Rows.Count - 2 To 0 Step -1
            'zamieniamy dane w każdej komórkce na String i tworzymy jeden wieli tekst
            Dim test As String = ""
            For j As Integer = 0 To ExcelView.ColumnCount - 1
                test += ExcelView.Rows(l).Cells(j).Value.ToString
            Next
            'jeśli tekstu nie ma bo wszystkie komórki były puste
            If test.Trim.Length = 0 Then
                'to usuwamy wiersz
                ExcelView.Rows.Remove(ExcelView.Rows(l))
            End If
        Next
        'ukrywamy checkBoxy
        For k As Integer = 0 To ColumnsHeaderCheckbox.listaCheckBoxow.Count - 1
            ColumnsHeaderCheckbox.listaCheckBoxow(k).Visible = False
        Next
        ColumnsHeaderCheckbox.listaCheckBoxow.Clear()
    End Sub

efekt będzie następujący:

Teraz dodamy sobie ostatni element i zrobimy to z klasą 😛

    Private Sub SaveExcelFile_Click(sender As Object, e As EventArgs) Handles SaveExcelFile.Click

        SaveExcelDialog.Filter = "Excel excel|*.xlsx"
        SaveExcelDialog.Title = "Save an Excel File"
        'określamy miejsce zapisu nowego pliku
        Try
            With SaveExcelDialog
                If .ShowDialog() = DialogResult.OK Then
                    savePath = .FileName
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
        'blokujemy formę aby urzytkownik nic nie klikał podczas zapisu
        blockForm()
        'ustawiamy maksimum paska procesu
        SavingProgress.Maximum = (ExcelView.RowCount - 1) * ExcelView.ColumnCount
        'ustawiamy i odpalamy nasz BackgroundWorker
        SaveExcelBackgroundWorker.WorkerReportsProgress = True
        SaveExcelBackgroundWorker.WorkerSupportsCancellation = True
        SaveExcelBackgroundWorker.RunWorkerAsync()         '
    End Sub

    Private Sub blockForm()
        ChooseFile.Enabled = False
        FormatData.Enabled = False
        SaveExcelFile.Enabled = False
        ExcelView.Enabled = False
    End Sub

    Private Sub SaveExcelBackgroundWorker_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) _
                                                Handles SaveExcelBackgroundWorker.DoWork

        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)

        'określamy nazwę arkusza 
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        'pobieramy nagłówki kolumn je również zapiszemy w Excelu
        For k As Integer = 1 To ExcelView.Columns.Count
            xlWorkSheet.Cells(1, k) = ExcelView.Columns(k - 1).HeaderText
        Next

        'pobieramy każdą komórkę i wstawiamy ją do xlWorkSheet
        For i = 0 To ExcelView.RowCount - 2
            For j = 0 To ExcelView.ColumnCount - 1
                xlWorkSheet.Cells(i + 2, j + 1) = ExcelView(j, i).Value.ToString()
                'dodajemy jeden do naszego paska procesu
                SavingProgress.Invoke(Sub()
                                          SavingProgress.Value += 1
                                      End Sub)
            Next
        Next
        'zapisujemy, zamykamy
        xlWorkSheet.SaveAs(savePath)
        xlWorkBook.Close()
        xlApp.Quit()

        'i czyścimy
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    'Kiedy nasz BackgroundWorker zakończy proces, uruchomiona zostanie metoda:
    Private Sub SaveExcelBackgroundWorker_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) _
                                                             Handles SaveExcelBackgroundWorker.RunWorkerCompleted
        MsgBox("You can find the excel: " + savePath)
        SavingProgress.Value = 0
        unBlockForm()
    End Sub

    Private Sub unBlockForm()
        ChooseFile.Enabled = True
        FormatData.Enabled = True
        SaveExcelFile.Enabled = True
        ExcelView.Enabled = True
    End Sub

Efekt poniżej:

Teraz możemy edytować i zapisywać nasze pliki Excel 🙂

Repo do pobrania na GithHubie: https://github.com/VisualMonsters/WorkingWithExcel