/// <summary>
/// 设置标题
/// </summary>
private void exportTitle2(DataDictCompare tableInfo)
{
IRow header = _sheet2.CreateRow(_row);
header.Height = 25 * 20;
//新建单元格
ICell cell = header.CreateCell(0);
// 单元格赋值
cell.SetCellValue(tableInfo.table_name_c);
cell.CellStyle = titleStyle;
//新建单元格
ICell cell1 = header.CreateCell(1);
cell1.SetCellValue(tableInfo.table_name);
cell1.CellStyle = titleStyle;
ICell cell2 = header.CreateCell(2);
cell2.CellStyle = titleStyle;
ICell cell3 = header.CreateCell(3);
cell3.CellStyle = titleStyle;
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
_sheet2.AddMergedRegion(new CellRangeAddress(_row, _row, 1, 3));
_row += 1;
}
//设置行
private void export(DataDictCompare dataDictCompare)
{
//获取枚举类
List <FieldCompare> dataDictList = dataDictCompare.FieldCompareList;
//新增字段
var createdFieldList = dataDictList.Where(t => t.FieldStatus == FieldStatusEnum.Created).ToList();
if (createdFieldList.Count > 0)
{
// 创建新增行
for (var i = _row; i < createdFieldList.Count + _row; i++)
{
FieldCompare fieldInfo = createdFieldList[i - _row];
IRow row1 = _sheet2.CreateRow(i);
//新建单元格
ICell cell = row1.CreateCell(0);
// 单元格赋值
cell.SetCellValue("新增字段");
cell.CellStyle = titleStyle;
//新建单元格-字段名称
ICell cell1 = row1.CreateCell(1);
cell1.SetCellValue(fieldInfo.field_name);
cell1.CellStyle = borderStyle;
//新建单元格-字段类型
ICell cell2 = row1.CreateCell(2);
string fieldType = getFieldType(fieldInfo);
cell2.SetCellValue(fieldType);
cell2.CellStyle = borderStyle;
//新建单元格-字段中文名称
ICell cell3 = row1.CreateCell(3);
cell3.SetCellValue(fieldInfo.field_name_c);
cell3.CellStyle = borderStyle;
}
_row += createdFieldList.Count;
}
//更新字段
var updatedFieldList = dataDictList.Where(t => t.FieldStatus == FieldStatusEnum.Update).ToList();
if (updatedFieldList.Count > 0)
{
// 创建新增行
for (var i = _row; i < updatedFieldList.Count + _row; i++)
{
FieldCompare fieldInfo = updatedFieldList[i - _row];
IRow row1 = _sheet2.CreateRow(i);
//新建单元格
ICell cell = row1.CreateCell(0);
// 单元格赋值
cell.SetCellValue("更新字段");
cell.CellStyle = titleStyle;
//新建单元格-字段名称
ICell cell1 = row1.CreateCell(1);
cell1.SetCellValue(fieldInfo.field_name);
cell1.CellStyle = borderStyle;
//新建单元格-字段类型
ICell cell2 = row1.CreateCell(2);
string fieldType = getFieldType(fieldInfo);
cell2.SetCellValue(fieldType);
cell2.CellStyle = borderStyle;
//新建单元格-字段中文名称
ICell cell3 = row1.CreateCell(3);
cell3.SetCellValue(fieldInfo.field_name_c);
cell3.CellStyle = borderStyle;
}
_row += updatedFieldList.Count;
}
//删除字段
var deletedFieldList = dataDictList.Where(t => t.FieldStatus == FieldStatusEnum.Deleted).ToList();
if (deletedFieldList.Count > 0)
{
// 创建新增行
for (var i = _row; i < deletedFieldList.Count + _row; i++)
{
FieldCompare fieldInfo = deletedFieldList[i - _row];
IRow row1 = _sheet2.CreateRow(i);
//新建单元格
ICell cell = row1.CreateCell(0);
// 单元格赋值
cell.SetCellValue("删除字段");
cell.CellStyle = titleStyle;
//新建单元格-字段名称
ICell cell1 = row1.CreateCell(1);
cell1.SetCellValue(fieldInfo.field_name);
cell1.CellStyle = borderStyle;
//新建单元格-字段类型
ICell cell2 = row1.CreateCell(2);
string fieldType = getFieldType(fieldInfo);
cell2.SetCellValue(fieldType);
cell2.CellStyle = borderStyle;
//新建单元格-字段中文名称
ICell cell3 = row1.CreateCell(3);
cell3.SetCellValue(fieldInfo.field_name_c);
cell3.CellStyle = borderStyle;
}
_row += deletedFieldList.Count;
}
_row += 2;
}
private void saveExcelFile(string path, bool isHigherVersion)
{
if (isHigherVersion)
{
_workBook = new XSSFWorkbook();
}
else
{
_workBook = new HSSFWorkbook();
}
setTitleSyle();
//行号标识重置
_row = 0;
_sheet1 = _workBook.CreateSheet("数据结构升级清单");
_sheet1.SetColumnWidth(0, (28 * 256));
_sheet1.SetColumnWidth(1, (28 * 256));
_sheet1.SetColumnWidth(2, (13 * 256));
//设置标题
exportTitle();
//导出第一张表
for (int i = 0; i < dicSourceCompareList.Count; i++)
{
DataDictCompare tableInfo = dicSourceCompareList[i];
IRow row1 = _sheet1.CreateRow(i + _row);
//新建单元格
ICell cell = row1.CreateCell(0);
// 单元格赋值
cell.SetCellValue(tableInfo.table_name_c);
//新建单元格
ICell cell1 = row1.CreateCell(1);
cell1.SetCellValue(tableInfo.table_name);
//枚举值
ICell cell2 = row1.CreateCell(2);
string statusStr = "";
if (tableInfo.TableStatus == TableStatusEnum.Created)
{
statusStr = "新增";
}
else if (tableInfo.TableStatus == TableStatusEnum.Update)
{
statusStr = "修改";
}
else if (tableInfo.TableStatus == TableStatusEnum.Deleted)
{
statusStr = "删除";
}
cell2.SetCellValue(statusStr);
}
//行号标识重置
_row = 0;
//导出第二张表
_sheet2 = _workBook.CreateSheet("字段修改清单");
_sheet2.SetColumnWidth(0, (31 * 256));
_sheet2.SetColumnWidth(1, (22 * 256));
_sheet2.SetColumnWidth(2, (22 * 256));
_sheet2.SetColumnWidth(3, (30 * 256));
var sourceList = dicSourceCompareList.Where(t => t.TableStatus == TableStatusEnum.Update).ToList();
foreach (DataDictCompare sourceTable in sourceList)
{
exportTitle2(sourceTable);
export(sourceTable);
}
//如果存在删除
if (File.Exists(path))
{
try
{
File.Delete(path);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
using (var file = new FileStream(path, FileMode.Create))
{
_workBook.Write(file);
MessageBox.Show("保存成功");
}
}
/// <summary>
/// 获取结果集
/// </summary>
/// <param name="sourceDbName">源数据库名称</param>
/// <param name="targetDbName">目标数据库名称</param>
/// <returns></returns>
private List <DataDictCompare> getResult(string sourceDbName, string targetDbName)
{
bool isHigherForSource = isHigher(sourceDbName);
var sqlSource = isHigherForSource ? sqlHigher : sql;
List <FieldCompare> dicSourceList = sqlHelper.ExecuteObjectList <FieldCompare>(string.Format(sqlSource, sourceDbName));
bool isHigherForTarget = isHigher(targetDbName);
var sqlTarget = isHigherForTarget ? sqlHigher : sql;
List <FieldCompare> dicTargetList = sqlHelper.ExecuteObjectList <FieldCompare>(string.Format(sqlTarget, targetDbName));
var sourceTableNameList = dicSourceList.Select(t => new
{
t.table_name,
t.table_name_c
}).Distinct().ToList();
var targetTableNameList = dicTargetList.Select(t => new
{
t.table_name,
t.table_name_c
}).Distinct().ToList();
List <DataDictCompare> dataDictCompares = new List <DataDictCompare>();
foreach (var tableInfo in sourceTableNameList)
{
string tableName = tableInfo.table_name;
DataDictCompare ddc = new DataDictCompare
{
table_name = tableName,
table_name_c = tableInfo.table_name_c
};
//判断是否新增的表
if (targetTableNameList.All(t => t.table_name != tableName))
{
ddc.TableStatus = TableStatusEnum.Created;
}
else
{
var fieldCompareList = dicSourceList.Where(t => t.table_name == tableName).ToList();
bool isSame = compareField(ref fieldCompareList, dicTargetList.Where(t => t.table_name == tableName).ToList());
ddc.FieldCompareList = fieldCompareList;
ddc.TableStatus = isSame ? TableStatusEnum.None : TableStatusEnum.Update;
}
dataDictCompares.Add(ddc);
}
foreach (var targetTableInfo in targetTableNameList)
{
//判断是否删除的表
if (sourceTableNameList.All(t => t.table_name != targetTableInfo.table_name))
{
DataDictCompare ddc = new DataDictCompare
{
table_name = targetTableInfo.table_name,
table_name_c = targetTableInfo.table_name_c,
TableStatus = TableStatusEnum.Deleted
};
dataDictCompares.Add(ddc);
}
}
List <DataDictCompare> dicSourceCompareListTemp = new List <DataDictCompare>();
if (dataDictCompares.Count > 0)
{
dicSourceCompareListTemp = dataDictCompares.Where(t => t.TableStatus != TableStatusEnum.None).OrderBy(t => t.table_name).ToList();
for (int i = 0; i < dicSourceCompareListTemp.Count; i++)
{
dicSourceCompareListTemp[i].RowNum = i + 1;
}
}
return(dicSourceCompareListTemp);
}
