|
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Windows;
using Microsoft.Win32;
using XlsxReadWrite.Properties;
namespace XlsxReadWrite
{
public partial class MainWindow : Window
{
private readonly DataTable data = new DataTable(Settings.Default.DataTableName, Settings.Default.DataTableNamespace);
private readonly string tempDir = Settings.Default.TemporaryDirectory;
private readonly string templateFile = Settings.Default.TempateFilePath;
public MainWindow()
{
InitializeComponent();
try
{
this.data.ReadXmlSchema(Settings.Default.DataTableXmlSchemaFileName);
}
catch
{
using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("XlsxReadWrite.Resources.XlsxReadWriteTable.xsd"))
this.data.ReadXmlSchema(stream);
}
this.dataGrid.ItemsSource = this.data.AsDataView();
}
private void ShowOpenFileDialog(object sender, RoutedEventArgs e)
{
var openFileDialog = new OpenFileDialog()
{
FileName = Settings.Default.InputFileName,
DefaultExt = "*.xlsx",
Filter = "Excel Workbook (.xlsx)|*.xlsx"
};
if (openFileDialog.ShowDialog(this) == true)
Settings.Default.InputFileName = openFileDialog.FileName;
}
private void ShowSaveFileDialog(object sender, RoutedEventArgs e)
{
var saveFileDialog = new SaveFileDialog()
{
FileName = Settings.Default.OutputFileName,
DefaultExt = "*.xlsx",
Filter = "Excel Workbook (.xlsx)|*.xlsx"
};
if (saveFileDialog.ShowDialog(this) == true)
Settings.Default.OutputFileName = saveFileDialog.FileName;
}
private void ReadInput(object sender, RoutedEventArgs e)
{
// Get the input file name from the text box.
var fileName = this.inputTextBox.Text;
// Delete contents of the temporary directory.
XlsxRW.DeleteDirectoryContents(tempDir);
// Unzip input XLSX file to the temporary directory.
XlsxRW.UnzipFile(fileName, tempDir);
IList<string> stringTable;
// Open XML file with table of all unique strings used in the workbook..
using (var stream = new FileStream(Path.Combine(tempDir, @"xl\sharedStrings.xml"),
FileMode.Open, FileAccess.Read))
// ..and call helper method that parses that XML and returns an array of strings.
stringTable = XlsxRW.ReadStringTable(stream);
// Open XML file with worksheet data..
using (var stream = new FileStream(Path.Combine(tempDir, @"xl\worksheets\sheet1.xml"),
FileMode.Open, FileAccess.Read))
// ..and call helper method that parses that XML and fills DataTable with values.
XlsxRW.ReadWorksheet(stream, stringTable, this.data);
}
private void WriteOutput(object sender, RoutedEventArgs e)
{
// Get the output file name from the text box.
string fileName = this.outputTextBox.Text;
// Delete contents of the temporary directory.
XlsxRW.DeleteDirectoryContents(tempDir);
// Unzip template XLSX file to the temporary directory.
XlsxRW.UnzipFile(templateFile, tempDir);
// We will need two string tables; a lookup IDictionary<string, int> for fast searching and
// an ordinary IList<string> where items are sorted by their index.
IDictionary<string, int> lookupTable;
// Call helper methods which creates both tables from input data.
var stringTable = XlsxRW.CreateStringTables(this.data, out lookupTable);
// Create XML file..
using (var stream = new FileStream(Path.Combine(tempDir, @"xl\sharedStrings.xml"),
FileMode.Create))
// ..and fill it with unique strings used in the workbook
XlsxRW.WriteStringTable(stream, stringTable);
// Create XML file..
using (var stream = new FileStream(Path.Combine(tempDir, @"xl\worksheets\sheet1.xml"),
FileMode.Create))
// ..and fill it with rows and columns of the DataTable.
XlsxRW.WriteWorksheet(stream, this.data, lookupTable);
// ZIP temporary directory to the XLSX file.
XlsxRW.ZipDirectory(tempDir, fileName);
// If checkbox is checked, show XLSX file in Microsoft Excel.
if (this.openFileCheckBox.IsChecked == true)
System.Diagnostics.Process.Start(fileName);
}
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.