Open XML SDK Quick Guide - Otaku-Projects/ReportEngine GitHub Wiki

            Cell cell12 = new Cell(){ CellReference = "E5", StyleIndex = (UInt32Value)5U, DataType = CellValues.SharedString };
            CellValue cellValue7 = new CellValue();
            cellValue7.Text = "2";

            SharedStringTable sharedStringTable1 = new SharedStringTable(){ Count = (UInt32Value)13U, UniqueCount = (UInt32Value)13U };

            SharedStringItem sharedStringItem1 = new SharedStringItem();
            Text text1 = new Text();
            text1.Text = "No.";

            sharedStringItem1.Append(text1);

            SharedStringItem sharedStringItem2 = new SharedStringItem();
            Text text2 = new Text();
            text2.Text = "Name of Patient";

            sharedStringItem2.Append(text2);

            SharedStringItem sharedStringItem3 = new SharedStringItem();
            Text text3 = new Text();
            text3.Text = "HKID no.";

            sharedStringItem3.Append(text3);

//...

// Generates content of pivotTablePart1.
        private void GeneratePivotTablePart1Content(PivotTablePart pivotTablePart1)
...
            Location location1 = new Location(){ Reference = "A5:B9", FirstHeaderRow = (UInt32Value)1U, FirstDataRow = (UInt32Value)1U, FirstDataColumn = (UInt32Value)1U, RowPageCount = (UInt32Value)1U, ColumnsPerPage = (UInt32Value)1U };
...

            PivotField pivotField10 = new PivotField(){ Axis = PivotTableAxisValues.AxisPage, ShowAll = false };
...            
            PageFields pageFields1 = new PageFields(){ Count = (UInt32Value)1U };
            PageField pageField1 = new PageField(){ Field = 9, Hierarchy = -1 };
            pageFields1.Append(pageField1);
...
            pivotTableDefinition1.Append(pageFields1);

// Generates content of pivotTablePart1.
        private void GeneratePivotTablePart1Content(PivotTablePart pivotTablePart1)
...
            Location location1 = new Location(){ Reference = "A5:B9", FirstHeaderRow = (UInt32Value)1U, FirstDataRow = (UInt32Value)1U, FirstDataColumn = (UInt32Value)1U };
...
            PivotField pivotField10 = new PivotField(){ ShowAll = false };
...
// removed PageFields pageFields1
...
// removed pivotTableDefinition1.Append(pageFields1);

        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
...
            DefinedNames definedNames1 = new DefinedNames();
            DefinedName definedName1 = new DefinedName(){ Name = "_xlnm._FilterDatabase", LocalSheetId = (UInt32Value)1U, Hidden = true };
            definedName1.Text = "Sheet1!$A$1:$K$2";
...
            PivotCaches pivotCaches1 = new PivotCaches();
            PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)12U, Id = "rId3" };
...

        // Generates content of pivotTableCacheDefinitionPart1.
        private void GeneratePivotTableCacheDefinitionPart1Content(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1)
...
            WorksheetSource worksheetSource1 = new WorksheetSource(){ Reference = "A1:K2", Sheet = "Sheet1" };
...
            CacheFields cacheFields1 = new CacheFields(){ Count = (UInt32Value)12U };
...
// one of the cache field was removed
            cacheFields1.Append(cacheField8);

        // Generates content of pivotTableCacheRecordsPart1.
        private void GeneratePivotTableCacheRecordsPart1Content(PivotTableCacheRecordsPart pivotTableCacheRecordsPart1)
// I removed the cache to save efforts for making cache in coding, remember to enable refresh on load
            // remove pivot cache
            //pivotTableCacheRecordsPart1.PivotCacheRecords = pivotCacheRecords1;

// Generates content of worksheetPart1.
        private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
...
            SheetDimension sheetDimension1 = new SheetDimension(){ Reference = "A1:L2" };
...
            SheetView sheetView1 = new SheetView(){ TopLeftCell = "B1", WorkbookViewId = (UInt32Value)0U };
            Selection selection1 = new Selection(){ ActiveCell = "I2", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "I2" } };
...
// remember to remove a column in columns1
            columns1.Append(column9);
// remember to remove a column when printing body row
            row1.Append(cell9);
...
// update raw data sheet filter if exists
AutoFilter autoFilter1 = new AutoFilter(){ Reference = "A1:L99986" };
...
// I am not sure is sheetDimension1 required update necessary, I comment it, the excel still generate successfully
// worksheet1.Append(sheetDimension1);

        // Generates content of pivotTablePart1.
        private void GeneratePivotTablePart1Content(PivotTablePart pivotTablePart1)
...
            PivotFields pivotFields1 = new PivotFields(){ Count = (UInt32Value)12U };
...
// remove one of pivotFields
pivotFields1.Append(pivotField9);
...
            PageFields pageFields1 = new PageFields(){ Count = (UInt32Value)1U };
            PageField pageField1 = new PageField(){ Field = 8, Hierarchy = -1 };

            pageFields1.Append(pageField1);
...
            DataFields dataFields1 = new DataFields(){ Count = (UInt32Value)1U };
            DataField dataField1 = new DataField(){ Name = "No. of service users eligible for Annual Grant", Field = (UInt32Value)9U, BaseField = 6, BaseItem = (UInt32Value)0U };

        // Generates content of pivotTableCacheDefinitionPart1.
        private void GeneratePivotTableCacheDefinitionPart1Content(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1, AnnualGrantPatientSearchCriteria criteria, int dataLength = 1)
...
        pivotCacheDefinition1.RefreshOnLoad = true;

            // Total Amount Reference = "E18",
            Cell cell171 = new Cell() { StyleIndex = (UInt32Value)48U };
            cell171.DataType = CellValues.Number;
            CellValue cellValue47 = new CellValue();
            cellValue47.Text = (sbd.returnValueToString(Convert.ToDecimal(dataList.Sum(x => x.APP_CLAIM_AMT))));
            cell171.Append(cellValue47);
//...
            row17.Append(cell170);
            row17.Append(cell171);
            row17.Append(cell172);
//...