コード例 #1
0
        private static void processFolders()
        {
            try
            {
                MyApp = new Excel.Application();
                MyApp.Visible = false;
                MyBook = MyApp.Workbooks.Open(@FileLocation);
                MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"];
                MySheetV = (Excel.Worksheet)MyBook.Sheets["Variables"];
                Console.WriteLine("Connected to Spread Sheet");
                Console.ReadLine();
                ProcessfromRow = Convert.ToInt64(MySheetV.Cells[11, 2].Value);
                ProcesstoRow = Convert.ToInt64(MySheetV.Cells[12, 2].Value);
                    string str1 = null, str2 = null, str3 = null, str4 = null, str5 = null;
                    bool createnew = false;
                    long fileconturi = 0, recuri = 0;
                    int lvl1Value = 10, lvl2Value = 10;
                    //EmpList.Add(emp);
                    
                    List<Sheets> sheets = new List<Sheets>();
                    Sheets sheet = new Sheets();
                    sheet.SheetName = FileLocation;
                    List<Rows> rows = new List<Rows>();
                    Rows r = new Rows();
                    for (var i = 2; i < ProcesstoRow+1; i++)
                    {
                        
                        //First child level
                        if (MySheet.Cells[i, y].Value2 != null)
                        {
                            str1 = MySheet.Cells[i, y].Value.ToString();

                                createnew = true;
                                lvl1Value = lvl1Value + 10;
                                //lvl2Value = 10;
                                //lvl3Value = 10;
                            y = y + 1;
                        }
                        else
                        {
                            y = y + 1;
                        }
                        if (MySheet.Cells[i, y].Value2 != null)
                        {
                            str2 = MySheet.Cells[i, y].Value.ToString();
                            if (createnew)
                            {
                                lvl2Value = lvl2Value + 10;
                                //lvl3Value = 10;
                            }

                            y = y + 1;
                        }
                        else
                        {
                            y = y + 1;
                        }
                        if (MySheet.Cells[i, y].Value2 != null)
                        {
                            str3 = MySheet.Cells[i, y].Value.ToString();
                            if (createnew)
                            {
                                r = new Rows();
                                    r.level1 = str1;
                                    r.level2 = str2;
                                    r.level3 = str3;
                            }
                            y = y + 1;
                        }
                        else
                        {
                            y = y + 1;
                            r = rows.LastOrDefault();
                        }

                            List<Folder> lstFolder = new List<Folder>();

                            if (MySheet.Cells[i, 4].Value2 != null)
                            {

                                str4 = MySheet.Cells[i, 4].Value.ToString();

                                //Check to see if the group identifyer is not blank and has a g
                                if (MySheet.Cells[i, 12].value2 != null && MySheet.Cells[i, 12].value.ToString() == "g")
                                {
                                    Console.WriteLine("Grouped string found in level 4");
                                    List<string> lstLvl4 = str4.Split('/').ToList();
                                    foreach (var kkk in lstLvl4.Where(x => x.Length > 2))
                                    {
                                        Folder fol = new Folder();
                                        fol.folder = kkk.Trim();
                                        lstFolder.Add(fol);
                                        fileconturi = recuri;
                                        if (MySheet.Cells[i, 5].Value2 != null)
                                        {
                                            str5 = MySheet.Cells[i, 5].Value.ToString();
                                            List<Subfolder> lstsubfolder = new List<Subfolder>();
                                            if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g")
                                            {
                                                List<string> lstLvl5 = str5.Split('/').ToList();
                                                foreach (var lll in lstLvl5.Where(x => x.Length > 2))
                                                {
                                                    Subfolder sf = new Subfolder();
                                                    sf.subfolder = lll.Trim();
                                                    lstsubfolder.Add(sf);
                                                }
                                                fol.SubFolders = lstsubfolder;
                                            }
                                            else
                                            {
                                                Subfolder sf = new Subfolder();
                                                sf.subfolder = str5.Trim();
                                                lstsubfolder.Add(sf);
                                                fol.SubFolders = lstsubfolder;
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    Folder fol = new Folder();
                                    fol.folder = str4;
                                    fileconturi = recuri;
                                    if (MySheet.Cells[i, 5].Value2 != null)
                                    {
                                        List<Subfolder> lstsf = new List<Subfolder>();
                                        str5 = MySheet.Cells[i, 5].Value.ToString();
                                        //r.Subfolder = str5;
                                        if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g")
                                        {
                                            List<string> lstLvl5 = str5.Split('/').ToList();
                                            foreach (var lll in lstLvl5.Where(x => x.Length > 2))
                                            {
                                                Subfolder sf = new Subfolder();
                                                sf.subfolder = lll.Trim();
                                                lstsf.Add(sf);
                                                //recsuburi = CreateNewRMSubFolder(recuri, lll.Trim(), db, i);
                                            }
                                        }
                                        else
                                        {
                                            Subfolder sf = new Subfolder();
                                            sf.subfolder = str5;

                                            if (MySheet.Cells[i, 7].value2 != null)
                                            {
                                                sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
                                            }

                                            lstsf.Add(sf);
                                            //fol.SubFolders=lstsf;
                                            //
                                            //recsuburi = CreateNewRMSubFolder(recuri, str5, db, i);
                                        }
                                        fol.SubFolders = lstsf;
                                    }
                                    else
                                    {
                                        if (MySheet.Cells[i, 7].value2 != null)
                                        {
                                            fol.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
                                        }
                                    }

                                    lstFolder.Add(fol);
                                }
                               
                                r.Folders = lstFolder;
                            }
                            y = 1;

                            if (str4 != null)
                            {

                                MySheet.Cells[i, 8] = fileconturi.ToString();


                            }
                            else
                            {
                                //Remove for basic testing
                                //long Newuri = CreateNewRMfolder(str1, str2, str3, str4, str5, str3Title);

                                //
                                //Console.WriteLine("New folder created (Uri: " + fileconturi.ToString() + ")");
                            }
                            r.SheetLine = i;

                        //7
                            //r.RMuri = fileconturi.ToString();
                            //r.Timestamp = DateTime.Now;
                            rows.Add(r);
                    }
                    sheet.Rows = rows;
                    sheets.Add(sheet);
                    //
                    string json = JsonConvert.SerializeObject(sheets.ToArray());
                    //FileLocation
                    var loc = Path.GetDirectoryName(FileLocation);
                    System.IO.File.WriteAllText(@loc+"\\SSprocessTIP.json", json);
                    //
                    MyBook.Save();
                    MyBook.Close(null, null, null);
                    MyApp.Quit();

                //}
            }
            catch (Exception exc)
            {
                MyBook.Save();
                MyBook.Close(null, null, null);
                MyApp.Quit();
                //
                Console.WriteLine("Error: "+exc.Message.ToString());
                return;
            }
        }
コード例 #2
0
        private static void ProcessClassFolders()
        {
            string iRow = null;
            try
            {
                MyApp = new Excel.Application();
                MyApp.Visible = false;
                MyBook = MyApp.Workbooks.Open(@FileLocation);
                MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"];
                MySheetV = (Excel.Worksheet)MyBook.Sheets["Variables"];
                Console.WriteLine("Connected to Spread Sheet");
                //Console.ReadLine();
                Console.WriteLine("Processing, please wait. If its a large SS then go make a coffee.");
                ProcessfromRow = Convert.ToInt64(MySheetV.Cells[11, 2].Value);
                ProcesstoRow = Convert.ToInt64(MySheetV.Cells[12, 2].Value);

                string str1 = null, str2 = null, str3 = null, str4 = null, str5 = null;
                bool createnew = false;
                long fileconturi = 0, recuri = 0;
                //EmpList.Add(emp);

                List<Sheets> sheets = new List<Sheets>();
                Sheets sheet = new Sheets();
                sheet.SheetName = FileLocation;
                List<Rows> rows = new List<Rows>();
                Rows r = new Rows();
                
                for (var i = 2; i < ProcesstoRow + 1; i++)
                {
                    if(i==350)
                    {
                        var dsd = "sfdsf";
                    }
                    
                    iRow = i.ToString();


                    //First child level
                    bool bLevel3New = false;
                    createnew = false;
                    if (MySheet.Cells[i, 1].Value2 != null)
                    {
                        str1 = MySheet.Cells[i, 1].Value.ToString();

                        createnew = true;

                    }

                    if (MySheet.Cells[i, 2].Value2 != null)
                    {
                        str2 = MySheet.Cells[i, 2].Value.ToString();
                        createnew = true;
                    }

                    if (MySheet.Cells[i, 3].Value2 != null)
                    {
                        str3 = MySheet.Cells[i, 3].Value.ToString();
                        createnew=true;
                        bLevel3New = true;
                    }
                    if(createnew)
                    {
                        r = new Rows();
                        r.level1 = str1;
                        r.level2 = str2;
                        r.level3 = str3;

                        r.SheetLine = i;
                        rows.Add(r);
                    }
                    
                    // New
                    if (MySheet.Cells[i, 4].Value2 != null)
                    {
                        if (bLevel3New == true)
                        {
                            List<Folder> lstFolder = new List<Folder>();
                            rows.LastOrDefault().Folders = lstFolder;
                        }
                        str4 = MySheet.Cells[i, 4].Value.ToString();
                        //int lvl4LoopCount = 0;

                        //Check to see if the group identifyer is not blank and has a g
                        if (MySheet.Cells[i, 12].value2 != null && MySheet.Cells[i, 12].value.ToString() == "g")
                        {
                            Console.WriteLine("Grouped string found in level 4");
                            List<string> lstLvl4 = str4.Split('/').ToList();
                            foreach (var kkk in lstLvl4.Where(x => x.Length > 2))
                            {
                                Folder fol = new Folder();
                                fol.folder = kkk.Trim();
                                fol.SheetLine = i;
                                rows.LastOrDefault().Folders.Add(fol);
                                //lstFolder.Add(fol);
                                fileconturi = recuri;
                                if (MySheet.Cells[i, 5].Value2 != null)
                                {
                                    str5 = MySheet.Cells[i, 5].Value.ToString();
                                    List<Subfolder> lstsubfolder = new List<Subfolder>();
                                    if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g")
                                    {
                                        List<string> lstLvl5 = str5.Split('/').ToList();
                                        foreach (var lll in lstLvl5.Where(x => x.Length > 2))
                                        {
                                            Subfolder sf = new Subfolder();
                                            sf.subfolder = lll.Trim();
                                            sf.SheetLine = i;

                                            lstsubfolder.Add(sf);
                                        }
                                        fol.SubFolders = lstsubfolder;
                                    }
                                    else
                                    {
                                        Subfolder sf = new Subfolder();
                                        sf.subfolder = str5.Trim();
                                        sf.SheetLine = i;
                                        lstsubfolder.Add(sf);
                                        fol.SubFolders = lstsubfolder;
                                    }
                                }
                            }
                        }
                        else
                        {
                            Folder fol = new Folder();
                            fol.folder = str4;
                            fol.SheetLine = i;
                            fileconturi = recuri;
                            if (MySheet.Cells[i, 5].Value2 != null)
                            {
                                List<Subfolder> lstsf = new List<Subfolder>();
                                str5 = MySheet.Cells[i, 5].Value.ToString();
                                //r.Subfolder = str5;
                                if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g")
                                {
                                    List<string> lstLvl5 = str5.Split('/').ToList();
                                    foreach (var lll in lstLvl5.Where(x => x.Length > 2))
                                    {
                                        Subfolder sf = new Subfolder();
                                        sf.subfolder = lll.Trim();
                                        sf.SheetLine = i;
                                        lstsf.Add(sf);
                                    }
                                }
                                else
                                {
                                    Subfolder sf = new Subfolder();
                                    sf.subfolder = str5;
                                    sf.SheetLine = i;
                                    if (MySheet.Cells[i, 7].value2 != null)
                                    {
                                        sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
                                    }

                                    lstsf.Add(sf);
                                }
                                fol.SubFolders = lstsf;
                            }
                            else
                            {
                                if (MySheet.Cells[i, 7].value2 != null)
                                {
                                    fol.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
                                }
                            }
                            rows.LastOrDefault().Folders.Add(fol);
                        }
                    }
                    else
                    {
                        if (MySheet.Cells[i, 5].Value2 != null)
                        {
                            if (rows.LastOrDefault().Folders.LastOrDefault().SubFolders==null)
                            {
                                List<Subfolder> lstsf = new List<Subfolder>();
                                rows.LastOrDefault().Folders.LastOrDefault().SubFolders = lstsf;
                            }
                            str5 = MySheet.Cells[i, 5].Value.ToString();
                            if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g")
                            {
                                List<string> lstLvl5 = str5.Split('/').ToList();
                                foreach (var lll in lstLvl5.Where(x => x.Length > 2))
                                {
                                    Subfolder sf = new Subfolder();
                                    sf.subfolder = lll.Trim();
                                    sf.SheetLine = i;
                                    if (MySheet.Cells[i, 7].value2 != null)
                                    {
                                        sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
                                    }
                                    rows.LastOrDefault().Folders.LastOrDefault().SubFolders.Add(sf);
                                }   
                            }
                            else
                            {
                                Subfolder sf = new Subfolder();
                                sf.subfolder = str5.Trim();
                                sf.SheetLine = i;
                                if (MySheet.Cells[i, 7].value2 != null)
                                {
                                    sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString();
                                }
                                //lstsubfolder.Add(sf);
                                //fol.SubFolders = lstsubfolder;
                                //rows.LastOrDefault().Folders.LastOrDefault().SubFolders = lstsubfolder;
                                rows.LastOrDefault().Folders.LastOrDefault().SubFolders.Add(sf);
                            }
                        }

                    }




                    //End New
                }




                sheet.Rows = rows;
                sheets.Add(sheet);
                //
                string json = JsonConvert.SerializeObject(sheets.ToArray());
                //FileLocation
                var loc = Path.GetDirectoryName(FileLocation);
                System.IO.File.WriteAllText(@loc + "\\SSprocessTIP.json", json);
                //
                MyBook.Save();
                MyBook.Close(null, null, null);
                MyApp.Quit();
                Console.WriteLine("Successful in building Json file.");
                Repeatmenu();

                //}
            }
            catch (Exception exc)
            {
                MyBook.Save();
                MyBook.Close(null, null, null);
                MyApp.Quit();
                //
                Console.WriteLine("Error: " + exc.Message.ToString()+" - "+iRow);
                return;
            }
        }