private HWObjectInstance[] ImportObjects() { var list = new List <HWObjectInstance>(); foreach (var obj in XmlData.Descendants("Object")) { var editorName = obj.Attribute("EditorName")?.Value; if (editorName == null) { continue; } var objectId = obj.DescendantNodes().OfType <XText>().Last().Value.Trim(); var position = ConvertVector(DeserializeVector3(obj.Attribute("Position").Value)); var forward = ConvertVector(DeserializeVector3(obj.Attribute("Forward").Value)); var right = ConvertVector(DeserializeVector3(obj.Attribute("Right").Value)); var hwObj = new HWObjectInstance() { Name = editorName, Definition = HWObjectDefinition.GetOrCreateFromId(Context, objectId), Matrix = Matrix4x4.CreateWorld(position, forward, Vector3.Cross(forward, right)) }; list.Add(hwObj); } return(list.ToArray()); }
private Color GetColor(string name) { return(ConvertVector3ToColor(DeserializeVector3(XmlData.Descendants(name).First().Value))); }
public string AddSheetWithTable(string TemplateSheetName, List <List <object> > TableData, Dictionary <String, String> ReplacementDict) { string result = "Error adding table data"; WSheetName = TemplateSheetName; using (SpreadsheetDocument document = SpreadsheetDocument.Open(resultfilepath + "CloudReport.xlsx", true)) { //create clone of template chart sheet as TemplateSheetName CloneSheet(document, "TemplateTableSheet", WSheetName); } try { using (SpreadsheetDocument document = SpreadsheetDocument.Open(resultfilepath + "CloudReport.xlsx", true)) { IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == WSheetName); if (sheets.Count() == 0) { return(result = "No worksheet found named as " + WSheetName); } else { WorkbookPart workbookPart = document.WorkbookPart; // int numOfRowsToAdd = TableData.Count(); Int32 currentRowNum = 3; Char currentColumn; //// inserting tabular data in excel sheet rows //foreach (List<Object> rowitem in TableData) //{ // //this is a line // currentColumn = 'B'; // foreach (var obj in rowitem) // { // //inserting values as string type // UpdateValue(workbookPart, WSheetName, currentColumn + currentRowNum.ToString(), obj.ToString(), 3, true); // // XLInsertNumberIntoCell(resultfilepath + "CloudReport.xlsx", WSheetName, currentColumn + currentRowNum.ToString(), 4); // currentColumn++; // } // currentRowNum++; //} Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where((s) => s.Name == WSheetName).FirstOrDefault(); WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id); using (XmlReader xmlr = XmlReader.Create(worksheetPart.GetStream())) { XmlData = XElement.Load(xmlr); } XElement sheetData = XmlData.Descendants(ns_s + "sheetData").Single(); //now we have a series of row, first of all build the new data List <XElement> wselements = new List <XElement>(); wselements.AddRange(sheetData.Elements().Take(TableData.Count())); //Int32 currentRowNum = numOfRowsToSkip + 1; currentRowNum = 3; foreach (List <Object> rowitem in TableData) { currentColumn = 'B'; XElement row = new XElement(ns_s + "row", new XAttribute("r", currentRowNum), new XAttribute("spans", "1:" + (rowitem.Count + 1).ToString())); foreach (var obj in rowitem) { row.Add(new XElement(ns_s + "c", new XAttribute("r", currentColumn + currentRowNum.ToString()), new XElement(ns_s + "v", new XText(obj.ToString())))); currentColumn++; } wselements.Add(row); currentRowNum++; } //Now we have all elements sheetData.Elements().Remove(); sheetData.Add(wselements); using (Stream s = worksheetPart.GetStream(FileMode.Create, FileAccess.Write)) { using (XmlWriter xmlw = XmlWriter.Create(s)) { XmlData.WriteTo(xmlw); } } } //Replace [Tag Name] by “Tag Value” in a worksheet foreach (KeyValuePair <string, string> item in ReplacementDict) { string tagname = item.Key; string tagvalue = item.Value; WorkbookPart workbookPart = document.WorkbookPart; SharedStringTablePart sharedStringsPart = workbookPart.SharedStringTablePart; IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants <DocumentFormat.OpenXml.Spreadsheet.Text>(); DoReplace(sharedStringTextElements, tagname, tagvalue); IEnumerable <WorksheetPart> worksheetParts = workbookPart.GetPartsOfType <WorksheetPart>(); foreach (var worksheet in worksheetParts) { var allTextElements = worksheet.Worksheet.Descendants <DocumentFormat.OpenXml.Spreadsheet.Text>(); DoReplace(allTextElements, tagname, tagvalue); } } } } catch (Exception ex) { result = ex.Message; } result = "Table Data inserted"; return(result); }
public string AddSheetWithChart(string TemplateSheetName, List <List <object> > ChartData, string[] SeriesLabels, Dictionary <String, String> ReplacementDict) { //Assume error string result = "Error Creating Chart"; WSheetName = TemplateSheetName; result = CopyFile(templatefilepath + "ExcelTemplate.xlsx", resultfilepath + "CloudReport1.xlsx"); try { using (SpreadsheetDocument document = SpreadsheetDocument.Open(resultfilepath + "CloudReport1.xlsx", true)) { //create clone of template chart sheet as TemplateSheetName CloneSheet(document, "TemplateChartSheet", WSheetName); } using (SpreadsheetDocument document = SpreadsheetDocument.Open(resultfilepath + "CloudReport1.xlsx", true)) { IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == WSheetName); if (sheets.Count() == 0) { return(result = "No worksheet found named as " + WSheetName); } else { WorkbookPart workbookPart = document.WorkbookPart; int numOfRowsToAdd = ChartData.Count(); //currentRowNum = 3; //Char currentColumn; // inserting Chart data in excel sheet rows //foreach (List<Object> rowitem in ChartData) //{ // //this is a line // currentColumn = 'B'; // foreach (var obj in rowitem) // { //inserted values are NOT of type string // UpdateValue(workbookPart, WSheetName, currentColumn + currentRowNum.ToString(), obj.ToString(), 3, false); // currentColumn++; // } // currentRowNum++; //} // update chart part to reflect the new data Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where((s) => s.Name == WSheetName).FirstOrDefault(); WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id); ChartPart part = worksheetPart.DrawingsPart.ChartParts.First(); XElement XmlChart; using (XmlReader xmlr = XmlReader.Create(part.GetStream())) { XmlChart = XElement.Load(xmlr); } XElement barChartData = XmlChart.Descendants(ns_c + "barChart").Single(); List <XElement> elements = new List <XElement>(); elements.AddRange(barChartData.Elements().Take(ChartData.Count())); // these 2 values are hard coded for now Char startColChar = 'B'; int currentRowNum = 3; // Char endColChar = 'B'; int colCount = 0; int m = 0; foreach (List <object> rowitem in ChartData) { Char endColChar = 'B'; colCount = rowitem.Count(); while (colCount > 1) { endColChar++; colCount--; } XElement ser = new XElement(ns_c + "ser"); ser.Add(new XElement(ns_c + "idx", new XAttribute("val", m))); ser.Add(new XElement(ns_c + "order", new XAttribute("val", m))); ser.Add(new XElement(ns_c + "tx", new XElement(ns_c + "v", new XText(SeriesLabels[m].ToString())))); m++; XElement cat = new XElement(ns_c + "cat"); XElement strRef = new XElement(ns_c + "strRef"); cat.Add(strRef); strRef.Add(new XElement(ns_c + "f", new XText("'" + WSheetName + "'!$" + "B" + "$2:$" + endColChar + "$2"))); //XElement strCache = new XElement(ns_c + "strCache"); //strRef.Add(strCache); //strCache.Add(new XElement(ns_c + "ptCount", new XAttribute("val", rowitem.Count()))); //int j = 0; //foreach (var obj in rowitem) //{ // strCache.Add(new XElement(ns_c + "pt", // new XAttribute("idx", j), // new XElement(ns_s + "v", new XText(obj.ToString())))); // j++; //} XElement val = new XElement(ns_c + "val"); XElement numRef = new XElement(ns_c + "numRef"); val.Add(numRef); numRef.Add(new XElement(ns_c + "f", new XText("'" + WSheetName + "'!$" + startColChar + "$" + currentRowNum + ":$" + endColChar + "$" + currentRowNum))); XElement numCache = new XElement(ns_c + "numCache"); numRef.Add(numCache); //numCache.Add(new XElement(ns_c + "formatCode", new XText(""$"#,##0_);\("$"#,##0\)"); numCache.Add(new XElement(ns_c + "ptCount", new XAttribute("val", rowitem.Count()))); int k = 0; foreach (var obj in rowitem) { numCache.Add(new XElement(ns_c + "pt", new XAttribute("idx", k), new XElement(ns_s + "v", new XText(obj.ToString())))); k++; } ser.Add(cat); ser.Add(val); elements.Add(ser); currentRowNum++; } //Now we have all elements barChartData.Elements().Remove(); barChartData.Add(elements); using (Stream s = part.GetStream(FileMode.Create, FileAccess.Write)) { using (XmlWriter xmlw = XmlWriter.Create(s)) { XmlChart.WriteTo(xmlw); } } result = "Chart updated"; ////save data in the sheet //currentRowNum = 3; //Char currentColumn; //// inserting Chart data in excel sheet rows //foreach (List<Object> rowitem in ChartData) //{ // //this is a line // currentColumn = 'B'; // foreach (var obj in rowitem) // { // // inserted values are NOT of type string // UpdateValue(workbookPart, WSheetName, currentColumn + currentRowNum.ToString(), obj.ToString(), 3, false); // currentColumn++; // } // currentRowNum++; //} using (XmlReader xmlr = XmlReader.Create(worksheetPart.GetStream())) { XmlData = XElement.Load(xmlr); } XElement sheetData = XmlData.Descendants(ns_s + "sheetData").Single(); //now we have a series of row, first of all build the new data List <XElement> wselements = new List <XElement>(); wselements.AddRange(sheetData.Elements().Take(ChartData.Count())); //Int32 currentRowNum = numOfRowsToSkip + 1; Char currentColumn; currentRowNum = 1; foreach (List <Object> rowitem in ChartData) { currentColumn = 'B'; XElement row = new XElement(ns_s + "row", new XAttribute(ns_s + "r", currentRowNum), new XAttribute(ns_s + "spans", "1:" + rowitem.Count)); int k = 0; foreach (var obj in rowitem) { row.Add(new XElement(ns_s + "c", new XAttribute(ns_s + "r", currentColumn + currentRowNum), new XElement(ns_s + "v", new XText(obj.ToString())))); currentColumn++; } wselements.Add(row); currentRowNum++; } //Now we have all elements sheetData.Elements().Remove(); sheetData.Add(wselements); using (Stream s = worksheetPart.GetStream(FileMode.Create, FileAccess.Write)) { using (XmlWriter xmlw = XmlWriter.Create(s)) { XmlData.WriteTo(xmlw); } } } //// Replace [Tag Name] by “Tag Value” in a worksheet foreach (KeyValuePair <string, string> item in ReplacementDict) { string tagname = item.Key; string tagvalue = item.Value; WorkbookPart workbookPart = document.WorkbookPart; SharedStringTablePart sharedStringsPart = workbookPart.SharedStringTablePart; IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants <DocumentFormat.OpenXml.Spreadsheet.Text>(); DoReplace(sharedStringTextElements, tagname, tagvalue); IEnumerable <WorksheetPart> worksheetParts = workbookPart.GetPartsOfType <WorksheetPart>(); foreach (var worksheet in worksheetParts) { var allTextElements = worksheet.Worksheet.Descendants <DocumentFormat.OpenXml.Spreadsheet.Text>(); DoReplace(allTextElements, tagname, tagvalue); } } } } catch (Exception ex) { result = ex.Message; } return(result); }