private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart,
            SaveContext context)
        {
            // Call all table headers to make sure their names are filled
            var x = 0;
            Worksheets.ForEach(w => w.Tables.ForEach(t => x = (t as XLTable).FieldNames.Count));

            sharedStringTablePart.SharedStringTable = new SharedStringTable {Count = 0, UniqueCount = 0};

            var stringId = 0;

            var newStrings = new Dictionary<String, Int32>();
            var newRichStrings = new Dictionary<IXLRichText, Int32>();
            foreach (
                var c in
                    Worksheets.Cast<XLWorksheet>().SelectMany(
                        w =>
                            w.Internals.CellsCollection.GetCells(
                                c => ((c.DataType == XLCellValues.Text && c.ShareString) || c.HasRichText)
                                     && (c as XLCell).InnerText.Length > 0
                                     && XLHelper.IsNullOrWhiteSpace(c.FormulaA1)
                                )))
            {
                c.DataType = XLCellValues.Text;
                if (c.HasRichText)
                {
                    if (newRichStrings.ContainsKey(c.RichText))
                        c.SharedStringId = newRichStrings[c.RichText];
                    else
                    {
                        var sharedStringItem = new SharedStringItem();
                        foreach (var rt in c.RichText.Where(r => !String.IsNullOrEmpty(r.Text)))
                        {
                            sharedStringItem.Append(GetRun(rt));
                        }

                        if (c.RichText.HasPhonetics)
                        {
                            foreach (var p in c.RichText.Phonetics)
                            {
                                var phoneticRun = new PhoneticRun
                                {
                                    BaseTextStartIndex = (UInt32)p.Start,
                                    EndingBaseIndex = (UInt32)p.End
                                };

                                var text = new Text {Text = p.Text};
                                if (p.Text.PreserveSpaces())
                                    text.Space = SpaceProcessingModeValues.Preserve;

                                phoneticRun.Append(text);
                                sharedStringItem.Append(phoneticRun);
                            }
                            var f = new XLFont(null, c.RichText.Phonetics);
                            if (!context.SharedFonts.ContainsKey(f))
                                context.SharedFonts.Add(f, new FontInfo {Font = f});

                            var phoneticProperties = new PhoneticProperties
                            {
                                FontId =
                                    context.SharedFonts[
                                        new XLFont(null, c.RichText.Phonetics)].
                                        FontId
                            };
                            if (c.RichText.Phonetics.Alignment != XLPhoneticAlignment.Left)
                                phoneticProperties.Alignment = c.RichText.Phonetics.Alignment.ToOpenXml();
                            if (c.RichText.Phonetics.Type != XLPhoneticType.FullWidthKatakana)
                                phoneticProperties.Type = c.RichText.Phonetics.Type.ToOpenXml();

                            sharedStringItem.Append(phoneticProperties);
                        }

                        sharedStringTablePart.SharedStringTable.Append(sharedStringItem);
                        sharedStringTablePart.SharedStringTable.Count += 1;
                        sharedStringTablePart.SharedStringTable.UniqueCount += 1;

                        newRichStrings.Add(c.RichText, stringId);
                        c.SharedStringId = stringId;

                        stringId++;
                    }
                }
                else
                {
                    if (newStrings.ContainsKey(c.Value.ToString()))
                        c.SharedStringId = newStrings[c.Value.ToString()];
                    else
                    {
                        var s = c.Value.ToString();
                        var sharedStringItem = new SharedStringItem();
                        var text = new Text {Text = s};
                        if (!s.Trim().Equals(s))
                            text.Space = SpaceProcessingModeValues.Preserve;
                        sharedStringItem.Append(text);
                        sharedStringTablePart.SharedStringTable.Append(sharedStringItem);
                        sharedStringTablePart.SharedStringTable.Count += 1;
                        sharedStringTablePart.SharedStringTable.UniqueCount += 1;

                        newStrings.Add(c.Value.ToString(), stringId);
                        c.SharedStringId = stringId;

                        stringId++;
                    }
                }
            }
        }
示例#2
1
        public ExcelHelper Open(string ExcelFile,bool IsEditable=false)
        {
            _expectSheets = new List<string>();
            OpenSettings os = new OpenSettings();
            this._excelFilePath = ExcelFile;
            _doc = SpreadsheetDocument.Open(_excelFilePath, IsEditable);
            _wbPart = _doc.WorkbookPart;
            _shareStringPart = _wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

            shareStringDic = new Dictionary<int, string>();

            int j = 0;

            foreach (var item in _shareStringPart.SharedStringTable.Elements<SharedStringItem>()) {
                shareStringDic.Add(j, item.InnerText);
                j++;
            }

            return _instance;
        }
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }
示例#4
0
 public void Close()
 {
     if (_doc != null)
     {
         _doc.Close();
         _expectSheets = null;
         _wbPart = null;
         _shareStringPart = null;
     }
 }
 public static String GetValue(Cell cell, SharedStringTablePart stringTablePart)
 {
     if (cell.ChildElements.Count == 0)
         return null;
     //get cell value
     String value = cell.CellValue.InnerText;
     //Look up real value from shared string table
     if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
         value = stringTablePart.SharedStringTable
         .ChildElements[Int32.Parse(value)]
         .InnerText;
     return value;
 }
示例#6
0
 //We use sharedstring becasue is more efficient. With same strings it use a ref and does not copy the value everytime
 internal static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
 {
     if (shareStringPart.SharedStringTable == null)
     shareStringPart.SharedStringTable = new SharedStringTable();
       var i = 0;
       foreach (var item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
       {
     if (item.InnerText == text)
       return i;
     i++;
       }
       shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
       shareStringPart.SharedStringTable.Save();
       return i;
 }
示例#7
0
        public List<object[]> LerPlanilhaDados(Stream stream)
        {
            List<object[]> dadosPlanilha = new List<object[]>();

            using (_document = SpreadsheetDocument.Open(stream, true))
            {
                try
                {
                    _wbPart = _document.WorkbookPart;
                    _sharedStringPart = GetSharedStringPart();

                    if (SelectFirstSheet())
                    {
                        int indice = 0;

                        foreach (Row row in _activeWorksheet.Descendants<Row>())
                        {
                            indice++;

                            if (indice == 1) continue; // Pula o cabeçalho.

                            List<object> dadosLinha = new List<object>();

                            foreach (Cell celula in row.Descendants<Cell>())
                                dadosLinha.Add(GetCellValue(celula));

                            dadosPlanilha.Add(dadosLinha.ToArray());
                        }
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    _document.Close();
                }

                return dadosPlanilha;
            }
        }
        // Generates content of sharedStringTablePart1.
        private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1)
        {
            SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)5U, UniqueCount = (UInt32Value)5U };

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

            sharedStringItem1.Append(text1);

            SharedStringItem sharedStringItem2 = new SharedStringItem();
            Text text2 = new Text();
            text2.Text = "HeaderNumber";

            sharedStringItem2.Append(text2);

            SharedStringItem sharedStringItem3 = new SharedStringItem();
            Text text3 = new Text();
            text3.Text = "HeaderLink";

            sharedStringItem3.Append(text3);

            SharedStringItem sharedStringItem4 = new SharedStringItem();
            Text text4 = new Text();
            text4.Text = "test";

            sharedStringItem4.Append(text4);

            SharedStringItem sharedStringItem5 = new SharedStringItem();
            Text text5 = new Text();
            text5.Text = "http://google.com";

            sharedStringItem5.Append(text5);

            sharedStringTable1.Append(sharedStringItem1);
            sharedStringTable1.Append(sharedStringItem2);
            sharedStringTable1.Append(sharedStringItem3);
            sharedStringTable1.Append(sharedStringItem4);
            sharedStringTable1.Append(sharedStringItem5);

            sharedStringTablePart1.SharedStringTable = sharedStringTable1;
        }
示例#9
0
        public SharedStringTableIndexer(SharedStringTablePart toIndex)
        {
            // Handle parameter checking in ctor.
            if (toIndex == null)
            {
                throw new ArgumentNullException("stringTablePart");
            }

            // In this case, we'll initialize the shared string table object for the caller.
            if (toIndex.SharedStringTable == null)
            {
                // Side-effect!
                toIndex.SharedStringTable = new SharedStringTable()
                {
                    Count = 0U,
                    UniqueCount = 0U
                };
            }

            this.Initialize(toIndex.SharedStringTable);
        }
示例#10
0
        public OpenXmlExcel(Stream stream)
        {
            spreadSheet = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
            WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();

            WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

            if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
            {
                shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            }
            else
            {
                shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
            }

            shareStringPart.SharedStringTable = new SharedStringTable();
            shareStringPart.SharedStringTable.Count = 1;
            shareStringPart.SharedStringTable.UniqueCount = 1;

            CurrentWorksheetPart = worksheetPart;
        }
示例#11
0
        private static void SetCellValue(SharedStringTablePart shareStringTablePart, Cell cell, string value)
        {


            if (cell == null || cell.CellValue.Text.Equals(string.Empty))
            {
                return;
            }
            else
            {
                if (cell.DataType.Value == CellValues.SharedString)
                {
                    int shareStringId = int.Parse(cell.CellValue.Text);
                    SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);

                    item.Elements<Text>().First().Text = value;

                }
                else
                {
                    cell.CellValue.Text = value;
                }
            }
        }
        // Generates content of sharedStringTablePart1.
        private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1)
        {
            SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)20U, UniqueCount = (UInt32Value)19U };

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

            sharedStringItem1.Append(text1);

            SharedStringItem sharedStringItem2 = new SharedStringItem();
            Text text2 = new Text();
            text2.Text = "Av. Corrientes 6277     ( 1427)   Buenos Aires";

            sharedStringItem2.Append(text2);

            SharedStringItem sharedStringItem3 = new SharedStringItem();
            Text text3 = new Text() { Space = SpaceProcessingModeValues.Preserve };
            text3.Text = "            Argentina - Tel.: 4323-9931";

            sharedStringItem3.Append(text3);

            SharedStringItem sharedStringItem4 = new SharedStringItem();
            Text text4 = new Text() { Space = SpaceProcessingModeValues.Preserve };
            text4.Text = "Medio:  ";

            sharedStringItem4.Append(text4);

            SharedStringItem sharedStringItem5 = new SharedStringItem();
            Text text5 = new Text();
            text5.Text = "Costo total";

            sharedStringItem5.Append(text5);

            SharedStringItem sharedStringItem6 = new SharedStringItem();
            Text text6 = new Text();
            text6.Text = "PRODUCTO:";

            sharedStringItem6.Append(text6);

            SharedStringItem sharedStringItem7 = new SharedStringItem();
            Text text7 = new Text();
            text7.Text = "HORARIO";

            sharedStringItem7.Append(text7);

            SharedStringItem sharedStringItem8 = new SharedStringItem();
            Text text8 = new Text();
            text8.Text = "Cantidad";

            sharedStringItem8.Append(text8);

            SharedStringItem sharedStringItem9 = new SharedStringItem();
            Text text9 = new Text();
            text9.Text = "Duración";

            sharedStringItem9.Append(text9);

            SharedStringItem sharedStringItem10 = new SharedStringItem();
            Text text10 = new Text();
            text10.Text = "Costo";

            sharedStringItem10.Append(text10);

            SharedStringItem sharedStringItem11 = new SharedStringItem();
            Text text11 = new Text();
            text11.Text = "Salidas";

            sharedStringItem11.Append(text11);

            SharedStringItem sharedStringItem12 = new SharedStringItem();
            Text text12 = new Text();
            text12.Text = "Total";

            sharedStringItem12.Append(text12);

            SharedStringItem sharedStringItem13 = new SharedStringItem();
            Text text13 = new Text();
            text13.Text = "Orden de Publicidad:";

            sharedStringItem13.Append(text13);

            SharedStringItem sharedStringItem14 = new SharedStringItem();
            Text text14 = new Text();
            text14.Text = "Programa:";

            sharedStringItem14.Append(text14);

            SharedStringItem sharedStringItem15 = new SharedStringItem();
            Text text15 = new Text();
            text15.Text = "Fecha de Emisión:";

            sharedStringItem15.Append(text15);

            SharedStringItem sharedStringItem16 = new SharedStringItem();
            Text text16 = new Text();
            text16.Text = "Contacto:";

            sharedStringItem16.Append(text16);

            SharedStringItem sharedStringItem17 = new SharedStringItem();
            Text text17 = new Text();
            text17.Text = "Tel/Fax:";

            sharedStringItem17.Append(text17);

            SharedStringItem sharedStringItem18 = new SharedStringItem();
            Text text18 = new Text();
            text18.Text = "Dirección:";

            sharedStringItem18.Append(text18);

            SharedStringItem sharedStringItem19 = new SharedStringItem();
            Text text19 = new Text();
            text19.Text = "Email:";

            sharedStringItem19.Append(text19);

            sharedStringTable1.Append(sharedStringItem1);
            sharedStringTable1.Append(sharedStringItem2);
            sharedStringTable1.Append(sharedStringItem3);
            sharedStringTable1.Append(sharedStringItem4);
            sharedStringTable1.Append(sharedStringItem5);
            sharedStringTable1.Append(sharedStringItem6);
            sharedStringTable1.Append(sharedStringItem7);
            sharedStringTable1.Append(sharedStringItem8);
            sharedStringTable1.Append(sharedStringItem9);
            sharedStringTable1.Append(sharedStringItem10);
            sharedStringTable1.Append(sharedStringItem11);
            sharedStringTable1.Append(sharedStringItem12);
            sharedStringTable1.Append(sharedStringItem13);
            sharedStringTable1.Append(sharedStringItem14);
            sharedStringTable1.Append(sharedStringItem15);
            sharedStringTable1.Append(sharedStringItem16);
            sharedStringTable1.Append(sharedStringItem17);
            sharedStringTable1.Append(sharedStringItem18);
            sharedStringTable1.Append(sharedStringItem19);

            sharedStringTablePart1.SharedStringTable = sharedStringTable1;
        }
        // Generates content of sharedStringTablePart1.
        protected override void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart)
        {
            SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)29U, UniqueCount = (UInt32Value)28U };

            SharedStringItem sharedStringItem1 = new SharedStringItem();
            Text text1 = new Text();
            text1.Text = "АКТ №" + sampleSelectionRequest.ActNumber;

            sharedStringItem1.Append(text1);

            SharedStringItem sharedStringItem2 = new SharedStringItem();
            Text text2 = new Text();
            text2.Text = string.Format("Выполненных работ между {0} и {1}", sampleSelectionRequest.Executor.Name, sampleSelectionRequest.Client.Name);

            sharedStringItem2.Append(text2);

            SharedStringItem sharedStringItem3 = new SharedStringItem();
            Text text3 = new Text();
            text3.Text = string.Format("Срок выполнения работ: _______{0}г", DateTime.Now.Year);

            sharedStringItem3.Append(text3);

            SharedStringItem sharedStringItem4 = new SharedStringItem();
            Text text4 = new Text();
            text4.Text = "Наименование выполненных работ (оказанных услуг)";

            sharedStringItem4.Append(text4);

            SharedStringItem sharedStringItem5 = new SharedStringItem();
            Text text5 = new Text();
            text5.Text = "Стоимость работ (услуг) всего без НДС, руб";

            sharedStringItem5.Append(text5);

            SharedStringItem sharedStringItem6 = new SharedStringItem();
            Text text6 = new Text();
            text6.Text = "Ставка НДС, %";

            sharedStringItem6.Append(text6);

            SharedStringItem sharedStringItem7 = new SharedStringItem();
            Text text7 = new Text();
            text7.Text = "Сумма НДС, руб";

            sharedStringItem7.Append(text7);

            SharedStringItem sharedStringItem8 = new SharedStringItem();
            Text text8 = new Text();
            text8.Text = "Стоимость работ всего с учётом НДС, руб";

            sharedStringItem8.Append(text8);

            SharedStringItem sharedStringItem9 = new SharedStringItem();
            Text text9 = new Text();
            text9.Text = "Полный анализ семян";

            sharedStringItem9.Append(text9);

            SharedStringItem sharedStringItem10 = new SharedStringItem();
            Text text10 = new Text();
            text10.Text = "Анализ семян на чистоту";

            sharedStringItem10.Append(text10);

            SharedStringItem sharedStringItem11 = new SharedStringItem();
            Text text11 = new Text();
            text11.Text = "Анализ семян на влажность";

            sharedStringItem11.Append(text11);

            SharedStringItem sharedStringItem12 = new SharedStringItem();
            Text text12 = new Text();
            text12.Text = "Анализ семян на заселенность вредителями";

            sharedStringItem12.Append(text12);

            SharedStringItem sharedStringItem13 = new SharedStringItem();
            Text text13 = new Text();
            text13.Text = "Анализ семян на всхожесть";

            sharedStringItem13.Append(text13);

            SharedStringItem sharedStringItem14 = new SharedStringItem();
            Text text14 = new Text();
            text14.Text = "Анализ семян на определение массы 1000 семян";

            sharedStringItem14.Append(text14);

            SharedStringItem sharedStringItem15 = new SharedStringItem();
            Text text15 = new Text();
            text15.Text = "Анализ семян на жизнеспособность";

            sharedStringItem15.Append(text15);

            SharedStringItem sharedStringItem16 = new SharedStringItem();
            Text text16 = new Text();
            text16.Text = "Анализ (для картофеля, лука и т.д.)";

            sharedStringItem16.Append(text16);

            SharedStringItem sharedStringItem17 = new SharedStringItem();
            Text text17 = new Text();
            text17.Text = "Фитоанализ";

            sharedStringItem17.Append(text17);

            SharedStringItem sharedStringItem18 = new SharedStringItem();
            Text text18 = new Text();
            text18.Text = "Определение алкалоидности семян люпина";

            sharedStringItem18.Append(text18);

            SharedStringItem sharedStringItem19 = new SharedStringItem();
            Text text19 = new Text();
            text19.Text = "Итого:";

            sharedStringItem19.Append(text19);

            SharedStringItem sharedStringItem20 = new SharedStringItem();
            Text text20 = new Text();
            text20.Text = "Сумма НДС:";

            sharedStringItem20.Append(text20);

            SharedStringItem sharedStringItem21 = new SharedStringItem();
            Text text21 = new Text();
            text21.Text = "Всего с учётом НДС:";

            sharedStringItem21.Append(text21);

            SharedStringItem sharedStringItem22 = new SharedStringItem();
            Text text22 = new Text();
            text22.Text = "Исполнитель:";

            sharedStringItem22.Append(text22);

            SharedStringItem sharedStringItem23 = new SharedStringItem();
            Text text23 = new Text();
            text23.Text = "Заказчик:";

            sharedStringItem23.Append(text23);

            SharedStringItem sharedStringItem24 = new SharedStringItem();
            Text text24 = new Text();
            text24.Text = sampleSelectionRequest.Executor.Name;

            sharedStringItem24.Append(text24);

            SharedStringItem sharedStringItem25 = new SharedStringItem();
            Text text25 = new Text();
            text25.Text = sampleSelectionRequest.Client.Name;

            sharedStringItem25.Append(text25);

            SharedStringItem sharedStringItem26 = new SharedStringItem();
            Text text26 = new Text();
            text26.Text = sampleSelectionRequest.Executor.AccountNumber;

            sharedStringItem26.Append(text26);

            SharedStringItem sharedStringItem27 = new SharedStringItem();
            Text text27 = new Text();
            text27.Text = sampleSelectionRequest.Client.AccountNumber;

            sharedStringItem27.Append(text27);

            SharedStringItem sharedStringItem28 = new SharedStringItem();
            Text text28 = new Text();
            text28.Text = "подпись";

            sharedStringItem28.Append(text28);

            sharedStringTable1.Append(sharedStringItem1);
            sharedStringTable1.Append(sharedStringItem2);
            sharedStringTable1.Append(sharedStringItem3);
            sharedStringTable1.Append(sharedStringItem4);
            sharedStringTable1.Append(sharedStringItem5);
            sharedStringTable1.Append(sharedStringItem6);
            sharedStringTable1.Append(sharedStringItem7);
            sharedStringTable1.Append(sharedStringItem8);
            sharedStringTable1.Append(sharedStringItem9);
            sharedStringTable1.Append(sharedStringItem10);
            sharedStringTable1.Append(sharedStringItem11);
            sharedStringTable1.Append(sharedStringItem12);
            sharedStringTable1.Append(sharedStringItem13);
            sharedStringTable1.Append(sharedStringItem14);
            sharedStringTable1.Append(sharedStringItem15);
            sharedStringTable1.Append(sharedStringItem16);
            sharedStringTable1.Append(sharedStringItem17);
            sharedStringTable1.Append(sharedStringItem18);
            sharedStringTable1.Append(sharedStringItem19);
            sharedStringTable1.Append(sharedStringItem20);
            sharedStringTable1.Append(sharedStringItem21);
            sharedStringTable1.Append(sharedStringItem22);
            sharedStringTable1.Append(sharedStringItem23);
            sharedStringTable1.Append(sharedStringItem24);
            sharedStringTable1.Append(sharedStringItem25);
            sharedStringTable1.Append(sharedStringItem26);
            sharedStringTable1.Append(sharedStringItem27);
            sharedStringTable1.Append(sharedStringItem28);

            sharedStringTablePart.SharedStringTable = sharedStringTable1;
        }
示例#14
0
		private bool MatchSeparatedField(IEnumerable<Cell> tcs, Field fld, FieldCellMap fm, SharedStringTablePart stringTable, CellFormats formats)
		{
			if (fm.Title != null && fld.titles.Select(t => t.ToLower()).Contains(fm.Title))
			{
				fm.field = fld;

				if (fld.DataFormat == DataFormatType.Date || fld.DataFormat == DataFormatType.DateTime || fld.DataFormat == DataFormatType.DateMixed)
				{
					var cell = tcs.Where(c => c.CellReference == fm.cellLoc.ValueRef).FirstOrDefault();
					fm.Value = Spreadsheet.GetCellValue(cell, stringTable.SharedStringTable, formats, fld);
				}

				return true;
			}

			return false;
		}
示例#15
0
        // Generates content of sharedStringTablePart1.
        protected override void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart)
        {
            SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)65U, UniqueCount = (UInt32Value)42U };

            SharedStringItem sharedStringItem1 = new SharedStringItem();
            Text text1 = new Text();
            text1.Text = "Поставщик:";

            sharedStringItem1.Append(text1);

            SharedStringItem sharedStringItem2 = new SharedStringItem();
            Text text2 = new Text();
            text2.Text = sampleSelectionRequest.Executor.ToString();

            sharedStringItem2.Append(text2);

            SharedStringItem sharedStringItem3 = new SharedStringItem();
            Text text3 = new Text();
            text3.Text = "СЧЕТ-ФАКТУРА";

            sharedStringItem3.Append(text3);

            SharedStringItem sharedStringItem4 = new SharedStringItem();
            Text text4 = new Text();
            text4.Text = "№" + sampleSelectionRequest.InvoiceNumber;

            sharedStringItem4.Append(text4);

            SharedStringItem sharedStringItem5 = new SharedStringItem();
            Text text5 = new Text();
            text5.Text = "от " + sampleSelectionRequest.CreationDate.ToLongDateString();

            sharedStringItem5.Append(text5);

            SharedStringItem sharedStringItem6 = new SharedStringItem();
            Text text6 = new Text();
            text6.Text = "предоплата за клубневой анализ";

            sharedStringItem6.Append(text6);

            SharedStringItem sharedStringItem7 = new SharedStringItem();
            Text text7 = new Text();
            text7.Text = "Плательщик:";

            sharedStringItem7.Append(text7);

            SharedStringItem sharedStringItem8 = new SharedStringItem();
            Text text8 = new Text();
            text8.Text = sampleSelectionRequest.Client.ToString();

            sharedStringItem8.Append(text8);

            SharedStringItem sharedStringItem9 = new SharedStringItem();
            Text text9 = new Text();
            text9.Text = "Р/сч №";

            sharedStringItem9.Append(text9);

            var bankRequisitesSubject = sampleSelectionRequest.Executor;

            SharedStringItem sharedStringItem10 = new SharedStringItem();
            Text text10 = new Text();
            text10.Text = bankRequisitesSubject.AccountNumber;

            sharedStringItem10.Append(text10);

            SharedStringItem sharedStringItem11 = new SharedStringItem();
            Text text11 = new Text();
            text11.Text = "Банк";

            sharedStringItem11.Append(text11);

            SharedStringItem sharedStringItem12 = new SharedStringItem();
            Text text12 = new Text();
            text12.Text = bankRequisitesSubject.AccountBank.Name;

            sharedStringItem12.Append(text12);

            SharedStringItem sharedStringItem13 = new SharedStringItem();
            Text text13 = new Text();
            text13.Text = "Код банка";

            sharedStringItem13.Append(text13);

            SharedStringItem sharedStringItem14 = new SharedStringItem();
            Text text14 = new Text();
            text14.Text = bankRequisitesSubject.AccountBank.Code.ToString();

            sharedStringItem14.Append(text14);

            SharedStringItem sharedStringItem15 = new SharedStringItem();
            Text text15 = new Text();
            text15.Text = "УНН";

            sharedStringItem15.Append(text15);

            SharedStringItem sharedStringItem16 = new SharedStringItem();
            Text text16 = new Text();
            text16.Text = string.Format(
                "Дополнение. Основание: прейскурант стоимости услуг, выполняемых испекцией по семеноводству № {0} от {1:D}",
                sampleSelectionRequest.PriceList.Number,
                sampleSelectionRequest.PriceList.BeginDate);

            sharedStringItem16.Append(text16);

            SharedStringItem sharedStringItem17 = new SharedStringItem();
            Text text17 = new Text();
            text17.Text = "наименование";

            sharedStringItem17.Append(text17);

            SharedStringItem sharedStringItem18 = new SharedStringItem();
            Text text18 = new Text();
            text18.Text = "к-во";

            sharedStringItem18.Append(text18);

            SharedStringItem sharedStringItem19 = new SharedStringItem();
            Text text19 = new Text();
            text19.Text = "цена";

            sharedStringItem19.Append(text19);

            SharedStringItem sharedStringItem20 = new SharedStringItem();
            Text text20 = new Text();
            text20.Text = "стоимость";

            sharedStringItem20.Append(text20);

            SharedStringItem sharedStringItem21 = new SharedStringItem();
            Text text21 = new Text();
            text21.Text = "%ндс";

            sharedStringItem21.Append(text21);

            SharedStringItem sharedStringItem22 = new SharedStringItem();
            Text text22 = new Text();
            text22.Text = "Сумма";

            sharedStringItem22.Append(text22);

            SharedStringItem sharedStringItem23 = new SharedStringItem();
            Text text23 = new Text();
            text23.Text = "всего с НДС";

            sharedStringItem23.Append(text23);

            SharedStringItem sharedStringItem24 = new SharedStringItem();
            Text text24 = new Text();
            text24.Text = "Полный анализ семян";

            sharedStringItem24.Append(text24);

            SharedStringItem sharedStringItem25 = new SharedStringItem();
            Text text25 = new Text();
            text25.Text = "54321";

            sharedStringItem25.Append(text25);

            SharedStringItem sharedStringItem26 = new SharedStringItem();
            Text text26 = new Text();
            text26.Text = "Анализ семян на чистоту";

            sharedStringItem26.Append(text26);

            SharedStringItem sharedStringItem27 = new SharedStringItem();
            Text text27 = new Text();
            text27.Text = "Анализ семян на влажность";

            sharedStringItem27.Append(text27);

            SharedStringItem sharedStringItem28 = new SharedStringItem();
            Text text28 = new Text();
            text28.Text = "Анализ семян на заселенность вредителями";

            sharedStringItem28.Append(text28);

            SharedStringItem sharedStringItem29 = new SharedStringItem();
            Text text29 = new Text();
            text29.Text = "Анализ семян на всхожесть";

            sharedStringItem29.Append(text29);

            SharedStringItem sharedStringItem30 = new SharedStringItem();
            Text text30 = new Text();
            text30.Text = "Анализ семян на определение массы 1000 семян";

            sharedStringItem30.Append(text30);

            SharedStringItem sharedStringItem31 = new SharedStringItem();
            Text text31 = new Text();
            text31.Text = "Анализ семян на жизнеспособность";

            sharedStringItem31.Append(text31);

            SharedStringItem sharedStringItem32 = new SharedStringItem();
            Text text32 = new Text();
            text32.Text = "Анализ (для картофеля, лука и т.д.)";

            sharedStringItem32.Append(text32);

            SharedStringItem sharedStringItem33 = new SharedStringItem();
            Text text33 = new Text();
            text33.Text = "Фитоанализ";

            sharedStringItem33.Append(text33);

            SharedStringItem sharedStringItem34 = new SharedStringItem();
            Text text34 = new Text();
            text34.Text = "Определение алкалоидности семян люпина";

            sharedStringItem34.Append(text34);

            SharedStringItem sharedStringItem35 = new SharedStringItem();
            Text text35 = new Text();
            text35.Text = "Выдача удостоверения (уведомления)";

            sharedStringItem35.Append(text35);

            SharedStringItem sharedStringItem36 = new SharedStringItem();
            Text text36 = new Text();
            text36.Text = "Продление срока действия удостоверения";

            sharedStringItem36.Append(text36);

            SharedStringItem sharedStringItem37 = new SharedStringItem();
            Text text37 = new Text();
            text37.Text = "Итого:";

            sharedStringItem37.Append(text37);

            SharedStringItem sharedStringItem38 = new SharedStringItem();
            Text text38 = new Text();
            text38.Text = "Всего сумма НДС:";

            sharedStringItem38.Append(text38);

            SharedStringItem sharedStringItem39 = new SharedStringItem();
            Text text39 = new Text();
            text39.Text = "Всего с учётом НДС:";

            sharedStringItem39.Append(text39);

            SharedStringItem sharedStringItem40 = new SharedStringItem();
            Text text40 = new Text();
            text40.Text = "* Счет-фактура является протоколом согласование цен";

            sharedStringItem40.Append(text40);

            SharedStringItem sharedStringItem41 = new SharedStringItem();
            Text text41 = new Text();
            text41.Text = "проба";

            sharedStringItem41.Append(text41);

            SharedStringItem sharedStringItem42 = new SharedStringItem();
            Text text42 = new Text();
            text42.Text = "ед. изм.";

            sharedStringItem42.Append(text42);

            SharedStringItem sharedStringItem43 = new SharedStringItem();
            Text text43 = new Text();
            text43.Text = sumVat.ToString(SeedsConfiguration.Current.Ui.CurrencyFormat);

            sharedStringItem43.Append(text43);

            SharedStringItem sharedStringItem44 = new SharedStringItem();
            Text text44 = new Text();
            text44.Text = sumFullCost.ToString(SeedsConfiguration.Current.Ui.CurrencyFormat);

            sharedStringItem44.Append(text44);

            sharedStringTable1.Append(sharedStringItem1);
            sharedStringTable1.Append(sharedStringItem2);
            sharedStringTable1.Append(sharedStringItem3);
            sharedStringTable1.Append(sharedStringItem4);
            sharedStringTable1.Append(sharedStringItem5);
            sharedStringTable1.Append(sharedStringItem6);
            sharedStringTable1.Append(sharedStringItem7);
            sharedStringTable1.Append(sharedStringItem8);
            sharedStringTable1.Append(sharedStringItem9);
            sharedStringTable1.Append(sharedStringItem10);
            sharedStringTable1.Append(sharedStringItem11);
            sharedStringTable1.Append(sharedStringItem12);
            sharedStringTable1.Append(sharedStringItem13);
            sharedStringTable1.Append(sharedStringItem14);
            sharedStringTable1.Append(sharedStringItem15);
            sharedStringTable1.Append(sharedStringItem16);
            sharedStringTable1.Append(sharedStringItem17);
            sharedStringTable1.Append(sharedStringItem18);
            sharedStringTable1.Append(sharedStringItem19);
            sharedStringTable1.Append(sharedStringItem20);
            sharedStringTable1.Append(sharedStringItem21);
            sharedStringTable1.Append(sharedStringItem22);
            sharedStringTable1.Append(sharedStringItem23);
            sharedStringTable1.Append(sharedStringItem24);
            sharedStringTable1.Append(sharedStringItem25);
            sharedStringTable1.Append(sharedStringItem26);
            sharedStringTable1.Append(sharedStringItem27);
            sharedStringTable1.Append(sharedStringItem28);
            sharedStringTable1.Append(sharedStringItem29);
            sharedStringTable1.Append(sharedStringItem30);
            sharedStringTable1.Append(sharedStringItem31);
            sharedStringTable1.Append(sharedStringItem32);
            sharedStringTable1.Append(sharedStringItem33);
            sharedStringTable1.Append(sharedStringItem34);
            sharedStringTable1.Append(sharedStringItem35);
            sharedStringTable1.Append(sharedStringItem36);
            sharedStringTable1.Append(sharedStringItem37);
            sharedStringTable1.Append(sharedStringItem38);
            sharedStringTable1.Append(sharedStringItem39);
            sharedStringTable1.Append(sharedStringItem40);
            sharedStringTable1.Append(sharedStringItem41);
            sharedStringTable1.Append(sharedStringItem42);
            sharedStringTable1.Append(sharedStringItem43);
            sharedStringTable1.Append(sharedStringItem44);

            sharedStringTablePart.SharedStringTable = sharedStringTable1;
        }
示例#16
0
		public MatchData MatchCellLayouts(MatchData md, IEnumerable<Cell> tcs, SheetLayout sheetLayout, SharedStringTablePart stringTable, CellFormats formats, FileInfo file)
		{
			// Obtain titles for all field cell layouts
			md.fldCellVersMaps = new List<FieldCellVersionMap>();

			foreach (var fldLayout in sheetLayout.wsLayout.cellLayouts)
			{
				var fldLayoutVals = new List<FieldCellMap>();

				foreach (var cellLoc in fldLayout.cellLocations)
				{
					try
					{
						var cl = tcs.FirstOrDefault(cll => cll.CellReference.InnerText == cellLoc.TitleRef);
						var clVal = tcs.FirstOrDefault(clv => clv.CellReference.InnerText == cellLoc.ValueRef);

						var title = Spreadsheet.GetCellValue(cl, stringTable.SharedStringTable, formats, null);
						if (title != null) title = System.Text.RegularExpressions.Regex.Replace(title, @"\s+", " ").Trim().ToLower();
						var val = Spreadsheet.GetCellValue(clVal, stringTable.SharedStringTable, formats, null);

						fldLayoutVals.Add(new FieldCellMap
						{
							cellLoc = cellLoc,
							Title = string.IsNullOrWhiteSpace(title) ? null : title.Trim().ToLower(),
							Value = string.IsNullOrWhiteSpace(val) ? null : val.Trim()
						});
					}
					catch (Exception ex)
					{
						Log.New.Msg(ex);
					}
				}

				md.fldCellVersMaps.Add(new FieldCellVersionMap { fldmaps = fldLayoutVals, fldLayout = fldLayout });
			}

			md.fldCellVersMaps.ForEach(vm => vm.fldmaps.ForEach(vfm => vfm.versMap = vm));

			var reqFlds = sheetLayout.wsLayout.fields.Where(sf => sf.fldType == FieldType.cell && sf.isRequired);

			// Match Titles to layout fields
			foreach (var flvv in md.fldCellVersMaps)
			{
				var agMaps = new List<FieldCellMap>();

				foreach (var fm in flvv.fldmaps.Where(m => m.Title != null))
				{
					MatchField(tcs, fm, sheetLayout, stringTable, formats, agMaps);
				}

				flvv.fldmaps.AddRange(agMaps);

				// Only keep data containing CellDataLayouts
				var fmts = new List<CellDataLayout> { CellDataLayout.combined, CellDataLayout.separate };
				flvv.fldmaps = flvv.fldmaps.Where(fm => fm.cellLoc != null && fmts.Contains(fm.cellLoc.dataLayout)).ToList();

				// Add a filename layout if the field exists.
				var fileName = sheetLayout.wsLayout.fields.FirstOrDefault(fld => fld.fldType == FieldType.fileName);
				if (fileName != null)
				{
					flvv.fldmaps.Add(new FieldCellMap { Title = FieldType.fileName.ToString(), field = fileName, Value = file.Name });
				}

				// Add a filePath layout if the field exists.
				var filePath = sheetLayout.wsLayout.fields.FirstOrDefault(fld => fld.fldType == FieldType.filePath);
				if (filePath != null)
				{
					flvv.fldmaps.Add(new FieldCellMap { Title = FieldType.filePath.ToString(), field = filePath, Value = file.FullName });
				}

				// Compute how well the matching went.
				flvv.noneNullTitleCnt = flvv.fldmaps.Where(fm => !string.IsNullOrWhiteSpace(fm.Title)).Count();
				flvv.noMatchCnt = flvv.fldmaps.Where(fm => fm.field == null).Count();
				flvv.missingReqFldCnt = reqFlds.Where(rf => !flvv.fldmaps.Select(fm => fm.field).Contains(rf)).Count();
				flvv.noReqValCnt = flvv.fldmaps.Where(fm => fm.field != null && fm.field.isRequired && string.IsNullOrWhiteSpace(fm.Value)).Count();
				flvv.noValCnt = flvv.fldmaps.Where(fm => fm.field != null && string.IsNullOrWhiteSpace(fm.Value)).Count();
			}

			// Find the best acceptable layout match.
			var fldLayout_v = md.fldCellVersMaps
									.Where(fl => fl.noMatchCnt == 0 && fl.noReqValCnt == 0 && fl.missingReqFldCnt == 0)
									.OrderByDescending(fl => fl.fldmaps.Count())
									.OrderBy(fl => fl.noValCnt)
									.FirstOrDefault();

			md.fldCellMap = fldLayout_v;

			md.matchCnt += md.fldCellMap != null ? 1 : 0;

			return md;
		}
示例#17
0
		private bool MatchField(IEnumerable<Cell> tcs, FieldCellMap fm, SheetLayout sheetLayout, SharedStringTablePart stringTable, CellFormats formats, List<FieldCellMap> agMaps)
		{
			bool foundFld = false;

			foreach (var fld in sheetLayout.wsLayout.fields.Where(f => f.fldType == FieldType.cell))
			{
				if (foundFld)
					break;

				try
				{
					switch (fm.cellLoc.dataLayout)
					{
						case CellDataLayout.combined:
							foundFld = MatchCombinedField(fld, fm);
							break;
						case CellDataLayout.separate:
							foundFld = MatchSeparatedField(tcs, fld, fm, stringTable, formats);
							break;
						case CellDataLayout.aggregate:
							// Cell contains an aggregate of fields. Based on 
							{
								var aggCell = tcs.Where(c => c.CellReference == fm.cellLoc.TitleRef).FirstOrDefault();
								var val = Spreadsheet.GetCellValue(aggCell, stringTable.SharedStringTable, formats, null);
								if (val != null && fm.cellLoc.aggregateCellCnt > 0 && fm.cellLoc.aggregateCellSeparator != null && fm.cellLoc.aggregateCellSeparator.Count() > 0)
								{
									var cells = Regex.Split(val.Trim(), fm.cellLoc.aggregateCellSeparator).ToList();
									if (cells.Count() == fm.cellLoc.aggregateCellCnt)
									{
										var nfms = new List<FieldCellMap>();

										foreach(var agv in fm.cellLoc.cellMaps)
										{
											FieldCellMap nfm = null;

											switch (agv.dataLayout)
											{
												case CellDataLayout.combined:
													nfm = new FieldCellMap {
														Title = cells[agv.aggregateIdx].ToLower(),
														Value = cells[agv.aggregateIdx],
														versMap = fm.versMap,
														cellLoc = new CellLocation
														{
															dataLayout = CellDataLayout.combined,
															TitleRef = fm.cellLoc.TitleRef,
															ValueRef = fm.cellLoc.ValueRef
														} 
													};

													MatchField(tcs, nfm, sheetLayout, stringTable, formats, agMaps);
													if (nfm.field != null)
														nfms.Add(nfm);

													break;

												case CellDataLayout.lookup:
													var nfld1 = sheetLayout.wsLayout.fields.Where(f => f.fldType == FieldType.cell);
													var nfld2 = nfld1.Where(f => f.titles != null);

													var nfld = nfld2.FirstOrDefault(f => f.titles.Select(t => t.ToLower()).Contains(agv.lookupString.ToLower()));
													
													if (nfld != null)
													{
														nfm = new FieldCellMap
														{
															Title = agv.lookupString,
															Value = cells[agv.aggregateIdx],
															field = nfld,
															versMap = fm.versMap,
															cellLoc = new CellLocation
															{
																dataLayout = CellDataLayout.separate,
																TitleRef = fm.cellLoc.TitleRef,
																ValueRef = fm.cellLoc.ValueRef
															}
														};

														nfms.Add(nfm);
													}
													break;
											}
										}

										if(nfms.Count == fm.cellLoc.cellMaps.Count())
										{
											agMaps.AddRange(nfms);
											foundFld = true;
											break;
										}
									}
								}
							}
							break;
					}


				}
				catch (Exception ex)
				{
					Log.New.Msg(ex);
				}

			}
			return foundFld;
		}
示例#18
0
		/// <remarks>
		///		An Excel SpreadSheet contains one or more Worksheets, each may or maynot contain data of interest.
		///			- Excel is a terrible way to collect data from a large number of different sources in a consistent and reliable way.
		///			- Be that as it may, Excel is favored by organizations that prefer manpower over automation when performing data processing tasks.
		///				- Most state agencies are typical of this kind of organization.
		///					- To top it off most of these agencies give little thought to gathering data in a consistent way. So we are likely to
		///						recieve a dump of spreadsheets with a variety of inconsistencies.
		///		
		///		As varied as these spreadsheets may be, a spreadsheet is expected to contain only a single type of related data set which is called a
		///			DataSourceType in this application.
		///		
		///		A DataSourceType describes how to process the worksheets in a spreadsheet. It indicates:
		///			- the name of the file to save extracted data.
		///			- a list of DataWorkSheets
		///			- an indicator as to how to process the spreadsheet against the list of DataWorkSheets:
		///			
		///				• MatchAllDataWorkSheets 
		///					- There must be a one to one correspondence between each DataWorkSheet and each SpreadSheet WorkSheet in order.
		///					- The DataWorkSheet name must match the SpreadSheet WorkSheet name.
		///					
		///				• MatchByClosestWorkSheetLayout 
		///					- Each SpreadSheet Worksheet will be matched against the closest DataWorkSheet/WorkSheetLayout
		///		
		///		A DataWorkSheet has
		///			- a Name to be used when processing the spreadsheet by MatchAllDataWorkSheets.
		///			- a WorkSheetLayout
		/// 
		///		A WorkSheetLayout is
		///			- a collection of field layout versions with additional information about how to determine where to look 
		///				for field cells on the spreadsheet.
		///			- a collection of data columns with addition information about how to determine where to look
		///				for the data column on the WorkSheet.
		///		
		///		- Each data column in the collection has associated with it a list of column titles that should 
		///			map from the WorkSheet to the data column.
		///			
		///		- The WorkSheetLayout also includes a collection of col layout versions. Each of these is a list of cells 
		///			that should be scraped for strings that are concatinated into a column title and the column to associate
		///			the title with.
		///			
		///		- For a given WorkSheet all layouts are processed 
		///		
		///		There is a layout of column title cells that will be scaped for column titles. Those titles 
		///		are then matched to a list of titles for a given data column. The assumption being that all titles to match
		///		are unique across all data columns for a given WorkSheetLayout
		/// </remarks>
		public MatchData MatchLayouts(Worksheet ws, SheetLayout sheetLayout, SharedStringTablePart stringTable, CellFormats formats, FileInfo file)
		{
			MatchData md = new MatchData();
			MatchData colMD = null;
			MatchData cellMD = null;

			// All cells in worksheet.
			var tcs = ws.Descendants<Cell>();

			switch (sheetLayout.wsLayout.layoutType)
			{
				case LayoutType.Both:
					colMD = MatchColLayouts(md, tcs, sheetLayout, stringTable, formats, file);
					cellMD = MatchCellLayouts(md, tcs, sheetLayout, stringTable, formats, file);
					md.fldCellMap = cellMD.fldCellMap;
					md.fldColMap = colMD.fldColMap;
					md.isPass = md.fldColMap != null && md.fldCellMap != null;
					break;
				case LayoutType.CellOnly:
					cellMD = MatchCellLayouts(md, tcs, sheetLayout, stringTable, formats, file);
					md.fldCellMap = cellMD.fldCellMap;
					md.isPass =  md.fldCellMap != null;
					break;
				case LayoutType.ColumnOnly:
					colMD = MatchColLayouts(md, tcs, sheetLayout, stringTable, formats, file);
					md.fldColMap = colMD.fldColMap;
					md.isPass = md.fldColMap != null;
					break;
			}

			if (md.isPass)
			{
				if (sheetLayout.srcWorksheets == null)
					sheetLayout.srcWorksheets = new List<Worksheet>();

				sheetLayout.srcWorksheets.Add(ws);
			}

			return md;
		}
        // Generates content of sharedStringTablePart1.
        private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1)
        {
            SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)78U, UniqueCount = (UInt32Value)44U };

            SharedStringItem sharedStringItem1 = new SharedStringItem();
            Text text1 = new Text();
            text1.Text = "CLIENTE:";

            sharedStringItem1.Append(text1);

            SharedStringItem sharedStringItem2 = new SharedStringItem();
            Text text2 = new Text();
            text2.Text = "FECHA DE EMISION:";

            sharedStringItem2.Append(text2);

            SharedStringItem sharedStringItem3 = new SharedStringItem();
            Text text3 = new Text();
            text3.Text = "Av. Corrientes 6277     ( 1427)   Buenos Aires";

            sharedStringItem3.Append(text3);

            SharedStringItem sharedStringItem4 = new SharedStringItem();
            Text text4 = new Text();
            text4.Text = "ORDEN:";

            sharedStringItem4.Append(text4);

            SharedStringItem sharedStringItem5 = new SharedStringItem();
            Text text5 = new Text() { Space = SpaceProcessingModeValues.Preserve };
            text5.Text = "            Argentina - Tel.: 4323-9931";

            sharedStringItem5.Append(text5);

            SharedStringItem sharedStringItem6 = new SharedStringItem();
            Text text6 = new Text();
            text6.Text = "MEDIO";

            sharedStringItem6.Append(text6);

            SharedStringItem sharedStringItem7 = new SharedStringItem();
            Text text7 = new Text();
            text7.Text = "PROGRAMA:";

            sharedStringItem7.Append(text7);

            SharedStringItem sharedStringItem8 = new SharedStringItem();
            Text text8 = new Text();
            text8.Text = "COORDINADOR GRAL. DE PRODUCCION:";

            sharedStringItem8.Append(text8);

            SharedStringItem sharedStringItem9 = new SharedStringItem();
            Text text9 = new Text();
            text9.Text = "CONTACTO:";

            sharedStringItem9.Append(text9);

            SharedStringItem sharedStringItem10 = new SharedStringItem();
            Text text10 = new Text();
            text10.Text = "TELEFONO/ Fax:";

            sharedStringItem10.Append(text10);

            SharedStringItem sharedStringItem11 = new SharedStringItem();
            Text text11 = new Text();
            text11.Text = "DIRECCION:";

            sharedStringItem11.Append(text11);

            SharedStringItem sharedStringItem12 = new SharedStringItem();
            Text text12 = new Text();
            text12.Text = "E-MAIL:";

            sharedStringItem12.Append(text12);

            SharedStringItem sharedStringItem13 = new SharedStringItem();
            Text text13 = new Text();
            text13.Text = "PROGRAMAS";

            sharedStringItem13.Append(text13);

            SharedStringItem sharedStringItem14 = new SharedStringItem();
            Text text14 = new Text();
            text14.Text = "PRODUCTOS";

            sharedStringItem14.Append(text14);

            SharedStringItem sharedStringItem15 = new SharedStringItem();
            Text text15 = new Text();
            text15.Text = "EMPRESA";

            sharedStringItem15.Append(text15);

            SharedStringItem sharedStringItem16 = new SharedStringItem();
            Text text16 = new Text() { Space = SpaceProcessingModeValues.Preserve };
            text16.Text = "Tipo de ";

            sharedStringItem16.Append(text16);

            SharedStringItem sharedStringItem17 = new SharedStringItem();
            Text text17 = new Text();
            text17.Text = "SEG.";

            sharedStringItem17.Append(text17);

            SharedStringItem sharedStringItem18 = new SharedStringItem();
            Text text18 = new Text();
            text18.Text = "S";

            sharedStringItem18.Append(text18);

            SharedStringItem sharedStringItem19 = new SharedStringItem();
            Text text19 = new Text();
            text19.Text = "D";

            sharedStringItem19.Append(text19);

            SharedStringItem sharedStringItem20 = new SharedStringItem();
            Text text20 = new Text();
            text20.Text = "L";

            sharedStringItem20.Append(text20);

            SharedStringItem sharedStringItem21 = new SharedStringItem();
            Text text21 = new Text();
            text21.Text = "M";

            sharedStringItem21.Append(text21);

            SharedStringItem sharedStringItem22 = new SharedStringItem();
            Text text22 = new Text();
            text22.Text = "J";

            sharedStringItem22.Append(text22);

            SharedStringItem sharedStringItem23 = new SharedStringItem();
            Text text23 = new Text();
            text23.Text = "V";

            sharedStringItem23.Append(text23);

            SharedStringItem sharedStringItem24 = new SharedStringItem();
            Text text24 = new Text();
            text24.Text = "Cantidad";

            sharedStringItem24.Append(text24);

            SharedStringItem sharedStringItem25 = new SharedStringItem();
            Text text25 = new Text() { Space = SpaceProcessingModeValues.Preserve };
            text25.Text = "Costo Neto ";

            sharedStringItem25.Append(text25);

            SharedStringItem sharedStringItem26 = new SharedStringItem();
            Text text26 = new Text();
            text26.Text = "Total";

            sharedStringItem26.Append(text26);

            SharedStringItem sharedStringItem27 = new SharedStringItem();
            Text text27 = new Text();
            text27.Text = "Salida";

            sharedStringItem27.Append(text27);

            SharedStringItem sharedStringItem28 = new SharedStringItem();
            Text text28 = new Text();
            text28.Text = "Salidas";

            sharedStringItem28.Append(text28);

            SharedStringItem sharedStringItem29 = new SharedStringItem();
            Text text29 = new Text();
            text29.Text = "x salida PNT";

            sharedStringItem29.Append(text29);

            SharedStringItem sharedStringItem30 = new SharedStringItem();
            Text text30 = new Text();
            text30.Text = "Neto";

            sharedStringItem30.Append(text30);

            SharedStringItem sharedStringItem31 = new SharedStringItem();
            Text text31 = new Text();
            text31.Text = "SPRAYETTE";

            sharedStringItem31.Append(text31);

            SharedStringItem sharedStringItem32 = new SharedStringItem();
            Text text32 = new Text();
            text32.Text = "PNT";

            sharedStringItem32.Append(text32);

            SharedStringItem sharedStringItem33 = new SharedStringItem();
            Text text33 = new Text();
            text33.Text = "NUMERO";

            sharedStringItem33.Append(text33);

            SharedStringItem sharedStringItem34 = new SharedStringItem();
            Text text34 = new Text();
            text34.Text = "PNT\'S TOTALES";

            sharedStringItem34.Append(text34);

            SharedStringItem sharedStringItem35 = new SharedStringItem();
            Text text35 = new Text();
            text35.Text = "COSTO POR SALIDA";

            sharedStringItem35.Append(text35);

            SharedStringItem sharedStringItem36 = new SharedStringItem();
            Text text36 = new Text();
            text36.Text = "EXCLUSIVE";

            sharedStringItem36.Append(text36);

            SharedStringItem sharedStringItem37 = new SharedStringItem();
            Text text37 = new Text();
            text37.Text = "SUBTOTAL";

            sharedStringItem37.Append(text37);

            SharedStringItem sharedStringItem38 = new SharedStringItem();
            Text text38 = new Text();
            text38.Text = "POLISHOP";

            sharedStringItem38.Append(text38);

            SharedStringItem sharedStringItem39 = new SharedStringItem();
            Text text39 = new Text();
            text39.Text = "IVA 21%:";

            sharedStringItem39.Append(text39);

            SharedStringItem sharedStringItem40 = new SharedStringItem();
            Text text40 = new Text();
            text40.Text = "TOTAL";

            sharedStringItem40.Append(text40);

            SharedStringItem sharedStringItem41 = new SharedStringItem();
            Text text41 = new Text();
            text41.Text = "PRODUCTO";

            sharedStringItem41.Append(text41);

            SharedStringItem sharedStringItem42 = new SharedStringItem();
            Text text42 = new Text();
            text42.Text = "ZOCALO";

            sharedStringItem42.Append(text42);

            SharedStringItem sharedStringItem43 = new SharedStringItem();
            Text text43 = new Text();
            text43.Text = "COD. INGESTA";

            sharedStringItem43.Append(text43);

            SharedStringItem sharedStringItem44 = new SharedStringItem();
            Text text44 = new Text();
            text44.Text = "SALIDAS";

            sharedStringItem44.Append(text44);

            sharedStringTable1.Append(sharedStringItem1);
            sharedStringTable1.Append(sharedStringItem2);
            sharedStringTable1.Append(sharedStringItem3);
            sharedStringTable1.Append(sharedStringItem4);
            sharedStringTable1.Append(sharedStringItem5);
            sharedStringTable1.Append(sharedStringItem6);
            sharedStringTable1.Append(sharedStringItem7);
            sharedStringTable1.Append(sharedStringItem8);
            sharedStringTable1.Append(sharedStringItem9);
            sharedStringTable1.Append(sharedStringItem10);
            sharedStringTable1.Append(sharedStringItem11);
            sharedStringTable1.Append(sharedStringItem12);
            sharedStringTable1.Append(sharedStringItem13);
            sharedStringTable1.Append(sharedStringItem14);
            sharedStringTable1.Append(sharedStringItem15);
            sharedStringTable1.Append(sharedStringItem16);
            sharedStringTable1.Append(sharedStringItem17);
            sharedStringTable1.Append(sharedStringItem18);
            sharedStringTable1.Append(sharedStringItem19);
            sharedStringTable1.Append(sharedStringItem20);
            sharedStringTable1.Append(sharedStringItem21);
            sharedStringTable1.Append(sharedStringItem22);
            sharedStringTable1.Append(sharedStringItem23);
            sharedStringTable1.Append(sharedStringItem24);
            sharedStringTable1.Append(sharedStringItem25);
            sharedStringTable1.Append(sharedStringItem26);
            sharedStringTable1.Append(sharedStringItem27);
            sharedStringTable1.Append(sharedStringItem28);
            sharedStringTable1.Append(sharedStringItem29);
            sharedStringTable1.Append(sharedStringItem30);
            sharedStringTable1.Append(sharedStringItem31);
            sharedStringTable1.Append(sharedStringItem32);
            sharedStringTable1.Append(sharedStringItem33);
            sharedStringTable1.Append(sharedStringItem34);
            sharedStringTable1.Append(sharedStringItem35);
            sharedStringTable1.Append(sharedStringItem36);
            sharedStringTable1.Append(sharedStringItem37);
            sharedStringTable1.Append(sharedStringItem38);
            sharedStringTable1.Append(sharedStringItem39);
            sharedStringTable1.Append(sharedStringItem40);
            sharedStringTable1.Append(sharedStringItem41);
            sharedStringTable1.Append(sharedStringItem42);
            sharedStringTable1.Append(sharedStringItem43);
            sharedStringTable1.Append(sharedStringItem44);

            sharedStringTablePart1.SharedStringTable = sharedStringTable1;
        }
        private void FillCellText(SharedStringTablePart shareStringPart, DocumentFormat.OpenXml.Spreadsheet.Cell cell, string text)
        {
            if (cell == null)
                throw new ArgumentException("cell");

            if (text == null)
                text = String.Empty;

            int index = InsertSharedStringItem(text, shareStringPart);

            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(index.ToString());
            cell.DataType = new EnumValue<DocumentFormat.OpenXml.Spreadsheet.CellValues>(DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString);
        }
示例#21
0
        //public byte[] Populate(QueriesDataModel queriesDataModel, string templateFilePath)
        //{
        //    // Read a template file.
        //    byte[] templateBytes;
        //    try
        //    {
        //        templateBytes = File.ReadAllBytes(templateFilePath);
        //    }
        //    catch (Exception ex)
        //    {
        //        Trace.TraceError("Unable to read a template file. Exception: {0}.", ex);
        //        throw;
        //    }

        //    using (var fileStream = new MemoryStream())
        //    {
        //        fileStream.Write(templateBytes, 0, templateBytes.Length);
        //        var spreadsheetDocument = SpreadsheetDocument.Open(fileStream, true);
        //        var workbookPart = spreadsheetDocument.WorkbookPart;

        //        // Get the SharedStringTablePart. If it does not exist, create a new one.
        //        //var sharedStringTablePart = workbookPart.GetPartsOfType<SharedStringTablePart>().Any()
        //        //                                    ? workbookPart.GetPartsOfType<SharedStringTablePart>().First()
        //        //                                    : workbookPart.AddNewPart<SharedStringTablePart>();

        //        foreach (var worksheetPart in workbookPart.WorksheetParts)
        //        {
        //            var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
        //            var variableCells = getVariableCells(worksheetPart);
        //            foreach (var variableCell in variableCells)
        //            {
        //                var refCell = new Cell
        //                {
        //                    CellReference = changeRowIndex(variableCell.Cell.CellReference, -1),
        //                    StyleIndex = variableCell.Cell.StyleIndex
        //                };

        //                var dataCollection = getDataForVariable(queriesDataModel, variableCell);
        //                foreach (var data in dataCollection)
        //                {
        //                    // Insert the text into the SharedStringTablePart.
        //                    //var index = insertSharedStringTableItem(data.ToString(), sharedStringTablePart);

        //                    var newCell = new Cell
        //                    {
        //                        CellReference = changeRowIndex(refCell.CellReference),
        //                        CellValue = new CellValue(data.ToString()),
        //                        DataType = new EnumValue<CellValues>(CellValues.String),
        //                        //CellValue = new CellValue(index.ToString()),
        //                        //DataType = new EnumValue<CellValues>(CellValues.SharedString),
        //                        StyleIndex = refCell.StyleIndex
        //                    };

        //                    // Find a row for the new cell.
        //                    var refRowIndex = getRowIndex(newCell.CellReference);
        //                    var refRow = sheetData.Elements<Row>().SingleOrDefault(x => x.RowIndex == refRowIndex);

        //                    // If a row does not exists, create a new one.
        //                    if (refRow == null)
        //                    {
        //                        var newRow = new Row { RowIndex = refRowIndex };
        //                        newRow.AppendChild(newCell);

        //                        var rowAfter = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex > newRow.RowIndex);
        //                        sheetData.InsertBefore(newRow, rowAfter);
        //                    }
        //                    // Otherwise, add a new cell to an existing row.
        //                    else
        //                    {
        //                        var existingCell = refRow.Elements<Cell>().SingleOrDefault(x => x.CellReference.Value == newCell.CellReference.Value);
        //                        if (existingCell == null)
        //                        {
        //                            var cellAfter =
        //                                refRow.Elements<Cell>()
        //                                    .FirstOrDefault(
        //                                        cell =>
        //                                            string.Compare(cell.CellReference.Value, newCell.CellReference.Value,
        //                                                StringComparison.InvariantCultureIgnoreCase) > 0);
        //                            refRow.InsertBefore(newCell, cellAfter);
        //                        }
        //                        else
        //                            refRow.ReplaceChild(newCell, existingCell);
        //                    }

        //                    refCell = newCell;
        //                }
        //            }
        //        }
        //        workbookPart.Workbook.Save();
        //        spreadsheetDocument.Close();

        //        return fileStream.ToArray();
        //    }
        //}

        private static int insertSharedStringTableItem(SharedStringTablePart sharedStringTablePart, string text)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (sharedStringTablePart.SharedStringTable == null)
                sharedStringTablePart.SharedStringTable = new SharedStringTable();

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            var i = 0;
            foreach (var item in sharedStringTablePart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                    return i;
                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            sharedStringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
            sharedStringTablePart.SharedStringTable.Save();

            return i;
        }
        // Generates content of sharedStringTablePart1.
        protected override void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart)
        {
            SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)66U, UniqueCount = (UInt32Value)65U };

            SharedStringItem sharedStringItem1 = new SharedStringItem();
            Text text1 = new Text();
            text1.Text = sampleSelectionRequest.Executor.Name;

            sharedStringItem1.Append(text1);

            SharedStringItem sharedStringItem2 = new SharedStringItem();
            Text text2 = new Text();
            text2.Text = "(наименование государственного учреждения)";

            sharedStringItem2.Append(text2);

            SharedStringItem sharedStringItem3 = new SharedStringItem();
            Text text3 = new Text();
            text3.Text = "Регистрационный №";

            sharedStringItem3.Append(text3);

            SharedStringItem sharedStringItem4 = new SharedStringItem();
            Text text4 = new Text();
            text4.Text = "Дата регистрации";

            sharedStringItem4.Append(text4);

            SharedStringItem sharedStringItem5 = new SharedStringItem();
            Text text5 = new Text();
            text5.Text = "на проведение апробации сельскохозяйственного растения";

            sharedStringItem5.Append(text5);

            SharedStringItem sharedStringItem6 = new SharedStringItem();
            Text text6 = new Text();
            text6.Text = "1.";

            sharedStringItem6.Append(text6);

            SharedStringItem sharedStringItem7 = new SharedStringItem();
            Text text7 = new Text();
            text7.Text = "Сведения о заявителе";

            sharedStringItem7.Append(text7);

            SharedStringItem sharedStringItem8 = new SharedStringItem();
            Text text8 = new Text();
            text8.Text = "1.1.";

            sharedStringItem8.Append(text8);

            SharedStringItem sharedStringItem9 = new SharedStringItem();
            Text text9 = new Text();
            text9.Text = "Наименование юридического лица; фамилия, собственное имя и отчество (если таковое имеется)";

            sharedStringItem9.Append(text9);

            SharedStringItem sharedStringItem10 = new SharedStringItem();
            Text text10 = new Text();
            text10.Text = "физического лица, в том числе индивидуального предпринимателя";

            sharedStringItem10.Append(text10);

            SharedStringItem sharedStringItem11 = new SharedStringItem();
            Text text11 = new Text();
            text11.Text = sampleSelectionRequest.Client.Name;

            sharedStringItem11.Append(text11);

            SharedStringItem sharedStringItem12 = new SharedStringItem();
            Text text12 = new Text();
            text12.Text = "1.2.";

            sharedStringItem12.Append(text12);

            SharedStringItem sharedStringItem13 = new SharedStringItem();
            Text text13 = new Text();
            text13.Text = "Место нахождения юридического лица; адрес регистрации по месту жительства (месту пребывания)";

            sharedStringItem13.Append(text13);

            SharedStringItem sharedStringItem14 = new SharedStringItem();
            Text text14 = new Text();
            text14.Text = sampleSelectionRequest.Client.Address;

            sharedStringItem14.Append(text14);

            SharedStringItem sharedStringItem15 = new SharedStringItem();
            Text text15 = new Text();
            text15.Text = "1.3.";

            sharedStringItem15.Append(text15);

            SharedStringItem sharedStringItem16 = new SharedStringItem();
            Text text16 = new Text();
            text16.Text = "Номер телефона, факс, адрес электронной почты";

            sharedStringItem16.Append(text16);

            SharedStringItem sharedStringItem17 = new SharedStringItem();
            Text text17 = new Text();
            text17.Text = sampleSelectionRequest.Client.Telephone;

            sharedStringItem17.Append(text17);

            SharedStringItem sharedStringItem18 = new SharedStringItem();
            Text text18 = new Text();
            text18.Text = "Прошу провести апробацию посева (посадки) сельскохозяйственного растения";

            sharedStringItem18.Append(text18);

            SharedStringItem sharedStringItem19 = new SharedStringItem();
            Text text19 = new Text();
            text19.Text = "2.";

            sharedStringItem19.Append(text19);

            SharedStringItem sharedStringItem20 = new SharedStringItem();
            Text text20 = new Text();
            text20.Text = "Сведения о семенах сельскохозяйственного растения и его посеве (посадке)";

            sharedStringItem20.Append(text20);

            SharedStringItem sharedStringItem21 = new SharedStringItem();
            Text text21 = new Text();
            text21.Text = "2.1.";

            sharedStringItem21.Append(text21);

            SharedStringItem sharedStringItem22 = new SharedStringItem();
            Text text22 = new Text();
            text22.Text = "Наименование сельскохозяйственного растения";

            sharedStringItem22.Append(text22);

            SharedStringItem sharedStringItem23 = new SharedStringItem();
            Text text23 = new Text();
            text23.Text = "2.2.";

            sharedStringItem23.Append(text23);

            SharedStringItem sharedStringItem24 = new SharedStringItem();
            Text text24 = new Text();
            text24.Text = "Наименование сорта сельскохозяйственного растения";

            sharedStringItem24.Append(text24);

            SharedStringItem sharedStringItem25 = new SharedStringItem();
            Text text25 = new Text();
            text25.Text = sampleSelectionRequest.PlantVariety.Plant.Name;

            sharedStringItem25.Append(text25);

            SharedStringItem sharedStringItem26 = new SharedStringItem();
            Text text26 = new Text();
            text26.Text = sampleSelectionRequest.PlantVariety.Name;

            sharedStringItem26.Append(text26);

            SharedStringItem sharedStringItem27 = new SharedStringItem();
            Text text27 = new Text();
            text27.Text = "2.3.";

            sharedStringItem27.Append(text27);

            SharedStringItem sharedStringItem28 = new SharedStringItem();
            Text text28 = new Text();
            text28.Text = "Категория высеянных семян сельскохозяйственного растения";

            sharedStringItem28.Append(text28);

            SharedStringItem sharedStringItem29 = new SharedStringItem();
            Text text29 = new Text();
            text29.Text = "2.4.";

            sharedStringItem29.Append(text29);

            SharedStringItem sharedStringItem30 = new SharedStringItem();
            Text text30 = new Text();
            text30.Text = "Этап размножения высеянных семян сельскохозяйственного растения";

            sharedStringItem30.Append(text30);

            SharedStringItem sharedStringItem31 = new SharedStringItem();
            Text text31 = new Text();
            text31.Text = sampleSelectionRequest.PlantVariety.Plant.Category;

            sharedStringItem31.Append(text31);

            SharedStringItem sharedStringItem32 = new SharedStringItem();
            Text text32 = new Text();
            text32.Text = sampleSelectionRequest.ReproductionStep.Name;

            sharedStringItem32.Append(text32);

            SharedStringItem sharedStringItem33 = new SharedStringItem();
            Text text33 = new Text();
            text33.Text = "2.5.";

            sharedStringItem33.Append(text33);

            SharedStringItem sharedStringItem34 = new SharedStringItem();
            Text text34 = new Text();
            text34.Text = "Номер партии высеянных семян сельскохозяйственного растения";

            sharedStringItem34.Append(text34);

            SharedStringItem sharedStringItem35 = new SharedStringItem();
            Text text35 = new Text();
            text35.Text = "2.6.";

            sharedStringItem35.Append(text35);

            SharedStringItem sharedStringItem36 = new SharedStringItem();
            Text text36 = new Text();
            text36.Text = "Номер и дача выдачи документа, подтверждающего посевные и сортовые качества семян сельскохозяйственного растения";

            sharedStringItem36.Append(text36);

            SharedStringItem sharedStringItem37 = new SharedStringItem();
            Text text37 = new Text();
            text37.Text = sampleSelectionRequest.SeedsBatchNumber;

            sharedStringItem37.Append(text37);

            SharedStringItem sharedStringItem38 = new SharedStringItem();
            Text text38 = new Text();
            text38.Text = "2.7.";

            sharedStringItem38.Append(text38);

            SharedStringItem sharedStringItem39 = new SharedStringItem();
            Text text39 = new Text();
            text39.Text = "Количество высеянных семян (единиц)";

            sharedStringItem39.Append(text39);

            SharedStringItem sharedStringItem40 = new SharedStringItem();
            Text text40 = new Text();
            text40.Text = "2.8.";

            sharedStringItem40.Append(text40);

            SharedStringItem sharedStringItem41 = new SharedStringItem();
            Text text41 = new Text();
            text41.Text = "Площадь поля, участка, посева (посадки)";

            sharedStringItem41.Append(text41);

            SharedStringItem sharedStringItem42 = new SharedStringItem();
            Text text42 = new Text();
            text42.Text = "2.9.";

            sharedStringItem42.Append(text42);

            SharedStringItem sharedStringItem43 = new SharedStringItem();
            Text text43 = new Text();
            text43.Text = "Номер поля, участка, посева (посадки)";

            sharedStringItem43.Append(text43);

            SharedStringItem sharedStringItem44 = new SharedStringItem();
            Text text44 = new Text();
            text44.Text = "2.10.";

            sharedStringItem44.Append(text44);

            SharedStringItem sharedStringItem45 = new SharedStringItem();
            Text text45 = new Text();
            text45.Text = "Место расположения поля, участка, посева (посадки)";

            sharedStringItem45.Append(text45);

            SharedStringItem sharedStringItem46 = new SharedStringItem();
            Text text46 = new Text();
            text46.Text = "2.11.";

            sharedStringItem46.Append(text46);

            SharedStringItem sharedStringItem47 = new SharedStringItem();
            Text text47 = new Text();
            text47.Text = "Происхождение семян сельскохозяйственных растений";

            sharedStringItem47.Append(text47);

            SharedStringItem sharedStringItem48 = new SharedStringItem();
            Text text48 = new Text();
            text48.Text = "2.12.";

            sharedStringItem48.Append(text48);

            SharedStringItem sharedStringItem49 = new SharedStringItem();
            Text text49 = new Text();
            text49.Text = "Дата посева (посадки)";

            sharedStringItem49.Append(text49);

            SharedStringItem sharedStringItem50 = new SharedStringItem();
            Text text50 = new Text();
            text50.Text = "2.13.";

            sharedStringItem50.Append(text50);

            SharedStringItem sharedStringItem51 = new SharedStringItem();
            Text text51 = new Text();
            text51.Text = "Сельскохозяйственные растения, предшествующие посеву (посадке) (указать год)";

            sharedStringItem51.Append(text51);

            SharedStringItem sharedStringItem52 = new SharedStringItem();
            Text text52 = new Text();
            text52.Text = "2.14.";

            sharedStringItem52.Append(text52);

            SharedStringItem sharedStringItem53 = new SharedStringItem();
            Text text53 = new Text();
            text53.Text = "Метод определения сортовой чистоты или сортовой типичности сельскохозяйственных растений, семена которых в случае реализации подлежат обязательному грунтовому контролю и (или) лабораторному сортовому контролю:";

            sharedStringItem53.Append(text53);

            SharedStringItem sharedStringItem54 = new SharedStringItem();
            Text text54 = new Text();
            text54.Text = "[ ] грунтовой контроль";

            sharedStringItem54.Append(text54);

            SharedStringItem sharedStringItem55 = new SharedStringItem();
            Text text55 = new Text();
            text55.Text = "[ ] лабораторный сортовой контроль";

            sharedStringItem55.Append(text55);

            SharedStringItem sharedStringItem56 = new SharedStringItem();
            Text text56 = new Text();
            text56.Text = "3. [ ] Вся информация, изложенная в заявлении, является окончательной и достоверной.";

            sharedStringItem56.Append(text56);

            SharedStringItem sharedStringItem57 = new SharedStringItem();
            Text text57 = new Text();
            text57.Text = "(должность представителя заявителя)";

            sharedStringItem57.Append(text57);

            SharedStringItem sharedStringItem58 = new SharedStringItem();
            Text text58 = new Text();
            text58.Text = "(подпись)";

            sharedStringItem58.Append(text58);

            SharedStringItem sharedStringItem59 = new SharedStringItem();
            Text text59 = new Text();
            text59.Text = "инициалы, фамилия";

            sharedStringItem59.Append(text59);

            SharedStringItem sharedStringItem60 = new SharedStringItem();
            Text text60 = new Text();
            text60.Text = "5 июня 2016 г.";

            sharedStringItem60.Append(text60);

            SharedStringItem sharedStringItem61 = new SharedStringItem();
            Text text61 = new Text();
            text61.Text = "* Для зерновых, зернобобовых, кукурузы - в течение минимум 2 лет; для крестоцветных кормовых и масличных, злаковых и бобовых трав - в течение минимум 5 лет; для прочих растений - в течение минимум 1 года.";

            sharedStringItem61.Append(text61);

            SharedStringItem sharedStringItem62 = new SharedStringItem();
            Text text62 = new Text();
            text62.Text = "ЗАЯВЛЕНИЕ";

            sharedStringItem62.Append(text62);

            SharedStringItem sharedStringItem63 = new SharedStringItem();
            Text text63 = new Text();
            text63.Text = "М.П. (при наличии)";

            sharedStringItem63.Append(text63);

            SharedStringItem sharedStringItem64 = new SharedStringItem();
            Text text64 = new Text();
            text64.Text = "4. К заявлению прилагаются";

            sharedStringItem64.Append(text64);

            SharedStringItem sharedStringItem65 = new SharedStringItem();
            Text text65 = new Text();
            text65.Text = "5. Дополнительные сведения";

            sharedStringItem65.Append(text65);

            sharedStringTable1.Append(sharedStringItem1);
            sharedStringTable1.Append(sharedStringItem2);
            sharedStringTable1.Append(sharedStringItem3);
            sharedStringTable1.Append(sharedStringItem4);
            sharedStringTable1.Append(sharedStringItem5);
            sharedStringTable1.Append(sharedStringItem6);
            sharedStringTable1.Append(sharedStringItem7);
            sharedStringTable1.Append(sharedStringItem8);
            sharedStringTable1.Append(sharedStringItem9);
            sharedStringTable1.Append(sharedStringItem10);
            sharedStringTable1.Append(sharedStringItem11);
            sharedStringTable1.Append(sharedStringItem12);
            sharedStringTable1.Append(sharedStringItem13);
            sharedStringTable1.Append(sharedStringItem14);
            sharedStringTable1.Append(sharedStringItem15);
            sharedStringTable1.Append(sharedStringItem16);
            sharedStringTable1.Append(sharedStringItem17);
            sharedStringTable1.Append(sharedStringItem18);
            sharedStringTable1.Append(sharedStringItem19);
            sharedStringTable1.Append(sharedStringItem20);
            sharedStringTable1.Append(sharedStringItem21);
            sharedStringTable1.Append(sharedStringItem22);
            sharedStringTable1.Append(sharedStringItem23);
            sharedStringTable1.Append(sharedStringItem24);
            sharedStringTable1.Append(sharedStringItem25);
            sharedStringTable1.Append(sharedStringItem26);
            sharedStringTable1.Append(sharedStringItem27);
            sharedStringTable1.Append(sharedStringItem28);
            sharedStringTable1.Append(sharedStringItem29);
            sharedStringTable1.Append(sharedStringItem30);
            sharedStringTable1.Append(sharedStringItem31);
            sharedStringTable1.Append(sharedStringItem32);
            sharedStringTable1.Append(sharedStringItem33);
            sharedStringTable1.Append(sharedStringItem34);
            sharedStringTable1.Append(sharedStringItem35);
            sharedStringTable1.Append(sharedStringItem36);
            sharedStringTable1.Append(sharedStringItem37);
            sharedStringTable1.Append(sharedStringItem38);
            sharedStringTable1.Append(sharedStringItem39);
            sharedStringTable1.Append(sharedStringItem40);
            sharedStringTable1.Append(sharedStringItem41);
            sharedStringTable1.Append(sharedStringItem42);
            sharedStringTable1.Append(sharedStringItem43);
            sharedStringTable1.Append(sharedStringItem44);
            sharedStringTable1.Append(sharedStringItem45);
            sharedStringTable1.Append(sharedStringItem46);
            sharedStringTable1.Append(sharedStringItem47);
            sharedStringTable1.Append(sharedStringItem48);
            sharedStringTable1.Append(sharedStringItem49);
            sharedStringTable1.Append(sharedStringItem50);
            sharedStringTable1.Append(sharedStringItem51);
            sharedStringTable1.Append(sharedStringItem52);
            sharedStringTable1.Append(sharedStringItem53);
            sharedStringTable1.Append(sharedStringItem54);
            sharedStringTable1.Append(sharedStringItem55);
            sharedStringTable1.Append(sharedStringItem56);
            sharedStringTable1.Append(sharedStringItem57);
            sharedStringTable1.Append(sharedStringItem58);
            sharedStringTable1.Append(sharedStringItem59);
            sharedStringTable1.Append(sharedStringItem60);
            sharedStringTable1.Append(sharedStringItem61);
            sharedStringTable1.Append(sharedStringItem62);
            sharedStringTable1.Append(sharedStringItem63);
            sharedStringTable1.Append(sharedStringItem64);
            sharedStringTable1.Append(sharedStringItem65);

            sharedStringTablePart.SharedStringTable = sharedStringTable1;
        }
示例#23
0
        /// 向SharedStringTablePart添加字符串
        /// </summary>
        /// <param name="text">字符串內容</param>
        /// <param name="shareStringPart">sharedStringTablePart內容</param>
        /// <returns>如果要添加的字符串已經存在,則直接返回該字符串的索引</returns>
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            //檢測SharedStringTable是否存在,如果不存在,則創建一個
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }
 
            int i = 0;
 
            //遍歷SharedStringTable中所有的Elements,查看目標字符串是否存在
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return i;
                }
                i++;
            }
 
            //如果目標字符串不存在,則創建一個,同時把SharedStringTable的最後一個Elements的"索引+1"返回
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();
 
            return i;
        }
示例#24
0
 /// <summary>
 /// Get cell's value depend on its style: share string or text
 /// </summary>
 /// <param name="shareStringTablePart"></param>
 /// <param name="cell"></param>
 /// <returns></returns>
 private static string GetCellValue(SharedStringTablePart shareStringTablePart, Cell cell)
 {
     if (cell == null || cell.CellValue == null || cell.CellValue.Text.Equals(string.Empty))
     {
         return null;
     }
     else
     {
         if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
         {
             int shareStringId = int.Parse(cell.CellValue.Text);
             SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
             return item.InnerText;
         }
         else
         {
             return cell.CellValue.Text;
         }
     }
 }
示例#25
0
 protected abstract void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart);
示例#26
0
		public MatchData MatchColLayouts(MatchData md, IEnumerable<Cell> tcs, SheetLayout sheetLayout, SharedStringTablePart stringTable, CellFormats formats, FileInfo file)
		{
			// Find and map columns to sheet layouts
			var field_ord = sheetLayout.wsLayout.fields
							.Where(c => c.fldType == FieldType.column)
							.OrderBy(c => c.OutputOrder)
							.Select(c => c.OutputOrder)
							.ToList();

			// Obtain column titles for all signature versions.
			md.fldColVersMaps = new List<FieldColumnVersionMap>();

			foreach (var sig in sheetLayout.wsLayout.colLayouts.OrderByDescending(scl => scl.titleLocations.Count()))
			// for each column layout version scape the worksheet for column title values
			{
				var fldColMaps = new List<FieldColumnMap>();

				var col_ord = sig.titleLocations.OrderBy(so => so.col).Select(so => so.col).ToList();

				foreach (var colLayout in sig.titleLocations)
				{
					string title = "";
					foreach (var c in colLayout.cellRefs)
					// A column may have a number of title cells that must be scraped and concatinated to product the title used for matching to data columns.
					{
						var cl = tcs.FirstOrDefault(cll => cll.CellReference.InnerText == c);
						var tlt = Spreadsheet.GetCellValue(cl, stringTable.SharedStringTable, formats, null);
						title += tlt;
					}

					title = System.Text.RegularExpressions.Regex.Replace(title.Replace('\n', ' '), @"\s+", " ").Trim().ToLower();
					fldColMaps.Add(new FieldColumnMap { column = colLayout.col, title = title, col_order = col_ord.IndexOf(colLayout.col) });
				}

				md.fldColVersMaps.Add(new FieldColumnVersionMap { colLayout = sig, colmaps = fldColMaps });
			}

			// Match the titles to the DataColumns
			foreach (var fcvm in md.fldColVersMaps)
			{
				foreach (var cm in fcvm.colmaps)
				{
					try
					{
						// TODO: Performance improvement if config strings are pre-processed for whitespace and case.
						cm.field = sheetLayout.wsLayout.fields
										.Where(cc => cc.fldType == FieldType.column && cc.titles != null)
										.FirstOrDefault(cc =>
										{
											var lct = cc.titles.Select(t =>
											{
												var tt = System.Text.RegularExpressions.Regex.Replace(t, @"\s+", " ");
												return tt.ToLower();
											});

											var hasTitle = lct.Contains(cm.title);

											return hasTitle;
										});

						// if required field and should be verified then check that first row has value
						if (sheetLayout.wsLayout.verifyFirstRowData && cm.field != null && cm.field.isRequired)
						{
							int col = fcvm.colLayout.colLayoutType == ColLayoutType.Row_Col ? cm.column : fcvm.colLayout.FirstRow;
							int row = fcvm.colLayout.colLayoutType == ColLayoutType.Row_Col ? fcvm.colLayout.FirstRow : cm.column;

							var valRef = Spreadsheet.GetCellRef(row, col);
							var clVal = tcs.FirstOrDefault(clv => clv.CellReference.InnerText == valRef);

							var val = Spreadsheet.GetCellValue(clVal, stringTable.SharedStringTable, formats, cm.field);

							if (!string.IsNullOrWhiteSpace(val))
							{
								cm.hasValue = true;
								cm.firstRowVal = val;
							}
						}

						cm.field_order = cm.field != null ? field_ord.IndexOf(cm.field.OutputOrder) : -9999;
					}
					catch (Exception ex)
					{
						Log.New.Msg(ex);
					}
				}

				// match by neighbor
				// - Locate flds located by related field
				var flds_byRelated = sheetLayout.wsLayout.fields.Where(f => f.locType == LocateType.byRelated);

				// - Locate column map for related field parent
				var related_pairs = flds_byRelated.Select(fr => new { fr = fr, cm = fcvm.colmaps.FirstOrDefault(rcm => rcm.field != null && rcm.field.OutputOrder == fr.RelatedCol) });

				// - Locate column map for related field
				var rf_cm = related_pairs.Where(rp => rp.cm != null).ToList().Select(rp => new { rp = rp, rc = fcvm.colmaps.FirstOrDefault(fcm => fcm.col_order == rp.cm.col_order + 1) });

				// - Update the field for located column map
				rf_cm.ToList().ForEach(rfcm =>
				{
					rfcm.rc.field = rfcm.rp.fr;
					rfcm.rc.field_order = rfcm.rc.field.OutputOrder;
				});

				fcvm.notNullTitleCnt = fcvm.colmaps.Where(cm => !string.IsNullOrWhiteSpace(cm.title)).Count();
				fcvm.noMatchCnt = fcvm.colmaps.Where(cm => cm.field == null).Count();
				fcvm.ReqNoValCnt = fcvm.colmaps.Where(cm => cm.field != null && sheetLayout.wsLayout.verifyFirstRowData && cm.field.isRequired && !cm.hasValue).Count();
				fcvm.disOrder = (int)fcvm.colmaps.Where(dm => dm.field != null).Select(dm => Math.Pow((dm.field_order - dm.col_order), 2)).Sum();
				var dupCols = fcvm.colmaps.Where(dm => dm.field != null).GroupBy(cd => cd.field).Where(d => d.Count() > 1);
				fcvm.colDups = dupCols.Count();
			}

			// Only match col layout versions with zero mismatch, favoring the version with the lowest disorder.
			var colLayout_v = md.fldColVersMaps.Where(sv => sv.noMatchCnt == 0 && sv.colDups == 0 && (!sheetLayout.wsLayout.verifyFirstRowData || sv.ReqNoValCnt == 0)).OrderByDescending(sv => sv.notNullTitleCnt).ThenByDescending(sv => sv.disOrder).FirstOrDefault();

			md.fldColMap = colLayout_v;

			md.matchCnt += md.fldColMap != null ? 1 : 0;

			return md;
		}
示例#27
0
        public ExcelSheetReader Load()
        {
            WorkbookPart wbp = ExcelLoader.SSDocument.WorkbookPart;
            WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(_sheet.Id.Value);
            DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = wsp.Worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.SheetData>();

            _iter = sheetData.Elements<Row>().GetEnumerator();
            _sstp = wbp.GetPartsOfType<SharedStringTablePart>().First();
            RowCount = sheetData.Elements<Row>().Count();
            MaxRowIndex = sheetData.Elements<Row>().Max(v => v.RowIndex.Value);

            //  Field data
            if (ExcelLoader.RowIndex_FieldName >= 0)
            {
                Row row = sheetData.Elements<Row>().Where(v => v.RowIndex == ExcelLoader.RowIndex_FieldName).FirstOrDefault();
                Int32 idx = 0;

                //  field name
                if (row == null)
                    throw new AegisException("FieldName index is {0}, but {1} has no {0} row index.", ExcelLoader.RowIndex_FieldName, _sheet.Name);
                Fields = new FieldInfo[row.LongCount()];

                foreach (Cell cell in row.Elements<Cell>())
                {
                    Fields[idx] = new FieldInfo();
                    Fields[idx].Name = GetTextInCell(cell);
                    ++idx;
                }

                //  data type
                idx = 0;
                row = sheetData.Elements<Row>().Where(v => v.RowIndex == ExcelLoader.RowIndex_DataType).First();
                foreach (Cell cell in row.Elements<Cell>())
                {
                    String text = GetTextInCell(cell).ToLower();

                    if (text == "int" || text == "integer")
                        Fields[idx++].DataType = DataType.Int;

                    else if (text == "double")
                        Fields[idx++].DataType = DataType.Double;

                    else if (text == "datetime")
                        Fields[idx++].DataType = DataType.DateTime;

                    else if (text == "string")
                        Fields[idx++].DataType = DataType.String;

                    else
                        throw new AegisException("Invalid field type at {0}.{1}", _sheet.Name, cell.CellReference);
                }
            }

            return this;
        }
        // Generates content of sharedStringTablePart1.
        private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1)
        {
            SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)5U, UniqueCount = (UInt32Value)5U };

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

            SharedStringItem sharedStringItem2 = new SharedStringItem();
            Text text2 = new Text();
            text2.Text = "score";

            SharedStringItem sharedStringItem3 = new SharedStringItem();
            Text text3 = new Text();
            text3.Text = "Sum of score";

            SharedStringItem sharedStringItem4 = new SharedStringItem();
            Text text4 = new Text();
            text4.Text = "Row Labels";

            SharedStringItem sharedStringItem5 = new SharedStringItem();
            Text text5 = new Text();
            text5.Text = "Grand Total";

            sharedStringItem1.Append(text1);
            sharedStringItem2.Append(text2);
            sharedStringItem3.Append(text3);
            sharedStringItem4.Append(text4);
            sharedStringItem5.Append(text5);

            sharedStringTable1.Append(sharedStringItem1);
            sharedStringTable1.Append(sharedStringItem2);
            sharedStringTable1.Append(sharedStringItem3);
            sharedStringTable1.Append(sharedStringItem4);
            sharedStringTable1.Append(sharedStringItem5);

            sharedStringTablePart1.SharedStringTable = sharedStringTable1;
        }
示例#29
0
        /// <summary>
        /// Create an instance of OpenXmlPart according to the given relationship type.
        /// </summary>
        /// <param name="openXmlPackage">The container OpenXmlPackage.</param>
        /// <param name="relationshipType">The relationship type of the target part.</param>
        /// <param name="openXmlPart">The created instance of OpenXmlPart.</param>
        /// <remarks>This partial method will be generated by code generaotr.</remarks>
        static partial void CreatePartCore(OpenXmlPackage openXmlPackage, string relationshipType, ref OpenXmlPart openXmlPart)
        {
            if (openXmlPackage == null)
            {
				throw new ArgumentNullException("openXmlPackage");
            }
            if (relationshipType == null)
            {
                throw new ArgumentNullException("relationshipType");
            }
            
            if (openXmlPackage is WordprocessingDocument)
            {
                switch (relationshipType)
                {
					case MainDocumentPart.RelationshipTypeConstant:
	openXmlPart = new MainDocumentPart();
	return;
case CustomXmlPart.RelationshipTypeConstant:
	openXmlPart = new CustomXmlPart();
	return;
case CustomXmlPropertiesPart.RelationshipTypeConstant:
	openXmlPart = new CustomXmlPropertiesPart();
	return;
case GlossaryDocumentPart.RelationshipTypeConstant:
	openXmlPart = new GlossaryDocumentPart();
	return;
case WordprocessingCommentsPart.RelationshipTypeConstant:
	openXmlPart = new WordprocessingCommentsPart();
	return;
case AlternativeFormatImportPart.RelationshipTypeConstant:
	openXmlPart = new AlternativeFormatImportPart();
	return;
case ChartPart.RelationshipTypeConstant:
	openXmlPart = new ChartPart();
	return;
case ChartDrawingPart.RelationshipTypeConstant:
	openXmlPart = new ChartDrawingPart();
	return;
case ImagePart.RelationshipTypeConstant:
	openXmlPart = new ImagePart();
	return;
case EmbeddedPackagePart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedPackagePart();
	return;
case ThemeOverridePart.RelationshipTypeConstant:
	openXmlPart = new ThemeOverridePart();
	return;
case ChartStylePart.RelationshipTypeConstant:
	openXmlPart = new ChartStylePart();
	return;
case ChartColorStylePart.RelationshipTypeConstant:
	openXmlPart = new ChartColorStylePart();
	return;
case DiagramColorsPart.RelationshipTypeConstant:
	openXmlPart = new DiagramColorsPart();
	return;
case DiagramDataPart.RelationshipTypeConstant:
	openXmlPart = new DiagramDataPart();
	return;
case SlidePart.RelationshipTypeConstant:
	openXmlPart = new SlidePart();
	return;
case DiagramPersistLayoutPart.RelationshipTypeConstant:
	openXmlPart = new DiagramPersistLayoutPart();
	return;
case DiagramLayoutDefinitionPart.RelationshipTypeConstant:
	openXmlPart = new DiagramLayoutDefinitionPart();
	return;
case DiagramStylePart.RelationshipTypeConstant:
	openXmlPart = new DiagramStylePart();
	return;
case EmbeddedObjectPart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedObjectPart();
	return;
case VmlDrawingPart.RelationshipTypeConstant:
	openXmlPart = new VmlDrawingPart();
	return;
case LegacyDiagramTextPart.RelationshipTypeConstant:
	openXmlPart = new LegacyDiagramTextPart();
	return;
case EmbeddedControlPersistenceBinaryDataPart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedControlPersistenceBinaryDataPart();
	return;
case NotesSlidePart.RelationshipTypeConstant:
	openXmlPart = new NotesSlidePart();
	return;
case NotesMasterPart.RelationshipTypeConstant:
	openXmlPart = new NotesMasterPart();
	return;
case ThemePart.RelationshipTypeConstant:
	openXmlPart = new ThemePart();
	return;
case UserDefinedTagsPart.RelationshipTypeConstant:
	openXmlPart = new UserDefinedTagsPart();
	return;
case SlideLayoutPart.RelationshipTypeConstant:
	openXmlPart = new SlideLayoutPart();
	return;
case SlideMasterPart.RelationshipTypeConstant:
	openXmlPart = new SlideMasterPart();
	return;
case EmbeddedControlPersistencePart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedControlPersistencePart();
	return;
case SlideSyncDataPart.RelationshipTypeConstant:
	openXmlPart = new SlideSyncDataPart();
	return;
case WorksheetPart.RelationshipTypeConstant:
	openXmlPart = new WorksheetPart();
	return;
case DrawingsPart.RelationshipTypeConstant:
	openXmlPart = new DrawingsPart();
	return;
case WebExtensionPart.RelationshipTypeConstant:
	openXmlPart = new WebExtensionPart();
	return;
case PivotTablePart.RelationshipTypeConstant:
	openXmlPart = new PivotTablePart();
	return;
case PivotTableCacheDefinitionPart.RelationshipTypeConstant:
	openXmlPart = new PivotTableCacheDefinitionPart();
	return;
case PivotTableCacheRecordsPart.RelationshipTypeConstant:
	openXmlPart = new PivotTableCacheRecordsPart();
	return;
case SingleCellTablePart.RelationshipTypeConstant:
	openXmlPart = new SingleCellTablePart();
	return;
case TableDefinitionPart.RelationshipTypeConstant:
	openXmlPart = new TableDefinitionPart();
	return;
case QueryTablePart.RelationshipTypeConstant:
	openXmlPart = new QueryTablePart();
	return;
case ControlPropertiesPart.RelationshipTypeConstant:
	openXmlPart = new ControlPropertiesPart();
	return;
case CustomPropertyPart.RelationshipTypeConstant:
	openXmlPart = new CustomPropertyPart();
	return;
case WorksheetSortMapPart.RelationshipTypeConstant:
	openXmlPart = new WorksheetSortMapPart();
	return;
case SlicersPart.RelationshipTypeConstant:
	openXmlPart = new SlicersPart();
	return;
case TimeLinePart.RelationshipTypeConstant:
	openXmlPart = new TimeLinePart();
	return;
case DocumentSettingsPart.RelationshipTypeConstant:
	openXmlPart = new DocumentSettingsPart();
	return;
case MailMergeRecipientDataPart.RelationshipTypeConstant:
	openXmlPart = new MailMergeRecipientDataPart();
	return;
case EndnotesPart.RelationshipTypeConstant:
	openXmlPart = new EndnotesPart();
	return;
case FontTablePart.RelationshipTypeConstant:
	openXmlPart = new FontTablePart();
	return;
case FontPart.RelationshipTypeConstant:
	openXmlPart = new FontPart();
	return;
case FootnotesPart.RelationshipTypeConstant:
	openXmlPart = new FootnotesPart();
	return;
case NumberingDefinitionsPart.RelationshipTypeConstant:
	openXmlPart = new NumberingDefinitionsPart();
	return;
case StyleDefinitionsPart.RelationshipTypeConstant:
	openXmlPart = new StyleDefinitionsPart();
	return;
case StylesWithEffectsPart.RelationshipTypeConstant:
	openXmlPart = new StylesWithEffectsPart();
	return;
case WebSettingsPart.RelationshipTypeConstant:
	openXmlPart = new WebSettingsPart();
	return;
case FooterPart.RelationshipTypeConstant:
	openXmlPart = new FooterPart();
	return;
case HeaderPart.RelationshipTypeConstant:
	openXmlPart = new HeaderPart();
	return;
case WordprocessingPrinterSettingsPart.RelationshipTypeConstant:
	openXmlPart = new WordprocessingPrinterSettingsPart();
	return;
case CustomizationPart.RelationshipTypeConstant:
	openXmlPart = new CustomizationPart();
	return;
case WordAttachedToolbarsPart.RelationshipTypeConstant:
	openXmlPart = new WordAttachedToolbarsPart();
	return;
case VbaProjectPart.RelationshipTypeConstant:
	openXmlPart = new VbaProjectPart();
	return;
case VbaDataPart.RelationshipTypeConstant:
	openXmlPart = new VbaDataPart();
	return;
case WordprocessingCommentsExPart.RelationshipTypeConstant:
	openXmlPart = new WordprocessingCommentsExPart();
	return;
case WordprocessingPeoplePart.RelationshipTypeConstant:
	openXmlPart = new WordprocessingPeoplePart();
	return;
case ThumbnailPart.RelationshipTypeConstant:
	openXmlPart = new ThumbnailPart();
	return;
case CoreFilePropertiesPart.RelationshipTypeConstant:
	openXmlPart = new CoreFilePropertiesPart();
	return;
case ExtendedFilePropertiesPart.RelationshipTypeConstant:
	openXmlPart = new ExtendedFilePropertiesPart();
	return;
case CustomFilePropertiesPart.RelationshipTypeConstant:
	openXmlPart = new CustomFilePropertiesPart();
	return;
case DigitalSignatureOriginPart.RelationshipTypeConstant:
	openXmlPart = new DigitalSignatureOriginPart();
	return;
case XmlSignaturePart.RelationshipTypeConstant:
	openXmlPart = new XmlSignaturePart();
	return;
case QuickAccessToolbarCustomizationsPart.RelationshipTypeConstant:
	openXmlPart = new QuickAccessToolbarCustomizationsPart();
	return;
case RibbonExtensibilityPart.RelationshipTypeConstant:
	openXmlPart = new RibbonExtensibilityPart();
	return;
case RibbonAndBackstageCustomizationsPart.RelationshipTypeConstant:
	openXmlPart = new RibbonAndBackstageCustomizationsPart();
	return;
case WebExTaskpanesPart.RelationshipTypeConstant:
	openXmlPart = new WebExTaskpanesPart();
	return;

				}
            }
            else if (openXmlPackage is SpreadsheetDocument)
            {
                switch (relationshipType)
                {
					case WorkbookPart.RelationshipTypeConstant:
	openXmlPart = new WorkbookPart();
	return;
case CustomXmlPart.RelationshipTypeConstant:
	openXmlPart = new CustomXmlPart();
	return;
case CustomXmlPropertiesPart.RelationshipTypeConstant:
	openXmlPart = new CustomXmlPropertiesPart();
	return;
case CalculationChainPart.RelationshipTypeConstant:
	openXmlPart = new CalculationChainPart();
	return;
case CellMetadataPart.RelationshipTypeConstant:
	openXmlPart = new CellMetadataPart();
	return;
case ConnectionsPart.RelationshipTypeConstant:
	openXmlPart = new ConnectionsPart();
	return;
case CustomXmlMappingsPart.RelationshipTypeConstant:
	openXmlPart = new CustomXmlMappingsPart();
	return;
case SharedStringTablePart.RelationshipTypeConstant:
	openXmlPart = new SharedStringTablePart();
	return;
case WorkbookRevisionHeaderPart.RelationshipTypeConstant:
	openXmlPart = new WorkbookRevisionHeaderPart();
	return;
case WorkbookRevisionLogPart.RelationshipTypeConstant:
	openXmlPart = new WorkbookRevisionLogPart();
	return;
case WorkbookUserDataPart.RelationshipTypeConstant:
	openXmlPart = new WorkbookUserDataPart();
	return;
case WorkbookStylesPart.RelationshipTypeConstant:
	openXmlPart = new WorkbookStylesPart();
	return;
case ThemePart.RelationshipTypeConstant:
	openXmlPart = new ThemePart();
	return;
case ImagePart.RelationshipTypeConstant:
	openXmlPart = new ImagePart();
	return;
case ThumbnailPart.RelationshipTypeConstant:
	openXmlPart = new ThumbnailPart();
	return;
case VolatileDependenciesPart.RelationshipTypeConstant:
	openXmlPart = new VolatileDependenciesPart();
	return;
case ChartsheetPart.RelationshipTypeConstant:
	openXmlPart = new ChartsheetPart();
	return;
case SpreadsheetPrinterSettingsPart.RelationshipTypeConstant:
	openXmlPart = new SpreadsheetPrinterSettingsPart();
	return;
case DrawingsPart.RelationshipTypeConstant:
	openXmlPart = new DrawingsPart();
	return;
case ChartPart.RelationshipTypeConstant:
	openXmlPart = new ChartPart();
	return;
case ChartDrawingPart.RelationshipTypeConstant:
	openXmlPart = new ChartDrawingPart();
	return;
case EmbeddedPackagePart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedPackagePart();
	return;
case ThemeOverridePart.RelationshipTypeConstant:
	openXmlPart = new ThemeOverridePart();
	return;
case ChartStylePart.RelationshipTypeConstant:
	openXmlPart = new ChartStylePart();
	return;
case ChartColorStylePart.RelationshipTypeConstant:
	openXmlPart = new ChartColorStylePart();
	return;
case DiagramColorsPart.RelationshipTypeConstant:
	openXmlPart = new DiagramColorsPart();
	return;
case DiagramDataPart.RelationshipTypeConstant:
	openXmlPart = new DiagramDataPart();
	return;
case SlidePart.RelationshipTypeConstant:
	openXmlPart = new SlidePart();
	return;
case DiagramPersistLayoutPart.RelationshipTypeConstant:
	openXmlPart = new DiagramPersistLayoutPart();
	return;
case DiagramLayoutDefinitionPart.RelationshipTypeConstant:
	openXmlPart = new DiagramLayoutDefinitionPart();
	return;
case DiagramStylePart.RelationshipTypeConstant:
	openXmlPart = new DiagramStylePart();
	return;
case EmbeddedObjectPart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedObjectPart();
	return;
case VmlDrawingPart.RelationshipTypeConstant:
	openXmlPart = new VmlDrawingPart();
	return;
case LegacyDiagramTextPart.RelationshipTypeConstant:
	openXmlPart = new LegacyDiagramTextPart();
	return;
case EmbeddedControlPersistenceBinaryDataPart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedControlPersistenceBinaryDataPart();
	return;
case NotesSlidePart.RelationshipTypeConstant:
	openXmlPart = new NotesSlidePart();
	return;
case NotesMasterPart.RelationshipTypeConstant:
	openXmlPart = new NotesMasterPart();
	return;
case UserDefinedTagsPart.RelationshipTypeConstant:
	openXmlPart = new UserDefinedTagsPart();
	return;
case SlideLayoutPart.RelationshipTypeConstant:
	openXmlPart = new SlideLayoutPart();
	return;
case SlideMasterPart.RelationshipTypeConstant:
	openXmlPart = new SlideMasterPart();
	return;
case EmbeddedControlPersistencePart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedControlPersistencePart();
	return;
case SlideSyncDataPart.RelationshipTypeConstant:
	openXmlPart = new SlideSyncDataPart();
	return;
case WorksheetPart.RelationshipTypeConstant:
	openXmlPart = new WorksheetPart();
	return;
case WorksheetCommentsPart.RelationshipTypeConstant:
	openXmlPart = new WorksheetCommentsPart();
	return;
case PivotTablePart.RelationshipTypeConstant:
	openXmlPart = new PivotTablePart();
	return;
case PivotTableCacheDefinitionPart.RelationshipTypeConstant:
	openXmlPart = new PivotTableCacheDefinitionPart();
	return;
case PivotTableCacheRecordsPart.RelationshipTypeConstant:
	openXmlPart = new PivotTableCacheRecordsPart();
	return;
case SingleCellTablePart.RelationshipTypeConstant:
	openXmlPart = new SingleCellTablePart();
	return;
case TableDefinitionPart.RelationshipTypeConstant:
	openXmlPart = new TableDefinitionPart();
	return;
case QueryTablePart.RelationshipTypeConstant:
	openXmlPart = new QueryTablePart();
	return;
case ControlPropertiesPart.RelationshipTypeConstant:
	openXmlPart = new ControlPropertiesPart();
	return;
case CustomPropertyPart.RelationshipTypeConstant:
	openXmlPart = new CustomPropertyPart();
	return;
case WorksheetSortMapPart.RelationshipTypeConstant:
	openXmlPart = new WorksheetSortMapPart();
	return;
case SlicersPart.RelationshipTypeConstant:
	openXmlPart = new SlicersPart();
	return;
case TimeLinePart.RelationshipTypeConstant:
	openXmlPart = new TimeLinePart();
	return;
case WebExtensionPart.RelationshipTypeConstant:
	openXmlPart = new WebExtensionPart();
	return;
case DialogsheetPart.RelationshipTypeConstant:
	openXmlPart = new DialogsheetPart();
	return;
case ExternalWorkbookPart.RelationshipTypeConstant:
	openXmlPart = new ExternalWorkbookPart();
	return;
case ExcelAttachedToolbarsPart.RelationshipTypeConstant:
	openXmlPart = new ExcelAttachedToolbarsPart();
	return;
case VbaProjectPart.RelationshipTypeConstant:
	openXmlPart = new VbaProjectPart();
	return;
case VbaDataPart.RelationshipTypeConstant:
	openXmlPart = new VbaDataPart();
	return;
case MacroSheetPart.RelationshipTypeConstant:
	openXmlPart = new MacroSheetPart();
	return;
case InternationalMacroSheetPart.RelationshipTypeConstant:
	openXmlPart = new InternationalMacroSheetPart();
	return;
case CustomDataPropertiesPart.RelationshipTypeConstant:
	openXmlPart = new CustomDataPropertiesPart();
	return;
case CustomDataPart.RelationshipTypeConstant:
	openXmlPart = new CustomDataPart();
	return;
case SlicerCachePart.RelationshipTypeConstant:
	openXmlPart = new SlicerCachePart();
	return;
case TimeLineCachePart.RelationshipTypeConstant:
	openXmlPart = new TimeLineCachePart();
	return;
case CoreFilePropertiesPart.RelationshipTypeConstant:
	openXmlPart = new CoreFilePropertiesPart();
	return;
case ExtendedFilePropertiesPart.RelationshipTypeConstant:
	openXmlPart = new ExtendedFilePropertiesPart();
	return;
case CustomFilePropertiesPart.RelationshipTypeConstant:
	openXmlPart = new CustomFilePropertiesPart();
	return;
case DigitalSignatureOriginPart.RelationshipTypeConstant:
	openXmlPart = new DigitalSignatureOriginPart();
	return;
case XmlSignaturePart.RelationshipTypeConstant:
	openXmlPart = new XmlSignaturePart();
	return;
case QuickAccessToolbarCustomizationsPart.RelationshipTypeConstant:
	openXmlPart = new QuickAccessToolbarCustomizationsPart();
	return;
case RibbonExtensibilityPart.RelationshipTypeConstant:
	openXmlPart = new RibbonExtensibilityPart();
	return;
case RibbonAndBackstageCustomizationsPart.RelationshipTypeConstant:
	openXmlPart = new RibbonAndBackstageCustomizationsPart();
	return;
case WebExTaskpanesPart.RelationshipTypeConstant:
	openXmlPart = new WebExTaskpanesPart();
	return;

                }
            }
            else if (openXmlPackage is PresentationDocument)
            {
                switch (relationshipType)
                {
					case PresentationPart.RelationshipTypeConstant:
	openXmlPart = new PresentationPart();
	return;
case CustomXmlPart.RelationshipTypeConstant:
	openXmlPart = new CustomXmlPart();
	return;
case CustomXmlPropertiesPart.RelationshipTypeConstant:
	openXmlPart = new CustomXmlPropertiesPart();
	return;
case FontPart.RelationshipTypeConstant:
	openXmlPart = new FontPart();
	return;
case PresentationPropertiesPart.RelationshipTypeConstant:
	openXmlPart = new PresentationPropertiesPart();
	return;
case TableStylesPart.RelationshipTypeConstant:
	openXmlPart = new TableStylesPart();
	return;
case ThemePart.RelationshipTypeConstant:
	openXmlPart = new ThemePart();
	return;
case ImagePart.RelationshipTypeConstant:
	openXmlPart = new ImagePart();
	return;
case ViewPropertiesPart.RelationshipTypeConstant:
	openXmlPart = new ViewPropertiesPart();
	return;
case SlidePart.RelationshipTypeConstant:
	openXmlPart = new SlidePart();
	return;
case ChartPart.RelationshipTypeConstant:
	openXmlPart = new ChartPart();
	return;
case ChartDrawingPart.RelationshipTypeConstant:
	openXmlPart = new ChartDrawingPart();
	return;
case EmbeddedPackagePart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedPackagePart();
	return;
case ThemeOverridePart.RelationshipTypeConstant:
	openXmlPart = new ThemeOverridePart();
	return;
case ChartStylePart.RelationshipTypeConstant:
	openXmlPart = new ChartStylePart();
	return;
case ChartColorStylePart.RelationshipTypeConstant:
	openXmlPart = new ChartColorStylePart();
	return;
case DiagramColorsPart.RelationshipTypeConstant:
	openXmlPart = new DiagramColorsPart();
	return;
case DiagramDataPart.RelationshipTypeConstant:
	openXmlPart = new DiagramDataPart();
	return;
case WorksheetPart.RelationshipTypeConstant:
	openXmlPart = new WorksheetPart();
	return;
case DrawingsPart.RelationshipTypeConstant:
	openXmlPart = new DrawingsPart();
	return;
case DiagramPersistLayoutPart.RelationshipTypeConstant:
	openXmlPart = new DiagramPersistLayoutPart();
	return;
case DiagramLayoutDefinitionPart.RelationshipTypeConstant:
	openXmlPart = new DiagramLayoutDefinitionPart();
	return;
case DiagramStylePart.RelationshipTypeConstant:
	openXmlPart = new DiagramStylePart();
	return;
case WebExtensionPart.RelationshipTypeConstant:
	openXmlPart = new WebExtensionPart();
	return;
case VmlDrawingPart.RelationshipTypeConstant:
	openXmlPart = new VmlDrawingPart();
	return;
case LegacyDiagramTextPart.RelationshipTypeConstant:
	openXmlPart = new LegacyDiagramTextPart();
	return;
case PivotTablePart.RelationshipTypeConstant:
	openXmlPart = new PivotTablePart();
	return;
case PivotTableCacheDefinitionPart.RelationshipTypeConstant:
	openXmlPart = new PivotTableCacheDefinitionPart();
	return;
case PivotTableCacheRecordsPart.RelationshipTypeConstant:
	openXmlPart = new PivotTableCacheRecordsPart();
	return;
case SingleCellTablePart.RelationshipTypeConstant:
	openXmlPart = new SingleCellTablePart();
	return;
case TableDefinitionPart.RelationshipTypeConstant:
	openXmlPart = new TableDefinitionPart();
	return;
case QueryTablePart.RelationshipTypeConstant:
	openXmlPart = new QueryTablePart();
	return;
case EmbeddedControlPersistencePart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedControlPersistencePart();
	return;
case EmbeddedControlPersistenceBinaryDataPart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedControlPersistenceBinaryDataPart();
	return;
case ControlPropertiesPart.RelationshipTypeConstant:
	openXmlPart = new ControlPropertiesPart();
	return;
case EmbeddedObjectPart.RelationshipTypeConstant:
	openXmlPart = new EmbeddedObjectPart();
	return;
case CustomPropertyPart.RelationshipTypeConstant:
	openXmlPart = new CustomPropertyPart();
	return;
case WorksheetSortMapPart.RelationshipTypeConstant:
	openXmlPart = new WorksheetSortMapPart();
	return;
case SlicersPart.RelationshipTypeConstant:
	openXmlPart = new SlicersPart();
	return;
case TimeLinePart.RelationshipTypeConstant:
	openXmlPart = new TimeLinePart();
	return;
case SlideCommentsPart.RelationshipTypeConstant:
	openXmlPart = new SlideCommentsPart();
	return;
case NotesSlidePart.RelationshipTypeConstant:
	openXmlPart = new NotesSlidePart();
	return;
case NotesMasterPart.RelationshipTypeConstant:
	openXmlPart = new NotesMasterPart();
	return;
case UserDefinedTagsPart.RelationshipTypeConstant:
	openXmlPart = new UserDefinedTagsPart();
	return;
case SlideLayoutPart.RelationshipTypeConstant:
	openXmlPart = new SlideLayoutPart();
	return;
case SlideMasterPart.RelationshipTypeConstant:
	openXmlPart = new SlideMasterPart();
	return;
case SlideSyncDataPart.RelationshipTypeConstant:
	openXmlPart = new SlideSyncDataPart();
	return;
case CommentAuthorsPart.RelationshipTypeConstant:
	openXmlPart = new CommentAuthorsPart();
	return;
case HandoutMasterPart.RelationshipTypeConstant:
	openXmlPart = new HandoutMasterPart();
	return;
case LegacyDiagramTextInfoPart.RelationshipTypeConstant:
	openXmlPart = new LegacyDiagramTextInfoPart();
	return;
case VbaProjectPart.RelationshipTypeConstant:
	openXmlPart = new VbaProjectPart();
	return;
case VbaDataPart.RelationshipTypeConstant:
	openXmlPart = new VbaDataPart();
	return;
case CoreFilePropertiesPart.RelationshipTypeConstant:
	openXmlPart = new CoreFilePropertiesPart();
	return;
case ExtendedFilePropertiesPart.RelationshipTypeConstant:
	openXmlPart = new ExtendedFilePropertiesPart();
	return;
case CustomFilePropertiesPart.RelationshipTypeConstant:
	openXmlPart = new CustomFilePropertiesPart();
	return;
case ThumbnailPart.RelationshipTypeConstant:
	openXmlPart = new ThumbnailPart();
	return;
case DigitalSignatureOriginPart.RelationshipTypeConstant:
	openXmlPart = new DigitalSignatureOriginPart();
	return;
case XmlSignaturePart.RelationshipTypeConstant:
	openXmlPart = new XmlSignaturePart();
	return;
case QuickAccessToolbarCustomizationsPart.RelationshipTypeConstant:
	openXmlPart = new QuickAccessToolbarCustomizationsPart();
	return;
case RibbonExtensibilityPart.RelationshipTypeConstant:
	openXmlPart = new RibbonExtensibilityPart();
	return;
case RibbonAndBackstageCustomizationsPart.RelationshipTypeConstant:
	openXmlPart = new RibbonAndBackstageCustomizationsPart();
	return;
case WebExTaskpanesPart.RelationshipTypeConstant:
	openXmlPart = new WebExTaskpanesPart();
	return;

                }
            }
            else
            {
                System.Diagnostics.Debug.Assert(false);				
            }
            return;
        }
示例#30
0
 static string GetCellValue(WorksheetPart worksheetPart, SharedStringTablePart stringTablePart, string startCol, string startRow)
 {
     string reference = startCol + startRow;
     //get exact cell based on reference
     IEnumerable<Cell> cell = worksheetPart.Worksheet.Descendants<Cell>()
     .Where(c => reference.Equals(c.CellReference));
     if (cell.Count() != 0)
         return GetValue(cell.First(), stringTablePart);
     else
         return null;
 }