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;
        }
Beispiel #5
0
 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
                    {
                        ;
                    }
                }
            }
        }
Beispiel #8
0
        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);
        }
Beispiel #11
0
        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;
        }
Beispiel #15
0
        /// <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());
        }
Beispiel #16
0
        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;
        }
Beispiel #18
0
        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;
        }
Beispiel #24
0
        //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;
            }
        }
Beispiel #25
0
        // Generates content of sharedStringTablePart1.
        protected override void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart)
        {
            SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)65U, UniqueCount = (UInt32Value)42U };

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

            sharedStringItem1.Append(text1);

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

            sharedStringItem2.Append(text2);

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

            sharedStringItem3.Append(text3);

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

            sharedStringItem4.Append(text4);

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

            sharedStringItem5.Append(text5);

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

            sharedStringItem6.Append(text6);

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

            sharedStringItem7.Append(text7);

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

            sharedStringItem8.Append(text8);

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

            sharedStringItem9.Append(text9);

            var bankRequisitesSubject = sampleSelectionRequest.Executor;

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

            sharedStringItem10.Append(text10);

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

            sharedStringItem11.Append(text11);

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

            sharedStringItem12.Append(text12);

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

            sharedStringItem13.Append(text13);

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

            sharedStringItem14.Append(text14);

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

            sharedStringItem15.Append(text15);

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

            sharedStringItem16.Append(text16);

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

            sharedStringItem17.Append(text17);

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

            sharedStringItem18.Append(text18);

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

            sharedStringItem19.Append(text19);

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

            sharedStringItem20.Append(text20);

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

            sharedStringItem21.Append(text21);

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

            sharedStringItem22.Append(text22);

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

            sharedStringItem23.Append(text23);

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

            sharedStringItem24.Append(text24);

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

            sharedStringItem25.Append(text25);

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

            sharedStringItem26.Append(text26);

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

            sharedStringItem27.Append(text27);

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

            sharedStringItem28.Append(text28);

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

            sharedStringItem29.Append(text29);

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

            sharedStringItem30.Append(text30);

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

            sharedStringItem31.Append(text31);

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

            sharedStringItem32.Append(text32);

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

            sharedStringItem33.Append(text33);

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

            sharedStringItem34.Append(text34);

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

            sharedStringItem35.Append(text35);

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

            sharedStringItem36.Append(text36);

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

            sharedStringItem37.Append(text37);

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

            sharedStringItem38.Append(text38);

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

            sharedStringItem39.Append(text39);

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

            sharedStringItem40.Append(text40);

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

            sharedStringItem41.Append(text41);

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

            sharedStringItem42.Append(text42);

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

            sharedStringItem43.Append(text43);

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

            sharedStringItem44.Append(text44);

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

            sharedStringTablePart.SharedStringTable = sharedStringTable1;
        }
        // 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];
            }
        }
Beispiel #28
0
        /// <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();
        }
Beispiel #29
0
        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;
        }
Beispiel #31
0
        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);
        }
Beispiel #32
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);
        }
Beispiel #33
0
        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)
            {
            }
        }
Beispiel #34
0
 /// <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;
        }
Beispiel #36
0
 /// <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;
 }
Beispiel #37
0
        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;
                }
            }
        }
Beispiel #38
0
        /// <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());
        }
Beispiel #39
0
        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;
            }
        }
Beispiel #40
0
        /// <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;
 }
Beispiel #43
0
        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));
                                }
                            }
                        }
                    }
                }
            }
        }
Beispiel #47
0
        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();
                }
            }
        }
Beispiel #48
0
        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;
        }
Beispiel #50
0
        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;
        }
Beispiel #52
0
        // 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);
            }
        }