/// <summary> /// Create and get a spreadsheet as a serialized string based on the Matrix /// </summary> /// <param name="theMatrix"></param> /// <param name="rowLists"></param> /// <param name="lngIsoCode"></param> /// <param name="ci"></param> /// <returns></returns> internal string GetXlsx(Matrix theMatrix, List <List <XlsxValue> > rowLists, string lngIsoCode = null, CultureInfo ci = null) { ExcelDocument xl = new ExcelDocument(); xl.CreateSpreadsheet(); Specification spec = theMatrix.GetSpecFromLanguage(lngIsoCode); if (spec == null) { spec = theMatrix.MainSpec; } //First we create a contents page. This is built up piecemeal List <XlsxValue> line; List <List <XlsxValue> > matrix = new List <List <XlsxValue> >(); //some blank lines first for (int i = 0; i < 7; i++) { line = new List <XlsxValue>(); int headerId = i == 0 ? 1 : 0; for (int j = 0; j < 1; j++) { XlsxValue xval = new XlsxValue() { Value = "", StyleId = 0, DataType = CellValues.String }; line.Add(xval); } matrix.Add(line); } //Now create the actual entries in the contents page line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, CellWidth = 20, Value = Label.Get("xlsx.table", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, CellWidth = 20, Value = "", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.code", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Code, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.name", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = spec.Contents, StyleId = 0 }); matrix.Add(line); string lngCulture = Utility.GetUserAcceptLanguage(); DateTime dtLast; string dateString = ""; if (theMatrix.CreationDate.Length > 15) { dtLast = new DateTime(Convert.ToInt32(theMatrix.CreationDate.Substring(0, 4)), Convert.ToInt32(theMatrix.CreationDate.Substring(4, 2)), Convert.ToInt32(theMatrix.CreationDate.Substring(6, 2)), Convert.ToInt32(theMatrix.CreationDate.Substring(9, 2)), Convert.ToInt32(theMatrix.CreationDate.Substring(12, 2)), 0); dateString = dtLast.ToString(ci != null ? ci : CultureInfo.InvariantCulture); } else { dateString = theMatrix.CreationDate; } line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.last-updated", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = dateString, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.note", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = spec.Notes != null ? String.Join(" ", spec.Notes) : spec.NotesAsString != null ? spec.NotesAsString : "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.url", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Configuration_BSO.GetCustomConfig("url.application") + "/" + Utility.GetCustomConfig("APP_COOKIELINK_RELEASE") + '/' + theMatrix.Release.RlsCode.ToString(), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.product", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = " ", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.code", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.PrcCode, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.name", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.PrcValue, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.contacts", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = " ", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.name", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.GrpContactName, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.email", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.GrpContactEmail, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.phone", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.GrpContactPhone, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.copyright", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = " ", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.code", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Copyright.CprCode, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.name", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Copyright.CprValue, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.url", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Copyright.CprUrl, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.properties", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = " ", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.official-statistics", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.IsOfficialStatistic ? Label.Get("xlsx.yes", lngIsoCode) : Label.Get("xlsx.no", lngIsoCode), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.exceptional", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.RlsExceptionalFlag ? Label.Get("xlsx.yes", lngIsoCode) : Label.Get("xlsx.no", lngIsoCode), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.archived", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.RlsArchiveFlag ? Label.Get("xlsx.yes", lngIsoCode) : Label.Get("xlsx.no", lngIsoCode), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.analytical", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.RlsAnalyticalFlag ? Label.Get("xlsx.yes", lngIsoCode) : Label.Get("xlsx.no", lngIsoCode), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.dependency", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = theMatrix.Release.RlsDependencyFlag ? Label.Get("xlsx.yes", lngIsoCode) : Label.Get("xlsx.no", lngIsoCode), StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = "", StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.language", lngIsoCode), StyleId = 8 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = " ", StyleId = 8 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.iso-code", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = spec.Language, StyleId = 0 }); matrix.Add(line); line = new List <XlsxValue>(); line.Add(new XlsxValue() { DataType = CellValues.String, Value = Label.Get("xlsx.iso-name", lngIsoCode), StyleId = 1 }); line.Add(new XlsxValue() { DataType = CellValues.String, Value = new Language_BSO().Read(spec.Language).LngIsoName, StyleId = 0 }); matrix.Add(line); //We've created the 2D object to represent the contents, so now we can create a worksheet from that: xl.InsertDataWorksheet(matrix, Label.Get("About", lngIsoCode), OrientationValues.Landscape, true); //On the worksheet we've just created, add an image to the top left hand corner xl.AddImage(Configuration_BSO.GetCustomConfig("url.logo"), Label.Get("About", lngIsoCode), 1, 1); //Create a second worksheet based on the Matrix contents xl.InsertDataWorksheet(theMatrix.GetMatrixSheet(null, false, 1), theMatrix.Code, OrientationValues.Landscape, true); //cleanup xl.Close(); //Test option...get a local version of the xlsx file //xl.SaveToFile(@"C:\nok\Schemas\" + theMatrix.Code + ".xlsx"); //return the serialized version of the spreadsheet return(xl.SerializeSpreadsheetFromByteArrayBase64()); }