/// <summary>Write the .csv or .xlsx file and maybe send an open command to the system</summary>
private void MenuFileExportSpreadsheet_Click(object sender, RoutedEventArgs e)
{
MenuItem menuItem = (MenuItem)sender;
bool exportXlsx = (sender == this.MenuFileExportXlsxAndOpen) || (sender == this.MenuFileExportXlsx);
bool openFile = (sender == this.MenuFileExportXlsxAndOpen) || (sender == this.MenuFileExportCsvAndOpen);
// backup any existing file as it's overwritten on export
string spreadsheetFileExtension = exportXlsx ? Constant.File.ExcelFileExtension : Constant.File.CsvFileExtension;
string spreadsheetFileName = Path.GetFileNameWithoutExtension(this.dataHandler.FileDatabase.FileName) + spreadsheetFileExtension;
string spreadsheetFilePath = Path.Combine(this.FolderPath, spreadsheetFileName);
if (FileBackup.TryCreateBackup(this.FolderPath, spreadsheetFileName))
{
this.statusBar.SetMessage("Backup of {0} made.", spreadsheetFileName);
}
SpreadsheetReaderWriter spreadsheetWriter = new SpreadsheetReaderWriter();
try
{
if (exportXlsx)
{
spreadsheetWriter.ExportFileDataToXlsx(this.dataHandler.FileDatabase, spreadsheetFilePath);
}
else
{
spreadsheetWriter.ExportFileDataToCsv(this.dataHandler.FileDatabase, spreadsheetFilePath);
}
this.statusBar.SetMessage("Data exported to " + spreadsheetFileName);
if (openFile)
{
// show the exported file in whatever program is associated with its extension
Process process = new Process();
process.StartInfo.UseShellExecute = true;
process.StartInfo.RedirectStandardOutput = false;
process.StartInfo.FileName = spreadsheetFilePath;
process.Start();
}
}
catch (IOException exception)
{
MessageBox messageBox = new MessageBox("Can't write the spreadsheet file.", this);
messageBox.Message.StatusImage = MessageBoxImage.Error;
messageBox.Message.Problem = "The following file can't be written: " + spreadsheetFilePath;
messageBox.Message.Reason = "You may already have it open in Excel or another application.";
messageBox.Message.Solution = "If the file is open in another application, close it and try again.";
messageBox.Message.Hint = String.Format("{0}: {1}", exception.GetType().FullName, exception.Message);
messageBox.ShowDialog();
return;
}
}
private async void MenuFileImportSpreadsheet_Click(object sender, RoutedEventArgs e)
{
if (this.state.SuppressSpreadsheetImportPrompt == false)
{
MessageBox messageBox = new MessageBox("How importing spreadsheet data works.", this, MessageBoxButton.OKCancel);
messageBox.Message.What = "Importing data from .csv (comma separated value) and .xslx (Excel) files follows the rules below.";
messageBox.Message.Reason = "Carnassial requires the file follow a specific format and processes its data in a specific way.";
messageBox.Message.Solution = "Modifying and importing a spreadsheet is supported only if the file is exported from and then back into image set with the same template." + Environment.NewLine;
messageBox.Message.Solution += "A limited set of modifications is allowed:" + Environment.NewLine;
messageBox.Message.Solution += "\u2022 Counter data must be zero or a positive integer." + Environment.NewLine;
messageBox.Message.Solution += "\u2022 Flag data must be 'true' or 'false', case insensitive." + Environment.NewLine;
messageBox.Message.Solution += "\u2022 FixedChoice data must be a string that exactly matches one of the FixedChoice menu options or the field's default value." + Environment.NewLine;
messageBox.Message.Solution += String.Format("\u2022 DateTime must be in '{0}' format.{1}", Constant.Time.DateTimeDatabaseFormat, Environment.NewLine);
messageBox.Message.Solution += String.Format("\u2022 UtcOffset must be a floating point number between {0} and {1}, inclusive.{2}", DateTimeHandler.ToDatabaseUtcOffsetString(Constant.Time.MinimumUtcOffset), DateTimeHandler.ToDatabaseUtcOffsetString(Constant.Time.MinimumUtcOffset), Environment.NewLine);
messageBox.Message.Solution += "Changing these things either doesn't work or is best done with care:" + Environment.NewLine;
messageBox.Message.Solution += "\u2022 FileName and RelativePath identify the file updates are applied to. Changing them causes a different file to be updated or a new file to be added." + Environment.NewLine;
messageBox.Message.Solution += "\u2022 RelativePath is interpreted relative to the spreadsheet file. Make sure it's in the right place!" + Environment.NewLine;
messageBox.Message.Solution += "\u2022 Column names can be swapped to assign data to different fields." + Environment.NewLine;
messageBox.Message.Solution += "\u2022 Adding, removing, or otherwise changing columns is not supported." + Environment.NewLine;
messageBox.Message.Solution += String.Format("\u2022 Using a worksheet with a name other than '{0}' in .xlsx files is not supported.{1}", Constant.Excel.FileDataWorksheetName, Environment.NewLine);
messageBox.Message.Result = String.Format("Carnassial will create a backup .ddb file in the {0} folder and then import as much data as it can. If data can't be imported you'll get a dialog listing the problems.", Constant.File.BackupFolder);
messageBox.Message.Hint = "\u2022 After you import, check your data. If it is not what you expect, restore your data by using that backup file." + Environment.NewLine;
messageBox.Message.Hint += String.Format("\u2022 Usually the spreadsheet should be in the same folder as the data file ({0}) it was exported from.{1}", Constant.File.FileDatabaseFileExtension, Environment.NewLine);
messageBox.Message.Hint += "\u2022 If you check 'Don't show this message' this dialog can be turned back on via the Options menu.";
messageBox.Message.StatusImage = MessageBoxImage.Information;
messageBox.DontShowAgain.Visibility = Visibility.Visible;
bool? proceeed = messageBox.ShowDialog();
if (proceeed != true)
{
return;
}
if (messageBox.DontShowAgain.IsChecked.HasValue)
{
this.state.SuppressSpreadsheetImportPrompt = messageBox.DontShowAgain.IsChecked.Value;
this.MenuOptionsEnableCsvImportPrompt.IsChecked = !this.state.SuppressSpreadsheetImportPrompt;
}
}
string defaultSpreadsheetFileName = Path.GetFileNameWithoutExtension(this.dataHandler.FileDatabase.FileName) + Constant.File.ExcelFileExtension;
string spreadsheetFilePath;
if (Utilities.TryGetFileFromUser("Select a file to merge into the current image set",
Path.Combine(this.dataHandler.FileDatabase.FolderPath, defaultSpreadsheetFileName),
String.Format("Spreadsheet files (*{0};*{1})|*{0};*{1}", Constant.File.CsvFileExtension, Constant.File.ExcelFileExtension),
out spreadsheetFilePath) == false)
{
return;
}
// Create a backup database file
if (FileBackup.TryCreateBackup(this.dataHandler.FileDatabase.FilePath))
{
this.statusBar.SetMessage("Backup of data file made.");
}
else
{
this.statusBar.SetMessage("No data file backup was made.");
}
SpreadsheetReaderWriter spreadsheetReader = new SpreadsheetReaderWriter();
try
{
List<string> importErrors;
bool importSuccededFully;
if (String.Equals(Path.GetExtension(spreadsheetFilePath), Constant.File.ExcelFileExtension, StringComparison.OrdinalIgnoreCase))
{
importSuccededFully = spreadsheetReader.TryImportFileDataFromXlsx(spreadsheetFilePath, this.dataHandler.FileDatabase, out importErrors);
}
else
{
importSuccededFully = spreadsheetReader.TryImportFileDataFromCsv(spreadsheetFilePath, this.dataHandler.FileDatabase, out importErrors);
}
if (importSuccededFully == false)
{
MessageBox messageBox = new MessageBox("Spreadsheet import incomplete.", this);
messageBox.Message.StatusImage = MessageBoxImage.Error;
messageBox.Message.Problem = String.Format("The file {0} could not be fully read.", spreadsheetFilePath);
messageBox.Message.Reason = "The spreadsheet is not fully compatible with the current image set.";
messageBox.Message.Solution = "Check that:" + Environment.NewLine;
messageBox.Message.Solution += "\u2022 The first row of the file is a header line." + Environment.NewLine;
messageBox.Message.Solution += "\u2022 The column names in the header line match the database." + Environment.NewLine;
messageBox.Message.Solution += "\u2022 Choice values use the correct case." + Environment.NewLine;
messageBox.Message.Solution += "\u2022 Counter values are numbers." + Environment.NewLine;
messageBox.Message.Solution += "\u2022 Flag values are either 'true' or 'false'.";
messageBox.Message.Result = "Either no data was imported or invalid parts of the spreadsheet were skipped.";
messageBox.Message.Hint = "The errors encountered were:";
foreach (string importError in importErrors)
{
messageBox.Message.Hint += "\u2022 " + importError;
}
messageBox.ShowDialog();
}
}
catch (Exception exception)
{
MessageBox messageBox = new MessageBox("Can't import the .csv file.", this);
messageBox.Message.StatusImage = MessageBoxImage.Error;
messageBox.Message.Problem = String.Format("The file {0} could not be opened.", spreadsheetFilePath);
messageBox.Message.Reason = "Most likely the file is open in another program.";
messageBox.Message.Solution = "If the file is open in another program, close it.";
messageBox.Message.Result = String.Format("{0}: {1}", exception.GetType().FullName, exception.Message);
messageBox.Message.Hint = "Is the file open in Excel?";
messageBox.ShowDialog();
}
// reload the file data table and update the enable/disable state of the user interface to match
await this.SelectFilesAndShowFileAsync();
await this.EnableOrDisableMenusAndControlsAsync();
this.statusBar.SetMessage(".csv file imported.");
}
public void RoundtripSpreadsheets()
{
foreach (string spreadsheetFileExtension in new List<string>() { Constant.File.CsvFileExtension, Constant.File.ExcelFileExtension })
{
bool xlsx = spreadsheetFileExtension == Constant.File.ExcelFileExtension;
// create database, push test images into the database, and load the image data table
FileDatabase fileDatabase = this.CreateFileDatabase(TestConstant.File.DefaultTemplateDatabaseFileName, TestConstant.File.DefaultNewFileDatabaseFileName);
List<FileExpectations> fileExpectations = this.PopulateDefaultDatabase(fileDatabase);
// roundtrip data
SpreadsheetReaderWriter readerWriter = new SpreadsheetReaderWriter();
string initialFilePath = this.GetUniqueFilePathForTest(Path.GetFileNameWithoutExtension(Constant.File.DefaultFileDatabaseFileName) + spreadsheetFileExtension);
if (xlsx)
{
readerWriter.ExportFileDataToXlsx(fileDatabase, initialFilePath);
}
else
{
readerWriter.ExportFileDataToCsv(fileDatabase, initialFilePath);
}
List<string> importErrors;
if (xlsx)
{
Assert.IsTrue(readerWriter.TryImportFileDataFromXlsx(initialFilePath, fileDatabase, out importErrors));
}
else
{
Assert.IsTrue(readerWriter.TryImportFileDataFromCsv(initialFilePath, fileDatabase, out importErrors));
}
Assert.IsTrue(importErrors.Count == 0);
// verify File table content hasn't changed
TimeZoneInfo imageSetTimeZone = fileDatabase.ImageSet.GetTimeZone();
for (int fileIndex = 0; fileIndex < fileExpectations.Count; ++fileIndex)
{
ImageRow file = fileDatabase.Files[fileIndex];
FileExpectations fileExpectation = fileExpectations[fileIndex];
fileExpectation.Verify(file, imageSetTimeZone);
}
// verify consistency of .csv export
string roundtripFilePath = Path.Combine(Path.GetDirectoryName(initialFilePath), Path.GetFileNameWithoutExtension(initialFilePath) + ".Roundtrip" + spreadsheetFileExtension);
if (xlsx)
{
readerWriter.ExportFileDataToXlsx(fileDatabase, roundtripFilePath);
}
else
{
readerWriter.ExportFileDataToCsv(fileDatabase, roundtripFilePath);
}
if (xlsx == false)
{
// check .csv content is identical
// For .xlsx this isn't meaningful as file internals can change.
string initialFileContent = File.ReadAllText(initialFilePath);
string roundtripFileContent = File.ReadAllText(roundtripFilePath);
Assert.IsTrue(initialFileContent == roundtripFileContent, "Initial and roundtrip {0} files don't match.", spreadsheetFileExtension);
}
// merge and refresh in memory table
int filesBeforeMerge = fileDatabase.CurrentlySelectedFileCount;
string mergeFilePath = Path.Combine(Path.GetDirectoryName(initialFilePath), Path.GetFileNameWithoutExtension(initialFilePath) + ".FilesToMerge" + spreadsheetFileExtension);
if (xlsx)
{
Assert.IsTrue(readerWriter.TryImportFileDataFromXlsx(mergeFilePath, fileDatabase, out importErrors));
}
else
{
Assert.IsTrue(readerWriter.TryImportFileDataFromCsv(mergeFilePath, fileDatabase, out importErrors));
}
Assert.IsTrue(importErrors.Count == 0);
fileDatabase.SelectFiles(FileSelection.All);
Assert.IsTrue(fileDatabase.CurrentlySelectedFileCount - filesBeforeMerge == 2);
// verify merge didn't affect existing File table content
for (int fileIndex = 0; fileIndex < fileExpectations.Count; ++fileIndex)
{
ImageRow file = fileDatabase.Files[fileIndex];
FileExpectations fileExpectation = fileExpectations[fileIndex];
fileExpectation.Verify(file, imageSetTimeZone);
}
}
}
