C# Worksheet.Append方法代码示例

        public void Create(string fileName)
        {
            FileName = fileName;

            using (SpreadsheetDocument doc = SpreadsheetDocument.Create(FileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = doc.AddWorkbookPart();
                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                //	Create Styles
                var stylesPart = doc.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                Style = new CustomStylesheet();
                LoadCustomFonts();
                LoadCustomBorders();
                LoadCustomStyles();
                Style.Save(stylesPart);

                string relId = workbookPart.GetIdOfPart(worksheetPart);

                //	create workbook and sheet
                var workbook = new Workbook();
                var worksheet = new Worksheet();
                var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };

                //	create columns
                var columns = new Columns();
                CreateColumns(columns);
                worksheet.Append(columns);

                //	create Sheet
                var sheets = new Sheets();
                var sheet = new Sheet { Name = "My sheet", SheetId = 1, Id = relId };
                sheets.Append(sheet);

                workbook.Append(fileVersion);
                workbook.Append(sheets);

                var sheetData = new SheetData();
                LoadData(sheetData);
                worksheet.Append(sheetData);

                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                doc.WorkbookPart.Workbook = workbook;
                doc.WorkbookPart.Workbook.Save();
                //doc.Close();
            }
        }

        // Adds child parts and generates content of the specified part
        private static void CreateParts(SpreadsheetDocument document)
        {
            WorkbookPart workbookPart1 = document.AddWorkbookPart();
            GenerateWorkbookPart1Content(workbookPart1);

            WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
            Worksheet worksheet1 = new Worksheet();
            SheetData sheetData1 = new SheetData();
            worksheet1.Append(sheetData1);
            worksheetPart1.Worksheet = worksheet1;
            //GenerateWorksheetPart1Content(worksheetPart1);
        }

        public MemoryStream GetExcel(string[] fieldsToExpose, DataTable data)
        {
            MemoryStream stream = new MemoryStream();
            UInt32 rowcount = 0;

            // Create the Excel document
            var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
            var workbookPart = document.AddWorkbookPart();
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            var relId = workbookPart.GetIdOfPart(worksheetPart);

            var workbook = new Workbook();
            var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
            var worksheet = new Worksheet();
            var sheetData = new SheetData();
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;

            var sheets = new Sheets();
            var sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
            sheets.Append(sheet);
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            document.WorkbookPart.Workbook = workbook;
            document.WorkbookPart.Workbook.Save();

            // Add header to the sheet
            var row = new Row { RowIndex = ++rowcount };
            for (int i = 0; i < fieldsToExpose.Length; i++)
            {
                row.Append(CreateTextCell(ColumnLetter(i), rowcount, fieldsToExpose[i]));
            }
            sheetData.AppendChild(row);
            worksheetPart.Worksheet.Save();

            // Add data to the sheet
            foreach (DataRow dataRow in data.Rows)
            {
                row = new Row { RowIndex = ++rowcount };
                for (int i = 0; i < fieldsToExpose.Length; i++)
                {
                    row.Append(CreateTextCell(ColumnLetter(i), rowcount, dataRow[fieldsToExpose[i]].ToString()));
                }
                sheetData.AppendChild(row);
            }
            worksheetPart.Worksheet.Save();
            document.Close();
            return stream;
        }

        private void CreateWorkSheet(WorksheetPart worksheetPart, DataGridView dgv)
        {
            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();

            UInt32Value currRowIndex = 1U;
            Row excelRow = new Row();
            foreach(DataGridViewColumn col in dgv.Columns)
            {
                Cell cell = new Cell();
                cell.DataType = CellValues.String;
                CellValue cellValue = new CellValue();
                cellValue.Text = col.HeaderText;
                cell.Append(cellValue);
                excelRow.Append(cell);
            }
            sheetData.Append(excelRow);
            currRowIndex++;
            foreach (DataGridViewRow row in dgv.Rows)
            {
                excelRow = new Row();
                excelRow.RowIndex = currRowIndex++;
                foreach (DataGridViewCell col in row.Cells)
                {
                    Cell cell = new Cell();
                    CellValue cellValue = new CellValue();
                    cell.DataType = CellValues.String;
                    cellValue.Text = col.Value?.ToString();
                    cell.Append(cellValue);
                    excelRow.Append(cell);
                }
                sheetData.Append(excelRow);
            }

            SheetFormatProperties formattingProps = new SheetFormatProperties()
            {
                DefaultRowHeight = 20D,
                DefaultColumnWidth = 20D
            };

            worksheet.Append(formattingProps);
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;
        }

        // Generates content of worksheetPart1.
        private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
        {
            Worksheet worksheet1 = new Worksheet();
            worksheet1.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

            SheetData sheetData1 = new SheetData();

            worksheet1.Append(sheetData1);

            worksheetPart1.Worksheet = worksheet1;
        }

        // Generates content of worksheetPart1.
        protected override void GenerateWorksheetPartContent(WorksheetPart worksheetPart)
        {
            Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
            SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1:E30" };

            SheetViews sheetViews1 = new SheetViews();

            SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
            Selection selection1 = new Selection() { ActiveCell = "A2", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A2:E2" } };

            sheetView1.Append(selection1);

            sheetViews1.Append(sheetView1);
            SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 12D, DyDescent = 0.2D };

            Columns columns1 = new Columns();
            Column column1 = new Column() { Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 42.7109375D, Style = (UInt32Value)1U, CustomWidth = true };
            Column column2 = new Column() { Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 11.5703125D, Style = (UInt32Value)1U, CustomWidth = true };
            Column column3 = new Column() { Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 6.85546875D, Style = (UInt32Value)1U, CustomWidth = true };
            Column column4 = new Column() { Min = (UInt32Value)4U, Max = (UInt32Value)4U, Width = 11.7109375D, Style = (UInt32Value)1U, CustomWidth = true };
            Column column5 = new Column() { Min = (UInt32Value)5U, Max = (UInt32Value)5U, Width = 13.5703125D, Style = (UInt32Value)1U, CustomWidth = true };
            Column column6 = new Column() { Min = (UInt32Value)6U, Max = (UInt32Value)16384U, Width = 9.140625D, Style = (UInt32Value)1U };

            columns1.Append(column1);
            columns1.Append(column2);
            columns1.Append(column3);
            columns1.Append(column4);
            columns1.Append(column5);
            columns1.Append(column6);

            SheetData sheetData1 = new SheetData();

            Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:5" }, DyDescent = 0.2D };

            Cell cell1 = new Cell() { CellReference = "B1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
            CellValue cellValue1 = new CellValue();
            cellValue1.Text = "0";

            cell1.Append(cellValue1);

            row1.Append(cell1);

            Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:5" }, Height = 23.25D, CustomHeight = true, DyDescent = 0.2D };

            Cell cell2 = new Cell() { CellReference = "A2", StyleIndex = (UInt32Value)4U, DataType = CellValues.SharedString };
            CellValue cellValue2 = new CellValue();
            cellValue2.Text = "1";

            cell2.Append(cellValue2);
            Cell cell3 = new Cell() { CellReference = "B2", StyleIndex = (UInt32Value)4U };
            Cell cell4 = new Cell() { CellReference = "C2", StyleIndex = (UInt32Value)4U };
            Cell cell5 = new Cell() { CellReference = "D2", StyleIndex = (UInt32Value)4U };
            Cell cell6 = new Cell() { CellReference = "E2", StyleIndex = (UInt32Value)4U };

            row2.Append(cell2);
            row2.Append(cell3);
            row2.Append(cell4);
            row2.Append(cell5);
            row2.Append(cell6);

            Row row3 = new Row() { RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:5" }, DyDescent = 0.2D };

            Cell cell7 = new Cell() { CellReference = "B4", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
            CellValue cellValue3 = new CellValue();
            cellValue3.Text = "2";

            cell7.Append(cellValue3);

            row3.Append(cell7);

            Row row4 = new Row() { RowIndex = (UInt32Value)6U, Spans = new ListValue<StringValue>() { InnerText = "1:5" }, Height = 60D, DyDescent = 0.2D };

            Cell cell8 = new Cell() { CellReference = "A6", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
            CellValue cellValue4 = new CellValue();
            cellValue4.Text = "3";

            cell8.Append(cellValue4);

            Cell cell9 = new Cell() { CellReference = "B6", StyleIndex = (UInt32Value)3U, DataType = CellValues.SharedString };
            CellValue cellValue5 = new CellValue();
            cellValue5.Text = "4";

            cell9.Append(cellValue5);

            Cell cell10 = new Cell() { CellReference = "C6", StyleIndex = (UInt32Value)3U, DataType = CellValues.SharedString };
            CellValue cellValue6 = new CellValue();
            cellValue6.Text = "5";

            cell10.Append(cellValue6);

            Cell cell11 = new Cell() { CellReference = "D6", StyleIndex = (UInt32Value)3U, DataType = CellValues.SharedString };
            CellValue cellValue7 = new CellValue();
            cellValue7.Text = "6";

            cell11.Append(cellValue7);

//.........这里部分代码省略.........

        // Generates content of worksheetPart1.
        protected override void GenerateWorksheetPartContent(WorksheetPart worksheetPart)
        {
            Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
            SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1:H31" };

            SheetViews sheetViews1 = new SheetViews();

            SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
            Selection selection1 = new Selection() { ActiveCell = "A23", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A23:XFD24" } };

            sheetView1.Append(selection1);

            sheetViews1.Append(sheetView1);
            SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 12D, DyDescent = 0.2D };

            Columns columns1 = new Columns();
            Column column1 = new Column() { Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 35.42578125D, Style = (UInt32Value)1U, CustomWidth = true };
            Column column2 = new Column() { Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 5.7109375D, Style = (UInt32Value)1U, CustomWidth = true };
            Column column3 = new Column() { Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 5.28515625D, Style = (UInt32Value)1U, BestFit = true, CustomWidth = true };
            Column column4 = new Column() { Min = (UInt32Value)4U, Max = (UInt32Value)4U, Width = 6.140625D, Style = (UInt32Value)1U, BestFit = true, CustomWidth = true };
            Column column5 = new Column() { Min = (UInt32Value)5U, Max = (UInt32Value)5U, Width = 10.42578125D, Style = (UInt32Value)1U, BestFit = true, CustomWidth = true };
            Column column6 = new Column() { Min = (UInt32Value)6U, Max = (UInt32Value)6U, Width = 4.85546875D, Style = (UInt32Value)1U, BestFit = true, CustomWidth = true };
            Column column7 = new Column() { Min = (UInt32Value)7U, Max = (UInt32Value)7U, Width = 6.140625D, Style = (UInt32Value)1U, BestFit = true, CustomWidth = true };
            Column column8 = new Column() { Min = (UInt32Value)8U, Max = (UInt32Value)8U, Width = 11.85546875D, Style = (UInt32Value)1U, CustomWidth = true };
            Column column9 = new Column() { Min = (UInt32Value)9U, Max = (UInt32Value)16384U, Width = 9.140625D, Style = (UInt32Value)1U };

            columns1.Append(column1);
            columns1.Append(column2);
            columns1.Append(column3);
            columns1.Append(column4);
            columns1.Append(column5);
            columns1.Append(column6);
            columns1.Append(column7);
            columns1.Append(column8);
            columns1.Append(column9);

            SheetData sheetData1 = new SheetData();

            Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:8" }, DyDescent = 0.2D };

            Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
            CellValue cellValue1 = new CellValue();
            cellValue1.Text = "0";

            cell1.Append(cellValue1);

            Cell cell2 = new Cell() { CellReference = "B1", StyleIndex = (UInt32Value)7U, DataType = CellValues.SharedString };
            CellValue cellValue2 = new CellValue();
            cellValue2.Text = "1";

            cell2.Append(cellValue2);
            Cell cell3 = new Cell() { CellReference = "C1", StyleIndex = (UInt32Value)8U };
            Cell cell4 = new Cell() { CellReference = "D1", StyleIndex = (UInt32Value)8U };
            Cell cell5 = new Cell() { CellReference = "E1", StyleIndex = (UInt32Value)8U };
            Cell cell6 = new Cell() { CellReference = "F1", StyleIndex = (UInt32Value)9U };

            Cell cell7 = new Cell() { CellReference = "G1", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
            CellValue cellValue3 = new CellValue();
            cellValue3.Text = "2";

            cell7.Append(cellValue3);
            Cell cell8 = new Cell() { CellReference = "H1", StyleIndex = (UInt32Value)6U };

            row1.Append(cell1);
            row1.Append(cell2);
            row1.Append(cell3);
            row1.Append(cell4);
            row1.Append(cell5);
            row1.Append(cell6);
            row1.Append(cell7);
            row1.Append(cell8);

            Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:8" }, DyDescent = 0.2D };
            Cell cell9 = new Cell() { CellReference = "A2", StyleIndex = (UInt32Value)6U };
            Cell cell10 = new Cell() { CellReference = "B2", StyleIndex = (UInt32Value)10U };
            Cell cell11 = new Cell() { CellReference = "C2", StyleIndex = (UInt32Value)11U };
            Cell cell12 = new Cell() { CellReference = "D2", StyleIndex = (UInt32Value)11U };
            Cell cell13 = new Cell() { CellReference = "E2", StyleIndex = (UInt32Value)11U };
            Cell cell14 = new Cell() { CellReference = "F2", StyleIndex = (UInt32Value)12U };

            Cell cell15 = new Cell() { CellReference = "G2", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
            CellValue cellValue4 = new CellValue();
            cellValue4.Text = "3";

            cell15.Append(cellValue4);
            Cell cell16 = new Cell() { CellReference = "H2", StyleIndex = (UInt32Value)6U };

            row2.Append(cell9);
            row2.Append(cell10);
            row2.Append(cell11);
            row2.Append(cell12);
            row2.Append(cell13);
            row2.Append(cell14);
            row2.Append(cell15);
            row2.Append(cell16);

//.........这里部分代码省略.........

        // Generates content of worksheetPart1.
        private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
        {
            Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
            SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1" };

            SheetViews sheetViews1 = new SheetViews();

            SheetView sheetView1 = new SheetView() { WorkbookViewId = (UInt32Value)0U };
            Selection selection1 = new Selection() { ActiveCell = "O9", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "O9" } };

            sheetView1.Append(selection1);

            sheetViews1.Append(sheetView1);
            SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

            SheetData sheetData1 = new SheetData();

            Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" }, DyDescent = 0.25D };

            Cell cell1 = new Cell() { CellReference = "A1", DataType = CellValues.SharedString };
            CellValue cellValue1 = new CellValue();
            cellValue1.Text = "2";

            cell1.Append(cellValue1);

            row1.Append(cell1);

            sheetData1.Append(row1);
            PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
            S.Drawing drawing1 = new S.Drawing() { Id = "rId1" };

            worksheet1.Append(sheetDimension1);
            worksheet1.Append(sheetViews1);
            worksheet1.Append(sheetFormatProperties1);
            worksheet1.Append(sheetData1);
            worksheet1.Append(pageMargins1);
            worksheet1.Append(drawing1);

            worksheetPart1.Worksheet = worksheet1;
        }

        public bool ImportExcel(string strSiteid, string strSupplierID, string strSupplyType, string strNotificationId, string strNotification, string CategoryID, string Category, string strReadingInterval)
        {
            if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx")))
            {
                System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx"));
            }

            string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "NetworkId", "Network"
            , "PropertyId","Property Address","SupplyTypeId","Supply Type","ReadFrequencyId","Read Frequency","MeterCategoryId","Meter Category","Reading Interval",
            "Start Date","End Date","Meter Serial","Device ID","Meter Start Date","Meter End Date","Opening Read","Offset Value",
            "Warrenty Date"};

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
            {
                SheetData sheetData = new SheetData();
                Row row = new Row();
                Row titleRow = new Row { RowIndex = (UInt32)1 };

                //-------------------------Field Type----------------------------------
                titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
                titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
                titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
                titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
                titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
                titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
                titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
                titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
                titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
                titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
                titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
                titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
                titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
                titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
                titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
                titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
                titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
                titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
                titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
                titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
                titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
                titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));

                // Append Row to SheetData
                sheetData.AppendChild(titleRow);

                DataTable dt = GetRecordFromSiteMaster(strSiteid, strSupplierID, strSupplyType, strNotificationId, strNotification, CategoryID, Category, strReadingInterval);

                if (dt != null)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        Row Subrow = new Row();
                        Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["ClientId"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Client"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["NetworkId"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["Network"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["PropertyId"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["Property Address"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("G", Convert.ToString(dt.Rows[i]["SupplyTypeId"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("H", Convert.ToString(dt.Rows[i]["Supply Type"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("I", Convert.ToString(dt.Rows[i]["ReadFrequencyId"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("J", Convert.ToString(dt.Rows[i]["Read Frequency"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("K", Convert.ToString(dt.Rows[i]["MeterCategoryId"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("L", Convert.ToString(dt.Rows[i]["Meter Category"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("M", Convert.ToString(dt.Rows[i]["Reading Interval"]), i + 2));

                        // Append Row to SheetData
                        sheetData.AppendChild(Subrow);
                    }
                }

                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                Worksheet sheet1 = new Worksheet();

                //-------------------Column Size---------------------
                Columns columns = new Columns();
                uint ival = 1;

                foreach (string value in ListOfExportFieldName)
                {

                    columns.Append(CreateColumnSize(1, ival, 28));
                    ival++;
                }
                sheet1.Append(columns);
                //-------------------Column Size---------------------

                sheet1.Append(sheetData);

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = sheet1;

                // Add Sheets to the Workbook.
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

                // Append a new worksheet and associate it with the workbook.
//.........这里部分代码省略.........

        /// <summary>
        /// Generate an excel file with the information of certificates
        /// </summary>
        /// <param name="dataSource">The list of certificates</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream GenerateCertificateReport(CertificateListModel model, string logoPath)
        {
            MemoryStream ms = new MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                //create the new workbook
                WorkbookPart workbookPart = document.AddWorkbookPart();
                Workbook workbook = new Workbook();
                workbookPart.Workbook = workbook;

                //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
                WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");

                //get and save the stylesheet
                Stylesheet stylesheet = VocStyleSheet();
                workbookStylesPart.Stylesheet = stylesheet;
                workbookStylesPart.Stylesheet.Save();

                //add the new workseet
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                Worksheet worksheet = new Worksheet();
                SheetData sheetData1 = new SheetData();

                Sheets sheets = new Sheets();

                //get the number of columns in the report
                Row rowTitle;

                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[4];
                for (int n = 0; n < 4; n++)
                    excelColumnNamesTitle[n] = GetExcelColumnName(n);

                //build the title
                for (int i = 1; i <= 6; i++)
                {
                    rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
                    for (int cellval = 0; cellval < 4; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    sheetData1.Append(rowTitle);
                }
                List<CertificateDocument> dataSource = model.Certificates.Collection;
                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the title
                UpdateStringCellValue("A2", Resources.Common.CertificateList, currentRowTitle, 5);

                //set min date and max date in header
                Row currentRowDateTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)5);
                string minDate, maxDate;
                //get dates
                if (string.IsNullOrEmpty(model.IssuanceDateFrom) || string.IsNullOrEmpty(model.IssuanceDateTo))
                {
                    minDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Min().ToString("dd/MM/yyyy");
                    maxDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Max().ToString("dd/MM/yyyy");
                }
                else
                {
                    minDate = model.IssuanceDateFrom;
                    maxDate = model.IssuanceDateTo;
                }

                //write both dates
                UpdateStringCellValue("B5", Resources.Common.IssuanceDateFrom + ": "+minDate, currentRowDateTitle, 7);
                UpdateStringCellValue("C5", Resources.Common.IssuanceDateTo + ": " + maxDate, currentRowDateTitle, 7);

                //merge all cells in the title
                MergeCell mergeCell = new MergeCell();
                mergeCell.Reference = "A2:D4";
                mergeCells.Append(mergeCell);

                Drawing drawing = AddLogo(logoPath, worksheetPart);

                Columns columns = new Columns();
                columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 32));
                columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 30));
                columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 33));
                columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 45));
                worksheet.Append(columns);

                int rowIndex = 8;

                Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex, Resources.Common.CertificateNumber, rowData, 2);
                AppendTextCell("B" + rowIndex, Resources.Common.IssuanceDate, rowData, 2);
                AppendTextCell("C" + rowIndex, Resources.Common.CertificateStatus, rowData, 2);
                AppendTextCell("D" + rowIndex, Resources.Common.EntryPoint, rowData, 2);
                sheetData1.Append(rowData);

                rowIndex = 9;

//.........这里部分代码省略.........

        /// <summary>
        /// Initialize the worksheet with required details
        /// </summary>
        /// <returns></returns>
        private Worksheet SetWorksheetData()
        {
            Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

            Columns columns = new Columns();
            Column column1 = new Column() { Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 15U, CustomWidth = true };
            Column column2 = new Column() { Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = (Double)ConversionSettings.ColumnWidth, CustomWidth = true };
            Column column3 = new Column() { Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = (Double)ConversionSettings.ColumnWidth, CustomWidth = true };
            Column column4 = new Column() { Min = (UInt32Value)4U, Max = (UInt32Value)4U, Width = (Double)ConversionSettings.ColumnWidth, CustomWidth = true };

            columns.Append(column1);
            columns.Append(column2);
            columns.Append(column3);
            columns.Append(column4);

            SheetData sheetData = new SheetData();

            SheetProtection sheetProtection = new SheetProtection() { Sheet = true, Objects = true, Scenarios = true };

            worksheet.Append(columns);
            worksheet.Append(sheetData);
            worksheet.Append(sheetProtection);
            return worksheet;
        }

        public bool ImportExcel(string strNotificationFrequencyId, string strNotificationFrequency, string NotificationTime, string BillingCurrencyId, string strBillingCurrency, string ContractTypeId, string ContractType, string DepartmentId, string Department)
        {
            if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx")))
            {
                System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx"));
            }

            string[] ListOfExportFieldName = new string[] { "NotificationFrequencyId", "Notification Frequency", "Notification Time", "BillingCurrencyId"
            , "Billing Currency","ContractTypeId","Contract Type","ClientContactDepartmentId","Client Contact Department","Client Name","1st Line of the Address",
            "2nd Line of the Address","City","County","Postcode","Company Number", "VAT Number","Client Bank","Sort Code","Client Bank A/c No","End Date",
            "Client Contact Start Date","Client Contact First Name","Client Contact Last Name","Client Contact 1st Line of the Address","Client Contact 2nd Line of the Address",
            "Client Contact City","Client Contact County","Client Contact Postcode","Client Contact Telephone Number","Client Contact Mobile Number","Client Contact Email Address"};

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
            {
                SheetData sheetData = new SheetData();
                Row row = new Row();
                Row titleRow = new Row { RowIndex = (UInt32)1 };

                //-------------------------Field Type----------------------------------
                titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
                titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
                titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
                titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
                titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
                titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
                titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
                titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
                titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
                titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
                titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
                titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
                titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
                titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
                titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
                titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
                titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
                titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
                titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
                titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
                titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
                titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));
                titleRow.AppendChild(CreateTextCell("W", ListOfExportFieldName[22], 1));
                titleRow.AppendChild(CreateTextCell("X", ListOfExportFieldName[23], 1));
                titleRow.AppendChild(CreateTextCell("Y", ListOfExportFieldName[24], 1));
                titleRow.AppendChild(CreateTextCell("Z", ListOfExportFieldName[25], 1));
                titleRow.AppendChild(CreateTextCell("AA",ListOfExportFieldName[26], 1));
                titleRow.AppendChild(CreateTextCell("AB",ListOfExportFieldName[27], 1));
                titleRow.AppendChild(CreateTextCell("AC",ListOfExportFieldName[28], 1));
                titleRow.AppendChild(CreateTextCell("AD",ListOfExportFieldName[29], 1));
                titleRow.AppendChild(CreateTextCell("AE",ListOfExportFieldName[30], 1));
                titleRow.AppendChild(CreateTextCell("AF",ListOfExportFieldName[31], 1));

                // Append Row to SheetData
                sheetData.AppendChild(titleRow);

                DataTable dt = GetRecordFromSiteMaster(strNotificationFrequencyId, strNotificationFrequency, NotificationTime, BillingCurrencyId, strBillingCurrency, ContractTypeId, ContractType, DepartmentId, Department);

                if (dt != null)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        Row Subrow = new Row();
                        Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["NotificationFrequencyId"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Notification Frequency"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["Notification Time"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["BillingCurrencyId"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["Billing Currency"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["ContractTypeId"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("G", Convert.ToString(dt.Rows[i]["Contract Type"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("H", Convert.ToString(dt.Rows[i]["ClientContactDepartmentId"]), i + 2));
                        Subrow.AppendChild(CreateTextCell("I", Convert.ToString(dt.Rows[i]["Client Contact Department"]), i + 2));

                        // Append Row to SheetData
                        sheetData.AppendChild(Subrow);
                    }
                }

                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                Worksheet sheet1 = new Worksheet();

                //-------------------Column Size---------------------
                Columns columns = new Columns();
                uint ival = 1;

                foreach (string value in ListOfExportFieldName)
                {
                    columns.Append(CreateColumnSize(1, ival, 28));
                    ival++;
                }
                sheet1.Append(columns);
                //-------------------Column Size---------------------

                sheet1.Append(sheetData);

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
//.........这里部分代码省略.........

        // Generates content of worksheetPart2(create test value in Sheet2)
        private void GenerateWorksheetPart2Content(WorksheetPart worksheetPart2)
        {
            Worksheet worksheet2 = new Worksheet();
            SheetDimension sheetDimension2 = new SheetDimension() { Reference = "A1:B4" };

            SheetViews sheetViews2 = new SheetViews();

            SheetView sheetView2 = new SheetView() { WorkbookViewId = (UInt32Value)0U };
            Selection selection1 = new Selection() { ActiveCell = "B4", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B4" } };

            sheetView2.Append(selection1);

            sheetViews2.Append(sheetView2);
            SheetFormatProperties sheetFormatProperties2 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

            SheetData sheetData2 = new SheetData();

            Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:2" }, DyDescent = 0.25D };

            Cell cell1 = new Cell() { CellReference = "A1", DataType = CellValues.SharedString };
            CellValue cellValue1 = new CellValue();
            cellValue1.Text = "0";

            cell1.Append(cellValue1);

            Cell cell2 = new Cell() { CellReference = "B1", DataType = CellValues.SharedString };
            CellValue cellValue2 = new CellValue();
            cellValue2.Text = "1";

            cell2.Append(cellValue2);

            row1.Append(cell1);
            row1.Append(cell2);

            Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:2" }, DyDescent = 0.25D };

            Cell cell3 = new Cell() { CellReference = "A2" };
            CellValue cellValue3 = new CellValue();
            cellValue3.Text = "1";

            cell3.Append(cellValue3);

            Cell cell4 = new Cell() { CellReference = "B2" };
            CellValue cellValue4 = new CellValue();
            cellValue4.Text = "100";

            cell4.Append(cellValue4);

            row2.Append(cell3);
            row2.Append(cell4);

            Row row3 = new Row() { RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:2" }, DyDescent = 0.25D };

            Cell cell5 = new Cell() { CellReference = "A3" };
            CellValue cellValue5 = new CellValue();
            cellValue5.Text = "2";

            cell5.Append(cellValue5);

            Cell cell6 = new Cell() { CellReference = "B3" };
            CellValue cellValue6 = new CellValue();
            cellValue6.Text = "120";

            cell6.Append(cellValue6);

            row3.Append(cell5);
            row3.Append(cell6);

            Row row4 = new Row() { RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:2" }, DyDescent = 0.25D };

            Cell cell7 = new Cell() { CellReference = "A4" };
            CellValue cellValue7 = new CellValue();
            cellValue7.Text = "3";

            cell7.Append(cellValue7);

            Cell cell8 = new Cell() { CellReference = "B4" };
            CellValue cellValue8 = new CellValue();
            cellValue8.Text = "132";

            cell8.Append(cellValue8);

            row4.Append(cell7);
            row4.Append(cell8);

            sheetData2.Append(row1);
            sheetData2.Append(row2);
            sheetData2.Append(row3);
            sheetData2.Append(row4);

            worksheet2.Append(sheetDimension2);
            worksheet2.Append(sheetViews2);
            worksheet2.Append(sheetFormatProperties2);
            worksheet2.Append(sheetData2);

            worksheetPart2.Worksheet = worksheet2;
        }

        //Main class function, export the mutationList to XLSX file, sets file name to testName.
        public static void saveXLS(String testName, List<Mutation> mutationList)
        {
            SpreadsheetDocument xl = null;
            string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
            fullPath += ".xlsx";
            try
            {
                xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
            }
            catch (IOException )
            {
                throw ;
            }
            WorkbookPart wbp = xl.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            Workbook wb = new Workbook();
            FileVersion fv = new FileVersion();
            fv.ApplicationName = "Microsoft Office Excel";
            Worksheet ws = new Worksheet();
            SheetData sd = new SheetData();
            WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
            wbsp.Stylesheet = GenerateStyleSheet();
            wbsp.Stylesheet.Save();

            //save the longest width for each column.
            string[] longestWordPerColumn = new string[12];

            int k = 0;

            //create and add header row.
            Row headerRow = new Row();
            foreach (string s in Mutation.getHeaderForExport())
            {
                Cell cell = new Cell();
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(s);
                cell.StyleIndex = 1;
                headerRow.AppendChild(cell);
                longestWordPerColumn[k] = s;
                k++;
            }
            sd.AppendChild(headerRow);

            //create and add rows for each mutation.
            foreach (Mutation m in mutationList)
            {
                Row newRow = new Row();
                string[] infoString = m.getInfoForExport();
                for (int i = 0; i < infoString.Length; i++)
                {
                    Cell cell1 = new Cell();
                    if (i == 1)
                        cell1.DataType = CellValues.Number;
                    else
                        cell1.DataType = CellValues.String;
                    cell1.CellValue = new CellValue(infoString[i]);
                    if (!m.CosmicName.Equals("-----"))
                        cell1.StyleIndex = 2;
                    else
                        cell1.StyleIndex = 3;
                    newRow.AppendChild(cell1);
                    if (longestWordPerColumn[i].Length < infoString[i].Length)
                        longestWordPerColumn[i] = infoString[i];
                }
                sd.AppendChild(newRow);
            }

            //Sets the column width to longest width for each column.
            Columns columns = new Columns();
            for (int i = 0; i < 12; i++)
            {
                columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
            }
            ws.Append(columns);


            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "Sheet1";
            sheet.SheetId = 1;
            sheet.Id = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);
            wb.Append(fv);
            wb.Append(sheets);
            xl.WorkbookPart.Workbook = wb;
            xl.WorkbookPart.Workbook.Save();
            xl.Close();
        }

        private void toolStripButtonExcel_Click(object sender, EventArgs e)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "XLSX files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
            dlg.FilterIndex = 1;
            dlg.RestoreDirectory = true;
            dlg.Title = "Save Spreadsheet";
            if (dlg.ShowDialog() != DialogResult.OK) {
                return;
            }
            SpreadsheetDocument spreadSheet = null;
            try {
                // Create the spreadsheet.
                spreadSheet = SpreadsheetDocument.Create(dlg.FileName, SpreadsheetDocumentType.Workbook);
                WorkbookPart workbookpart = spreadSheet.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
                Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

                // First worksheet is the phone information.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                Worksheet worksheet = new Worksheet();
                worksheetPart.Worksheet = worksheet;
                UInt32Value sheetId = 1;
                Sheet sheet = new Sheet()
                                  {
                                      Id = spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart),
                                      SheetId = sheetId,
                                      Name = "Phone Information"
                                  };
                sheets.Append(sheet);
                SheetData sheetData = new SheetData();
                AddRow(sheetData, "Saved", DateTime.Now.ToString("f"));
                AddRow(sheetData, "Manufacturer", phoneInfo.Manufacturer);
                AddRow(sheetData, "Model", phoneInfo.Model);
                AddRow(sheetData, "Note", phoneInfo.Note);
                worksheet.Append(sheetData);

                // Next worrksheet is the call logs.
                worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheet = new Worksheet();
                worksheetPart.Worksheet = worksheet;
                sheet = new Sheet()
                            {
                                Id = spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart),
                                SheetId = ++sheetId,
                                Name = "Call Logs"
                            };
                sheets.Append(sheet);
                sheetData = new SheetData();
                AddRow(sheetData, "Number", "Name", "Type", "Timestamp");
                foreach (CallLogListViewItem item in callLogItems) {
                    AddRow(sheetData, item.SubItems[1].Text, item.SubItems[2].Text, item.SubItems[3].Text,
                           item.SubItems[4].Text);
                }
                worksheet.Append(sheetData);

                // Now add the address book worksheet.
                worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheet = new Worksheet();
                worksheetPart.Worksheet = worksheet;
                sheet = new Sheet()
                            {
                                Id = spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart),
                                SheetId = ++sheetId,
                                Name = "Address Book"
                            };
                sheets.Append(sheet);
                sheetData = new SheetData();
                AddRow(sheetData, "Name", "Number");
                foreach (AddressBookListViewItem item in adrBookItems) {
                    AddRow(sheetData, item.SubItems[1].Text, item.SubItems[2].Text);
                }
                worksheet.Append(sheetData);

                // Add text messages.
                worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheet = new Worksheet();
                worksheetPart.Worksheet = worksheet;
                sheet = new Sheet()
                            {
                                Id = spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart),
                                SheetId = ++sheetId,
                                Name = "SMS"
                            };
                sheets.Append(sheet);
                sheetData = new SheetData();
                AddRow(sheetData, "Number 1", "Number 2", "Text", "Timestamp");
                foreach (SmsListViewItem item in smsItems) {
                    AddRow(sheetData, item.SubItems[1].Text, item.SubItems[2].Text, item.SubItems[3].Text,
                           item.SubItems[4].Text);
                }
                worksheet.Append(sheetData);

                // Add images.
                worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheet = new Worksheet();
                worksheetPart.Worksheet = worksheet;
                sheet = new Sheet()
                {
                    Id = spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart),
//.........这里部分代码省略.........