C# Row.Append方法代码示例

        private Row GenerateRowForProperty(UInt32Value rowIndex, int linesToAdd, ProjectFile projectFile, String property)
        {
            Row row = new Row() { RowIndex = (UInt32Value)(rowIndex + linesToAdd), Spans = new ListValue<StringValue>() { InnerText = "3:9" }, DyDescent = 0.25D };
            ProjectProperty projectProperty = projectFile.ProjectProperties.FirstOrDefault(prop => prop.Type == property);
            if (projectProperty != null)
            {
                int sharedStringIndexForType = 0;

                for (int i = 0; i < SharedStrings.Count; i++)
                {
                    if (SharedStrings[i].Text.Text == projectProperty.Type)
                    {
                        sharedStringIndexForType = i;
                        break;
                    }
                }

                List<Cell> cells = new List<Cell>()
                    {
                        new Cell(){CellReference = GetCell(String.Format("C{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 7U,DataType = CellValues.SharedString, CellValue = new CellValue(sharedStringIndexForType.ToString(CultureInfo.InvariantCulture))},
                        new Cell(){CellReference = GetCell(String.Format("D{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 8U,DataType = CellValues.Number, CellValue = new CellValue(projectProperty.Rate.ToString(CultureInfo.InvariantCulture))},
                        new Cell(){CellReference = GetCell(String.Format("F{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 9U,DataType = CellValues.Number, CellValue = new CellValue(projectProperty.Words.ToString(CultureInfo.InvariantCulture))},
                        new Cell(){CellReference = GetCell(String.Format("G{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 9U,DataType = CellValues.Number, CellValue = new CellValue(projectProperty.Characters.ToString(CultureInfo.InvariantCulture))},
                        new Cell(){CellReference = GetCell(String.Format("H{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 9U,DataType = CellValues.Number, CellValue = new CellValue(projectProperty.ValueByWords.ToString(CultureInfo.InvariantCulture))},
                        new Cell(){CellReference = GetCell(String.Format("I{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 9U,DataType = CellValues.SharedString, CellValue = new CellValue("15")}
                    };
                row.Append(cells.ConvertAll(c => (OpenXmlElement) c));
            }
            return row;
        }

        public static Row ToRow(this TimeRecord record, DateTime startTime)
        {
            var idCell = new Cell
            {
                DataType = CellValues.Number,
                CellValue = new CellValue(record.Id.ToString(CultureInfo.InvariantCulture)),
            };

            var timeCell = new Cell
            {
                DataType = CellValues.String,
                CellValue = new CellValue(record.FullTime.ToString(Configuration.TimeFormat)),
            };

            var str = record.FullTime - startTime;
            var deltaCell = new Cell
            {
                DataType = CellValues.String,
                CellValue = new CellValue(str.ToString()),
            };

            var numberCell = new Cell
            {
                DataType = CellValues.Number,
                CellValue = new CellValue(record.Number.ToString(CultureInfo.InvariantCulture)),
            };

            var row = new Row();
            row.Append(idCell, timeCell, numberCell, deltaCell);

            if (record.IsPossiblyWrong)
            {
                var isPossblyWrongCell = new Cell
                {
                    DataType = CellValues.String,
                    CellValue = new CellValue("The number may be wrong!"),
                };

                row.Append(isPossblyWrongCell);
            }

            return row;
        }

        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;
        }

        public static void InsertValuesInWorksheet(WorksheetPart worksheetPart, uint rowIdx, List<string> values)
        {
            var worksheet = worksheetPart.Worksheet;
            var sheetData = worksheet.GetFirstChild<SheetData>();

            Row row = new Row();
            values.ForEach(v =>
            {
                Cell cell = new Cell() { DataType = CellValues.InlineString };
                InlineString inlineString = new InlineString();
                inlineString.Append(new Text() { Text = v });
                cell.Append(inlineString);
                row.Append(cell);
            });
            sheetData.Append(row);
        }

        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;
        }

        private void CreateDataCell(Row row, object obj, int headerIndex, ref int index)
        {
            string header = Utility.IntToAlpha(headerIndex);

            if (obj is string)
            {
                row.Append(new TextCell(header, obj.ToString(), index));
            }
            else if (obj is bool)
            {
                string value = (bool)obj ? "Yes" : "No";
                row.Append(new TextCell(header, value, index));
            }
            else if (obj is DateTime)
            {
                string value = ((DateTime)obj).ToOADate().ToString();
                row.Append(new DateCell(header, (DateTime)obj, index));
            }
            else if (obj is decimal || obj is double)
            {
                row.Append(new FormatedNumberCell(header, obj.ToString(), index));
            }
            else if (obj.GetType().GetInterface("ICollection", true) != null)
            {
                var collection = obj as System.Collections.ICollection;
                if (collection != null)
                    foreach (var item in collection)
                    {
                        CreateDataCell(row, item, headerIndex, ref index);
                        headerIndex++;
                    }
            }
            else
            {
                long value;
                if (long.TryParse(obj.ToString(), out value))
                {
                    row.Append(new NumberCell(header, obj.ToString(), index));
                }
                else
                {
                    row.Append(new TextCell(header, obj.ToString(), index));
                }
            }
        }

 /// <summary>
 /// Append text in a cell
 /// </summary>
 /// <param name="cellReference">Reference</param>
 /// <param name="cellStringValue">Value</param>
 /// <param name="excelRow">Excel row</param>
 /// <param name="styleIndex">Style</param>
 private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow, UInt32Value styleIndex)
 {
     //  Add a new Excel Cell to our Row
     Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
     CellValue cellValue = new CellValue();
     cellValue.Text = cellStringValue;
     cell.StyleIndex = styleIndex;
     cell.Append(cellValue);
     excelRow.Append(cell);
 }

        // Generates content of worksheetPart2.
        private void GenerateWorksheetPart2Content(WorksheetPart worksheetPart2)
        {
            Worksheet worksheet2 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            worksheet2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet2.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
            worksheet2.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
            worksheet2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet2.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
            SheetDimension sheetDimension2 = new SheetDimension() { Reference = "A1:V19" };

            SheetViews sheetViews2 = new SheetViews();

            SheetView sheetView2 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
            Selection selection2 = new Selection() { ActiveCell = "X5", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "X5" } };

            sheetView2.Append(selection2);

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

            SheetData sheetData2 = new SheetData();

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

            Cell cell2 = new Cell() { CellReference = "A1" };
            CellValue cellValue2 = new CellValue();
            cellValue2.Text = "1";

            cell2.Append(cellValue2);

            Cell cell3 = new Cell() { CellReference = "C1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
            CellValue cellValue3 = new CellValue();
            cellValue3.Text = "0";

            cell3.Append(cellValue3);

            row2.Append(cell2);
            row2.Append(cell3);

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

            Cell cell4 = new Cell() { CellReference = "A2" };
            CellValue cellValue4 = new CellValue();
            cellValue4.Text = "2";

            cell4.Append(cellValue4);

            row3.Append(cell4);

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

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

            cell5.Append(cellValue5);

            row4.Append(cell5);

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

            Cell cell6 = new Cell() { CellReference = "A4" };
            CellValue cellValue6 = new CellValue();
            cellValue6.Text = "4";

            cell6.Append(cellValue6);

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

            cell7.Append(cellValue7);

            row5.Append(cell6);
            row5.Append(cell7);

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

            Cell cell8 = new Cell() { CellReference = "A5" };
            CellValue cellValue8 = new CellValue();
            cellValue8.Text = "5";

            cell8.Append(cellValue8);

            Cell cell9 = new Cell() { CellReference = "Q5", StyleIndex = (UInt32Value)3U, DataType = CellValues.SharedString };
            CellValue cellValue9 = new CellValue();
            cellValue9.Text = "6";

            cell9.Append(cellValue9);

            Cell cell10 = new Cell() { CellReference = "T5" };
            CellValue cellValue10 = new CellValue();
            cellValue10.Text = "4";

            cell10.Append(cellValue10);

            row6.Append(cell8);
            row6.Append(cell9);
            row6.Append(cell10);
//.........这里部分代码省略.........

        // Given a Worksheet and a cell name, verifies that the specified cell exists.
        // If it does not exist, creates a new cell.
        private static void CreateSpreadsheetCellIfNotExist(Worksheet worksheet, string cellName)
        {
            string columnName = GetColumnName(cellName);
            uint rowIndex = GetRowIndex(cellName);

            IEnumerable<Row> rows = worksheet.Descendants<Row>().Where(r => r.RowIndex.Value == rowIndex);

            // If the Worksheet does not contain the specified row, create the specified row.
            // Create the specified cell in that row, and insert the row into the Worksheet.
            if (rows.Count() == 0)
            {
                Row row = new Row() { RowIndex = new UInt32Value(rowIndex) };
                Cell cell = new Cell() { CellReference = new StringValue(cellName) };
                row.Append(cell);
                worksheet.Descendants<SheetData>().First().Append(row);
                worksheet.Save();
            }
            else
            {
                Row row = rows.First();

                IEnumerable<Cell> cells = row.Elements<Cell>().Where(c => c.CellReference.Value == cellName);

                // If the row does not contain the specified cell, create the specified cell.
                if (cells.Count() == 0)
                {
                    Cell cell = new Cell() { CellReference = new StringValue(cellName) };
                    row.Append(cell);
                    worksheet.Save();
                }
            }
        }

        // 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 worksheetPart3(create Sheet1)
        private void GenerateWorksheetPart3Content(WorksheetPart worksheetPart3)
        {
            Worksheet worksheet3 = new Worksheet();
            SheetDimension sheetDimension3 = new SheetDimension() { Reference = "A1:B5" };

            SheetViews sheetViews3 = new SheetViews();
            SheetView sheetView3 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };

            sheetViews3.Append(sheetView3);
            SheetFormatProperties sheetFormatProperties3 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

            Columns columns1 = new Columns();
            Column column1 = new Column() { Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 13D, BestFit = true, CustomWidth = true };
            Column column2 = new Column() { Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 12.28515625D, BestFit = true, CustomWidth = true };

            columns1.Append(column1);
            columns1.Append(column2);

            SheetData sheetData3 = new SheetData();

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

            Cell cell9 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
            CellValue cellValue9 = new CellValue();
            cellValue9.Text = "3";

            cell9.Append(cellValue9);

            Cell cell10 = new Cell() { CellReference = "B1", DataType = CellValues.SharedString };
            CellValue cellValue10 = new CellValue();
            cellValue10.Text = "2";

            cell10.Append(cellValue10);

            row5.Append(cell9);
            row5.Append(cell10);

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

            Cell cell11 = new Cell() { CellReference = "A2", StyleIndex = (UInt32Value)3U };
            CellValue cellValue11 = new CellValue();
            cellValue11.Text = "1";

            cell11.Append(cellValue11);

            Cell cell12 = new Cell() { CellReference = "B2", StyleIndex = (UInt32Value)1U };
            CellValue cellValue12 = new CellValue();
            cellValue12.Text = "100";

            cell12.Append(cellValue12);

            row6.Append(cell11);
            row6.Append(cell12);

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

            Cell cell13 = new Cell() { CellReference = "A3", StyleIndex = (UInt32Value)3U };
            CellValue cellValue13 = new CellValue();
            cellValue13.Text = "2";

            cell13.Append(cellValue13);

            Cell cell14 = new Cell() { CellReference = "B3", StyleIndex = (UInt32Value)1U };
            CellValue cellValue14 = new CellValue();
            cellValue14.Text = "120";

            cell14.Append(cellValue14);

            row7.Append(cell13);
            row7.Append(cell14);

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

            Cell cell15 = new Cell() { CellReference = "A4", StyleIndex = (UInt32Value)3U };
            CellValue cellValue15 = new CellValue();
            cellValue15.Text = "3";

            cell15.Append(cellValue15);

            Cell cell16 = new Cell() { CellReference = "B4", StyleIndex = (UInt32Value)1U };
            CellValue cellValue16 = new CellValue();
            cellValue16.Text = "132";

            cell16.Append(cellValue16);

            row8.Append(cell15);
            row8.Append(cell16);

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

            Cell cell17 = new Cell() { CellReference = "A5", StyleIndex = (UInt32Value)3U, DataType = CellValues.SharedString };
            CellValue cellValue17 = new CellValue();
            cellValue17.Text = "4";

            cell17.Append(cellValue17);

            Cell cell18 = new Cell() { CellReference = "B5", StyleIndex = (UInt32Value)1U };
            CellValue cellValue18 = new CellValue();
            cellValue18.Text = "352";
//.........这里部分代码省略.........

        static void Main(string[] args)
        {
            if (args.Count() != 1)
            {
                Console.WriteLine("Usage: {0} <path>", Environment.GetCommandLineArgs()[0]);
                return;
            }

            try
            {
                foreach (string fileName in Directory.GetFiles(args[0], "*.xlsx"))
                {
                    Console.WriteLine("Adding top row to worksheet \"Input\" for Excel file \"{0}\"...", fileName);

                    using (SpreadsheetDocument ssd = SpreadsheetDocument.Open(fileName, true))
                    {
                        WorkbookPart wbp = ssd.WorkbookPart;
                        Sheet sheet = wbp.Workbook.Descendants<Sheet>().Where(s => s.Name == "Input").FirstOrDefault();
                        WorksheetPart wsp = (WorksheetPart)ssd.WorkbookPart.GetPartById(sheet.Id.Value);

                        SheetData sd = wsp.Worksheet.GetFirstChild<SheetData>();
                        Row rr = sd.Descendants<Row>().Where(r => r.RowIndex == 2).FirstOrDefault();
                        Row nr = new Row() { RowIndex = 2 };

                        Cell a2 = new Cell() { CellReference = "A2", DataType = CellValues.InlineString };
                        {
                            InlineString ils = new InlineString();
                            ils.Append(new Text() { Text = "TypeGuessRows" });
                            a2.Append(ils);
                        }
                        Cell b2 = new Cell() { CellReference = "B2", DataType = CellValues.InlineString };
                        {
                            InlineString ils = new InlineString();
                            ils.Append(new Text() { Text = "0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789" });
                            b2.Append(ils);
                        }

                        nr.Append(a2);
                        nr.Append(b2);

                        CalculationChainPart ccp = wbp.CalculationChainPart;
                        if (ccp != null)
                        {
                            CalculationCell cc = ccp.CalculationChain.Descendants<CalculationCell>().Where(c => c.CellReference == "B2").FirstOrDefault();
                            if (cc != null)
                                cc.Remove();

                            if (ccp.CalculationChain.Count() == 0)
                                wbp.DeletePart(ccp);
                        }

                        foreach (Row rw in wsp.Worksheet.Descendants<Row>().Where(r => r.RowIndex.Value >= 2))
                        {
                            uint nri = Convert.ToUInt32(rw.RowIndex.Value + 1);
                            foreach (Cell cl in rw.Elements<Cell>())
                            {
                                string cr = cl.CellReference.Value;
                                cl.CellReference = new StringValue(cr.Replace(rw.RowIndex.Value.ToString(), nri.ToString()));
                            }
                            rw.RowIndex = new UInt32Value(nri);
                        }

                        sd.InsertBefore(nr, rr);

                        wsp.Worksheet.Save();
                    }
                }

                //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                //Excel.Application excel = new Excel.Application();
                //Excel.Workbook workbook = excel.Workbooks.Open(
                //    @"C:\DATA\NS_IMPORT\Network Standards - Managing Risk  - Canada.xlsx",
                //    0,
                //    false,
                //    5,
                //    String.Empty,
                //    String.Empty,
                //    true,
                //    Excel.XlPlatform.xlWindows,
                //    "\t",
                //    true,
                //    false,
                //    0,
                //    false,
                //    true,
                //    false);
                //Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item("Input");

                //worksheet.Rows.Insert(2, 1);
                //worksheet.Rows[2][0].Value = "TYPESELECTOR";
                //worksheet.Rows[2][1].Value = "0123456789";

                //excel.SaveWorkspace(@"C:\DATA\NS_IMPORT\OUTPUT.xlsx");
            }
            catch (Exception ex)
            {
                while (ex != null)
                {
                    Console.WriteLine(ex.Message);
                    ex = ex.InnerException;
//.........这里部分代码省略.........

        private List<Row> AddResourceColumns(SpreadsheetDocument doc, string tabName)
        {
            foreach (var resource in _resources)
            {
                resource.List = resource.List.OrderBy(o => o.Category).ThenBy(o => o.Value).ToList();
            }

            List<Row> rows = new List<Row>();

            var headerRow = new Row();
            for (var i = 0; i < _resources.Count; i++)
            {
                var headerCell = new Cell
                {
                    CellValue = new CellValue(_resources[i].ResourceName),
                    DataType = new EnumValue<CellValues>(CellValues.String),
                    CellReference = (i + 1).ConvertToColumnName() + 1
                };
                headerRow.Append(headerCell);
            }
            rows.Add(headerRow);

            var resourceCount = _resources.Count;
            var maxOfResourceItems = _resources.Max(r => r.List.Count);

            var definedNameDict = new Dictionary<string, List<ResourceIndex>>();

            for (var i = 0; i < maxOfResourceItems; i++)
            {
                var contentRow = new Row();
                for (var j = 0; j < resourceCount; j++)
                {
                    if (i >= _resources[j].List.Count)
                    {
                        continue;
                    }
                    var str = _resources[j].List[i].Value;
                    var category = _resources[j].List[i].Category;
                    var columnName = (j + 1).ConvertToColumnName();
                    var contentCell = new Cell
                    {
                        CellValue = new CellValue(str),
                        //TODO: Currently only support string, will add more types if needed
                        DataType = new EnumValue<CellValues>(CellValues.String),
                        CellReference = columnName + (i + 2)
                    };
                    var key = category;

                    if (!definedNameDict.ContainsKey(key))
                    {
                        definedNameDict.Add(key,
                            new List<ResourceIndex> {new ResourceIndex {Column = columnName, Row = i + 2}});
                    }
                    else
                    {
                        definedNameDict[key].Add(new ResourceIndex {Column = columnName, Row = i + 2});
                    }
                    contentRow.Append(contentCell);
                }
                rows.Add(contentRow);
            }
            AddDefinedNames(doc, definedNameDict, tabName);
            return rows;
        }

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

            SheetProperties sheetProperties1 = new SheetProperties();
            PageSetupProperties pageSetupProperties1 = new PageSetupProperties() { FitToPage = true };

            sheetProperties1.Append(pageSetupProperties1);
            SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1:AL30" };

            SheetViews sheetViews1 = new SheetViews();

            SheetView sheetView1 = new SheetView() { ShowGridLines = false, ShowZeros = false, TabSelected = true, ZoomScale = (UInt32Value)85U, WorkbookViewId = (UInt32Value)0U };
            Selection selection1 = new Selection() { ActiveCell = "AM27", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "AM27" } };

            sheetView1.Append(selection1);

            sheetViews1.Append(sheetView1);
            SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { BaseColumnWidth = (UInt32Value)10U, DefaultRowHeight = 12.75D };

            Columns columns1 = new Columns();
            Column column1 = new Column() { Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 33.42578125D, BestFit = true, CustomWidth = true };
            Column column2 = new Column() { Min = (UInt32Value)3U, Max = (UInt32Value)11U, Width = 2.140625D, BestFit = true, CustomWidth = true };
            Column column3 = new Column() { Min = (UInt32Value)12U, Max = (UInt32Value)31U, Width = 3.140625D, BestFit = true, CustomWidth = true };
            Column column4 = new Column() { Min = (UInt32Value)32U, Max = (UInt32Value)32U, Width = 4.28515625D, CustomWidth = true };
            Column column5 = new Column() { Min = (UInt32Value)33U, Max = (UInt32Value)33U, Width = 3.140625D, BestFit = true, CustomWidth = true };
            Column column6 = new Column() { Min = (UInt32Value)34U, Max = (UInt32Value)34U, Width = 9.28515625D, Style = (UInt32Value)62U, BestFit = true, CustomWidth = true };
            Column column7 = new Column() { Min = (UInt32Value)35U, Max = (UInt32Value)35U, Width = 7.7109375D, Style = (UInt32Value)51U, BestFit = true, CustomWidth = true };
            Column column8 = new Column() { Min = (UInt32Value)36U, Max = (UInt32Value)36U, Width = 9.28515625D, Style = (UInt32Value)62U, BestFit = true, CustomWidth = true };
            Column column9 = new Column() { Min = (UInt32Value)37U, Max = (UInt32Value)37U, Width = 11.28515625D, Style = (UInt32Value)41U, BestFit = true, CustomWidth = true };

            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:38" } };
            Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)10U };
            Cell cell2 = new Cell() { CellReference = "B1", StyleIndex = (UInt32Value)11U };
            Cell cell3 = new Cell() { CellReference = "C1", StyleIndex = (UInt32Value)11U };
            Cell cell4 = new Cell() { CellReference = "D1", StyleIndex = (UInt32Value)11U };
            Cell cell5 = new Cell() { CellReference = "E1", StyleIndex = (UInt32Value)11U };
            Cell cell6 = new Cell() { CellReference = "F1", StyleIndex = (UInt32Value)11U };
            Cell cell7 = new Cell() { CellReference = "G1", StyleIndex = (UInt32Value)11U };
            Cell cell8 = new Cell() { CellReference = "H1", StyleIndex = (UInt32Value)11U };
            Cell cell9 = new Cell() { CellReference = "I1", StyleIndex = (UInt32Value)11U };
            Cell cell10 = new Cell() { CellReference = "J1", StyleIndex = (UInt32Value)11U };
            Cell cell11 = new Cell() { CellReference = "K1", StyleIndex = (UInt32Value)11U };
            Cell cell12 = new Cell() { CellReference = "L1", StyleIndex = (UInt32Value)11U };
            Cell cell13 = new Cell() { CellReference = "M1", StyleIndex = (UInt32Value)11U };
            Cell cell14 = new Cell() { CellReference = "N1", StyleIndex = (UInt32Value)11U };
            Cell cell15 = new Cell() { CellReference = "O1", StyleIndex = (UInt32Value)11U };
            Cell cell16 = new Cell() { CellReference = "P1", StyleIndex = (UInt32Value)11U };
            Cell cell17 = new Cell() { CellReference = "Q1", StyleIndex = (UInt32Value)11U };
            Cell cell18 = new Cell() { CellReference = "R1", StyleIndex = (UInt32Value)11U };
            Cell cell19 = new Cell() { CellReference = "S1", StyleIndex = (UInt32Value)11U };
            Cell cell20 = new Cell() { CellReference = "T1", StyleIndex = (UInt32Value)11U };
            Cell cell21 = new Cell() { CellReference = "U1", StyleIndex = (UInt32Value)11U };
            Cell cell22 = new Cell() { CellReference = "V1", StyleIndex = (UInt32Value)11U };
            Cell cell23 = new Cell() { CellReference = "W1", StyleIndex = (UInt32Value)11U };
            Cell cell24 = new Cell() { CellReference = "X1", StyleIndex = (UInt32Value)11U };
            Cell cell25 = new Cell() { CellReference = "Y1", StyleIndex = (UInt32Value)11U };
            Cell cell26 = new Cell() { CellReference = "Z1", StyleIndex = (UInt32Value)11U };
            Cell cell27 = new Cell() { CellReference = "AA1", StyleIndex = (UInt32Value)11U };
            Cell cell28 = new Cell() { CellReference = "AB1", StyleIndex = (UInt32Value)11U };
            Cell cell29 = new Cell() { CellReference = "AC1", StyleIndex = (UInt32Value)11U };
            Cell cell30 = new Cell() { CellReference = "AD1", StyleIndex = (UInt32Value)11U };
            Cell cell31 = new Cell() { CellReference = "AE1", StyleIndex = (UInt32Value)11U };
            Cell cell32 = new Cell() { CellReference = "AF1", StyleIndex = (UInt32Value)11U };
            Cell cell33 = new Cell() { CellReference = "AG1", StyleIndex = (UInt32Value)11U };
            Cell cell34 = new Cell() { CellReference = "AH1", StyleIndex = (UInt32Value)52U };
            Cell cell35 = new Cell() { CellReference = "AI1", StyleIndex = (UInt32Value)42U };
            Cell cell36 = new Cell() { CellReference = "AJ1", StyleIndex = (UInt32Value)52U };
            Cell cell37 = new Cell() { CellReference = "AK1", StyleIndex = (UInt32Value)38U };

            row1.Append(cell1);
            row1.Append(cell2);
            row1.Append(cell3);
            row1.Append(cell4);
            row1.Append(cell5);
            row1.Append(cell6);
            row1.Append(cell7);
            row1.Append(cell8);
            row1.Append(cell9);
            row1.Append(cell10);
            row1.Append(cell11);
            row1.Append(cell12);
            row1.Append(cell13);
            row1.Append(cell14);
            row1.Append(cell15);
            row1.Append(cell16);
//.........这里部分代码省略.........