/// <summary>
/// Allows the programmer to run a query against the Database.
/// </summary>
/// <param name="sql">The SQL to run</param>
/// <returns>A DataTable containing the result set.</returns>
public DataTable GetDataTable(string sql)
{
DataTable dt = new DataTable();
try
{
SQLiteConnection cnn = new SQLiteConnection(dbConnection);
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn)
{
CommandText = sql
};
//mycommand.Parameters.Add(sql);
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
cnn.Close();
}
catch (SQLiteException e)
{
IO.Log(2018062101, "GetDataTable " + e.GetType() + " | " + sql + " | " + e.Message);;
//throw new Exception(e.Message);
}
catch (Exception e)
{
IO.Log(2018062102, "GetDataTable " + e.GetType() + " | " + sql + " | " + e.Message);
//throw new Exception(e.Message);
}
return(dt);
}
/// <summary>
/// Allows the programmer to easily insert into the DB
/// </summary>
/// <param name="tableName">The table into which we insert the data.</param>
/// <param name="data">A dictionary containing the column names and data for the insert.</param>
/// <returns>A boolean true or false to signify success or failure.</returns>
public bool Insert(String tableName, Dictionary <String, String> data)
{
String columns = "";
String values = "";
Boolean returnCode = true;
foreach (KeyValuePair <String, String> val in data)
{
columns += String.Format(" {0},", val.Key.ToString());
values += String.Format(" '{0}',", val.Value);
}
columns = columns.Substring(0, columns.Length - 1);
values = values.Substring(0, values.Length - 1);
try
{
this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
}
catch (Exception fail)
{
IO.Log(2018062104, fail.GetType() + " | " + fail.Message + " | " + fail.InnerException);
returnCode = false;
}
return(returnCode);
}
private void Button_tableToExcel_Click(object sender, RoutedEventArgs e)
{
string targetPath = Path.Combine(Var.LocDirExcel, "BEB_" + Var.SqlSelectedTimeVon.Substring(0, 10) + "_" + Var.SqlSelectedTimeBis.Substring(0, 10) + ".xls");
IO.Log(2018062806, "Speichere Excel-Datei: " + targetPath);
ExcelClass.ExportToExcel(Var.MyDataTable, targetPath);
System.Diagnostics.Process.Start(Var.LocDirExcel);
}
// CancellationTokenSource cts;
private void CancelButton_Click(object sender, RoutedEventArgs e)
{
CancellationTokenSource cts = new CancellationTokenSource();
// Request cancellation.
cts.Cancel();
IO.Log(20180626, "Cancellation set in token source...");
Thread.Sleep(2500);
// Cancellation should have happened, so call Dispose.
cts.Dispose();
}
public static void ReadFromIni(string iniPath)
{
if (!File.Exists(iniPath))
{
MessageBox.Show("Die INI-Datei '" + iniPath + "' existiert nicht.", "ReadFromIni()");
}
try
{
NativeMethods Ini;
Ini = new NativeMethods(Var.IniPath);
bool _GlobalLoggingAllowed;
bool _TryParseSucessfull;
_TryParseSucessfull = bool.TryParse(Ini["Allgemein"]["GlobalLoggingAllowed"], out _GlobalLoggingAllowed);
if (_TryParseSucessfull)
{
Var.GlobalLoggingAllowed = _GlobalLoggingAllowed;
}
else
{
MessageBox.Show("'GlobalLoggingAllowed' war '" + Ini["Allgemein"]["GlobalLoggingAllowed"] + "' und konnte in INI nicht als BOOL erkannt werden und wird 'true' gesetzt. ", "ReadFromIni()");
Var.GlobalLoggingAllowed = true;
}
Var.SleepingMilliSec = Int32.Parse(Ini["Allgemein"]["SleepingMilliSec"].ToString());
Var.DbPath = Ini["DB"]["DbPath"].ToString();
Var.LocDirInput = Ini["DIR"]["LocDirInput"].ToString();
Var.LocDirOperation = Ini["DIR"]["LocDirOperation"].ToString();
Var.LocDirScales = Ini["DIR"]["LocDirScales"].ToString();
Var.LocDirExcel = Ini["DIR"]["LocDirExcel"].ToString();
Var.FtpDirInput = Ini["FTP"]["FtpDir_Input"].ToString();
Var.FtpDirOperation = Ini["FTP"]["FtpDir_Operation"].ToString();
Var.FtpDirScales = Ini["FTP"]["FtpDir_Scales"].ToString();
Var.FtpUsername1 = Ini["FTP"]["Username1"].ToString();
Var.FtpPassword1 = Ini["FTP"]["Password1"].ToString();
IO.Log(2018062004, "INI-Datei wurde geladen.");
}
catch (Exception ex)
{
MessageBox.Show("Die INI-Datei wurde nicht vollständig geladen. Nähere Informationen in der aktuellen LOG-Datei bei Fehlernummer 2018062005", "Fehler INI-Werte lesen.", MessageBoxButton.OK, MessageBoxImage.Warning);
IO.Log(2018062005, "INI wurde nicht geladen. " + ex.Message + " | " + ex.InnerException + " | " + ex.Source + " | " + ex.GetBaseException().ToString());
}
}
/// <summary>
/// Allows the programmer to easily delete rows from the DB.
/// </summary>
/// <param name="tableName">The table from which to delete.</param>
/// <param name="where">The where clause for the delete.</param>
/// <returns>A boolean true or false to signify success or failure.</returns>
public bool Delete(String tableName, String where)
{
Boolean returnCode = true;
try
{
this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
}
catch (Exception fail)
{
IO.Log(2018062103, fail.Message);
returnCode = false;
}
return(returnCode);
}
/// <summary>
/// Wird mit DispatcherTimer timer aus MainWindow getriggert. Prüft, ob die Zeit AutoReadFtpStartTime abgelaufen ist, setzt sie ggf. neu und startet das Laden aus FTP-Verzeichnis.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void TimerTick(object sender, EventArgs e)
{
if (DateTime.Now.CompareTo(Var.StartTimeFtpAutoRead) > 0)
{
IO.Log(2018062905, "Aktualisierung von Ftp/Datei/Datenbank (Letzte Prüfung: " + Var.StartTimeFtpAutoRead + ")");
Var.StartTimeFtpAutoRead = DateTime.Now.AddHours(Var.HourTimePeriodForNewFtpUpdate);
LoadFtp(Var.FtpDirInput, Var.LocDirInput);
LoadDir(Var.LocDirInput, Var.ArchiveFile_Input, IO.WriteToDb_Input);
LoadFtp(Var.FtpDirOperation, Var.LocDirOperation);
LoadDir(Var.LocDirOperation, Var.ArchiveFile_Operation, IO.WriteToDb_Operation);
LoadDir(Var.LocDirScales, Var.ArchiveFile_Scales, IO.WriteToDb_Scales);
}
}
/* List Directory Contents File/Folder Name Only */
public string[] DirectoryListSimple(string directory)
{
try
{
System.Threading.Thread.Sleep(Var.SleepingMilliSec);
/* Create an FTP Request */
//ftpRequest = (FtpWebRequest)FtpWebRequest.Create(host + "/" + directory);
IO.Log(2018062803, "Liste FTP-Ordner " + Path.Combine(host, directory) + @"/");
ftpRequest = (FtpWebRequest)FtpWebRequest.Create(Path.Combine(host, directory) + @"/");
/* Log in to the FTP Server with the User Name and Password Provided */
ftpRequest.Credentials = new NetworkCredential(user, pass);
/* When in doubt, use these options */
ftpRequest.UseBinary = true;
ftpRequest.UsePassive = true;
ftpRequest.KeepAlive = true;
/* Specify the Type of FTP Request */
ftpRequest.Method = WebRequestMethods.Ftp.ListDirectory;
/* Establish Return Communication with the FTP Server */
ftpResponse = (FtpWebResponse)ftpRequest.GetResponse();
/* Establish Return Communication with the FTP Server */
ftpStream = ftpResponse.GetResponseStream();
/* Get the FTP Server's Response Stream */
StreamReader ftpReader = new StreamReader(ftpStream);
/* Store the Raw Response */
string directoryRaw = null;
/* Read Each Line of the Response and Append a Pipe to Each Line for Easy Parsing */
try { while (ftpReader.Peek() != -1)
{
directoryRaw += ftpReader.ReadLine() + "|";
}
}
catch (Exception ex) { IO.Log(2018061808, "ftp, directoryListSimple | " + ex.ToString()); }
/* Resource Cleanup */
ftpReader.Close();
ftpStream.Close();
ftpResponse.Close();
ftpRequest = null;
System.Threading.Thread.Sleep(Var.SleepingMilliSec);
/* Return the Directory Listing as a string Array by Parsing 'directoryRaw' with the Delimiter you Append (I use | in This Example) */
try { string[] directoryList = directoryRaw.Split("|".ToCharArray()); return(directoryList); }
catch (Exception ex) { IO.Log(2018061809, "ftp, directoryListSimple | " + ex.ToString()); }
}
catch (Exception ex) { IO.Log(2018061810, "ftp, directoryListSimple | " + ex.ToString()); }
/* Return an Empty string Array if an Exception Occurs */
return(new string[] { "" });
}
/// <summary>
/// Entlässt ein COM-Objekt, z.B. Excel-Objekte
/// </summary>
/// <param name="obj">COM-Objekt, z.B. Excel-Objekte</param>
private static void ReleaseObject(object obj)
{
try
{
Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
IO.Log(2018062903, "Eine Ausnahme ist aufgetreten während ein COM-Objekt entlassen wird: " + ex.GetType() + " | " + ex.Message + " | " + ex.InnerException);
}
finally
{
GC.Collect();
}
}
public static void CreateDirWithWriteAccess(string path)
{
if (!Directory.Exists(path))
{
try
{
DirectorySecurity securityRules = new DirectorySecurity();
SecurityIdentifier sid = new SecurityIdentifier(WellKnownSidType.AuthenticatedUserSid, null);
securityRules.AddAccessRule(new FileSystemAccessRule(sid, FileSystemRights.FullControl, AccessControlType.Allow));
Directory.CreateDirectory(path, securityRules);
IO.Log(2018062506, Directory.GetAccessControl(path).ToString());
}
catch (Exception ex)
{
IO.Log(2018062506, "Ordner <" + path + "> konnte nicht mit Schreibrechten erstellt werden. | " + ex.Message);
}
}
}
/// <summary>
/// Prüft Ob die Datei locIniPath existiert. Wenn nicht, wird sie neu erstellt und mit Standardwerten beschrieben.
/// </summary>
/// <param name="locIniPath"></param>
public static void CheckAndCreateIniFile(string locIniPath)
{
try
{
string locIniDir = Path.GetDirectoryName(locIniPath);
Directory.CreateDirectory(locIniDir);
}
catch (Exception ex)
{
IO.Log(2018062107, ex.GetType() + ex.Message);
}
if (!File.Exists(locIniPath))
{
FileInfo FI = new FileInfo(locIniPath);
FileStream FS = FI.Create();
FS.Close();
NativeMethods Ini;
Ini = new NativeMethods(locIniPath);
Ini["Allgemein"]["GlobalLoggingAllowed"] = "true";
Ini["Allgemein"]["SleepingMilliSec"] = "100";
Ini["DIR"]["LocDirInput"] = Path.Combine(Var.AppDir, "Data", "Input");
Ini["DIR"]["LocDirOperation"] = Path.Combine(Var.AppDir, "Data", "Operation");
Ini["DIR"]["LocDirScales"] = Path.Combine(Var.AppDir, "Data", "Scales");
Ini["DIR"]["LocDirExcel"] = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "BEB_Excel");
Ini["FTP"][";FtpDir_Input"] = @"ftp://192.168.1.100/SAMP04";
Ini["FTP"][";FtpDir_Operation"] = @"ftp://192.168.1.100/SAMP03";
Ini["FTP"]["FtpDir_Input"] = @"ftp://localhost/input/";
Ini["FTP"]["FtpDir_Operation"] = @"ftp://localhost/operation/";
Ini["FTP"]["FtpDir_Scales"] = @"ftp://localhost/scales/";
Ini["FTP"]["Username1"] = "ftpuser";
Ini["FTP"]["Password1"] = "ftpuser";
Ini["DB"]["DbPath"] = Path.Combine(Var.AppDir, "Konfig", "BEBDB.sqlite");
Ini["MODBUS"]["Com-Port"] = "COM1";
}
}
/* Download File */
public void Download(string remoteFile, string localFile)
{
try
{
/* Create an FTP Request */
ftpRequest = (FtpWebRequest)FtpWebRequest.Create(host + "/" + remoteFile);
/* Log in to the FTP Server with the User Name and Password Provided */
ftpRequest.Credentials = new NetworkCredential(user, pass);
/* When in doubt, use these options */
ftpRequest.UseBinary = true;
ftpRequest.UsePassive = true;
ftpRequest.KeepAlive = true;
/* Specify the Type of FTP Request */
ftpRequest.Method = WebRequestMethods.Ftp.DownloadFile;
/* Establish Return Communication with the FTP Server */
ftpResponse = (FtpWebResponse)ftpRequest.GetResponse();
/* Get the FTP Server's Response Stream */
ftpStream = ftpResponse.GetResponseStream();
/* Open a File Stream to Write the Downloaded File */
FileStream localFileStream = new FileStream(localFile, FileMode.Create);
/* Buffer for the Downloaded Data */
byte[] byteBuffer = new byte[bufferSize];
int bytesRead = ftpStream.Read(byteBuffer, 0, bufferSize);
/* Download the File by Writing the Buffered Data Until the Transfer is Complete */
try
{
while (bytesRead > 0)
{
localFileStream.Write(byteBuffer, 0, bytesRead);
bytesRead = ftpStream.Read(byteBuffer, 0, bufferSize);
}
}
catch (Exception ex) { IO.Log(2018061802, "ftp, download | " + ex.ToString()); }
/* Resource Cleanup */
localFileStream.Close();
ftpStream.Close();
ftpResponse.Close();
ftpRequest = null;
}
catch (Exception ex) { IO.Log(2018061803, "ftp, download | " + ex.ToString()); }
return;
}
/* Get the Size of a File */
public string GetFileSize(string fileName)
{
try
{
/* Create an FTP Request */
ftpRequest = (FtpWebRequest)FtpWebRequest.Create(host + "/" + fileName);
/* Log in to the FTP Server with the User Name and Password Provided */
ftpRequest.Credentials = new NetworkCredential(user, pass);
/* When in doubt, use these options */
ftpRequest.UseBinary = true;
ftpRequest.UsePassive = true;
ftpRequest.KeepAlive = true;
/* Specify the Type of FTP Request */
ftpRequest.Method = WebRequestMethods.Ftp.GetFileSize;
/* Establish Return Communication with the FTP Server */
ftpResponse = (FtpWebResponse)ftpRequest.GetResponse();
/* Establish Return Communication with the FTP Server */
ftpStream = ftpResponse.GetResponseStream();
/* Get the FTP Server's Response Stream */
StreamReader ftpReader = new StreamReader(ftpStream);
/* Store the Raw Response */
string fileInfo = null;
/* Read the Full Response Stream */
try { while (ftpReader.Peek() != -1)
{
fileInfo = ftpReader.ReadToEnd();
}
}
catch (Exception ex) { IO.Log(2018061806, "ftp, getFileSize | " + ex.ToString()); }
/* Resource Cleanup */
ftpReader.Close();
ftpStream.Close();
ftpResponse.Close();
ftpRequest = null;
/* Return File Size */
return(fileInfo);
}
catch (Exception ex) { IO.Log(2018061807, "ftp, getFileSize | " + ex.ToString()); }
/* Return an Empty string Array if an Exception Occurs */
return("");
}
public static async void LoadFtpFiles(IEnumerable <string> filesToLoad, string ftpDirSource, string locDirTarget, IProgress <int> progress1, IProgress <int> progress2, IProgress <int> progressIndex)
{
CancellationTokenSource cts = new CancellationTokenSource();
int i = 0;
int maxi = filesToLoad.Count();
foreach (string fileName in filesToLoad)
{
//Fortschrittsanzeige
if (progress1 != null)
{
progress1.Report(++i * 100 / maxi);
}
if (progressIndex != null)
{
progressIndex.Report(i);
}
//Wenn fileName leer, dann Schleife überspringen.
if (fileName.Length == 0)
{
continue;
}
Thread.Sleep(Var.SleepingMilliSec); // CPU-bound work
// Start asyn operation
Uri ftpSite = new Uri(Path.Combine(ftpDirSource, fileName));
string locFileTarget = Path.Combine(locDirTarget, fileName);
try
{
await Ftp.DownloadFileAsync(ftpSite, locFileTarget, progress2, cts.Token);
}
catch (Exception ex)
{
IO.Log(2018062005, ftpSite + " => " + locFileTarget + " | " + ex.Message);
}
}
}
/// <summary>
/// List die Dateinamen von CSV-Dateien aus locDirPath und gibt sie wenn sie nicht in der Datei locListFilePath sind als IEnumerable string aus.
/// </summary>
/// <param name="locDirPath">Pfad zum Ordner, der die Dateien enthält</param>
/// <param name="locListFilePath">Pfad zu Textdatei, die Ausnahmen enthält.</param>
/// <returns>Liste der CSV-Dateinamen aus locDirPath ohne die Ausnahmen aus locListFilePath</returns>
public static IEnumerable <string> ListFilesFromFolder(string locDirPath, string locListFilePath)
{
List <string> logFilesList = new List <string> {
""
};
if (File.Exists(locListFilePath))
{
// Read each line of the file into a string array. Each element of the array is one line of the file.
string[] lines = File.ReadAllLines(locListFilePath);
// Go through the file contents by using a foreach loop.
if (lines.Length > 0)
{
foreach (string line in lines)
{
logFilesList.Add(Path.Combine(locDirPath, line.Trim()));
}
}
}
else
{
IO.Log(2018062206, "Datei '" + locListFilePath + "' existiert nicht.");
logFilesList = new List <string>()
{
""
};
}
try
{
//Lese das Verzeichnis, schließe Ausnahmen aus.
return(Directory.EnumerateFiles(locDirPath, "*.csv").Except(logFilesList));
}
catch (Exception ex)
{
IO.Log(2018062207, ex.Message + " | " + ex.Source);
return(null);
}
}
public MainWindow()
{
// Inhalt der Klasse Var für XAML berietstellen
DataContext = var;
// Programm initialisieren
InitializeComponent();
// Variablen, Pfade usw. initialisieren
InitializeApp();
IO.Log(2018062209, "Anwendung wurde gestartet.");
//Prüfe die ausgelesenen lokalen Pfade und zeige grün wenn sie existieren, sonst rot
ValidateLocalPaths();
DispatcherTimer timer = new DispatcherTimer();
timer.Tick += (TimerTick);
timer.Start();
datePickerBis.SelectedDate = DateTime.Now.AddDays(1 - DateTime.Now.Day).AddMonths(1);
//Fenstergröße
Height = (int)System.Windows.SystemParameters.PrimaryScreenHeight * 0.9;
Width = (int)System.Windows.SystemParameters.PrimaryScreenWidth * 0.5;
}
/// <summary>
/// Prüft ein FTP-Verzeichnis und gibt Grün wieder, wenn vorhadnen, sonst rot.
/// </summary>
/// <param name="dirPath"></param>
/// <returns></returns>
public static System.Windows.Media.Brush DoesFtpDirectoryExist(string dirPath)
{
try
{
FtpWebRequest request = (FtpWebRequest)WebRequest.Create(dirPath);
request.Credentials = new NetworkCredential(Var.FtpUsername1, Var.FtpPassword1);
request.Timeout = 4000;
request.Method = WebRequestMethods.Ftp.ListDirectory;
FtpWebResponse response = (FtpWebResponse)request.GetResponse();
return(Brushes.Green);
}
catch (WebException ex)
{
IO.Log(2018061814, "DoesFtpDirectoryExist() | '" + dirPath + "' existiert nicht. | " + ex.Message);
return(Brushes.Red);
}
catch (Exception ex)
{
IO.Log(2018062003, "DoesFtpDirectoryExist() | '" + dirPath + "' existiert nicht. | " + ex.GetType() + " | " + ex.Message);
return(Brushes.Red);
}
}
//Calling the method:
//string ftpDirectory = "ftp://ftpserver.com/rootdir/test_if_exist_directory/"; //Note: backslash at the last position of the path.
//bool dirExists = DoesFtpDirectoryExist(ftpDirectory);
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// von http://csharp-blog.de/2015/11/c-asyncawait-fortschritts-benachrichtigung-und-abbruch-moeglichkeit/
public static async Task DownloadFileAsync(Uri ftpSite, string targetPath, IProgress <int> onProgressChanged, CancellationToken token)
{
try
{
//File.Create(Path.Combine(targetPath));
int totalBytes = 0;
long fileSize = 0;
Thread.Sleep(Var.SleepingMilliSec);
FtpWebRequest requestS = (FtpWebRequest)WebRequest.Create(ftpSite);
requestS.Credentials = new NetworkCredential(Var.FtpUsername1, Var.FtpPassword1);
requestS.Method = WebRequestMethods.Ftp.GetFileSize;
using (FtpWebResponse response = (FtpWebResponse)await requestS.GetResponseAsync())
{
fileSize = response.ContentLength;
response.Close();
Thread.Sleep(Var.SleepingMilliSec);
}
FtpWebRequest request = (FtpWebRequest)WebRequest.Create(ftpSite);
request.Credentials = new NetworkCredential(Var.FtpUsername1, Var.FtpPassword1);
request.Method = WebRequestMethods.Ftp.DownloadFile;
using (FtpWebResponse response = (FtpWebResponse)await request.GetResponseAsync())
{
Stream data = response.GetResponseStream();
//Console.WriteLine($"Downloading {ftpSite.AbsoluteUri} to {targetPath}...");
IO.Log(2018061904, $"Starte Download von {ftpSite.AbsoluteUri} nach {targetPath}");
byte[] byteBuffer = new byte[4096];
using (FileStream output = new FileStream(targetPath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite, 4096, useAsync: true))
{
int bytesRead = 1;
while (bytesRead > 0)
{
bytesRead = await data.ReadAsync(byteBuffer, 0, byteBuffer.Length);
if (bytesRead > 0)
{
totalBytes += bytesRead;
// Report progress
int locProgress = Convert.ToInt32(totalBytes * 100 / fileSize);
onProgressChanged?.Report(locProgress);
// Upload
await output.WriteAsync(byteBuffer, 0, bytesRead, token);
}
}
}
//Console.WriteLine($"Downloaded {ftpSite.AbsoluteUri} to {targetPath}");
IO.Log(2018061905, $"Download von {ftpSite.AbsoluteUri} nach {targetPath} war erfolgreich.");
}
}
catch (OperationCanceledException)
{
//Console.Write("Download cancelled!");
IO.Log(2018061906, "Download abgebrochen.");
}
catch (WebException e)
{
//Console.WriteLine($"Failed to download {ftpSite.AbsoluteUri} to {targetPath}");
//Console.WriteLine(e);
IO.Log(2018061907, $"Download fehlgeschlagen: {ftpSite.AbsoluteUri} nach {targetPath} : " + e.Message);
throw;
}
}
/// aus BEB_csharp03
///
/// <summary>
/// Schreibt die Tabelle dt in eine Excel - Datei.
/// </summary>
/// <param name="sheetTitle">Blatttitel</param>
/// <param name="dt">Tabelle als Datenquelle</param>
/// <param name="excelDirPath">Ordner, in den die Excel-Datei gespeichert werdne soll.</param>
public static void ExportToExcel(DataTable dt, string excelFilePath)
{
string rootDir = Var.LocDirExcel;
string sheetTitle = "Blatt";
if (dt.TableName.Length < 3)
{
//Arbeitsblattname = 2 Reihe, 1 Spalte aus dt
sheetTitle = dt.Rows[1][dt.Columns[0]].ToString().Substring(0, 10);
}
else
{
sheetTitle = dt.TableName;
}
/*Set up work book, work sheets, and excel application*/
Excel.Application oexcel = null;
try
{
oexcel = new Excel.Application();
}
catch
{
IO.Log(2018062901, "Excel ist nicht richtig installiert!!");
return;
}
oexcel.DisplayAlerts = false;
try
{
object misValue = System.Reflection.Missing.Value;
Excel.Workbook obook;
try
{
//Versuche die Excel-Datei zu öffnen
obook = oexcel.Workbooks.Open(excelFilePath);
}
catch
{
//Öffne ein neues Workbook
obook = oexcel.Workbooks.Add(misValue);
}
Excel.Worksheet osheet = new Excel.Worksheet();
osheet = obook.Worksheets.Add(misValue);
//osheet = obook.Worksheets.get_Item(1);
osheet.Name = sheetTitle;
int colIndex = 0;
int rowIndex = 1;
//Spaltenüberschriften
foreach (DataColumn dc in dt.Columns)
{
colIndex++;
osheet.Cells[1, colIndex] = dc.ColumnName;
}
// Erste Reihe Fett darstellen.
osheet.Cells[1, 1].EntireRow.Font.Bold = true;
//Tabelleninhalt
foreach (DataRow dr in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn dc in dt.Columns)
{
colIndex++;
osheet.Cells[rowIndex, colIndex] = dr[dc.ColumnName];
}
}
// letzte Reihe Fett darstellen.
osheet.Cells[rowIndex, 1].EntireRow.Font.Bold = true;
osheet.Columns.AutoFit();
obook.SaveAs(excelFilePath);
obook.Close();
oexcel.Quit();
ReleaseObject(osheet);
ReleaseObject(obook);
ReleaseObject(oexcel);
GC.Collect();
}
catch (Exception ex)
{
oexcel.Quit();
IO.Log(2018062902, ex.GetType() + " | " + ex.Message + " | " + ex.InnerException);
}
}
public static void WriteToDb_Input(string sourceFilePath, IProgress <int> progress)
{
bool keinFehler = true;
try
{
Sql sql = new Sql(Var.DbPath);
int lineInFile = 0;
//nur die Zahl aus dem Dateinamen verwenden
string dateiname = Path.GetFileNameWithoutExtension(sourceFilePath).Substring(2);
// Quelle: https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/file-system/how-to-read-from-a-text-file
// Read each line of the file into a string array. Each element of the array is one line of the file.
//string[] lines = File.ReadAllLines(sourceFilePath); // @"C:\Users\Public\TestFolder\WriteLines2.txt"
var reader = File.OpenText(sourceFilePath);
var fileText = reader.ReadToEnd();
string[] lines = fileText.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
int totLines = lines.Count();
// Go through the file contents by using a foreach loop.
foreach (string line in lines)
{
++lineInFile;
if (progress != null)
{
progress.Report(lineInFile);
}
if (line.Length < 2)
{
continue;
}
string[] items = line.Replace("\"", "").Split(','); // Entferne Anführungszeichen, teile beim ','
// Wenn das erste Zeichen keine Zahl ist: Überspringe die Zeile.
if (!char.IsNumber(items[0].ToCharArray()[0]))
{
continue;
}
string zeit = "20" + items[0].Replace('/', '-') + " " + items[1];
int annahme = 0, annahme1 = 0, annahme2 = 0;
int feststoff = 0, feststoff1 = 0, feststoff2 = 0;
if (int.TryParse(items[2], out annahme1))
{
annahme += annahme1;
}
if (int.TryParse(items[3], out annahme2))
{
annahme += annahme2;
}
//if (int.TryParse(items[6], out annahme3)) annahme += annahme3; // Gülle in Nachgärer gepumpt.
if (int.TryParse(items[4], out feststoff1))
{
feststoff += feststoff1;
}
if (int.TryParse(items[5], out feststoff2))
{
feststoff += feststoff2;
}
Dictionary <string, string> data = new Dictionary <string, string>
{
{ "Zeit", zeit },
{ "Datei", dateiname + "-" + lineInFile.ToString("D3") },
{ "Annahme", annahme.ToString() },
{ "Feststoff", feststoff.ToString() }
};
if (Var.CheckforDuplicateInDb)
{
// nur eintragen, wenn Zeitstempel noch nicht vorhanden?
if (sql.ExecuteScalar("SELECT 1 FROM Input WHERE Zeit ='" + zeit + "'").Length > 0)
{
continue;
}
}
try
{
if (!sql.Insert("Input", data))
{
keinFehler = false;
IO.Log(2018062208, "Die Zeile " + lineInFile.ToString() + " aus Datei '" + sourceFilePath + "' konnte nicht in die Datenbank übernommen werden.");
}
}
catch (Exception exception)
{
IO.Log(2018062202, "Items.Lenght:" + items.Length + " | " + exception.Message + " | " + exception.InnerException + " | " + exception.Source);
}
}
//Dateinamen in Protokolldatei anhängen.
if (keinFehler)
{
using (StreamWriter sw = File.AppendText(Var.ArchiveFile_Input))
{
sw.WriteLine(Path.GetFileName(sourceFilePath));
}
}
}
catch (FieldAccessException exception)
{
IO.Log(2018062204, "FieldAccessException: " + exception.Message + "\r\n" + exception.InnerException);
}
catch (IndexOutOfRangeException exception)
{
IO.Log(2018062205, exception.Message + " | Datei: '" + sourceFilePath + "' entspricht nicht dem erwarteten Schema.");
}
catch (Exception exception)
{
IO.Log(2018062203,
"Datei: " + sourceFilePath + "\r\n" +
exception.Message + "\r\n" +
exception.GetType() + "\r\n" +
exception.InnerException);
}
}
public static void WriteToDb_Operation(string sourceFilePath, IProgress <int> progress)
{
bool keinFehler = true;
try
{
Sql sql = new Sql(Var.DbPath);
int lineInFile = 0;
//nur die Zahl aus dem Dateinamen verwenden
string dateiname = Path.GetFileNameWithoutExtension(sourceFilePath).Substring(2);
// Quelle: https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/file-system/how-to-read-from-a-text-file
// Read each line of the file into a string array. Each element of the array is one line of the file.
var reader = File.OpenText(sourceFilePath);
var fileText = reader.ReadToEnd();
string[] lines = fileText.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
int totLines = lines.Count();
// Go through the file contents by using a foreach loop.
foreach (string line in lines)
{
++lineInFile;
if (progress != null)
{
progress.Report(lineInFile);
}
if (line.Length < 2)
{
continue;
}
string[] items = line.Replace("\"", "").Split(','); // Entferne Anführungszeichen, teile beim ','
// Wenn das erste Zeichen keine Zahl ist: Überspringe die Zeile.
if (!char.IsNumber(items[0].ToCharArray()[0]))
{
continue;
}
string zeit = "20" + items[0].Replace('/', '-') + " " + items[1];
Dictionary <string, string> data = new Dictionary <string, string>
{
{ "Zeit", zeit },
{ "Gasfüllstand_ges", items[2] },
{ "Gasfüllstand_F1", items[3] },
{ "Gasfüllstand_F2", items[4] },
{ "Gasfüllstand_NG", items[5] },
{ "Gasfüllstand_GPL", items[6] },
{ "Gasanalyse_CH4", items[7] },
{ "Gasanalyse_O2", items[8] },
{ "Gasanalyse_H2S", items[9] },
{ "BHKW_kW", items[10] },
{ "Temp_F1", items[12] },
{ "Temp_F2", items[13] },
{ "Temp_NG", items[14] }
};
if (Var.CheckforDuplicateInDb)
{
// nur eintragen, wenn Zeitstempel noch nicht vorhanden?
if (sql.ExecuteScalar("SELECT 1 FROM Operation WHERE Zeit ='" + zeit + "'").Length > 0)
{
continue;
}
}
try
{
if (!sql.Insert("Operation", data))
{
keinFehler = false;
IO.Log(2018062208, "Die Zeile " + lineInFile.ToString() + " aus Datei '" + sourceFilePath + "' konnte nicht in die Datenbank übernommen werden.");
}
}
catch (Exception exception)
{
IO.Log(2018062701, "Items.Lenght:" + items.Length + " | " + exception.Message + " | " + exception.InnerException + " | " + exception.Source);
}
}
//Dateinamen in Protokolldatei anhängen.
if (keinFehler)
{
using (StreamWriter sw = File.AppendText(Var.ArchiveFile_Operation))
{
sw.WriteLine(Path.GetFileName(sourceFilePath));
}
}
}
catch (FieldAccessException exception)
{
IO.Log(2018062702, "FieldAccessException: " + exception.Message + "\r\n" + exception.InnerException);
}
catch (IndexOutOfRangeException exception)
{
IO.Log(2018062703, exception.Message + " | Datei: '" + sourceFilePath + "' entspricht nicht dem erwarteten Schema.");
}
catch (Exception exception)
{
IO.Log(2018062704,
"Datei: " + sourceFilePath + "\r\n" +
exception.Message + "\r\n" +
exception.GetType() + "\r\n" +
exception.InnerException);
}
}
public static void WriteToDb_Scales(string sourceFilePath, IProgress <int> progress)
{
bool keinFehler = true;
try
{
Sql sql = new Sql(Var.DbPath);
int lineInFile = 0;
//nur die Zahl aus dem Dateinamen verwenden
string dateiname = Path.GetFileNameWithoutExtension(sourceFilePath);
// Quelle: https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/file-system/how-to-read-from-a-text-file
// Read each line of the file into a string array. Each element of the array is one line of the file.
StreamReader reader = new StreamReader(sourceFilePath, Encoding.Default);
var fileText = reader.ReadToEnd();
IO.Log(123456, fileText);
string[] lines = fileText.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
int totLines = lines.Count();
// Go through the file contents by using a foreach loop.
foreach (string line in lines)
{
++lineInFile;
if (progress != null)
{
progress.Report(lineInFile);
}
if (line.Length < 2)
{
continue;
}
string[] items = line.Replace("\"", "").Split(';'); // Entferne Anführungszeichen, teile beim ','
// Wenn das erste Zeichen keine Zahl ist: Überspringe die Zeile.
if (!char.IsNumber(items[0].ToCharArray()[0]))
{
continue;
}
string[] datum = items[19].Split('.');
string zeit = "20" + datum[2].ToString() + "-" + datum[1].ToString() + "-" + datum[0].ToString() + " " + items[20];
Dictionary <string, string> data = new Dictionary <string, string>
{
{ "LfdNr", items[0] },
{ "Datei", dateiname },
{ "SchlagNr", items[2] },
{ "Sorte", items[11].Trim() },
{ "Nettogewicht", items[13] },
{ "Kunde_Name", items[8].Trim() },
{ "Kennzeichen", items[3].Trim() },
{ "Erst_Gewicht", items[17] },
{ "Tara_Hand", items[18].ToString() },
{ "Zweit_Gewicht", items[21] },
{ "Zeit", zeit }
};
/*
* [Scales] (
* [LfdNr] INTEGER NOT NULL PRIMARY KEY,
* [Datei] TEXT NOT NULL,
* [SchlagNr] INT,
* [Sorte] TEXT NOT NULL,
* [Nettogewicht] INT,
* [Kunde_Name] TEXT NOT NULL,
* [Erst_Gewicht] INT,
* [Tara_Hand] TEXT,
* [Zweit_Gewicht] INT,
* [Zeit] TEXT NOT NULL
* )");
*/
if (Var.CheckforDuplicateInDb)
{
// nur eintragen, wenn Zeitstempel noch nicht vorhanden?
if (sql.ExecuteScalar("SELECT 1 FROM Operation WHERE Zeit ='" + zeit + "'").Length > 0)
{
continue;
}
}
try
{
if (!sql.Insert("Scales", data))
{
keinFehler = false;
IO.Log(2018062908, "Die Zeile " + lineInFile.ToString() + " aus Datei '" + sourceFilePath + "' konnte nicht in die Datenbank übernommen werden.");
}
}
catch (Exception exception)
{
IO.Log(2018062909, "Items.Lenght:" + items.Length + " | " + exception.Message + " | " + exception.InnerException + " | " + exception.Source);
}
}
//Dateinamen in Protokolldatei anhängen.
if (keinFehler)
{
using (StreamWriter sw = File.AppendText(Var.ArchiveFile_Scales))
{
sw.WriteLine(Path.GetFileName(sourceFilePath));
}
}
}
catch (FieldAccessException exception)
{
IO.Log(2018062910, "FieldAccessException: " + exception.Message + "\r\n" + exception.InnerException);
}
catch (IndexOutOfRangeException exception)
{
IO.Log(2018062911, exception.Message + " | Datei: '" + sourceFilePath + "' entspricht nicht dem erwarteten Schema.");
}
catch (Exception exception)
{
IO.Log(2018062912,
"Datei: " + sourceFilePath + "\r\n" +
exception.Message + "\r\n" +
exception.GetType() + "\r\n" +
exception.InnerException);
}
}
