/// <summary> /// List all files of a determined type(s) in a directory tree /// </summary> /// <param name="homeDir">Root directory</param> /// <param name="fileType">Type of files to search. Multiple types are searchable with the '|' separator</param> /// <returns>String array of files paths</returns> public static string[] GetFilesToProcess(string homeDir, string fileType) { string[] AllFiles = new string[0]; if (Directory.Exists(homeDir)) { try { AllFiles = GetFiles(homeDir, fileType, SearchOption.AllDirectories); foreach (string sFile in AllFiles) { FileOps.RemoveAttributes(sFile); //File.Delete(sFile); } } catch (UnauthorizedAccessException) { } return(AllFiles); } else { Logger.PrintLC("Search Folder " + homeDir + " not exists, no files found"); return(AllFiles); } }
/// <summary> /// Converts a proprietary MS old format (xls) to the Open Office (xlsx). /// -A.Amato, 2016 11 /// </summary> /// <param name="fileName">Path and file name to convert.</param> /// <returns>True if successfull, False otherwise.</returns> public static bool ConvertXLStoXLSX(string fileName = null) { if (string.IsNullOrEmpty(fileName)) { return(false); } FileInfo fileInfo = new FileInfo(fileName); if (fileInfo.Exists && (fileInfo.Extension == ".xls")) { //Excel.ApplicationClass ExApp = new Excel.ApplicationClass(); Excel.Workbook ExWB; // = new Excel.Workbook(); try { Excel.Worksheet ExWS = new Excel.Worksheet(); if (ExApp == null) { ExApp = new Excel.ApplicationClass(); } FileOps.RemoveAttributes(fileName); ExWB = ExApp.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); fileName = Path.ChangeExtension(fileName, ".xlsx"); ExApp.DisplayAlerts = false; FileInfo FileToSaveInfo = new FileInfo(fileName); if (FileToSaveInfo.Exists) { FileToSaveInfo.Delete(); } ExWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,//.xlOpenXMLStrictWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); ExWB.Close(); ExApp.DisplayAlerts = true; Logger.PrintLC("File " + fileInfo.Name + " converted successfully to XLSX", 3); Marshal.FinalReleaseComObject(ExWB); Marshal.FinalReleaseComObject(ExWS); //Marshal.FinalReleaseComObject(ExApp); } catch (Exception exp) { Logger.PrintLC("File " + fileInfo.Name + " could not be converted to XLSX. Error: " + exp.Message, 3); return(false); } } else { return(false); } return(true); }
/// <summary> /// Save the Persistence on 'this' Connection /// </summary> /// <returns></returns> public bool SavePersistence() { try { FileOps.RemoveAttributes(fileERwin); if (!scPersistenceUnit.Save()) { Logger.PrintLC("Could NOT save Persistence: " + scPersistenceUnit.ObjectId, 2); return(false); } else { Logger.PrintLC("Persistence SAVED: " + scPersistenceUnit.ObjectId, 2); return(true); } } catch (Exception exp) { Logger.PrintLC("Persistence NOT saved.", 2); return(false); } }
public bool openModelConnection(string ERw) { if (ERw == null) { return(false); } if (!File.Exists(ERw)) { Logger.PrintLC("Could not find file: " + ERw, 2); return(false); } try { DatabaseN = new List <string>(); SchemaN = new List <string> { "SYSADM", "SYSFUN", "SYSIBM", "SYSPROC", "SYSTOOLS" }; fileERwin = ERw; FileOps.RemoveAttributes(ERw); fileInfoERwin = new FileInfo(fileERwin); scERwin = new SCAPI.Application(); scPersistenceUnit = scERwin.PersistenceUnits.Add(ERw, "RDO=No"); scSession = scERwin.Sessions.Add(); scSession.Open(scPersistenceUnit); Logger.PrintLC("Connection opened.", 2); return(true); } catch (Exception exp) { Logger.PrintLC("Connection opening error: " + exp.Message, 2); return(false); } }
public static bool XLSXWriteErrorInCell(FileInfo fileDaAprire, int row, int column, int priorityWrite, string text, string sheet = ConfigFile.ATTRIBUTI) { string file = fileDaAprire.FullName; if (!File.Exists(file)) { Logger.PrintLC("Reading File " + fileDaAprire.Name + ": doesn't exist.", priorityWrite); return(false); } FileOps.RemoveAttributes(file); if (fileDaAprire.Extension == ".xls") { if (!ConvertXLStoXLSX(file)) { return(false); } file = Path.ChangeExtension(file, ".xlsx"); fileDaAprire = new FileInfo(file); } ExcelPackage p = null; ExcelWorkbook WB = null; ExcelWorksheets ws = null; try { p = new ExcelPackage(fileDaAprire); WB = p.Workbook; ws = WB.Worksheets; //.Add(wsName + wsNumber.ToString()); } catch (Exception exp) { Logger.PrintLC("Reading file: " + fileDaAprire.Name + ": could not open file in location " + fileDaAprire.DirectoryName, priorityWrite); return(false); } //bool FilesEnd = false; //int EmptyRow = 0; //int columns = 0; foreach (var worksheet in ws) { if (worksheet.Name == sheet) { try { worksheet.Cells[row, column].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, column].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 0, 0)); worksheet.Cells[row, column].Style.Font.Bold = true; worksheet.Cells[row, column].Value = "KO"; worksheet.Cells[row, column + 1].Value = text; p.SaveAs(fileDaAprire); return(true); } catch (Exception exp) { Logger.PrintLC("Error while writing on file " + fileDaAprire.Name + ". Description: " + exp.Message); return(false); } } } Logger.PrintLC("File writing. Sheet " + sheet + "could not be found in file " + fileDaAprire.Name, priorityWrite); return(false); }
/// <summary> /// Reads and processes Attributes data from excel's 'ATTRIBUTI' sheet /// </summary> /// <param name="fileDaAprire"></param> /// <param name="sheet"></param> /// <returns></returns> public static List <AttributeT> ReadXFileAttribute(FileInfo fileDaAprire, string db, string sheet = ConfigFile.ATTRIBUTI) { string file = fileDaAprire.FullName; List <AttributeT> listaFile = new List <AttributeT>(); if (!File.Exists(file)) { Logger.PrintLC("Reading Attributes. File " + fileDaAprire.Name + " doesn't exist.", 2); return(listaFile = null); } FileOps.RemoveAttributes(file); if (fileDaAprire.Extension == ".xls") { if (!ConvertXLStoXLSX(file)) { return(listaFile = null); } file = Path.ChangeExtension(file, ".xlsx"); fileDaAprire = new FileInfo(file); } ExcelPackage p = null; ExcelWorkbook WB = null; ExcelWorksheets ws = null; try { p = new ExcelPackage(fileDaAprire); WB = p.Workbook; ws = WB.Worksheets; //.Add(wsName + wsNumber.ToString()); } catch (Exception exp) { Logger.PrintLC("Reading Attributes. Could not open file " + fileDaAprire.Name + "in location " + fileDaAprire.DirectoryName, 2); return(listaFile = null); } bool FilesEnd = false; int EmptyRow = 0; foreach (var worksheet in ws) { if (worksheet.Name == sheet) { FilesEnd = false; for (int RowPos = ConfigFile.HEADER_RIGA + 1; FilesEnd != true; RowPos++) { bool incorrect = false; string nomeTabella = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Nome Tabella Legacy"]].Text; string nomeCampo = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Nome Campo Legacy"]].Text; string dataType = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Datatype"]].Text; string chiave = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Chiave"]].Text; string unique = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Unique"]].Text; string chiaveLogica = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Chiave Logica"]].Text; string mandatoryFlag = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Mandatory Flag"]].Text; string dominio = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Dominio"]].Text; string storica = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Storica"]].Text; string datoSensibile = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Dato Sensibile"]].Text; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET2].Value = ""; string error = ""; //Check Nome Tabella Legacy if (string.IsNullOrWhiteSpace(nomeTabella)) { incorrect = true; error += "NOME TABELLA LEGACY mancante."; } //Check Nome Campo Legacy if (string.IsNullOrWhiteSpace(nomeCampo)) { incorrect = true; if (!string.IsNullOrWhiteSpace(error)) { error += " "; } error += "NOME CAMPO LEGACY mancante."; } //Check DataType if (string.IsNullOrWhiteSpace(dataType)) { incorrect = true; if (!string.IsNullOrWhiteSpace(error)) { error += " "; } error += "DATATYPE mancante."; } else { if (!Funct.ParseDataType(dataType, db)) { incorrect = true; if (!string.IsNullOrWhiteSpace(error)) { error += " "; } error += "DATATYPE non conforme."; } } //Check Chiave if (!(string.Equals(chiave, "S", StringComparison.OrdinalIgnoreCase) || string.Equals(chiave, "N", StringComparison.OrdinalIgnoreCase))) { incorrect = true; if (!string.IsNullOrWhiteSpace(error)) { error += " "; } error += "CHIAVE non conforme."; } //Check Unique if (!(string.Equals(unique, "S", StringComparison.OrdinalIgnoreCase) || string.Equals(unique, "N", StringComparison.OrdinalIgnoreCase))) { incorrect = true; if (!string.IsNullOrWhiteSpace(error)) { error += " "; } error += "UNIQUE non conforme."; } //Check Chiave Logica if (!(string.Equals(chiaveLogica, "S", StringComparison.OrdinalIgnoreCase) || string.Equals(chiaveLogica, "N", StringComparison.OrdinalIgnoreCase))) { incorrect = true; if (!string.IsNullOrWhiteSpace(error)) { error += " "; } error += "CHIAVE LOGICA non conforme."; } //Check Mandatory Flag if (!(string.Equals(mandatoryFlag, "S", StringComparison.OrdinalIgnoreCase) || string.Equals(mandatoryFlag, "N", StringComparison.OrdinalIgnoreCase))) { incorrect = true; if (!string.IsNullOrWhiteSpace(error)) { error += " "; } error += "MANDATORY FLAG non conforme."; } //Check Dominio if (!(string.Equals(dominio, "S", StringComparison.OrdinalIgnoreCase) || string.Equals(dominio, "N", StringComparison.OrdinalIgnoreCase))) { incorrect = true; if (!string.IsNullOrWhiteSpace(error)) { error += " "; } error += "DOMINIO non conforme."; } //Check Storica if (!(string.Equals(storica, "S", StringComparison.OrdinalIgnoreCase) || string.Equals(storica, "N", StringComparison.OrdinalIgnoreCase))) { incorrect = true; if (!string.IsNullOrWhiteSpace(error)) { error += " "; } error += "STORICA non conforme."; } //Check Dato Sensibile if (!(string.Equals(datoSensibile, "S", StringComparison.OrdinalIgnoreCase) || string.Equals(datoSensibile, "N", StringComparison.OrdinalIgnoreCase))) { incorrect = true; if (!string.IsNullOrWhiteSpace(error)) { error += " "; } error += "DATO SENSIBILE non conforme."; } if (incorrect == false) { EmptyRow = 0; AttributeT ValRiga = new AttributeT(row: RowPos, db: db, nomeTabellaLegacy: nomeTabella); // Assegnazione valori checkati ValRiga.NomeTabellaLegacy = nomeTabella; ValRiga.NomeCampoLegacy = nomeCampo; ValRiga.DataType = dataType; if (string.Equals(chiave.ToUpper(), "S")) { ValRiga.Chiave = 0; } else { ValRiga.Chiave = 100; } ValRiga.Unique = unique; ValRiga.ChiaveLogica = chiaveLogica; if (string.Equals(mandatoryFlag.ToUpper(), "S")) { ValRiga.MandatoryFlag = 1; } else { ValRiga.MandatoryFlag = 0; } ValRiga.Dominio = dominio; ValRiga.Storica = storica; ValRiga.DatoSensibile = datoSensibile; //Assegnazione valori opzionali if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["SSA"]].Text)) { ValRiga.SSA = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["SSA"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Area"]].Text)) { ValRiga.Area = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Area"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Definizione Campo"]].Text)) { ValRiga.DefinizioneCampo = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Definizione Campo"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Tipologia Tabella \n(dal DOC. LEGACY) \nEs: Dominio,Storica,\nDati"]].Text)) { ValRiga.TipologiaTabella = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Tipologia Tabella \n(dal DOC. LEGACY) \nEs: Dominio,Storica,\nDati"]].Text; } int t; //Funzionale all'assegnazione di 'Lunghezza' e 'Decimali' if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Lunghezza"]].Text)) { if (int.TryParse(worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Lunghezza"]].Text, out t)) { ValRiga.Lunghezza = t; } } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Decimali"]].Text)) { if (int.TryParse(worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Decimali"]].Text, out t)) { ValRiga.Decimali = t; } } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Provenienza dominio "]].Text)) { ValRiga.ProvenienzaDominio = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Provenienza dominio "]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Note"]].Text)) { ValRiga.Note = worksheet.Cells[RowPos, ConfigFile._ATTRIBUTI["Note"]].Text; } listaFile.Add(ValRiga); worksheet.Column(ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET1).Width = 10; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 255, 0)); worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET1].Style.Font.Bold = true; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET1].Value = "OK"; } else { worksheet.Column(ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET1).Width = 10; worksheet.Column(ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET2).Width = 50; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 0, 0)); worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET1].Style.Font.Bold = true; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET1].Value = "KO"; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI + ConfigFile.ATTRIBUTI_EXCEL_COL_OFFSET2].Value = error; EmptyRow += 1; if (EmptyRow >= 10) { FilesEnd = true; } } //****************************************** // Verifica lo stato delle successive 10 righe per determinare la fine della tabella. int prossime = 0; for (int i = 1; i < 11; i++) { if (string.IsNullOrWhiteSpace(worksheet.Cells[RowPos + i, ConfigFile._ATTRIBUTI["Nome Tabella Legacy"]].Text)) { prossime++; } } if (prossime == 10) { FilesEnd = true; } //****************************************** } p.SaveAs(new FileInfo(Path.Combine(ConfigFile.FOLDERDESTINATION, fileDaAprire.Name))); return(listaFile); } } return(listaFile = null); }
/// <summary> /// Reads and processes Table data from excel's 'TABELLE' sheet /// </summary> /// <param name="fileDaAprire"></param> /// <param name="sheet"></param> /// <returns></returns> public static List <EntityT> ReadXFileEntity(FileInfo fileDaAprire, string db, string sheet = ConfigFile.TABELLE) { string file = fileDaAprire.FullName; List <EntityT> listaFile = new List <EntityT>(); if (!File.Exists(file)) { Logger.PrintLC("Reading Tables. File " + fileDaAprire.Name + " doesn't exist.", 3); return(listaFile = null); } FileOps.RemoveAttributes(file); if (fileDaAprire.Extension == ".xls") { if (!ConvertXLStoXLSX(file)) { return(listaFile = null); } file = Path.ChangeExtension(file, ".xlsx"); fileDaAprire = new FileInfo(file); } ExcelPackage p = null; ExcelWorkbook WB = null; ExcelWorksheets ws = null; try { p = new ExcelPackage(fileDaAprire); WB = p.Workbook; ws = WB.Worksheets; //.Add(wsName + wsNumber.ToString()); } catch (Exception exp) { Logger.PrintLC("Reading Tables. Could not open file " + fileDaAprire.Name + "in location " + fileDaAprire.DirectoryName, 3); return(listaFile = null); } bool FilesEnd = false; int EmptyRow = 0; int columns = 0; foreach (var worksheet in ws) { if (worksheet.Name == sheet) { FilesEnd = false; for (int RowPos = ConfigFile.HEADER_RIGA + 1; FilesEnd != true; RowPos++) { bool incorrect = false; string nome = worksheet.Cells[RowPos, ConfigFile._TABELLE["Nome Tabella"]].Text; string flag = worksheet.Cells[RowPos, ConfigFile._TABELLE["Flag BFD"]].Text; if (string.IsNullOrWhiteSpace(nome)) { incorrect = true; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET2].Value = ""; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 0, 0)); worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Font.Bold = true; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Value = "KO"; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET2].Value = "Valore di NOME TABELLA mancante."; } if (!(string.Equals(flag, "S", StringComparison.OrdinalIgnoreCase) || string.Equals(flag, "N", StringComparison.OrdinalIgnoreCase))) { incorrect = true; string error = worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + 2].Text; if (!string.IsNullOrWhiteSpace(error)) { error = error + " "; } worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 0, 0)); worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Font.Bold = true; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Value = "KO"; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET2].Value = error + "Valore di FLAG BFD non conforme."; } if (incorrect == false) { EmptyRow = 0; EntityT ValRiga = new EntityT(row: RowPos, db: db, tName: nome); ValRiga.TableName = nome; if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["SSA"]].Text)) { ValRiga.SSA = worksheet.Cells[RowPos, ConfigFile._TABELLE["SSA"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Nome host"]].Text)) { ValRiga.HostName = worksheet.Cells[RowPos, ConfigFile._TABELLE["Nome host"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Nome Database"]].Text)) { ValRiga.DatabaseName = worksheet.Cells[RowPos, ConfigFile._TABELLE["Nome Database"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Schema"]].Text)) { ValRiga.Schema = worksheet.Cells[RowPos, ConfigFile._TABELLE["Schema"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Descrizione Tabella"]].Text)) { ValRiga.TableDescr = worksheet.Cells[RowPos, ConfigFile._TABELLE["Descrizione Tabella"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Tipologia Informazione"]].Text)) { ValRiga.InfoType = worksheet.Cells[RowPos, ConfigFile._TABELLE["Tipologia Informazione"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Perimetro Tabella"]].Text)) { ValRiga.TableLimit = worksheet.Cells[RowPos, ConfigFile._TABELLE["Perimetro Tabella"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Granularità Tabella"]].Text)) { ValRiga.TableGranularity = worksheet.Cells[RowPos, ConfigFile._TABELLE["Granularità Tabella"]].Text; } if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Flag BFD"]].Text)) { ValRiga.FlagBFD = worksheet.Cells[RowPos, ConfigFile._TABELLE["Flag BFD"]].Text; } listaFile.Add(ValRiga); worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 255, 0)); worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Font.Bold = true; worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Value = "OK"; } else { EmptyRow += 1; if (EmptyRow >= 10) { FilesEnd = true; } } //****************************************** // Verifica lo stato delle successive 10 righe per determinare la fine della tabella. int prossime = 0; for (int i = 1; i < 11; i++) { if (string.IsNullOrWhiteSpace(worksheet.Cells[RowPos + i, ConfigFile._TABELLE["Nome Tabella"]].Text)) { prossime++; } } if (prossime == 10) { FilesEnd = true; } //****************************************** } p.SaveAs(new FileInfo(Path.Combine(ConfigFile.FOLDERDESTINATION, fileDaAprire.Name))); return(listaFile); } } return(listaFile = null); }
public static bool FileValidation(string file) { //SCAPI.Application testAPP = new SCAPI.Application(); string testoLog = string.Empty; FileInfo fileDaAprire = new FileInfo(file); if (fileDaAprire.Extension == ".xls") { if (!ConvertXLStoXLSX(file)) { return(false); } file = Path.ChangeExtension(file, ".xlsx"); fileDaAprire = new FileInfo(file); } // if (file.EndsWith(".xls")) ExcelPackage p = new ExcelPackage(fileDaAprire); //using (ExcelPackage p = new ExcelPackage(fileDaAprire)) //{ //p.SaveAs(@"C:\nome.xls"); //WB.Worksheets ExcelWorkbook WB = p.Workbook; ExcelWorksheets ws = WB.Worksheets; //.Add(wsName + wsNumber.ToString()); bool sheetFound = false; bool columnsFound = false; int columns = 0; foreach (var worksheet in ws) { // SEZIONE TABELLE if (worksheet.Name == ConfigFile.TABELLE) { columns = 0; sheetFound = true; columnsFound = false; //List<string> dd = new List<string>(); for (int columnsPosition = ConfigFile.HEADER_COLONNA_MIN_TABELLE; columnsPosition <= ConfigFile.HEADER_COLONNA_MAX_TABELLE; columnsPosition++) { string value = worksheet.Cells[ConfigFile.HEADER_RIGA, columnsPosition].Text; if (ConfigFile._TABELLE.ContainsKey(value)) { columns += 1; if (ConfigFile._TABELLE[value] != columnsPosition) { goto ERROR; } //dd.Add(worksheet.Cells[ConfigFile.HEADER_RIGA, columnsPosition].Text); } else { //worksheet.Cells[ConfigFile.HEADER_RIGA, columnsPosition].Value = ""; testoLog = fileDaAprire.Name + ": file could not be elaborated."; Logger.PrintLC(testoLog, 2); goto ERROR; } } if (columns == ConfigFile.HEADER_MAX_COLONNE_TABELLE) { columnsFound = true; } else { goto ERROR; } //worksheet.Cell[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid; //worksheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228)); //worksheet.Cells[1, 1].Style.Font.Bold = true; //p.Save(); } // SEZIONE ATTRIBUTI if (worksheet.Name == ConfigFile.ATTRIBUTI) { columns = 0; columnsFound = false; sheetFound = true; for (int columnsPosition = ConfigFile.HEADER_COLONNA_MIN_ATTRIBUTI; columnsPosition <= ConfigFile.HEADER_COLONNA_MAX_ATTRIBUTI; columnsPosition++) { string value = worksheet.Cells[ConfigFile.HEADER_RIGA, columnsPosition].Text; if (ConfigFile._ATTRIBUTI.ContainsKey(value)) { columns += 1; if (ConfigFile._ATTRIBUTI[value] != columnsPosition) { goto ERROR; } } else { testoLog = fileDaAprire.Name + ": file could not be elaborated."; Logger.PrintLC(testoLog, 2); goto ERROR; } } if (columns == ConfigFile.HEADER_MAX_COLONNE_ATTRIBUTI) { columnsFound = true; } else { goto ERROR; } } // SEZIONE RELAZIONI if (worksheet.Name == ConfigFile.RELAZIONI) { columns = 0; columnsFound = false; sheetFound = true; for (int columnsPosition = ConfigFile.HEADER_COLONNA_MIN_RELAZIONI; columnsPosition <= ConfigFile.HEADER_COLONNA_MAX_RELAZIONI; columnsPosition++) { string value = worksheet.Cells[ConfigFile.HEADER_RIGA, columnsPosition].Text; if (ConfigFile._RELAZIONI.ContainsKey(value)) { columns += 1; if (ConfigFile._RELAZIONI[value] != columnsPosition) { goto ERROR; } } else { testoLog = fileDaAprire.Name + ": file could not be elaborated."; Logger.PrintLC(testoLog, 2); goto ERROR; } } if (columns == ConfigFile.HEADER_MAX_COLONNE_RELAZIONI) { columnsFound = true; } else { goto ERROR; } } } ERROR: WB.Dispose(); p.Dispose(); //MngProcesses.KillAllOf(MngProcesses.ProcList("EXCEL")); string fileError = Path.Combine(fileDaAprire.DirectoryName, Path.GetFileNameWithoutExtension(file) + "_KO.txt"); string fileCorrect = Path.Combine(fileDaAprire.DirectoryName, Path.GetFileNameWithoutExtension(file) + "_OK.txt"); if (File.Exists(fileError)) { FileOps.RemoveAttributes(fileError); File.Delete(fileError); } if (File.Exists(fileCorrect)) { FileOps.RemoveAttributes(fileCorrect); File.Delete(fileCorrect); } if (sheetFound != true || columnsFound != true) { Logger.PrintLC(fileDaAprire.Name + ": file NON idoneo all'elaborazione.", 2); Logger.PrintF(fileError, "File columns not formatted correctly.", true); return(false); } Logger.PrintLC(fileDaAprire.Name + ": file IDONEO all'elaborazione.", 2); Logger.PrintF(fileCorrect, "File columns formatted correctly.", true); return(true); }