1. Overview
I needed to prepare several hundred Excel sheets containing excerpts from log files in text format, and I created a program in C# that reads the text files and copies portions of them on the Excel sheets.
The program will make copies of Excel sheets in a given format and put strings read from log files in some cells.
The program was created using NPOI 2.7.1, which allows manipulation of Excel files in C#.
The program created at this time has been slightly modified and uploaded to the link below.
https://github.com/fukagai-takuya/wpf-handle-excel
I had used Prism to program with MVVM, but this time I implemented data binding between strings and commands without Prism to check its internal processing.
I wanted to avoid using Prism in combination with the Material Design In XAML Toolkit, which requires caution, which is another reason I did not use Prism this time.
2. Contents of the prepared program
The contents of the prepared program are described below.
2.1. Open the solution in Visual Studio 2022
Open the following file of the downloaded program with Visual Studio 2022.
./HandlingExcelWithWpf/HandlingExcelWithWpf.sln
2.2. Build and Launch
When the program is built and executed, the screen shown in the image below will be displayed.
2.3. Select sample files and generate output files
Click “Select a format Excel file” button and select the following Excel file.
./HandlingExcelWithWpf/HandlingExcelWithWpf/SampleFiles/SampleExcelFile.xlsx
This file is an Excel file with a single sheet SampleSheet as shown in the image below.
Click “Select a log file” button and select the text file below.
./HandlingExcelWithWpf/HandlingExcelWithWpf/SampleFiles/SampleTextFile.txt
This file is a text file with the following contents.
2024/09/01 push-up:75 sit-up:60 chin-up:30 note:Same as usual 2024/09/02 push-up:75 sit-up:60 chin-up:30 note:Same as usual 2024/09/03 push-up:80 sit-up:70 chin-up:30 note:Good condition 2024/09/04 push-up:75 sit-up:60 chin-up:26 note:Same as usual 2024/09/05 push-up:75 sit-up:60 chin-up:30 note:Same as usual
Click “Select an output folder” button and select the directory where the Excel file to be created will be saved.
After selecting files and directories, “Generate an Excel file” button will be enabled, as shown in the image below.
Click on “Generate an Excel file” button to create an Excel file; once the Excel file is created, a message box will appear as shown in the image below.
An Excel file with the following name will be created in the destination folder.
/c/dev/tmp/OutputSheets2024-09-01-20-52-27.xlsx
This file is an Excel file showing the contents read from the text file. It is an Excel file consisting of 5 sheets.
3. Implementation Details
3.1. File handling with NPOI 2.7.1
NPOI 2.7.1 methods are called in the following method of GenerateExcelFileCommand, which is executed when the “Generate an Excel file button” is clicked.
The method OutputExcelFile() creates an instance of Excel Book based on the Excel file selected by clicking the “Select a format Excel file” button. For the created Excel Book instance, data is repeatedly written to the Sheet and the Sheet is duplicated. Finally, the contents of the Excel Book instance are written out as a new Excel file.
The method WriteLogToSheet(ISheet sheet, List<string> logLines) outputs the contents read from a text file to cells in an Excel Sheet.
private void OutputExcelFile()
{
// Read Excel book
IWorkbook book = WorkbookFactory.Create(_MainWindowViewModel.SelectedFormatFileName);
// Get Excel sheet with sheet name
ISheet sheet = book.GetSheet(_SheetName);
int sheetNum = 1;
StreamReader sr = new StreamReader(_MainWindowViewModel.SelectedLogFileName);
var logLines = new List<string>();
while (true)
{
// Read the next line
var line = sr.ReadLine();
if (line == null || line.Trim().Length == 0)
{
if (sheetNum > 1)
{
// Prepare a new Excel sheet by copying a current sheet
sheet = sheet.CopySheet($"{_SheetName}({sheetNum})");
}
WriteLogToSheet(sheet, logLines);
sheetNum++;
if (line == null)
{
break;
}
}
else
{
logLines.Add(line);
}
}
// close the file
sr.Close();
DateTime dateTime = DateTime.Now;
string dateTimeStr = dateTime.ToString("yyyy-MM-dd-HH-mm-ss");
string outputFileName = $"{_MainWindowViewModel.SelectedOutputDirName}\\OutputSheets{dateTimeStr}.xlsx";
using(var fs = new FileStream(outputFileName, FileMode.Create))
{
book.Write(fs);
}
MessageWindow.Show("Finished");
}
private void WriteCell(ISheet sheet, int rowIndex, int columnIndex, string outputString)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
var cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex);
cell.CellStyle.WrapText = true;
cell.SetCellValue(outputString);
}
private Regex _RegDate = new Regex(@"(\d{4}/\d{2}/\d{2})");
private Regex _RegItem = new Regex(@"([-a-z]+):(.+)");
private void WriteLogToSheet(ISheet sheet, List<string> logLines)
{
foreach (var line in logLines)
{
var match = _RegDate.Match(line);
if (match.Success)
{
var dateStr = match.Groups[1].Value.Trim();
WriteCell(sheet, 2, 1, dateStr);
continue;
}
match = _RegItem.Match(line);
if (match.Success)
{
var keyStr = match.Groups[1].Value.Trim();
var valueStr = match.Groups[2].Value.Trim();
if (keyStr == "push-up")
{
WriteCell(sheet, 5, 1, valueStr);
}
else if (keyStr == "sit-up")
{
WriteCell(sheet, 8, 1, valueStr);
}
else if (keyStr == "chin-up")
{
WriteCell(sheet, 11, 1, valueStr);
}
else if (keyStr == "note")
{
WriteCell(sheet, 14, 1, valueStr);
}
}
}
logLines.Clear();
}
3.2. Updating the data-bound strings
An example of the strings bound to TextBox.Text in MainWindow.xaml.
<TextBox Grid.Row="0" Grid.Column="1" IsReadOnly="True"
TextWrapping="Wrap" Text="{Binding SelectedFormatFileName, Mode=OneWay}" />
In the MainWindow constructor below, an instance of MainWindowViewModel is set to the DataContext.
public partial class MainWindow : MetroWindow
{
public MainWindow()
{
InitializeComponent();
this.DataContext = new MainWindowViewModel();
}
}
In MainWindowViewModel.cs, NotifyPropertyChanged(nameof(SelectedFormatFileName)); is executed when the property SelectedFormatFileName is updated to notify MainWindow of changes to the property SelectedFormatFileName, as shown in the code below. This way, when the property SelectedFormatFileName is updated, the string displayed in MainWindow will be updated.
this.GenerateExcelFile.NotifyCanExecuteChanged(); is a method that updates the enable/disable status of the “Generate an Excel file” button.
public class MainWindowViewModel : INotifyPropertyChanged
{
public event PropertyChangedEventHandler? PropertyChanged;
private void NotifyPropertyChanged([CallerMemberName] String propertyName = "")
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
...
private string m_SelectedFormatFileName = "";
public string SelectedFormatFileName
{
get => m_SelectedFormatFileName;
set
{
m_SelectedFormatFileName = value;
NotifyPropertyChanged(nameof(SelectedFormatFileName));
this.GenerateExcelFile.NotifyCanExecuteChanged();
}
}
3.3. Data Binding of Commands
MainWindow.xaml also provides data binding for commands, as shown in the code example below. This is also implemented without Prism.
<Button Grid.Row="0" Grid.Column="0" Margin="5" Content="{Binding SelectFormatFileButtonName}" Command="{Binding SelectFormatFile}" />
...
<Button Grid.Row="3" Grid.Column="0" Margin="5" Content="{Binding GenerateExcelFileButtonName}" Command="{Binding GenerateExcelFile}" />
Data binding of commands is implemented by setting an instance of a class that implements the ICommand or IRelayCommand interface, as shown in the MainWindowViewModel.cs code example below.
public ICommand SelectFormatFile { get; }
...
public IRelayCommand GenerateExcelFile { get; }
public MainWindowViewModel()
{
this.SelectFormatFile = new SelectFormatFileCommand(this);
...
this.GenerateExcelFile = new GenerateExcelFileCommand(this);
}
The SelectFormatFileCommand class is shown below. The CanExecute(object? parameter) method returns true because it is always ready to execute. When the “Select a format Excel file” button is clicked, the Execute(object? parameter) method of the following code is called.
public class SelectLogFileCommand : ICommand
{
public event EventHandler? CanExecuteChanged;
private MainWindowViewModel _MainWindowViewModel;
public SelectLogFileCommand(MainWindowViewModel mainWindowViewModel)
{
_MainWindowViewModel = mainWindowViewModel;
}
public bool CanExecute(object? parameter)
{
return true;
}
public void Execute(object? parameter)
{
var dialog = new Microsoft.Win32.OpenFileDialog();
dialog.DefaultExt = ".txt"; // Default file extension
dialog.Filter = "State Inquiry Log File (.txt)|*.txt"; // Filter files by extension
// Show open file dialog box
bool? result = dialog.ShowDialog();
// Process open file dialog box results
if (result == true)
{
_MainWindowViewModel.SelectedLogFileName = dialog.FileName;
}
}
}
The following code is a part of the GenerateExcelFileCommand class. IRelayCommand is the ICommand interface with the NotifyCanExecuteChanged() method added. IRelayCommand is included in Microsoft’s NuGet package CommunityToolkit.Mvvm 8.3.0.
public class GenerateExcelFileCommand : IRelayCommand
{
public event EventHandler? CanExecuteChanged;
public void NotifyCanExecuteChanged()
{
CanExecuteChanged?.Invoke(this, EventArgs.Empty);
}
private MainWindowViewModel _MainWindowViewModel;
private const string _SheetName = "SampleSheet";
public GenerateExcelFileCommand(MainWindowViewModel mainWindowViewModel)
{
_MainWindowViewModel = mainWindowViewModel;
}
public bool CanExecute(object? parameter)
{
if (_MainWindowViewModel.SelectedFormatFileName != "" &&
_MainWindowViewModel.SelectedLogFileName != "" &&
_MainWindowViewModel.SelectedOutputDirName != "")
{
return true;
}
else
{
return false;
}
}
public void Execute(object? parameter)
{
OutputExcelFile();
}
When a new value is set to the property SelectedFormatFileName of the MainWindowViewModel class, as in the code example below, call this.GenerateExcelFile.NotifyCanExecuteChanged(); to update the enable/disable status of the “Generate an Excel file” button.
public class MainWindowViewModel : INotifyPropertyChanged
{
...
private string m_SelectedFormatFileName = "";
public string SelectedFormatFileName
{
get => m_SelectedFormatFileName;
set
{
m_SelectedFormatFileName = value;
NotifyPropertyChanged(nameof(SelectedFormatFileName));
this.GenerateExcelFile.NotifyCanExecuteChanged();
}
}