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