예제 #1
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();
        }
예제 #2
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);
        }