/// <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);
}
}
/// <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
{
return(AllFiles);
}
}
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);
}
}
/// <summary>
/// MAIN process
/// </summary>
/// <returns></returns>
public static int StartProcess()
{
try
{
string[] ElencoExcel = DirOps.GetFilesToProcess(ConfigFile.ROOT, "*.xls|.xlsx");
List <string> gg = FileOps.GetTrueFilesToProcess(ElencoExcel);
//####################################
//Ciclo MAIN
foreach (var file in gg)
{
string TemplateFile = null;
if (ExcelOps.FileValidation(file))
{
FileT fileT = Parser.ParseFileName(file);
string destERFile = null;
if (fileT != null)
{
switch (fileT.TipoDBMS)
{
case ConfigFile.DB2_NAME:
TemplateFile = ConfigFile.ERWIN_TEMPLATE_DB2;
break;
case ConfigFile.ORACLE:
TemplateFile = ConfigFile.ERWIN_TEMPLATE_ORACLE;
break;
default:
TemplateFile = ConfigFile.ERWIN_TEMPLATE_DB2;
break;
}
FileInfo origin = new FileInfo(file);
string fileName = Path.GetFileNameWithoutExtension(file);
destERFile = Path.Combine(ConfigFile.FOLDERDESTINATION, fileName + Path.GetExtension(TemplateFile));
if (!FileOps.CopyFile(TemplateFile, destERFile))
{
continue;
}
}
else
{
continue;
}
//Apertura connessione per il file attuale
ConnMng connessione = new ConnMng();
if (!connessione.openModelConnection(destERFile))
{
continue;
}
//Aggiornamento della struttura dati per il file attuale
if (!connessione.SetRootObject())
{
continue;
}
if (!connessione.SetRootCollection())
{
continue;
}
FileInfo fInfo = new FileInfo(file);
List <EntityT> DatiFile = ExcelOps.ReadXFileEntity(fInfo, fileT.TipoDBMS);
foreach (var dati in DatiFile)
{
connessione.CreateEntity(dati, TemplateFile);
}
fInfo = new FileInfo(Path.Combine(ConfigFile.FOLDERDESTINATION, Path.GetFileNameWithoutExtension(file) + ".xlsx"));
List <AttributeT> AttrFile = null;
if (File.Exists(fInfo.FullName))
{
AttrFile = ExcelOps.ReadXFileAttribute(fInfo, fileT.TipoDBMS);
}
//ATTRIBUTI - PASSAGGIO UNO
//Aggiornamento dati struttura
if (!connessione.SetRootObject())
{
continue;
}
if (!connessione.SetRootCollection())
{
continue;
}
//############################
foreach (var dati in AttrFile)
{
connessione.CreateAttributePassOne(dati, TemplateFile);
}
//ATTRIBUTI - PASSAGGIO DUE
//Aggiornamento dati struttura
if (!connessione.SetRootObject())
{
continue;
}
if (!connessione.SetRootCollection())
{
continue;
}
//############################
foreach (var dati in AttrFile)
{
connessione.CreateAttributePassTwo(dati, TemplateFile);
}
//Chiusura connessione per il file attuale.
connessione.CloseModelConnection();
}
}
return(0);
}
catch (Exception exp)
{
//return exp.HResult;
return(6);
}
return(6);
}
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);
}