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)
|
{
|
|
}
|
}
|
}
|
