Hao's learning log

發表文章

[C#] 使用 ClosedXML將DataGridView 匯出至 Excel

使用 ClosedXML將DataGridView 匯出至 Excel using ClosedXML.Excel; public void ExportToExcel() { var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Sheet1"); // 匯出 DataGridView 標題 for (int col = 0; col < dataGridView1.Columns.Count; col++) { ws.Cell(1, col + 1).Value = dataGridView1.Columns[col].HeaderText; } // 匯出 DataGridView 內容 for (int row = 0; row < dataGridView1.Rows.Count; row++) { for (int col = 0; col < dataGridView1.Columns.Count; col++) { var cellValue = dataGridView1.Rows[row].Cells[col].Value; ws.Cell(row + 2, col + 1).Value = cellValue != null ? cellValue.ToString() : ""; } } // 儲存 Excel wb.SaveAs(@"C:\Temp\Data.xlsx"); MessageBox.Show("Excel 匯出完成！"); } 參考網址: By Hao★

閱讀完整內容

Gitlab安裝

本篇以ubuntu來做範例，如果是別的作業系統的安裝方法可以參考官方網站安裝教學： https://about.gitlab.com/installation/ sudo apt-get install curl openssh-server ca-certificates postfix tzdata 因為git在連線時需要使用ssh連線進行安全性驗證所以需要openssh-server ca-certificates 另外gitlab支援寄信通知或是驗證功能所以需要信箱伺服器支援需要postfix 而curl是安裝時所需要使用到安裝GitLab package server curl -sS https://packages.gitlab.com/install/repositories/gitlab/gitlab-ce/script.deb.sh | sudo bash sudo apt-get install gitlab-ce 當安裝完畢後，需配置gitlab的環境，輸入指令： sudo gitlab-ctl reconfigure 預設gitlab會安裝在80 port 假如80 port已經被佔用，可以修改port (不需要可略過) 輸入指令修改/etc/gitlab/gitlab.rb sudo nano /etc/gitlab/gitlab.rb 可以在裡面找到external_url 將其修改成自己的url與想要的port 例如： external_url 'http://10.0.2.15:81' 修改完後重新配置gitlab環境 sudo gitlab-ctl reconfigure 查詢root預設密碼 sudo more /etc/gitlab/initial_root_password 使用root登入Gitlab 參考網址: https://xenby.com/b/131-%E6%8E%A8%E8%96%A6-gitlab%E5%AE%89%E8%A3%9D%E8%88%87%E5%9F%BA%E6%9C%AC%E8%A8%AD%E5%AE%9A%E6%95%99%E5%AD%B8 By Hao★

閱讀完整內容

使用Visual Studio取得GitHub專案

開啟Visual Studio (以下範例使用Microsoft Visual Studio Community 2022 (64 位元) - Current , 版本 17.12.4) 選擇【複製存放庫】選擇【GitHub】 1.輸入GitHub儲存庫URL 2.選擇本機端存放位置 3.點選【複製】點選【複製】後，會開始將GitHub上的專案Clone下來 Clone完成後，Visual Studio會自動開啟該專案的資料夾(橘色虛線框)，但這種開啟方式沒辦法直接使用所以我們在資料夾點擊滑鼠右鍵，選擇【在檔案總管中開啟資料夾】執行專案會看到Visual Studio以專案的方式開啟，並且檔案左方會有藍色的鎖頭。(如橘色虛線框) 參考網址: By Hao★

閱讀完整內容

[VB.Net] 使用 ClosedXML將DataGridView 匯出至 Excel

使用 ClosedXML將DataGridView 匯出至 Excel Imports ClosedXML.Excel Public Sub ExportToExcel() Using stream As FileStream = New FileStream("D:\01.Project\Template.xlsx", FileMode.Open, FileAccess.ReadWrite) Using memoryStream As New MemoryStream() ' 初始化ExcelPackage，並讀取模板' Using workbook As New XLWorkbook(stream) Dim ws As IXLWorksheet = workbook.Worksheet(1) ' 假設操作第一個工作表' ' 匯出 DataGridView 標題' For col As Integer = 0 To DataGridView1.Columns.Count - 1 ws.Cell(1, col + 1).Value = DataGridView1.Columns(col).HeaderText Next ' 匯出 DataGridView 內容' For row As Integer = 0 To DataGridView1.Rows.Count - 1 For col As Integer = 0 To DataGridView1.Columns.Count - 1 Dim cellValue = DataGridView1.Rows(row).Cells(col).Value ws.Cell(row + 2, c...

閱讀完整內容

[VB.Net] 使用Distinct

VB.Net使用Distinct Imports System.Linq Dim values As New List(Of Integer) Dim val1 As Integer = 0 Dim val2 As Integer = 1 Dim val3 As Integer = 1 values.Add(val1) values.Add(val2) values.Add(val3) ' 判斷 List 是否有重複數字' If values.Count <> values.Distinct().Count() Then Console.WriteLine("發現重複數值") Else Console.WriteLine("順序未重複") End If 參考網址: By Hao★

閱讀完整內容

[VB.Net] 連接資料庫並將資料顯示在DataGride中

連接資料庫並將資料顯示在DataGride中 Imports System.Data.SqlClient Public Class Form1 Private connectionString As String = "Server=YOUR_SERVER;Database=YOUR_DATABASE;User Id=YOUR_USER;Password=YOUR_PASSWORD;" Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load Using conn As New SqlConnection(connectionString) conn.Open() Dim query As String = "SELECT TOP 10 * FROM YourTable" Dim cmd As New SqlCommand(query, conn) Dim reader As SqlDataReader = cmd.ExecuteReader() ' 直接將 DataReader 轉為 DataSource' Dim dt As New DataTable() dt.Load(reader) DataGridView1.DataSource = dt End Using End Sub End Class 參考網址: By Hao★

閱讀完整內容

[C#] 連接資料庫並將資料顯示在DataGride中

連接資料庫並將資料顯示在DataGride中 using System.Data; using Microsoft.Data.SqlClient; namespace WinFormsApp1 { public partial class Form1 : Form { //連線設定 | 代表意思 | 適用情境 //Encrypt=False | 不加密，資料明文傳輸 | 內網、測試環境、安全無虞 //Encrypt=True | 開啟SSL/TLS加密連線 | 公網連線、有敏感資料時 //TrustServerCertificate=True | 信任伺服器憑證（即使不是官方頒發） | 測試環境、內部自簽憑證 private string connectionString = "Server=YOUR_SERVER;Database=YOUR_DATABASE;User Id=YOUR_...

閱讀完整內容

[VB.Net] 使用ClosedXML匯出資料到 Excel

匯出 DataGridView 資料到 Excel Imports ClosedXML.Excel Using stream As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.ReadWrite) Using memoryStream As New MemoryStream() ' 初始化ExcelPackage，並讀取模板' Using workbook As New XLWorkbook(stream) Dim worksheet = workbook.Worksheet(1) ' 假設操作第一個工作表' #Region "塞入欄位" worksheet.Cells("A6").Value = "需填入的值" #End Region '儲存 Excel 文件到 MemoryStream' workbook.SaveAs(memoryStream) End Using Dim sfd As New SaveFileDialog() sfd.Filter = "Excel(*.xlsx)|*.xlsx" ' 設定預設檔案名稱' sfd.FileName = "檔案名稱" + ".xlsx" If (sfd.ShowDialog() = DialogResult.OK) Then '將 MemoryStream 位置重設為 0 (從頭開始讀取)' memoryStream.Position = 0 '將 Stream 轉換為 Byte 陣列並寫入檔案' My.Computer.FileSyst...

閱讀完整內容

[MS SQL] 將資料表索引進行重建(REBUILD & REORGANIZE)

將資料表索引進行重建(REBUILD & REORGANIZE) DECLARE @dbName NVARCHAR(128) SET @dbName = DB_NAME() DECLARE @schemaName NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @indexName NVARCHAR(128) DECLARE @indexId INT DECLARE @fragPercent FLOAT DECLARE @sql NVARCHAR(MAX) -- 迴圈檢查每個索引的碎片程度 DECLARE indexCursor CURSOR FOR SELECT s.name AS schemaName, t.name AS tableName, i.name AS indexName, i.index_id AS indexId, ips.avg_fragmentation_in_percent AS fragPercent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id INNER JOIN sys.tables t ON i.object_id = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE ips.avg_fragmentation_in_percent > 10 -- 篩選需要維護的索引 AND i.type IN (1, 2) -- 只處理 Clustered 和 Non-Clustered 索引 AND i.is_disabled = 0 -- 忽略已停用的索引 ORDER BY tableName OPEN indexCursor FETCH NEXT FROM indexCursor INTO @schemaName, @tableName...

閱讀完整內容

ZebraDesigner 3 產生的 .prn檔案亂碼

ZebraDesigner 擴充字體步驟安裝 Zebra Setup Utilities 安裝過程皆下一步即可下載印表機字體字體下載完後，編輯器上面就能選到新的字體，且使用列印至檔案所產出的.prn就不會亂碼。參考網址: https://blog.csdn.net/lixiaojie15523694746/article/details/103991751 By Hao★

閱讀完整內容

[MS SQL] 查詢所有Procedure

查詢所有Procedure --MS SQL使用 SELECT name AS ProcedureName, create_date AS 建立時間, modify_date AS 異動時間 FROM sys.procedures WHERE is_ms_shipped = 0 -- 只顯示使用者自訂的預存程序 ORDER BY ProcedureName; --通用版本(尚未測試) SELECT ROUTINE_TYPE, ROUTINE_SCHEMA AS SchemaName, ROUTINE_NAME AS ProcedureName, CREATED AS 建立時間, LAST_ALTERED AS 最後異動時間 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' -- 只顯示預存程序 ORDER BY ROUTINE_NAME; 參考網址: By Hao★

閱讀完整內容

[MS SQL] Procedure 最後執行時間

Procedure最後執行時間 SELECT OBJECT_NAME(object_id) AS ProcedureName, last_execution_time AS 最後執行時間 FROM sys.dm_exec_procedure_stats WHERE OBJECT_NAME(object_id) <> '' ORDER BY ProcedureName; 參考網址: By Hao★

閱讀完整內容

[MS SQL] 資料表使用紀錄查詢

查詢資料表使用紀錄 SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update AS 最後更新時間, last_user_seek AS 最後搜尋時間, last_user_scan AS 最後掃描時間 FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID() -- 當前數據庫 AND OBJECT_ID = OBJECT_ID('TableName') -- 指定資料表名稱 ORDER BY TableName; 參考網址: By Hao★

閱讀完整內容

[VB.Net] UI介面多國語系切換功能

UI介面多國語系切換功能 Imports System.ComponentModel Imports System.Globalization Imports System.Resources 'UI介面語系轉換 Public Class C_UILanguage Private systemResourceManager As ResourceManager Private errorResourceManager As ResourceManager Dim CRM As System.ComponentModel.ComponentResourceManager Public Sub New() systemResourceManager = New ResourceManager("專案名稱.Resources", GetType(C_UILanguage).Assembly) errorResourceManager = New ResourceManager("專案名稱.Resources.ErrorMessages", GetType(C_UILanguage).Assembly) End Sub Public Sub _Main(ByVal _Form As Windows.Forms.Form) '檢查引數 If _Form Is Nothing Then _Form.Text = "Form" Throw New ArgumentNullException(_Form.Text) End If CRM = Nothing '切換介面顯示的語言 Try CRM = New ComponentResourceManager(_Form.GetType) ' 設置表單的標題 CRM.ApplyResources(_Form, "$this...

閱讀完整內容

[VB.Net] 匯出 DataGridView 資料到 Excel

匯出 DataGridView 資料到 Excel Imports System.Runtime.InteropServices Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click ' 檢查DataGridView是否有數據' If dgv1.Rows.Count = 0 Then MessageBox.Show("沒有資料可以匯出", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Hand) Return End If ' 檢查是否安裝Excel' If Not IsExcelInstalled() Then MessageBox.Show("這台電腦沒有安裝 Office Excel，無法繼續執行！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Hand) Return End If Try ' 開始匯出資料到 Excel' ExportDataGridViewToExcel(dgv1) Catch ex As Exception MessageBox.Show(ex.ToString, "匯出 Excel 發生錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub ' 檢查是否安裝Excel' Private Function IsExcelInstalled() As Boolean Try Dim excelApp As New Excel.Application() If excelApp IsNot Nothing Then excelApp.Quit() ...

閱讀完整內容

[MS SQL] 使用 PIVOT 扭轉資料，由直列轉為橫向資料

PIVOT使用 --【簡易版】 SELECT * FROM ( SELECT l.MasterLangId, l.LangType, l.ShowText FROM dbo.Lang l ) t PIVOT ( -- 設定彙總欄位及方式 MAX(ShowText) -- 設定轉置欄位，並指定轉置欄位中需彙總的條件值作為新欄位 FOR LangType IN ([zh-TW], [zh-CN], [en-US]) ) p; --【動態生成欄位】 -- 使用 FOR XML PATH 動態生成轉置列清單 DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX); SELECT @cols = STUFF(( SELECT ',' + QUOTENAME(LangType) FROM ( SELECT DISTINCT LangType FROM dbo.Lang ) AS Sub ORDER BY CASE WHEN LangType = 'zh-TW' THEN 1 WHEN LangType = 'zh-CN' THEN 2 ELSE 3 END, LangType FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); -- 動態 PIVOT 查詢 SET @query = ' SELECT * FROM ( SELECT l.MasterLangId, l.LangType, l.ShowText FROM dbo.Lang l ) t PIVOT ( MAX(ShowText) FOR LangType IN (' + @cols + ') ) p; '; -- 執行動態 SQL EXEC sp_executesql @query; 參考網址: https://dotb...

閱讀完整內容

[MS SQL] SQL Server 寄送郵件(sp_send_dbmail)

sp_send_dbmail使用 EXEC msdb.dbo.sp_send_dbmail @profile_name='MES_MAIL', @recipients='eMail Address', @blind_copy_recipients='eMail Address', @subject = '主旨', @body = '內容', @body_format = 'HTML'; SELECT * FROM msdb.dbo.sysmail_allitems --查詢寄送的郵件記錄參考網址: https://vito-note.blogspot.com/2013/06/database-mail.html https://ithelp.ithome.com.tw/articles/10198042?sc=pt https://learn.microsoft.com/zh-tw/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver16 https://blog.csdn.net/Laaguid/article/details/89671396 By Hao★

閱讀完整內容

[MS SQL] 查詢StoredProcedure中的Key Word

查詢StoredProcedure中的Key Word SELECT name AS StoredProcedureName, OBJECT_DEFINITION(OBJECT_ID(name)) AS ProcedureDefinition FROM sys.procedures WHERE upper(OBJECT_DEFINITION(OBJECT_ID(name))) LIKE '%KEY_WORD%' ORDER BY name; 參考網址: By Hao★

閱讀完整內容

[C#] 取得Guid

取得Guid using System; Console.WriteLine(Guid.NewGuid()); 參考網址: https://learn.microsoft.com/zh-tw/dotnet/api/system.guid.newguid?view=net-7.0 https://blog.csdn.net/WuLex/article/details/109571681 By Hao★

閱讀完整內容

[C#] Stream.CopyTo 方法使用

Stream.CopyTo 方法使用 //創建新的stream MemoryStream destination = new MemoryStream(); using (FileStream source = new FileStream("C:\temp\Machine.jpg", FileMode.Open)) { //將來源source,複製到目的地destination. source.CopyTo(destination); } 參考網址: https://learn.microsoft.com/zh-tw/dotnet/api/system.io.stream.copyto?view=net-7.0 錯誤案例: //創建新的stream MemoryStream destination = new MemoryStream(); //來源端stream FileStream source = new FileStream("C:\temp\Machine.jpg", FileMode.Open, FileAccess.Read); destination.CopyTo(source); //錯誤訊息System.NotSupportedException: 'Stream does not support writing.' //原因:因為source是唯讀的，且來源端與目的地位置應交換參考網址: https://learn.microsoft.com/zh-tw/dotnet/api/system.notsupportedexception?view=net-7.0 By Hao★

閱讀完整內容

PostgreSQL 資料庫安裝

至官網下載版本13.4並執行安裝程式將安裝目錄改成 D:\PostgreSQL\13 取消 Stack Builder 資料安裝預設目錄D:\PostgreSQL\13\data 設定資料庫管理員帳號 postgres 的密碼預設Port為5432 資料庫安裝中安裝完成使用GUI工具PgAdmin連線到資料庫開始選單 -> PostgreSQL 13 -> 點選 pgAdmin4 首次執行需設定一組pgAdmin的新密碼 (非資料庫管理員帳號 postgres 的密碼) 點選 Servers 輸入資料庫管理員帳號 postgres 的密碼，點選OK 連線成功參考網址: PostgreSQL - 安裝教學 By Hao★

閱讀完整內容