/// <summary>
/// 向当前活动表插入数据
/// </summary>
/// <param name="be"></param>
public void InsertData(ExcelBE be)
{
#if IS_USE_EXCEL_COM
worksheet.Cells[be.Row, be.Col] = be.Text;
workSheet_range = worksheet.get_Range(be.StartCell, be.EndCell);
workSheet_range.Merge(be.IsMerge);
workSheet_range.Interior.Color = GetColorValue(be.InteriorColor);
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.ColumnWidth = be.ColumnWidth;
workSheet_range.RowHeight = be.RowHeight;
//to-do:枚举HorizontalAlignment
if (be.HorizontalAlignmentIndex == 1)
{
workSheet_range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;;
}
else if (be.HorizontalAlignmentIndex == 2)
{
workSheet_range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;;
}
//to-do:设置单元格边框(未列在参数)
workSheet_range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
workSheet_range.Borders.Color = System.Drawing.Color.White.ToArgb();
workSheet_range.Font.Color = string.IsNullOrEmpty(be.FontColor) ? System.Drawing.Color.White.ToArgb() : System.Drawing.Color.Black.ToArgb();
workSheet_range.Font.Name = be.FontName;
workSheet_range.Font.Bold = be.FontBold;
workSheet_range.Font.Size = be.FontSize;
workSheet_range.NumberFormat = be.Formart;
#endif
}
/// <summary>
/// 在当前列上加链接
/// </summary>
/// <param name="sheetName">sheet名称</param>
/// <param name="screenTipMsg">鼠标移上去显示文字</param>
public void AddLink(ExcelBE be, string sheetName, string screenTipMsg)
{
#if IS_USE_EXCEL_COM
string hyperlinkTargetAddress = sheetName + "!A1";
worksheet.Hyperlinks.Add(
workSheet_range,
string.Empty,
hyperlinkTargetAddress,
sheetName + "!A1-" + screenTipMsg,
sheetName);
workSheet_range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
workSheet_range.Borders.Color = System.Drawing.Color.White.ToArgb();
workSheet_range.Font.Color = string.IsNullOrEmpty(be.FontColor) ? System.Drawing.Color.White.ToArgb() : System.Drawing.Color.Black.ToArgb();
workSheet_range.Font.Name = be.FontName;
workSheet_range.Font.Bold = be.FontBold;
workSheet_range.Font.Size = be.FontSize;
workSheet_range.NumberFormat = be.Formart;
#endif
}
/// <summary>
/// 在当前列上加链接
/// </summary>
/// <param name="sheetName">sheet名称</param>
/// <param name="screenTipMsg">鼠标移上去显示文字</param>
public void AddLink(ExcelBE be,string sheetName, string screenTipMsg)
{
#if IS_USE_EXCEL_COM
string hyperlinkTargetAddress = sheetName+"!A1";
worksheet.Hyperlinks.Add(
workSheet_range,
string.Empty,
hyperlinkTargetAddress,
sheetName + "!A1-"+screenTipMsg,
sheetName);
workSheet_range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
workSheet_range.Borders.Color = System.Drawing.Color.White.ToArgb();
workSheet_range.Font.Color = string.IsNullOrEmpty(be.FontColor) ? System.Drawing.Color.White.ToArgb() : System.Drawing.Color.Black.ToArgb();
workSheet_range.Font.Name = be.FontName;
workSheet_range.Font.Bold = be.FontBold;
workSheet_range.Font.Size = be.FontSize;
workSheet_range.NumberFormat = be.Formart;
#endif
}
/// <summary>
/// 点击生成数据库说明Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExportexcel_Click(object sender, EventArgs e)
{
btnExportexcel.Enabled = false;
UtilExcelCom.Release();
ExcelBE be = null;
Dictionary<string, Dictionary<string, string>> tableInfos;
tableInfos = (cbDbType.SelectedIndex == 0) ? UtilSqlserver.TableinfoList() : UtilMysql.TableinfoList();
int rowHeight = 25;
//数据库表说明
UtilExcelCom.Current().SetSheet("Summary");
be = new ExcelBE(1, 1, "编号", "A1", "A1", "GRAYDARK", false, 10, 13.50, rowHeight, 2, null, "Century Gothic", 10, true, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(1, 2, "表名称", "B1", "B1", "GRAYDARK", false, 10, 32.63, rowHeight, 2, null, "Century Gothic", 10, true, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(1, 3, "说明", "C1", "C1", "GRAYDARK", false, 10, 24.50, rowHeight, 2, null, "Century Gothic", 10, true, null);
UtilExcelCom.Current().InsertData(be);
int rowno = 2;
int index = 1;
int line = 1;
string idIndex = "";
string tablet_name = "", table_comment = "";
string[] commentArr;
ArrayList commentList;
foreach (Dictionary<string, string> tablename in tableInfos.Values)
{
table_comment = "";
idIndex = index.ToString();
if (idIndex.Length < 2) idIndex = "0" + idIndex;
be = new ExcelBE(rowno, 1, "A" + idIndex, "A" + rowno, "A" + rowno, null, false, 10, 13.50, rowHeight, 2, null, "Century Gothic", 10, false, null);
UtilExcelCom.Current().InsertData(be);
tablet_name=tablename["Name"];
be = new ExcelBE(rowno, 2, tablet_name, "B" + rowno, "B" + rowno, null, false, 10, 32.63, rowHeight, 1, null, "Century Gothic", 10, false, null);
UtilExcelCom.Current().InsertData(be);
//添加链接
UtilExcelCom.Current().AddLink(be,tablet_name,"列名");
table_comment = tablename["Comment"];
table_comment = table_comment.Trim();
line = 1;
rowHeight = 25;
if (UtilString.Contains(table_comment, "\r", "\n"))
{
rowHeight = 16;
commentArr = table_comment.Split(new char[2] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
rowHeight = rowHeight * line;
commentList = new ArrayList(commentArr);
table_comment = "";
foreach (var commenti in commentList)
{
table_comment += commenti + "\r\n";
line += 1;
}
line -= 1;
table_comment = table_comment.Substring(0, table_comment.Length - 2);
rowHeight = rowHeight * line + 8;
}
be = new ExcelBE(rowno, 3, table_comment, "C" + rowno, "C" + rowno, null, false, 10, 24.50, rowHeight, 1, null, "Century Gothic", 10, false, null);
UtilExcelCom.Current().InsertData(be);
rowno++;
index++;
}
//数据库各表详细说明
Dictionary<string, Dictionary<string, string>> columnInfos;
string comment="", dicComment="";
foreach (Dictionary<string, string> tablename in tableInfos.Values)
{
rowHeight = 16;
columnInfos = (cbDbType.SelectedIndex == 0) ? UtilSqlserver.FieldInfoList(tablename["Name"]) : UtilMysql.FieldInfoList(tablename["Name"]);
UtilExcelCom.Current().AddSheet(tablename["Name"]);
be = new ExcelBE(1, 1, "列名", "A1", "A1", "GRAYDARK", false, 10, 36.50, rowHeight, 2, null, "Century Gothic", 10, true, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(1, 2, "数据类型", "B1", "B1", "GRAYDARK", false, 10, 24.50, rowHeight, 2, null, "Century Gothic", 10, true, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(1, 3, "长度", "C1", "C1", "GRAYDARK", false, 10, 24.50, rowHeight, 2, null, "Century Gothic", 10, true, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(1, 4, "允许NULL", "D1", "D1", "GRAYDARK", false, 10, 24.50, rowHeight, 2, null, "Century Gothic", 10, true, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(1, 5, "键值", "E1", "E1", "GRAYDARK", false, 10, 24.50, rowHeight, 2, null, "Century Gothic", 10, true, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(1, 6, "说明", "F1", "F1", "GRAYDARK", false, 10, 39, rowHeight, 2, null, "Century Gothic", 10, true, null);
UtilExcelCom.Current().InsertData(be);
bool isDicComment = false;
foreach (Dictionary<string, string> columnInfo in columnInfos.Values)
{
if (columnInfo.Keys.Contains("Comment"))
{
comment = columnInfo["Comment"];
}
else
{
comment = "";
}
if (UtilString.Contains(comment, "\r", "\n"))
{
if (columnInfo["Type"].Equals("char"))
{
isDicComment = true;
break;
}
}
}
if (isDicComment)
{
be = new ExcelBE(1, 7, "数据字典定义", "G1", "G1", "GRAYDARK", false, 10, 48.50, rowHeight, 2, null, "Century Gothic", 10, true, null);
UtilExcelCom.Current().InsertData(be);
}
int tablerowno = 2;
bool isColumnDicComment = false;
foreach (Dictionary<string, string> columnInfo in columnInfos.Values)
{
rowHeight = 16;
if (columnInfo.Keys.Contains("Comment"))
comment = columnInfo["Comment"];
else
comment = "";
isColumnDicComment = false;
dicComment = "";
line = 1;
comment = comment.Trim();
if (isDicComment)
{
if (UtilString.Contains(comment, "\r", "\n"))
{
commentArr = comment.Split(new char[2] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
comment = commentArr[0];
commentList = new ArrayList(commentArr);
commentList.Remove(0);
foreach (var commenti in commentList)
{
dicComment += commenti + "\r\n";
line += 1;
}
line -= 1;
dicComment = dicComment.Substring(0, dicComment.Length - 2);
rowHeight = rowHeight * line + 8;
if (columnInfo["Type"].Equals("char"))
isColumnDicComment = true;
else
comment = dicComment;
}
else
{
comment = comment.Trim();
if (UtilString.Contains(comment, "\r", "\n"))
{
commentArr = comment.Split(new char[2] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
commentList = new ArrayList(commentArr);
comment = "";
foreach (var commenti in commentList)
{
comment += commenti + "\r\n";
line += 1;
}
line -= 1;
comment = comment.Substring(0, comment.Length - 2);
rowHeight = rowHeight * line + 8;
}
}
}
else
{
comment = comment.Trim();
if (UtilString.Contains(comment, "\r", "\n"))
{
commentArr = comment.Split(new char[2] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
commentList = new ArrayList(commentArr);
comment = "";
foreach (var commenti in commentList)
{
comment += commenti + "\r\n";
line += 1;
}
line -= 1;
comment = comment.Substring(0, comment.Length - 2);
rowHeight = rowHeight * line + 8;
}
}
be = new ExcelBE(tablerowno, 1, columnInfo["Field"], "A" + tablerowno, "A" + tablerowno, null, false, 10, 18, rowHeight, 2, null, "Century Gothic", 10, false, null);
UtilExcelCom.Current().InsertData(be);
string Type_Only = "";
if (cbDbType.SelectedIndex == 0) Type_Only = columnInfo["Type"]; else Type_Only = columnInfo["Type_Only"];
be = new ExcelBE(tablerowno, 2, Type_Only, "B" + tablerowno, "B" + tablerowno, null, false, 10, 15, rowHeight, 1, null, "Century Gothic", 10, false, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(tablerowno, 3, columnInfo["Length"], "C" + tablerowno, "C" + tablerowno, null, false, 10, 9, rowHeight, 1, null, "Century Gothic", 10, false, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(tablerowno, 4, columnInfo["Null"], "D" + tablerowno, "D" + tablerowno, null, false, 10, 9, rowHeight, 1, null, "Century Gothic", 10, false, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(tablerowno, 5, columnInfo["Fkpk"], "E" + tablerowno, "E" + tablerowno, null, false, 10, 9, rowHeight, 1, null, "Century Gothic", 10, false, null);
UtilExcelCom.Current().InsertData(be);
be = new ExcelBE(tablerowno, 6, comment, "F" + tablerowno, "F" + tablerowno, null, false, 10, 39, rowHeight, 1, null, "Century Gothic", 10, false, null);
UtilExcelCom.Current().InsertData(be);
if (isDicComment)
{
if (!isColumnDicComment)dicComment = "";
be = new ExcelBE(tablerowno, 7, dicComment, "G" + tablerowno, "G" + tablerowno, null, false, 10, 45, rowHeight, 1, null, "Century Gothic", 10, false, null);
UtilExcelCom.Current().InsertData(be);
}
tablerowno++;
}
}
UtilExcelCom.Current().SetActivateSheet(1);
//显示Excel
UtilExcelCom.Current().DoExport();
string sitename = ConfigurationManager.AppSettings["SiteName"];
UtilExcelCom.Current().Save(Path.Combine(System.Environment.CurrentDirectory, sitename + "数据模型.xlsx"));
btnExportexcel.Enabled = true;
}
/// <summary>
/// 向当前活动表插入数据
/// </summary>
/// <param name="be"></param>
public void InsertData(ExcelBE be)
{
#if IS_USE_EXCEL_COM
worksheet.Cells[be.Row, be.Col] = be.Text;
workSheet_range = worksheet.get_Range(be.StartCell, be.EndCell);
workSheet_range.Merge(be.IsMerge);
workSheet_range.Interior.Color = GetColorValue(be.InteriorColor);
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.ColumnWidth = be.ColumnWidth;
workSheet_range.RowHeight = be.RowHeight;
//to-do:枚举HorizontalAlignment
if (be.HorizontalAlignmentIndex == 1)
{
workSheet_range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; ;
}
else if (be.HorizontalAlignmentIndex == 2)
{
workSheet_range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; ;
}
//to-do:设置单元格边框(未列在参数)
workSheet_range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
workSheet_range.Borders.Color = System.Drawing.Color.White.ToArgb();
workSheet_range.Font.Color = string.IsNullOrEmpty(be.FontColor) ? System.Drawing.Color.White.ToArgb() : System.Drawing.Color.Black.ToArgb();
workSheet_range.Font.Name = be.FontName;
workSheet_range.Font.Bold = be.FontBold;
workSheet_range.Font.Size = be.FontSize;
workSheet_range.NumberFormat = be.Formart;
#endif
}