private bool CheckGeneratorSQL(MDataBaseDefine db, out string errorMsg)
{
if (db == null)
{
errorMsg = "待处理的数据为空";
return(false);
}
if (string.IsNullOrEmpty(db.DataBaseName))
{
errorMsg = "数据库名称为空";
return(false);
}
string msg = string.Empty;
if (!this.CheckGeneratorSQL(db.TableList, out msg))
{
errorMsg = msg;
return(false);
}
errorMsg = string.Empty;
return(true);
}
private void SetFirstPageWorkSheet(IWorkbook workbook, MDataBaseDefine database)
{
ISheet firstPageSheet = workbook.CreateSheet("首页");
IRow row = firstPageSheet.CreateRow(15);
ICell cell = row.CreateCell(5);
IFont font1 = workbook.CreateFont();
font1.FontName = "微软雅黑";
font1.FontHeightInPoints = 36;
cell.CellStyle = this.GetCellStyle(workbook, font1, null, FillPattern.NoFill, null, HorizontalAlignment.Center, VerticalAlignment.Center, false);
cell.SetCellValue(string.Format("【{0}-{1}-{2}】", database.DataBaseName + "数据库", database.DataBaseName, database.DataBaseType.ToString()));
row = firstPageSheet.CreateRow(17);
cell = row.CreateCell(5);
cell.CellStyle = this.GetCellStyle(workbook, font1, null, FillPattern.NoFill, null, HorizontalAlignment.Center, VerticalAlignment.Center, false);
cell.SetCellValue("数据库项目定义");
for (int j = 0; j <= 6; j++)
{
firstPageSheet.AutoSizeColumn(j);
}
}
public MResult <string> GeneratorSQL(MDataBaseDefine db)
{
// 1、进行数据验证
string errorMsg = string.Empty;
if (!this.CheckGeneratorSQL(db, out errorMsg))
{
return(new MResult <string>()
{
ErrorMsg = errorMsg,
IsSuccess = false,
Result = string.Empty,
});
}
string databaseSQL = this.GetCreateDataBaseSQL(db);
StringBuilder tableSQL = new StringBuilder();
foreach (var table in db.TableList)
{
string str = this.GetCreateTableSQL(table);
tableSQL.Append(str).AppendLine();
}
return(new MResult <string>
{
IsSuccess = true,
ErrorMsg = string.Empty,
Result = databaseSQL.ToString() + tableSQL.ToString()
});
}
/// <summary>
/// 生成SQL语句
/// </summary>
/// <param name="db">数据库定义</param>
/// <returns>结果</returns>
public MResult <string> GeneratorSQL(MDataBaseDefine db)
{
var dao = DALFactory.GetGeneratorSQLDAL(db.DataBaseType);
var ret = dao.GeneratorSQL(db);
return(ret);
}
private string GetCreateDataBaseSQL(MDataBaseDefine db)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("/* 建立数据库{0} */", db.DataBaseName).AppendLine();
sb.AppendFormat("CREATE DATABASE {0} DEFAULT charset utf8 collate utf8_general_ci;", db.DataBaseName).AppendLine();
return(sb.ToString());
}
public MDataBaseDefine GenerateDataBaseDefine(string connstr)
{
MDataBaseDefine ret = null;
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
var dao = DALFactory.GetSchemaDAO(MDataBaseType.SQLSERVER, MDBAccessType.READONLY);
ret = dao.GenerateDataBaseDefine(conn);
}
return(ret);
}
public MDataBaseDefine GenerateDataBaseDefine(System.Data.IDbConnection conn)
{
MDataBaseDefine database = new MDataBaseDefine();
database.TableList = new List <MTableDefine>();
// 获取所有表名
List <string> tableNameList = this.GetTableNameList(conn);
foreach (var tableName in tableNameList)
{
var table = this.GetTableDefine(conn, tableName);
database.TableList.Add(table);
}
// 填充索引信息
List <MTableIndex> indexList = this.GetIndexInfo(conn);
var group = from p in indexList
group p by new { p.TableName } into g
select new { g.Key };
foreach (var g in group)
{
var tableFind = database.TableList.Find(sa => sa.TableName == g.Key.TableName);
if (tableFind != null)
{
var columns = tableFind.FieldList;
var indexs = indexList.FindAll(sa => sa.TableName == g.Key.TableName);
indexs.ForEach(sa =>
{
var column = columns.Find(p => p.FieldName == sa.ColumnName);
if (sa.IsUnique)
{
column.IsUniqueIndex = true;
}
else
{
column.IndexNo = columns.Max(q => q.IndexNo) + 1;
}
});
}
}
return(database);
}
public void GenerateDatabaseExcel(MDataBaseDefine database)
{
string fileName = @"D:\01code\02mine\04DataLayer\MainTest\" + database.DataBaseName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
IWorkbook workbook = new HSSFWorkbook();
this.SetFirstPageWorkSheet(workbook, database);
this.SetTableIndexPage(workbook, database);
foreach (var table in database.TableList)
{
this.SetTableNamePageList(workbook, table, fileName);
}
//表格制作完成后，保存
//创建一个文件流对象
using (FileStream fs = File.Open(fileName, FileMode.OpenOrCreate))
{
workbook.Write(fs);
//最后记得关闭对象
workbook.Close();
}
}
private MDataBaseDefine GetDataBaseDefine(DataSet ds)
{
MDataBaseDefine model = new MDataBaseDefine();
DataTable tb = ds.Tables["数据表一栏$"];
model.DataBaseName = tb.Rows[1][1].ToString();
string databaseType = tb.Rows[2][1].ToString();
if (!string.IsNullOrEmpty(databaseType) && databaseType.ToUpper().Trim() == "MYSQL")
{
model.DataBaseType = MDataBaseType.MYSQL;
}
else
{
model.DataBaseType = MDataBaseType.UNKNOW;
}
model.ServerAddress = tb.Rows[3][1].ToString();
model.ReadAccount = tb.Rows[5][1].ToString();
model.WriteAccount = tb.Rows[5][1].ToString();
return(model);
}
public MResult <Dictionary <string, string> > GenneratorModelList(MDataBaseDefine db, string namespaceStr)
{
MResult <Dictionary <string, string> > ret = new MResult <Dictionary <string, string> >();
return(ret);
}
private void SetTableIndexPage(IWorkbook workbook, MDataBaseDefine database)
{
ISheet sheet = workbook.CreateSheet("数据表一栏");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null, "微软雅黑", 10, false);
cell.SetCellValue("Table表一栏");
row = sheet.CreateRow(1);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("库名");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("");
row = sheet.CreateRow(2);
cell = row.CreateCell(0);
cell.SetCellValue("库类型");
cell.CellStyle = this.GetCellStyle(workbook, null);
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(database.DataBaseName);
row = sheet.CreateRow(3);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("服务器地址");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(database.ServerAddress);
row = sheet.CreateRow(4);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("建库时间");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("");
row = sheet.CreateRow(5);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("读账号");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(database.ReadAccount);
row = sheet.CreateRow(6);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("写账号");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(database.WriteAccount);
row = sheet.CreateRow(7);
cell = row.CreateCell(4);
cell.CellStyle = this.GetCellStyle(workbook, null, "宋体", 10, false);
cell.SetCellValue("○修改，●新增，◎使用");
HSSFColor gray = new HSSFColor.Grey40Percent();
row = sheet.CreateRow(8);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("#");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("一级分类");
cell = row.CreateCell(2);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("二级分类");
cell = row.CreateCell(3);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("TableName");
cell = row.CreateCell(4);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("表名");
cell = row.CreateCell(5);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("备注");
int i = 9;
foreach (var table in database.TableList)
{
row = sheet.CreateRow(i);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(i - 8);
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableNameCH);
cell = row.CreateCell(2);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableNameCH);
cell = row.CreateCell(3);
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);//建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
link.Address = "#" + table.TableName + "!A1";
cell.Hyperlink = link;
cell.SetCellValue(table.TableName);
this.SetHyperLinkCellStyle(workbook, cell);
cell = row.CreateCell(4);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableNameCH);
cell = row.CreateCell(5);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableNameCH);
i++;
}
for (int j = 0; j <= 6; j++)
{
sheet.AutoSizeColumn(j);
}
}
/// <summary>
/// 获取数据库中的所有表结构信息等，现在的问题是table的cell 的 高度控制不好，内容是上对齐的 20180316
/// </summary>
public void Generate()
{
MDataBaseDefine res = getData();
object path; //文件路径变量
MSWord.Application wordApp;
MSWord.Document wordDoc;
path = Environment.CurrentDirectory + "\\" + DateTime.Now.ToString("yyyyMMdd") + "_" + this.dataBaseName + ".doc";
wordApp = new MSWord.Application();
wordApp.Visible = true;//使文档可见
//如果已存在，则删除
if (File.Exists((string)path))
{
File.Delete((string)path);
}
//由于使用的是COM库，因此有许多变量需要用Missing.Value代替
wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
//页面设置
wordDoc.PageSetup.PaperSize = MSWord.WdPaperSize.wdPaperA4; //设置纸张样式为A4纸
wordDoc.PageSetup.Orientation = MSWord.WdOrientation.wdOrientPortrait; //排列方式为垂直方向
wordDoc.PageSetup.TopMargin = 57.0f;
wordDoc.PageSetup.BottomMargin = 57.0f;
wordDoc.PageSetup.LeftMargin = 57.0f;
wordDoc.PageSetup.RightMargin = 57.0f;
wordDoc.PageSetup.HeaderDistance = 30.0f; //页眉位置
wordApp.Selection.ParagraphFormat.LineSpacing = 16f; //设置文档的行间距
wordApp.Selection.ParagraphFormat.FirstLineIndent = 30; //首行缩进的长度
this.AddHeading1("物理模型设计", wordDoc);
this.DrawTableCountInfo(wordApp, wordDoc, res);
wordDoc.Content.InsertAfter("\n"); //这一句与下一句的顺序不能颠倒，原因还没搞透
wordApp.Selection.EndKey(ref unite, ref Nothing); //这一句不加，有时候好像也不出问题，不过还是加了安全
this.AddHeading2("2 表描述", wordDoc);
int tableIndex = 1;
foreach (var item in res.TableList)
{
this.DrawTableDetailInfo(wordApp, wordDoc, tableIndex, item);
tableIndex++;
}
wordDoc.Content.InsertAfter("\n");
// WdSaveFormat为Word 2003文档的保存格式
// office 2007就是wdFormatDocumentDefault
object format = MSWord.WdSaveFormat.wdFormatDocumentDefault;
//将wordDoc文档对象的内容保存为DOCX文档
wordDoc.SaveAs(ref path, ref format, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
wordDoc.Close(ref Nothing, ref Nothing, ref Nothing);
//关闭wordApp组件对象
wordApp.Quit(ref Nothing, ref Nothing, ref Nothing);
Console.WriteLine(path + " 创建完毕！");
Console.ReadKey();
}
/// <summary>
/// 绘制表清单
/// </summary>
/// <param name="wordApp"></param>
/// <param name="wordDoc"></param>
/// <param name="res"></param>
private void DrawTableCountInfo(MSWord.Application wordApp, MSWord.Document wordDoc, MDataBaseDefine res)
{
// 这一句与下一句的顺序不能颠倒，原因还没搞透
//wordDoc.Content.InsertAfter("\n");
// 这一句不加，有时候好像也不出问题，不过还是加了安全
wordApp.Selection.EndKey(ref unite, ref Nothing);
this.AddHeading2("1 表清单", wordDoc);
//将光标移动到文档末尾
wordApp.Selection.EndKey(ref unite, ref Nothing);
wordApp.Selection.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphLeft;
int tableRow = res.TableList.Count + 1;
int tableColumn = 3;
// 定义一个Word中的表格对象
MSWord.Table table = wordDoc.Tables.Add(wordApp.Selection.Range,
tableRow, tableColumn, ref Nothing, ref Nothing);
// 默认创建的表格没有边框，这里修改其属性，使得创建的表格带有边框
table.Borders.Enable = 1;//这个值可以设置得很大，例如5、13等等
// 表格的索引是从1开始的。
table.Cell(1, 1).Range.Text = "序号";
table.Cell(1, 2).Range.Text = "代码";
table.Cell(1, 3).Range.Text = "描述";
for (int i = 1; i < tableRow; i++)
{
int row = i + 1;
var tableInfo = res.TableList[i - 1];
table.Cell(row, 1).Range.Text = i + ".";
table.Cell(row, 2).Range.Text = tableInfo.TableName;
table.Cell(row, 3).Range.Text = tableInfo.TableNameCH;
}
// 设置table样式
// 高度规则是：行高有最低值下限？
table.Rows.HeightRule = MSWord.WdRowHeightRule.wdRowHeightAtLeast;
//table.Rows.Height = wordApp.CentimetersToPoints(float.Parse("0.8"));//
table.Range.Font.Size = 11F;
table.Range.Font.Bold = 0;
table.Range.Font.Name = "新宋体";
// 表格文本居左
table.Range.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphCenter;
// 文本垂直贴到底部
table.Range.Cells.VerticalAlignment = MSWord.WdCellVerticalAlignment.wdCellAlignVerticalBottom;
// 设置table边框样式
table.Borders.OutsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle; //表格外框是双线
table.Borders.InsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle; //表格内框是单线
// 加粗
table.Rows[1].Range.Font.Bold = 1;
table.Rows[1].Range.Font.Size = 11F;
//将第 1列宽度设置为90
table.Columns[1].Width = 90;
//将其他列的宽度都设置为75
for (int i = 2; i <= tableColumn; i++)
{
table.Columns[i].Width = 200;
}
}
private static void TestExcel()
{
string json = "{\"DataBaseName\":\"TaxClient\",\"DataBaseType\":0,\"ServerAddress\":\"localhost\",\"ReadAccount\":\"\",\"WriteAccount\":\"\",\"TableList\":[{\"TableName\":\"C_UserHistoricalPassword\",\"TableNameCH\":\"\",\"TableDescrption\":null,\"PrimaryKey\":null,\"FieldList\":[{\"FieldFormat\":null,\"Index\":1,\"FieldNameCH\":\"\",\"FieldName\":\"UserID\",\"DataType\":\"bigint\",\"Length\":19,\"IsNullable\":false,\"PrimaryKeyIndex\":1,\"ForeignRelation\":null,\"IsUniqueIndex\":false,\"IndexNo\":1,\"IsAutoIncrement\":false,\"DefaultValue\":\"\",\"ValueConstraint\":null,\"ProjectSignificance\":null,\"DigitalLength\":0},{\"FieldFormat\":null,\"Index\":2,\"FieldNameCH\":\"\",\"FieldName\":\"Password\",\"DataType\":\"nvarchar\",\"Length\":50,\"IsNullable\":false,\"PrimaryKeyIndex\":0,\"ForeignRelation\":null,\"IsUniqueIndex\":false,\"IndexNo\":0,\"IsAutoIncrement\":false,\"DefaultValue\":\"\",\"ValueConstraint\":null,\"ProjectSignificance\":null,\"DigitalLength\":0},{\"FieldFormat\":null,\"Index\":3,\"FieldNameCH\":\"\",\"FieldName\":\"UpdateTime\",\"DataType\":\"datetime\",\"Length\":23,\"IsNullable\":false,\"PrimaryKeyIndex\":2,\"ForeignRelation\":null,\"IsUniqueIndex\":false,\"IndexNo\":0,\"IsAutoIncrement\":false,\"DefaultValue\":\"\",\"ValueConstraint\":null,\"ProjectSignificance\":null,\"DigitalLength\":3}]},{\"TableName\":\"A_RoleMenu\",\"TableNameCH\":\"\",\"TableDescrption\":null,\"PrimaryKey\":null,\"FieldList\":[{\"FieldFormat\":null,\"Index\":1,\"FieldNameCH\":\"\",\"FieldName\":\"RoleMenuID\",\"DataType\":\"uniqueidentifier\",\"Length\":16,\"IsNullable\":false,\"PrimaryKeyIndex\":1,\"ForeignRelation\":null,\"IsUniqueIndex\":false,\"IndexNo\":0,\"IsAutoIncrement\":false,\"DefaultValue\":\"\",\"ValueConstraint\":null,\"ProjectSignificance\":null,\"DigitalLength\":0},{\"FieldFormat\":null,\"Index\":2,\"FieldNameCH\":\"bigint\",\"FieldName\":\"RoleID\",\"DataType\":\"bigint\",\"Length\":19,\"IsNullable\":false,\"PrimaryKeyIndex\":0,\"ForeignRelation\":null,\"IsUniqueIndex\":false,\"IndexNo\":0,\"IsAutoIncrement\":false,\"DefaultValue\":\"\",\"ValueConstraint\":null,\"ProjectSignificance\":null,\"DigitalLength\":0},{\"FieldFormat\":null,\"Index\":3,\"FieldNameCH\":\"int\",\"FieldName\":\"MenuID\",\"DataType\":\"int\",\"Length\":10,\"IsNullable\":false,\"PrimaryKeyIndex\":0,\"ForeignRelation\":null,\"IsUniqueIndex\":false,\"IndexNo\":0,\"IsAutoIncrement\":false,\"DefaultValue\":\"\",\"ValueConstraint\":null,\"ProjectSignificance\":null,\"DigitalLength\":0},{\"FieldFormat\":null,\"Index\":4,\"FieldNameCH\":\"\",\"FieldName\":\"OrgID\",\"DataType\":\"uniqueidentifier\",\"Length\":16,\"IsNullable\":true,\"PrimaryKeyIndex\":0,\"ForeignRelation\":null,\"IsUniqueIndex\":false,\"IndexNo\":0,\"IsAutoIncrement\":false,\"DefaultValue\":\"\",\"ValueConstraint\":null,\"ProjectSignificance\":null,\"DigitalLength\":0}]}]}";
MDataBaseDefine model = JsonConvert.DeserializeObject <MDataBaseDefine>(json);
}
