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