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++; } } } }
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; }
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; }
//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; }
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; }
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); }
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; }
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; }
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; }
// 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; }
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; }
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; }
/// <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); }
//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; }
Ā Ā Ā Ā Ā Ā Ā Ā /// å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; Ā Ā Ā Ā Ā Ā Ā Ā }
/// <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; } } }
protected abstract void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart);
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; }
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; }
/// <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; }
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; }