public new void Dispose()
{
if (AutoFilter != null)
{
AutoFilter.Dispose();
}
base.Dispose();
}
private IAsset ReadFromSheet(Worksheet sheet)
{
// We need this sheet to be active so that we can access to its window via Application.ActiveWindow.
sheet.Activate();
var cells = new CellAccessor(sheet);
// Find the header and data rows.
int header_row, data_row;
{
AutoFilter auto_filter = null;
Window active_window = null;
if ((auto_filter = sheet.AutoFilter) != null)
{
// This worksheet has an auto filter.
// Use the filter's header row (i.e., those with filter buttons) as the header,
// and the rows below it as the data.
header_row = auto_filter.Range.Row;
data_row = header_row + 1;
}
else if ((active_window = sheet.Application.ActiveWindow).FreezePanes)
{
// This worksheet has freeze panes.
// Use the last non-empty row on the frozen pane as the header,
// and the non-frozen rows as the data.
var r = active_window.ScrollRow;
var panes = active_window.Panes;
for (int i = 1; i <= panes.Count; i++)
{
var pane = panes[i];
r = Math.Min(r, pane.ScrollRow + pane.VisibleRange.Rows.Count);
}
data_row = r;
while (--r > 1 && cells.IsRowEmpty(r))
{
;
}
header_row = r;
}
else
{
// Otherwise, use the first row as the header (even if it is totally empty),
// and the remainig rows as data.
header_row = 1;
data_row = 2;
}
active_window?.Dispose();
auto_filter?.Dispose();
}
// Fetch column headers on the first row.
var headers = new string[cells.Columns + 1];
for (int column = 1; column <= cells.Columns; column++)
{
var label = cells[header_row, column];
headers[column] = string.IsNullOrWhiteSpace(label) ? null : label;
}
// Detect source and target languages.
// We only consider visible cells for source and target language columns.
string source_label = null, source_language = null;
string target_label = null, target_language = null;
for (int column = 1; column <= cells.Columns; column++)
{
if (cells.IsCellVisible(header_row, column))
{
var lang = FindLanguageCode(headers[column]);
if (lang != null)
{
if (source_language == null)
{
// Assume the first language code represents the source language.
source_label = headers[column];
source_language = lang;
}
else if (target_language == null && lang != source_language)
{
// Assume the second language code represents the target language.
target_label = headers[column];
target_language = lang;
break;
}
}
}
}
// Group columns into source, target, and other properties.
var source_columns = new List <int>();
var target_columns = new List <int>();
var properties_columns = new List <int>();
for (int column = 1; column <= cells.Columns; column++)
{
var label = headers[column];
if (source_label?.Equals(label, StringComparison.InvariantCultureIgnoreCase) == true)
{
source_columns.Add(column);
}
else if (target_label?.Equals(label, StringComparison.InvariantCultureIgnoreCase) == true)
{
target_columns.Add(column);
}
else
{
properties_columns.Add(column);
}
}
// Detect duplicate header labels (i.e., property names)
var duplicates = new HashSet <string>(headers.Where(h => h != null).GroupBy(h => h).Where(g => g.Count() > 1).Select(g => g.Key));
// Modify header labels so that they are unique.
// Note that the following modification could produce new ducplicates.
// We don't go too strict, because this is just a nice-to-have feature.
foreach (var column in properties_columns)
{
var label = headers[column];
if (label == null)
{
// No label. Use Excel column name as a label.
headers[column] = cells.GetColumnName(column);
}
else if (duplicates.Contains(label))
{
// Duplicate label. Append the column name.
headers[column] = string.Format("{0} ({1})", label, cells.GetColumnName(column));
}
else
{
// Already unique name. Leave it alone.
}
}
// Turn data rows into TransPairs.
var pairs = new List <ITransPair>(cells.Rows); // This capacity is just a rough standard.
int serial = 0;
var sources = new List <InlineString>(source_columns.Count);
var targets = new List <InlineString>(target_columns.Count);
var properties = new List <string>(properties_columns.Count);
for (int row = data_row; row <= cells.Rows; row++)
{
if (cells.IsRowEmpty(row))
{
// Ignore an empty row.
}
else
{
var visible = cells.IsRowVisible(row);
var id = row.ToString();
// Collect source texts.
sources.Clear();
sources.AddRange(source_columns.Select(c => cells[row, c]).Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => new InlineString(s)));
if (sources.Count == 0)
{
sources.Add(InlineString.Empty);
}
// Collect target texts.
targets.Clear();
targets.AddRange(target_columns.Select(c => cells[row, c]).Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => new InlineString(s)));
if (targets.Count == 0)
{
targets.Add(InlineString.Empty);
}
// Collect property values.
properties.Clear();
properties.AddRange(properties_columns.Select(c => cells[row, c]));
int last = properties.Count - 1;
while (last > 0 && string.IsNullOrWhiteSpace(properties[last]))
{
--last;
}
if (last < properties.Count - 1)
{
properties.RemoveRange(last + 1, properties.Count - last - 1);
}
var properties_array = properties.Count > 0 ? properties.ToArray() : EMPTY_PROPERTY_VALUES;
// Create TransPairs.
// We allow multiple source and/or target texts in an Excel row,
// so we generate all possible source-target combinations.
foreach (var source in sources)
{
foreach (var target in targets)
{
pairs.Add(new ExcelGlossaryPair()
{
Serial = visible ? ++serial : -1,
Id = id,
Source = source,
Target = target,
SourceLang = source_language,
TargetLang = target_language,
PropertyValues = properties_array,
});
}
}
}
}
if (pairs.Count == 0)
{
return(null);
}
pairs.TrimExcess();
return(new ExcelGlossaryAsset()
{
Package = (sheet.Parent as Workbook)?.FullName,
Original = sheet.Name,
SourceLang = source_language,
TargetLang = target_language,
TransPairs = pairs,
Properties = properties_columns.Select(column => new PropInfo(headers[column], false)).ToArray(),
});
}
