/// <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); } } }