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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
'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ą 😛
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
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
1 komentarz
Fajna stronka. Też chciałem właśnie taką zrobić w podobnym celu (przypominajki dla siebie i przy okazji dla kogoś), ale wieczny brak czasu nie pozwala. Pozdrawiam i wytrwałości życzę! Dzięki za materiał z bitmapami