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