private void btnLoadSchemaAndTables_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); } Worksheet worksheet = workbook.Worksheets["hdr表集合"]; int cols = worksheet.Cells.Columns.Count; int rows = worksheet.Cells.Rows.Count; var result = AsposeHelper.AutoFitMergedCells(workbook, "hdr表集合", 2, 0); var bindingList = new BindingList <HdrTablesCollection>(result); var source = new BindingSource(bindingList, null); grid.DataSource = source; var resultjson = JsonConvert.SerializeObject(result); this.richTextBox1.AppendText(resultjson); }
private void btnLoadHdr_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(workbook, "hdr表集合", 2, 0); #region 读取数据 List <ColumnEntity> tableColumns = null; var schema = result.Select(p => p.Schema).Distinct().ToList(); 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_{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 btnLoad_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(workbook, "sd域表集合", 2, 0); var sdstrings = File.ReadAllText(Path.Combine(AppContext.BaseDirectory, "sd_tables.json"), Encoding.Default); IList <sd_tables> sd_tables = JsonConvert.DeserializeObject <IList <sd_tables> >(sdstrings); var result1 = sd_tables.Select(p => new HdrTablesCollection { Domain = "sd", Schema = "sd", SchemaDesc = "sd", TableName = p.table_name, TableDesc = p.table_desc }).ToList(); #region 读取数据 List <ColumnEntity> tableColumns = null; var schema = result.Select(p => p.Schema).Distinct().ToList(); var schme = new List <string> { "sd" }; using (var conn = new PostgresHelper(dbConnectionString)) { BizHelper bizHelper = new BizHelper(); var sql = bizHelper.BuildQueryTableColumnsSQL(schme); 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 string outfilePath = Path.Combine(basePath, $"hdr_v106_{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(result1, tableColumns1, wb, 9, true); wb.Save(outfilePath, SaveFormat.Xlsx); Process.Start("Explorer", "/select," + outfilePath); }