datagridview-export-excel ソースコード全文
DataGridView_Export_Excel.slnx
<Solution> <Project Path="DataGridView_Export_Excel/DataGridView_Export_Excel.csproj" /></Solution>DataGridView_Export_Excel/DataGridView_Export_Excel.csproj
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup> <OutputType>WinExe</OutputType> <TargetFramework>net10.0-windows</TargetFramework> <Nullable>enable</Nullable> <UseWindowsForms>true</UseWindowsForms> <ImplicitUsings>enable</ImplicitUsings> </PropertyGroup>
<ItemGroup> <COMReference Include="Microsoft.Office.Interop.Excel"> <WrapperTool>tlbimp</WrapperTool> <VersionMinor>9</VersionMinor> <VersionMajor>1</VersionMajor> <Guid>00020813-0000-0000-c000-000000000046</Guid> <Lcid>0</Lcid> <Isolated>false</Isolated> <EmbedInteropTypes>true</EmbedInteropTypes> </COMReference> </ItemGroup>
</Project>DataGridView_Export_Excel/DataGridView_Export_Excel.csproj.user
<?xml version="1.0" encoding="utf-8"?><Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <ItemGroup> <Compile Update="Form1.cs"> <SubType>Form</SubType> </Compile> </ItemGroup></Project>DataGridView_Export_Excel/Form1.cs
using System.Data;using System.Runtime.InteropServices;
// 長い名前空間を省略するため、ファイルの最上部で別名を定義しますusing Excel = Microsoft.Office.Interop.Excel;
namespace DataGridView_Export_Excel{ public partial class Form1 : Form { private DataTable _dataTable = new DataTable();
public Form1() { InitializeComponent(); InitializeDataGridView(); btnExportExcel.Click += btnExportExcel_Click; }
private void InitializeDataGridView() { // テスト用データの準備（5行×3列） _dataTable.Columns.Add("Code", typeof(string)); _dataTable.Columns.Add("Name", typeof(string)); _dataTable.Columns.Add("Price", typeof(int));
_dataTable.Rows.Add("P001", "ノートパソコン", 120000); _dataTable.Rows.Add("P002", "USBメモリ", 2500); _dataTable.Rows.Add("P003", "モニター", 35000); _dataTable.Rows.Add("P004", "マウス", 4500); _dataTable.Rows.Add("P005", "キーボード", 7800);
dataGridView1.DataSource = _dataTable; dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; }
/// <summary> /// 「Excel出力」ボタンのクリックイベント /// </summary> private void btnExportExcel_Click(object? sender, EventArgs e) { // 出力先のファイルパスをユーザーに指定させる（SaveFileDialog） using var sfd = new SaveFileDialog(); sfd.Filter = "Excel ブック (*.xlsx)|*.xlsx"; sfd.FileName = $"商品リスト_{DateTime.Now:yyyyMMddHRmmss}.xlsx";
if (sfd.ShowDialog() != DialogResult.OK) return;
// Excel関係のオブジェクトは try-finally で確実に解放するために外側で宣言します Excel.Application? xlApp = null; Excel.Workbooks? xlBooks = null; Excel.Workbook? xlBook = null; Excel.Sheets? xlSheets = null; Excel.Worksheet? xlSheet = null; Excel.Range? xlRange = null;
try { // 1. メモリ上に DataGridView のデータを2次元配列として抽出する（ヘッダー行＋データ行） int rowCount = dataGridView1.RowCount; int colCount = dataGridView1.ColumnCount;
// Excelのインデックスに合わせて 1始まりの配列を作成 object[,] dataArray = new object[rowCount + 1, colCount];
// ヘッダー（列名）の格納 for (int c = 0; c < colCount; c++) { dataArray[0, c] = dataGridView1.Columns[c].HeaderText; }
// セルデータの格納 for (int r = 0; r < rowCount; r++) { for (int c = 0; c < colCount; c++) { dataArray[r + 1, c] = dataGridView1.Rows[r].Cells[c].Value ?? ""; } }
// 2. Excelの起動とブックの作成 xlApp = new Excel.Application(); xlApp.Visible = false; // 処理中にExcel画面をピコピコ表示させない（高速化） xlApp.DisplayAlerts = false; // 上書き警告などを非表示にする
xlBooks = xlApp.Workbooks; xlBook = xlBooks.Add(); xlSheets = xlBook.Sheets; xlSheet = (Excel.Worksheet)xlSheets[1];
// 3. 💡【高速化の肝】開始セルと終了セルからRangeを特定し、2次元配列を一括転送 Excel.Range startCell = (Excel.Range)xlSheet.Cells[1, 1]; Excel.Range endCell = (Excel.Range)xlSheet.Cells[rowCount + 1, colCount]; xlRange = xlSheet.Range[startCell, endCell];
// 配列を代入（これだけで全セルに一瞬で値が入ります） xlRange.Value = dataArray;
// 見栄えの調整：列幅を文字量に合わせて自動フィット xlRange.Columns.AutoFit();
// 4. ファイルの保存 xlBook.SaveAs(sfd.FileName); MessageBox.Show("Excelファイルの出力が完了しました！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show($"Excel出力中にエラーが発生しました:\n{ex.Message}", "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { // 5. ⭕【最重要】生成されたオブジェクトを逆順で完全に解放する（finallyブロック） if (xlBook != null) { xlBook.Close(false); // 保存は終わっているので変更破棄で閉じる } if (xlApp != null) { xlApp.Quit(); // Excelアプリケーションを終了 }
// COMリソースの明示的解放（解放を怠るとEXCEL.EXEがプロセスに残ります） if (xlRange != null) Marshal.ReleaseComObject(xlRange); if (xlSheet != null) Marshal.ReleaseComObject(xlSheet); if (xlSheets != null) Marshal.ReleaseComObject(xlSheets); if (xlBook != null) Marshal.ReleaseComObject(xlBook); if (xlBooks != null) Marshal.ReleaseComObject(xlBooks); if (xlApp != null) Marshal.ReleaseComObject(xlApp);
// ガベージコレクションを強制して完全にメモリから抹消する GC.Collect(); GC.WaitForPendingFinalizers(); } } }}DataGridView_Export_Excel/Form1.Designer.cs
namespace DataGridView_Export_Excel{ partial class Form1 { /// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.IContainer components = null;
/// <summary> /// Clean up any resources being used. /// </summary> /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param> protected override void Dispose(bool disposing) { if (disposing && (components != null)) { components.Dispose(); } base.Dispose(disposing); }
#region Windows Form Designer generated code
/// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { tableLayoutPanel1 = new TableLayoutPanel(); dataGridView1 = new DataGridView(); btnExportExcel = new Button(); tableLayoutPanel1.SuspendLayout(); ((System.ComponentModel.ISupportInitialize)dataGridView1).BeginInit(); SuspendLayout(); // // tableLayoutPanel1 // tableLayoutPanel1.ColumnCount = 1; tableLayoutPanel1.ColumnStyles.Add(new ColumnStyle(SizeType.Percent, 50F)); tableLayoutPanel1.Controls.Add(dataGridView1, 0, 0); tableLayoutPanel1.Controls.Add(btnExportExcel, 0, 1); tableLayoutPanel1.Dock = DockStyle.Fill; tableLayoutPanel1.Location = new Point(0, 0); tableLayoutPanel1.Name = "tableLayoutPanel1"; tableLayoutPanel1.RowCount = 2; tableLayoutPanel1.RowStyles.Add(new RowStyle(SizeType.Percent, 87.55556F)); tableLayoutPanel1.RowStyles.Add(new RowStyle(SizeType.Percent, 12.4444447F)); tableLayoutPanel1.Size = new Size(800, 450); tableLayoutPanel1.TabIndex = 0; // // dataGridView1 // dataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize; dataGridView1.Dock = DockStyle.Fill; dataGridView1.Location = new Point(3, 3); dataGridView1.Name = "dataGridView1"; dataGridView1.RowHeadersWidth = 62; dataGridView1.Size = new Size(794, 388); dataGridView1.TabIndex = 0; // // btnExportExcel // btnExportExcel.Dock = DockStyle.Fill; btnExportExcel.Location = new Point(3, 397); btnExportExcel.Name = "btnExportExcel"; btnExportExcel.Size = new Size(794, 50); btnExportExcel.TabIndex = 1; btnExportExcel.Text = "button1"; btnExportExcel.UseVisualStyleBackColor = true; // // Form1 // AutoScaleDimensions = new SizeF(10F, 25F); AutoScaleMode = AutoScaleMode.Font; ClientSize = new Size(800, 450); Controls.Add(tableLayoutPanel1); Name = "Form1"; Text = "Form1"; tableLayoutPanel1.ResumeLayout(false); ((System.ComponentModel.ISupportInitialize)dataGridView1).EndInit(); ResumeLayout(false); }
#endregion
private TableLayoutPanel tableLayoutPanel1; private DataGridView dataGridView1; private Button btnExportExcel; }}DataGridView_Export_Excel/Form1.resx
<?xml version="1.0" encoding="utf-8"?><root> <!-- Microsoft ResX Schema
Version 2.0
The primary goals of this format is to allow a simple XML format that is mostly human readable. The generation and parsing of the various data types are done through the TypeConverter classes associated with the data types.
Example:
... ado.net/XML headers & schema ... <resheader name="resmimetype">text/microsoft-resx</resheader> <resheader name="version">2.0</resheader> <resheader name="reader">System.Resources.ResXResourceReader, System.Windows.Forms, ...</resheader> <resheader name="writer">System.Resources.ResXResourceWriter, System.Windows.Forms, ...</resheader> <data name="Name1"><value>this is my long string</value><comment>this is a comment</comment></data> <data name="Color1" type="System.Drawing.Color, System.Drawing">Blue</data> <data name="Bitmap1" mimetype="application/x-microsoft.net.object.binary.base64"> <value>[base64 mime encoded serialized .NET Framework object]</value> </data> <data name="Icon1" type="System.Drawing.Icon, System.Drawing" mimetype="application/x-microsoft.net.object.bytearray.base64"> <value>[base64 mime encoded string representing a byte array form of the .NET Framework object]</value> <comment>This is a comment</comment> </data>
There are any number of "resheader" rows that contain simple name/value pairs.
Each data row contains a name, and value. The row also contains a type or mimetype. Type corresponds to a .NET class that support text/value conversion through the TypeConverter architecture. Classes that don't support this are serialized and stored with the mimetype set.
The mimetype is used for serialized objects, and tells the ResXResourceReader how to depersist the object. This is currently not extensible. For a given mimetype the value must be set accordingly:
Note - application/x-microsoft.net.object.binary.base64 is the format that the ResXResourceWriter will generate, however the reader can read any of the formats listed below.
mimetype: application/x-microsoft.net.object.binary.base64 value : The object must be serialized with : System.Runtime.Serialization.Formatters.Binary.BinaryFormatter : and then encoded with base64 encoding.
mimetype: application/x-microsoft.net.object.soap.base64 value : The object must be serialized with : System.Runtime.Serialization.Formatters.Soap.SoapFormatter : and then encoded with base64 encoding.
mimetype: application/x-microsoft.net.object.bytearray.base64 value : The object must be serialized into a byte array : using a System.ComponentModel.TypeConverter : and then encoded with base64 encoding. --> <xsd:schema id="root" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xsd:import namespace="http://www.w3.org/XML/1998/namespace" /> <xsd:element name="root" msdata:IsDataSet="true"> <xsd:complexType> <xsd:choice maxOccurs="unbounded"> <xsd:element name="metadata"> <xsd:complexType> <xsd:sequence> <xsd:element name="value" type="xsd:string" minOccurs="0" /> </xsd:sequence> <xsd:attribute name="name" use="required" type="xsd:string" /> <xsd:attribute name="type" type="xsd:string" /> <xsd:attribute name="mimetype" type="xsd:string" /> <xsd:attribute ref="xml:space" /> </xsd:complexType> </xsd:element> <xsd:element name="assembly"> <xsd:complexType> <xsd:attribute name="alias" type="xsd:string" /> <xsd:attribute name="name" type="xsd:string" /> </xsd:complexType> </xsd:element> <xsd:element name="data"> <xsd:complexType> <xsd:sequence> <xsd:element name="value" type="xsd:string" minOccurs="0" msdata:Ordinal="1" /> <xsd:element name="comment" type="xsd:string" minOccurs="0" msdata:Ordinal="2" /> </xsd:sequence> <xsd:attribute name="name" type="xsd:string" use="required" msdata:Ordinal="1" /> <xsd:attribute name="type" type="xsd:string" msdata:Ordinal="3" /> <xsd:attribute name="mimetype" type="xsd:string" msdata:Ordinal="4" /> <xsd:attribute ref="xml:space" /> </xsd:complexType> </xsd:element> <xsd:element name="resheader"> <xsd:complexType> <xsd:sequence> <xsd:element name="value" type="xsd:string" minOccurs="0" msdata:Ordinal="1" /> </xsd:sequence> <xsd:attribute name="name" type="xsd:string" use="required" /> </xsd:complexType> </xsd:element> </xsd:choice> </xsd:complexType> </xsd:element> </xsd:schema> <resheader name="resmimetype"> <value>text/microsoft-resx</value> </resheader> <resheader name="version"> <value>2.0</value> </resheader> <resheader name="reader"> <value>System.Resources.ResXResourceReader, System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</value> </resheader> <resheader name="writer"> <value>System.Resources.ResXResourceWriter, System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</value> </resheader></root>DataGridView_Export_Excel/Program.cs
namespace DataGridView_Export_Excel{ internal static class Program { /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main() { // To customize application configuration such as set high DPI settings or default font, // see https://aka.ms/applicationconfiguration. ApplicationConfiguration.Initialize(); Application.Run(new Form1()); } }}