public void SaveLoad_Data_AreEqual()
{
var file = new FileInfo("SaveLoad_Data_AreEqual.xml");
var workbook = new XWorkbook()
{
ExcelWorkbook = new XExcelWorkbook()
{
},
Worksheets = new List<XWorksheet>()
{
new XWorksheet()
{
Name = "Tab1",
Table = new XTable()
{
},
},
},
};
workbook.Save(file);
var act = XWorkbook.Load(file);
}
/// <summary>
/// Adiciona uma nova worksheet a uma workbook existente
/// </summary>
/// <param name="xWorkbook">workbook</param>
/// <param name="xWorksheet">nova worksheet</param>
private void updateWorkbook(XWorkbook xWorkbook, XWorksheet xWorksheet)
{
List <XWorksheet> xWorksheets = xWorkbook.getWorksheets();
xWorksheets.Add(xWorksheet);
xWorkbook.setWorksheets(xWorksheets);
}
private XWorkbook createXFormulas(string expressionText, XWorkbook xWorkbook)
{
PSWOclCompiler oclCompiler = new PSWOclCompiler(environment, tracker);
List <object> nodes = oclCompiler.compileOclStream(expressionText, "",
new StreamWriter(Console.OpenStandardOutput()), typeof(CSTContextDeclarationCS));
CSTOperationContextCS operationContextCS = ((CSTOperationContextCS)nodes[0]);
var constraints = operationContextCS.getConstraintsNodesCS();
CSTOperationConstraintCS operationConstraint = (CSTOperationConstraintCS)constraints[0];
ExpressionInOcl expressionInOcl = operationConstraint.getExpressionInOCL();
OclExpressionImpl bodyExpression = (OclExpressionImpl)expressionInOcl.getBodyExpression();
XFormulaCreatorVisitor visitor = new XFormulaCreatorVisitor();
bodyExpression.accept(visitor);
string formula = visitor.getFormula();
CoreClassifier classifier = (CoreClassifier)expressionInOcl.getContextualElement();
XDataTable targetTable = getTargetTable(xWorkbook, classifier);
var operation = operationContextCS.getOperationNodeCS();
var name = operation.getOperationName();
XDataTableColumn targetColumn =
targetTable.getDataTableColumns().FirstOrDefault(c => c.getName().Equals(name));
if (targetColumn == null)
{
throw new Exception("Coluna não encontrada!");
}
XTextExp xtext = new XTextExp();
xtext.setTextSymbol(formula);
targetColumn.setDataContent(xtext);
MessageBox.Show(formula);
var extraColumns = visitor.getExtraColumns();
foreach (KeyValuePair <string, string> pair in extraColumns)
{
string columnName = pair.Key;
string columnFormula = pair.Value;
targetTable = getTargetTable(xWorkbook, visitor.getCurrentClassifier());
var newTableColumn = new XDataTableColumn();
newTableColumn.setName(columnName);
newTableColumn.setDataTable(targetTable);
updateDataTable(targetTable, newTableColumn);
XTextExp xColumnFormula = new XTextExp();
xColumnFormula.setTextSymbol(columnFormula);
newTableColumn.setDataContent(xColumnFormula);
}
return(xWorkbook);
}
public string UploadWorkbook(XWorkbook workbook)
{
if (s_savedBooks.Count > 10)
{
throw new InvalidOperationException("OVERLOADED");
}
var guid = Guid.NewGuid().ToString();
s_savedBooks.TryAdd(guid, workbook);
return(guid);
}
private XWorksheet GetXReference(XWorkbook xWorkbook, string xreference, ref XDataTable targettable)
{
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
targettable = xWorksheet.getDataTables().FirstOrDefault(d => d.getName().Equals(xreference));
if (targettable != null)
{
return(xWorksheet);
}
}
targettable = null;
return(null);
}
public static byte[] CreateExcel(XWorkbook data)
{
using (var p = new ExcelPackage())
{
foreach (var srcSheet in data.Sheets)
{
var excelSheet = p.Workbook.Worksheets.Add(string.IsNullOrWhiteSpace(srcSheet.Data)
? $"Worksheet{p.Workbook.Worksheets.Count + 1}"
: srcSheet.Data);
CreateData(srcSheet, excelSheet);
}
return(p.GetAsByteArray());
}
}
/// <summary>
/// Evento de click
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void generateClick(object sender, RoutedEventArgs e)
{
const string filePath = @"C:/spreadsheet/Teste.xlsx";
if (File.Exists(filePath))
{
File.Delete(filePath);
}
Excel.Application eApp = new Excel.Application();
try
{
XWorkbook xWorkbook = createXWorkbook(TextBoxPath.Text);
string line;
StreamReader file = new StreamReader(TextBoxExpPath.Text);
while ((line = file.ReadLine()) != null)
{
createXFormulas(line, xWorkbook);
}
file.Close();
Excel.Workbook eBook = createWorkbook(eApp, xWorkbook);
eBook = createValidation(eBook, xWorkbook);
eBook = createFormulas(eBook, xWorkbook);
eBook.SaveAs("Teste");
eBook.Close();
Marshal.ReleaseComObject(eBook);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
eApp.Quit();
Marshal.ReleaseComObject(eApp);
}
}
/// <summary>
/// Cria as fórmulas do workbook passado como parâmetro
/// </summary>
/// <param name="eBook"></param>
/// <param name="xWorkbook"></param>
private Excel.Workbook createFormulas(Excel.Workbook eBook, XWorkbook xWorkbook)
{
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
Excel.Worksheet eWorksheet = eBook.Sheets[xWorksheet.getName()];
foreach (XDataTable xDataTable in xWorksheet.getDataTables())
{
Excel.ListObjects eListObjects = eWorksheet.ListObjects;
Excel.ListObject eListObject = eListObjects[xDataTable.getName()];
foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
{
Excel.ListColumns eListColumns = eListObject.ListColumns;
Excel.ListColumn eListColumn = eListColumns[sheetColumn.getName()];
if (sheetColumn.getDataContent() is XTextExp)
{
Excel.Range rng = eListColumn.DataBodyRange;
XTextExp formula = (XTextExp)sheetColumn.getDataContent();
rng.Formula = string.Format("{0}", formula.getTextSymbol());
Marshal.ReleaseComObject(rng);
}
Marshal.ReleaseComObject(eListColumn);
Marshal.ReleaseComObject(eListColumns);
}
Marshal.ReleaseComObject(eListObject);
Marshal.ReleaseComObject(eListObjects);
}
Marshal.ReleaseComObject(eWorksheet);
}
return(eBook);
}
/// <summary>
/// Retorna a representação de uma planilha em memória que satisfaz o modelo passado como parâmetro
/// </summary>
/// <param name="classDiagramPath">caminho para o arquivo .classdiagram que contém o modelo</param>
/// <returns>represetação da planilha em memória</returns>
public XWorkbook createXWorkbook(string classDiagramPath)
{
ModelReader reader = new VscdReader(classDiagramPath);
CoreModelImpl model = (CoreModelImpl)reader.getModel();
OclTypesFactory.setEnvironment(model);
XWorkbook xWorkbook = new XWorkbook();
xWorkbook.setName(model.getName());
environment = model.getEnvironmentWithoutParents();
IEnumerable <CoreClassifier> modelClasses = getClasses(model);
foreach (CoreClassifier coreClassifier in modelClasses)
{
XWorksheet xWorksheet = new XWorksheet();
xWorksheet.setName(coreClassifier.getName());
xWorksheet.setWorkbook(xWorkbook);
updateWorkbook(xWorkbook, xWorksheet);
XDataTable xDataTable = new XDataTable();
xDataTable.setName(coreClassifier.getName());
xDataTable.setWorksheet(xWorksheet);
updateWorksheet(xWorksheet, xDataTable);
createFeatureColumns(coreClassifier, xDataTable, model);
createAssociationColumn(model, coreClassifier, xDataTable);
}
IEnumerable <CoreClassifier> enumerations = getEnumerations(model);
var iEnumerable = enumerations as IList <CoreClassifier> ?? enumerations.ToList();
if (enumerations != null && iEnumerable.Any())
{
foreach (CoreClassifier enumeration in iEnumerable)
{
XWorksheet xWorksheet = new XWorksheet();
xWorksheet.setName(enumeration.getName());
xWorksheet.setWorkbook(xWorkbook);
updateWorkbook(xWorkbook, xWorksheet);
XDataTable xDataTable = new XDataTable();
xDataTable.setName(enumeration.getName());
xDataTable.setWorksheet(xWorksheet);
updateWorksheet(xWorksheet, xDataTable);
XDataTableColumn xDataTableColumn = new XDataTableColumn();
xDataTableColumn.setName(enumeration.getName());
xDataTableColumn.setDataTable(xDataTable);
updateDataTable(xDataTable, xDataTableColumn);
XDataArray dataArray = new XDataArray();
var arraySymbol =
(from CoreAttributeImpl attribute in
enumeration.getClassifierFeatures().Where(f => f is CoreAttributeImpl)
select attribute.getName()).ToList();
dataArray.setArray(arraySymbol);
xDataTableColumn.setDataContent(dataArray);
}
}
return(xWorkbook);
}
/// <summary>
/// Cria validações de dados nas colunas de referência a tabelas
/// </summary>
/// <param name="eBook">workbook correspondente do excel (COM)</param>
/// <param name="xWorkbook">xworkbook</param>
private Excel.Workbook createValidation(Excel.Workbook eBook, XWorkbook xWorkbook)
{
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
Excel.Worksheet eWorksheet = eBook.Sheets[xWorksheet.getName()];
foreach (XDataTable xDataTable in xWorksheet.getDataTables())
{
Excel.ListObjects eListObjects = eWorksheet.ListObjects;
Excel.ListObject eListObject = eListObjects[xDataTable.getName()];
foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
{
Excel.ListColumns eListColumns = eListObject.ListColumns;
Excel.ListColumn eListColumn = eListColumns[sheetColumn.getName()];
string xreference = sheetColumn.getXReference();
if (xreference != null)
{
var targettable = new XDataTable();
var targetsheet = GetXReference(xWorkbook, xreference, ref targettable);
if (targettable != null)
{
var index = targettable.getKeyIndex();
eWorksheet = eBook.Sheets[targetsheet.getName()];
eListObjects = eWorksheet.ListObjects;
eListObject = eListObjects[targettable.getName()];
eListColumns = eListObject.ListColumns;
eListColumn = eListColumns[index];
string rangename = targettable.getName() + index.ToString(CultureInfo.InvariantCulture);
eBook.Names.Add(rangename, eListColumn.DataBodyRange);
Excel.Name targetName = eBook.Names.Item(rangename, Type.Missing, Type.Missing);
string nameLocal = "=" + targetName.NameLocal;
eWorksheet = eBook.Sheets[xWorksheet.getName()];
eListObjects = eWorksheet.ListObjects;
eListObject = eListObjects[xDataTable.getName()];
eListColumns = eListObject.ListColumns;
eListColumn = eListColumns[sheetColumn.getName()];
eListColumn.DataBodyRange.Validation.Add(Excel.XlDVType.xlValidateList,
Excel.XlDVAlertStyle.xlValidAlertStop, Missing.Value,
nameLocal, Missing.Value);
}
Marshal.ReleaseComObject(eListColumn);
}
Marshal.ReleaseComObject(eListColumn);
Marshal.ReleaseComObject(eListColumns);
}
Marshal.ReleaseComObject(eListObject);
Marshal.ReleaseComObject(eListObjects);
}
Marshal.ReleaseComObject(eWorksheet);
}
return(eBook);
}
/// <summary>
/// Cria a workbook e todos os seus objetos no formato do Excel (COM)
/// </summary>
/// <param name="eApp"></param>
/// <param name="xWorkbook"></param>
private Excel.Workbook createWorkbook(Excel.Application eApp, XWorkbook xWorkbook)
{
Excel.Workbook eBook = eApp.Workbooks.Add();
eBook.Title = xWorkbook.getName();
PrepareWorkbook(eBook);
int numWorksheets = 1;
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
Excel.Worksheet eWorksheet = numWorksheets == 1
? (Excel.Worksheet)eBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value)
: (Excel.Worksheet)eBook.Worksheets.Add(Missing.Value, eBook.Worksheets[numWorksheets - 1], Missing.Value, Missing.Value);
eWorksheet.Name = xWorksheet.getName();
int i = 1;
foreach (XDataTable xDataTable in xWorksheet.getDataTables())
{
Excel.Range title = eWorksheet.Cells[i, 1];
title.Value = xDataTable.getName();
Excel.Font titleFont = title.Font;
titleFont.Bold = true;
titleFont.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
Marshal.ReleaseComObject(titleFont);
Marshal.ReleaseComObject(title);
Excel.ListObjects eListObjects = eWorksheet.ListObjects;
Excel.Range bBegin = (Excel.Range)eWorksheet.Cells[++i, 1];
Excel.ListObject eListObject = eListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, bBegin, Missing.Value, Excel.XlYesNoGuess.xlNo, Missing.Value);
eListObject.Name = xDataTable.getName() ?? "";
eListObject.ShowTotals = false;
eListObject.ListRows.Add();
eListObject.ListRows.Add();
int columns = 1;
foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
{
Excel.ListColumns eListColumns = eListObject.ListColumns;
Excel.ListColumn eListColumn = columns == 1 ? eListColumns[1] : eListColumns.Add();
eListColumn.Name = sheetColumn.getName();
eListColumn.TotalsCalculation = Excel.XlTotalsCalculation.xlTotalsCalculationSum;
eListColumn.Range.EntireColumn.ColumnWidth = 14;
if (xDataTable.getDataTableColumns().Count == 1 &&
xDataTable.getDataTableColumns()[0].getDataContent() != null)
{
var datacontent = xDataTable.getDataTableColumns()[0].getDataContent();
if (datacontent is XDataArray)
{
var datacontentimpl = (XDataArray)datacontent;
var arraysymbol = datacontentimpl.getArray();
int numSymbols = 0;
foreach (var symbol in arraysymbol)
{
if (eListColumn.DataBodyRange.Count < numSymbols)
{
eListObject.ListRows.Add();
}
eListColumn.DataBodyRange[numSymbols + 1] = symbol;
numSymbols++;
}
}
}
columns++;
Marshal.ReleaseComObject(eListColumn);
Marshal.ReleaseComObject(eListColumns);
}
Marshal.ReleaseComObject(eListObject);
Marshal.ReleaseComObject(bBegin);
Marshal.ReleaseComObject(eListObjects);
}
numWorksheets++;
Marshal.ReleaseComObject(eWorksheet);
}
return(eBook);
}
/// <summary>
/// Retorna a tabela correspondente a determinado classifier
/// </summary>
/// <param name="xWorkbook">workbook onde se encontra tabela</param>
/// <param name="classifier">classifier correspondente a tabela</param>
/// <returns>Se encontrar, retorna a referência para a tabela, caso contrário, retorna null</returns>
private XDataTable getTargetTable(XWorkbook xWorkbook, CoreClassifier classifier)
{
return(xWorkbook.getWorksheets()
.SelectMany(xWorksheet => xWorksheet.getDataTables())
.FirstOrDefault(xDataTable => xDataTable.getName().Equals(classifier.getName())));
}
