Merging multiple Excel files with WPF

Recently, the company has done a red envelopes project, one of the main work is to import 2.38 million coupons into the platform, for card coupon collection and verification.
But the vouchers provided to me are given in one of the Excel files. Then import all the coupons into a table via the Excel file Import function.
Because there are 2.38 million coupons, so there are more than 100 Excel files. If it is imported, it is too troublesome. So, to make an application,
Merges multiple Excel files into one file. Baidu on the Internet a bit, and indeed there is such use of software. But some are either not free, or some are free but feature-constrained.
So decide to write one yourself. Just recently, I've also learned about Microsoft's WPF, using it to practice the results of learning.

1 development environment
Win7
. NET Framework 4.0
VS2010 SP1

NuGet

2 Functional Requirements
Select a folder to get all the Excel files in that folder and display them on the screen.
The user can then select some of these files to be merged into and cancel the operation.

3 Implementation Notes
According to the requirements analysis, you can probably know the following function points
3-1 Select Folder
System.Windows.Forms.FolderBrowserDialog fb = new System.Windows.Forms.FolderBrowserDialog ();
if (FB. ShowDialog () = = System.Windows.Forms.DialogResult.OK)
{
Select the folder path
SelectedPath = fb. SelectedPath + "/";
}

3-2 get the Excel file recursively and display it on the ListBox control
Declaring a list of Excel files
Private list<excelfile> excelfilelist = new list<excelfile> ();
Recursive get function
private void FindFile (string dirpath)//parameter Dirpath for the specified directory
{
Find files under the specified directory and subdirectories
DirectoryInfo dir=new DirectoryInfo (Dirpath);
Try
{
foreach (DirectoryInfo D in Dir.getdirectories ())//Find subdirectories
{
FindFile (D.fullname);
}
foreach (FileInfo f in Dir.getfiles ("*.xls"))//Find File
{
Excelfilelist.add (new Excelfile {Name = f.name, Path = f.fullname, IsSelected = True, status= "unhandled"});
}
}
catch (Exception e)
{
MessageBox.Show (E.message);
}
}
Data binding
Listbox1.itemssource = excelfilelist;

3-3 user-selectable Excel files
Rewrite the ListBoxItem template and put a checkbox in it
<listbox horizontalalignment= "Stretch" margin= "12,14,12,0" name= "ListBox1" verticalalignment= "Stretch" Horizontalcontentalignment= "Stretch" grid.row= "2" minheight= "" "minwidth=" 480 [verticalcontentalignment=] Stretch ">
<ListBox.ItemTemplate>
<DataTemplate>
<grid margin= "3" >
<Grid.ColumnDefinitions>
<columndefinition width= "Auto" ></ColumnDefinition>
<columndefinition width= "Auto" ></ColumnDefinition>
<columndefinition width= "*" ></ColumnDefinition>
<columndefinition width= "Auto" ></ColumnDefinition>
</Grid.ColumnDefinitions>
<checkbox grid.column= "0" padding= "3" verticalalignment= "Center" ischecked= "{Binding Path=isselected,mode=twoway } "></CheckBox>
<textblock grid.column= "1" padding= "5" verticalalignment= "Center" text= "{Binding path=name}" ></textblock >
<textblock grid.column= "2" padding= "6" verticalalignment= "Center" text= "{Binding Path=path}" ></textblock >
<textblock grid.column= "3" padding= "3" verticalalignment= "Center" text= "{Binding path=status}" ></textblock >
</Grid>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>

3-4 merge Excel files, and can cancel the merge operation
It is a time-out operation to read the contents of an Excel file and write it to a file last. So it's done in a multi-threaded way. The BackgroundWorker component is used mainly.
public static bool Merge (string SelectedPath, list<excelfile> excelfilelist, System.ComponentModel.BackgroundWorker BackgroundWorker)
{
Start the search for primes and wait.
var writer = new StreamWriter (SelectedPath + "All.csv", false, System.Text.Encoding.UTF8);
Writer. WriteLine ("Coupon code, voucher password, activity description, valid start date, valid end date, whether imported, card voucher ID, custom code");

int i = 0;
foreach (var f in excelfilelist)
{
i++;
if (backgroundworker.cancellationpending)
{
Return without doing any more work.
return false;
}

if (backgroundworker.workerreportsprogress)
{
Backgroundworker.reportprogress (i);
}

if (!f.isselected) continue;
F.status = "in Process";
var stream = new FileStream (F.path, FileMode.Open);
Iexceldatareader Excelreader = Excelreaderfactory.createopenxmlreader (stream);
Excelreader.read ();
while (Excelreader.read ())
{
var code = (excelreader.getstring (1). Trim ());
var pwd = (excelreader.getstring (2). Trim ());
var activity = (excelreader.getstring (3). Trim ());

if (!string. IsNullOrEmpty (code))
{
var card_id = "PGW8RT3XXBYGETZUJVLV1BBLUQDK";
var card_code = code + pwd;
Writer. WriteLine ("{0},{1},{2},2015-02-19 00:00:00,2015-03-31 23:59:59,0,{3},{4}", code, PWD, activity, card_id, Card_code);
}
}
Excelreader.close ();
F.status = "processing Done";
}
Writer. Flush ();
Writer. Close ();

return true;

}
The final implementation of the software's main interface

Action screen for merging files

Finally, the person thinks the page is still a bit ugly, can add some animation, cool style and so on. But that's not what I'm good at.

Download source code

Merging multiple Excel files with WPF