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);
}
public void ExportVithStyle(IList <HdrTablesCollection> schemas, List <ColumnEntity> tableColumns, Workbook workbook, int columnsOffset = 0, bool generateDiffColumn = false)
{
var schemaAndTablas = schemas.GroupBy(p => p.Schema).ToList();
AsposeHelper heper = new AsposeHelper();
IList <Style> styles = heper.CheckTest(workbook);
for (int ischema = 0; ischema < schemaAndTablas.Count; ischema++)
{
//一个schema
var item = schemaAndTablas[ischema];
string schemaname = item.Key;
var itemlst = schemaAndTablas[ischema].ToList();//所有的表
Worksheet sheetSchema = workbook.Worksheets.FirstOrDefault(p => p.Name == schemaname);
if (sheetSchema == null)
{
sheetSchema = workbook.Worksheets.Add(schemaname);
}
var cells = sheetSchema.Cells;
//架构下所有的表
int rowIndex = 2;
int columnOffset = columnsOffset;
for (int jtable = 0; jtable < itemlst.Count; jtable++)
{
string tableName = itemlst[jtable].TableName;
#region 写入表级别 说明
cells[CellsHelper.CellIndexToName(rowIndex, 0 + columnOffset)].PutValue(tableName);
rowIndex++;
cells[CellsHelper.CellIndexToName(rowIndex, 0 + columnOffset)].PutValue(schemaname);
//占用行
//列
rowIndex++;
cells[CellsHelper.CellIndexToName(rowIndex, 0 + columnOffset)].PutValue("序号");
cells[CellsHelper.CellIndexToName(rowIndex, 1 + columnOffset)].PutValue("列名");
cells[CellsHelper.CellIndexToName(rowIndex, 2 + columnOffset)].PutValue("中文名");
cells[CellsHelper.CellIndexToName(rowIndex, 3 + columnOffset)].PutValue("字段类型");
cells[CellsHelper.CellIndexToName(rowIndex, 4 + columnOffset)].PutValue("允许空");
cells[CellsHelper.CellIndexToName(rowIndex, 5 + columnOffset)].PutValue("外键");
cells[CellsHelper.CellIndexToName(rowIndex, 6 + columnOffset)].PutValue("字典系统");
cells[CellsHelper.CellIndexToName(rowIndex, 7 + columnOffset)].PutValue("字典标准化");
cells[CellsHelper.CellIndexToName(rowIndex, 8 + columnOffset)].PutValue("说明");
if (generateDiffColumn)
{
cells[CellsHelper.CellIndexToName(rowIndex, 9 + columnOffset)].PutValue("列名对比");
cells[CellsHelper.CellIndexToName(rowIndex, 10 + columnOffset)].PutValue("字段类型对比");
cells[CellsHelper.CellIndexToName(rowIndex, 11 + columnOffset)].PutValue("允许空对比");
}
rowIndex++;
#endregion
#region 处理列级别 表对应的列
//写入具体的表列
var tabItemCols = tableColumns.Where(p => p.table_schema == schemaname).Where(p => p.table_name == tableName).OrderBy(p => p.ordinal_position).ToList();
for (int icolumn = 0; icolumn < tabItemCols.Count; icolumn++)
{
var colItem = tabItemCols[icolumn];
cells[CellsHelper.CellIndexToName(rowIndex, 0 + columnOffset)].PutValue(colItem.ordinal_position);
cells[CellsHelper.CellIndexToName(rowIndex, 0 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 1 + columnOffset)].PutValue(colItem.column_name);
cells[CellsHelper.CellIndexToName(rowIndex, 1 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 2 + columnOffset)].PutValue(colItem.chinese_name);
cells[CellsHelper.CellIndexToName(rowIndex, 2 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 3 + columnOffset)].PutValue(colItem.data_typ);
cells[CellsHelper.CellIndexToName(rowIndex, 3 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 4 + columnOffset)].PutValue(colItem.is_nullable);
cells[CellsHelper.CellIndexToName(rowIndex, 4 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 5 + columnOffset)].PutValue(colItem.ref_key);
cells[CellsHelper.CellIndexToName(rowIndex, 5 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 6 + columnOffset)].PutValue(colItem.vale_range);
cells[CellsHelper.CellIndexToName(rowIndex, 6 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 7 + columnOffset)].PutValue(colItem.is_standard);
cells[CellsHelper.CellIndexToName(rowIndex, 7 + columnOffset)].SetStyle(styles[2]);
cells[CellsHelper.CellIndexToName(rowIndex, 8 + columnOffset)].PutValue(colItem.col_memo);
cells[CellsHelper.CellIndexToName(rowIndex, 8 + columnOffset)].SetStyle(styles[2]);
if (generateDiffColumn)
{
string columnNameIndexName = CellsHelper.CellIndexToName(rowIndex, 1); // 列名
string columnTypeIndexName = CellsHelper.CellIndexToName(rowIndex, 3); // 字段类型
string columnIsNullIndexName = CellsHelper.CellIndexToName(rowIndex, 4); // 允许空
string columnNameIndexName1 = CellsHelper.CellIndexToName(rowIndex, 1 + 9); // 列名
string columnTypeIndexName1 = CellsHelper.CellIndexToName(rowIndex, 3 + 9); // 字段类型
string columnIsNullIndexName1 = CellsHelper.CellIndexToName(rowIndex, 4 + 9); // 允许空
//★☆◊
cells[CellsHelper.CellIndexToName(rowIndex, 9 + columnOffset)].Formula = $"=IF(EXACT({columnNameIndexName}, {columnNameIndexName1}), \"★\", \"☆\")";
cells[CellsHelper.CellIndexToName(rowIndex, 10 + columnOffset)].Formula = $"=IF(EXACT({columnTypeIndexName}, {columnTypeIndexName1}), \"★\", \"☆\")";
cells[CellsHelper.CellIndexToName(rowIndex, 11 + columnOffset)].Formula = $"=IF(EXACT({columnIsNullIndexName}, {columnIsNullIndexName1}), \"★\", \"☆\")";
}
rowIndex++;
}
#endregion
}
sheetSchema.AutoFitColumns();
sheetSchema.AutoFitRows();
}
}
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);
}
