Ejemplo n.º 1
0
        /// <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());
        }