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);
}