private string[] GetTableName(string baseName)
{
List <string> aa = null;
using (var conn = new PostgresHelper(dbConnectionString))
{
aa = conn.Query <string>(@"select table_name from information_schema.tables where table_schema = 'public' and table_name like @tbname ", new { tbname = baseName.ToLower() + "%" }).ToList <string>();
}
string[] arrTname = aa.ToArray();
string[] arrTname1 = new string[arrTname.Length];
int j = 2;
for (int i = 0; i < arrTname.Length; i++)
{
if (arrTname[i].EndsWith("_Header"))
{
arrTname1[0] = arrTname[i];
continue;
}
if (arrTname[i].EndsWith("_Body"))
{
arrTname1[1] = arrTname[i];
continue;
}
arrTname1[j++] = arrTname[i];
}
return(arrTname1);
}
private void btnLoad_SD_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(this.txtHDRExcelPath.Text))
{
MessageBox.Show("请选择hdr sd模型描述excel文件");
return;
}
if (!File.Exists(this.txtHDRExcelPath.Text))
{
MessageBox.Show("hdr sd模型描述excel文件不存在");
return;
}
//string filePath = Path.Combine(basePath, @"..\HDR库表集合_V1.0.5-201908.xlsx");
string filePath = this.txtHDRExcelPath.Text;
var inputfilename = Path.GetFileNameWithoutExtension(this.txtHDRExcelPath.Text);
Workbook workbook = null;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
workbook = new Workbook(fs);
}
var worksheets = workbook.Worksheets;
HdrExcelProcesser hdrExcelProcesser = new HdrExcelProcesser();
List <ExcelColumn> cols = new List <ExcelColumn>();
var result = hdrExcelProcesser.ProcessSchemaSd(worksheets);
cols.AddRange(result);
string outfilePath = Path.Combine(basePath, $"{inputfilename}_hdrcolumns_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");
Workbook wb = new Workbook();
wb.Worksheets.Clear();
Worksheet sheet1 = wb.Worksheets.Add("hdr_columns");
sheet1.Cells.ImportCustomObjects(cols as ICollection, 0, 0, new ImportTableOptions {
});
wb.Save(outfilePath, SaveFormat.Xlsx);
using (var conn = new PostgresHelper(dbConnectionString))
{
var bulkinserthelper = conn.BulkinsertHdrColumns(cols, false);
}
Process.Start("Explorer", "/select," + outfilePath);
}
private void btnTest_Click(object sender, EventArgs e)
{
DateTimeOffset dtos = new DateTimeOffset(DateTime.UtcNow);
TimeZoneInfo sdfdse = TimeZoneInfo.Local;
var sdfere = TimeZoneInfo.GetSystemTimeZones();
foreach (var item in sdfere)
{
Debug.WriteLine($"{item.DaylightName} {item.Id}");
}
var tsts = TimeZoneInfo.ConvertTime(dtos, TimeZoneInfo.Local);
var tt = dtos.ToString("yyy-MM-dd HH:mm:ss.fff zzz");
DateTime tes = DateTime.UtcNow;
DateTime dt_utc = DateTime.UtcNow;
DateTime dt_bj = DateTime.Now;
TimeZone localZone = TimeZone.CurrentTimeZone;
using (var conn = new PostgresHelper(dbConnectionString))
{
var result1 = conn.Query <time_zone_test>("select * from time_zone_test");
Debug.WriteLine($"当前时区:{localZone.StandardName}");
foreach (var item in result1)
{
Debug.WriteLine($"{item.id} | {item.time_with_tz.ToString("yyyy-MM-dd HH:mm:ss.fff zzz") } | { item.time_without_tz.ToString("yyyy-MM-dd HH:mm:ss.fff zzz")}");
}
}
using (var conn = new PostgresHelper(dbConnectionString))
{
var result1 = conn.Query <time_zone_test>("select * from time_zone_test");
Debug.WriteLine($"当前时区:{localZone.StandardName}");
foreach (var item in result1)
{
Debug.WriteLine($"{item.id} | {item.time_with_tz.ToString("yyyy-MM-dd HH:mm:ss.fff zzz") } | { item.time_without_tz.ToString("yyyy-MM-dd HH:mm:ss.fff zzz")}");
}
}
//this.Close();
return;
}
private void btnExportHDRExcel_Click(object sender, EventArgs e)
{
IList <HdrTablesCollection> schemaAndTablesEntities = JsonConvert.DeserializeObject <IList <HdrTablesCollection> >(this.richTextBox1.Text);
var schemaAndTableEntGroup = schemaAndTablesEntities.GroupBy(p => p.Schema);
var schema = schemaAndTablesEntities.Select(p => p.Schema).Distinct().ToList();
string schemas = schema.Select(p => "'" + p + "'").Aggregate((a, b) => a + "," + b);
List <ColumnEntity> result = null;
#region 查询数据库 获取 Columns 信息
using (var conn = new PostgresHelper(dbConnectionString))
{
BizHelper bizHelper = new BizHelper();
var sql = bizHelper.BuildQueryTableColumnsSQL(schema);
result = conn.Query <ColumnEntity>(sql).ToList <ColumnEntity>();
}
//类型处理
result = result.Select(p =>
{
if (p.data_typ == "int2")
{
p.data_typ = "smallint";
}
if (p.data_typ == "int4")
{
p.data_typ = "int(4)";
}
if (p.data_typ == "int8")
{
p.data_typ = "bigint";
}
if (p.data_typ == "timestamp(6)")
{
p.data_typ = "timestamp";
}
if (p.data_typ == "timestamp(3)")
{
p.data_typ = "timestamp";
}
if (p.data_typ.Length == 6 && p.data_typ.Contains("[]") && p.data_typ.Contains("int"))
{
p.data_typ = "int[]";
}
return(p);
}).ToList();
#endregion
Workbook workbook = new Workbook();
workbook.Worksheets.Clear();
AsposeHelper heper = new AsposeHelper();
IList <Style> styles = heper.CheckTest(workbook);
foreach (var item in schemaAndTableEntGroup)
{
var schemaName = item.Key; //架构名
var schemaTables = item.ToList(); //表集合
var workSheet = workbook.Worksheets.Add(schemaName); //架构的 工作表
var schemaConfigTables = schemaTables.Select(p => p.TableName.Trim()); //架构下所有的表
var cells = workSheet.Cells;
//生成行 列名行
int rowIndex = 2;
foreach (var tabItem in schemaTables)
{
//表 cells["A"].PutValue(tabItem.);
cells["A" + rowIndex.ToString()].PutValue(tabItem.TableName.Trim());
rowIndex++;
cells["A" + rowIndex.ToString()].PutValue(schemaName);
//占用行
//列
rowIndex++;
cells["A" + rowIndex.ToString()].PutValue("序号");
cells["B" + rowIndex.ToString()].PutValue("列名");
cells["C" + rowIndex.ToString()].PutValue("中文名");
cells["D" + rowIndex.ToString()].PutValue("字段类型");
cells["E" + rowIndex.ToString()].PutValue("允许空");
cells["F" + rowIndex.ToString()].PutValue("外键");
cells["G" + rowIndex.ToString()].PutValue("字典系统");
cells["H" + rowIndex.ToString()].PutValue("字典标准化");
cells["I" + rowIndex.ToString()].PutValue("说明");
rowIndex++;
var tabItemCols = result.Where(p => p.table_schema == schemaName).Where(p => p.table_name == tabItem.TableName.Trim()).ToList();
for (int i = 0; i < tabItemCols.Count; i++)
{
var colItem = tabItemCols[i];
cells["A" + rowIndex.ToString()].PutValue(colItem.ordinal_position);
cells["A" + rowIndex.ToString()].SetStyle(styles[2]);
cells["B" + rowIndex.ToString()].PutValue(colItem.column_name);
cells["B" + rowIndex.ToString()].SetStyle(styles[2]);
cells["C" + rowIndex.ToString()].PutValue(colItem.chinese_name);
cells["C" + rowIndex.ToString()].SetStyle(styles[2]);
cells["D" + rowIndex.ToString()].PutValue(colItem.data_typ);
cells["D" + rowIndex.ToString()].SetStyle(styles[2]);
cells["E" + rowIndex.ToString()].PutValue(colItem.is_nullable);
cells["E" + rowIndex.ToString()].SetStyle(styles[2]);
cells["F" + rowIndex.ToString()].PutValue(colItem.ref_key);
cells["F" + rowIndex.ToString()].SetStyle(styles[2]);
cells["G" + rowIndex.ToString()].PutValue(colItem.vale_range);
cells["G" + rowIndex.ToString()].SetStyle(styles[2]);
cells["H" + rowIndex.ToString()].PutValue(colItem.is_standard);
cells["H" + rowIndex.ToString()].SetStyle(styles[2]);
cells["I" + rowIndex.ToString()].PutValue(colItem.col_memo);
cells["I" + rowIndex.ToString()].SetStyle(styles[2]);
rowIndex++;
}
}
workSheet.AutoFitColumns();
workSheet.AutoFitRows();
}
//workbook.Settings.Password = "******";
string fileName = $"HDR_Model_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
workbook.Save(Path.Combine(basePath, fileName), SaveFormat.Xlsx);
System.Diagnostics.Process.Start("Explorer", "/select," + Path.Combine(basePath, fileName));
this.Close();
}
private void btnLoadHDR_SD_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(this.txtHDRExcelPath.Text))
{
return;
}
if (!File.Exists(this.txtHDRExcelPath.Text))
{
return;
}
string filePathWithName = this.txtHDRExcelPath.Text;
Workbook workbook = null;
using (FileStream fs = new FileStream(filePathWithName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
workbook = new Workbook(fs);
}
var worksheets = workbook.Worksheets;
var result = AsposeHelper.AutoFitMergedCells_SD(workbook, "sd域表集合", 2, 0);
#region 读取数据
List <ColumnEntity> tableColumns = null;
var schema = new List <string> {
"sd"
};
using (var conn = new PostgresHelper(dbConnectionString))
{
BizHelper bizHelper = new BizHelper();
var sql = bizHelper.BuildQueryTableColumnsSQL(schema);
tableColumns = conn.Query <ColumnEntity>(sql).ToList <ColumnEntity>();
}
var tableColumns1 = tableColumns.Select(p =>
{
if (p.data_typ == "int2")
{
p.data_typ = "smallint";
}
if (p.data_typ == "int4")
{
p.data_typ = "int";
}
if (p.data_typ == "int8")
{
p.data_typ = "bigint";
}
if (p.data_typ == "timestamp(6)")
{
p.data_typ = "timestamp";
}
if (p.data_typ == "timestamp(3)")
{
p.data_typ = "timestamp";
}
if (p.data_typ.Length == 6 && p.data_typ.Contains("[]") && p.data_typ.Contains("int"))
{
p.data_typ = "int[]";
}
return(p);
}).ToList();
#endregion
var schemaAndTablas = result.GroupBy(p => p.Schema).ToList();
//var schemas = result.Select(p => p.Schema).Distinct();
var schemaTables = JsonConvert.SerializeObject(result);
string outfilePath = Path.Combine(basePath, $"hdr_sd_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");
Workbook wb;
using (FileStream fs = new FileStream(filePathWithName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
wb = new Workbook(fs);
}
AsposeHelper hDRExcelHelper = new AsposeHelper();
hDRExcelHelper.ExportVithStyle(result, tableColumns1, wb, 9, true);
wb.Save(outfilePath, SaveFormat.Xlsx);
Process.Start("Explorer", "/select," + outfilePath);
}
private void btnLoadHdr_To_hdr_columns_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(this.txtHDRExcelPath.Text))
{
MessageBox.Show("请选择hdr模型描述excel文件");
return;
}
if (!File.Exists(this.txtHDRExcelPath.Text))
{
MessageBox.Show("hdr模型描述excel文件不存在");
return;
}
//string filePath = Path.Combine(basePath, @"..\HDR库表集合_V1.0.5-201908.xlsx");
string filePath = this.txtHDRExcelPath.Text;
var inputfilename = Path.GetFileNameWithoutExtension(this.txtHDRExcelPath.Text);
Workbook workbook = null;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
workbook = new Workbook(fs);
}
var worksheets = workbook.Worksheets;
HdrExcelProcesser hdrExcelProcesser = new HdrExcelProcesser();
List <ExcelColumn> cols = new List <ExcelColumn>();
var result = hdrExcelProcesser.ProcessSchemaMdm(worksheets);
cols.AddRange(result);
var result1 = hdrExcelProcesser.ProcessSchemaPatient(worksheets);
cols.AddRange(result1);
var result2 = hdrExcelProcesser.ProcessSchemaVisit(worksheets);
cols.AddRange(result2);
var result3 = hdrExcelProcesser.ProcessSchemaOrders(worksheets);
cols.AddRange(result3);
var result4 = hdrExcelProcesser.ProcessSchemaFee(worksheets);
cols.AddRange(result4);
var result5 = hdrExcelProcesser.ProcessSchemaDiag(worksheets);
cols.AddRange(result5);
var result6 = hdrExcelProcesser.ProcessSchemaAllergy(worksheets);
cols.AddRange(result6);
var result7 = hdrExcelProcesser.ProcessSchemaChecks(worksheets);
cols.AddRange(result7);
var result8 = hdrExcelProcesser.ProcessSchemaLab(worksheets);
cols.AddRange(result8);
var result9 = hdrExcelProcesser.ProcessSchemaOperation(worksheets);
cols.AddRange(result9);
var result10 = hdrExcelProcesser.ProcessSchemaNurse(worksheets);
cols.AddRange(result10);
var result11 = hdrExcelProcesser.ProcessSchemaCases(worksheets);
cols.AddRange(result11);
var result12 = hdrExcelProcesser.ProcessSchemaEmr(worksheets);
cols.AddRange(result12);
var result13 = hdrExcelProcesser.ProcessSchemaTumour(worksheets);
cols.AddRange(result13);
var result14 = hdrExcelProcesser.ProcessSchemaOther(worksheets);
cols.AddRange(result14);
var result15 = hdrExcelProcesser.ProcessSchemaReportcard(worksheets);
cols.AddRange(result15);
var result16 = hdrExcelProcesser.ProcessSchemaPhyexam(worksheets);
cols.AddRange(result16);
var result17 = hdrExcelProcesser.ProcessSchemaBiobank(worksheets);
cols.AddRange(result17);
var result18 = hdrExcelProcesser.ProcessSchemaNlp(worksheets);
cols.AddRange(result18);
var result19 = hdrExcelProcesser.ProcessSchemaEventflow(worksheets);
cols.AddRange(result19);
var result20 = hdrExcelProcesser.ProcessSchemaEtl(worksheets);
cols.AddRange(result20);
var result21 = hdrExcelProcesser.ProcessSchemaPublic(worksheets);
cols.AddRange(result21);
var result22 = hdrExcelProcesser.ProcessSchemaCure(worksheets);
cols.AddRange(result22);
string outfilePath = Path.Combine(basePath, $"{inputfilename}_hdrcolumns_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");
Workbook wb = new Workbook();
wb.Worksheets.Clear();
Worksheet sheet1 = wb.Worksheets.Add("hdr_columns");
sheet1.Cells.ImportCustomObjects(cols as ICollection, 0, 0, new ImportTableOptions {
});
wb.Save(outfilePath, SaveFormat.Xlsx);
using (var conn = new PostgresHelper(dbConnectionString))
{
var bulkinserthelper = conn.BulkinsertHdrColumns(cols, false);
}
Process.Start("Explorer", "/select," + outfilePath);
}
private void btnNewExcel_Click(object sender, EventArgs e)
{
Workbook workbook = new Workbook();
workbook.Worksheets.Clear();
Worksheet sheet = workbook.Worksheets.Add("Test");
sheet.IsGridlinesVisible = false;
Shape recshape = sheet.Shapes.AddAutoShape(AutoShapeType.RoundSingleCornerRectangle, 0, 0, 2, 1, 20, 60);
recshape.Bottom = 3;
recshape.Height = 38;
recshape.Left = 2;
recshape.LeftToCorner = 46;
recshape.Placement = PlacementType.MoveAndSize;
recshape.Right = 127;
recshape.TextHorizontalAlignment = TextAlignmentType.Center;
recshape.TextHorizontalOverflow = TextOverflowType.Clip;
recshape.TextOptions.Color = Color.FromArgb(255, 255, 192, 0);
//recshape.TextOptions.Name = "微软雅黑";
recshape.TextVerticalAlignment = TextAlignmentType.Center;
recshape.TextVerticalOverflow = TextOverflowType.Clip;
recshape.Top = 1;
recshape.TopToCorner = 1;
recshape.Width = 125;
//recshape.X = 46;
recshape.Font.Size = 11;
recshape.Font.Name = "微软雅黑";
recshape.Fill.FillType = FillType.Solid;
recshape.Text = "返回";
Hyperlink link = recshape.AddHyperlink("http://www.baidu.com");
#region 生成目录表
IList <schemata> schemataList = null;
IList <Table> tableList = null;
string owneer = "dba";
using (var conn = new PostgresHelper(dbConnectionString))
{
schemataList = conn.Query <schemata>($@"select * from information_schema.schemata where schema_owner = '{owneer}'").ToList <schemata>();
string schemas = schemataList.Select(p => "'" + p.schema_name + "'").Aggregate((a, b) => a + " , " + b);
tableList = conn.Query <Table>($"select * from information_schema.tables where table_schema in ({schemas})").ToList <Table>();
}
Worksheet tableSheet = workbook.Worksheets.Add("表集合");
int impResult = tableSheet.Cells.ImportCustomObjects(tableList.ToList(), 0, 0, new ImportTableOptions {
});
tableSheet.IsGridlinesVisible = false;
int totalcols = tableSheet.Cells.MaxDataColumn + 1;
int totalrows = tableSheet.Cells.MaxDataRow + 1;
Range rge = tableSheet.Cells.CreateRange(0, 0, totalrows, totalcols);
//rge.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Blue);
//rge.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Blue);
//rge.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Blue);
//rge.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thick, Color.Blue);
//rge.SetOutlineBorder(BorderType.DiagonalUp, CellBorderType.Thick, Color.Blue);
//rge.SetOutlineBorder(BorderType.Horizontal, CellBorderType.Thick, Color.Blue);
//rge.SetOutlineBorder(BorderType.Vertical, CellBorderType.Thick, Color.Blue);
rge.SetOutlineBorders(new CellBorderType[] { CellBorderType.Thick, CellBorderType.Thin, CellBorderType.Thick, CellBorderType.Thin }, new Color[] { Color.Black, Color.Red, Color.Black, Color.Red });
int startColumn = CellsHelper.ColumnNameToIndex("M") + 1;
int columnCount = CellsHelper.ColumnNameToIndex("XFD") - startColumn + 1;
tableSheet.Cells.HideColumns(startColumn, columnCount);
#endregion
string fileName = $"NewExcel_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
workbook.Save(Path.Combine(basePath, fileName), SaveFormat.Xlsx);
this.Close();
}
