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++; } } } }
internal void ForceSaveToSharedStringTable(SharedStringItem ssi) { int index = listSharedString.Count; string sHash = SLTool.RemoveNamespaceDeclaration(ssi.InnerXml); listSharedString.Add(sHash); dictSharedStringHash[sHash] = index; }
/// <summary> /// Get existing shared strings in a list of SharedStringItem objects. WARNING: This is only a snapshot. Any changes made to the returned result are not used. /// </summary> /// <returns>A list of existing SharedStringItem objects.</returns> public List<SharedStringItem> GetSharedStringItems() { List<SharedStringItem> result = new List<SharedStringItem>(); SharedStringItem ssi; for (int i = 0; i < listSharedString.Count; ++i) { ssi = new SharedStringItem(); ssi.InnerXml = listSharedString[i]; result.Add(ssi); } return result; }
// 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; }
internal SharedStringDef(SharedStringItem itm, Int32 ix) { Item = itm; iIndex = ix; }
/// <summary> /// This method get the content of the Excel file /// </summary> /// <param name="file">Excel File</param> /// <returns>Table of the content of the Excel file</returns> /// <author>Edwin (Origin) - Abigail Rodriguez(Edit)</author> public ActionResult ImpExcel(HttpPostedFileBase file) { Dictionary <string, int> orderCell = new Dictionary <string, int>(); string[] arrayalf = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; for (int i = 0; i < arrayalf.Length; i++) { orderCell.Add(arrayalf[i], i); } DataSet ds = new DataSet(); List <List <string> > tr = new List <List <string> >(); try { if (Request.Files["file"].ContentLength > 0 /* && (idcategory != "0" && idcategory != null)*/) { string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName); if (fileExtension == ".xls" || fileExtension == ".xlsx") { string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName; if (System.IO.File.Exists(fileLocation)) { System.IO.File.Delete(fileLocation); } Request.Files["file"].SaveAs(fileLocation); } string fileLocation2 = Server.MapPath("~/Content/") + Request.Files["file"].FileName; if (System.IO.File.Exists(fileLocation2)) { System.IO.File.Delete(fileLocation2); } Request.Files["file"].SaveAs(fileLocation2); using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileLocation2, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last(); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();; foreach (Row r in sheetData.Elements <Row>()) { List <string> td = new List <string>(); int index = 0; foreach (Cell c in r.Elements <Cell>()) { string cellIndex = c.CellReference.ToString().Substring(0, 1); bool validate = false; int numcellx = 0; foreach (var x in orderCell) { if (x.Key == cellIndex) { numcellx = x.Value; } if (x.Key == cellIndex && x.Value == index) { validate = true; break; } } if (validate == false) { numcellx = numcellx - index; for (int i = 0; i < numcellx; i++) { td.Add(""); } index = index + numcellx; } Int32 id = -1; if (c.DataType != null && c.DataType.Value == CellValues.SharedString) { if (Int32.TryParse(c.InnerText, out id)) { SharedStringItem item = GetSharedStringItemById(workbookPart, id); if (item.Text != null) { td.Add(item.Text.Text); } else if (item.InnerText != null) { td.Add(item.InnerText); } else if (item.InnerXml != null) { td.Add(item.InnerXml); } } else { td.Add(c.CellValue.Text); } } else { try { td.Add(c.CellValue.Text); } catch (Exception ex) { td.Add(""); } } index++; } tr.Add(td); } spreadsheetDocument.Close(); } List <List <string> > data = new List <List <string> >(); ViewData["categoriedata"] = data; ViewData["Filelocation"] = fileLocation2; ViewData["Filerequest"] = file; return(View(tr)); } else { return(null); } } catch (Exception ex) { return(null); } }
private static void ReplaceRefInFormulaToValue(ExcelFormula excelFormula, WorksheetPart workSheetPart, SpreadsheetDocument RecultDoc) { SharedStringTablePart shareStringTablePart = RecultDoc.WorkbookPart.SharedStringTablePart; for (int i = 0; i < excelFormula.Count; i++) { ExcelFormulaToken token = excelFormula[i]; if (token.Subtype == ExcelFormulaTokenSubtype.Reference) { try { Cell fCell = FindCell(token.Value, workSheetPart); if (fCell.CellValue != null) { if (fCell.DataType.Value == CellValues.SharedString) { int itemIndex = int.Parse(fCell.CellValue.Text); if (shareStringTablePart != null) { SharedStringItem item = shareStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(itemIndex); if (item != null) { token.Value = item.Text.Text; } } } else { token.Value = fCell.CellValue.Text; } } else if (fCell.CellFormula != null) { token.Value = calcFormule(fCell, workSheetPart, RecultDoc, false); } else { token.Value = ""; } double d; if (double.TryParse(token.Value, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.CreateSpecificCulture("en-US"), out d)) { token.Subtype = ExcelFormulaTokenSubtype.Number; } else { token.Subtype = ExcelFormulaTokenSubtype.Text; } } catch (System.Exception ex) { ; } finally { ; } } if (token.Subtype == ExcelFormulaTokenSubtype.Range) { try { string str = ""; foreach (Cell c in FindCells(token.Value, workSheetPart)) { if (c.CellValue != null) { if (c.DataType.Value == CellValues.SharedString) { int itemIndex = int.Parse(c.CellValue.Text); if (shareStringTablePart != null) { SharedStringItem item = shareStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(itemIndex); if (item != null) { str += item.Text.Text + ","; } } } else { str += c.CellValue.Text + ","; } } else if (c.CellFormula != null) { str += calcFormule(c, workSheetPart, RecultDoc, false) + ","; } } token.Value = str.TrimEnd(','); } catch (System.Exception ex) { ; } finally { ; } } } }
private static string GetFormattedCellValue(WorkbookPart workbookPart, Cell cell) { if (cell == null || cell.CellValue == null || string.IsNullOrEmpty(cell.CellValue.Text)) { return(""); } var numberingFormats = workbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats; string value = ""; try { if (cell.DataType == null) // number & dates { int styleIndex = (int)cell.StyleIndex.Value; if (cell.StyleIndex == null) { return(cell.CellValue.Text); } CellFormat cellFormat = (CellFormat)workbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt(styleIndex); uint formatId = cellFormat.NumberFormatId.Value; if (formatId >= 14 && formatId <= 22) { double oaDate; if (double.TryParse(cell.CellValue.Text, out oaDate)) { value = DateTime.FromOADate(oaDate).ToString("yyyy-MM-dd HH:mm:ss"); } } else if (formatId == 0) { value = cell.CellValue.Text; } else { NumberingFormat numberingFormat = null; try { numberingFormat = numberingFormats.Cast <NumberingFormat>() .SingleOrDefault(f => f.NumberFormatId.Value == formatId); } catch (Exception e) { } if (numberingFormat != null && numberingFormat.FormatCode.Value.Contains("yy")) { double oaDate; if (double.TryParse(cell.CellValue.Text, out oaDate)) { value = DateTime.FromOADate(oaDate).ToString("yyyy-MM-dd HH:mm:ss"); } } else { value = cell.CellValue.Text; } } } else // Shared string or boolean { switch (cell.DataType.Value) { case CellValues.SharedString: SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(int.Parse(cell.CellValue.Text)); value = ssi.Text.Text; break; case CellValues.Boolean: value = cell.CellValue.Text == "0" ? "false" : "true"; break; default: value = cell.CellValue.Text; break; } } } catch (Exception ex) { return(""); } return(value); }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable(){ Count = (UInt32Value)128U, UniqueCount = (UInt32Value)52U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "FECHA DE EMISION:"; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "MEDIO"; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "PROGRAMA:"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "CONTACTO:"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "TELEFONO/ Fax:"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "DIRECCION:"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "E-MAIL:"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(){ Space = SpaceProcessingModeValues.Preserve }; text8.Text = "Gerencia DRTV - Marketing "; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "SPRAYETTE S.A"; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "IDELMA A. ALVAREZ"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "AMERICA TV"; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "INTRUSOS"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "50 SEG"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "SALIDA"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "DURACION"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "LUNES"; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "MARTES"; sharedStringItem17.Append(text17); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "MIÉRCOLES"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "JUEVES"; sharedStringItem19.Append(text19); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "VIERNES"; sharedStringItem20.Append(text20); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "SÁBADO"; sharedStringItem21.Append(text21); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "DOMINGO"; sharedStringItem22.Append(text22); SharedStringItem sharedStringItem23 = new SharedStringItem(); Text text23 = new Text(); text23.Text = "SALIDA 1"; sharedStringItem23.Append(text23); SharedStringItem sharedStringItem24 = new SharedStringItem(); Text text24 = new Text(); text24.Text = "SUBTOTAL"; sharedStringItem24.Append(text24); SharedStringItem sharedStringItem25 = new SharedStringItem(); Text text25 = new Text(); text25.Text = "HORARIO"; sharedStringItem25.Append(text25); SharedStringItem sharedStringItem26 = new SharedStringItem(); Text text26 = new Text(); text26.Text = "PNTS TOTALES:"; sharedStringItem26.Append(text26); SharedStringItem sharedStringItem27 = new SharedStringItem(); Text text27 = new Text(); text27.Text = "COSTO POR SALIDA"; sharedStringItem27.Append(text27); SharedStringItem sharedStringItem28 = new SharedStringItem(); Text text28 = new Text(); text28.Text = "IVA 21%:"; sharedStringItem28.Append(text28); SharedStringItem sharedStringItem29 = new SharedStringItem(); Text text29 = new Text(); text29.Text = "TOTAL"; sharedStringItem29.Append(text29); SharedStringItem sharedStringItem30 = new SharedStringItem(); Text text30 = new Text(){ Space = SpaceProcessingModeValues.Preserve }; text30.Text = "Av. Corrientes 6277 ( 1427) Buenos Aires "; sharedStringItem30.Append(text30); SharedStringItem sharedStringItem31 = new SharedStringItem(); Text text31 = new Text(); text31.Text = "Argentina - Tel.: 4323-9931"; sharedStringItem31.Append(text31); SharedStringItem sharedStringItem32 = new SharedStringItem(); Text text32 = new Text(); text32.Text = "ORDEN DE PUBLICIDAD"; sharedStringItem32.Append(text32); SharedStringItem sharedStringItem33 = new SharedStringItem(); Text text33 = new Text(); text33.Text = "Orden De Publicidad - INTRUSOS"; sharedStringItem33.Append(text33); SharedStringItem sharedStringItem34 = new SharedStringItem(); Text text34 = new Text(); text34.Text = "PRODUCTO"; sharedStringItem34.Append(text34); SharedStringItem sharedStringItem35 = new SharedStringItem(); Text text35 = new Text(); text35.Text = "EMPRESA"; sharedStringItem35.Append(text35); SharedStringItem sharedStringItem36 = new SharedStringItem(); Text text36 = new Text(); text36.Text = "ZOCALO"; sharedStringItem36.Append(text36); SharedStringItem sharedStringItem37 = new SharedStringItem(); Text text37 = new Text(); text37.Text = "COD. INGESTA"; sharedStringItem37.Append(text37); SharedStringItem sharedStringItem38 = new SharedStringItem(); Text text38 = new Text(); text38.Text = "SALIDAS"; sharedStringItem38.Append(text38); SharedStringItem sharedStringItem39 = new SharedStringItem(); Text text39 = new Text(); text39.Text = "PHILIPS JUICER (GRABADO) cod. 10253"; sharedStringItem39.Append(text39); SharedStringItem sharedStringItem40 = new SharedStringItem(); Text text40 = new Text(); text40.Text = "POLISHOP"; sharedStringItem40.Append(text40); SharedStringItem sharedStringItem41 = new SharedStringItem(); Text text41 = new Text(); text41.Text = "INCLUIDO"; sharedStringItem41.Append(text41); SharedStringItem sharedStringItem42 = new SharedStringItem(); Text text42 = new Text(); text42.Text = "STEP GYM cod. 1652"; sharedStringItem42.Append(text42); SharedStringItem sharedStringItem43 = new SharedStringItem(); Text text43 = new Text(); text43.Text = "SPRAYETTE"; sharedStringItem43.Append(text43); SharedStringItem sharedStringItem44 = new SharedStringItem(); Text text44 = new Text(); text44.Text = "MY STIM 2 cod. 1653"; sharedStringItem44.Append(text44); SharedStringItem sharedStringItem45 = new SharedStringItem(); Text text45 = new Text(); text45.Text = "LA BAGUETTE (GRABADO) cod. 10254"; sharedStringItem45.Append(text45); SharedStringItem sharedStringItem46 = new SharedStringItem(); Text text46 = new Text(); text46.Text = "NUMERO"; sharedStringItem46.Append(text46); SharedStringItem sharedStringItem47 = new SharedStringItem(); Text text47 = new Text(); text47.Text = "0800-444-1407"; sharedStringItem47.Append(text47); SharedStringItem sharedStringItem48 = new SharedStringItem(); Text text48 = new Text(); text48.Text = "EXCLUSIVE"; sharedStringItem48.Append(text48); SharedStringItem sharedStringItem49 = new SharedStringItem(); Text text49 = new Text(); text49.Text = "0800-444-1429"; sharedStringItem49.Append(text49); SharedStringItem sharedStringItem50 = new SharedStringItem(); Text text50 = new Text(); text50.Text = "0800-444-1416"; sharedStringItem50.Append(text50); SharedStringItem sharedStringItem51 = new SharedStringItem(); Text text51 = new Text(); text51.Text = "FEBRERO"; sharedStringItem51.Append(text51); SharedStringItem sharedStringItem52 = new SharedStringItem(); Text text52 = new Text(); text52.Text = "COMFORTISSE BRA (GRABADO) cod. 1880"; sharedStringItem52.Append(text52); 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); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
public int RemoveSharedStringItem(int sharedStringId) { bool remove = true; if (this.workbook == null || this.workbook.Workbook == null) { Console.WriteLine("Error: This spreadsheet has no workbook!"); return(-1); } foreach (var part in this.workbook.GetPartsOfType <WorksheetPart>()) { Worksheet worksheet = part.Worksheet; foreach (var cell in worksheet.GetFirstChild <SheetData>().Descendants <Cell>()) { if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString && cell.CellValue.Text == sharedStringId.ToString()) { remove = false; break; } } if (!remove) { break; } } if (remove) { if (this.sharedStrings == null) { Console.WriteLine("Error: This spreadsheet has no sharedString table!"); return(-1); } SharedStringItem item = this.sharedStrings.SharedStringTable .Elements <SharedStringItem>().ElementAt(sharedStringId); if (item != null) { item.Remove(); // Refresh all the shared string references. foreach (var part in this.workbook.GetPartsOfType <WorksheetPart>()) { Worksheet worksheet = part.Worksheet; foreach (var cell in worksheet.GetFirstChild <SheetData>().Descendants <Cell>()) { if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) { int itemIndex = int.Parse(cell.CellValue.Text); if (itemIndex > sharedStringId) { cell.CellValue.Text = (itemIndex - 1).ToString(); } } } worksheet.Save(); } this.sharedStrings.SharedStringTable.Save(); } else { Console.WriteLine("Warning: No item found at {0}!", sharedStringId); return(-1); } } else { Console.WriteLine("Warning: No shared string item deleted!"); return(-1); } return(sharedStringId); }
private bool SetValueToProperty <T>(T entity, PropertyInfo propertyInfo, Cell thecurrentcell, WorkbookPart workbookPart) where T : new() { //statement to take the integer value string currentcellvalue = string.Empty; if (thecurrentcell.DataType != null && thecurrentcell.DataType == CellValues.SharedString) { int id; if (Int32.TryParse(thecurrentcell.InnerText, out id)) { SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id); if (item.Text != null) { //code to take the string value currentcellvalue = item.Text.Text + " "; } else if (item.InnerText != null) { currentcellvalue = item.InnerText; } else if (item.InnerXml != null) { currentcellvalue = item.InnerXml; } } } else { currentcellvalue = thecurrentcell.InnerText + " "; } if (!String.IsNullOrEmpty(currentcellvalue)) { // Integer type if (propertyInfo.PropertyType.Equals(typeof(int))) { int id; if (Int32.TryParse(currentcellvalue, out id)) { propertyInfo.SetValue(entity, id); } else { return(false); } } else if (propertyInfo.PropertyType.Equals(typeof(Int64))) { Int64 id; if (Int64.TryParse(currentcellvalue, out id)) { propertyInfo.SetValue(entity, id); } else { return(false); } } // String type else { var removeBlanks = propertyInfo.GetCustomAttribute <RemoveBlanksAttribute>(); if (removeBlanks != null) { currentcellvalue = currentcellvalue.Replace(" ", ""); } propertyInfo.SetValue(entity, currentcellvalue); } return(true); } return(false); }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)7U, UniqueCount = (UInt32Value)7U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "Formatted Text"; sharedStringItem1.Append(text16); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "Now is the time for all good men to come to the aid of their country."; sharedStringItem2.Append(text17); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "Second sheet"; sharedStringItem3.Append(text18); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "a"; sharedStringItem4.Append(text19); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "b"; sharedStringItem5.Append(text20); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "c"; sharedStringItem6.Append(text21); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "www.ericwhite.com"; sharedStringItem7.Append(text22); sharedStringTable1.Append(sharedStringItem1); sharedStringTable1.Append(sharedStringItem2); sharedStringTable1.Append(sharedStringItem3); sharedStringTable1.Append(sharedStringItem4); sharedStringTable1.Append(sharedStringItem5); sharedStringTable1.Append(sharedStringItem6); sharedStringTable1.Append(sharedStringItem7); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable(){ Count = (UInt32Value)18U, UniqueCount = (UInt32Value)15U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "TEST_A_1"; PhoneticProperties phoneticProperties1 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem1.Append(text1); sharedStringItem1.Append(phoneticProperties1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "TEST_A_2"; PhoneticProperties phoneticProperties2 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem2.Append(text2); sharedStringItem2.Append(phoneticProperties2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "A"; PhoneticProperties phoneticProperties3 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem3.Append(text3); sharedStringItem3.Append(phoneticProperties3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "B"; PhoneticProperties phoneticProperties4 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem4.Append(text4); sharedStringItem4.Append(phoneticProperties4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "TEST_B_1"; PhoneticProperties phoneticProperties5 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem5.Append(text5); sharedStringItem5.Append(phoneticProperties5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "TEST_B_2"; PhoneticProperties phoneticProperties6 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem6.Append(text6); sharedStringItem6.Append(phoneticProperties6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "TETS_A_3"; PhoneticProperties phoneticProperties7 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem7.Append(text7); sharedStringItem7.Append(phoneticProperties7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "C"; PhoneticProperties phoneticProperties8 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem8.Append(text8); sharedStringItem8.Append(phoneticProperties8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "TEST_B_3"; PhoneticProperties phoneticProperties9 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem9.Append(text9); sharedStringItem9.Append(phoneticProperties9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "D"; PhoneticProperties phoneticProperties10 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem10.Append(text10); sharedStringItem10.Append(phoneticProperties10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "E"; PhoneticProperties phoneticProperties11 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem11.Append(text11); sharedStringItem11.Append(phoneticProperties11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "F"; PhoneticProperties phoneticProperties12 = new PhoneticProperties(){ FontId = (UInt32Value)1U }; sharedStringItem12.Append(text12); sharedStringItem12.Append(phoneticProperties12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "Column1"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "Column2"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "Column3"; sharedStringItem15.Append(text15); 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); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable(){ Count = (UInt32Value)130U, UniqueCount = (UInt32Value)32U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "Date"; PhoneticRun phoneticRun1 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U }; Text text8 = new Text(); text8.Text = ""; phoneticRun1.Append(text8); PhoneticProperties phoneticProperties4 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana }; sharedStringItem1.Append(text7); sharedStringItem1.Append(phoneticRun1); sharedStringItem1.Append(phoneticProperties4); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "Product"; PhoneticRun phoneticRun2 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U }; Text text10 = new Text(); text10.Text = ""; phoneticRun2.Append(text10); PhoneticProperties phoneticProperties5 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana }; sharedStringItem2.Append(text9); sharedStringItem2.Append(phoneticRun2); sharedStringItem2.Append(phoneticProperties5); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "Quantity"; PhoneticRun phoneticRun3 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U }; Text text12 = new Text(); text12.Text = ""; phoneticRun3.Append(text12); PhoneticProperties phoneticProperties6 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana }; sharedStringItem3.Append(text11); sharedStringItem3.Append(phoneticRun3); sharedStringItem3.Append(phoneticProperties6); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "Price"; PhoneticRun phoneticRun4 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U }; Text text14 = new Text(); text14.Text = ""; phoneticRun4.Append(text14); PhoneticProperties phoneticProperties7 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana }; sharedStringItem4.Append(text13); sharedStringItem4.Append(phoneticRun4); sharedStringItem4.Append(phoneticProperties7); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "DeliveryDate"; sharedStringItem5.Append(text15); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "Employee"; PhoneticRun phoneticRun5 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U }; Text text17 = new Text(); text17.Text = ""; phoneticRun5.Append(text17); PhoneticProperties phoneticProperties8 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana }; sharedStringItem6.Append(text16); sharedStringItem6.Append(phoneticRun5); sharedStringItem6.Append(phoneticProperties8); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "product_A"; sharedStringItem7.Append(text18); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "sales staff G"; sharedStringItem8.Append(text19); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "product_D"; sharedStringItem9.Append(text20); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "sales staff B"; sharedStringItem10.Append(text21); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "sales staff C"; sharedStringItem11.Append(text22); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text23 = new Text(); text23.Text = "product_E"; sharedStringItem12.Append(text23); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text24 = new Text(); text24.Text = "sales staff E"; sharedStringItem13.Append(text24); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text25 = new Text(); text25.Text = "sales staff J"; sharedStringItem14.Append(text25); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text26 = new Text(); text26.Text = "product_B"; sharedStringItem15.Append(text26); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text27 = new Text(); text27.Text = "sales staff I"; sharedStringItem16.Append(text27); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text28 = new Text(); text28.Text = "product_C"; sharedStringItem17.Append(text28); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text29 = new Text(); text29.Text = "product_H"; sharedStringItem18.Append(text29); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text30 = new Text(); text30.Text = "product_I"; sharedStringItem19.Append(text30); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text31 = new Text(); text31.Text = "sales staff H"; sharedStringItem20.Append(text31); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text32 = new Text(); text32.Text = "sales staff A"; sharedStringItem21.Append(text32); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text33 = new Text(); text33.Text = "sales staff F"; sharedStringItem22.Append(text33); SharedStringItem sharedStringItem23 = new SharedStringItem(); Text text34 = new Text(); text34.Text = "product_F"; sharedStringItem23.Append(text34); SharedStringItem sharedStringItem24 = new SharedStringItem(); Text text35 = new Text(); text35.Text = "product_G"; sharedStringItem24.Append(text35); SharedStringItem sharedStringItem25 = new SharedStringItem(); Text text36 = new Text(); text36.Text = "sales staff D"; sharedStringItem25.Append(text36); SharedStringItem sharedStringItem26 = new SharedStringItem(); Text text37 = new Text(); text37.Text = "product_J"; sharedStringItem26.Append(text37); SharedStringItem sharedStringItem27 = new SharedStringItem(); Text text38 = new Text(); text38.Text = "ProductName"; PhoneticRun phoneticRun6 = new PhoneticRun(){ BaseTextStartIndex = (UInt32Value)0U, EndingBaseIndex = (UInt32Value)0U }; Text text39 = new Text(); text39.Text = ""; phoneticRun6.Append(text39); PhoneticProperties phoneticProperties9 = new PhoneticProperties(){ FontId = (UInt32Value)0U, Type = PhoneticValues.Hiragana }; sharedStringItem27.Append(text38); sharedStringItem27.Append(phoneticRun6); sharedStringItem27.Append(phoneticProperties9); SharedStringItem sharedStringItem28 = new SharedStringItem(); Text text40 = new Text(); text40.Text = "Employee"; sharedStringItem28.Append(text40); SharedStringItem sharedStringItem29 = new SharedStringItem(); Text text41 = new Text(); text41.Text = "Sum of Quantity"; sharedStringItem29.Append(text41); SharedStringItem sharedStringItem30 = new SharedStringItem(); Text text42 = new Text(); text42.Text = "Row Labels"; sharedStringItem30.Append(text42); SharedStringItem sharedStringItem31 = new SharedStringItem(); Text text43 = new Text(); text43.Text = "Grand Total"; sharedStringItem31.Append(text43); SharedStringItem sharedStringItem32 = new SharedStringItem(); Text text44 = new Text(); text44.Text = "Toral / Price"; sharedStringItem32.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); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
/// <summary> /// /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="r"></param> /// <returns></returns> private List <string> rowToList(Row r) { string[] rowAsStringArray = new string[numOfColumns]; // create a new cell Cell c = new Cell(); for (int i = 0; i < r.ChildElements.Count(); i++) { // get current cell at i c = r.Elements <Cell>().ElementAt(i); string value = ""; if (c != null) { if (c.CellValue != null) { // if cell reference in range of the area int cellReferencAsInterger = getColumnNumber(getColumnName(c.CellReference)); int start = getColumnNumber(this.areaOfData.StartColumn); int end = getColumnNumber(this.areaOfData.EndColumn); if (cellReferencAsInterger >= start && cellReferencAsInterger <= end) { // if Value a text if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.SharedString) { int sharedStringIndex = int.Parse(c.CellValue.Text, CultureInfo.InvariantCulture); SharedStringItem sharedStringItem = sharedStrings[sharedStringIndex]; value = sharedStringItem.InnerText; } // not a text else if (c.StyleIndex != null && c.StyleIndex.HasValue) { uint styleIndex = c.StyleIndex.Value; CellFormat cellFormat = stylesheet.CellFormats.ChildElements[(int)styleIndex] as CellFormat; if (cellFormat.ApplyNumberFormat != null && cellFormat.ApplyNumberFormat.HasValue && cellFormat.ApplyNumberFormat.Value && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue) { uint numberFormatId = cellFormat.NumberFormatId.Value; // Number format 14-22 and 45-47 are built-in date and/or time formats if ((numberFormatId >= 14 && numberFormatId <= 22) || (numberFormatId >= 45 && numberFormatId <= 47)) { DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture)); value = dateTime.ToString(); } else { if (stylesheet.NumberingFormats != null && stylesheet.NumberingFormats.Any(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId)) { NumberingFormat numberFormat = stylesheet.NumberingFormats.First(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId) as NumberingFormat; if (numberFormat != null && numberFormat.FormatCode != null && numberFormat.FormatCode.HasValue) { string formatCode = numberFormat.FormatCode.Value; if ((formatCode.Contains("h") && formatCode.Contains("m")) || (formatCode.Contains("m") && formatCode.Contains("d"))) { DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture)); value = dateTime.ToString(); } else { value = c.CellValue.Text; } } else { value = c.CellValue.Text; } } else { value = c.CellValue.Text; } } } else { value = c.CellValue.Text; } } // define index based on cell refernce - offset int index = cellReferencAsInterger - offset - 1; rowAsStringArray[index] = value; } } //end if cell value } //end if cell null } //for return(rowAsStringArray.ToList()); }
public List <Person> Load_sheet() { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(file_path, false)) { WorkbookPart workbookPart = doc.WorkbookPart; List <Person> people = new List <Person>(); List <string> collected_strings = new List <string>(); Sheet theSheet = workbookPart.Workbook.Descendants <Sheet>().FirstOrDefault(); if (theSheet == null) { throw new ArgumentException("sheetName"); } WorksheetPart wsPart = (WorksheetPart)(workbookPart.GetPartById(theSheet.Id)); var rows = wsPart.Worksheet.Descendants <DocumentFormat.OpenXml.Spreadsheet.Row>(); foreach (var row in rows) { collected_strings.Clear(); foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>()) { string cellValue = string.Empty; if (c.DataType != null) { if (c.DataType == CellValues.SharedString) { int id = -1; if (Int32.TryParse(c.InnerText, out id)) { SharedStringItem item = GetSharedStringItemById(workbookPart, id); if (item.Text != null) { cellValue = item.Text.Text; } else if (item.InnerText != null) { cellValue = item.InnerText; } else if (item.InnerXml != null) { cellValue = item.InnerXml; } } } } else { DateTime start_date = new DateTime(1900, 1, 1); try { int days = Convert.ToInt32(c.CellValue.Text); start_date = start_date.AddDays(days - 2); string str_date = start_date.ToString("d.MM.yyyy"); cellValue = str_date; } catch { cellValue = null; } } collected_strings.Add(cellValue); } people.Add(new Person(collected_strings)); } people.RemoveAt(0); return(people); } }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable(){ Count = (UInt32Value)53U, UniqueCount = (UInt32Value)28U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "account_id"; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "account_parent"; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "account_description"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "account_type"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "account_rollup"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "Custom Members"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "CurrentUser"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "RefreshTime"; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "Total Expense"; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "Expense"; 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 = "REDMOND\\pberruti"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "Liabilities"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "Liability"; 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 = "Marketing"; 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 = "Cost of Goods Sold"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "Income"; sharedStringItem19.Append(text19); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "General & Administration"; sharedStringItem20.Append(text20); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "Lease"; sharedStringItem21.Append(text21); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "Gross Sales"; sharedStringItem22.Append(text22); SharedStringItem sharedStringItem23 = new SharedStringItem(); Text text23 = new Text(); text23.Text = "LookUpCube(\"[Sales]\",\"(Measures.[Store Sales],\"+time.currentmember.UniqueName+\",\"+ Store.currentmember.UniqueName+\")\")"; sharedStringItem23.Append(text23); SharedStringItem sharedStringItem24 = new SharedStringItem(); Text text24 = new Text(); text24.Text = "Net Sales"; sharedStringItem24.Append(text24); SharedStringItem sharedStringItem25 = new SharedStringItem(); Text text25 = new Text(); text25.Text = "Net Income"; sharedStringItem25.Append(text25); SharedStringItem sharedStringItem26 = new SharedStringItem(); Text text26 = new Text(); text26.Text = "Information Systems"; sharedStringItem26.Append(text26); SharedStringItem sharedStringItem27 = new SharedStringItem(); Text text27 = new Text(); text27.Text = "Assets"; sharedStringItem27.Append(text27); SharedStringItem sharedStringItem28 = new SharedStringItem(); Text text28 = new Text(); text28.Text = "Asset"; 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); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
public void Init(SpreadsheetDocument document) { AllCells = document.FindCellsByRange(CellRange); CellRange.SheetTemplate = document.FindSheetByName(CellRange.SheetName); CellRange.WorksheetPart = ((WorksheetPart)document.WorkbookPart.GetPartById(CellRange.SheetTemplate.Id)); CellRange.MergeCells = CellRange.WorksheetPart.Worksheet.GetFirstChild <MergeCells>(); if (CellRange.MergeCells != null) { CellRange.MergeCellsDic = CellRange.MergeCells.ToDictionary(e => (e as MergeCell).Reference.Value.ToUpper().Split(':')[0], e => (e as MergeCell)); } foreach (var rowGroup in AllCells) { foreach (Cell cell in rowGroup) { if (cell.DataType != null && cell.CellValue != null) { string stringValue = null; switch (cell.DataType.Value) { case CellValues.SharedString: { int index = int.Parse(cell.CellValue.Text); SharedStringItem stringItem = document.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(index); stringValue = stringItem.InnerText; break; } case CellValues.String: { stringValue = cell.CellValue.Text; break; } } if (PARAM_PATTERN == null) { PARAM_PATTERN = new Regex(this.regexVariable); } if (!string.IsNullOrEmpty(stringValue) && PARAM_PATTERN.IsMatch(stringValue)) { var match = PARAM_PATTERN.Match(stringValue); var Func = ""; string Variable = ""; string VariableFull = Variable = match.Groups[1].Value; string regexFunc = @".\:(.*)"; var regexFuncRexgex = new Regex(regexFunc); if (regexFuncRexgex.IsMatch(VariableFull)) { var match1 = regexFuncRexgex.Match(VariableFull); Func = match1.Groups[1].Value; Variable = VariableFull.Replace(":" + Func, ""); } VariableFull = string.Format("[%{0}%]", VariableFull); AllCellsConfig.Add(new { TextOrigin = stringValue, Cell = cell, VariableFull = VariableFull, Variable = Variable, Func = Func, }); } } } } }
static void Main(string[] args) { var excelFileName = @"./../image.xlsx"; using (var document = SpreadsheetDocument.Open(excelFileName, false)) { var workbookPart = document.WorkbookPart; var sheet = workbookPart.Workbook.Descendants <Sheet>().Where(sheetItem => sheetItem.Name == "image").FirstOrDefault(); if (null == sheet) { Console.WriteLine("Sheet can not be found in the file."); return; } var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id); var styleSheet = workbookPart.WorkbookStylesPart.Stylesheet; var cells = worksheetPart.Worksheet.Descendants <Cell>(); var sharedStringCells = cells.Where(cellItem => (null != cellItem.DataType) && (cellItem.DataType.Value == CellValues.SharedString)); foreach (var sharedStringCell in sharedStringCells) { string cellInnerText = string.Empty; int itemIndex = int.Parse(sharedStringCell.InnerText); SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(itemIndex); try { /* * The font data applied to each cells in a excel file is stored in cellXfs element in styles.xml file. * The element, cellXfs, can be get from "CellFormats" property in StyleSheet. * And the font data each cells contain can be specified from CellFormats property by StyleIndex * variable. */ var cellFormat = (CellFormat)styleSheet.CellFormats.ElementAt(int.Parse(sharedStringCell.StyleIndex)); /* * The font information actually applied to the cell is stored in fonts element in styles.xml file. * The data can be get by CellFormat.FontId. */ var cellFontId = cellFormat.FontId; var cellFont = styleSheet.Fonts.ElementAt(int.Parse(cellFontId)); var cellFontStrikeElement = cellFont.ChildElements.Where(cellFontItem => cellFontItem is Strike); if (0 < cellFontStrikeElement.Count()) { Console.WriteLine($"セルに取り消し線が設定されています。({sharedStringCell.CellReference})"); continue; } var phoneticProperty = item.Elements <PhoneticProperties>().FirstOrDefault(); var fontId = int.Parse(phoneticProperty.FontId); var fonts = styleSheet.Fonts.Elements(); var fontItem = fonts.ElementAt((int)fontId); var cellStrike = fontItem.Elements <Strike>(); if (0 < cellStrike.Count()) { Console.WriteLine(item.InnerText); continue; } var runElements = item.Elements <Run>(); if (0 < runElements.Count()) { /* * Check the format informations have strikethrough element * if one or more r element can be found. */ foreach (var runElement in runElements) { var runPropertyElements = runElement.RunProperties; var strikeElements = runPropertyElements.Elements <Strike>(); if (0 < strikeElements.Count()) { continue; } else { cellInnerText += runElement.InnerText; } } } else { cellInnerText += item.InnerText; } } catch (Exception) { cellInnerText += item.InnerText; } Console.WriteLine($"{sharedStringCell.CellReference} : {cellInnerText}({item.InnerText})({sharedStringCell.StyleIndex})"); } } return; }
static DataTable ReadExcelasJSON(string fileName) { DataTable dtTable = new DataTable(); try { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false)) { WorkbookPart workbookPart = doc.WorkbookPart; Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>(); string sheetId = ""; foreach (Sheet thesheet in thesheetcollection) { if (thesheet.Name == "Исходник") { sheetId = thesheet.Id; break; } } //using for each loop to get the sheet from the sheetcollection //foreach (Sheet thesheet in thesheetcollection.OfType<Sheet>()) //{ //statement to get the worksheet object by using the sheet id //Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet; Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(sheetId)).Worksheet; SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = thesheetdata.Descendants <Row>(); //Выделяет все символы из строки Regex regex = new Regex(@"\D+"); //Выделяет все цифры из строки Regex regex1 = new Regex(@"\d+"); //Поиск колонки с признаком классификатора ОС string[] rowHeaders = new string[] { "Власник/Орендодавець" }; int?startRowIndex = null; int ozColumnIndex = 0; //string[] vechicleType = new string[] { // "АТП-2004 ТОВ".Replace(" ",""), // "ТОВ Кернел-Трейд".Replace(" ", ""), // "ПРАТ Кропивницький ОЕЗ".Replace(" ", ""), // "СТОВ Дружба - Нова".Replace(" ", ""), // "СТОВ Придніпровський край".Replace(" ", ""), // "Коритня-Агро КМ ТОВ".Replace(" ", ""), // "Приколотнянский МЭЗ ТОВ".Replace(" ", ""), // "Агро Інвест Україна, ТОВ".Replace(" ", ""), // "СТОВ Дружба-Нова".Replace(" ", ""), // "Вовчанський ОЕЗ ПРАТ".Replace(" ", ""), // "Юнігрейн-Агро ТОВ, Семенівка".Replace(" ", ""), // "Вовчанський ОЕЗ ПРАТ".Replace(" ", "") //}; List <string> passengerCellValue = new List <string>(); List <int> rowsToProc = new List <int>(); //Поиск первой строки с данными for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++) { for (int rCnt1 = 0; rCnt1 < thesheetdata.ElementAt(rCnt).ChildElements.Count(); rCnt1++) { Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1); int id; if (Int32.TryParse(thecurrentcell.InnerText, out id)) { SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id); if (item.Text != null) { if (Array.IndexOf(rowHeaders, item.Text.Text) > -1) { startRowIndex = rCnt; ozColumnIndex = rCnt1; //rowsToProc.Add(rCnt); //passengerCellValue = thecurrentcell.InnerText; break; } } } } if (startRowIndex != null) { break; } } //rowHeaders = new string[] { "Кластер", "Назва підприємства", "Назва основного засобу", "Класифікатор ОЗ", "Дата випуску", "Державний номер", "Стан ОЗ", "Модель" }; rowHeaders = new string[] { "Власник/Орендодавець" }; SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart; List <int> columns = new List <int>(); //Поиск номеров колонок для получения данных int idx = 0; foreach (Cell c in rows.ElementAt((int)startRowIndex).Elements <Cell>()) { if (c.CellValue != null) { if (c.DataType != null && c.DataType.Value == CellValues.SharedString) { if (Array.IndexOf(rowHeaders, stringTablePart.SharedStringTable.ChildElements[Int32.Parse(c.CellValue.InnerXml)].InnerText) > -1) { columns.Add(idx);// regex.Match(c.CellReference).Value); } } } idx++; } for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++) { try { Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(ozColumnIndex); int id; if (Int32.TryParse(thecurrentcell.InnerText, out id)) { SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id); //if (Array.IndexOf(vechicleType.ToArray(), item.Text.Text.Replace("\"", "").Replace(" ", "")) > -1 && item.Text.Text.IndexOf("Дружба")>-1) //{ // int fkjgk = 0; // //rowsToProc.Add(rCnt); // //passengerCellValue.Add(thecurrentcell.InnerText); //} //if (Array.IndexOf(vechicleType.ToArray(), item.Text.Text.Replace("\"","").Replace(" ", "")) < 0) //{ rowsToProc.Add(rCnt); passengerCellValue.Add(thecurrentcell.InnerText); //} } } catch (Exception) { //throw; } } for (int rCnt = 0; rCnt < rowsToProc.Count(); rCnt++) { List <string> rowList = new List <string>(); for (int c = 0; c < columns.Count(); c++) { Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rowsToProc[rCnt]).ChildElements.ElementAt(columns[c]); //statement to take the integer value string currentcellvalue = string.Empty; if (thecurrentcell.DataType != null) { if (thecurrentcell.DataType == CellValues.SharedString) { int id; if (Int32.TryParse(thecurrentcell.InnerText, out id)) { SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id); if (item.Text != null) { //first row will provide the column name. if (rCnt == 0) { dtTable.Columns.Add(item.Text.Text); } else { rowList.Add(item.Text.Text); } } else { rowList.Add(""); } } } } else { if (rCnt != 0)//reserved for column values { rowList.Add(thecurrentcell.InnerText); } } } if (rCnt != 0)//reserved for column values { dtTable.Rows.Add(rowList.ToArray()); } } } } catch (Exception) { throw; } return(dtTable); }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)54U, UniqueCount = (UInt32Value)22U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text1.Text = "Av. Corrientes 6277 ( 1427) Buenos Aires "; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "Argentina - Tel.: 4323-9931"; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "ORDEN DE PUBLICIDAD"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "ORDEN DE PUBLICIDAD Nº"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "MEDIO:"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "CLIENTE:"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "FECHA DE EMISION:"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "CONTACTO:"; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text9.Text = "EMAIL: "; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "HORARIO:"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "HORARIO"; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "LUNES"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "MARTES"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "MIERCOLES"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "JUEVES"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "VIERNES"; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "SABADO"; sharedStringItem17.Append(text17); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "DOMINGO"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "TOTAL SALIDAS"; sharedStringItem19.Append(text19); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "COSTO X INFOMERCIAL"; sharedStringItem20.Append(text20); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "SUBTOTAL"; sharedStringItem21.Append(text21); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "TOTAL"; sharedStringItem22.Append(text22); 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); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
private static string GetStringCellValue(SpreadsheetDocument doc, Cell cell) { SharedStringItem ssi = doc.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(int.Parse(cell.CellValue.InnerText)); return(ssi.Text.Text.Trim()); }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable(){ Count = (UInt32Value)1U, UniqueCount = (UInt32Value)1U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "este es un texto en A1"; sharedStringItem1.Append(text1); sharedStringTable1.Append(sharedStringItem1); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
//Em teste public static DataTable CreateDataTableFromStreamBeta(Stream stream, string sheetName = null) { //Sheet sheet = null; //IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>(); //if (!string.IsNullOrEmpty(sheetName)) //{ // sheet = sheets.FirstOrDefault(x => x.Name == sheetName); //} //else //{ // sheet = sheets.FirstOrDefault(); //} //if (sheet == null) //{ // //throw new ArgumentException("sheetName"); // throw new InvalidOperationException("Erro na folha de dados"); //} //dt.TableName = sheet.Name; try { DataTable dtTable = new DataTable(); //Lets open the existing excel file and read through its content . Open the excel using openxml sdk using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true)) { //create the object for workbook part WorkbookPart workbookPart = doc.WorkbookPart; Sheet thesheet = null; IEnumerable <Sheet> sheets = workbookPart.Workbook.Descendants <Sheet>(); if (!string.IsNullOrEmpty(sheetName)) { thesheet = sheets.FirstOrDefault(x => x.Name == sheetName); } else { thesheet = sheets.FirstOrDefault(); } if (thesheet == null) { //throw new ArgumentException("sheetName"); throw new InvalidOperationException("Erro na folha de dados"); } //Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>(); //using for each loop to get the sheet from the sheetcollection //foreach (Sheet thesheet in thesheetcollection.OfType<Sheet>()) //{ //statement to get the worksheet object by using the sheet id Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet; SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>(); for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++) { List <string> rowList = new List <string>(); for (int rCnt1 = 0; rCnt1 < thesheetdata.ElementAt(rCnt).ChildElements.Count(); rCnt1++) { Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1); //statement to take the integer value string currentcellvalue = string.Empty; if (thecurrentcell.DataType != null) { if (thecurrentcell.DataType == CellValues.SharedString) { int id; if (Int32.TryParse(thecurrentcell.InnerText, out id)) { SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id); if (item.Text != null) { //first row will provide the column name. if (rCnt == 0) { dtTable.Columns.Add(item.Text.Text); } else { rowList.Add(item.Text.Text); } } else if (item.InnerText != null) { currentcellvalue = item.InnerText; } else if (item.InnerXml != null) { currentcellvalue = item.InnerXml; } } } } else { if (rCnt != 0)//reserved for column values { rowList.Add(thecurrentcell.InnerText); } } } if (rCnt != 0)//reserved for column values { dtTable.Rows.Add(rowList.ToArray()); } } } return(dtTable); //} } catch (Exception ex) { Console.WriteLine(ex.Message); throw; } }
// 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; }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)18U, UniqueCount = (UInt32Value)17U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text(); text1.Text = "Tel. / Fax.:"; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text2.Text = "Att.: "; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "Fecha:"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "Medio:"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "EDITORIAL"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "REVISTA"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "MEDIDA"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "PRODUCTO"; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "Argentina - Tel.: 4323-9931"; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "Av. Corrientes 6277"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text11.Text = "(C1427BPA) Buenos Aires "; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "ORDEN DE PUBLICIDAD"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "Subtotal:"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "iva 21%:"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "Total"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "FECHA"; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "COSTO"; sharedStringItem17.Append(text17); 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); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
/// <summary> /// Adds the target string to the indexer. If the string is already detected to exist inside /// the indexer, then it will not be duplicated. /// </summary> /// <param name="toAdd">The string to add.</param> /// <param name="preserveSpace">Should spaces be preserved? By default this is set to /// false.</param> /// <returns>The index of the string inside the shared string table.</returns> public long Add(string toAdd, bool preserveSpace = false) { if (!this.indexDict.ContainsKey(toAdd)) { var sharedStrItem = new SharedStringItem() { Text = new Text() { Space = preserveSpace ? SpaceProcessingModeValues.Preserve : SpaceProcessingModeValues.Default, Text = toAdd } }; this.SharedStringTable.Append(sharedStrItem); // now update our books this.indexDict[toAdd] = this.currentIndex; this.strDict[this.currentIndex] = toAdd; this.SharedStringTable.Count++; this.SharedStringTable.UniqueCount++; return this.currentIndex++; } else { return this.indexDict[toAdd]; } }
/// <summary> /// Load file and add it to Excel class. /// </summary> public void Load(string fileName) { FileNameAdd(fileName); // SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false); // Dictionary <string, string> sheetIdToName = new Dictionary <string, string>(); foreach (Sheet sheet in document.WorkbookPart.Workbook.Sheets) { sheetIdToName.Add(sheet.Id, sheet.Name); SheetNameAdd(fileName, sheet.Name); } // foreach (WorksheetPart worksheetPart in document.WorkbookPart.WorksheetParts) { string sheetId = document.WorkbookPart.GetIdOfPart(worksheetPart); string sheetName = sheetIdToName[sheetId]; SharedStringItem[] sharedStringList = new SharedStringItem[0]; if (document.WorkbookPart.SharedStringTablePart != null) { sharedStringList = document.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray(); } // OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); while (reader.Read()) { if (reader.ElementType == typeof(Row)) { Row row = (Row)reader.LoadCurrentElement(); string rowName = row.RowIndex; foreach (Cell cell in row.Elements <Cell>().ToArray()) { string text = null; bool isText = false; bool isError = false; string columnName = cell.CellReference.Value.Substring(0, cell.CellReference.Value.Length - rowName.Length); if (cell.DataType != null) { switch (cell.DataType.Value) { case CellValues.Boolean: text = cell.CellValue.Text; break; case CellValues.SharedString: isText = true; int index = int.Parse(cell.CellValue.InnerText); SharedStringItem item = sharedStringList[index]; if (item.Text != null) { text = item.Text.Text; } else { text = item.InnerText; // For cell text with formating. (Formating is removed when read like this). } break; case CellValues.String: isText = true; text = cell.CellValue.Text; break; case CellValues.Error: isError = true; text = cell.CellValue.Text; break; default: throw new Exception("Type unknown!"); } } else { if (cell.CellValue != null) { text = cell.CellValue.Text; // Number. } } // if (text != null) { text = text.Trim(); } if (!string.IsNullOrEmpty(text)) { CellSet(fileName, sheetName, rowName, columnName, new ExcelCell(text, isText, isError)); } } } } reader.Close(); } document.Close(); }
public async Task <IActionResult> UploadExcel(ApplicantExcelUploadViewModel model) { if (ModelState.IsValid && model.LessonId > 0) { if (model.ExcelFile == null || model.ExcelFile.Length == 0) { return(Content("file not selected")); } string fileName = model.ExcelFile.FileName; string filePath = Path.Combine( Directory.GetCurrentDirectory(), "wwwroot", "files", "UploadedExcels", fileName); using (var stream = new FileStream(filePath, FileMode.Create)) { await model.ExcelFile.CopyToAsync(stream); } List <Applicant> applicantList = new List <Applicant>(); try { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; string cellValue = string.Empty; foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts) { int rowNumber = 0; OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); while (reader.Read()) { int columnNumber = 0; if (reader.ElementType == typeof(Row)) { reader.ReadFirstChild(); Applicant applicant = new Applicant() { Lesson = _dbContext.Lessons.Find(model.LessonId), Date = DateTime.Now }; do { if (rowNumber == 0) { continue; } if (reader.ElementType == typeof(Cell)) { Cell c = (Cell)reader.LoadCurrentElement(); if (c.DataType != null && c.DataType == CellValues.SharedString) { SharedStringItem ssi = workbookPart.SharedStringTablePart .SharedStringTable .Elements <SharedStringItem>() .ElementAt(Int32.Parse(c.CellValue.InnerText)); var s = ssi.Text; cellValue = ssi.Text.Text; } else { cellValue = c.CellValue.InnerText; } switch (columnNumber) { case 0: applicant.FirstName = cellValue; break; case 1: applicant.LastName = cellValue; break; case 2: applicant.Email = cellValue; break; case 3: applicant.Phone1 = cellValue.RemoveWhiteSpace(); break; case 4: if (cellValue != "x") { applicant.Phone2 = cellValue.RemoveWhiteSpace(); } break; case 7: applicant.Date = cellValue.UNIXTimeToDateTime(); break; default: break; } } columnNumber++; } while (reader.ReadNextSibling()); if (rowNumber != 0) { applicantList.Add(applicant); } rowNumber++; } } } } } catch (Exception e) { throw new Exception(e.Message); } finally { System.IO.File.Delete(filePath); } _dbContext.Applicants.AddRange(applicantList); _dbContext.SaveChanges(); return(RedirectToAction("AllApplicants", "Applicant")); } return(RedirectToAction("UploadExcel")); }
/// <summary> /// Creates an instance of SLTable, given row and column indices of opposite cells in a cell range. /// </summary> /// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param> /// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param> /// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param> /// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param> /// <returns>An SLTable with the required information.</returns> public SLTable CreateTable(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex) { int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1; if (StartRowIndex < EndRowIndex) { iStartRowIndex = StartRowIndex; iEndRowIndex = EndRowIndex; } else { iStartRowIndex = EndRowIndex; iEndRowIndex = StartRowIndex; } if (StartColumnIndex < EndColumnIndex) { iStartColumnIndex = StartColumnIndex; iEndColumnIndex = EndColumnIndex; } else { iStartColumnIndex = EndColumnIndex; iEndColumnIndex = StartColumnIndex; } if (iStartRowIndex < 1) iStartRowIndex = 1; if (iStartRowIndex == SLConstants.RowLimit) iStartRowIndex = SLConstants.RowLimit - 1; if (iStartColumnIndex < 1) iStartColumnIndex = 1; // consider minus 1 in case there's a totals row so there's less checking... if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit; if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit; if (iEndRowIndex <= iStartRowIndex) iEndRowIndex = iStartRowIndex + 1; SLTable tbl = new SLTable(); tbl.SetAllNull(); slwb.RefreshPossibleTableId(); tbl.Id = slwb.PossibleTableId; tbl.DisplayName = string.Format("Table{0}", tbl.Id); tbl.Name = tbl.DisplayName; tbl.StartRowIndex = iStartRowIndex; tbl.StartColumnIndex = iStartColumnIndex; tbl.EndRowIndex = iEndRowIndex; tbl.EndColumnIndex = iEndColumnIndex; tbl.AutoFilter.StartRowIndex = tbl.StartRowIndex; tbl.AutoFilter.StartColumnIndex = tbl.StartColumnIndex; tbl.AutoFilter.EndRowIndex = tbl.EndRowIndex; tbl.AutoFilter.EndColumnIndex = tbl.EndColumnIndex; tbl.HasAutoFilter = true; SLTableColumn tc; uint iColumnId = 1; int i, index; uint j; SLCell c; SLCellPoint pt; string sHeaderText = string.Empty; SharedStringItem ssi; SLRstType rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>()); for (i = tbl.StartColumnIndex; i <= tbl.EndColumnIndex; ++i) { pt = new SLCellPoint(StartRowIndex, i); sHeaderText = string.Empty; if (slws.Cells.ContainsKey(pt)) { c = slws.Cells[pt]; if (c.CellText == null) { if (c.DataType == CellValues.Number) sHeaderText = c.NumericValue.ToString(CultureInfo.InvariantCulture); else if (c.DataType == CellValues.Boolean) sHeaderText = c.NumericValue > 0.5 ? "TRUE" : "FALSE"; else sHeaderText = string.Empty; } else { sHeaderText = c.CellText; } if (c.DataType == CellValues.SharedString) { index = -1; if (c.CellText != null) { if (int.TryParse(c.CellText, out index)) { index = -1; } } else { index = Convert.ToInt32(c.NumericValue); } if (index >= 0 && index < listSharedString.Count) { ssi = new SharedStringItem(); ssi.InnerXml = listSharedString[index]; rst.FromSharedStringItem(ssi); sHeaderText = rst.ToPlainString(); } } } j = iColumnId; if (sHeaderText.Length == 0) { sHeaderText = string.Format("Column{0}", j); } while (tbl.TableNames.Contains(sHeaderText)) { ++j; sHeaderText = string.Format("Column{0}", j); } tc = new SLTableColumn(); tc.Id = iColumnId; tc.Name = sHeaderText; tbl.TableColumns.Add(tc); tbl.TableNames.Add(sHeaderText); ++iColumnId; } tbl.TableStyleInfo.ShowFirstColumn = false; tbl.TableStyleInfo.ShowLastColumn = false; tbl.TableStyleInfo.ShowRowStripes = true; tbl.TableStyleInfo.ShowColumnStripes = false; tbl.HasTableStyleInfo = true; return tbl; }
protected int updateCell(Row exelRow, WorkbookPart wb, DataSet xmlDs, string xmlTableName, string xmlId, params object[] para) { if (!xmlDs.Tables.Contains(xmlTableName)) { return(-1); } DataRow dr = getDataRow(xmlDs.Tables[xmlTableName], xmlId); if (dr == null) { return(-2); } int rowindex = 0; string columnName = "col"; string source = "source"; string celltype = "celltype"; Cell c = GetCell(exelRow, (uint)rowindex, columnName); SharedStringItem sharedo = null; string value = ""; SharedStringTablePart stringTable = null; int cal = 0; if (c.DataType.Value == CellValues.SharedString) { stringTable = wb.GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); if (stringTable != null) { //value = stringTable.SharedStringTable. // ElementAt(int.Parse(c.CellValue.InnerText)).InnerText; //value = stringTable.SharedStringTable. // ElementAt(int.Parse(c.CellValue.InnerText)).InnerText; sharedo = (SharedStringItem)stringTable.SharedStringTable.ElementAt(int.Parse(c.CellValue.InnerText)).Clone(); if (sharedo != null && sharedo.HasChildren && sharedo.ChildElements.Count > 1) { cal = 1; } } if (cal == 0) { sharedo = null;//set null to cal on value value = stringTable.SharedStringTable.ElementAt(int.Parse(c.CellValue.InnerText)).InnerText; } } else { //cvlo = (CellValue) c.CellValue.Clone(); value = c.CellValue.InnerText; } string[] replaceformat = source.Split('|'); int cr = Math.Min(replaceformat.Length, para.Length); int k = 0; for (int i = 0; i < cr; i++) { if (sharedo != null) { for (k = 0; k < sharedo.ChildElements.Count; k++) { sharedo.ChildElements[k].InnerXml = sharedo.ChildElements[k].InnerXml.Replace(replaceformat[i], para[i].ToString()); // cvlo.ChildElements[k].InnerText = cvlo.ChildElements[k].InnerText.Replace(replaceformat[i], para[i].ToString()); } } else { value = value.Replace(replaceformat[i], para[i].ToString()); } } // CellValue cvl = new CellValue(value); if (sharedo == null) { c.CellValue = new CellValue(value);//cvlo;// c.DataType = convertType(celltype); } else { stringTable.SharedStringTable.AppendChild(sharedo); c.DataType = CellValues.SharedString; stringTable.SharedStringTable.Save(); c.CellValue = new CellValue((stringTable.SharedStringTable.ChildElements.Count - 1).ToString()); } return(0); }
public async Task <List <List <String> > > FromExcel(IFormFile files, string userID) { List <List <String> > lst = new List <List <String> >(); if (files == null || files.Length == 0) { throw new Exception("boom"); } string filename = DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + "_" + DateTime.Now.Hour + "_" + DateTime.Now.Minute + "ExcelList_" + Guid.NewGuid() + ".xlsx";; string path = Path.Combine( Directory.GetCurrentDirectory(), "files", "UploadedExcels", userID); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } string filePath = Path.Combine(path, filename); using (var stream = new FileStream(filePath, FileMode.Create)) { await files.CopyToAsync(stream); } try { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; string cellValue = string.Empty; List <List <string> > lstExcel = new List <List <string> >(); List <string> exelRow; foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts) { int rowNumber = 0; OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); while (reader.Read()) { if (reader.ElementType == typeof(Row)) { reader.ReadFirstChild(); // int cellNumber = 1; exelRow = new List <string>(); do { if (reader.ElementType == typeof(Cell)) { Cell c = (Cell)reader.LoadCurrentElement(); if (c.DataType != null && c.DataType == CellValues.SharedString) { SharedStringItem ssi = workbookPart.SharedStringTablePart .SharedStringTable .Elements <SharedStringItem>() .ElementAt(Int32.Parse(c.CellValue.InnerText)); cellValue = ssi.Text.Text; } else { cellValue = c.CellValue.InnerText; } exelRow.Add(cellValue); } }while (reader.ReadNextSibling()); lstExcel.Add(exelRow); rowNumber++; } } } //await _dbContext.Stores.AddRangeAsync(storesFromExcel); //await _dbContext.SaveChangesAsync(); } } catch (Exception) { //file.Delete(); throw new Exception("booooom on import"); } finally { //file.Delete(); } return(lst); }
public Test(string wkbookFile) { try { document = SpreadsheetDocument.Open(wkbookFile, false); WorkbookPart workbookPart = document.WorkbookPart; Sheets formresults = workbookPart.Workbook.GetFirstChild <Sheets>(); // Antar at det alltid er det første arket vi skal prosessere //using for each loop to get the sheet from the sheetcollection foreach (Sheet thesheet in formresults) { Console.WriteLine("Excel Sheet Name : " + thesheet.Name); Console.WriteLine("----------------------------------------------- "); //statement to get the worksheet object by using the sheet id Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet; var wbpst = workbookPart.GetPartsOfType <WorkbookStylesPart>(); SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild <SheetData>(); foreach (Row thecurrentrow in thesheetdata) { foreach (Cell thecurrentcell in thecurrentrow) { //statement to take the integer value string currentcellvalue = string.Empty; if (thecurrentcell.DataType != null) { if (thecurrentcell.DataType == CellValues.SharedString) { int id; if (Int32.TryParse(thecurrentcell.InnerText, out id)) { SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id); if (item.Text != null) { //code to take the string value Console.WriteLine(item.Text.Text + " "); } else if (item.InnerText != null) { currentcellvalue = item.InnerText; } else if (item.InnerXml != null) { currentcellvalue = item.InnerXml; } } } } else { Console.WriteLine(Convert.ToInt16(thecurrentcell.InnerText) + " "); } } Console.WriteLine(); } Console.WriteLine(""); Console.ReadLine(); } } catch (Exception) { } }
/// <summary> /// Form an SLRstType from DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class. /// </summary> /// <param name="SharedString">A source DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class.</param> public void FromSharedStringItem(SharedStringItem SharedString) { this.istrReal.InnerXml = SharedString.InnerXml; }
// Generates content of sharedStringTablePart1. private void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1) { SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)77U, UniqueCount = (UInt32Value)35U }; SharedStringItem sharedStringItem1 = new SharedStringItem(); Text text1 = new Text() { Space = SpaceProcessingModeValues.Preserve }; text1.Text = "Av. Corrientes 6277 ( 1427) Buenos Aires "; sharedStringItem1.Append(text1); SharedStringItem sharedStringItem2 = new SharedStringItem(); Text text2 = new Text(); text2.Text = "Argentina - Tel.: 4323-9931"; sharedStringItem2.Append(text2); SharedStringItem sharedStringItem3 = new SharedStringItem(); Text text3 = new Text(); text3.Text = "ORDEN DE PUBLICIDAD"; sharedStringItem3.Append(text3); SharedStringItem sharedStringItem4 = new SharedStringItem(); Text text4 = new Text(); text4.Text = "MEDIO"; sharedStringItem4.Append(text4); SharedStringItem sharedStringItem5 = new SharedStringItem(); Text text5 = new Text(); text5.Text = "PROGRAMA:"; sharedStringItem5.Append(text5); SharedStringItem sharedStringItem6 = new SharedStringItem(); Text text6 = new Text(); text6.Text = "FECHA DE EMISION:"; sharedStringItem6.Append(text6); SharedStringItem sharedStringItem7 = new SharedStringItem(); Text text7 = new Text(); text7.Text = "CONTACTO:"; sharedStringItem7.Append(text7); SharedStringItem sharedStringItem8 = new SharedStringItem(); Text text8 = new Text(); text8.Text = "TELEFONO/ Fax:"; sharedStringItem8.Append(text8); SharedStringItem sharedStringItem9 = new SharedStringItem(); Text text9 = new Text(); text9.Text = "DIRECCION:"; sharedStringItem9.Append(text9); SharedStringItem sharedStringItem10 = new SharedStringItem(); Text text10 = new Text(); text10.Text = "E-MAIL:"; sharedStringItem10.Append(text10); SharedStringItem sharedStringItem11 = new SharedStringItem(); Text text11 = new Text(); text11.Text = "SALIDA"; sharedStringItem11.Append(text11); SharedStringItem sharedStringItem12 = new SharedStringItem(); Text text12 = new Text(); text12.Text = "DURACION"; sharedStringItem12.Append(text12); SharedStringItem sharedStringItem13 = new SharedStringItem(); Text text13 = new Text(); text13.Text = "LUNES"; sharedStringItem13.Append(text13); SharedStringItem sharedStringItem14 = new SharedStringItem(); Text text14 = new Text(); text14.Text = "MARTES"; sharedStringItem14.Append(text14); SharedStringItem sharedStringItem15 = new SharedStringItem(); Text text15 = new Text(); text15.Text = "MIÉRCOLES"; sharedStringItem15.Append(text15); SharedStringItem sharedStringItem16 = new SharedStringItem(); Text text16 = new Text(); text16.Text = "JUEVES"; sharedStringItem16.Append(text16); SharedStringItem sharedStringItem17 = new SharedStringItem(); Text text17 = new Text(); text17.Text = "VIERNES"; sharedStringItem17.Append(text17); SharedStringItem sharedStringItem18 = new SharedStringItem(); Text text18 = new Text(); text18.Text = "SÁBADO"; sharedStringItem18.Append(text18); SharedStringItem sharedStringItem19 = new SharedStringItem(); Text text19 = new Text(); text19.Text = "DOMINGO"; sharedStringItem19.Append(text19); SharedStringItem sharedStringItem20 = new SharedStringItem(); Text text20 = new Text(); text20.Text = "SALIDA 1"; sharedStringItem20.Append(text20); SharedStringItem sharedStringItem21 = new SharedStringItem(); Text text21 = new Text(); text21.Text = "SUBTOTAL"; sharedStringItem21.Append(text21); SharedStringItem sharedStringItem22 = new SharedStringItem(); Text text22 = new Text(); text22.Text = "HORARIO"; sharedStringItem22.Append(text22); SharedStringItem sharedStringItem23 = new SharedStringItem(); Text text23 = new Text(); text23.Text = "PRODUCTO"; sharedStringItem23.Append(text23); SharedStringItem sharedStringItem24 = new SharedStringItem(); Text text24 = new Text(); text24.Text = "EMPRESA"; sharedStringItem24.Append(text24); SharedStringItem sharedStringItem25 = new SharedStringItem(); Text text25 = new Text(); text25.Text = "ZOCALO"; sharedStringItem25.Append(text25); SharedStringItem sharedStringItem26 = new SharedStringItem(); Text text26 = new Text(); text26.Text = "COD. INGESTA"; sharedStringItem26.Append(text26); SharedStringItem sharedStringItem27 = new SharedStringItem(); Text text27 = new Text(); text27.Text = "SALIDAS"; sharedStringItem27.Append(text27); SharedStringItem sharedStringItem28 = new SharedStringItem(); Text text28 = new Text(); text28.Text = "PNTS TOTALES:"; sharedStringItem28.Append(text28); SharedStringItem sharedStringItem29 = new SharedStringItem(); Text text29 = new Text(); text29.Text = "COSTO POR SALIDA"; sharedStringItem29.Append(text29); SharedStringItem sharedStringItem30 = new SharedStringItem(); Text text30 = new Text(); text30.Text = "IVA 21%:"; sharedStringItem30.Append(text30); SharedStringItem sharedStringItem31 = new SharedStringItem(); Text text31 = new Text(); text31.Text = "TOTAL"; sharedStringItem31.Append(text31); SharedStringItem sharedStringItem32 = new SharedStringItem(); Text text32 = new Text(); text32.Text = "NUMERO"; sharedStringItem32.Append(text32); SharedStringItem sharedStringItem33 = new SharedStringItem(); Text text33 = new Text(); text33.Text = "SPRAYETTE"; sharedStringItem33.Append(text33); SharedStringItem sharedStringItem34 = new SharedStringItem(); Text text34 = new Text(); text34.Text = "EXCLUSIVE"; sharedStringItem34.Append(text34); SharedStringItem sharedStringItem35 = new SharedStringItem(); Text text35 = new Text(); text35.Text = "POLISHOP"; sharedStringItem35.Append(text35); 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); sharedStringTablePart1.SharedStringTable = sharedStringTable1; }
/// <summary> /// Form a DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class from this SLRstType class. /// </summary> /// <returns>A DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class.</returns> public SharedStringItem ToSharedStringItem() { SharedStringItem ssi = new SharedStringItem(); ssi.InnerXml = SLTool.RemoveNamespaceDeclaration(this.istrReal.InnerXml); return ssi; }
public static DataTable Run( [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = null)] HttpRequest req, ILogger log) { var file = req.Form.Files["file"]; { try { DataTable dtTable = new DataTable(); var filestream = file.OpenReadStream(); //Lets open the existing excel file and read through its content . Open the excel using openxml sdk using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filestream, false)) { WorkbookPart workbookPart = doc.WorkbookPart; Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>(); foreach (Sheet thesheet in thesheetcollection.OfType <Sheet>()) { Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet; SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>(); for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++) { List <string> rowList = new List <string>(); for (int rCnt1 = 0; rCnt1 < thesheetdata.ElementAt(rCnt).ChildElements.Count(); rCnt1++) { Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1); string currentcellvalue = string.Empty; if (thecurrentcell.DataType != null) { if (thecurrentcell.DataType == CellValues.SharedString) { int id; if (Int32.TryParse(thecurrentcell.InnerText, out id)) { SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id); if (item.Text != null) { if (rCnt == 0) { dtTable.Columns.Add(item.Text.Text); } else { rowList.Add(item.Text.Text); } } else if (item.InnerText != null) { currentcellvalue = item.InnerText; } else if (item.InnerXml != null) { currentcellvalue = item.InnerXml; } } } } else { if (rCnt != 0) { rowList.Add(thecurrentcell.InnerText); } } } if (rCnt != 0) { dtTable.Rows.Add(rowList.ToArray()); } } } // return JsonConvert.SerializeObject(dtTable); return(dtTable); } } catch (Exception ex) { Console.WriteLine(ex.Message); throw; } } }
/// <summary> /// Convert a excel row to a list of strings /// Every cell is one value /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="r"> row from a excel file</param> /// <returns>list of string for each cell in the row</returns> private List <string> RowToList(Row r) { string[] rowAsStringArray = new string[numOfColumns]; // create a new cell Cell c = new Cell(); for (int i = 0; i < r.ChildElements.Count(); i++) { // get current cell at i c = r.Elements <Cell>().ElementAt(i); string value = ""; if (c != null) { int cellReferencAsInterger = GetColumnNumber(GetColumnName(c.CellReference)); if (c.CellValue != null) { // if cell reference in range of the area int start = GetColumnNumber(this._areaOfData.StartColumn); int end = GetColumnNumber(this._areaOfData.EndColumn); if (cellReferencAsInterger >= start && cellReferencAsInterger <= end) { // shared string if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.SharedString) { int sharedStringIndex = int.Parse(c.CellValue.Text, CultureInfo.InvariantCulture); SharedStringItem sharedStringItem = _sharedStrings[sharedStringIndex]; value = sharedStringItem.InnerText; } //// string //else if (c.DataType != null && c.DataType.Value == CellValues.String) //{ // value = c.CellValue.Text; //} // not a text //else if (c.StyleIndex != null && c.StyleIndex.HasValue) //{ // uint styleIndex = c.StyleIndex.Value; // CellFormat cellFormat = _stylesheet.CellFormats.ChildElements[(int)styleIndex] as CellFormat; // if (cellFormat != null && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue) // { // uint numberFormatId = cellFormat.NumberFormatId.Value; // NumberingFormat numberFormat = _stylesheet.NumberingFormats.FirstOrDefault(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId) as NumberingFormat; // // // if (numberFormat != null) // { // if (numberFormat != null && numberFormat.FormatCode != null && numberFormat.FormatCode.HasValue) // { // string formatCode = numberFormat.FormatCode.Value; // if ((formatCode.ToLower().Contains("d") && formatCode.ToLower().Contains("m")) || // (formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("y")) || // (formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("d")) || // (formatCode.ToLower().Contains("h") && formatCode.ToLower().Contains("m")) || // (formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("s")) // ) // { // //DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture)); // //value = dateTime.ToString(); // double tmp = 0; // if (double.TryParse(c.CellValue.Text, out tmp)) value = ExcelHelper.FromExcelSerialDate(tmp).ToString(); // else value = c.CellValue.Text; // } // } // } // } //} if (string.IsNullOrEmpty(value)) { value = c.CellValue.Text; } // define index based on cell refernce - offset int index = cellReferencAsInterger - offset - 1; rowAsStringArray[index] = value; } }//end if cell value else { int index = cellReferencAsInterger - offset - 1; rowAsStringArray[index] = ""; } } //end if cell null } //for // replace all null values with ""; for (int i = 0; i < rowAsStringArray.Length; i++) { if (rowAsStringArray[i] == null) { rowAsStringArray[i] = ""; } } return(rowAsStringArray.ToList()); }
public void ParseXlsx() { try { using (Stream stream = File.Open(thrlistPath, FileMode.Open)) using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); int t = -1; int i = 0; foreach (Row r in sheetData.Elements <Row>().Skip(2)) { threats.Add(new ThreatModel()); t++; i = 0; foreach (Cell c in r.Elements <Cell>().Take(8)) { switch (i) { case 0: threats[t].ThreatId = Int32.Parse(c.InnerText); break; case 1: int id = Int32.Parse(c.InnerText); SharedStringItem item = GetSharedStringItemById(workbookPart, id); threats[t].ThreatName = item.Text.Text; break; case 2: id = Int32.Parse(c.InnerText); item = GetSharedStringItemById(workbookPart, id); threats[t].Description = item.Text.Text; break; case 3: id = Int32.Parse(c.InnerText); item = GetSharedStringItemById(workbookPart, id); threats[t].Source = item.Text.Text; break; case 4: id = Int32.Parse(c.InnerText); item = GetSharedStringItemById(workbookPart, id); threats[t].Target = item.Text.Text; break; case 5: threats[t].ConfidentialityBreach = (c.InnerText == "1") ? true : false; break; case 6: threats[t].IntegrityViolation = (c.InnerText == "1") ? true : false; break; case 7: threats[t].AccessViolation = (c.InnerText == "1") ? true : false; break; default: break; } i++; } } } } catch (FileFormatException) { MessageBox.Show("Your file seems to be corrupted or has wrong format"); } catch (DirectoryNotFoundException) { MessageBox.Show("Directory not found \n App (project) should have 'Data' in the root folder"); } catch (FileNotFoundException) { MessageBox.Show("Missing file or disk fails"); } catch (IOException) { MessageBox.Show("Refresh button needs to rest... Please try again"); } catch (UnauthorizedAccessException) { MessageBox.Show("OS denies acces to file \n" + " Try again and...\n May the Force be with you"); } catch (Exception) { MessageBox.Show("We made a little whoopsie --- Already fixing! \n Please try again"); } finally { updateThreats = threats; Paginate(threats); currentPage = 0; PreviousPage.Visibility = Visibility.Collapsed; if (Pages.Count == 1) { NextPage.Visibility = Visibility.Collapsed; } else { NextPage.Visibility = Visibility.Visible; } Table.ItemsSource = null; Table.ItemsSource = Pages[0]; RefreshFile.Visibility = Visibility.Visible; } }
/// <summary> /// Convert a excel row to a list of strings /// Every cell is one value /// </summary> /// <remarks></remarks> /// <seealso cref=""/> /// <param name="r"> row from a excel file</param> /// <returns>list of string for each cell in the row</returns> private List <string> RowToList(Row r, List <long> varIds) { List <int> columns = new List <int>(); foreach (long id in varIds) { //get the index of the variableintifier where id euqls id from varids int columnPosition = GetColumnNumber(this._areaOfData.StartColumn) + this.SubmitedVariableIdentifiers.IndexOf(this.SubmitedVariableIdentifiers.Where(p => p.id.Equals(id)).FirstOrDefault()); columns.Add(columnPosition); } List <string> rowAsStringArray = new List <string>(); // create a new cell Cell c = new Cell(); for (int i = 0; i < r.ChildElements.Count(); i++) { // get current cell at i c = r.Elements <Cell>().ElementAt(i); string value = ""; if (c != null) { int cellReferencAsInterger = GetColumnNumber(GetColumnName(c.CellReference)); if (c.CellValue != null) { // if cell reference in range of the area int start = GetColumnNumber(this._areaOfData.StartColumn); int end = GetColumnNumber(this._areaOfData.EndColumn); if (columns.Contains(cellReferencAsInterger)) { // if Value a text if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.SharedString) { int sharedStringIndex = int.Parse(c.CellValue.Text, CultureInfo.InvariantCulture); SharedStringItem sharedStringItem = _sharedStrings[sharedStringIndex]; value = sharedStringItem.InnerText; Debug.WriteLine(value); } // not a text else if (c.StyleIndex != null && c.StyleIndex.HasValue) { uint styleIndex = c.StyleIndex.Value; CellFormat cellFormat = _stylesheet.CellFormats.ChildElements[(int)styleIndex] as CellFormat; if (cellFormat.ApplyNumberFormat != null && cellFormat.ApplyNumberFormat.HasValue && cellFormat.ApplyNumberFormat.Value && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue) { uint numberFormatId = cellFormat.NumberFormatId.Value; // Number format 14-22 and 45-47 are built-in date and/or time formats if ((numberFormatId >= 14 && numberFormatId <= 22) || (numberFormatId >= 45 && numberFormatId <= 47)) { double tmp = 0; if (double.TryParse(c.CellValue.Text, out tmp)) { value = ExcelHelper.FromExcelSerialDate(tmp).ToString(); } else { value = c.CellValue.Text; } } else { if (_stylesheet.NumberingFormats != null && _stylesheet.NumberingFormats.Any(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId)) { NumberingFormat numberFormat = _stylesheet.NumberingFormats.First(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId) as NumberingFormat; if (numberFormat != null && numberFormat.FormatCode != null && numberFormat.FormatCode.HasValue) { string formatCode = numberFormat.FormatCode.Value; if ((formatCode.Contains("h") && formatCode.Contains("m")) || (formatCode.Contains("m") && formatCode.Contains("d"))) { double tmp = 0; if (double.TryParse(c.CellValue.Text, out tmp)) { value = ExcelHelper.FromExcelSerialDate(tmp).ToString(); } else { value = c.CellValue.Text; } } else { value = c.CellValue.Text; } } else { value = c.CellValue.Text; } } else { value = c.CellValue.Text; } } } else { value = c.CellValue.Text; } } // define index based on cell refernce - offset //int index = cellReferencAsInterger - offset - 1; if (columns.Contains(cellReferencAsInterger)) { rowAsStringArray.Add(value); } } }//end if cell value else { if (columns.Contains(cellReferencAsInterger)) { rowAsStringArray.Add(value); } } } //end if cell null } //for return(rowAsStringArray); }
/// <summary> /// Form an SLRstType from DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class. /// </summary> /// <param name="SharedString">A source DocumentFormat.OpenXml.Spreadsheet.SharedStringItem class.</param> public void FromSharedStringItem(SharedStringItem SharedString) { istrReal.InnerXml = SharedString.InnerXml; }
private string GetCellValue(Cell cell, SharedStringItem[] items) { string txt = string.Empty; if (cell.CellValue != null) txt = cell.CellValue.Text; if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) txt = items[int.Parse(txt)].InnerText; return txt; }
public string GenerateJsonTable(SheetFormat sheetFormat, String worksheetUri) { // open excel file SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(this.fileStream, false); // get workbookpart WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; _sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray(); _stylesheet = workbookPart.WorkbookStylesPart.Stylesheet; WorksheetPart worksheetPart = null; foreach (Sheet worksheet in workbookPart.Workbook.Descendants <Sheet>()) { //Get the current worksheetpart and see if it is the correct one WorksheetPart tmp = (WorksheetPart)workbookPart.GetPartById(worksheet.Id); if (tmp.Uri.ToString() == worksheetUri) { //Found the correct WorksheetPart worksheetPart = tmp; } } OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); int expectedRowIndex = 1; while (reader.Read()) { if (reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Row)) { do { DocumentFormat.OpenXml.Spreadsheet.Row row = (DocumentFormat.OpenXml.Spreadsheet.Row)reader.LoadCurrentElement(); List <String> rowAsStringList = new List <string>(); //Since this library will ignore empty rows, check if we skipped some and add empty rows if necessary //This will still ignore empty rows at the end of the file but those wouldn't have any influence on the indices of data & header anyway while (row.RowIndex > expectedRowIndex) { List <String> dummyRow = new List <string>(); dummyRow.Add(""); table.Add(dummyRow); expectedRowIndex++; } // create a new cell Cell c = new Cell(); int expectedIndex = 0; //To check whether we skipped cells because they were empty for (int i = 0; i < row.ChildElements.Count(); i++) { // get current cell at i c = row.Elements <Cell>().ElementAt(i); string value = ""; if (c != null) { //See if cells have been skipped (empty cells are not contained in the xml and therefore not contained in row.ChildElements) //See: https://stackoverflow.com/a/3981249 // Gets the column index of the cell with data int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(c.CellReference)); if (expectedIndex < cellColumnIndex) { //We skipped one or more cells so add some blank data do { rowAsStringList.Add(""); //Insert blank data expectedIndex++; }while (expectedIndex < cellColumnIndex); } //We now have the correct index and can grab the value of the cell if (c.CellValue != null) { // if Value a text if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.SharedString) { int sharedStringIndex = int.Parse(c.CellValue.Text, CultureInfo.InvariantCulture); SharedStringItem sharedStringItem = _sharedStrings[sharedStringIndex]; value = sharedStringItem.InnerText; } //If cell contains boolean (doesn't always work for files saved with libre office) else if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.Boolean) { if (c.InnerText == "1") { value = "true"; } else { value = "false"; } } // not a text else if (c.StyleIndex != null && c.StyleIndex.HasValue) { uint styleIndex = c.StyleIndex.Value; CellFormat cellFormat = _stylesheet.CellFormats.ChildElements[(int)styleIndex] as CellFormat; if (cellFormat.ApplyNumberFormat != null && cellFormat.ApplyNumberFormat.HasValue && cellFormat.ApplyNumberFormat.Value && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue) { uint numberFormatId = cellFormat.NumberFormatId.Value; // Number format 14-22 and 45-47 are built-in date and/or time formats if ((numberFormatId >= 14 && numberFormatId <= 22) || (numberFormatId >= 45 && numberFormatId <= 47)) { DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture)); value = dateTime.ToString(); } else { if (_stylesheet.NumberingFormats != null && _stylesheet.NumberingFormats.Any(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId)) { NumberingFormat numberFormat = _stylesheet.NumberingFormats.First(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId) as NumberingFormat; if (numberFormat != null && numberFormat.FormatCode != null && numberFormat.FormatCode.HasValue) { string formatCode = numberFormat.FormatCode.Value; if ((formatCode.Contains("h") && formatCode.Contains("m")) || (formatCode.Contains("m") && formatCode.Contains("d"))) { DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture)); value = dateTime.ToString(); } else { value = c.CellValue.Text; } } else { value = c.CellValue.Text; } } else { value = c.CellValue.Text; } } } else { value = c.CellValue.Text; } } else { value = c.CellValue.Text; } rowAsStringList.Add(value); }//end if cell value null else { rowAsStringList.Add(""); } }//end if cell null expectedIndex++; }//for children of row //Check if there's a new max length for the length of a row maxCellCount = Math.Max(maxCellCount, rowAsStringList.Count); //Just read a row, so increase the expected index for the next one expectedRowIndex++; table.Add(rowAsStringList); } while (reader.ReadNextSibling()); // Skip to the next row break; } } //Make sure each row has the same number of values in it foreach (List <String> row in table) { while (row.Count < maxCellCount) { row.Add(""); } } //Convert the Lists to Arrays List <String>[] rowArray = table.ToArray(); //The elements of the Array are the rows in form of String-lists String[][] tableArray = new String[rowArray.Length][]; for (int i = 0; i < rowArray.Length; i++) { tableArray[i] = rowArray[i].ToArray(); } return(JsonConvert.SerializeObject(tableArray)); }
// 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; }
// 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; }
public DataCache(XlsxWrapper xlsxWrapper) { owner = xlsxWrapper; WorkbookPart wbPart = xlsxWrapper.spreadsheetDocument.WorkbookPart; List <string> sharedStringCache = new List <string>(); if (wbPart.SharedStringTablePart != null) { using (OpenXmlReader shareStringReader = OpenXmlReader.Create(wbPart.SharedStringTablePart)) { while (shareStringReader.Read()) { if (shareStringReader.ElementType == typeof(SharedStringItem)) { SharedStringItem stringItem = (SharedStringItem)shareStringReader.LoadCurrentElement(); sharedStringCache.Add(stringItem.Text?.Text ?? string.Empty); } } } } foreach (WorksheetPart sheetPart in wbPart.WorksheetParts) { void AddEntry(CellRef reference, string value) { if (value2adress.ContainsKey(value)) { value2adress[value].Add(reference); } else { value2adress[value] = new List <CellRef>() { reference } }; adress2value[reference] = value; } string sheetID = wbPart.GetIdOfPart(sheetPart); using (OpenXmlReader sheetReader = OpenXmlReader.Create(sheetPart)) { while (sheetReader.Read()) { if (sheetReader.ElementType == typeof(Cell)) { Cell cell = (Cell)sheetReader.LoadCurrentElement(); CellRef cellAdress = new CellRef() { sheetID = sheetID, adress = cell.CellReference }; if ((cell.DataType?.Value ?? CellValues.Error) == CellValues.SharedString) { AddEntry( cellAdress, sharedStringCache[Int32.Parse(cell.CellValue.InnerText)]); } else { AddEntry(cellAdress, owner.GetStringValue(cell)); } } } } } } }
public static void ReadFile() { using (SpreadsheetDocument doc = SpreadsheetDocument.Open("File1.xlsx", false)) { //create the object for workbook part WorkbookPart workbookPart = doc.WorkbookPart; Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>(); StringBuilder excelResult = new StringBuilder(); //using for each loop to get the sheet from the sheetcollection foreach (Sheet thesheet in thesheetcollection) { excelResult.AppendLine("Excel Sheet Name : " + thesheet.Name); excelResult.AppendLine("----------------------------------------------- "); //statement to get the worksheet object by using the sheet id Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet; SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild <SheetData>(); foreach (Row thecurrentrow in thesheetdata) { foreach (Cell thecurrentcell in thecurrentrow) { //statement to take the integer value string currentcellvalue = string.Empty; if (thecurrentcell.DataType != null) { if (thecurrentcell.DataType == CellValues.SharedString) { int id; if (Int32.TryParse(thecurrentcell.InnerText, out id)) { SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id); if (item.Text != null) { //code to take the string value excelResult.Append(item.Text.Text + " "); } else if (item.InnerText != null) { currentcellvalue = item.InnerText; } else if (item.InnerXml != null) { currentcellvalue = item.InnerXml; } } } } else { excelResult.Append(Convert.ToInt16(thecurrentcell.InnerText) + " "); } } excelResult.AppendLine(); } excelResult.Append(""); Console.WriteLine(excelResult.ToString()); Console.ReadLine(); } } }
public void ReadExcelFile() { try { string strDoc = @"C:\Users\Public\Downloads\Book1.xlsx"; //Lets open the existing excel file and read through its content . Open the excel using openxml sdk using (SpreadsheetDocument doc = SpreadsheetDocument.Open(strDoc, false)) { //create the object for workbook part WorkbookPart workbookPart = doc.WorkbookPart; Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>(); List <SimCard> simCards = new List <SimCard>(); int sheetLevel = 1; int Started = 1; bool IsAdded = false; //using for each loop to get the sheet from the sheetcollection if (sheetLevel == 1) { foreach (Sheet thesheet in thesheetcollection) { //statement to get the worksheet object by using the sheet id Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet; SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild <SheetData>(); foreach (Row thecurrentrow in thesheetdata) { SimCard simCardItem = new SimCard(); foreach (Cell thecurrentcell in thecurrentrow) { var holdData = ""; //statement to take the integer value string currentcellvalue = string.Empty; if (thecurrentcell.DataType != null) { if (thecurrentcell.DataType == CellValues.SharedString) { if (int.TryParse(thecurrentcell.InnerText, out int id)) { SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id); if (item.InnerText != null) { currentcellvalue = item.InnerText; } } } holdData = currentcellvalue; } else { holdData = thecurrentcell.InnerText ?? Convert.ToInt16(thecurrentcell.InnerText) + " "; } int tamp = GetCellNumber(thecurrentcell.CellReference.Value.ToString()); if (tamp > Started && holdData != null) { IsAdded = true; string firstChar = thecurrentcell.CellReference.Value[0].ToString(); if (firstChar == "B") { simCardItem.Name = holdData.ToString(); ///// simCardItem.Supplier = GetSupplier(GetSimCardNumber(holdData.ToString())); } else if (firstChar == "C") { simCardItem.Price = int.Parse(holdData); } } else { IsAdded = false; } } if (IsAdded) { simCards.Add(simCardItem); } //RowStarted++; //_logger.LogTrace("insert ..." + RowStarted); //if (RowStarted >= RowLimit) //{ // _uow.GetRepository<SimCard>().Add(simCards); // _uow.SaveChanges(); // RowStarted = 1; // simCards = new List<SimCard>(); //} } _uow.GetRepository <SimCard>().Add(simCards); _uow.SaveChanges(); } } sheetLevel++; } } catch (Exception ex) { Logger.LogError(ex.Message.ToString()); throw ex; } }
// 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; }
public void Main() { string user, password, path; // クラウドサービスのリストを取得する List <string> listCloudService = new List <string>(); int index = -1; string systemPath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location).ToString(); if (!(File.Exists(systemPath + "\\CloudServices_Information\\CloudServices.xlsx"))) { //log.WriteLog("ファイルエクセルクラウドサービス情報が存在しません", null); Console.WriteLine("ファイルエクセルクラウドサービス情報が存在しません"); return; } // Excelファイル情報クラウドサービスへのパス path = systemPath + "\\CloudServices_Information\\CloudServices.xlsx"; SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false); WorkbookPart workbookPart = doc.WorkbookPart; Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById("rId1")).Worksheet; SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>(); var lastRow = thesheetdata.Descendants <Row>().LastOrDefault(); var lastRowToInt = Int32.Parse(lastRow.RowIndex); Cell theCellUser = (Cell)thesheetdata.ElementAt(1).ChildElements.ElementAt(4); SharedStringItem textUser = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellUser.InnerText)); user = textUser.Text.Text; Cell theCellPassword = (Cell)thesheetdata.ElementAt(1).ChildElements.ElementAt(5); SharedStringItem textPassword = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellPassword.InnerText)); password = textPassword.Text.Text; // クラウドサービスのステータスのリストを取得する for (int i = 1; i < lastRowToInt - 1; i++) { Cell theCellCheck = (Cell)thesheetdata.ElementAt(i).ChildElements.ElementAt(1); Int32.TryParse(theCellCheck.InnerText, out index); string textCheck = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(index).InnerText; if ((String.Compare("〇", textCheck)) == 0) { Cell theCellCloudService = (Cell)thesheetdata.ElementAt(i).ChildElements.ElementAt(2); SharedStringItem textCloudService = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellCloudService.InnerText)); listCloudService.Add(textCloudService.Text.Text); } } var stateStoping = "stop"; MyTestCaseTest test_case = new MyTestCaseTest(); test_case.SetUp(); test_case.StartAzure(user, password); for (int i = 0; i < listCloudService.Count; i++) { if (test_case.TestCases(listCloudService[i], stateStoping) == true) { //log.WriteLog(stateStoping + " " + listCloudService[i], "OK"); Console.WriteLine(stateStoping + " " + listCloudService[i] + " " + "OK"); } else { //log.WriteLog(stateStoping + " " + listCloudService[i], "ERROR"); Console.WriteLine(stateStoping + " " + listCloudService[i] + " " + "ERROR"); } } test_case.TearDown(); }
// 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; }
// Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer // reference the specified SharedStringItem and removes the item. private static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document) { bool remove = true; foreach (var part in document.WorkbookPart.GetPartsOfType <WorksheetPart>()) { Worksheet worksheet = part.Worksheet; foreach (var cell in worksheet.GetFirstChild <SheetData>().Descendants <Cell>()) { // Verify if other cells in the document reference the item. if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString && cell.CellValue.Text == shareStringId.ToString()) { // Other cells in the document still reference the item. Do not remove the item. remove = false; break; } } if (!remove) { break; } } // Other cells in the document do not reference the item. Remove the item. if (remove) { SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart; if (shareStringTablePart == null) { return; } SharedStringItem item = shareStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(shareStringId); if (item != null) { item.Remove(); // Refresh all the shared string references. foreach (var part in document.WorkbookPart.GetPartsOfType <WorksheetPart>()) { Worksheet worksheet = part.Worksheet; foreach (var cell in worksheet.GetFirstChild <SheetData>().Descendants <Cell>()) { if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) { int itemIndex = int.Parse(cell.CellValue.Text); if (itemIndex > shareStringId) { cell.CellValue.Text = (itemIndex - 1).ToString(); } } } worksheet.Save(); } document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save(); } } }
// 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; }
public bool UploadLookups(int enterpriseId, int userId, IFormsRepository formsRepo, string excelFileName) { def_LookupMaster lookupMaster = formsRepo.GetLookupMastersByLookupCode("ADAP_CLINIC"); if (lookupMaster == null) { throw new Exception("Cannot find lookup master: code ADAP_CLINIC"); } MarkAllLookupsInactive(lookupMaster.lookupMasterId, enterpriseId, formsRepo); int dataStartingRow = 7; Workbook workBook; SharedStringTable sharedStrings; IEnumerable <Sheet> workSheets; WorksheetPart recSheet; try { //Excel.Application xlApp; //Excel.Workbook xlWorkBook; //Excel.Worksheet xlWorkSheet; //Excel.Range range; //xlApp = new Excel.Application(); //xlWorkBook = xlApp.Workbooks.Open(excelFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //range = xlWorkSheet.UsedRange; //for (int rCnt = dataStartingRow; rCnt <= range.Rows.Count; rCnt++) //{ // // Create string array for row to pass to UpdateLookup function // string[] dataValues = new string[range.Columns.Count]; // int index = 0; // for (int cCnt = 1; cCnt <= range.Columns.Count; cCnt++) // { // string str = (range.Cells[rCnt, cCnt] as Excel.Range).Text.ToString(); // dataValues[index] = str; // index++; // } // if (arrayNotEmpty(dataValues)) // { // UpdateLookups(enterpriseId, dataValues, formsRepo); // } //} //xlWorkBook.Close(true, null, null); //xlApp.Quit(); //return true; //Declare helper variables. string recID; //Open the Excel workbook. using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFileName, true)) { //References to the workbook and Shared String Table. workBook = document.WorkbookPart.Workbook; workSheets = workBook.Descendants <Sheet>(); sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable; //Reference to Excel Worksheet with Recipient data. //recID = workSheets.First(s => s.Name == strWorkSheetName).Id; recID = workSheets.First().Id; recSheet = (WorksheetPart)document.WorkbookPart.GetPartById(recID); //LINQ query to skip first row with column names. IEnumerable <Row> dataRows = from row in recSheet.Worksheet.Descendants <Row>() where row.RowIndex >= dataStartingRow select row; // Count the number of columns for headers. IEnumerable <Row> templateHeader = from row in recSheet.Worksheet.Descendants <Row>() where row.RowIndex == (dataStartingRow - 1) select row; int numCols = templateHeader.First().Count(); foreach (Row row in dataRows) { //If a column is missing from row, add an empty value IEnumerable <Cell> cells = from cell in row.Descendants <Cell>() select cell; int columnCounter = 0; List <string> dataValues = new List <string>(); foreach (var cell in cells) { string colReference = cell.CellReference; int colIndex = GetColumnIndexFromName(GetColumnName(colReference)); // if column index is higher than what it should be, add blank space to it until it matches up. if (columnCounter < colIndex) { for (int i = columnCounter; i < colIndex; i++) { dataValues.Add(""); } } string cellValue = string.Empty; if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString)) { int id = -1; if (Int32.TryParse(cell.InnerText, out id)) { SharedStringItem item = GetSharedStringItemById(document.WorkbookPart, id); if (item.Text != null) { cellValue = item.Text.Text; } else if (item.InnerText != null) { cellValue = item.InnerText; } else if (item.InnerXml != null) { cellValue = item.InnerXml; } } } else if (cell.CellValue != null) { cellValue = cell.CellValue.InnerText; } dataValues.Add(cellValue); columnCounter = colIndex + 1; } if (dataValues.Count() < numCols) { for (int i = dataValues.Count(); i < numCols; i++) { dataValues.Add(""); } } else if (dataValues.Count() > numCols) { // if dataValues is more than number of column headers, delete the last data for (int i = dataValues.Count(); i > numCols; i--) { dataValues.RemoveAt(i - 1); } } if (arrayNotEmpty(dataValues.ToArray())) { UpdateLookups(enterpriseId, dataValues.ToArray(), formsRepo); } else { break; } } document.Close(); } return(true); } catch (Exception ex) { Console.WriteLine(ex.Message); throw ex; return(false); } }