From 1a1c8e71fcd14858f595029f089b2d4a00202b32 Mon Sep 17 00:00:00 2001
From: ogi <Administrator@S-OGI-PC>
Date: Fri, 05 Dec 2025 09:24:16 +0900
Subject: [PATCH] プロジェクトファイルを追加。
---
HotelPms.SourceFactory/TableToExcel.cs | 246 +++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 246 insertions(+), 0 deletions(-)
diff --git a/HotelPms.SourceFactory/TableToExcel.cs b/HotelPms.SourceFactory/TableToExcel.cs
new file mode 100644
index 0000000..6cf3bfe
--- /dev/null
+++ b/HotelPms.SourceFactory/TableToExcel.cs
@@ -0,0 +1,246 @@
+using HotelPms.Share.Data;
+using HotelPms.Share.Util;
+using HotelPms.Share.Windows.Util;
+using NPOI.SS.UserModel;
+using System;
+using System.Collections.Generic;
+using System.ComponentModel;
+using System.Data;
+using System.Drawing;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+using System.Windows.Forms;
+
+namespace HotelPms.SourceFactory
+{
+ public partial class TableToExcel : Form
+ {
+ public TableToExcel()
+ {
+ InitializeComponent();
+ }
+
+ private void OpenExcel_Click(object sender, EventArgs e)
+ {
+ openFileDialog1.InitialDirectory = Application.StartupPath;
+ if (openFileDialog1.ShowDialog() != DialogResult.OK) { return; }
+ txtExcelPath.Text = openFileDialog1.FileName;
+
+ cmbSheet.Items.Clear();
+ using (NPOIExcel excel = new NPOIExcel(txtExcelPath.Text))
+ {
+ for (int i = 0; i < excel.Workbook.NumberOfSheets; i++)
+ {
+ cmbSheet.Items.Add(excel.Workbook.GetSheetAt(i).SheetName);
+ }
+ }
+
+ }
+
+ private void btnCopySheet_Click(object sender, EventArgs e)
+ {
+ using (NPOIExcel excel = new NPOIExcel(txtExcelPath.Text))
+ {
+ ISheet sheet = excel.Workbook.GetSheetAt(excel.Workbook.GetSheetIndex(cmbSheet.SelectedItem.ToString()));
+ sheet.CopyTo(excel.Workbook, txtNewSheet.Text, true, true); //2.5.2のNET5.0版にBUGがあって、keepFormulasを使えない 2.5.3でFixした
+ //sheet.CopyTo(excel.Workbook, txtNewSheet.Text, true, false);
+ excel.Save();
+
+ MessageBox.Show("OK");
+ }
+
+ }
+
+ private void btnOpenDB_Click(object sender, EventArgs e)
+ {
+ using (DataAccess dataAccess = new MsSqlNet(new DBConnectItem() { HostName = txtHost.Text, UserID = txtUser.Text, Password = txtPassword.Text, DBName = txtDB.Text }))
+ {
+ using (DataTable data = dataAccess.GetTableList())
+ {
+ cmbTable.Items.Clear();
+ foreach(DataRow row in data.Rows)
+ {
+ cmbTable.Items.Add(row["Name"]);
+ }
+
+ if (cmbTable.Items.Count > 0) { cmbTable.SelectedIndex = 0; }
+ }
+ }
+ }
+
+ private void CopyPage(NPOIExcel excel, int beginRowdx, int pageRowCount)
+ {
+ int j = beginRowdx;
+ for (int i = 0; i < pageRowCount; i++)
+ {
+ excel.Sheet.CopyRow(i, j);
+ j++;
+ }
+ }
+
+ private void SetTableToSheet(DataTable data, NPOIExcel excel, string sheetName)
+ {
+ excel.SetCurrentSheet(excel.Workbook.GetSheetIndex(sheetName));
+
+ int pageRowCount = 50;
+ int pageNo = 1;
+
+ int rowIdx = 7;
+ foreach (DataRow row in data.Rows)
+ {
+ if (rowIdx % pageRowCount == 0)
+ {
+ //ページ最後の空行
+ pageNo++;
+
+ //ページコピー
+ if ((excel.Sheet.LastRowNum + 1) < pageNo * pageRowCount)
+ {
+ CopyPage(excel, rowIdx + 1, pageRowCount);
+ }
+
+ rowIdx += 7;
+ }
+
+ excel.SetCell($"G{rowIdx - 3}", sheetName);
+ excel.SetCell($"AC{rowIdx - 2}", DateTime.Now.ToString("yyyy/MM/dd"));
+
+ excel.SetCell($"D{rowIdx}", row["Field"].ToString()); //TableName
+ int len = CConvert.ToInt(row["max_length"]);
+ string type = row["type"].ToString().ToUpper();
+ if (type == "NVARCHAR") { len /= 2; }
+ else if (type == "INT" || type == "DATETIME" || type == "SMALLDATETIME" || type == "SMALLINT" || type == "TINYINT" || type == "BIT") { len = -1; }
+ string lenText = (len == -1 ? string.Empty : $"({len})");
+
+ excel.SetCell($"S{rowIdx}", $"{row["type"].ToString().ToUpper()}{lenText}"); //型(長さ)
+ excel.SetCell($"X{rowIdx}", row["PKFlg"].ToString() == "1" ? "〇" : string.Empty); //PK
+ rowIdx++; //次の行番号
+ }
+ }
+
+ private void button1_Click(object sender, EventArgs e)
+ {
+ if(cmbSheet.SelectedIndex == -1)
+ {
+ MessageBox.Show("シートを選んでください。");
+ cmbSheet.Focus();
+ return;
+ }
+
+ if (cmbTable.SelectedIndex == -1)
+ {
+ MessageBox.Show("テーブルを選んでください。");
+ cmbSheet.Focus();
+ return;
+ }
+
+ using (MsSqlNet dataAccess = new MsSqlNet(new DBConnectItem() { HostName = txtHost.Text, UserID = txtUser.Text, Password = txtPassword.Text, DBName = txtDB.Text }))
+ {
+ using (DataTable data = dataAccess.GetFieldList(cmbTable.SelectedItem.ToString()))
+ {
+ using (NPOIExcel excel = new NPOIExcel(txtExcelPath.Text))
+ {
+ SetTableToSheet(data, excel, cmbSheet.SelectedItem.ToString());
+ excel.Save();
+ MessageBox.Show("OK");
+ }
+ }
+ }
+ }
+
+ private bool ExistsKey(string data, Dictionary<string, int> keyDict)
+ {
+ if (keyDict.Count == 0) { return true; }
+ foreach(KeyValuePair<string, int> item in keyDict)
+ {
+ if (data.StartsWith(item.Key)) { return true; }
+ }
+ return false;
+ }
+
+ private void button2_Click(object sender, EventArgs e)
+ {
+ Dictionary<string, int> keyDict = GetTableKey();
+
+ using (NPOIExcel excel = new NPOIExcel(txtExcelPath.Text))
+ {
+ excel.SetCurrentSheet(excel.Workbook.GetSheetIndex("目次"));
+ int rowIdx = 5;
+ int i = 1;
+ foreach (string item in cmbTable.Items)
+ {
+ if (!ExistsKey(item, keyDict)) { continue; }
+ excel.SetCell($"D{rowIdx}", item); //TableName
+ rowIdx++;
+ i++;
+ if (i == 52)
+ {
+ rowIdx += 5;
+ i = 1;
+ }
+ }
+
+ excel.Save();
+ MessageBox.Show("OK");
+ }
+ }
+
+ private Dictionary<string, int> GetTableKey()
+ {
+ Dictionary<string, int> keyDict = new Dictionary<string, int>();
+ if (txtTableConditon.Text.Length > 0)
+ {
+ string[] condList = txtTableConditon.Text.Split(new char[] { ',' });
+ foreach (string key in condList)
+ {
+ keyDict.Add(key, 0);
+ }
+ }
+ return keyDict;
+ }
+
+
+ private void button3_Click(object sender, EventArgs e)
+ {
+ if (cmbTable.Items.Count == 0)
+ {
+ MessageBox.Show("テーブル一覧がない。");
+ cmbSheet.Focus();
+ }
+
+ Dictionary<string, int> keyDict = GetTableKey();
+
+ using (MsSqlNet dataAccess = new MsSqlNet(new DBConnectItem() { HostName = txtHost.Text, UserID = txtUser.Text, Password = txtPassword.Text, DBName = txtDB.Text }))
+ {
+ using (NPOIExcel excel = new NPOIExcel(txtExcelPath.Text))
+ {
+ int i = 0;
+ foreach (string tableName in cmbTable.Items)
+ {
+ if (!ExistsKey(tableName, keyDict)) { continue; }
+ using (DataTable data = dataAccess.GetFieldList(tableName))
+ {
+ SetTableToSheet(data, excel, tableName);
+ }
+ i++;
+ lblStatus.Text = i.ToString();
+ lblStatus.Refresh();
+ }
+ excel.Save();
+ MessageBox.Show("OK");
+ }
+ }
+ }
+
+ private void txtExcelPath_TextChanged(object sender, EventArgs e)
+ {
+
+ }
+
+ private void cmbSheet_SelectedIndexChanged(object sender, EventArgs e)
+ {
+
+ }
+ }
+}
--
Gitblit v1.10.0