public List <SpreadsheetColumn> GetColumns(SpreadsheetConfiguration configuration, string worksheetName)
{
using (InitializeProvider(configuration))
{
SpreadsheetTable table = configuration.Tables.Find(c => c.Name == worksheetName);
if (table != null)
{
return(_provider.GetColumns(table));
}
else
{
return(new List <SpreadsheetColumn>());
}
}
}
public List <SpreadsheetColumn> GetColumns(SpreadsheetTable table)
{
if (table.Columns == null)
{
WorksheetPart worksheetPart = GetWorksheetPart(table);
List <SpreadsheetColumn> columns = new List <SpreadsheetColumn>();
Row row = worksheetPart.Worksheet.Descendants <Row>().Where(r => r.RowIndex == 1).First();
foreach (Cell cell in row.ChildElements)
{
string value = GetValue(cell);
if (table.HeaderRow == 0)
{
value = SpreadsheetReference.GetColumnName(cell.CellReference);
}
SpreadsheetColumn column = new SpreadsheetColumn
{
Name = value,
Label = value,
DataType = GetDataType(cell.DataType),
ColumnIdx = SpreadsheetReference.GetColumnName(cell.CellReference),
DataLength = GetDataLength(GetDataType(cell.DataType))
};
columns.Add(column);
}
return(columns);
}
else
{
return(table.Columns);
}
}
public SpreadsheetConfiguration ProcessConfiguration(SpreadsheetConfiguration configuration, Stream inputFile)
{
Dispose();
List <SpreadsheetTable> tables = new List <SpreadsheetTable>();
if (inputFile == null)
{
_document = GetDocument(_configuration.Location);
}
else
{
_document = SpreadsheetDocument.Open(inputFile, false);
//C:\iring-tools\branches\2.4.x
//_document = SpreadsheetDocument.Open("C:\\iring-tools\\branches\\2.4.x\\Test890.xlsx", false);
}
DefinedNames definedNames = _document.WorkbookPart.Workbook.DefinedNames;
if (definedNames != null)
{
foreach (DefinedName definedName in definedNames)
{
SpreadsheetTable table = new SpreadsheetTable
{
TableType = TableType.DefinedName,
Name = definedName.Name,
Label = definedName.Name,
Reference = definedName.InnerText,
HeaderRow = 1
};
table.Columns = GetColumns(table);
table.Identifier = table.Columns[0].Name;
tables.Add(table);
}
}
Sheets sheets = _document.WorkbookPart.Workbook.Sheets;
if (sheets != null)
{
foreach (Sheet sheet in sheets)
{
if (sheet.Name.InnerText.StartsWith("Sheet"))
{
continue;
}
WorksheetPart worksheetPart = (WorksheetPart)_document.WorkbookPart.GetPartById(sheet.Id);
SpreadsheetTable table = new SpreadsheetTable
{
TableType = TableType.Worksheet,
Name = sheet.Name,
Label = sheet.Name,
Reference = string.Format("'{0}!'{1}", sheet.Name, worksheetPart.Worksheet.SheetDimension.Reference),
HeaderRow = 1
};
table.Columns = GetColumns(table);
table.Identifier = table.Columns[0].Name;
tables.Add(table);
}
}
configuration.Tables = tables;
return(configuration);
}
public WorksheetPart GetWorksheetPart(SpreadsheetTable table)
{
string sheetName = SpreadsheetReference.GetSheetName(table.Reference);
return(GetWorksheetPart(sheetName));
}
private Response SaveDataObjects(string objectType, IList <IDataObject> dataObjects)
{
try
{
Response response = new Response();
SpreadsheetTable cfTable = _provider.GetConfigurationTable(objectType);
SpreadsheetReference tableReference = cfTable.GetReference();
WorksheetPart worksheetPart = _provider.GetWorksheetPart(cfTable);
foreach (IDataObject dataObject in dataObjects)
{
Status status = new Status();
try
{
string identifier = GetIdentifier(dataObject);
status.Identifier = identifier;
SpreadsheetColumn column = cfTable.Columns.First <SpreadsheetColumn>(c => c.Name.Equals(cfTable.Identifier));
Cell cell = worksheetPart.Worksheet.Descendants <Cell>().FirstOrDefault(c => SpreadsheetReference.GetColumnName(c.CellReference).Equals(column.ColumnIdx) && _provider.GetValue(c).Equals(identifier));
if (cell != null)
{
Row existingRow = cell.Ancestors <Row>().First();
foreach (SpreadsheetColumn col in cfTable.Columns)
{
Cell existingCell = existingRow.Descendants <Cell>().First(c => SpreadsheetReference.GetColumnName(c.CellReference).Equals(col.ColumnIdx));
existingCell.DataType = SpreadsheetProvider.GetCellValue(col.DataType);
if (!string.IsNullOrEmpty(Convert.ToString(dataObject.GetPropertyValue(col.Name))))
{
existingCell.CellValue.Text = Convert.ToString(dataObject.GetPropertyValue(col.Name));
}
else if (existingCell.CellValue == null)
{
existingCell.CellValue = new CellValue(Convert.ToString(dataObject.GetPropertyValue(col.Name)));
}
}
}
else
{
tableReference.EndRow++;
Row newRow = new Row
{
RowIndex = (UInt32Value)tableReference.EndRow,
Spans = new ListValue <StringValue>
{
InnerText = string.Format("1:{0}", cfTable.Columns.Count)
}
};
foreach (SpreadsheetColumn col in cfTable.Columns)
{
Cell newCell = new Cell
{
CellReference = string.Format("{0}{1}", col.ColumnIdx, newRow.RowIndex),
DataType = SpreadsheetProvider.GetCellValue(col.DataType),
CellValue = new CellValue(Convert.ToString(dataObject.GetPropertyValue(col.Name)))
};
newRow.Append(newCell);
}
SheetData sheetData = (SheetData)worksheetPart.Worksheet.Descendants <SheetData>().First();
sheetData.Append(newRow);
}
worksheetPart.Worksheet.SheetDimension.Reference = tableReference.GetReference(false);
cfTable.Reference = tableReference.GetReference(true);
worksheetPart.Worksheet.Save();
status.Messages.Add("Record [" + identifier + "] has been saved successfully.");
}
catch (Exception ex)
{
status.Level = StatusLevel.Error;
string message = String.Format(
"Error while posting dataObject [{0}]. {1}",
dataObject.GetPropertyValue("Tag"),
ex.ToString()
);
status.Messages.Add(message);
}
response.Append(status);
}
return(response);
}
catch (Exception ex)
{
_logger.Error("Error in LoadDataObjects: " + ex);
throw new Exception("Error while loading data objects of type [" + objectType + "].", ex);
}
finally
{
_provider.Dispose();
}
}
private IList <IDataObject> LoadDataObjects(string objectType, DataFilter filter)
{
try
{
bool addDataObject = true, containFilterProperty = false;
IList <IDataObject> dataObjects = new List <IDataObject>();
int index = 0;
SpreadsheetTable cfTable = _provider.GetConfigurationTable(objectType);
SpreadsheetReference tableReference = cfTable.GetReference();
WorksheetPart worksheetPart = _provider.GetWorksheetPart(tableReference.SheetName);
IEnumerable <Row> rows = worksheetPart.Worksheet.Descendants <Row>().Where(r => r.RowIndex > tableReference.StartRow && r.RowIndex <= tableReference.EndRow);
foreach (Row row in rows)
{
index = 0;
addDataObject = true;
containFilterProperty = false;
IDataObject dataObject = new GenericDataObject
{
ObjectType = objectType,
};
foreach (Cell col in row.ChildElements)
{
index++;
string columnIdx = SpreadsheetReference.GetColumnName(col.CellReference);
SpreadsheetColumn column = cfTable.Columns.First <SpreadsheetColumn>(c => columnIdx.Equals(c.ColumnIdx));
if (column != null)
{
if (index == 1)
{
if (_provider.GetValue(col) == null)
{
addDataObject = false;
break;
}
}
if (filter != null)
{
foreach (Expression expression in filter.Expressions)
{
if (expression.PropertyName.ToLower().Equals(column.Name.ToLower()))
{
containFilterProperty = true;
if (_provider.GetValue(col) == null)
{
addDataObject = false;
break;
}
}
}
if (filter.Expressions.Count == 0)
{
addDataObject = true;
}
}
if (addDataObject)
{
dataObject.SetPropertyValue(column.Name, _provider.GetValue(col));
}
}
}
if (!containFilterProperty && filter != null)
{
if (filter.Expressions.Count > 0)
{
addDataObject = false;
}
}
if (addDataObject)
{
dataObjects.Add(dataObject);
}
//foreach (var col in cfTable.Columns)
//{
// if (!((GenericDataObject)dataObject).Dictionary.ContainsKey(col.Name))
// dataObject.SetPropertyValue(col.Name, null);
//}
}
return(dataObjects);
}
catch (Exception ex)
{
_logger.Error("Error in LoadDataObjects: " + ex);
throw new Exception("Error while loading data objects of type [" + objectType + "].", ex);
}
finally
{
// _provider.Dispose();
}
}
public override Response Delete(string objectType, IList <string> identifiers)
{
Response response = new Response();
try
{
if (identifiers == null || identifiers.Count == 0)
{
Status status = new Status();
status.Level = StatusLevel.Warning;
status.Messages.Add("Nothing to delete.");
response.Append(status);
return(response);
}
SpreadsheetTable cftable = _provider.GetConfigurationTable(objectType);
SpreadsheetReference tableReference = cftable.GetReference();
WorksheetPart worksheetPart = _provider.GetWorksheetPart(tableReference.SheetName);
SpreadsheetColumn column = cftable.Columns.First <SpreadsheetColumn>(c => cftable.Identifier.Equals(c.Name));
IEnumerable <Row> rows = worksheetPart.Worksheet.Descendants <Row>();
foreach (string identifier in identifiers)
{
Status status = new Status();
status.Identifier = identifier;
try
{
foreach (Row row in rows)
{
Cell cell = row.Descendants <Cell>().First(c => SpreadsheetReference.GetColumnName(c.CellReference).Equals(column.ColumnIdx));
if (_provider.GetValue(cell).Equals(identifier))
{
row.Remove();
string message = String.Format(
"DataObject [{0}] deleted successfully.",
identifier
);
status.Messages.Add(message);
}
}
}
catch (Exception ex)
{
_logger.Error("Error in Delete: " + ex);
status.Level = StatusLevel.Error;
string message = String.Format(
"Error while deleting dataObject [{0}]. {1}",
identifier,
ex
);
status.Messages.Add(message);
}
response.Append(status);
rows = worksheetPart.Worksheet.Descendants <Row>().OrderBy(r => r.RowIndex.Value);
uint i = 1;
foreach (Row row in rows)
{
row.RowIndex.Value = i++;
}
tableReference.EndRow = --i;
worksheetPart.Worksheet.SheetDimension.Reference = tableReference.GetReference(false);
cftable.Reference = tableReference.GetReference(true);
worksheetPart.Worksheet.Save();
}
}
finally
{
_provider.Dispose();
}
return(response);
}