- 最後登錄
- 2022-11-24
- 在線時間
- 271 小時
- 註冊時間
- 2008-9-2
- 閱讀權限
- 20
- 精華
- 0
- UID
- 4896884
- 帖子
- 541
- 積分
- 212 點
- 潛水值
- 13707 米
| 若對尊貴或贊助會員有任何疑問,歡迎向我們查詢。我們的即時通或MSN: admin@eyny.com 這是一個將 DataGridView 相關的一些常用到的功能,作一些小小分享,其中就包括轉成EXCEL檔案、列印等。
其中也有一些平常設計程式會用到的小技巧,諸如資料模組的設計,擴充法、資料型別的轉換等等,給大家參考。
這是Form1.cs- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Data;
- using System.Drawing;
- using System.Drawing.Printing;
- using System.IO;
- using System.Linq;
- using System.Runtime.InteropServices;
- using System.Text;
- using System.Threading.Tasks;
- using System.Windows.Forms;
- using Excel = Microsoft.Office.Interop.Excel;
- namespace ListToDataGridView
- {
- public partial class Form1 : Form
- {
- //通常定義欄位都是以private為主
- private List<PersonModel> person;
- private string toExcelString = "";
- private Timer timer;
- //定義常數
- private const int WM_CLOSE = 0x10;
- public Form1()
- {
- InitializeComponent();
- }
- //API
- [DllImport("User32.dll", EntryPoint = "FindWindow", SetLastError = true, CharSet = CharSet.Auto)]
- public static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
- [DllImport("User32.dll", EntryPoint = "PostMessage", SetLastError = true, CharSet = CharSet.Auto)]
- public static extern bool PostMessage(IntPtr hWnd, uint Msg, UIntPtr wParam, IntPtr lParam);
- private void Form1_Load(object sender, EventArgs e)
- {
- //建構時一次加入3筆資料
- person = new List<PersonModel>()
- {
- new PersonModel
- {
- Name = "王大維",
- ID = "B122111111",
- Birthday = "19680705",
- TEL = "07-23593512",
- CellPhone = "0982-333-333",
- Address = "高雄市苓雅區中華西路二段451號" ,
- EMail = "daviwang@msa.hinet.net"
- },
- new PersonModel
- {
- Name = "姜中華",
- ID = "C122221221",
- Birthday = "19771025",
- TEL = "04-23257066",
- CellPhone = "0952-666-666",
- Address = "台中市北屯區中西三路1026號",
- EMail = "hwa661025888@yahoo.com.tw"
- },
- new PersonModel
- {
- Name = "張小明",
- ID = "A122331331",
- Birthday = "19780630",
- TEL = "02-25805632",
- CellPhone = "0988-999-999",
- Address = "台北市大安區復興南路一段203號10樓之16",
- EMail = "ming53621107@gmail.com"
- }
- };
- //或是後來再加入1筆資料
- person.Add(new PersonModel
- {
- Name = "李維勳",
- ID = "T122551551",
- Birthday = "19750630",
- TEL = "02-23231122",
- CellPhone = "0938-777-777",
- Address = "台北市松山區信義路二段300號11樓之1",
- EMail = "waishin@dodogogo.com"
- });
-
- //實際應用時也許會像這樣
- /*
- person.Add(new PersonModel
- {
- Name = textBox1.Text,
- ID = textBox2.Text,
- Birthday = textBox3.Text,
- TEL = textBox4.Text,
- CellPhone = textBox5.Text,
- Address = textBox6.Text,
- EMail = textBox7.Text
- }); */
- //=============================================================
- //這段程式碼是將 List 放到 dataGridView1 裡
- //這裡我在 ExtensionUtility.cs 這個類別裡寫的一個擴充方法
- dataGridView1.DataSource = person.ToBindingSource<PersonModel>();
- //=============================================================
- //=============================================================
- //設定欄位寬度,這段也可以放到dataGridView1_DataBindingComplete裡面
- for (int i = 0; i < dataGridView1.ColumnCount; i++)
- {
- dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
- }
- //=============================================================
- }
- //匯出EXCEL檔案功能按鈕
- private void toExcelButton_Click(object sender, EventArgs e)
- {
- if (toExcelString == "") return;
- //為了避免相互干擾,會強制關閉執行中EXCEL
- initailExcel();
- //所以要確認其它開啟的EXCEL檔案已經存檔並關閉。
- Excel.Application xlApplication = new Excel.Application();
- Excel.Workbook workBook = null;
- Excel.Worksheet first_sheet = null;
- Excel.Range tmprng = null;
- string xlfilename = "";
- FolderBrowserDialog fd = new FolderBrowserDialog();
- if (fd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
- {
- if (fd.SelectedPath.Substring(fd.SelectedPath.Length-1, 1) == @"\")
- //如果是選根目錄
- xlfilename = fd.SelectedPath + @"(" + convertDate(DateTime.Now) + ")-個人資料(明細表).xlsx";
- else
- //選子目錄
- xlfilename = fd.SelectedPath + @"\(" + convertDate(DateTime.Now) + ")-個人資料(明細表).xlsx";
- }
- else
- {
- return;
- }
- try
- {
- //EXCEL檔案如果已經存在就直接刪除
- if (File.Exists(xlfilename)) { File.Delete(xlfilename); }
- //建立新的EXCEL檔案
- workBook = xlApplication.Workbooks.Add();
- first_sheet = workBook.Sheets[1] as Excel.Worksheet;
- //將文字複製到剪貼簿
- Clipboard.SetText(toExcelString, TextDataFormat.UnicodeText);
- //將C到E欄和G欄設定為文字格式並靠右對齊
- tmprng = first_sheet.Range["C:E"];
- tmprng.NumberFormat = "@";
- tmprng.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
- //在A1貼上剪貼簿上的內容
- first_sheet.Paste(first_sheet.Range["A1", Type.Missing], false);
- first_sheet.Name = "服務事項明細表";
- //設定自動欄寬
- first_sheet.Cells.EntireColumn.AutoFit();
- //設定自動篩選
- tmprng = first_sheet.get_Range("A1", "A1");
- tmprng.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
- //不詢問直接覆蓋原有檔案
- xlApplication.DisplayAlerts = false;
- //存檔為 EXCEL 2007 格式
- workBook.SaveAs(xlfilename, Excel.XlFileFormat.xlWorkbookDefault);
- }
- catch (Exception ex)
- {
- //這一段是秀出錯誤訊息並且5秒鐘後自動關閉
- StartTimerKillMessageBox(5);
- MessageBox.Show(ex.Message + ex.ToString(), "MessageBox");
- }
- finally
- {
- //釋放掉一些資源的程式碼
- NAR(tmprng);
- NAR(first_sheet);
- NAR(workBook);
- xlApplication.Quit();
- NAR(xlApplication);
- GC.Collect();
- if (File.Exists(xlfilename)) MessageBox.Show("匯出成功!!");
- }
- }
- //列印功能按鈕
- private void toPrinter_Click(object sender, EventArgs e)
- {
- if (toExcelString == "") return;
- string tmpstr = "";
- string[] ss = System.Text.RegularExpressions.Regex.Split(toExcelString, "\r\n");
- foreach (string s in ss)
- { //以下這段程式是將文字稍作排版
- if (s.Length < 5) continue;
- string[] sv = System.Text.RegularExpressions.Regex.Split(s, "\t");
- if (sv.Length == 7)
- {
- tmpstr += sv[0] + new String(' ', 12 - ChtStr_Lenght(sv[0]));
- tmpstr += sv[1] + new String(' ', 11 - ChtStr_Lenght(sv[1]));
- tmpstr += sv[2] + new String(' ', 11 - ChtStr_Lenght(sv[2]));
- tmpstr += sv[3] + new String(' ', 16 - ChtStr_Lenght(sv[3]));
- tmpstr += sv[4] + new String(' ', 16 - ChtStr_Lenght(sv[4]));
- tmpstr += sv[5] + new String(' ', 40 - ChtStr_Lenght(sv[5]));
- tmpstr += sv[6] + new String(' ', 28 - ChtStr_Lenght(sv[6])) + "\r\n";
- }
- }
- PrintDocument p = new PrintDocument();
- //把版面設定成橫印
- p.DefaultPageSettings.Landscape = true;
- p.PrintPage += delegate(object sender1, PrintPageEventArgs e1)
- { //這個列印程式只能印出第一頁,如果要印出多頁,這段程式要做修改
- e1.Graphics.DrawString(tmpstr, new Font("標楷體", 11),
- new SolidBrush(Color.Black),
- new RectangleF(25, 50, p.DefaultPageSettings.PrintableArea.Height,
- p.DefaultPageSettings.PrintableArea.Width));
- };
- try
- {
- p.Print();
- }
- catch (Exception ex)
- {
- throw new Exception("列印出錯:", ex);
- }
- }
- private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
- {
- //每次DataBinding完成時都將person這個List轉成PersonExcelModel這個模組型態(LINQ語法)
- //並且輸出成以TAB相隔的字串,方便貼到EXCEL工作表上
- //其實這一整段基本上是可以放在Button_Click裡面的
- toExcelString = "姓名\t證號\t生日\t電話\t手機\t地址\t電郵\r\n";
- //這個是LINQ語法,我個人覺得是很好用東東
- //=============================================================
- var personexcel = from p in person
- group p by new {
- p.Name,
- p.ID,
- p.Birthday,
- p.TEL,
- p.CellPhone,
- p.Address,
- p.EMail
- } into rs
- select new PersonExcelModel()
- {
- 姓名 = rs.Key.Name,
- 證號 = rs.Key.ID,
- 生日 = rs.Key.Birthday,
- 電話 = rs.Key.TEL,
- 手機 = rs.Key.CellPhone,
- 地址 = rs.Key.Address,
- 電郵 = rs.Key.EMail
- };
- //=============================================================
- //所以personexcel現在已經是PersonExcelModel這種形態了
- foreach (var item in personexcel)
- {
- toExcelString += item.ToString(); //這裡應用的就是之前複寫的ToString()方法
- }
- }
- //啟動關閉訊息框
- public void StartTimerKillMessageBox(int sec, string msg = "MessageBox")
- {
- timer = new Timer();
- timer.Interval = sec * 1000; //等sec秒
- timer.Tag = msg;
- timer.Tick += new EventHandler(Timer_CloseMessageBox);
- timer.Start();
- }
- public void Timer_CloseMessageBox(object sender, EventArgs e)
- {
- KillMessageBox(Convert.ToString(((Timer)sender).Tag));
- //停止Timer
- ((Timer)sender).Stop();
- }
- public void KillMessageBox(string msg)
- {
- //依MessageBox的標題,找出MessageBox的視窗
- IntPtr ptr = FindWindow(null, msg);
- if (ptr != IntPtr.Zero)
- {
- //找到則關閉MessageBox視窗
- PostMessage(ptr, WM_CLOSE, UIntPtr.Zero, IntPtr.Zero);
- }
- }
- //傳回自訂格式的日期
- public string convertDate(DateTime dt)
- {
- return dt.Year.ToString("0000") + dt.Month.ToString("00") + dt.Day.ToString("00");
- }
- //傳回有中文的字串的真實長度
- public int ChtStr_Lenght(string a_SrcStr)
- {
- byte[] l_byte = System.Text.Encoding.Default.GetBytes(a_SrcStr);
- return l_byte.Length;
- }
- //釋放占用的資源
- public void NAR(object sender)
- {
- try
- {
- if (sender != null)
- {
- while (Marshal.ReleaseComObject(sender) > 0) ;
- }
- }
- finally
- { sender = null; }
- }
- public void initailExcel()
- {
- //檢查PC有無Excel在執行,有的話強制關閉。
- foreach (var item in System.Diagnostics.Process.GetProcesses())
- {
- if (item.ProcessName.ToUpper() == "EXCEL")
- {
- item.Kill();
- item.WaitForExit();
- }
- }
- }
- }
- }
複製代碼 這是DataModel.cs,用來設計資料的模組- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- namespace ListToDataGridView
- {
- class PersonModel : IEquatable<PersonModel>
- {
- public string Name { get; set; }
- public string ID { get; set; }
- public string Birthday { get; set; }
- public string TEL { get; set; }
- public string CellPhone { get; set; }
- public string Address { get; set; }
- public string EMail { get; set; }
- //IEquatable這個介面需要實作Equals這個方法
- public bool Equals(PersonModel other)
- {
- return this.ID.Equals(other.ID);
- }
- }
- class PersonExcelModel : IEquatable<PersonExcelModel>
- {
- public string 姓名 { get; set; }
- public string 證號 { get; set; }
- public string 生日 { get; set; }
- public string 電話 { get; set; }
- public string 手機 { get; set; }
- public string 地址 { get; set; }
- public string 電郵 { get; set; }
- //IEquatable這個介面需要實作Equals這個方法
- public bool Equals(PersonExcelModel other)
- {
- return this.證號.Equals(other.證號);
- }
- //再寫一個多載方法可以和PersonModel做比對
- public bool Equals(PersonModel other)
- {
- return this.證號.Equals(other.ID);
- }
- //這裡我們複寫ToString()這個方法,以便讓它可以符合輸出到EXCEL的需求
- public override string ToString()
- {
- return this.姓名 + "\t" + this.證號 + "\t" + this.生日
- + "\t" + this.電話 + "\t" + this.手機
- + "\t" + this.地址 + "\t" + this.電郵 + "\r\n";
- }
- }
- }
複製代碼 這是ExtensionUtility.cs,裡面裝了一個把List轉成BindingSource的擴充方法- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- using System.Windows.Forms;
- namespace ListToDataGridView
- {
- public static class ExtensionUtility
- {
- //擴充方法,有興趣自己找資料研究,可以先看看MSDN的說明
- //https://msdn.microsoft.com/zh-tw/library/bb383977.aspx
- public static BindingSource ToBindingSource<TSource>(this IList<TSource> data)
- {
- var bindingList = new BindingList<TSource>(data);
- return new BindingSource(bindingList, null);
- }
- }
- }
複製代碼 請大家不吝指教... |
|