根据数据库生成word帮助文档
开发项目都需要一个帮助文档，如果已经建好了数据库，那么帮助文档自然就可以自己生成了，具体思路是这样的：1.获得数据库中所有的表名；2.根据每一个表名获得每个表中详细的字段信息，这些信息有很多是需要从系统信息的表里面读；3.根据读取的信息生成一个word文档。
附：Sysobjects 里面有各个表的具体信息，sysproperties存储有表中每一个字段的描述，syscolumns存储有表中每一个字段的具体信息，systypes有数据库中每个xtype对应的类型。
具体代码如下：
public string GetTableID(string tableName)
{
if (tableName != "Table")
{
string sConnectionString;
sConnectionString = tbDB.Text;
SqlConnection objConn = new SqlConnection(sConnectionString);
objConn.Open();
SqlDataAdapter daPoint = new SqlDataAdapter("select * from sysobjects where name='" + tableName + "'", objConn);
DataTable dt = new DataTable();
daPoint.Fill(dt);
objConn.Close();
return dt.Rows[0]["ID"].ToString();
}
return "0";
}
public string GetSingleDescription(string tableName, int smallID)
{
string sConnectionString;
sConnectionString = tbDB.Text;
SqlConnection objConn = new SqlConnection(sConnectionString);
objConn.Open();
string ew = GetTableID(tableName);
SqlDataAdapter daPoint = new SqlDataAdapter("select * from sysproperties where ID=" + Convert.ToInt32(GetTableID(tableName)) + " and smallid=" + smallID + "", objConn);
DataTable dt = new DataTable();
daPoint.Fill(dt);
objConn.Close();
if (dt.Rows.Count > 0)
{
return dt.Rows[0]["value"].ToString();
}
return "空";
}
//判断字段是否可为空
private string GetIfNull(int tableID, string columnName)
{
string isNull = "不明";
string sConnectionString;
sConnectionString = tbDB.Text;
SqlConnection objConn = new SqlConnection(sConnectionString);
objConn.Open();
SqlDataAdapter daPoint = new SqlDataAdapter("select * from syscolumns where id=" + tableID + " and name='" + columnName + "'", objConn);
DataTable dt = new DataTable();
daPoint.Fill(dt);
objConn.Close();
if (dt.Rows.Count > 0)
{
if (Convert.ToInt32(dt.Rows[0]["isnullable"].ToString()) == 0)
{
isNull = "不可为空";
}
if (Convert.ToInt32(dt.Rows[0]["isnullable"].ToString()) == 1)
{
isNull = "可为空";
}
}
return isNull;
}
public DataTable GetAllTablesName()
{
try
{
string sConnectionString;
sConnectionString = tbDB.Text;
SqlConnection objConn = new SqlConnection(sConnectionString);
objConn.Open();
SqlDataAdapter daPoint = new SqlDataAdapter("Select * From sysobjects where xtype='u'", objConn);
DataSet dsYC = new DataSet();
daPoint.Fill(dsYC);
objConn.Close();
return dsYC.Tables[0];
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//获得每个表的详细信息，并填充到dataset里面
public DataSet GetData()
{
string sConnectionString;
sConnectionString = tbDB.Text;
SqlConnection objConn = new SqlConnection(sConnectionString);
objConn.Open();
SqlDataAdapter daPoint = new SqlDataAdapter();
DataSet dsYC = new DataSet();
DataTable tableNames = GetAllTablesName();
for (int i = 0; i < tableNames.Rows.Count; i++)
{
try
{
string tableName = tableNames.Rows[i].ItemArray[0].ToString();
string select = "Select * From " + tableName;
daPoint = new SqlDataAdapter(select, objConn);
daPoint.FillSchema(dsYC, SchemaType.Mapped, tableName);
daPoint.Fill(dsYC);
objConn.Close();
}
catch (Exception)
{
//throw new Exception(ex.Message);
}
}
return dsYC;
}
//从systypes表中根据获得每个xtype对应的具体类型
private string GetSingleType(int xtype)
{
string sConnectionString;
sConnectionString = tbDB.Text;
SqlConnection objConn = new SqlConnection(sConnectionString);
objConn.Open();
SqlDataAdapter daPoint = new SqlDataAdapter("select * from systypes where xtype=" + xtype + "", objConn);
DataTable dt = new DataTable();
daPoint.Fill(dt);
objConn.Close();
if (dt.Rows.Count > 0)
{
return dt.Rows[0]["name"].ToString();
}
return "无此类型";
}
//获得每个字段的类型
private string GetDataType(int tableID, string columnName)
{
string dataType = "空";
string sConnectionString;
sConnectionString = tbDB.Text;
SqlConnection objConn = new SqlConnection(sConnectionString);
objConn.Open();
SqlDataAdapter daPoint = new SqlDataAdapter("select * from syscolumns where id=" + tableID + " and name='" + columnName + "'", objConn);
DataTable dt = new DataTable();
daPoint.Fill(dt);
objConn.Close();
if (dt.Rows.Count > 0)
{
dataType = GetSingleType(Convert.ToInt32(dt.Rows[0]["xtype"].ToString()));
}
return dataType;
}
public void CreateWordFile(DataSet ds)
{
try
{
ds.Tables.Remove("Table");
}
catch (Exception)
{ }
//不知为何有时会多出一个名叫Table的表，所以要移除掉
DataTable allTableID = GetAllTablesNameAndID();
string message = "";
//try
//{
Object Nothing = System.Reflection.Missing.Value;
object filename = saveFileDialog1.FileName;
//创建Word文档
Word.Application WordApp = new Word.ApplicationClass();
Word.Document WordDoc = WordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
WordApp.Selection.ParagraphFormat.LineSpacing = 15f;//设置文档的行间距
//移动焦点并换行
object count = 14;
object WdLine = Word.WdUnits.wdLine;//换一行;
WordApp.Selection.MoveDown(ref WdLine, ref count, ref Nothing);//移动焦点
WordApp.Selection.TypeParagraph();//插入段落
Word.Table newTable;
string singleDescription = "";
//遍历全部表
for (int i = 0; i < ds.Tables.Count; i++)
{
DataTable dt = ds.Tables[i];
//文档中创建表格
newTable = WordDoc.Tables.Add(WordApp.Selection.Range, dt.Columns.Count + 2, 3, ref Nothing, ref Nothing);
//设置表格样式
newTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleNone;
newTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleNone;
newTable.Columns[1].Width = 150f;
newTable.Columns[2].Width = 150f;
newTable.Columns[3].Width = 150f;
//填充表格内容
newTable.Cell(1, 1).Range.Text = dt.TableName;
newTable.Cell(1, 1).Range.Bold = 2;//设置单元格中字体为粗体
//合并单元格
newTable.Cell(1, 1).Merge(newTable.Cell(1, 3));
WordApp.Selection.Cells.VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;//垂直居中
WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
//填充表格内容
newTable.Cell(2, 1).Range.Text = "名称";
newTable.Cell(2, 2).Range.Text = "类型";
newTable.Cell(2, 3).Range.Text = "备注";
//遍历每个表中每个项
for (int j = 0; j < dt.Columns.Count; j++)
{
newTable.Cell(j + 3, 1).Range.Text = dt.Columns[j].ColumnName;
newTable.Cell(j + 3, 2).Range.Text = GetDataType(Convert.ToInt32(GetTableID(dt.TableName)), dt.Columns[j].ColumnName) + "（" + GetIfNull(Convert.ToInt32(GetTableID(dt.TableName)), dt.Columns[j].ColumnName) + "）";
newTable.Cell(j + 3, 3).Range.Text = singleDescription = GetSingleDescription(dt.TableName, j);
}
//设置分隔,要不然会出现一个个table嵌套的情况
Word.Paragraph WordParagh = WordDoc.Paragraphs.Add(ref Nothing);
WordParagh.Range.InsertParagraphAfter();
object mymissing = System.Reflection.Missing.Value;
object myunit = Word.WdUnits.wdStory;
WordApp.Selection.EndKey(ref myunit, ref mymissing);
}
//文件保存
WordDoc.SaveAs(ref filename, 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, ref Nothing);
message = "文档生成成功，以保存到C:CNSI下";
WordDoc.Close(ref Nothing, ref Nothing, ref Nothing);
WordApp.Quit(ref Nothing, ref Nothing, ref Nothing);
MessageBox.Show("生成成功！");
}
引用：http://blog.csdn.net/minjunyu/archive/2007/08/07/1729304.aspx
新手，代码可能比较乱，希望能对大家有所帮助~
