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());
}
