Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        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;
        }
Exemplo n.º 4
0
        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();
        }
Exemplo n.º 5
0
        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);
        }
Exemplo n.º 6
0
        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);
        }
Exemplo n.º 7
0
        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();
        }