Пример #1
0
        private void btn_cargar_Click(object sender, EventArgs e)
        {
            SLDocument            sl          = new SLDocument("C:/Users/alumno/Documents/archivoPlano.csv");
            SLWorksheetStatistics propiedades = sl.GetWorksheetStatistics();

            int ultimafila = propiedades.EndRowIndex;
        }
        ///
        /// REMOVE COURSES THAT DO NOT NEED MEETINGS CREATED FOR THEM
        ///
        public void CompareExcelDocumentAndLookupTable()
        {
            List <Course>         newCourses = new List <Course>();
            SLWorksheetStatistics stats      = _Excel_Document.GetWorksheetStatistics();
            int end = stats.EndRowIndex;

            if (_Excel_Document.GetCellValueAsString(1, 1).ToLower() != "course name" || _Excel_Document.GetCellValueAsString(1, 2).ToLower() != "number of meetings")
            {
                _valid = false;
                return;
            }

            for (int i = 2; i <= end; i++)
            {
                string current_course             = _Excel_Document.GetCellValueAsString(i, 1).Replace(" ", "_").ToUpper();
                int    current_number_of_meetings = _Excel_Document.GetCellValueAsInt32(i, 2);

                Course c = new Course(current_course, new List <Section>(), current_number_of_meetings);

                if (!newCourses.Contains(c) && current_course != "" && current_number_of_meetings != 0)
                {
                    newCourses.Add(c);
                }
            }

            _course_lookup.Clear();

            foreach (Course course in newCourses)
            {
                _course_lookup.Add(course.GetName(), course);
            }
        }
Пример #3
0
        public void validateClickstreams()
        {
            //Open Excel Clickstream *Report* File and extract list of clickstreamids
            SLDocument repform = new SLDocument(clickstreamfile);
            Dictionary <string, bool> reportclickstreams = new Dictionary <string, bool>();           //Bool indicates validated
            SLWorksheetStatistics     stats1             = repform.GetWorksheetStatistics();

            for (int j = repstartrow; j <= stats1.EndRowIndex; j++)
            {
                string clickstream    = repform.GetCellValueAsString(clickstreamcolumn + j);              //Clickstream name
                string clickstreamkey = repform.GetCellValueAsString(clickstreamkeycolumn + j);           //Key Message Name
                string presid         = repform.GetCellValueAsString(clickstreamprescolumn + j);          //Presentation ID
                try { reportclickstreams.Add(presid + "#" + clickstreamkey + "@" + clickstream, false); } //Concatenated PresID, Key and Clicksteam so can still just use string dictionaries in case can refactor later to be same as key message
                catch (Exception e) { if (e.HResult == -2147024809)
                                      {
                                      }
                                      else
                                      {
                                          throw e;
                                      } }                                                                    //Allow for duplicates
            }

            //Query all presentations from metadatasheets for CLickstream information
            ExtractClickStreamNames();
            //Run comparison
            CompareClickstreams(reportclickstreams);
        }
Пример #4
0
        public static string LeerXls(string full)
        {
            SLDocument            documento   = new SLDocument(full);
            SLWorksheetStatistics propiedades = documento.GetWorksheetStatistics();

            int ultimaColumna = propiedades.EndColumnIndex;

            ArrayList cabeza = new ArrayList();

            for (long i = 0; i < (ultimaColumna); i++)
            {
                string num = Letracolumna(i);
                string ca  = documento.GetCellValueAsString(num + 1);
                cabeza.Add(ca);
            }
            List <string> re = new List <string>();

            foreach (string sw in cabeza)
            {
                if (!re.Contains(sw))
                {
                    re.Add(sw);
                }
            }

            string cad = re[0];

            for (int i = 1; i < (re.Count); i++)
            {
                cad = cad + "," + re[i];
            }
            Console.WriteLine(cad);
            return(cad);
        }
Пример #5
0
        public void Load(string filePath)
        {
            if (!System.IO.File.Exists(filePath))
            {
                throw new System.IO.FileNotFoundException();
            }

            SLDocument document;

            try
            {
                document = new SLDocument(filePath); //Открываем Excel документ
            }
            catch
            {
                throw new System.Exception("Невозможно открыть документ");
            }

            SLWorksheetStatistics stats = document.GetWorksheetStatistics(); //Статистика документа

            int startColumnIndex = stats.StartColumnIndex;                   //Индекс первого столбца
            int headerRowIndex   = stats.StartRowIndex;                      //Индекс строки заголовков
            int startRowIndex    = headerRowIndex + 1;                       //Индекс первой строки данных

            TableHeader = new List <string>();
            //Получаем заголовки:
            for (int i = startColumnIndex; i <= stats.EndColumnIndex; ++i)
            {
                TableHeader.Add(document.GetCellValueAsString(headerRowIndex, i));
            }

            this.Columns.Clear();
            foreach (string h in TableHeader)
            {
                this.Columns.Add(new DataColumn(h)); //Заполняем DataTable заголовками столбцов
            }

            this.Rows.Clear();
            //Добавление строк в DataTable
            for (int rowIndex = startRowIndex; rowIndex <= stats.EndRowIndex; rowIndex++)
            {
                //Добавляем ячейки в словарь с ключами именами столбцов

                string[] newRow = new string[stats.NumberOfColumns]; //Массив ячеек строки

                for (int columnIndex = startColumnIndex; columnIndex <= stats.EndColumnIndex; columnIndex++)
                {
                    newRow[columnIndex - startColumnIndex] = document.GetCellValueAsString(rowIndex, columnIndex);
                }

                this.Rows.Add(newRow);
            }
            this.TableChanged();
        }
            protected virtual void SetupExcelDocument()
            {
                document.DocumentProperties.Creator        = "Asp Mvc Download Replicator";
                document.DocumentProperties.LastModifiedBy = "Asp Mvc Download Replicator";
                document.DocumentProperties.ContentStatus  = Status;
                document.DocumentProperties.Title          = DocumentName;
                document.DocumentProperties.Description    = String.IsNullOrEmpty(DocumentNo) ? DocumentName : String.Format("{0}-{1}", DocumentName, DocumentNo);

                documentStat = document.GetWorksheetStatistics();

                AdditionalSetupExcelDocument();
            }
        /// <summary>
        /// demonstrate how to get used columns in the format a a letter rather than an integer
        /// </summary>
        /// <returns></returns>
        public string[] UsedCellsInWorkSheet(string pFileName, string pSheetName)
        {
            using (var doc = new SLDocument(pFileName, pSheetName))
            {
                SLWorksheetStatistics stats = doc.GetWorksheetStatistics();

                IEnumerable <string> columnNames = Enumerable.Range(1, stats.EndColumnIndex)
                                                   // ReSharper disable once ConvertClosureToMethodGroup
                                                   .Select((cellIndex) => SLConvert.ToColumnName(cellIndex));

                return(columnNames.ToArray());
            }
        }
Пример #8
0
        private static DataSet ReadExcelFileSLDocument(string filePath, string sheetName = null)
        {
            var       ds             = new DataSet();
            DataTable dtStrongTyping = new DataTable("Sheet1");

            using (SLDocument sl = sheetName == null ? new SLDocument(filePath) : new SLDocument(filePath, sheetName))
            {
                SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
                int iStartColumnIndex       = stats.StartColumnIndex;

                //Get headerrow
                for (var colIndex = stats.StartColumnIndex; colIndex <= stats.EndColumnIndex; colIndex++)
                {
                    dtStrongTyping.Columns.Add(new DataColumn(sl.GetCellValueAsString(1, colIndex), typeof(string)));
                }

                for (var row = stats.StartRowIndex + 1; row <= stats.EndRowIndex; ++row)
                {
                    DataRow dr = dtStrongTyping.NewRow();
                    for (var colIndex = stats.StartColumnIndex; colIndex <= stats.EndColumnIndex; colIndex++)
                    {
                        //var fieldType = GetDataType(sl.GetCellStyle(row, colIndex).FormatCode);

                        if (IsDateTimeType(sl.GetCellStyle(row, colIndex).FormatCode))
                        {
                            //DateTime date;
                            //DateTime.TryParse(sl.GetCellValueAsString(row, colIndex), out date);
                            try
                            {
                                var dateNumber = Convert.ToInt32(sl.GetCellValueAsString(row, colIndex));
                                dr[colIndex - stats.StartColumnIndex] = FromExcelSerialDate(dateNumber).ToString("dd-MMM-yyyy");
                            }
                            catch (FormatException)
                            {
                                dr[colIndex - stats.StartColumnIndex] = sl.GetCellValueAsString(row, colIndex);
                            }
                        }
                        else
                        {
                            dr[colIndex - stats.StartColumnIndex] = sl.GetCellValueAsString(row, colIndex);
                        }
                    }
                    dtStrongTyping.Rows.Add(dr);
                }
            }
            ds.Tables.Add(dtStrongTyping);
            return(ds);
        }
Пример #9
0
        public SLDocument LoadTextFile(SLDocument sldocWorksheet, string strTextFileFullPath)
        {
            sldocWorksheet.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Delimited");

            SLTextImportOptions sltioWorksheetOptions = new SLTextImportOptions();

            sltioWorksheetOptions.Culture = new CultureInfo("en-US");
            SLWorksheetStatistics sldocstatsWorksheetStats = sldocWorksheet.GetWorksheetStatistics();
            int iLastColumnWithData = sldocstatsWorksheetStats.EndColumnIndex;

            for (int iCounter = 0; iCounter < iLastColumnWithData; iCounter++)
            {
                sltioWorksheetOptions.SetColumnFormat(iCounter, SLTextImportColumnFormatValues.Text);
            }

            sldocWorksheet.ImportText(strTextFileFullPath, "A1", sltioWorksheetOptions);

            return(sldocWorksheet);
        }
Пример #10
0
        ///
        /// RETURN A LIST OF INSTRUCTORS FROM A SPECIFIED EXCEL WORKBOOK
        ///
        public static List <Instructor> GetInstructorListFromExcelSpreadsheet(string file)
        {
            if (!ValidExcelDocument(file))
            {
                return(null);
            }

            List <Instructor> listOfInstructors = new List <Instructor>();

            _excelDocument = new SLDocument(file);
            _stats         = _excelDocument.GetWorksheetStatistics();

            string columnOne   = _excelDocument.GetCellValueAsString(1, 1).ToLower();
            string columnTwo   = _excelDocument.GetCellValueAsString(1, 2).ToLower();
            string columnThree = _excelDocument.GetCellValueAsString(1, 3).ToLower();

            if (columnOne != "first name" || columnTwo != "last name" || columnThree != "email")
            {
                LogInformation.AddLineToLogInformation("Error", "The first row of the spreadsheet must have the following columns: first name | last name | email");
                return(null);
            }

            int end = _stats.EndRowIndex + 1;

            for (int i = 2; i < end; i++)
            {
                string first = _excelDocument.GetCellValueAsString(i, 1);
                string last  = _excelDocument.GetCellValueAsString(i, 2);
                string email = _excelDocument.GetCellValueAsString(i, 3);
                if (first != "" && last != "" && email != "")
                {
                    Instructor inst = new Instructor(first, last, email);
                    listOfInstructors.Add(inst);
                }
                else
                {
                    LogInformation.AddLineToLogInformation("Error", "Row " + i + " is missing information and was skipped.");
                }
            }

            return(listOfInstructors);
        }
Пример #11
0
        public void SaveWorksheetToTextFile(SLDocument sldocWorksheet, string strPath)
        {
            using (TextWriter textwriterModifiedDialogExport = new StreamWriter(strPath + "dialogExport - Rename Pairs.txt"))
            {
                List <string>         strListOfRenamePairs = new List <string>();
                SLWorksheetStatistics sldocWorksheetStats  = sldocWorksheet.GetWorksheetStatistics();

                for (int iCounter = 2; iCounter < sldocWorksheetStats.EndRowIndex; iCounter++)
                {
                    string strResponseText    = sldocWorksheet.GetCellValueAsString("B" + iCounter.ToString());
                    string strFileName        = sldocWorksheet.GetCellValueAsString("A" + iCounter.ToString());
                    string strFinalStringLine = strFileName + strResponseText;
                    strListOfRenamePairs.Add(strFinalStringLine);
                }

                foreach (String strLineFromList in strListOfRenamePairs)
                {
                    textwriterModifiedDialogExport.WriteLine(strLineFromList);
                }
            }
        }
Пример #12
0
        public void ReadDimensions(string filename, string sheet, int rows)
        {
            using (SLDocument sl = new SLDocument(filename, sheet))
            {
                SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
                int iStartColumnIndex       = CollumnOffset;

                for (int row = RowOffset; (row <= (RowOffset + rows)); ++row)
                {
                    Dimensions.Add(new DimensionLine
                    {
                        Kote       = sl.GetCellValueAsString(row, iStartColumnIndex),
                        DeltaPlus  = sl.GetCellValueAsInt32(row, iStartColumnIndex + 7),
                        DeltaMinus = sl.GetCellValueAsInt32(row, iStartColumnIndex + 9),
                        Nazivno    = sl.GetCellValueAsInt32(row, iStartColumnIndex + 1),
                        Mjereno    = sl.GetCellValueAsInt32(row, iStartColumnIndex + 4),

                        //  Delta = sl.GetCellValueAsInt32(row, iStartColumnIndex + 10)
                    });
                }
            }
        }
Пример #13
0
        public void LeerExcel(string fileLocation)
        {
            if (File.Exists(fileLocation))
            {
                SLDocument            hoja    = new SLDocument(fileLocation);
                SLWorksheetStatistics statics = hoja.GetWorksheetStatistics();

                List <Record> registros = new List <Record>();
                Record        reg       = null;

                for (int i = 2; i < statics.EndRowIndex + 1; i++)
                {
                    if (string.IsNullOrEmpty(hoja.GetCellValueAsString(i, 1)) &&
                        string.IsNullOrEmpty(hoja.GetCellValueAsString(i, 2)) &&
                        string.IsNullOrEmpty(hoja.GetCellValueAsString(i, 3)))
                    {
                        break;
                    }
                    reg              = new Record();
                    reg.Marca        = hoja.GetCellValueAsString(i, 1);
                    reg.Fonema       = hoja.GetCellValueAsString(i, 2);
                    reg.Nogaceta     = hoja.GetCellValueAsInt32(i, 3);
                    reg.Fgaceta      = hoja.GetCellValueAsDateTime(i, 4);
                    reg.Codigo_clase = hoja.GetCellValueAsInt32(i, 5);
                    reg.Fpresenta    = hoja.GetCellValueAsDateTime(i, 6);
                    reg.Nopub        = hoja.GetCellValueAsInt32(i, 7);
                    reg.Noexp        = hoja.GetCellValueAsInt32(i, 8);
                    reg.Solicitant   = hoja.GetCellValueAsString(i, 9);
                    reg.Codigo_pais  = hoja.GetCellValueAsInt32(i, 10);
                    reg.Apoderado    = hoja.GetCellValueAsString(i, 11);
                    reg.Tipo         = hoja.GetCellValueAsString(i, 12);
                    reg.Fdigitacio   = hoja.GetCellValueAsDateTime(i, 13);

                    registros.Add(reg);
                }
                InsertarRegistroEnBD(registros);
                File.Delete(fileLocation);
            }
        }
Пример #14
0
        protected void ButtonMySQL_Click(object sender, EventArgs e)
        {
            //leemos el archivo con sldocument
            SLDocument            sl          = new SLDocument(@"C:\Users\13237\OneDrive\Desktop\crudDB.xlsx"); //ubicacion
            SLWorksheetStatistics propiedades = sl.GetWorksheetStatistics();                                    //traer las propiedades del archivo

            int             ultimaFila = propiedades.EndRowIndex;                                               //saber cuantas filas existen, trae la ultima fila
            MySqlConnection conexionBD = ClsMySQL.conexion();

            conexionBD.Open();                    //se abre la conexion

            for (int x = 2; x <= ultimaFila; x++) //leer todas las filas
            {
                //se trae el codigo y se le coloca un alias
                string sql = "INSERT INTO tabla_alumnos(Correlativo, Nombre, Parcial1, Parcial2, Parcial3, Parcial4, Seccion) " +
                             "VALUES(@Correlativo, @Nombre, @Parcial1, @Parcial2, @Parcial3, @Parcial4, @Seccion)";

                try
                {
                    //transaccion a mysql
                    //con el objeto se agrega el alias, el valor que se le asigna, la columna
                    MySqlCommand comando = new MySqlCommand(sql, conexionBD);
                    comando.Parameters.AddWithValue("@Correlativo", sl.GetCellValueAsString("A" + x));
                    comando.Parameters.AddWithValue("@Nombre", sl.GetCellValueAsString("B" + x));
                    comando.Parameters.AddWithValue("@Parcial1", sl.GetCellValueAsString("C" + x));
                    comando.Parameters.AddWithValue("@Parcial2", sl.GetCellValueAsString("D" + x));
                    comando.Parameters.AddWithValue("@Parcial3", sl.GetCellValueAsString("E" + x));
                    comando.Parameters.AddWithValue("@Parcial4", sl.GetCellValueAsString("F" + x));
                    comando.Parameters.AddWithValue("@Seccion", sl.GetCellValueAsString("G" + x));
                    comando.ExecuteNonQuery(); //se ejecuta la insercion
                }//si hay un error se utiliza mysqlexception
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }//si funciona aparecerá un messagebox
            MessageBox.Show("El archivo se ha cargado");
        }
Пример #15
0
        /// <summary>
        /// Based on ImportDataTable2, added background color to odd rows,
        /// conditional formatting, in this case the State column.
        /// </summary>
        /// <param name="pFileName">Existing excel file</param>
        /// <param name="pStartReference">Row to start import</param>
        /// <param name="pDataTable">Data source</param>
        /// <param name="pIncludeHeaders">Should column headers be used (currently only does headers)</param>
        /// <returns></returns>
        public bool ImportDataTable4(string pFileName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true)
        {
            bool success = false;

            var sheetName = "Demo4";

            try
            {
                // open to Sheet1 (will always exists in this code sample)
                using (SLDocument doc = new SLDocument(pFileName, "Sheet1"))
                {
                    var helper = new LightHelpers();
                    if (helper.SheetExists(pFileName, sheetName))
                    {
                        doc.DeleteWorksheet(sheetName);
                    }

                    // add the sheet
                    doc.AddWorksheet(sheetName);

                    var isSet = doc.SelectWorksheet(sheetName);

                    // next three lines are responsible for removal of grid-lines in the current WorkSheet
                    SLPageSettings ps = new SLPageSettings();
                    ps.ShowGridLines = false;
                    doc.SetPageSettings(ps);

                    // setup a format for the date column
                    var birthdayColumnStyle = doc.CreateStyle();
                    birthdayColumnStyle.FormatCode = "mm-dd-yyyy";

                    // create style for odd rows (we skip the header row)
                    var oddRowStyle = doc.CreateStyle();
                    oddRowStyle.SetPatternFill(PatternValues.LightGray, SLThemeColorIndexValues.Accent3Color, SLThemeColorIndexValues.Accent3Color);

                    var moCellStyle = doc.CreateStyle();
                    moCellStyle.Font.Bold      = true;
                    moCellStyle.Font.FontColor = SysDraw.Color.Red;

                    // since we need these several times, set them up as variables
                    var activeCellIndex   = pDataTable.Columns["Active"].Ordinal + 1;
                    var birthDayCellIndex = pDataTable.Columns["BirthDay"].Ordinal + 1;

                    // set birthday style to our data
                    doc.SetCellStyle(2, birthDayCellIndex, pDataTable.Rows.Count + 1, birthDayCellIndex, birthdayColumnStyle);

                    // do the Datatable import.
                    doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);

                    SLWorksheetStatistics stats = doc.GetWorksheetStatistics();

                    // change format of the bool column
                    for (int row = 1; row < stats.EndRowIndex + 1; row++)
                    {
                        var value = doc.GetCellValueAsString(row, activeCellIndex);
                        if (value == "TRUE")
                        {
                            doc.SetCellValue(row, activeCellIndex, "Yes");
                        }
                        else if (value == "FALSE")
                        {
                            doc.SetCellValue(row, activeCellIndex, "No");
                        }

                        // apply background color style to odd rows
                        if (row.IsOdd() && row > 1)
                        {
                            doc.SetRowStyle(row, oddRowStyle);
                        }
                        else
                        {
                            var cellReference = SLConvert.ToCellReference(row, 6);
                            var stateValue    = doc.GetCellValueAsString(cellReference);
                            if (stateValue == "MO")
                            {
                                doc.SetCellStyle(cellReference, moCellStyle);
                            }
                        }
                    }

                    // delete the primary key column
                    doc.DeleteColumn(1, 1);

                    // does what it says, auto-fit cell content
                    doc.AutoFitColumn(2, pDataTable.Columns.Count);

                    // de-select the current sheet and select sheet1
                    doc.SelectWorksheet("Sheet1");
                    doc.Save();

                    success = true;
                }
            }
            catch (Exception ex)
            {
                HasErrors        = true;
                ExceptionMessage = ex.Message;
                success          = false;
            }

            return(success);
        }
Пример #16
0
        ///
        /// RETURN A LIST OF COURSES FROM A SPECIFIED EXCEL WORKBOOK
        ///
        public static List <Course> GetCourseListFromExcelSpreadsheet(string file)
        {
            if (!ValidExcelDocument(file))
            {
                LogInformation.AddLineToLogInformation("Error", "The file must be an Excel file with the extension .xlsx");
                return(null);
            }

            _excelDocument = new SLDocument(file);
            _stats         = _excelDocument.GetWorksheetStatistics();

            string columnOne   = _excelDocument.GetCellValueAsString(1, 1).ToLower();
            string columnTwo   = _excelDocument.GetCellValueAsString(1, 2).ToLower();
            string columnThree = _excelDocument.GetCellValueAsString(1, 3).ToLower();

            if (columnOne != "course name" || columnTwo != "section" || columnThree != "number of meetings")
            {
                LogInformation.AddLineToLogInformation("Error", "The first row of the spreadsheet must have the following columns: course name | section | number of meetings");
                return(null);
            }

            List <Course> listOfCourses = new List <Course>();

            int end = _stats.EndRowIndex + 1;

            for (int i = 2; i < end; i++)
            {
                bool   valid            = true;
                string name             = _excelDocument.GetCellValueAsString(i, 1).ToUpper();
                string section          = _excelDocument.GetCellValueAsString(i, 2);
                int    numberOfMeetings = _excelDocument.GetCellValueAsInt32(i, 3);

                if (name == "")
                {
                    LogInformation.AddLineToLogInformation("Error", "Row " + i + " is missing the name of the course and was skipped.");
                    valid = false;
                }

                if (section == "")
                {
                    LogInformation.AddLineToLogInformation("Error", "Row " + i + " is missing the section and was skipped.");
                    valid = false;
                }

                if (valid)
                {
                    List <Section> sections = new List <Section>();
                    Section        s        = new Section(section, numberOfMeetings);
                    sections.Add(s);
                    Course c = new Course(name, sections, numberOfMeetings);
                    if (listOfCourses.Contains(c))
                    {
                        int idx = listOfCourses.IndexOf(c);
                        listOfCourses[idx].AddSections(sections);
                    }
                    else
                    {
                        listOfCourses.Add(c);
                    }
                }
            }

            return(listOfCourses);
        }
Пример #17
0
        public List <DocType> ImportEXCEL(string fname, int ntime, int etime)
        {
            if (!System.IO.File.Exists(fname))
            {
                return(new List <DocType>());
            }
            ;
            try
            {
                SLDocument            sl    = new SLDocument(fname, "工作表1");
                SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
                if (stats.EndRowIndex <= 0)
                {
                    return(new List <DocType>());
                }
                List <DocType> ndocs = new List <DocType>();
                for (int i = 0; i < stats.EndRowIndex; i++)
                {
                    if (string.IsNullOrEmpty(sl.GetCellValueAsString(i + 2, 2)))
                    {
                        break;
                    }
                    DocType docs = new DocType
                    {
                        //Index = sl.GetCellValueAsString(i + 2, 1),
                        ID      = sl.GetCellValueAsString(i + 2, 2).Trim(),
                        Color   = sl.GetCellStyle(i + 2, 2).Font.FontColor.ToString(),
                        webID   = sl.GetCellValueAsString(i + 2, 13).Trim(),
                        Name    = sl.GetCellValueAsString(i + 2, 4).Trim(),
                        Version = sl.GetCellValueAsString(i + 2, 5).Trim(),
                        Depart  = sl.GetCellValueAsString(i + 2, 6).Trim(),
                        doctp   = sl.GetCellValueAsString(i + 2, 7).Trim(),
                        Stime   = sl.GetCellValueAsDateTime(i + 2, 8),
                        Rtime   = sl.GetCellValueAsDateTime(i + 2, 9),
                        Own     = sl.GetCellValueAsString(i + 2, 11).Trim()
                    };
                    docs.Ntime = docs.Rtime.AddYears(ntime);
                    docs.Etime = docs.Rtime.AddYears(etime);
                    if (DateTime.TryParseExact(sl.GetCellValueAsString(i + 2, 8), "yyyy-MM-dd", null, System.Globalization.DateTimeStyles.None, out DateTime _date))
                    {
                        docs.Stime = _date;
                    }
                    if (DateTime.TryParseExact(sl.GetCellValueAsString(i + 2, 9), "yyyy-MM-dd", null, System.Globalization.DateTimeStyles.None, out _date))
                    {
                        docs.Rtime = _date;
                    }
                    if (sl.GetCellStyle(i + 2, 2).Font.FontColor == System.Drawing.Color.FromArgb(255, 255, 0, 0))
                    {
                        docs.Eng = true;
                    }
                    if (docs.webID.Contains("documentId"))
                    {
                        var m = Regex.Match(docs.webID, @"documentId=(\d+)");
                        if (m.Success)
                        {
                            docs.webID = m.Groups[1].ToString();
                        }
                    }
                    else if (Int64.TryParse(sl.GetCellValueAsString(i + 2, 3).Trim(), out long id2))
                    {
                        docs.webID = id2.ToString();
                    }
                    else
                    {
                        docs.webID = "-1";
                    }

                    /*if (Int64.TryParse(sl.GetCellValueAsString(i + 2, 1).Trim(), out long id))
                     * {
                     *  docs.Index = id.ToString();
                     * }
                     * else
                     *  docs.Index = "-1";*/
                    if (Double.TryParse(sl.GetCellValueAsString(i + 2, 5).Trim(), out double id3))
                    {
                        docs.Version = id3.ToString();
                    }
                    else
                    {
                        docs.Version = "-1";
                    }
                    ndocs.Add(docs);
                }
                sl.CloseWithoutSaving();
                return(ndocs);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return(new List <DocType>());
            }

            /*
             * odocs = ADocs.GroupBy(o => o.Depart).ToDictionary(o => o.Key, o => o.ToList());
             * try
             * {
             *  foreach (var x in odocs)
             *  {
             *      int i = 0;
             *      SLDocument sl = new SLDocument();
             *      foreach (var y in x.Value)
             *      {
             *          sl.SetCellValue(i + 1, 1, y.ID);
             *          sl.SetCellValue(i + 1, 2, y.webID);
             *          sl.SetCellValue(i + 1, 3, y.Name);
             *          sl.SetCellValue(i + 1, 4, y.Version);
             *          sl.SetCellValue(i + 1, 5, y.Depart);
             *          sl.SetCellValue(i + 1, 6, y.Own);
             *          i++;
             *      }
             *      string fpath = Environment.CurrentDirectory + @"\部門";
             *      if (!System.IO.Directory.Exists(fpath))
             *      {
             *          System.IO.Directory.CreateDirectory(fpath);
             *      }
             *      sl.SaveAs(fpath + @"\" + x.Key + ".xlsx");
             *  }
             * }
             * catch (Exception ex)
             * {
             *  MessageBox.Show(ex.Message);
             * }
             */
        }
Пример #18
0
        //TODO refactor with Validate Clickstreams for the report extraction bit
        public void validatePresentations()
        {
            Dictionary <string, string>       presreport  = new Dictionary <string, string>(); //Reported Presenation IDs
            Dictionary <string, bool>         pubformpres = new Dictionary <string, bool>();   //Publishing Form Presenation IDs - bool indicates if validated or not
            SortedDictionary <string, string> missingpubs = new SortedDictionary <string, string>();

            //Grab Presentation IDs from each publishing form using presentation report
            //List publising forms
            listFileNames(pubfolder);           //Populate list of Publishing Form spreadsheets
            foreach (var f in pubforms)
            {
                SLDocument pubform  = openPubForm(pubfolder + "\\" + f.Value);
                string     curSheet = pubform.GetCurrentWorksheetName();
                pubform.SelectWorksheet(PresTab);
                string PresID = pubform.GetCellValueAsString(PresIDCell);
                try { pubformpres.Add(PresID, false); }         //Default to not validated
                catch (Exception e) { if (e.HResult == -2147024809)
                                      {
                                      }
                                      else
                                      {
                                          throw e;
                                      } }                                                                    //Allow for duplicates
            }

            //Then verify against the Presentation IDs in Veeva  (and those coded into "GotoSlide" in the pages?  No, rely on manual checks for that for now)
            //Use publishing report
            //Open Excel Clickstream Report File and extract list of clickstreamids
            SLDocument            repform = new SLDocument(presrepfile);
            SLWorksheetStatistics stats1  = repform.GetWorksheetStatistics();

            for (int j = repstartrow; j <= stats1.EndRowIndex; j++)
            {
                string Pres = repform.GetCellValueAsString(prescolumn + j);               //Presentation ID
                try { presreport.Add(Pres, Pres); }
                catch (Exception e) { if (e.HResult == -2147024809)
                                      {
                                      }
                                      else
                                      {
                                          throw e;
                                      } }                                                                    //Allow for duplicates
            }

            //Now Compare
            HashSet <string> lines = new HashSet <string>();

            lines.Add("Presentation IDs Missing from Publishing Forms :");
            string[] headermessage = new string[] { "Presentations found in Publising Form but not found in Veeva" };
            //Validate names in publishing form match folders
            //Don't use a clever (big O) comparison as may not be a perfect match
            foreach (var v in presreport)
            {
                if (pubformpres.ContainsKey(v.Key))
                {
                    pubformpres[v.Key] = true;      //Flag as validated
                }
                else
                {
                    //Presentation from spreadsheets has no corresponding entry in Veeva
                    missingpubs.Add(v.Key, v.Value);
                }
            }

            //Identify any pubforms that are still invalid (i.e. no presenation in veeva matches)

            if (pubformpres.ContainsValue(false))
            {
                var matches = pubformpres.Where(pair => pair.Value != true).Select(pair => pair.Key);
                Console.Write(matches.ToString());
                System.IO.File.WriteAllLines(folderPath + "\\PresentationValidation" + logfile, headermessage);
                System.IO.File.AppendAllLines(folderPath + "\\PresentationValidation" + logfile, matches.ToArray <string>());
            }

            //Also need to identify if any veeva presentations lack a corresponding pub form
            foreach (var v in missingpubs)
            {
                Console.WriteLine(v.Key);
                lines.Add(v.Key);
            }
            System.IO.File.AppendAllLines(folderPath + "\\PresentationValidation" + logfile, lines.ToArray <string>());

            //For completeness, probably worth writing out the "true", i.e. validated, publishing form names, so can see that all the lists tie up together.
        }
Пример #19
0
        public SLDocument SanitizeInvalidDialogCharacters(SLDocument sldocWorksheet)
        {
            SLWorksheetStatistics sldocWorksheetStats = sldocWorksheet.GetWorksheetStatistics();
            int iNumberOfCells      = sldocWorksheetStats.EndRowIndex;
            int iEmptyStringCounter = 0000;

            for (int iCounter = 2; iCounter <= iNumberOfCells; iCounter++)
            {
                //Begin working on Column A - RESPONSE_TEXT
                string strCurrentCell = sldocWorksheet.GetCellValueAsString("A" + iCounter.ToString());
                string strModifiedCurrentCell;


                if (strCurrentCell.Length > iMaxCharacters)
                {
                    strModifiedCurrentCell = strCurrentCell.Remove(iMaxCharacters - 1);
                }

                else
                {
                    strModifiedCurrentCell = strCurrentCell;
                }


                if (strModifiedCurrentCell.Length > 0)
                {
                    for (int iCharArrayCounter = 0; iCharArrayCounter < chararrayInvalidCharacters.Length; iCharArrayCounter++)
                    {
                        strModifiedCurrentCell = strModifiedCurrentCell.Replace(chararrayInvalidCharacters[iCharArrayCounter], chararrayValidCharacters[iCharArrayCounter]);
                    }

                    strModifiedCurrentCell = "|" + strModifiedCurrentCell;
                    strModifiedCurrentCell = strModifiedCurrentCell + ".fuz";

                    strModifiedCurrentCell = RemoveRedundantPeriods(strModifiedCurrentCell);

                    sldocWorksheet.SetCellValue("A" + iCounter.ToString(), strModifiedCurrentCell);
                    //End
                }

                else
                {
                    iEmptyStringCounter++;

                    strModifiedCurrentCell = "|[Empty_" + iEmptyStringCounter.ToString("000000") + "].fuz";

                    sldocWorksheet.SetCellValue("A" + iCounter.ToString(), strModifiedCurrentCell);
                }


                if (strModifiedCurrentCell == "|.fuz")
                {
                    iEmptyStringCounter++;

                    strModifiedCurrentCell = "|[Empty_" + iEmptyStringCounter.ToString("000000") + "].fuz";

                    sldocWorksheet.SetCellValue("A" + iCounter.ToString(), strModifiedCurrentCell);
                }


                //Begin working on Column B - FILENAME
                //We just want to append the dialog file extension to the cell's string.
                strCurrentCell         = sldocWorksheet.GetCellValueAsString("B" + iCounter.ToString());
                strModifiedCurrentCell = strCurrentCell + ".fuz";

                sldocWorksheet.SetCellValue("B" + iCounter.ToString(), strModifiedCurrentCell);
                //End
            }


            return(sldocWorksheet);
        }
Пример #20
0
        //TODO refactor as similar code to above
        //Extract Clickstream details from publishing form
        private void ExtractClickstream(string filename, string thisFolder)
        {
            caseErrors         = new HashSet <string>();
            unabletoreadErrors = new HashSet <string>();
            //Extract clickstreams from publishing form
            SLDocument pubform        = openPubForm(thisFolder + "\\" + filename);
            var        worksheetnames = pubform.GetWorksheetNames();

            caseErrors.Add("Case Sensitive Errors :");
            pubform.SelectWorksheet(MetaPresTab);
            string PresID = pubform.GetCellValueAsString(MetaPresIDCell);

            unabletoreadErrors.Add(PresID);
            SLWorksheetStatistics       stats1 = pubform.GetWorksheetStatistics();
            Dictionary <string, string> keymessagenumberstonames = new Dictionary <string, string>();
            Dictionary <string, string> caseinsensitivekeymessagenumberstonames = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);

            //Get Key Message Number to Name Mapping - no need to store Presentation id as disposable lookup dictionary on a per spreadsheet basis
            for (int j = metakeymessagestartrow; j <= stats1.EndRowIndex; j++)
            {
                string kmnumber = pubform.GetCellValueAsString(Metadatakeynumbercolumn + j);
                kmnumber = String.Format("{0:0.00}", kmnumber);
                string kmname = pubform.GetCellValueAsString(Metadatakeynamecolumn + j);
                string kmzip  = kmnumber + "_" + pubform.GetCellValueAsString(MetadataKeyMessageCol + j) + ".zip";
                try { keymessagenumberstonames.Add(kmnumber + "_" + kmname, kmzip); } catch (Exception e) { if (e.HResult == -2147024809)
                                                                                                            {
                                                                                                            }
                                                                                                            else
                                                                                                            {
                                                                                                                throw e;
                                                                                                            } }                                                                     //Allow for duplicates
                try { caseinsensitivekeymessagenumberstonames.Add(kmnumber + "_" + kmname, kmzip); } catch (Exception e) { if (e.HResult == -2147024809)
                                                                                                                           {
                                                                                                                           }
                                                                                                                           else
                                                                                                                           {
                                                                                                                               throw e;
                                                                                                                           } }                                                                    //Allow for duplicates
            }
            pubform.SelectWorksheet(ClickTab);
            stats1 = pubform.GetWorksheetStatistics();
            for (int j = clickstreamstartrow; j < stats1.EndRowIndex; j++)
            {
                //Need to resolve the decimal issue (e.g. Pub Form has stored just 61 formatted to 61.00 while Clickstream Form has 61.00 stored actually as 61.00
                string clickstream    = pubform.GetCellValueAsString(pubclickstreamcolumn + j);            //Clickstream name
                string clickstreamkey = pubform.GetCellValueAsString(pubclickstreamkeycolumn + j);         //Key Message Number

                /*                clickstreamkey = String.Format("{0:0.00}", clickstreamkey);
                 *              //Quick bodge to make match new metadata sheets
                 *              //string clickstreamname = clickstream + ".zip";
                 *
                 *              //Deal with the ones where James has put the name in as well as the number. Clunky Christmas Eve coding.
                 *
                 *              int clickstreamkeyint;
                 *              if (int.TryParse(clickstreamkey, out clickstreamkeyint))
                 *              {
                 *                  //Already an integer
                 *                  clickstreamkey = clickstreamkeyint.ToString();
                 *              }
                 *              else
                 *              {
                 *                  int endindex = clickstreamkey.IndexOf(" ");
                 *                  if (endindex > 0) {
                 *                      clickstreamkey = clickstreamkey.Substring(0, endindex);
                 *                  }
                 *              }
                 *
                 *              string clickstreamname = "";
                 *              bool hasValue = keymessagenumberstonames.TryGetValue(clickstreamkey, out clickstreamname);                                                                                           //Lookup Key Message Number using dictionary generated earlier
                 *
                 *              if (!hasValue) { }  //TODO: Handle the lack of a lookup?
                 */
                string clickstreamname = "";
                if (clickstream == "")
                {
                    unabletoreadErrors.Add(clickstreamkey);
                }
                else
                {
                    bool hasValue = keymessagenumberstonames.TryGetValue(clickstreamkey, out clickstreamname);                                                                                           //Lookup Key Message Number using dictionary generated earlier
                    if (clickstream != "" && clickstreamname != "")
                    {
                        try { clickstreams.Add(PresID + "#" + clickstreamname + "@" + clickstream, clickstream); }                //Concatenated PresID, Key Name and Clicksteam so can still just use string dictionaries in case can refactor later to be same as key message
                        catch (Exception e) { if (e.HResult == -2147024809)
                                              {
                                              }
                                              else
                                              {
                                                  throw e;
                                              } }                                                                    //Allow for duplicates
                    }
                    else
                    {
                        hasValue = caseinsensitivekeymessagenumberstonames.TryGetValue(clickstreamkey, out clickstreamname);
                        caseErrors.Add(clickstreamkey);
                        if (clickstream != "" && clickstreamname != "")
                        {
                            try { clickstreams.Add(PresID + "#" + clickstreamname + "@" + clickstream, clickstream); }                //Concatenated PresID, Key Name and Clicksteam so can still just use string dictionaries in case can refactor later to be same as key message
                            catch (Exception e) { if (e.HResult == -2147024809)
                                                  {
                                                  }
                                                  else
                                                  {
                                                      throw e;
                                                  } }                                                                    //Allow for duplicates
                        }
                    }
                }
            }

            System.IO.File.AppendAllLines(folderPath + "\\unabletoreadErrors.txt", unabletoreadErrors.ToArray <string>());
        }
        public void UpdateCoordinatesSingle()
        {
            //string current = GetActiveWindowsTXT();
            //Form currForm = Form.ActiveForm;

            //Control.ControlCollection coll = currForm.Controls;
            //PPLMain pMain = currForm.ActiveControl as PPLMain;
            //foreach (Control ctl in coll)
            //{

            //}

            //pMain.Focus();
            //FormCollection frmColl = Application.OpenForms;
            foreach (Form f in Application.OpenForms)
            {
                //if (f.Name.Contains("PGE"))
                //{
                //    PPLMain main = f.ActiveControl as PPLMain;
                //}
            }
            //PPLMain main = WeifenLuo.WinFormsUI.ThemeVS2013.VS2013DockPane as PPLMain;



            bool enabled = false;



            ToolStripMenuItem proposed    = GetSaveAsProposedDesignButton();
            ToolStripMenuItem overrideFAA = GetFAAButton();

            enabled = overrideFAA.Enabled;


            if (enabled)
            {
                PPLPole pole     = cPPLMain.GetPole();
                string  sapPM    = pole.GetValueString("Aux Data 1");
                string  poleLoc  = pole.GetValueString("Aux Data 3");
                string  slLoc    = poleLoc.Substring(poleLoc.LastIndexOf('_') + 1);
                string  poleLat  = string.Empty;
                string  poleLong = string.Empty;

                string pdFilePath      = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + sapPM + " Planning Data For PGE Computer.xlsx";
                string treeAttachSheet = "Existing Pole Data";


                SLDocument            planningData = new SLDocument(pdFilePath, treeAttachSheet);
                SLWorksheetStatistics wsStats      = planningData.GetWorksheetStatistics();
                int  numRows = wsStats.NumberOfRows;
                int  numCols = wsStats.NumberOfColumns;
                bool found   = false;                for (int i = 2; i <= numRows; i++)
                {
                    if (planningData.GetCellValueAsString(i, 2).Equals(slLoc))
                    {
                        poleLat  = planningData.GetCellValueAsString(i, 17);
                        poleLong = planningData.GetCellValueAsString(i, 18);
                        found    = true;
                        PPLMessageBox.Show("Pole found");
                    }
                }

                if (!found)
                {
                    PPLMessageBox.Show("Pole not found");
                }
                planningData.CloseWithoutSaving();
            }
            else
            {
                PPLMessageBox.Show("Override Coordinates for FAA not enabled for this pole.");
            }
        }
Пример #22
0
        //TODO compare common code to ExtractClickstream and refactor

        private void ExtractKeyMessage(string filename, string thisFolder, bool current = true)
        {
            //Extract key messages from publishing form

            string PresID = "";
            SortedDictionary <int, string> thisPresKeyMessages = new SortedDictionary <int, string>();
            SLDocument pubform  = new SLDocument(thisFolder + "\\" + filename);
            string     curSheet = pubform.GetCurrentWorksheetName();

            if (curSheet != "")
            {
                pubform.SelectWorksheet(PresTab);
            }
            SLWorksheetStatistics stats1 = pubform.GetWorksheetStatistics();

            //Get Presentation ID
            PresID = pubform.GetCellValueAsString(PresIDCell);
            //Vulnerability on EndRowIndex (AbbVie Care and Safety Profile for RA)//Think I fixed it - need to ensure worksheet is selected before making the Statistics call
            for (int j = keymessagestartrow; j <= stats1.EndRowIndex; j++)
            {
                string kmzip        = pubform.GetCellValueAsString(KeyMessageCol + j);
                int    DisplayOrder = pubform.GetCellValueAsInt32(pubdisplaynumbercolumn + j);
                if (kmzip.Contains(".zip"))
                {
                    try
                    {
                        if (current)
                        {
                            thisPresKeyMessages.Add(DisplayOrder, kmzip.Replace(".zip", "")); //Doing it this way round means that where a slide is duplicated we still get it in each presentation for the tree view
                            keymessages.Add(kmzip.Replace(".zip", ""), PresID);               //Temp To Lower
                        }
                        else
                        {
                            oldkeymessages.Add(kmzip.Replace(".zip", ""), PresID);
                        }                                                                       //Temp To Lower
                    }
                    catch (Exception e)
                    { if (e.HResult == -2147024809)
                      {
                          //Ignore as duplicate keys can occur if same key message in more than one presentation
                      }
                      else
                      {
                          throw e;
                      } }
                }
                else
                {//May be shared message row
                    try {
                        var skm = pubform.GetCellValueAsString(pubsharedcolumn + j);
                        if (skm.Contains(".zip"))
                        {
                            sharedkeymessages.Add(PresID, skm);         //We can do this by Presentation rather than by key message
                        }
                        else
                        {//Trying to cater for other eventualities
                            if (pubform.GetCellValueAsString(pubsharedcolumn + (j - 1)).Contains("Shared Resources\nFile Name"))
                            {
                                sharedkeymessages.Add(PresID, skm);
                            }
                        }
                    }catch (Exception e) { if (e.HResult == -2147024809)
                                           {
                                           }
                                           else
                                           {
                                               throw e;
                                           } }
                }
            }
            //Resort thisPresKeymessages into display order
            thisPresKeyMessages.OrderBy(key => key.Key);
            try
            {
                orderedKeymessages.Add(PresID, thisPresKeyMessages);
            }
            catch (Exception e)
            {
                if (e.HResult == -2147024809)
                {
                }
                else
                {
                    throw e;
                }
            }
        }
        ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        public void TreeBuilder()
        {
            /////////////Catalog Guids and other constants//////////////////
            const string spoolGuid     = "21670682-ab38-484c-bed8-a212650ee25a";
            const string dummyLoadGuid = "85a21fd2-86ad-4744-b1eb-fc6fb232596b";
            const double pi            = Math.PI;

            /////////////End Catalog Guids and other constants//////////////

            PPLPole         pole         = cPPLMain.GetPole();
            List <TreeNode> treeNodeList = cPPLMain.cCatalogManager.GetNodes(PPLCatalogForm.CATALOG_TYPE.MASTER);
            //dummy load case needs to be added to the pole in order to populate the Aux Data fields
            PPLEnvironment dummyLoadCase = FindElement(dummyLoadGuid, treeNodeList) as PPLEnvironment;

            pole.AddChild(dummyLoadCase);
            string sapPM   = pole.GetValueString("Aux Data 1");
            string poleLoc = pole.GetValueString("Aux Data 3");

            //Get pole info from planning spreadsheet
            string pdFilePath      = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + sapPM + " Planning Data.xlsx";
            string treeAttachSheet = "Ocalc Tree Attachments";

            SLDocument            planningData = new SLDocument(pdFilePath, treeAttachSheet);
            SLWorksheetStatistics wsStats      = planningData.GetWorksheetStatistics();
            int    numRows        = wsStats.NumberOfRows;
            int    numCols        = wsStats.NumberOfColumns;
            string instructionStr = string.Empty;

            for (int i = 2; i <= numRows; i++)
            {
                if (planningData.GetCellValueAsString(i, 1).Contains(poleLoc))
                //if (planningData.GetCellValueAsString(i, 1).Equals(poleLoc))
                {
                    instructionStr = planningData.GetCellValueAsString(i, 2);
                }
            }
            planningData.CloseWithoutSaving();

            //assign variables from instruction array
            string[] instructionArr = instructionStr.Split(',');
            string   hftdGuid       = instructionArr[0];

            string go95Guid = instructionArr[1];
            string wireGuid = instructionArr[2];

            double[] distanceArr = instructionArr[3].Split(':').Select(Double.Parse).ToArray();

            //check for spans over 125' and exit program if found
            for (int i = 0; i <= distanceArr.Length - 1; i++)
            {
                if (distanceArr[i] > 125)
                {
                    PPLMessageBox.Show("One or more spans to this pole exceeds 125'. Interset is required", "Cannot Build Pole!");
                    goto end;
                }
            }


            double[] headingArr = instructionArr[4].Split(':').Select(Double.Parse).ToArray();


            ////if length of distanceArr != 2 ... need to figure out logic for this though the case would be very rare
            //if (distanceArr.Length == 2)
            //{
            //    double buryDepth = 7 * 12;
            //    pole.SetValue("BuryDepthInInches", buryDepth);
            //    PPLEnvironment hftdLoadCase = FindElement(hftdGuid, treeNodeList) as PPLEnvironment;
            //    PPLEnvironment go95LoadCase = FindElement(go95Guid, treeNodeList) as PPLEnvironment;

            //    //in the PPL_Lib library the Substitute method takes two arguments, PPLElement and PPLMain
            //    //but this gives an error in the script.  When using this script for testing you have to comment out
            //    //the pPPLMain.  But when using it on the PGE computer you must uncomment it or it will throw an error
            //    dummyLoadCase.Substitute(hftdLoadCase, pPPLMain);
            //    //pole.AddChild(hftdLoadCase);
            //    pole.AddChild(go95LoadCase);

            //    PPLInsulator spool = FindElement(spoolGuid, treeNodeList) as PPLInsulator;
            //    double deltaAngle = Math.Abs(headingArr[1] - headingArr[0]);
            //    if (deltaAngle > 180)
            //    {
            //        spool.CoordinateA = ((Math.Max(headingArr[0], headingArr[1]) - Math.Min(headingArr[0], headingArr[1])) / 2 + Math.Min(headingArr[0], headingArr[1]) + 180) * pi / 180;
            //    }

            //    else
            //    {
            //        spool.CoordinateA = ((Math.Max(headingArr[0], headingArr[1]) - Math.Min(headingArr[0], headingArr[1])) / 2 + Math.Min(headingArr[0], headingArr[1])) * (pi / 180);
            //    }
            //    spool.CoordinateZ = pole.LengthInInches - 9;
            //    spool.SetValue("Side", "Inline");
            //    pole.AddChild(spool);
            //    spool.SnapToParent();

            //    PPLSpan[] spans = new PPLSpan[distanceArr.Length];
            //    for (int i = 0; i <= distanceArr.Length - 1; i++)
            //    {
            //        spans[i] = FindElement(wireGuid, treeNodeList) as PPLSpan;
            //        spans[i].CoordinateA = (headingArr[i] * pi / 180) - spool.CoordinateA;
            //        spans[i].SetValue("SpanDistanceInInches", distanceArr[i] * 12);
            //        spool.AddChild(spans[i]);
            //        spans[i].SnapToInsulator();
            //    }
            //}
            //else
            //{
            //    PPLMessageBox.Show("Cannot build pole!");
            //}

            double buryDepth = 6.5 * 12;

            pole.SetValue("BuryDepthInInches", buryDepth);
            PPLEnvironment hftdLoadCase = FindElement(hftdGuid, treeNodeList) as PPLEnvironment;
            PPLEnvironment go95LoadCase = FindElement(go95Guid, treeNodeList) as PPLEnvironment;

            //in the PPL_Lib library the Substitute method takes two arguments, PPLElement and PPLMain
            //but this gives an error in the script.  When using this script for testing you have to comment out
            //the pPPLMain.  But when using it on the PGE computer you must uncomment it or it will throw an error
            dummyLoadCase.Substitute(hftdLoadCase /*, cPPLMain*/);
            //pole.AddChild(hftdLoadCase);
            pole.AddChild(go95LoadCase);


            double minHeading;
            double maxHeading;

            if (distanceArr.Length < 4)
            {
                PPLInsulator spool = FindElement(spoolGuid, treeNodeList) as PPLInsulator;

                if (distanceArr.Length == 2)
                {
                    minHeading = Math.Min(headingArr[0], headingArr[1]);
                    maxHeading = Math.Max(headingArr[0], headingArr[1]);
                }

                else if (distanceArr.Length == 3)
                {
                    minHeading = headingArr[0];
                    maxHeading = headingArr[0];
                    for (int i = 0; i < headingArr.Length - 1; i++)
                    {
                        if (minHeading > headingArr[i])
                        {
                            minHeading = headingArr[i];
                        }
                        else if (maxHeading < headingArr[i])
                        {
                            maxHeading = headingArr[i];
                        }
                    }
                }
                else
                {
                    PPLMessageBox.Show("Cannot build pole!");
                    goto end;
                }

                double deltaAngle = maxHeading - minHeading;
                if (deltaAngle > 180)
                {
                    spool.CoordinateA = ((deltaAngle / 2) + minHeading + 180) * pi / 180;
                }
                else
                {
                    spool.CoordinateA = ((deltaAngle / 2) + minHeading) * (pi / 180);
                }

                spool.CoordinateZ = pole.LengthInInches - 9;
                spool.SetValue("Side", "Inline");
                pole.AddChild(spool);
                spool.SnapToParent();

                PPLSpan[] spans = new PPLSpan[distanceArr.Length];
                for (int i = 0; i <= distanceArr.Length - 1; i++)
                {
                    spans[i]             = FindElement(wireGuid, treeNodeList) as PPLSpan;
                    spans[i].CoordinateA = (headingArr[i] * pi / 180) - spool.CoordinateA;
                    spans[i].SetValue("SpanDistanceInInches", distanceArr[i] * 12);
                    spool.AddChild(spans[i]);
                    spans[i].SnapToInsulator();
                }
            }

            else if (distanceArr.Length == 4)
            {
                PPLInsulator[] spools     = new PPLInsulator[2];
                PPLSpan[]      multiSpans = new PPLSpan[headingArr.Length];

                double[] minHead = new double[2];
                minHead[0] = Math.Min(headingArr[0], headingArr[2]);
                minHead[1] = Math.Min(headingArr[1], headingArr[3]);

                double[] maxHead = new double[2];
                maxHead[0] = Math.Max(headingArr[0], headingArr[2]);
                maxHead[1] = Math.Max(headingArr[1], headingArr[3]);

                double[] deltaAng = new double[2];
                //deltaAng[0] = Math.Abs(headingArr[0] - headingArr[2]);
                //deltaAng[1] = Math.Abs(headingArr[1] - headingArr[3]);

                int spansIndex = 0;
                int lessThan   = 2;

                for (int i = 0; i <= spools.Length - 1; i++)
                {
                    deltaAng[i] = maxHead[i] - minHead[i];
                    spools[i]   = FindElement(spoolGuid, treeNodeList) as PPLInsulator;
                    if (deltaAng[i] > 180)
                    {
                        spools[i].CoordinateA = ((deltaAng[i] / 2) + minHead[i] + 180) * pi / 180;
                    }
                    else
                    {
                        spools[i].CoordinateA = ((deltaAng[i] / 2) + minHead[i]) * (pi / 180);
                    }


                    spools[i].CoordinateZ = pole.LengthInInches - 9;
                    spools[i].SetValue("Side", "Inline");
                    pole.AddChild(spools[i]);
                    spools[i].SnapToParent();
                    for (int j = spansIndex; j <= lessThan; j += 2)
                    {
                        multiSpans[j]             = FindElement(wireGuid, treeNodeList) as PPLSpan;
                        multiSpans[j].CoordinateA = (headingArr[j] * pi / 180) - spools[i].CoordinateA;
                        multiSpans[j].SetValue("SpanDistanceInInches", distanceArr[j] * 12);
                        spools[i].AddChild(multiSpans[j]);
                        multiSpans[j].SnapToInsulator();
                    }
                    spansIndex++;
                    lessThan++;
                }
            }
            OcalcReload(cPPLMain);
end:
            { }
        }
Пример #24
0
        /// <summary>
        /// Import DataTable,
        /// BirthDate is formatted
        /// Active field is set manually
        /// </summary>
        /// <param name="pFileName">Existing excel file</param>
        /// <param name="pStartReference">Row to start import</param>
        /// <param name="pDataTable">Data source</param>
        /// <param name="pIncludeHeaders">Should column headers be used (currently only does headers)</param>
        /// <returns></returns>
        /// <remarks>
        /// Unlike example above we will create Demo2 worksheet, import our DataTable
        /// and do some formatting. Each time we run this code I check to see if Demo2
        /// sheet exist, if so, remove and add it back in.
        /// </remarks>
        public bool ImportDataTable2(string pFileName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true)
        {
            bool success = false;

            try
            {
                using (SLDocument doc = new SLDocument(pFileName, "Sheet1"))
                {
                    var helper = new LightHelpers();

                    if (helper.SheetExists(pFileName, "Demo2"))
                    {
                        doc.DeleteWorksheet("Demo2");
                    }

                    doc.AddWorksheet("Demo2");
                    doc.SelectWorksheet("Demo2");

                    // create a date style for birthday column
                    var birthdayColumnStyle = doc.CreateStyle();
                    birthdayColumnStyle.FormatCode = "mm-dd-yyyy";

                    // since we need these several times, set them up as variables
                    var activeCellIndex   = pDataTable.Columns["Active"].Ordinal + 1;
                    var birthDayCellIndex = pDataTable.Columns["BirthDay"].Ordinal + 1;

                    // set birthday style to our data
                    doc.SetCellStyle(2, birthDayCellIndex, pDataTable.Rows.Count + 1, birthDayCellIndex, birthdayColumnStyle);

                    // do the Datatable import.
                    doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);

                    SLWorksheetStatistics stats = doc.GetWorksheetStatistics();

                    // there is no method to set all bool values from TRUE/FALSE to Yes/No so we do it manually
                    // we add +1 to EndRowIndex because of the header
                    for (int row = 1; row < stats.EndRowIndex + 1; row++)
                    {
                        var value = doc.GetCellValueAsString(row, activeCellIndex);
                        if (value == "TRUE")
                        {
                            doc.SetCellValue(row, activeCellIndex, "Yes");
                        }
                        else if (value == "FALSE")
                        {
                            doc.SetCellValue(row, activeCellIndex, "No");
                        }
                    }

                    // set the column header stype
                    var headerSyle = doc.CreateStyle();
                    headerSyle.Font.FontColor = SysDraw.Color.White;
                    headerSyle.Font.Strike    = false;
                    headerSyle.Fill.SetPattern(PatternValues.Solid, SysDraw.Color.Green, SysDraw.Color.White);
                    headerSyle.Font.Underline = UnderlineValues.None;
                    headerSyle.Font.Bold      = true;
                    headerSyle.Font.Italic    = false;
                    doc.SetCellStyle(1, 1, 1, pDataTable.Columns.Count, headerSyle);

                    // set one column to auto fit
                    //doc.AutoFitColumn(birthDayCellIndex, stats.EndRowIndex + 1);

                    // set all columns to auto fix beginning at data rather than column header
                    doc.AutoFitColumn(2, pDataTable.Columns.Count);

                    // save to the document we opened rather than a new document.
                    doc.Save();

                    success = true;
                }
            }
            catch (Exception ex)
            {
                HasErrors        = true;
                ExceptionMessage = ex.Message;
                success          = false;
            }


            return(success);
        }
Пример #25
0
        /// <summary>
        /// Focus here is on removal of grid-lines while using code from
        /// ImportDataTable2 method above. Also removed header formatting.
        /// </summary>
        /// <param name="pFileName">Existing excel file</param>
        /// <param name="pStartReference">Row to start import</param>
        /// <param name="pDataTable">Data source</param>
        /// <param name="pIncludeHeaders">Should column headers be used (currently only does headers)</param>
        /// <returns></returns>
        public bool ImportDataTable3(string pFileName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true)
        {
            bool success = false;

            var sheetName = "Demo3";

            try
            {
                // open to Sheet1 (will always exists in this code sample)
                using (SLDocument doc = new SLDocument(pFileName, "Sheet1"))
                {
                    var helper = new LightHelpers();

                    if (helper.SheetExists(pFileName, sheetName))
                    {
                        doc.DeleteWorksheet(sheetName);
                    }

                    // add the sheet
                    doc.AddWorksheet(sheetName);

                    var isSet = doc.SelectWorksheet(sheetName);

                    // next three lines are responsible for removal of grid-lines in the current WorkSheet
                    SLPageSettings ps = new SLPageSettings();
                    ps.ShowGridLines = false;
                    doc.SetPageSettings(ps);

                    // setup a format for the date column
                    var birthdayColumnStyle = doc.CreateStyle();
                    birthdayColumnStyle.FormatCode = "mm-dd-yyyy";

                    // since we need these several times, set them up as variables
                    var activeCellIndex   = pDataTable.Columns["Active"].Ordinal + 1;
                    var birthDayCellIndex = pDataTable.Columns["BirthDay"].Ordinal + 1;

                    // set birthday style to our data
                    doc.SetCellStyle(2, birthDayCellIndex, pDataTable.Rows.Count + 1, birthDayCellIndex, birthdayColumnStyle);

                    // do the Datatable import.
                    doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);

                    SLWorksheetStatistics stats = doc.GetWorksheetStatistics();

                    // change format of the bool column
                    for (int row = 1; row < stats.EndRowIndex + 1; row++)
                    {
                        var value = doc.GetCellValueAsString(row, activeCellIndex);
                        if (value == "TRUE")
                        {
                            doc.SetCellValue(row, activeCellIndex, "Yes");
                        }
                        else if (value == "FALSE")
                        {
                            doc.SetCellValue(row, activeCellIndex, "No");
                        }
                    }

                    // does what it says, auto-fit cell content
                    doc.AutoFitColumn(2, pDataTable.Columns.Count);

                    doc.Save();

                    success = true;
                }
            }
            catch (Exception ex)
            {
                HasErrors        = true;
                ExceptionMessage = ex.Message;
                success          = false;
            }


            return(success);
        }
        public static DataSet ReadData(DataSet dataSet)
        {
            Dictionary <int, int>    votanti = new Dictionary <int, int>();
            Dictionary <string, int> denumireFacultateCuId = new Dictionary <string, int>();
            Dictionary <string, int> denumirePSCuId        = new Dictionary <string, int>();
            DataRow row;
            bool    auFormatCorectProgrameleDeStudii = true;

            using (SLDocument sl = new SLDocument(GlobalValues.PATH_SOURCE_FILE))
            {
                SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
                int    iStartColumnIndex = stats.StartColumnIndex, psStartColumnIndex = 0;
                String denumireFacultate = String.Empty, denumirePS = String.Empty;
                int    indexColumnEmail = 0, indexColumnNume = 0, indexColumnPrenume = 0;
                int    indexColumnGradDidactic = 0, indexColumnFacultatea = 0;

                for (int columnIndex = stats.StartColumnIndex; columnIndex <= 6; columnIndex++)
                {
                    string columnHeader = sl.GetCellValueAsString(stats.StartRowIndex, columnIndex);

                    if (String.IsNullOrEmpty(columnHeader))
                    {
                        break;
                    }

                    if (columnHeader.ToUpper().Equals(GlobalValues.ColumnHeader_NUME))
                    {
                        indexColumnNume = columnIndex;
                        continue;
                    }

                    if (columnHeader.ToUpper().Equals(GlobalValues.ColumnHeader_PRENUME))
                    {
                        indexColumnPrenume = columnIndex;
                        continue;
                    }

                    if (columnHeader.ToUpper().Equals(GlobalValues.ColumnHeader_EMAIL))
                    {
                        indexColumnEmail = columnIndex;
                        continue;
                    }

                    if (columnHeader.ToUpper().Equals(GlobalValues.ColumnHeader_GRAD_DIDACTIC))
                    {
                        indexColumnGradDidactic = columnIndex;
                        continue;
                    }

                    if (columnHeader.ToUpper().Equals(GlobalValues.ColumnHeader_FACULTATEA))
                    {
                        indexColumnFacultatea = columnIndex;
                        psStartColumnIndex    = indexColumnFacultatea + 1;
                        continue;
                    }
                }

                for (int columnIndex = psStartColumnIndex; columnIndex <= stats.EndColumnIndex; columnIndex++)
                {
                    denumirePS        = sl.GetCellValueAsString(stats.StartRowIndex, columnIndex);
                    denumireFacultate = sl.GetCellValueAsString(stats.StartRowIndex + 1, columnIndex);

                    if (String.IsNullOrEmpty(denumirePS) && String.IsNullOrEmpty(denumireFacultate))
                    {
                        break;
                    }

                    if (!String.IsNullOrEmpty(denumirePS) && !denumirePS.ToUpper().StartsWith("L") && !denumirePS.ToUpper().StartsWith("M"))
                    {
                        auFormatCorectProgrameleDeStudii = false;
                        break;
                    }
                }


                for (int columnIndex = psStartColumnIndex; columnIndex <= stats.EndColumnIndex; columnIndex++)
                {
                    denumirePS        = sl.GetCellValueAsString(stats.StartRowIndex, columnIndex);
                    denumireFacultate = sl.GetCellValueAsString(stats.StartRowIndex + 1, columnIndex);

                    if (String.IsNullOrEmpty(denumirePS) && String.IsNullOrEmpty(denumireFacultate))
                    {
                        break;
                    }

                    if (String.IsNullOrEmpty(denumirePS))
                    {
                        continue;
                        // TO DO : ISSUE WARNING?
                    }

                    // adauga programul de studiu

                    row = dataSet.Tables["ProgramStudiu"].NewRow();
                    row["DenumireScurta"]  = denumirePS;
                    row["Facultate"]       = denumireFacultate;
                    row["NumarAbsolventi"] = 0;
                    row["NumarVotanti"]    = 0;

                    if ((auFormatCorectProgrameleDeStudii && denumirePS.ToUpper().StartsWith("M")) || sl.GetCellStyle(stats.StartRowIndex, columnIndex).Fill.PatternForegroundColor.R == 195) //PS : Master
                    {
                        row["ID_TipCiclu"] = GlobalValues.Map_TIP_CICLU_INVATAMANT_ID[GlobalValues.Denumire_TIP_CICLU_INVATAMANT_MASTER];
                    }
                    else   //PS : LICENTA
                    {
                        row["ID_TipCiclu"] = GlobalValues.Map_TIP_CICLU_INVATAMANT_ID[GlobalValues.Denumire_TIP_CICLU_INVATAMANT_LICENTA];
                    }

                    dataSet.Tables["ProgramStudiu"].Rows.Add(row);
                    denumirePSCuId.Add(denumirePS, (int)row["ID_ProgramStudiu"]);
                }

                string nume = String.Empty, prenume = String.Empty, email = String.Empty, gradDidactic = String.Empty, facultateServiciu = String.Empty;
                int    idProfesor;

                for (int rowIndex = stats.StartRowIndex + 4; rowIndex <= stats.EndRowIndex; ++rowIndex)
                {
                    nume    = sl.GetCellValueAsString(rowIndex, indexColumnNume);
                    prenume = sl.GetCellValueAsString(rowIndex, indexColumnPrenume);

                    if (String.IsNullOrEmpty(nume) && String.IsNullOrEmpty(prenume))
                    {
                        break;
                    }

                    row            = dataSet.Tables["Profesor"].NewRow();
                    row["Nume"]    = nume;
                    row["Prenume"] = prenume;

                    if (indexColumnEmail != 0)
                    {
                        email        = sl.GetCellValueAsString(rowIndex, indexColumnEmail);
                        row["Email"] = email;
                    }
                    else
                    {
                        row["Email"] = String.Empty;
                    }

                    gradDidactic      = sl.GetCellValueAsString(rowIndex, indexColumnGradDidactic);
                    facultateServiciu = sl.GetCellValueAsString(rowIndex, indexColumnFacultatea);

                    row["GradDidactic"]      = gradDidactic;
                    row["FacultateServiciu"] = facultateServiciu;

                    if (gradDidactic.ToUpper().Equals(GlobalValues.DenumireScurta_CADRU_EXTERN))
                    {
                        row["EligibilRemunerare"] = false;
                    }
                    else
                    {
                        row["EligibilRemunerare"] = true;
                    }

                    dataSet.Tables["Profesor"].Rows.Add(row);

                    //ADD REZULTATE VOT

                    for (int columnIndex = psStartColumnIndex; columnIndex <= stats.EndColumnIndex; columnIndex++)
                    {
                        denumirePS        = sl.GetCellValueAsString(stats.StartRowIndex, columnIndex);
                        denumireFacultate = sl.GetCellValueAsString(stats.StartRowIndex + 1, columnIndex);

                        if (String.IsNullOrEmpty(denumirePS) && String.IsNullOrEmpty(denumireFacultate))
                        {
                            break;
                        }

                        if (String.IsNullOrEmpty(denumirePS))
                        {
                            continue;
                        }

                        if (!sl.GetCellValueAsString(rowIndex, columnIndex).ToUpper().Equals("DA"))
                        {
                            continue;
                        }

                        idProfesor              = (int)row["ID_Profesor"];
                        row                     = dataSet.Tables["RezultatVotProfesorProgramStudiu"].NewRow();
                        row["ID_Profesor"]      = idProfesor;
                        row["ID_ProgramStudiu"] = denumirePSCuId[denumirePS];
                        row["NumarVoturi"]      = 0;
                        dataSet.Tables["RezultatVotProfesorProgramStudiu"].Rows.Add(row);
                    }
                }
            }

            return(dataSet);
        }
Пример #27
0
        public void ValidarConvenio(string cotizaCliente, string nombre, string usuario, string cotizacionClienteID)
        {
            log4net.Config.XmlConfigurator.ConfigureAndWatch(
                new FileInfo(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile));

            string CarpetaParaGuardar = "";

            CarpetaParaGuardar = Path.Combine(Server.MapPath("~/Archivos"), nombre); //Obtengo la posición del archivo
            HttpContext Contexto = HttpContext.Current;

            Logger.Info("Comenzando método ValidarConvenio. Parámetros: " + cotizaCliente + ", " + nombre + ", " + usuario + ", " + cotizacionClienteID);
            string str;
            string strSearch = "Redespacho";
            int    fCnt      = 0;
            int    cCnt      = 0;
            int    rw        = 0;
            int    cl        = 0;

            //string cpD = "";
            //string cpO = "";
            char[] splitParams = new char[] { '(', ')' };
            bool   pais        = false;
            //Session["ExcelTarifario"] = "";

            ExcelMasivoTarifario      emt   = new ExcelMasivoTarifario();     //emt = excel masivo tarifario
            IConveniosClientesDominio _conv = new ConveniosClientesDominio(); //_conv = convenio
            var        lemt = new List <ExcelMasivoTarifario>();              //lemt = listado excel masivo tarifario
            List <int> sess = new List <int>();

            List <OrigenDestinoSEL> listaOrgDst = new List <OrigenDestinoSEL>();
            List <TarifarioZonaSEL> listaTrZona = new List <TarifarioZonaSEL>();

            int ClienteID = Convert.ToInt32(_conv.GetClienteID(Convert.ToInt32(cotizacionClienteID)));

            #region try
            //Abro el archivo guardado y lo recorro para validar los datos
            try
            {
                //Support (http://spreadsheetlight.com)
                SLDocument            libro = new SLDocument(CarpetaParaGuardar);
                SLWorksheetStatistics stats = libro.GetWorksheetStatistics();

                Logger.Info("Iniciando try.");
                rw = stats.EndRowIndex;
                cl = stats.EndColumnIndex;

                Logger.Info("Filas: " + rw + ". Columnas: " + cl);
                #region Recorrido de datos
                if (cl == 8)
                {
                    for (fCnt = 2; fCnt <= rw; fCnt++)
                    {
                        emt = new ExcelMasivoTarifario();

                        for (cCnt = 1; cCnt <= cl; cCnt++)
                        {
                            str = libro.GetCellValueAsString(fCnt, cCnt);
                            str = str.Replace(",", " ");
                            str = str.Replace("'", " ");
                            str = str.Replace("º", " ");
                            str = str.Replace(Environment.NewLine, "");

                            switch (cCnt) //Aplico el control por cada fila y columna para validar los datos
                            {
                            case 1:       //Unidad de Venta
                                if (str != "")
                                {
                                    List <ConsUnidadVentaSEL> listaUV = new List <ConsUnidadVentaSEL>();
                                    listaUV = _conv.GetUnidadVenta(str);

                                    if (listaUV.Count >= 1)
                                    {
                                        foreach (ConsUnidadVentaSEL uv in listaUV)
                                        {
                                            emt.UnidadVentaID = uv.UnidadVentaID;
                                            emt.UnidadVenta   = uv.UnidadVentaDescrip;
                                        }
                                    }
                                    else
                                    {
                                        throw new ArgumentException("La Unidad de Venta no es la correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                    }
                                }
                                else
                                {
                                    //emt.UnidadVenta = str;
                                    throw new ArgumentException("La celda se encuentra vacia. Columna: " + cCnt + " Fila: " + fCnt);
                                }
                                break;

                            case 2:     //Modalidad de Entrega
                                if (str != "")
                                {
                                    List <ModalidadEntregaSEL> listaME = new List <ModalidadEntregaSEL>();
                                    listaME = _conv.GetModalidadEntrega(str);

                                    if (listaME.Count >= 1)
                                    {
                                        foreach (ModalidadEntregaSEL me in listaME)
                                        {
                                            emt.ModalidadEntregaID = me.ModalidadEntregaID;
                                            emt.ModalidadEntrega   = me.ModalidadEntregaDescrip;

                                            emt.CotizacionClienteUVentaModalidadID = _conv.GetCotizacionClienteUVModalidad(Convert.ToInt32(cotizaCliente), me.ModalidadEntregaID);
                                        }
                                    }
                                    else
                                    {
                                        throw new ArgumentException("La Modalidad de Entrega no es la correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                    }
                                }
                                else
                                {
                                    //emt.ModalidadEntrega = str;
                                    throw new ArgumentException("La celda se encuentra vacia. Columna: " + cCnt + " Fila: " + fCnt);
                                }
                                break;

                            case 3:     //Origen
                                if (str != "")
                                {
                                    string[] esAgrup_Loc = str.Split(splitParams, StringSplitOptions.RemoveEmptyEntries);

                                    if (esAgrup_Loc.Count() > 1)
                                    {
                                        if (esAgrup_Loc[1] == "Agrup.")
                                        {
                                            listaOrgDst.Clear();
                                            listaOrgDst = _conv.GetOrigenDestino(7, esAgrup_Loc[0], ClienteID, false);

                                            if (listaOrgDst.Count >= 1)
                                            {
                                                foreach (OrigenDestinoSEL od in listaOrgDst)
                                                {
                                                    emt.CodigoOrigen = od.ClienteOrigenDestinoID;
                                                    emt.TipoOrigenID = 7;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("La Agrupación Origen no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }

                                        else if (esAgrup_Loc[1] == "Loc")
                                        {
                                            listaOrgDst.Clear();
                                            listaOrgDst = _conv.GetOrigenDestino(3, esAgrup_Loc[0], ClienteID, false);

                                            if (listaOrgDst.Count >= 1)
                                            {
                                                foreach (OrigenDestinoSEL od in listaOrgDst)
                                                {
                                                    emt.CodigoOrigen = od.ClienteOrigenDestinoID;
                                                    emt.TipoOrigenID = 3;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("La Localidad Origen no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }

                                        else if (esAgrup_Loc[1] == "ZONA")
                                        {
                                            List <ZonaOrigenDestinoSEL> listaZona = new List <ZonaOrigenDestinoSEL>();
                                            listaZona = _conv.GetZonaUV(ClienteID, esAgrup_Loc[0].ToString());

                                            if (listaZona.Count() > 0)
                                            {
                                                foreach (ZonaOrigenDestinoSEL lz in listaZona)
                                                {
                                                    emt.ClienteZonaID = lz.ClienteZonaID;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("La Localidad Origen no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }

                                        else if (esAgrup_Loc[0].Contains(strSearch.ToUpper()))     //Busco por Redespacho
                                        {
                                            string unionZonaD = str.Remove(str.Length - 7).TrimEnd();

                                            listaOrgDst.Clear();
                                            listaOrgDst = _conv.GetOrigenDestino(6, unionZonaD, ClienteID, true);

                                            if (listaOrgDst.Count >= 1)
                                            {
                                                foreach (OrigenDestinoSEL od in listaOrgDst)
                                                {
                                                    emt.CodigoDestino = od.ClienteOrigenDestinoID;
                                                    emt.TipoDestinoID = 6;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("El Redespacho Destino no es correcto. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }

                                        else
                                        {
                                            listaOrgDst.Clear();
                                            listaOrgDst = _conv.GetOrigenDestino(4, esAgrup_Loc[0], ClienteID, false);

                                            if (listaOrgDst.Count >= 1)
                                            {
                                                foreach (OrigenDestinoSEL od in listaOrgDst)
                                                {
                                                    emt.CodigoOrigen = od.ClienteOrigenDestinoID;
                                                    emt.TipoOrigenID = 4;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("La Provincia Origen no es el correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }
                                    }
                                    else
                                    {
                                        listaOrgDst.Clear();
                                        if (esAgrup_Loc[0] == "Argentina")
                                        {
                                            pais        = true;
                                            listaOrgDst = _conv.GetOrigenDestino(5, esAgrup_Loc[0], ClienteID, false);
                                        }
                                        else
                                        {
                                            listaOrgDst = _conv.GetOrigenDestino(4, esAgrup_Loc[0], ClienteID, false);
                                        }

                                        if (listaOrgDst.Count >= 1)
                                        {
                                            foreach (OrigenDestinoSEL od in listaOrgDst)
                                            {
                                                emt.CodigoOrigen = od.ClienteOrigenDestinoID;
                                                if (pais)
                                                {
                                                    pais             = false;
                                                    emt.TipoOrigenID = 5;
                                                }
                                                else
                                                {
                                                    emt.TipoOrigenID = 4;
                                                }
                                            }
                                        }
                                        else
                                        {
                                            throw new ArgumentException("La Provincia Origen no es el correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                        }
                                    }
                                }
                                else
                                {
                                    throw new ArgumentException("La celda se encuentra vacia. Columna: " + cCnt + " Fila: " + fCnt);
                                }

                                break;

                            case 4:     //Destino
                                if (str != "")
                                {
                                    string[] esAgrup_Loc = str.Split(splitParams, StringSplitOptions.RemoveEmptyEntries);

                                    if (esAgrup_Loc.Count() > 1)
                                    {
                                        if (esAgrup_Loc[1] == "Agrup.")
                                        {
                                            listaOrgDst.Clear();
                                            listaOrgDst = _conv.GetOrigenDestino(7, esAgrup_Loc[0], ClienteID, true);

                                            if (listaOrgDst.Count >= 1)
                                            {
                                                foreach (OrigenDestinoSEL od in listaOrgDst)
                                                {
                                                    emt.CodigoDestino = od.ClienteOrigenDestinoID;
                                                    emt.TipoDestinoID = 7;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("La Agrupación Destino no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }

                                        else if (esAgrup_Loc[1] == "Loc")
                                        {
                                            listaOrgDst.Clear();
                                            listaOrgDst = _conv.GetOrigenDestino(3, esAgrup_Loc[0], ClienteID, true);

                                            if (listaOrgDst.Count >= 1)
                                            {
                                                foreach (OrigenDestinoSEL od in listaOrgDst)
                                                {
                                                    emt.CodigoDestino = od.ClienteOrigenDestinoID;
                                                    emt.TipoDestinoID = 3;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("La Localidad Destino no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }

                                        else if (esAgrup_Loc[1] == "ZONA")
                                        {
                                            List <ZonaOrigenDestinoSEL> listaZona = new List <ZonaOrigenDestinoSEL>();
                                            listaZona = _conv.GetZonaUV(ClienteID, esAgrup_Loc[0].ToString());

                                            if (listaZona.Count() > 0)
                                            {
                                                foreach (ZonaOrigenDestinoSEL lz in listaZona)
                                                {
                                                    emt.ClienteZonaID = lz.ClienteZonaID;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("La Localidad Destino no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }

                                        else if (esAgrup_Loc[0].Contains(strSearch.ToUpper()))     //Busco por Redespacho
                                        {
                                            string unionZonaD = str.Remove(str.Length - 7).TrimEnd();

                                            listaOrgDst.Clear();
                                            listaOrgDst = _conv.GetOrigenDestino(6, unionZonaD, ClienteID, true);

                                            if (listaOrgDst.Count >= 1)
                                            {
                                                foreach (OrigenDestinoSEL od in listaOrgDst)
                                                {
                                                    emt.CodigoDestino = od.ClienteOrigenDestinoID;
                                                    emt.TipoDestinoID = 6;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("El Redespacho Destino no es correcto. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }

                                        else
                                        {
                                            listaOrgDst.Clear();
                                            listaOrgDst = _conv.GetOrigenDestino(4, esAgrup_Loc[0], ClienteID, true);

                                            if (listaOrgDst.Count >= 1)
                                            {
                                                foreach (OrigenDestinoSEL od in listaOrgDst)
                                                {
                                                    emt.CodigoDestino = od.ClienteOrigenDestinoID;
                                                    emt.TipoDestinoID = 4;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("La Provincia Destino no es el correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }
                                    }
                                    else
                                    {
                                        listaOrgDst.Clear();
                                        if (esAgrup_Loc[0] == "Argentina")
                                        {
                                            pais        = true;
                                            listaOrgDst = _conv.GetOrigenDestino(5, esAgrup_Loc[0], ClienteID, true);
                                        }
                                        else
                                        {
                                            listaOrgDst = _conv.GetOrigenDestino(4, esAgrup_Loc[0], ClienteID, true);
                                        }

                                        if (listaOrgDst.Count >= 1)
                                        {
                                            foreach (OrigenDestinoSEL od in listaOrgDst)
                                            {
                                                emt.CodigoDestino = od.ClienteOrigenDestinoID;
                                                if (pais)
                                                {
                                                    pais             = false;
                                                    emt.TipoOrigenID = 5;
                                                }
                                                else
                                                {
                                                    emt.TipoDestinoID = 4;
                                                }
                                            }
                                        }
                                        else
                                        {
                                            throw new ArgumentException("La Provincia Destino no es el correcta. Columna: " + cCnt + " Fila: " + fCnt);
                                        }
                                    }
                                }
                                else
                                {
                                    throw new ArgumentException("La celda se encuentra vacia. Columna: " + cCnt + " Fila: " + fCnt);
                                }
                                break;

                            case 5:     //Tarifario
                                if (str != "")
                                {
                                    string[] esTarifario = str.Split(splitParams, StringSplitOptions.RemoveEmptyEntries);

                                    if (esTarifario.Count() > 1)
                                    {
                                        if (esTarifario[1] == "PART.Esp.")
                                        {
                                            List <TarifarioEspecificoSEL> listaTrEsp = new List <TarifarioEspecificoSEL>();
                                            listaTrEsp = _conv.GetTarifarioEspecifico(esTarifario[0]);

                                            if (listaTrEsp.Count >= 1)
                                            {
                                                foreach (TarifarioEspecificoSEL te in listaTrEsp)
                                                {
                                                    emt.TarifarioEspecificoID = te.TarifarioClienteFleteID;
                                                    emt.TarifarioEspecifico   = te.TarifarioClienteFleteDescrip;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("El Tarifario Específico no es el correcto. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }
                                        else if (esTarifario[1] == "PART. Zonas")
                                        {
                                            listaTrZona = _conv.GetTarifarioZona(esTarifario[0]);

                                            if (listaTrZona.Count >= 1)
                                            {
                                                foreach (TarifarioZonaSEL tz in listaTrZona)
                                                {
                                                    emt.TarifarioZonasID = tz.TarifarioFleteID;
                                                    emt.TarifarioZonas   = tz.TarifarioFleteDescrip;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("El Tarifario por Zonas no es el correcto. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }
                                        else if (esTarifario[1] == "Estand.")
                                        {
                                            listaTrZona = _conv.GetTarifarioZona(esTarifario[0]);

                                            if (listaTrZona.Count >= 1)
                                            {
                                                foreach (TarifarioZonaSEL tz in listaTrZona)
                                                {
                                                    emt.TarifarioEstandarizadoID = tz.TarifarioFleteID;
                                                    emt.TarifarioEstandarizado   = tz.TarifarioFleteDescrip;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("El Tarifario Estandarizado no es el correcto. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }
                                        else if (esTarifario[1] == "Redesp.")
                                        {
                                            List <TarifarioRedespachoSEL> listaRedesp = new List <TarifarioRedespachoSEL>();
                                            listaRedesp = _conv.GetTarifarioRedespacho(esTarifario[0]);

                                            if (listaRedesp.Count >= 1)
                                            {
                                                foreach (TarifarioRedespachoSEL tr in listaRedesp)
                                                {
                                                    emt.TarifarioRedespachoID = tr.TarifarioVentaReDespachoID;
                                                    emt.TarifarioRedespacho   = tr.TarifarioVentaReDespachoDescrip;
                                                }
                                            }
                                            else
                                            {
                                                throw new ArgumentException("El Tarifario Redespacho no es el correcto. Columna: " + cCnt + " Fila: " + fCnt);
                                            }
                                        }
                                    }
                                    else
                                    {
                                        throw new ArgumentException("El Tarifario utilizado no expecifica a ningún tarifario.");
                                    }
                                }
                                else
                                {
                                    //emt.TarifarioEspecifico = str;
                                    throw new ArgumentException("La celda se encuentra vacia. Columna: " + cCnt + " Fila: " + fCnt);
                                }
                                break;

                            case 6:
                                break;

                            case 7:
                                break;

                            case 8:
                                break;
                            }
                        }
                        #endregion

                        lemt.Add(emt);
                    }

                    foreach (var em in lemt) //Guardo cada convenio por separado
                    {
                        em.NombreExcel = nombre;
                        em.UsuarioID   = Convert.ToInt32(usuario);
                        _conv.AddConvenio(em);
                        sess.Add(em.ExcelMasivoTarifarioID);
                    }

                    Session["ExcelTarifario"] = sess;
                }
                else
                {
                    throw new ArgumentException("La Cantidad de Columnas no son correctas");
                }

                lemt.Clear();
                Logger.Info("Finalizado correctamente.");
            }
            catch (Exception ex)
            {
                Contexto.Response.ContentType = "application/json";
                Contexto.Response.Write("{\"error\":false,\"msg\":\"" + ex.Message.ToString() + "\"}");
                Logger.Error("Error: " + ex.Message.ToString());
            }
        }
Пример #28
0
        private void mcatBrowseButton_Click(object sender, EventArgs e)
        {
            if (cn.IsOpen)
            {
                //Open the dialog for the user to choose a spreadsheet
                OpenFileDialog openSheetDialog = new OpenFileDialog();
                openSheetDialog.FileName    = "";
                openSheetDialog.Filter      = "Excel 2007 Workbook (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                openSheetDialog.FilterIndex = 1;

                if (openSheetDialog.ShowDialog() == DialogResult.OK && Path.GetExtension(openSheetDialog.FileName) == ".xlsx")
                {
                    using (new CursorWait())
                    {
                        label1.Text = "Importing spreadsheet data. This may take a moment.";
                        label1.Refresh();
                        mcatBrowseButton.Visible = false;
                        mcatBrowseButton.Refresh();
                        pcatBrowseButton.Visible = false;

                        //open the file as a Spreadsheet Light document
                        SLDocument            ss    = new SLDocument(openSheetDialog.FileName);
                        SLWorksheetStatistics stats = ss.GetWorksheetStatistics();

                        //iterate through rows until the final row is reached
                        for (int r = 2; r <= stats.EndRowIndex; r++)
                        {
                            //Get the string values from the table
                            string   legal_state_cd        = ss.GetCellValueAsString(r, 1).Trim();
                            string   aamc                  = ss.GetCellValueAsString(r, 2).Trim();
                            DateTime date                  = ss.GetCellValueAsDateTime(r, 3);
                            string   lname                 = ss.GetCellValueAsString(r, 4).Trim();
                            string   fname                 = ss.GetCellValueAsString(r, 5).Trim();
                            string   mname                 = ss.GetCellValueAsString(r, 6).Trim();
                            string   suffix                = ss.GetCellValueAsString(r, 7).Trim();
                            string   address               = ss.GetCellValueAsString(r, 8).Trim();
                            string   city                  = ss.GetCellValueAsString(r, 9).Trim();
                            string   state                 = ss.GetCellValueAsString(r, 10).Trim();
                            string   postal_cd             = ss.GetCellValueAsString(r, 11).Trim();
                            string   country               = ss.GetCellValueAsString(r, 12).Trim();
                            string   email                 = ss.GetCellValueAsString(r, 13).Trim();
                            string   sex                   = ss.GetCellValueAsString(r, 14).Trim();
                            int      age                   = ss.GetCellValueAsInt32(r, 15);
                            string   major_cd              = ss.GetCellValueAsString(r, 16).Trim();
                            string   major                 = ss.GetCellValueAsString(r, 17).Trim();
                            string   primary_interest_cd   = ss.GetCellValueAsString(r, 18).Trim();
                            string   primary_interest      = ss.GetCellValueAsString(r, 19).Trim();
                            int      cpbs_score            = ss.GetCellValueAsInt32(r, 20);
                            int      cpbs_cb_lower         = ss.GetCellValueAsInt32(r, 21);
                            int      cpbs_cb_upper         = ss.GetCellValueAsInt32(r, 22);
                            int      cpbs_percentile_rank  = ss.GetCellValueAsInt32(r, 23);
                            int      cars_score            = ss.GetCellValueAsInt32(r, 24);
                            int      cars_cb_lower         = ss.GetCellValueAsInt32(r, 25);
                            int      cars_cb_upper         = ss.GetCellValueAsInt32(r, 26);
                            int      cars_percentile_rank  = ss.GetCellValueAsInt32(r, 27);
                            int      bbfl_score            = ss.GetCellValueAsInt32(r, 28);
                            int      bbfl_cb_lower         = ss.GetCellValueAsInt32(r, 29);
                            int      bbfl_cb_upper         = ss.GetCellValueAsInt32(r, 30);
                            int      bbfl_percentile_rank  = ss.GetCellValueAsInt32(r, 31);
                            int      psbb_score            = ss.GetCellValueAsInt32(r, 32);
                            int      psbb_cb_lower         = ss.GetCellValueAsInt32(r, 33);
                            int      psbb_cb_upper         = ss.GetCellValueAsInt32(r, 34);
                            int      psbb_percentile_rank  = ss.GetCellValueAsInt32(r, 35);
                            int      total_score           = ss.GetCellValueAsInt32(r, 36);
                            int      total_cb_lower        = ss.GetCellValueAsInt32(r, 37);
                            int      total_cb_upper        = ss.GetCellValueAsInt32(r, 38);
                            int      total_percentile_rank = ss.GetCellValueAsInt32(r, 39);


                            //add each row to the standard data table
                            mcatTable.Rows.Add("", legal_state_cd, aamc, date, lname, fname, mname, suffix, address, city, state, postal_cd, country, email, sex, age, major_cd, major, primary_interest_cd, primary_interest, cpbs_score, cpbs_cb_lower, cpbs_cb_upper, cpbs_percentile_rank, cars_score, cars_cb_lower, cars_cb_upper, cars_percentile_rank, bbfl_score, bbfl_cb_lower, bbfl_cb_upper, bbfl_percentile_rank, psbb_score, psbb_cb_lower, psbb_cb_upper, psbb_percentile_rank, total_score, total_cb_lower, total_cb_upper, total_percentile_rank);;
                        }
                        //close the SLDocument, as the data is now in a datatable
                        ss.CloseWithoutSaving();

                        if (cn.IsOpen)
                        {
                            //clear any old results from the table
                            cn.Execute("DELETE FROM LECOM_MATCHING_MCAT_TABLE", SQLTypes.Text);

                            SqlParameters dtParm = new SqlParameters();
                            dtParm.Add("exampleDT", mcatTable, SqlDbType.Structured);
                            dtParm.List[0].TypeName = "dbo.LecomMatchingMcatTableType";
                            cn.Execute("dbo.Lecom_MatchingImportMcat", SQLTypes.StoredProcedure, dtParm);
                            label1.Text = "Excel data import complete! Click below to view the match report.";
                            mcatReportViewButton.Visible = true;
                        }
                    }
                }
                else
                {
                    MessageBox.Show("Please select a valid Excel 2007 file!");
                    openSheetDialog.ShowDialog();
                }
            }
        }
Пример #29
0
        public void ColorCells(SLDocument document)
        {
            float value = 0;

            DateTime today_from = new DateTime();

            today_from = DateTime.Today;

            DateTime current_date = new DateTime(today_from.Year, today_from.Month, today_from.Day, new GregorianCalendar());

            float.TryParse(oreMacchina.Text, out value); // parso la stringa in un float, lo faccio per poter fare i calcoli

            var list = new List <string>();

            SLWorksheetStatistics stats = document.GetWorksheetStatistics();
            int endColumnIndex          = stats.EndColumnIndex;

            // adesso faccio i calcoli per prima fase
            float    result_1   = (value * storage_value_data_1) / 100; // risultato per le ore relative alla prima lavorazione
            TimeSpan timespan_1 = TimeSpan.FromHours(result_1);         // con il timespan converto i decimali in ore

            // method which don't respect the DRY guide line

            SLConditionalFormatting cf; // I need this for color
            int i, j;

            for (i = today_from.Day; i < 5; ++i)              // control and set the value of the cell
            {
                cf = new SLConditionalFormatting("B5", "H5"); // control the color of the cells

                for (j = 1; j < 7; ++j)                       // control and set the value of the cell
                {
                    if (result_1 > 16)
                    {
                        cf = new SLConditionalFormatting("B5", "H5");  // control the color of the cells
                    }
                    document.SetCellValue(i, j, result_1);

                    // set a custom color for
                    cf.SetCustom3ColorScale(SLConditionalFormatMinMaxValues.Value, "0", SLThemeColorIndexValues.Accent1Color, 0.2,
                                            SLConditionalFormatRangeValues.Percentile, "35", SLThemeColorIndexValues.Accent3Color, -0.1,
                                            SLConditionalFormatMinMaxValues.Value, "0", SLThemeColorIndexValues.Accent6Color, 0.5);
                    document.AddConditionalFormatting(cf);
                }
            }



            foreach (var sheetName in document.GetWorksheetNames())
            {
                // with this for I read data as string in the second row of the excel sheet
                for (int z = 1; z <= endColumnIndex; z++)
                {
                    list.Add(document.GetCellValueAsString(3, z));
                }



                // NON FUNZIONA, NON VIENE INSERITA LA DATA ODIERNA NELLA CELLA D4

                var print_current_day_test_1_cell = document.SetCellValue("D4", current_date.Day + "/" + current_date.Month + "/" + current_date.Year);
                // se metto diverso funziona ma dovrebbe funzionare con l'uguale uguale
                if (document.GetCellValueAsDateTime("2") != document.GetCellValueAsDateTime("D4")) // !!!!!!!!!!!!!!!!!!!!!! scorta di punti esclamativi
                {
                    // works with the dateTime if was stored in a cell but if I try to get the cell value and compare it with DateTime.Today or current_date it doesn't work
                    MessageBox.Show("Ho trovato la data odierna");

                    for (int y = 3; y <= 500; y++)
                    {
                        if (document.GetCellValueAsInt32("A" + y) == 0)
                        {
                            document.SetCellValue("A" + y, y);
                        }
                    }

                    document.SetCellValue("G4", "Sono riuscito a trovare la data odierna");
                }
            }
        }
Пример #30
0
        private void pcatBrowseButton_Click(object sender, EventArgs e)
        {
            if (cn.IsOpen)
            {
                //Open the dialog for the user to choose a spreadsheet
                OpenFileDialog openSheetDialog = new OpenFileDialog();
                openSheetDialog.FileName    = "";
                openSheetDialog.Filter      = "Excel 2007 Workbook (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                openSheetDialog.FilterIndex = 1;

                if (openSheetDialog.ShowDialog() == DialogResult.OK && Path.GetExtension(openSheetDialog.FileName) == ".xlsx")
                {
                    using (new CursorWait())
                    {
                        label1.Text = "Importing spreadsheet data. This may take a moment.";
                        label1.Refresh();

                        //Update UI
                        mcatBrowseButton.Visible = false;
                        mcatBrowseButton.Refresh();
                        pcatBrowseButton.Visible = false;

                        //open the file as a Spreadsheet Light document
                        SLDocument            ss    = new SLDocument(openSheetDialog.FileName);
                        SLWorksheetStatistics stats = ss.GetWorksheetStatistics();

                        //iterate through rows until the final row is reached
                        for (int r = 2; r <= stats.EndRowIndex; r++)
                        {
                            //Get the string values from the table
                            string lname    = ss.GetCellValueAsString(r, 1).Trim();
                            string fname    = ss.GetCellValueAsString(r, 2).Trim();
                            string mi       = ss.GetCellValueAsString(r, 3).Trim();
                            string email    = ss.GetCellValueAsString(r, 4).Trim();
                            string address1 = ss.GetCellValueAsString(r, 5).Trim();
                            string address2 = ss.GetCellValueAsString(r, 6).Trim();
                            string city     = ss.GetCellValueAsString(r, 8).Trim();
                            string state    = ss.GetCellValueAsString(r, 9).Trim();
                            string zip      = ss.GetCellValueAsString(r, 10).Trim();
                            string country  = ss.GetCellValueAsString(r, 11).Trim();

                            //add each row to the standard data table
                            pcatTable.Rows.Add("", lname, fname, mi, email, address1, address2, city, state, zip, country);
                        }
                        //close the SLDocument, as the data is now in a datatable
                        ss.CloseWithoutSaving();

                        if (cn.IsOpen)
                        {
                            //clear any old results from the table
                            cn.Execute("DELETE FROM LECOM_MATCHING_PCAT_TABLE", SQLTypes.Text);

                            SqlParameters dtParm = new SqlParameters();
                            dtParm.Add("exampleDT", pcatTable, SqlDbType.Structured);
                            dtParm.List[0].TypeName = "dbo.LecomMatchingPcatTableType";
                            cn.Execute("dbo.Lecom_MatchingImportPcat", SQLTypes.StoredProcedure, dtParm);
                            label1.Text = "Excel data import complete! Click below to view the match report.";
                            pcatReportViewButton.Visible = true;
                        }
                    }
                }
                else
                {
                    MessageBox.Show("Please select a valid Excel 2007 file!");
                    openSheetDialog.ShowDialog();
                }
            }
        }