CheckForLeadingEqualSign ( String sAttributeValue ) { Debug.Assert(sAttributeValue != null); // If you try to programmatically insert a value that starts with an // equal sign into an Excel cell and the value is not a valid formula, // you'll get this: // // [COMException]: Exception from HRESULT: 0x800A03EC // // This can be fixed by inserting an apostrophe before the equal sign, // which forces Excel to treat the value as text instead of a formula. // // Note: Don't use String.StartsWith() here. That won't detect an // equal sign at index position zero in right-to-left languages. if (sAttributeValue.Length > 0 && sAttributeValue[0] == '=') { sAttributeValue = ExcelTextUtil.ForceCellText(sAttributeValue); } return(sAttributeValue); }
/// <summary> /// get customized datatable /// </summary> /// <param name="filePath">the source file path, contain data, txt or excel</param> /// <param name="result">template of xml that we setup with ourself</param> /// <param name="pattern">regex of the file, replace some string to the other string</param> /// <param name="replacement">replacement string</param> /// <returns>customized datatable</returns> public static DataTable GetCustomizedDataTable(string filePath, ParserEntity result, string pattern, string replacement) { DataTable dataTable = ExcelTextUtil.ReadText(filePath, result.SplitRegex, pattern, replacement); DataTable dt = GetCustomizedDataTable(result, dataTable); return(dt); }
/// <summary> /// customized datatable /// </summary> /// <param name="filePath">the source file path, contain data, txt or excel</param> /// <param name="result">template of xml that we setup with ourself</param> /// <returns>customized datatable</returns> public static DataTable GetCustomizedDataTable(string filePath, ParserEntity result) { try { DataTable dataTable = null; if (result.IsExcel) { try { dataTable = ExcelTextUtil.ReadSheet(filePath, result.SheetName); } catch (Exception ex) { try { dataTable = ExcelTextUtil.ReadFirstSheet(filePath); } catch (Exception exp) { throw new Exception("Excel 文件格式不正确,请检查!"); } } } else { dataTable = ExcelTextUtil.ReadText(filePath, result.SplitRegex); } DataTable dt = GetCustomizedDataTable(result, dataTable); return(dt); } catch (Exception e) { throw new Exception("Excel 文件格式不正确,请检查。"); } }
AddGraphMetricValuesForTopWordsAndWordPairs ( IEnumerable <IEdge> edges, String statusEdgeColumnName, Int32 maximumTopTerms, WordCounter wordCounter, WordPairCounter wordPairCounter, Int32 vertexRowID, List <GraphMetricValueWithID> topWordsInTweetByCountGraphMetricValues, List <GraphMetricValueWithID> topWordsInTweetBySalienceGraphMetricValues, List <GraphMetricValueWithID> topWordPairsInTweetByCountGraphMetricValues, List <GraphMetricValueWithID> topWordPairsInTweetBySalienceGraphMetricValues ) { Debug.Assert(edges != null); Debug.Assert(!String.IsNullOrEmpty(statusEdgeColumnName)); Debug.Assert(maximumTopTerms > 0); Debug.Assert(wordCounter != null); Debug.Assert(wordPairCounter != null); Debug.Assert(topWordsInTweetByCountGraphMetricValues != null); Debug.Assert(topWordsInTweetBySalienceGraphMetricValues != null); Debug.Assert(topWordPairsInTweetByCountGraphMetricValues != null); Debug.Assert(topWordPairsInTweetBySalienceGraphMetricValues != null); String sTopWordsInTweetByCount, sTopWordsInTweetBySalience, sTopWordPairsInTweetByCount, sTopWordPairsInTweetBySalience; ConcatenateTopWordsAndWordPairs(edges, statusEdgeColumnName, maximumTopTerms, wordCounter, wordPairCounter, out sTopWordsInTweetByCount, out sTopWordsInTweetBySalience, out sTopWordPairsInTweetByCount, out sTopWordPairsInTweetBySalience); topWordsInTweetByCountGraphMetricValues.Add( new GraphMetricValueWithID(vertexRowID, ExcelTextUtil.ForceCellText(sTopWordsInTweetByCount))); topWordsInTweetBySalienceGraphMetricValues.Add( new GraphMetricValueWithID(vertexRowID, ExcelTextUtil.ForceCellText(sTopWordsInTweetBySalience))); topWordPairsInTweetByCountGraphMetricValues.Add( new GraphMetricValueWithID(vertexRowID, ExcelTextUtil.ForceCellText(sTopWordPairsInTweetByCount))); topWordPairsInTweetBySalienceGraphMetricValues.Add( new GraphMetricValueWithID(vertexRowID, ExcelTextUtil.ForceCellText(sTopWordPairsInTweetBySalience))); }
ConcatenateTopWordsOrWordPairs ( List <GraphMetricValueOrdered> topWordsOrWordPairs, Boolean concatenateTopWords, Int32 maximumTopWordsOrWordPairs ) { Debug.Assert(topWordsOrWordPairs != null); Debug.Assert(maximumTopWordsOrWordPairs > 0); return(String.Join( concatenateTopWords ? WordSeparator : WordPairSeparator, TwitterSearchNetworkStringUtil.TakeTopStringsAsArray( (from oGroupMetricValueWithID in topWordsOrWordPairs select ExcelTextUtil.UnforceCellText( (String)oGroupMetricValueWithID.Value)) , maximumTopWordsOrWordPairs) )); }
CopyWordMetricsForGroup ( String groupNameOrDummyGroupName, GraphMetricValueOrdered [] word1Values, GraphMetricValueOrdered [] word2Values, GraphMetricValueOrdered [] countValues, GraphMetricValueOrdered [] groupNameValues, Int32 firstRowForGroup, Int32 maximumTopWordsOrWordPairs, List <GraphMetricValueOrdered> topWordsOrWordPairs, List <GraphMetricValueOrdered> topCounts ) { Debug.Assert(!String.IsNullOrEmpty(groupNameOrDummyGroupName)); Debug.Assert(word1Values != null); // word2Values Debug.Assert(countValues != null); Debug.Assert(groupNameValues != null); Debug.Assert(firstRowForGroup >= 0); Debug.Assert(maximumTopWordsOrWordPairs > 0); Debug.Assert(topWordsOrWordPairs != null); Debug.Assert(topCounts != null); Int32 iRows = groupNameValues.Length; for (Int32 iRow = firstRowForGroup, iItems = 0; iRow < iRows && iItems < maximumTopWordsOrWordPairs; iRow++, iItems++) { Object oWord1AsObject = word1Values[iRow].Value; Object oCountAsObject = countValues[iRow].Value; Object oGroupNameAsObject = groupNameValues[iRow].Value; if ( !(oGroupNameAsObject is String) || (String)oGroupNameAsObject != groupNameOrDummyGroupName || !(oWord1AsObject is String) || !(oCountAsObject is Int32) ) { break; } String sWordOrWordPair = ExcelTextUtil.UnforceCellText( (String)oWord1AsObject); if (word2Values != null) { Object oWord2AsObject = word2Values[iRow].Value; if (!(oWord2AsObject is String)) { break; } sWordOrWordPair = FormatWordPair( sWordOrWordPair, ExcelTextUtil.UnforceCellText((String)oWord2AsObject)); } topWordsOrWordPairs.Add(new GraphMetricValueOrdered( ExcelTextUtil.ForceCellText(sWordOrWordPair))); topCounts.Add(new GraphMetricValueOrdered(oCountAsObject)); } }
Convert <TGroup> ( ICollection <TGroup> groups, GroupToGroupVertices <TGroup> groupToGroupVertices, GroupToString <TGroup> groupToGroupName, Boolean collapseGroups, GroupToString <TGroup> groupToCollapsedGroupAttributes ) { Debug.Assert(groups != null); Debug.Assert(groupToGroupVertices != null); // These columns are needed: // // * Group name on the group worksheet. // // * Vertex color on the group worksheet. // // * Vertex shape on the group worksheet. // // * Group name on the group-vertex worksheet. // // * Vertex name on the group-vertex worksheet. // // * Vertex ID on the group-vertex worksheet. This gets copied from // the hidden ID column on the Vertex worksheet via an Excel // formula. // These columns might be needed: // // * Collapsed flag on the group worksheet. // // * Collapsed group attributes on the group worksheet. List <GraphMetricValueOrdered> oGroupNamesForGroupWorksheet = new List <GraphMetricValueOrdered>(); List <GraphMetricValueOrdered> oVertexColorsForGroupWorksheet = new List <GraphMetricValueOrdered>(); List <GraphMetricValueOrdered> oVertexShapesForGroupWorksheet = new List <GraphMetricValueOrdered>(); List <GraphMetricValueOrdered> oCollapsedFlagsForGroupWorksheet = new List <GraphMetricValueOrdered>(); List <GraphMetricValueOrdered> oCollapsedAttributesForGroupWorksheet = new List <GraphMetricValueOrdered>(); List <GraphMetricValueOrdered> oGroupNamesForGroupVertexWorksheet = new List <GraphMetricValueOrdered>(); List <GraphMetricValueOrdered> oVertexNamesForGroupVertexWorksheet = new List <GraphMetricValueOrdered>(); // This column contains a constant value, which is a formula. GraphMetricValueOrdered [] aoVertexIDsForGroupVertexWorksheet = { new GraphMetricValueOrdered( GroupManager.GetExcelFormulaForVertexID()) }; Int32 iGroups = groups.Count; Int32 iGroup = 0; ColorConverter2 oColorConverter2 = new ColorConverter2(); VertexShapeConverter oVertexShapeConverter = new VertexShapeConverter(); GraphMetricValueOrdered oTrueGraphMetricValueOrdered = new GraphMetricValueOrdered( (new BooleanConverter()).GraphToWorkbook(true)); foreach (TGroup oGroup in from oGroup in groups orderby groupToGroupVertices(oGroup).Count descending select oGroup) { String sGroupName; if (groupToGroupName == null) { sGroupName = 'G' + (iGroup + 1).ToString( CultureInfo.InvariantCulture); } else { sGroupName = groupToGroupName(oGroup); } Color oColor; VertexShape eShape; GroupManager.GetVertexAttributes(iGroup, iGroups, out oColor, out eShape); GraphMetricValueOrdered oGroupNameGraphMetricValue = new GraphMetricValueOrdered(sGroupName); oGroupNamesForGroupWorksheet.Add(oGroupNameGraphMetricValue); // Write the color in a format that is understood by // ColorConverter2.WorkbookToGraph(), which is what // WorksheetReaderBase uses. oVertexColorsForGroupWorksheet.Add( new GraphMetricValueOrdered( oColorConverter2.GraphToWorkbook(oColor))); oVertexShapesForGroupWorksheet.Add( new GraphMetricValueOrdered( oVertexShapeConverter.GraphToWorkbook(eShape))); if (collapseGroups) { oCollapsedFlagsForGroupWorksheet.Add( oTrueGraphMetricValueOrdered); } if (groupToCollapsedGroupAttributes != null) { oCollapsedAttributesForGroupWorksheet.Add( new GraphMetricValueOrdered( groupToCollapsedGroupAttributes(oGroup))); } Int32 iVertices = 0; foreach (IVertex oVertex in groupToGroupVertices(oGroup)) { oGroupNamesForGroupVertexWorksheet.Add( oGroupNameGraphMetricValue); oVertexNamesForGroupVertexWorksheet.Add( new GraphMetricValueOrdered( ExcelTextUtil.ForceCellText(oVertex.Name))); iVertices++; } iGroup++; } List <GraphMetricColumn> oGraphMetricColumns = new List <GraphMetricColumn>(); oGraphMetricColumns.AddRange( new GraphMetricColumn [] { new GraphMetricColumnOrdered(WorksheetNames.Groups, TableNames.Groups, GroupTableColumnNames.Name, ExcelTableUtil.AutoColumnWidth, null, CellStyleNames.Required, oGroupNamesForGroupWorksheet.ToArray() ), new GraphMetricColumnOrdered(WorksheetNames.Groups, TableNames.Groups, GroupTableColumnNames.VertexColor, ExcelTableUtil.AutoColumnWidth, null, CellStyleNames.VisualProperty, oVertexColorsForGroupWorksheet.ToArray() ), new GraphMetricColumnOrdered(WorksheetNames.Groups, TableNames.Groups, GroupTableColumnNames.VertexShape, ExcelTableUtil.AutoColumnWidth, null, CellStyleNames.VisualProperty, oVertexShapesForGroupWorksheet.ToArray() ), new GraphMetricColumnOrdered(WorksheetNames.GroupVertices, TableNames.GroupVertices, GroupVertexTableColumnNames.GroupName, ExcelTableUtil.AutoColumnWidth, null, null, oGroupNamesForGroupVertexWorksheet.ToArray() ), new GraphMetricColumnOrdered(WorksheetNames.GroupVertices, TableNames.GroupVertices, GroupVertexTableColumnNames.VertexName, ExcelTableUtil.AutoColumnWidth, null, null, oVertexNamesForGroupVertexWorksheet.ToArray() ), new GraphMetricColumnOrdered(WorksheetNames.GroupVertices, TableNames.GroupVertices, GroupVertexTableColumnNames.VertexID, ExcelTableUtil.AutoColumnWidth, null, null, aoVertexIDsForGroupVertexWorksheet ), }); if (collapseGroups) { oGraphMetricColumns.Add( new GraphMetricColumnOrdered(WorksheetNames.Groups, TableNames.Groups, GroupTableColumnNames.Collapsed, ExcelTableUtil.AutoColumnWidth, null, CellStyleNames.VisualProperty, oCollapsedFlagsForGroupWorksheet.ToArray() )); } if (groupToCollapsedGroupAttributes != null) { oGraphMetricColumns.Add( new GraphMetricColumnOrdered(WorksheetNames.Groups, TableNames.Groups, GroupTableColumnNames.CollapsedAttributes, ExcelTableUtil.AutoColumnWidth, null, CellStyleNames.DoNotEdit, oCollapsedAttributesForGroupWorksheet.ToArray() )); } return(oGraphMetricColumns.ToArray()); }