Пример #1
0
        PivotData getSamplePivotData(bool diag = false)
        {
            var vals = new List <object>();
            var keys = new List <uint[]>();

            for (uint a = 0; a < 3; a++)
            {
                for (uint b = (diag?a:0); b < 3; b++)
                {
                    for (uint c = (diag?b:0); c < 3; c++)
                    {
                        keys.Add(new uint[] { a, b + 3, c + 6 });
                        vals.Add(2);
                    }
                }
            }

            var pvtState = new PivotDataState()
            {
                DimCount  = 3,
                KeyValues = new object[] { "A1", "A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3" },
                Values    = vals.ToArray(),
                ValueKeys = keys.ToArray()
            };
            var pvt = new PivotData(new string[] { "A", "B", "C" }, new CountAggregatorFactory(), true);

            pvt.SetState(pvtState);

            /*foreach (var v in pvt.AllValues) {
             *      Console.WriteLine(v.Key.ToString() );
             * }
             * Console.WriteLine("*******");*/
            return(pvt);
        }
Пример #2
0
        private JObject GetActivity(string rootCode, PivotData initData)
        {
            var rootModel = this.storageProvider.GetRootModel(rootCode);

            if (string.IsNullOrEmpty(rootModel?.JsonStructure))
            {
                throw new Exception($"No root model with name {rootModel}");
            }

            var tree   = ObjectHelper.GetInnerObject(JObject.Parse(rootModel.JsonStructure));
            var bpNode = tree[ParserHelper.GetPivotName(PivotType.BusinessProcess)];

            if (bpNode == null)
            {
                throw new Exception("No BP in your model");
            }

            var ac = bpNode[initData.GetJTokenName()];

            if (!(ac is JObject))
            {
                throw new Exception($"No ac with objectCode {initData.MainValue}");
            }

            var res = new JObject {
                [initData.MainValue] = ac
            };

            return(res);
        }
Пример #3
0
        public static List <string> GetJTokenPath(this PivotData pivotData)
        {
            var result = new List <string>();

            var chunks = new List <string>();

            // Do not include pivot name for Transactions
            if (pivotData.PivotDefinition.Type != PivotType.Transaction)
            {
                chunks.Add(ParserHelper.GetPivotName(pivotData.PivotDefinition.Type));
            }

            // Do not include pivot main values for Activity
            if (pivotData.PivotDefinition.Type != PivotType.Activity)
            {
                if (!string.IsNullOrEmpty(pivotData.MainValue))
                {
                    chunks.Add(pivotData.MainValue);
                }
            }

            result.Add(string.Join(".", chunks));

            // Do not include pivot secondary values for Activity
            if (pivotData.PivotDefinition.Type != PivotType.Activity)
            {
                if (pivotData.SecondaryValues.Any())
                {
                    result.Add(pivotData.SecondaryValues.First());
                }
            }

            return(result);
        }
Пример #4
0
        public void PivotTable_ES()
        {
            var pvtData = new PivotData(new[] { "District", "SubCounty", "wrd_name", "Sex", "Quarter", "HES" }, new CountAggregatorFactory());

            pvtData.ProcessData(new DataTableReader(ReturnEconomic_strengthening()));
            var pvtTbl = new PivotTable(
                new[] { "Quarter", "District", "SubCounty" }, //rows
                new[] { "HES" },                              //columns
                pvtData);

            var outputWr  = new StringWriter();
            var pvtHtmlWr = new PivotTableHtmlWriter(outputWr);

            pvtHtmlWr.SubtotalRows           = true;
            pvtHtmlWr.AllowHtml              = true;
            pvtHtmlWr.TotalsRowHeaderText    = "Grand Total";
            pvtHtmlWr.TotalsColumnHeaderText = "Sub County Totals";
            pvtHtmlWr.TableClass             = "table border = '1' table - bordered table-hover";

            pvtHtmlWr.Write(pvtTbl);

            var pvtTblHtml = outputWr.ToString();

            LitPivot.Text = pvtTblHtml;
        }
Пример #5
0
        public void QueryTest()
        {
            var pvtData = new PivotData(new[] { "a", "b", "d" },
                                        new CompositeAggregatorFactory(
                                            new CountAggregatorFactory(),
                                            new SumAggregatorFactory("d")
                                            ),
                                        false);

            pvtData.ProcessData(DataUtils.getSampleData(10000), DataUtils.getProp);

            var q           = new SliceQuery(pvtData).Dimension("a").Where("a", "val1", "val2").Measure(0);
            var pvtDataRes1 = q.Execute(false);

            Assert.True(pvtDataRes1.AggregatorFactory is CountAggregatorFactory);
            Assert.Equal(1, pvtDataRes1.Dimensions.Length);
            Assert.Equal(2, pvtDataRes1.GetDimensionKeys()[0].Length);
            Assert.Equal(((object[])pvtData["val1", Key.Empty, Key.Empty].Value)[0], pvtDataRes1["val1"].Value);

            var grandTotalCount = Convert.ToInt32(((object[])pvtData[Key.Empty, Key.Empty, Key.Empty].Value)[0]);
            var q2 = new SliceQuery(pvtData)
                     .Dimension("d")
                     .Measure(
                new SumAggregatorFactory("i"),                         // since this is derived measure field name actually may not match source data fields
                (sourceAggr) => {
                var cntAggr = sourceAggr.AsComposite().Aggregators[0];
                return(new SumAggregator("i", new object[] { cntAggr.Count, Convert.ToDecimal(cntAggr.Value) / grandTotalCount * 100 }));
            }
                );
            var pvtDataRes2 = q2.Execute(true);

            Assert.Equal(100M, pvtDataRes2[ValueKey.Empty1D].Value);
            Assert.Equal(1M, pvtDataRes2[0].Value);
        }
Пример #6
0
        public async Task ReadPivotData(string measurementName)
        {
            PivotData data = await _reader.QueryAsync(measurementName, new QueryRange(-7, RangeUnit.day));

            Console.WriteLine(data.ToJson());
            Console.WriteLine("-----------------");
        }
Пример #7
0
        public void PivotData_3D()
        {
            var pvtData = new PivotData(new string[] { "a", "year", "month" },
                                        new CountAggregatorFactory());

            pvtData.ProcessData(SampleGenerator(100000), GetRecordValue);

            Assert.Equal(50000, Convert.ToInt32(
                             pvtData[pvtData.GetDimensionKeys()[0][0], Key.Empty, Key.Empty].Value));

            Assert.Equal(100000, Convert.ToInt32(
                             pvtData[Key.Empty, pvtData.GetDimensionKeys()[1][0], Key.Empty].Value));

            Assert.Equal(30240, Convert.ToInt32(
                             pvtData[Key.Empty, Key.Empty, pvtData.GetDimensionKeys()[2][0]].Value));

            Assert.Equal(100000, Convert.ToInt32(
                             pvtData[Key.Empty, Key.Empty, Key.Empty].Value));

            // incremental processing
            pvtData.ProcessData(SampleGenerator(10000), GetRecordValue);
            Assert.Equal(55000, Convert.ToInt32(
                             pvtData[pvtData.GetDimensionKeys()[0][0], Key.Empty, Key.Empty].Value));
            Assert.Equal(40240, Convert.ToInt32(
                             pvtData[Key.Empty, Key.Empty, pvtData.GetDimensionKeys()[2][0]].Value));

            Assert.Equal(110000, Convert.ToInt32(
                             pvtData[Key.Empty, Key.Empty, Key.Empty].Value));
        }
Пример #8
0
        public void PivotData_Slice()
        {
            var testData   = generateData();
            var pvtData    = new PivotData(new string[] { "name", "date", "qty", "total" }, new CountAggregatorFactory(), testData);
            var slice1Data = pvtData.Slice(new string[] { "name", "qty" }, false);

            Assert.Equal(2, slice1Data.Dimensions.Length);

            var vk = new ValueKey("Test0", 0);

            Assert.Equal(pvtData["Test0", Key.Empty, 0, Key.Empty].Value, slice1Data[vk].Value);

            Assert.Equal(pvtData[Key.Empty, Key.Empty, Key.Empty, Key.Empty].Value, slice1Data[Key.Empty, Key.Empty].Value);

            var slice2Data = pvtData.Slice(new string[] { "name", "qty" }, false, (v) => {
                return(v.Key.DimKeys[0].Equals("Test0"));
            });

            Assert.Equal(((uint)pvtData[Key.Empty, Key.Empty, Key.Empty, Key.Empty].Value) / 2, slice2Data[Key.Empty, Key.Empty].Value);

            // check zero-dim slice
            var sliceZeroDimData = slice2Data.Slice(null, false);

            Assert.Equal(slice2Data[Key.Empty, Key.Empty].Value, sliceZeroDimData[new object[0]].Value);
        }
Пример #9
0
        public void PivotData_GetDimensionKeys()
        {
            var testData = generateData();
            var pvtData  = new PivotData(new string[] { "name", "qty", "i" }, new CountAggregatorFactory(), testData);

            var dimKeys = pvtData.GetDimensionKeys();

            Assert.Equal(3, dimKeys.Length);

            // check "i" dim keys
            Assert.Equal(1, dimKeys[2].Length);
            Assert.Equal(2, dimKeys[2][0]);

            // check "i" dim keys
            Assert.Equal(10, dimKeys[1].Length);

            var onlyOneDimKeys = pvtData.GetDimensionKeys(new [] { "i" });

            Assert.Equal(1, onlyOneDimKeys.Length);
            Assert.Equal(1, onlyOneDimKeys[0].Length);

            // check custom comparers
            var qtyDimKeys = pvtData.GetDimensionKeys(new [] { "i", "qty" },
                                                      new IComparer <object> [] {
                null,                          //use default for i
                NaturalSortKeyComparer.ReverseInstance
            });

            Assert.Equal(9, qtyDimKeys[1][0]);
        }
Пример #10
0
        protected void Page_Load(object sender, EventArgs e)
        {
            string pCode = "", pName = ""; bool defaultVal;

            defaultVal = false;
            pName      = Page.RouteData.Values["pivotname"] as string;
            pCode      = Page.RouteData.Values["pivotcode"] as string;

            if (pCode == null)
            {
                defaultVal = true;
            }
            string redirPth = "~/home.aspx";

            try
            {
                if (UserData.checkPivot(pName))
                {
                    PivotData p = PivotData.getPivotRedir(pName, pCode, defaultVal);
                    if (p.PivotType == "pl")
                    {
                        redirPth = p.PivotContent;
                    }
                    //Server.ClearError();
                }
                else
                {
                    redirPth = "~/error.aspx";
                }
            }
            catch (Exception exp)
            { redirPth = "~/error.aspx"; }
            //}
            Response.Redirect(redirPth);
        }
Пример #11
0
        public void PivotData_LazyTotals()
        {
            var nameToIdx = new Dictionary <string, int>()
            {
                { "A", 0 },
                { "B", 1 },
                { "C", 2 },
                { "D", 3 }
            };
            var data = new int[][] {
                new int[] { 1, 2, 3, 4 },
                new int[] { 1, 2, 4, 8 },
                new int[] { 2, 2, 4, 4 },
                new int[] { 3, 3, 4, 16 }
            };
            var pvt = new PivotData(
                new string[] { "A", "B", "C", "D" }, new CountAggregatorFactory(), true);

            pvt.ProcessData(data, (r, f) => {
                return(((int[])r)[nameToIdx[f]]);
            });
            Assert.Equal(3, Convert.ToInt32(pvt[Key.Empty, 2, Key.Empty, Key.Empty].Value));
            Assert.Equal(2, Convert.ToInt32(pvt[Key.Empty, 2, 4, Key.Empty].Value));
            Assert.Equal(1, Convert.ToInt32(pvt[1, Key.Empty, 4, Key.Empty].Value));
        }
Пример #12
0
        public void PivotData_Merge()
        {
            var pvtData = new PivotData(new string[] { "a", "year", "month" },
                                        new AverageAggregatorFactory("i"));

            var pvtData1 = new PivotData(new string[] { "a", "year", "month" },
                                         new AverageAggregatorFactory("i"));
            var pvtData2 = new PivotData(new string[] { "a", "year", "month" },
                                         new AverageAggregatorFactory("i"));
            var pvtData3 = new PivotData(new string[] { "a", "b" }, new CountAggregatorFactory());

            Assert.Throws <ArgumentException>(() => {
                pvtData1.Merge(pvtData3);
            });

            pvtData.ProcessData(SampleGenerator(20000), GetRecordValue);
            pvtData1.ProcessData(SampleGenerator(10000), GetRecordValue);
            pvtData2.ProcessData(SampleGenerator(10000, 10000), GetRecordValue);

            pvtData1.Merge(pvtData2);

            foreach (var v in pvtData.AllValues)
            {
                var aggr       = pvtData[v.Key];
                var aggrMerged = pvtData1[v.Key];
                Assert.Equal(aggr.Count, aggrMerged.Count);
                Assert.Equal(aggr.Value, aggrMerged.Value);
            }
            Assert.Equal(pvtData.GetDimensionKeys()[0].Length, pvtData1.GetDimensionKeys()[0].Length);
            Assert.Equal(pvtData.GetDimensionKeys()[1].Length, pvtData1.GetDimensionKeys()[1].Length);
            Assert.Equal(pvtData.GetDimensionKeys()[2].Length, pvtData1.GetDimensionKeys()[2].Length);
        }
Пример #13
0
        static void Main(string[] args)
        {
            // sample dataset
            var ordersTable = GetOrdersTable();

            // build data cube by DataTable
            var ordersPvtData = new PivotData(new[] { "product", "year" },
                                              new CompositeAggregatorFactory(
                                                  new SumAggregatorFactory("quantity"),
                                                  new SumAggregatorFactory("total"),
                                                  new AverageAggregatorFactory("total")
                                                  ));

            ordersPvtData.ProcessData(new DataTableReader(ordersTable));

            // lets calculate simple expression-based formula measure
            var dynFormula = new DynamicFormulaMeasure("sumoftotal / sumofquantity", ordersPvtData);
            var resPvtData = new SliceQuery(ordersPvtData)
                             .Measure("Weighted Total", dynFormula.GetFormulaValue, dynFormula.GetParentMeasureIndexes())
                             .Execute();

            // now resPvtData has only one measure calculated by the formula

            foreach (var k in resPvtData.GetDimensionKeys(new[] { "year" })[0])
            {
                Console.WriteLine("Weighted total for [{0}]: {1:0.##}", k, resPvtData[Key.Empty, k].Value);
            }
            Console.WriteLine("Weighted Grand Total: {0:0.##}", resPvtData[Key.Empty, Key.Empty].Value);
            Console.WriteLine("\nPress any key to continue...");
            Console.ReadKey();
        }
Пример #14
0
 private void updatePivotRotation(PivotData pd)
 {
     //vector from pivot to sun
     //finding angle to turn towards based on direction of vector on a single axis
     if (userLock)
     {
         float y = userRotation;
         if (pd.pivotTransform.lossyScale.z < 0)
         {
             y = -y;
         }
         Quaternion current = pd.pivotTransform.rotation;
         pd.pivotTransform.localRotation = pd.defaultOrientation;
         Quaternion target = pd.pivotTransform.rotation * Quaternion.Euler(0f, y, 0f);
         pd.pivotTransform.rotation = Quaternion.Lerp(current, target, TimeWarp.deltaTime * this.trackingSpeed);
     }
     else
     {
         Vector3 vector = pd.pivotTransform.InverseTransformPoint(sunTransform.position);
         float   y      = (float)SSTUUtils.toDegrees(Mathf.Atan2(vector.x, vector.z));
         if (pd.pivotTransform.lossyScale.z < 0)
         {
             y = -y;
         }
         Quaternion to = pd.pivotTransform.rotation * Quaternion.Euler(0f, y, 0f);
         pd.pivotTransform.rotation = Quaternion.Lerp(pd.pivotTransform.rotation, to, TimeWarp.deltaTime * this.trackingSpeed);
     }
 }
        protected void CheckSerializerPerf(PivotData pvtData, string serializerName, Func <PivotDataState, object> serialize, Func <object, PivotDataState> deserialize)
        {
            var sw = new Stopwatch();

            sw.Start();
            var pvtState = pvtData.GetState();

            sw.Stop();
            Console.WriteLine("PivotData GetState time: {0}", sw.Elapsed);


            sw.Restart();
            var serializedState = serialize(pvtState);

            sw.Stop();
            Console.WriteLine("PivotDataState {0} serialize time: {1}", serializerName, sw.Elapsed);

            sw.Restart();
            var pvtStateFromSerialized = deserialize(serializedState);

            sw.Stop();
            Console.WriteLine("PivotDataState {0} Deserialize time: {1}", serializerName, sw.Elapsed);

            var pvtData3 = new PivotData(new string[] { "year", "month", "a", "i" }, new CountAggregatorFactory(), true);

            sw.Restart();
            pvtData3.SetState(pvtStateFromSerialized);
            sw.Stop();
            Console.WriteLine("PivotData SetState time: {0}", sw.Elapsed);

            Assert.Equal(pvtState.KeyValues.Length, pvtStateFromSerialized.KeyValues.Length);
            Assert.Equal(pvtState.ValueKeys.Length, pvtStateFromSerialized.ValueKeys.Length);
            Assert.Equal(pvtState.Values.Length, pvtStateFromSerialized.Values.Length);
        }
Пример #16
0
        static void GroupByMultipleWithCustomAccessor(IList <Customer> list, string[] groupByColumns)
        {
            Console.WriteLine("Aggregating by {0} with custom fields accessor...", String.Join(", ", groupByColumns));

            var sw = new Stopwatch();

            // configure pivot data
            var pvtData = new PivotData(groupByColumns, new CountAggregatorFactory());

            // process the list
            sw.Start();
            pvtData.ProcessData(list, (o, colName) => {
                // returned values are totally controlled by accessor delegates
                // this is fastest way but it is applicable only for typed collections
                var customer = (Customer)o;
                switch (colName)
                {
                case "Category": return(customer.Category);

                case "Status": return(customer.Status);
                }
                return(null);
            });
            sw.Stop();

            Console.WriteLine("Results: unique groups = {0}, processing time = {1} sec", pvtData.Count, sw.Elapsed.TotalSeconds);
        }
Пример #17
0
    protected Literal PlotStackedCol_TOTAL_byBudgetNbr(DataTable dt)
    {
        List <string> rows = new List <string> {
            "ObjDesc", "caldate"
        };
        //List<string> cols = new List<string> { "budgetNbr", "budgetName", "BegDate", "EndDate", "PastProj" };
        List <string> cols = new List <string> {
            "const"
        };

        List <string> stats = new List <string> {
            "Sum"
        };


        List <string> keepCols = new List <string>();

        keepCols.AddRange(rows);
        keepCols.AddRange(cols);

        SumAggregatorFactory sumAggr = new SumAggregatorFactory("amt");

        var cube = new PivotData(keepCols.ToArray(), sumAggr);

        cube.ProcessData(new DataTableReader(dt));

        PivotTable pivotTable         = new PivotTable(rows.ToArray(), cols.ToArray(), cube);
        PivotTableDataTableWriter foo = new PivotTableDataTableWriter("foo");
        DataTable dt_totals           = foo.Write(pivotTable);

        string[] series_vals = new string[1] {
            "ObjDesc"
        };
        PivotTable pivotTable_series   = new PivotTable(series_vals, null, cube);
        PivotTableDataTableWriter foo2 = new PivotTableDataTableWriter("foo2");
        DataTable dt_series            = foo2.Write(pivotTable_series);



        //DataView dv =

        //DataTable dt_series2 = sql.DataTable_from_SQLstring("select Name from fin.vwFTE_Previous_and_Projections where budgetNbr = '" + budgetNbr + "' group by Name");
        //DataTable dtraw2 = sql.DataTable_from_SQLstring("select * from fin.vwFTE_Previous_and_Projections where budgetNbr = '" + budgetNbr + "' and calcFTE>0 order by Name");

        //string x1 = utilCharts.Highcharts_StackedColumn()

        Literal lit2 = new Literal();
        string  x2   = utilCharts.Highcharts_StackedColumn(dt_series, dt_totals, "caldate", "1[trial]", "ObjDesc", "ObjDesc", "chart1", "charttitle",
                                                           "Month", "Totals", -999, -999, -999, -999, 700, 400);

        lit2.Text += x2;

        ////panel2.Controls.Add(lit2);

        //sql.Close();

        return(lit2);
    }
Пример #18
0
        public void PivotData_2D()
        {
            var testData = generateData();

            var pvtData1 = new PivotData(new string[] { "name", "qty" }, new CountAggregatorFactory(), testData);

            Assert.Equal(2, pvtData1.GetDimensionKeys()[0].Length);
            Assert.Equal(10, pvtData1.GetDimensionKeys()[1].Length);
            foreach (var cKey in pvtData1.GetDimensionKeys()[0])
            {
                foreach (var rKey in pvtData1.GetDimensionKeys()[1])
                {
                    var v = pvtData1[cKey, rKey];
                    if (v.Count > 0)
                    {
                        Assert.Equal(100, Convert.ToInt32(v.Value));
                    }
                }
            }


            var pvtData = new PivotData(new string[] { "name", "date" }, new SumAggregatorFactory("i"), testData);

            Assert.Equal(2, pvtData.GetDimensionKeys()[0].Length);
            Assert.Equal(42, pvtData.GetDimensionKeys()[1].Length);

            var rowTest0Totals = new ValueKey("Test0", Key.Empty);

            Assert.Equal(1000M, pvtData[rowTest0Totals].Value);

            // calc test
            var calcData = new object[5][] {
                new object[] { "A", 10, 50 },
                new object[] { "A", 15, 40 },
                new object[] { "B", 20, 50 },
                new object[] { "B", 25, 60 },
                new object[] { "C", 10, 0 }
            };
            Func <object, string, object> getVal = (r, f) => {
                return(((object[])r)[Convert.ToInt32(f)]);
            };
            var countPvtData = new PivotData(new string[] { "0", "1" }, new CountAggregatorFactory());

            countPvtData.ProcessData(calcData, getVal);
            Assert.Equal(2, Convert.ToInt32(countPvtData["A", Key.Empty].Value));
            Assert.Equal(1, Convert.ToInt32(countPvtData["C", Key.Empty].Value));

            var avgPvtData = new PivotData(new string[] { "0", "1" }, new AverageAggregatorFactory("2"));

            avgPvtData.ProcessData(calcData, getVal);
            Assert.Equal(45M, avgPvtData["A", Key.Empty].Value);
            Assert.Equal(0M, avgPvtData["C", Key.Empty].Value);
            Assert.Equal(25M, avgPvtData[Key.Empty, 10].Value);
        }
Пример #19
0
        protected void Page_Load(object sender, EventArgs e)
        {
            PvtData = GetDataCube();

            if (!IsPostBack)
            {
                // bind years filter items
                var yearKeys = PvtData.GetDimensionKeys(new[] { "Order Year" })[0];
                filterYear.DataSource = yearKeys.Select(y => new ListItem(y.ToString(), y.ToString())).ToArray();
                filterYear.DataBind();
            }
        }
Пример #20
0
        public void PivotTable_LargeWithTotals()
        {
            // test for UlongCache impl
            var pvtData = new PivotData(Repeat(63, i => "a" + i.ToString()), new CountAggregatorFactory());

            pvtData.ProcessData(new int[] { 0, 1, 2, 3, 4, }, (row, field) => {
                var fldIdx = Int32.Parse(field.Substring(1));
                var rowIdx = (int)row;
                if (fldIdx < rowIdx)
                {
                    return(0);
                }
                return(rowIdx);
            });
            Check(new PivotTable(pvtData.Dimensions, null, pvtData));
            Check(new PivotTable(Repeat(62, i => "a" + i.ToString()), new string[] { "a62" }, pvtData));


            // test for BitArrayCache impl
            var pvtData2 = new PivotData(Repeat(128, i => "a" + i.ToString()), new CountAggregatorFactory());

            pvtData2.ProcessData(new int[] { 0, 1, 2, 3, 4, }, (row, field) => {
                var fldIdx = Int32.Parse(field.Substring(1));
                var rowIdx = (int)row;
                if (fldIdx < rowIdx)
                {
                    return(0);
                }
                return(rowIdx);
            });
            Check(new PivotTable(pvtData2.Dimensions, null, pvtData2));
            Check(new PivotTable(Repeat(126, i => "a" + i.ToString()), new string[] { "a126", "a127" }, pvtData2));

            void Check(IPivotTable pvtTbl)
            {
                Assert.Equal(3,
                             Convert.ToInt32(pvtTbl.GetValue(
                                                 new ValueKey(Repeat <object>(pvtTbl.Rows.Length, i => Key.Empty, new object[] { 0, 0, 0 })),
                                                 new ValueKey(Repeat <object>(pvtTbl.Columns.Length, i => Key.Empty))
                                                 ).Value));
                Assert.Equal(4,
                             Convert.ToInt32(pvtTbl.GetValue(
                                                 new ValueKey(Repeat <object>(pvtTbl.Rows.Length, i => Key.Empty, new object[] { 0, 0 })),
                                                 new ValueKey(Repeat <object>(pvtTbl.Columns.Length, i => Key.Empty))
                                                 ).Value));
                Assert.Equal(5,
                             Convert.ToInt32(pvtTbl.GetValue(
                                                 new ValueKey(Repeat <object>(pvtTbl.Rows.Length, i => Key.Empty)),
                                                 new ValueKey(Repeat <object>(pvtTbl.Columns.Length, i => Key.Empty))
                                                 ).Value));
            }

            T[] Repeat <T>(int n, Func <int, T> getVal, params T[] explicitVals)
Пример #21
0
        public static string GetJTokenName(this PivotData pivotData, bool escape = true)
        {
            var result = string.Empty;

            var tokens = pivotData.GetJTokenPath().Take(1).ToList();

            tokens = escape ? tokens.EscapeJTokenNames() : tokens;

            result = string.Join(".", tokens);

            return(result);
        }
Пример #22
0
        public static QueryNode SetPivot(this QueryNode queryNode, PivotType type, string mainValue,
                                         params string[] secondaryValues)
        {
            var pivotData = new PivotData
            {
                MainValue       = mainValue,
                SecondaryValues = secondaryValues?.ToList() ?? new List <string>(),
                PivotDefinition = ParserHelper.GetPivot(type)
            };

            queryNode.PivotData = pivotData;

            return(queryNode);
        }
        public void PivotDataState_SerializationPerf()
        {
            var pvtData = new PivotData(
                new string[] { "year", "month", "a", "i" },
                new CountAggregatorFactory(), true);

            pvtData.ProcessData(
                PivotDataTests.SampleGenerator(1000000),                 // 1 mln of unique aggregates
                PivotDataTests.GetRecordValue);

            /*var js = new JavaScriptSerializer();
             * js.MaxJsonLength = Int32.MaxValue;
             * CheckSerializerPerf(pvtData, "JSON",
             *      (state) => {
             *              var jsonStr = js.Serialize(state);
             *              Console.WriteLine("JSON length: {0}", jsonStr.Length);
             *              return jsonStr;
             *      },
             *      (state) => {
             *              return js.Deserialize<PivotDataState>( (string)state);
             *      });*/

            var binFmt = new BinaryFormatter();

            /*CheckSerializerPerf(pvtData, "BinaryFormatter",
             *      (state) => {
             *              var memStream = new MemoryStream();
             *              binFmt.Serialize(memStream, state);
             *              Console.WriteLine("Serialized bytes: {0}",memStream.Length);
             *              memStream.Position = 0;
             *              return memStream;
             *      },
             *      (state) => {
             *              return (PivotDataState)binFmt.Deserialize( (Stream)state );
             *      });*/


            CheckSerializerPerf(pvtData, "PivotDataState",
                                (state) => {
                var memStream = new MemoryStream();
                state.Serialize(memStream);
                var bytes = memStream.ToArray();
                Console.WriteLine("Serialized bytes: {0}", bytes.Length);
                return(bytes);
            },
                                (state) => {
                return(PivotDataState.Deserialize(new MemoryStream((byte[])state)));
            });
        }
Пример #24
0
        public void FormulaTest()
        {
            // simple test for 1-measure
            var pvtData1 = new PivotData(new[] { "a", "d" },
                                         new SumAggregatorFactory("b"),
                                         false);

            pvtData1.ProcessData(DataUtils.getSampleData(1000), DataUtils.getProp);
            var q1 = new SliceQuery(pvtData1).Dimension("a").Measure("a*2", (paramAggrs) => {
                return(Convert.ToDecimal(paramAggrs[0].Value) * 2);
            }, new[] { 0 });
            var pvt1Res = q1.Execute();

            Assert.Equal(
                ((decimal)pvtData1["val1", Key.Empty].Value) * 2,
                pvt1Res["val1"].Value);

            // test for composite aggregator
            var pvtData = new PivotData(new[] { "a", "d" },
                                        new CompositeAggregatorFactory(
                                            new CountAggregatorFactory(),
                                            new SumAggregatorFactory("b")
                                            ),
                                        false);

            pvtData.ProcessData(DataUtils.getSampleData(1000), DataUtils.getProp);

            var q = new SliceQuery(pvtData);

            q.Dimension("a");
            q.Measure(1);
            q.Measure("a*2", (paramAggrs) => {
                return(Convert.ToDecimal(paramAggrs[0].Value) * 2);
            }, new[] { 1 });

            var pvtRes = q.Execute();

            Assert.Equal(
                ((decimal)pvtData["val1", Key.Empty].AsComposite().Aggregators[1].Value) * 2,
                pvtRes["val1"].AsComposite().Aggregators[1].Value);

            // check that calculated measures are merged correctly
            pvtRes.ProcessData(DataUtils.getSampleData(10), DataUtils.getProp);

            Assert.Equal(
                ((decimal)pvtRes["val1"].AsComposite().Aggregators[0].Value) * 2,
                pvtRes["val1"].AsComposite().Aggregators[1].Value);
        }
Пример #25
0
        static void GroupByMultipleWithObjectMember(IList <Customer> list, string[] groupByColumns)
        {
            Console.WriteLine("Aggregating by {0} with help of ObjectMember...", String.Join(", ", groupByColumns));

            var sw = new Stopwatch();

            // configure pivot data
            var pvtData = new PivotData(groupByColumns, new CountAggregatorFactory());

            // process the list
            sw.Start();
            pvtData.ProcessData(list, new ObjectMember().GetValue);
            sw.Stop();

            Console.WriteLine("Results: unique groups = {0}, processing time = {1} sec", pvtData.Count, sw.Elapsed.TotalSeconds);
        }
        private PivotData GetPivotData(string valX, string valY)
        {
            IEnumerable <PivotData> flt = SummaryOfData.Where(d => d.ValueX == valX && d.ValueY == valY);

            if (flt.Count() > 0)
            {
                return(flt.ElementAt(0));
            }
            PivotData pd = new PivotData()
            {
                ValueX = valX, ValueY = valY
            };

            SummaryOfData.Add(pd);
            return(pd);
        }
Пример #27
0
        public void PivotTable_SortPerformance()
        {
            var pvtData = new PivotData(new[] { "a", "b" },
                                        new CompositeAggregatorFactory(
                                            new CountAggregatorFactory(),
                                            new SumAggregatorFactory("d")
                                            ),
                                        true);

            pvtData.ProcessData(DataUtils.getSampleData(50000), DataUtils.getProp);

            var pvtTbl = new PivotTable(new[] { "b" }, new [] { "a" }, pvtData);

            pvtTbl.SortRowKeys(0, ListSortDirection.Descending);

            pvtTbl.SortRowKeys(null, ListSortDirection.Descending);
        }
Пример #28
0
        public void PivotData_GetState()
        {
            var testData = generateData();
            var pvtData  = new PivotData(new string[] { "name", "qty" }, new CountAggregatorFactory(), testData, false);
            var pvtState = pvtData.GetState();

            var pvtData2 = new PivotData(new string[] { "name", "qty" }, new CountAggregatorFactory(), false);

            pvtData2.SetState(pvtState);

            Action <PivotData, PivotData> check = (pvt1, pvt2) => {
                Assert.Equal(pvt1.AllValues.Count, pvt2.AllValues.Count);
                Assert.Equal(pvt1[ValueKey.Empty2D].Value, pvt2[ValueKey.Empty2D].Value);

                foreach (var vk in pvt1.AllValues)
                {
                    Assert.Equal(pvt1[vk.Key].Value, pvt2[vk.Key].Value);
                    Assert.Equal(pvt1[vk.Key].Count, pvt2[vk.Key].Count);
                }
            };

            check(pvtData, pvtData2);

            var jsonState = JsonConvert.SerializeObject(pvtState);
            //Output.WriteLine(jsonState);

            var pvtStateFromJson = JsonConvert.DeserializeObject <PivotDataState>(jsonState);
            var pvtData3         = new PivotData(new string[] { "name", "qty" }, new CountAggregatorFactory(), false);

            pvtData3.SetState(pvtStateFromJson);

            check(pvtData, pvtData3);

            // internal serialization
            var memStream = new MemoryStream();

            pvtState.Serialize(memStream);

            memStream = new MemoryStream(memStream.ToArray());
            var pvtStateFromSerialized = PivotDataState.Deserialize(memStream);
            var pvtData4 = new PivotData(new string[] { "name", "qty" }, new CountAggregatorFactory(), false);

            pvtData4.SetState(pvtStateFromSerialized);

            check(pvtData, pvtData4);
        }
Пример #29
0
        public void WhereTest()
        {
            var pvtData = new PivotData(new[] { "a", "d" }, new CountAggregatorFactory());

            pvtData.ProcessData(DataUtils.getSampleData(10000), DataUtils.getProp);

            // empty filter = nothing is changed
            Assert.Equal(pvtData.Count, new SliceQuery(pvtData).Where("a").Execute().Count);

            // 1/3 of 10k
            Assert.Equal(3334, Convert.ToInt32(
                             new SliceQuery(pvtData).Where("a", "val1").Dimension("a").Execute()[Key.Empty].Value));

            // 2/3 of 10k
            Assert.Equal(6667, Convert.ToInt32(
                             new SliceQuery(pvtData).Where("a", "val1", "val2").Dimension("a").Execute()[Key.Empty].Value));
        }
Пример #30
0
        public static PivotData GetPivotData(string nodeName)
        {
            var pivotName  = nodeName.Split('.').First();
            var pivotValue = nodeName.Split('.').Last();
            var pivotType  = ParserHelper.GetPivotTypeByName(pivotName);

//            if (!pivotType.HasValue)
//                return null;

            var pivotData = new PivotData
            {
                PivotDefinition = ParserHelper.GetPivot(pivotType ?? PivotType.Transaction),
                MainValue       = pivotValue,
            };

            return(pivotData);
        }
Пример #31
0
        public void ExportChannelReportToExcelUSD(Stream stream, int year)
        {
            if (stream == null)
                throw new ArgumentNullException("stream");

            using (var xlPackage = new ExcelPackage(stream))
            {
                // get handle to the existing worksheet
                var worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1");

                //Format Header 1
                int header1 = 2;
                worksheet.Cells[1, header1].Value = "Total";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "January";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "February";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "March";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "April";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "May";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "June";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "July";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "August";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "September";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "October";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "November";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "December";
                header1 = header1 + 6;

                //Format Header 2
                for (int month = 0; month <= 12; month++)
                {
                    int i = month * 6;

                    worksheet.Cells[2, i + 2].Value = "Last actual amt";
                    worksheet.Cells[2, i + 3].Value = "Target amt";
                    worksheet.Cells[2, i + 4].Value = "Actual amt";
                    worksheet.Cells[2, i + 5].Value = "Achievement";
                    worksheet.Cells[2, i + 6].Value = "Growth";
                    worksheet.Cells[2, i + 7].Value = "Quantity";
                }

                //Exc
                double exchangeRate = 22500;

                //Data
                int row = 3;

                var thisYearTargetSales = from item in _dataContext.TargetSetups
                                          where item.Year == year
                                          && item.IsTeamTarget == true
                                          //&& (item.Mr.SalesTeam.ChannelId == (int)ChannelEnum.Direct || item.Mr.SalesTeam.ChannelId == (int)ChannelEnum.Indirect)
                                          select item;

                var actualSalesQuery = from item in _dataContext.ReportDatas
                                       where (item.Year == year || item.Year == year - 1)
                                       select item;

                if (_workContext.CurrentCustomer.IsInCustomerRole("Administrators") || _workContext.CurrentCustomer.IsInCustomerRole("Management"))
                {
                    //sales = query;
                }
                else
                {
                    if (_workContext.CurrentCustomer.IsInCustomerRole("MrManagers"))
                    {
                        actualSalesQuery = from item in actualSalesQuery
                                           where item.SalesTeamCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
                                           select item;
                    }
                    else
                    {
                        actualSalesQuery = from item in actualSalesQuery
                                           where item.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
                                           select item;

                        thisYearTargetSales = from item in _dataContext.TargetSetups
                                              where item.Year == year
                                              && item.IsTeamTarget == false
                                              && item.Mr.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
                                              select item;
                    }

                }

                DataTable table = LinqExtension.LinqResultToDataTable(actualSalesQuery);

                var quantityPivotData = new PivotData(new[] { "ChannelName", "Sku", "Year", "Month" },
                                new SumAggregatorFactory("Quantity")
                    );
                quantityPivotData.ProcessData(new DataTableReader(table));

                var amountPivotData = new PivotData(new[] { "ChannelName", "Sku", "Year", "Month" },
                                new SumAggregatorFactory("CifAmount")
                    );
                amountPivotData.ProcessData(new DataTableReader(table));

                foreach (var channel in _dataContext.Channels.Where(a => a.ChannelName.ToLower() != "na"))
                {
                    CommonTargetActualSales channelLine = new CommonTargetActualSales();
                    channelLine.Code = channel.ChannelName;
                    channelLine.Name = channel.ChannelName;

                    channelLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 1].Value);
                    channelLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 2].Value);
                    channelLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 3].Value);
                    channelLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 4].Value);
                    channelLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 5].Value);
                    channelLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 6].Value);
                    channelLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 7].Value);
                    channelLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 8].Value);
                    channelLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 9].Value);
                    channelLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 10].Value);
                    channelLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 11].Value);
                    channelLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 12].Value);

                    channelLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 1].Value) ;
                    channelLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 2].Value) ;
                    channelLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 3].Value) ;
                    channelLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 4].Value) ;
                    channelLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 5].Value) ;
                    channelLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 6].Value) ;
                    channelLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 7].Value) ;
                    channelLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 8].Value) ;
                    channelLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 9].Value) ;
                    channelLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 10].Value) ;
                    channelLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 11].Value) ;
                    channelLine.ActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 12].Value) ;

                    channelLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 1].Value) ;
                    channelLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 2].Value) ;
                    channelLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 3].Value) ;
                    channelLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 4].Value) ;
                    channelLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 5].Value) ;
                    channelLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 6].Value) ;
                    channelLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 7].Value) ;
                    channelLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 8].Value) ;
                    channelLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 9].Value) ;
                    channelLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 10].Value) ;
                    channelLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 11].Value) ;
                    channelLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 12].Value) ;

                    if (thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Count() > 0)
                    {
                        channelLine.TargetAmt01 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount01) / exchangeRate;
                        channelLine.TargetAmt02 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount02) / exchangeRate;
                        channelLine.TargetAmt03 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount03) / exchangeRate;
                        channelLine.TargetAmt04 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount04) / exchangeRate;
                        channelLine.TargetAmt05 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount05) / exchangeRate;
                        channelLine.TargetAmt06 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount06) / exchangeRate;
                        channelLine.TargetAmt07 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount07) / exchangeRate;
                        channelLine.TargetAmt08 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount08) / exchangeRate;
                        channelLine.TargetAmt09 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount09) / exchangeRate;
                        channelLine.TargetAmt10 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount10) / exchangeRate;
                        channelLine.TargetAmt11 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount11) / exchangeRate;
                        channelLine.TargetAmt12 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount12) / exchangeRate;
                    }

                    if (channelLine.TargetAmt == 0 && channelLine.LastYearActualAmt == 0 && channelLine.ActualAmt == 0)
                    {
                        //do nothing
                    }
                    else
                    {
                        ExportChannelReportToExcelRow(worksheet, row, Color.LightBlue, channelLine);
                        row++;
                    }

                    foreach (var item in _dataContext.Items.OrderBy(a=>a.ItemName))
                    {
                        CommonTargetActualSales itemLine = new CommonTargetActualSales();
                        itemLine.Code = item.Sku;
                        itemLine.Name = item.ItemName;

                        itemLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 1].Value);
                        itemLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 2].Value);
                        itemLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 3].Value);
                        itemLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 4].Value);
                        itemLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 5].Value);
                        itemLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 6].Value);
                        itemLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 7].Value);
                        itemLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 8].Value);
                        itemLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 9].Value);
                        itemLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 10].Value);
                        itemLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 11].Value);
                        itemLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 12].Value);

                        itemLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 1].Value) ;
                        itemLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 2].Value) ;
                        itemLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 3].Value) ;
                        itemLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 4].Value) ;
                        itemLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 5].Value) ;
                        itemLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 6].Value) ;
                        itemLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 7].Value) ;
                        itemLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 8].Value) ;
                        itemLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 9].Value) ;
                        itemLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 10].Value) ;
                        itemLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 11].Value) ;
                        itemLine.ActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 12].Value) ;

                        itemLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 1].Value) ;
                        itemLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 2].Value) ;
                        itemLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 3].Value) ;
                        itemLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 4].Value) ;
                        itemLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 5].Value) ;
                        itemLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 6].Value) ;
                        itemLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 7].Value) ;
                        itemLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 8].Value) ;
                        itemLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 9].Value) ;
                        itemLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 10].Value) ;
                        itemLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 11].Value) ;
                        itemLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 12].Value) ;

                        if (thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Count() > 0)
                        {
                            itemLine.TargetAmt01 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount01) / exchangeRate;
                            itemLine.TargetAmt02 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount02) / exchangeRate;
                            itemLine.TargetAmt03 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount03) / exchangeRate;
                            itemLine.TargetAmt04 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount04) / exchangeRate;
                            itemLine.TargetAmt05 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount05) / exchangeRate;
                            itemLine.TargetAmt06 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount06) / exchangeRate;
                            itemLine.TargetAmt07 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount07) / exchangeRate;
                            itemLine.TargetAmt08 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount08) / exchangeRate;
                            itemLine.TargetAmt09 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount09) / exchangeRate;
                            itemLine.TargetAmt10 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount10) / exchangeRate;
                            itemLine.TargetAmt11 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount11) / exchangeRate;
                            itemLine.TargetAmt12 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount12) / exchangeRate;
                        }

                        if (itemLine.TargetAmt == 0 && itemLine.LastYearActualAmt == 0 && itemLine.ActualAmt == 0)
                        {
                            //do nothing
                        }
                        else
                        {
                            ExportChannelReportToExcelRow(worksheet, row, Color.LightBlue, itemLine, 1);
                            row++;
                        }
                    }

                }

                //Report name
                worksheet.Cells[2, 1].Value = "Report by Channel - product in " + year.ToString();
                worksheet.Cells[2, 1].AutoFitColumns();

                //Rows & columns count
                int cols = 12 * 6 + 6 + 1;
                int rows = row;

                //Format header
                var header = worksheet.Cells[1, 1, 2, cols];
                header.Style.Font.Bold = true;
                header.Style.Fill.PatternType = ExcelFillStyle.Solid;
                header.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
                //Format total
                var total = worksheet.Cells[rows, 1, row, cols];
                total.Style.Font.Bold = true;
                total.Style.Fill.PatternType = ExcelFillStyle.Solid;
                total.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
                //Format row
                var text = worksheet.Cells[1, 1, row, 1];
                text.Style.Font.Bold = true;
                text.Style.Fill.PatternType = ExcelFillStyle.Solid;
                text.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
                text.AutoFitColumns();

                // save the new spreadsheet
                xlPackage.Save();
            }
        }
 private void updatePivotRotation(PivotData pd, bool lerp = true)
 {
     //vector from pivot to sun
     Vector3 vector = pd.pivotTransform.InverseTransformPoint(sunTransform.position);
     //finding angle to turn towards based on direction of vector on a single axis
     float y = (float)SSTUUtils.toDegrees(Mathf.Atan2(vector.x, vector.z));
     if (pd.pivotTransform.lossyScale.z < 0) { y = -y; }
     if (lerp) // lerp towards destination rotation by trackingSpeed amount
     {
         Quaternion to = pd.pivotTransform.rotation * Quaternion.Euler(0f, y, 0f);
         pd.pivotTransform.rotation = Quaternion.Lerp(pd.pivotTransform.rotation, to, TimeWarp.deltaTime * this.trackingSpeed);
     }
     else // or just set to the new rotation
     {
         Quaternion to = pd.pivotTransform.rotation * Quaternion.Euler(0f, y, 0f);
         pd.pivotTransform.rotation = to;
     }
 }
        private void findTransforms()
        {
            String[] suncatcherNames = SSTUUtils.parseCSV(rayTransforms);
            String[] pivotNames = SSTUUtils.parseCSV(pivotTransforms);
            String[] secPivotNames = SSTUUtils.parseCSV(secondaryPivotTransforms);

            PivotData pd;
            SuncatcherData sd;
            int len2;
            Transform[] trs;
            String name;

            List<PivotData> tempPivotData = new List<PivotData>();
            int len = pivotNames.Length;
            for (int i = 0; i < len; i++)
            {
                name = pivotNames[i];
                if (String.IsNullOrEmpty(name)) { MonoBehaviour.print("ERROR: Empty name for solar pivot for part: " + part); }
                trs = part.transform.FindChildren(name);
                len2 = trs.Length;
                if (len2 == 0) { MonoBehaviour.print("ERROR: Could not locate solar pivot transforms for name: "+name + " for part: " + part); }
                for (int k = 0; k < len2; k++)
                {
                    pd = new PivotData();
                    pd.pivotTransform = trs[k];
                    pd.defaultOrientation = pd.pivotTransform.localRotation;
                    tempPivotData.Add(pd);
                }
            }
            pivotData = tempPivotData.ToArray();
            tempPivotData.Clear();

            len = secPivotNames.Length;
            for (int i = 0; i < len; i++)
            {
                name = secPivotNames[i];
                if (String.IsNullOrEmpty(name)) { continue; }
                trs = part.transform.FindChildren(name);
                len2 = trs.Length;
                if (len2 == 0) { MonoBehaviour.print("ERROR: Could not locate secondary pivot transforms for name: " + name + " for part: " + part); }
                for (int k = 0; k < len2; k++)
                {
                    pd = new PivotData();
                    pd.pivotTransform = trs[k];
                    pd.defaultOrientation = pd.pivotTransform.localRotation;
                    tempPivotData.Add(pd);
                }
            }
            secondaryPivotData = tempPivotData.ToArray();
            tempPivotData.Clear();

            List<SuncatcherData> tempSunData = new List<SuncatcherData>();
            len = suncatcherNames.Length;
            for (int i = 0; i < len; i++)
            {
                name = suncatcherNames[i];
                if (String.IsNullOrEmpty(name)) { MonoBehaviour.print("ERROR: Empty name for suncatcher for part: "+ part); }
                trs = part.transform.FindChildren(name);
                len2 = trs.Length;
                if (len2 == 0) { MonoBehaviour.print("ERROR: Could not locate suncatcher transforms for name: " + name+" for part: "+part); }
                for (int k = 0; k < len2; k++)
                {
                    sd = new SuncatcherData();
                    sd.suncatcherTransform = trs[k];
                    tempSunData.Add(sd);
                }
            }
            suncatcherData = tempSunData.ToArray();

            Transform t1;
            if (windBreakTransformName != null && windBreakTransformName.Length > 0)
            {
                t1 = part.FindModelTransform(windBreakTransformName);
                if (t1 != null)
                {
                    windBreakTransform = t1;
                }
            }
            if (windBreakTransform == null && pivotData.Length > 0)
            {
                windBreakTransform = pivotData[0].pivotTransform;
            }//else it will use default vessel transform
        }
Пример #34
0
        public void Export3ItemGroupsToExcel(Stream stream, int year)
        {
            using (var package = new ExcelPackage(stream))
            {
                var query = from item in _dataContext.ReportDatas
                            where item.ChannelName.ToLower() == "direct"
                            && (item.ItemGroupName.ToLower() == "prepenem" || item.ItemGroupName.ToLower() == "maxpenepm" || item.ItemGroupName.ToLower() == "combilipid")
                            && (item.Year == year || item.Year == year - 1)
                            select item;

                DataTable table = LinqExtension.LinqResultToDataTable(query);

                var pivotData = new PivotData(new[] { "ChannelName", "ItemGroupName", "ClientName", "Year", "Month" },
                        new SumAggregatorFactory("CifAmount")
                    );

                pivotData.ProcessData(new DataTableReader(table));

                package.Compression = CompressionLevel.Default;
                var pivot = package.Workbook.Worksheets.Add("PivotTable");
                var data = package.Workbook.Worksheets.Add("Data");

                var pivotTable = new PivotTable(
                        new[] { "ItemGroupName", "ClientName" }, //rows
                        new[] { "Year", "Month" }, // columns
                        pivotData
                    );

                var excelPivotTableWriter = new ExcelPivotTableWriter(pivot, data);

                excelPivotTableWriter.Write(pivotTable);

                //pkg.Workbook.Worksheets.Delete(wsData);

                package.Save();
            }
        }
Пример #35
0
        public void ExportSalesTeamMrReportToExcel(Stream stream, int year)
        {
            if (stream == null)
                throw new ArgumentNullException("stream");

            using (var xlPackage = new ExcelPackage(stream))
            {
                // get handle to the existing worksheet
                var worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1");

                //Format Header 1
                int header1 = 2;
                worksheet.Cells[1, header1].Value = "Total";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "January";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "February";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "March";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "April";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "May";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "June";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "July";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "August";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "September";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "October";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "November";
                header1 = header1 + 6;
                worksheet.Cells[1, header1].Value = "December";
                header1 = header1 + 6;

                //Format Header 2
                for (int month = 0; month <= 12; month++)
                {
                    int i = month * 6;

                    worksheet.Cells[2, i + 2].Value = "Last actual amt";
                    worksheet.Cells[2, i + 3].Value = "Target amt";
                    worksheet.Cells[2, i + 4].Value = "Actual amt";
                    worksheet.Cells[2, i + 5].Value = "Achievement";
                    worksheet.Cells[2, i + 6].Value = "Growth";
                    worksheet.Cells[2, i + 7].Value = "Quantity";
                }

                //Data
                int row = 3;

                var thisYearTargetSales = from item in _dataContext.TargetSetups
                                          where item.Year == year
                                          && item.IsTeamTarget == false
                                          && item.Item.Sku.ToLower() != "na"
                                          select item;

                var actualSalesQuery = from item in _dataContext.ReportDatas
                                       where (item.Year == year || item.Year == year - 1)
                                       select item;

                if (_workContext.CurrentCustomer.IsInCustomerRole("Administrators") || _workContext.CurrentCustomer.IsInCustomerRole("Management"))
                {
                    //sales = query;
                }
                else
                {
                    if (_workContext.CurrentCustomer.IsInCustomerRole("MrManagers"))
                    {
                        actualSalesQuery = from item in actualSalesQuery
                                           where item.SalesTeamCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
                                           select item;
                    }
                    else
                    {
                        actualSalesQuery = from item in actualSalesQuery
                                           where item.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
                                           select item;

                        thisYearTargetSales = from item in _dataContext.TargetSetups
                                              where item.Year == year
                                              && item.IsTeamTarget == true
                                              && item.Mr.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
                                              select item;
                    }

                }

                DataTable table = LinqExtension.LinqResultToDataTable(actualSalesQuery);

                var quantityPivotData = new PivotData(new[] { "ChannelName", "SalesTeamCode", "MrCode", "Year", "Month" },
                                new SumAggregatorFactory("Quantity")
                    );
                quantityPivotData.ProcessData(new DataTableReader(table));

                var amountPivotData = new PivotData(new[] { "ChannelName", "SalesTeamCode", "MrCode", "Year", "Month" },
                                new SumAggregatorFactory("TotalAmount")
                    );
                amountPivotData.ProcessData(new DataTableReader(table));

                foreach (var channel in _dataContext.Channels)
                {
                    #region Channel line
                    CommonTargetActualSales channelLine = new CommonTargetActualSales();
                    channelLine.Code = channel.ChannelId.ToString();
                    channelLine.Name = channel.ChannelName;

                    var thisYearTargetSalesChannel = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId);

                    channelLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 1].Value);
                    channelLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 2].Value);
                    channelLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 3].Value);
                    channelLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 4].Value);
                    channelLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 5].Value);
                    channelLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 6].Value);
                    channelLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 7].Value);
                    channelLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 8].Value);
                    channelLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 9].Value);
                    channelLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 10].Value);
                    channelLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 11].Value);
                    channelLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 12].Value);

                    channelLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 1].Value);
                    channelLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 2].Value);
                    channelLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 3].Value);
                    channelLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 4].Value);
                    channelLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 5].Value);
                    channelLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 6].Value);
                    channelLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 7].Value);
                    channelLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 8].Value);
                    channelLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 9].Value);
                    channelLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 10].Value);
                    channelLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 11].Value);
                    channelLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 12].Value);

                    channelLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 1].Value);
                    channelLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 2].Value);
                    channelLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 3].Value);
                    channelLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 4].Value);
                    channelLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 5].Value);
                    channelLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 6].Value);
                    channelLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 7].Value);
                    channelLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 8].Value);
                    channelLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 9].Value);
                    channelLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 10].Value);
                    channelLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 11].Value);
                    channelLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 12].Value);

                    channelLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 1);
                    channelLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 2);
                    channelLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 3);
                    channelLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 4);
                    channelLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 5);
                    channelLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 6);
                    channelLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 7);
                    channelLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 8);
                    channelLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 9);
                    channelLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 10);
                    channelLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 11);
                    channelLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 12);

                    if (channelLine.TargetAmt == 0 && channelLine.LastYearActualAmt == 0 && channelLine.ActualAmt == 0)
                    {
                        //do nothing
                    }
                    else
                    {
                        ExportSalesTeamMrReportToExcelRow(worksheet, row, Color.LightBlue, channelLine);
                        row++;
                    }
                    #endregion

                    foreach (var team in channel.SalesTeams)
                    {
                        #region team line

                        CommonTargetActualSales teamLine = new CommonTargetActualSales();
                        teamLine.Code = team.SalesTeamCode;
                        teamLine.Name = team.SalesTeamName;

                        var thisYearTargetItemGroup = thisYearTargetSales.Where(a => a.Mr.SalesTeam.SalesTeamCode.ToLower() == team.SalesTeamCode.ToLower());

                        teamLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 1].Value);
                        teamLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 2].Value);
                        teamLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 3].Value);
                        teamLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 4].Value);
                        teamLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 5].Value);
                        teamLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 6].Value);
                        teamLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 7].Value);
                        teamLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 8].Value);
                        teamLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 9].Value);
                        teamLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 10].Value);
                        teamLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 11].Value);
                        teamLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 12].Value);

                        teamLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 1].Value);
                        teamLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 2].Value);
                        teamLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 3].Value);
                        teamLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 4].Value);
                        teamLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 5].Value);
                        teamLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 6].Value);
                        teamLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 7].Value);
                        teamLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 8].Value);
                        teamLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 9].Value);
                        teamLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 10].Value);
                        teamLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 11].Value);
                        teamLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 12].Value);

                        teamLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 1].Value);
                        teamLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 2].Value);
                        teamLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 3].Value);
                        teamLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 4].Value);
                        teamLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 5].Value);
                        teamLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 6].Value);
                        teamLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 7].Value);
                        teamLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 8].Value);
                        teamLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 9].Value);
                        teamLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 10].Value);
                        teamLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 11].Value);
                        teamLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 12].Value);

                        teamLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 1);
                        teamLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 2);
                        teamLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 3);
                        teamLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 4);
                        teamLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 5);
                        teamLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 6);
                        teamLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 7);
                        teamLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 8);
                        teamLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 9);
                        teamLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 10);
                        teamLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 11);
                        teamLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 12);

                        if (teamLine.TargetAmt == 0 && teamLine.LastYearActualAmt == 0 && teamLine.ActualAmt == 0)
                        {
                            //do nothing
                        }
                        else
                        {
                            ExportSalesTeamMrReportToExcelRow(worksheet, row, Color.LightBlue, teamLine, 1);

                            row++;
                        }

                        #endregion

                        foreach (var item in team.Mrs.Where(a=>a.MrCode.ToLower()!=team.SalesTeamCode.ToLower()))
                        {
                            #region item line

                            CommonTargetActualSales mrLine = new CommonTargetActualSales();
                            mrLine.Code = item.MrCode;
                            mrLine.Name = item.MrName;

                            var thisYearTargetItem = thisYearTargetSales.Where(a => a.Mr.MrCode.ToLower() == item.MrCode.ToLower());

                            mrLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 1].Value);
                            mrLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 2].Value);
                            mrLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 3].Value);
                            mrLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 4].Value);
                            mrLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 5].Value);
                            mrLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 6].Value);
                            mrLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 7].Value);
                            mrLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 8].Value);
                            mrLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 9].Value);
                            mrLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 10].Value);
                            mrLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 11].Value);
                            mrLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 12].Value);

                            mrLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 1].Value);
                            mrLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 2].Value);
                            mrLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 3].Value);
                            mrLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 4].Value);
                            mrLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 5].Value);
                            mrLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 6].Value);
                            mrLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 7].Value);
                            mrLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 8].Value);
                            mrLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 9].Value);
                            mrLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 10].Value);
                            mrLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 11].Value);
                            mrLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 12].Value);

                            mrLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 1].Value);
                            mrLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 2].Value);
                            mrLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 3].Value);
                            mrLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 4].Value);
                            mrLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 5].Value);
                            mrLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 6].Value);
                            mrLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 7].Value);
                            mrLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 8].Value);
                            mrLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 9].Value);
                            mrLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 10].Value);
                            mrLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 11].Value);
                            mrLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 12].Value);

                            mrLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetItem, year, 1);
                            mrLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetItem, year, 2);
                            mrLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetItem, year, 3);
                            mrLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetItem, year, 4);
                            mrLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetItem, year, 5);
                            mrLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetItem, year, 6);
                            mrLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetItem, year, 7);
                            mrLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetItem, year, 8);
                            mrLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetItem, year, 9);
                            mrLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetItem, year, 10);
                            mrLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetItem, year, 11);
                            mrLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetItem, year, 12);

                            if (mrLine.TargetAmt == 0 && mrLine.LastYearActualAmt == 0 && mrLine.ActualAmt == 0)
                            {
                                //do nothing
                            }
                            else
                            {
                                ExportSalesTeamMrReportToExcelRow(worksheet, row, Color.LightBlue, mrLine, 2);
                                row++;
                            }
                            #endregion
                        }

                    }
                }

                #region Total line
                CommonTargetActualSales totalLine = new CommonTargetActualSales();
                totalLine.Code = "Total";
                totalLine.Name = "Total";

                totalLine.ActualQty01 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 1].Value);
                totalLine.ActualQty02 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 2].Value);
                totalLine.ActualQty03 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 3].Value);
                totalLine.ActualQty04 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 4].Value);
                totalLine.ActualQty05 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 5].Value);
                totalLine.ActualQty06 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 6].Value);
                totalLine.ActualQty07 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 7].Value);
                totalLine.ActualQty08 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 8].Value);
                totalLine.ActualQty09 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 9].Value);
                totalLine.ActualQty10 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 10].Value);
                totalLine.ActualQty11 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 11].Value);
                totalLine.ActualQty12 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 12].Value);

                totalLine.ActualAmt01 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 1].Value);
                totalLine.ActualAmt02 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 2].Value);
                totalLine.ActualAmt03 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 3].Value);
                totalLine.ActualAmt04 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 4].Value);
                totalLine.ActualAmt05 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 5].Value);
                totalLine.ActualAmt06 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 6].Value);
                totalLine.ActualAmt07 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 7].Value);
                totalLine.ActualAmt08 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 8].Value);
                totalLine.ActualAmt09 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 9].Value);
                totalLine.ActualAmt10 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 10].Value);
                totalLine.ActualAmt11 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 11].Value);
                totalLine.ActualAmt11 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 12].Value);

                totalLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 1].Value);
                totalLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 2].Value);
                totalLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 3].Value);
                totalLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 4].Value);
                totalLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 5].Value);
                totalLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 6].Value);
                totalLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 7].Value);
                totalLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 8].Value);
                totalLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 9].Value);
                totalLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 10].Value);
                totalLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 11].Value);
                totalLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 12].Value);

                totalLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetSales, year, 1);
                totalLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetSales, year, 2);
                totalLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetSales, year, 3);
                totalLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetSales, year, 4);
                totalLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetSales, year, 5);
                totalLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetSales, year, 6);
                totalLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetSales, year, 7);
                totalLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetSales, year, 8);
                totalLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetSales, year, 9);
                totalLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetSales, year, 10);
                totalLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetSales, year, 11);
                totalLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetSales, year, 12);

                ExportItemGroupItemReportToExcelRow(worksheet, row, Color.LightBlue, totalLine);

                #endregion

                //Report name
                worksheet.Cells[2, 1].Value = "Report by Channel - Sales team - Mr in " + year.ToString();
                worksheet.Cells[2, 1].AutoFitColumns();

                //Rows & columns count
                int cols = 12 * 6 + 6 + 1;
                int rows = row;

                //Format header
                var header = worksheet.Cells[1, 1, 2, cols];
                header.Style.Font.Bold = true;
                header.Style.Fill.PatternType = ExcelFillStyle.Solid;
                header.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
                //Format total
                var total = worksheet.Cells[rows, 1, row, cols];
                total.Style.Font.Bold = true;
                total.Style.Fill.PatternType = ExcelFillStyle.Solid;
                total.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
                //Format row
                var text = worksheet.Cells[1, 1, row, 1];
                text.Style.Font.Bold = true;
                text.Style.Fill.PatternType = ExcelFillStyle.Solid;
                text.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
                text.AutoFitColumns();

                // save the new spreadsheet
                xlPackage.Save();
            }
        }