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