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