Click here to Skip to main content
12,510,860 members (49,674 online)
Click here to Skip to main content

Stats

271.6K views
11.4K downloads
288 bookmarked
Posted

Read and write Open XML files (MS Office 2007)

, 31 Jul 2011 CPOL
Learn how to read and write Open XML files (MS Office 2007).
Excel2007ReadWrite
bin
Debug
Release
Excel2007ReadWrite.exe
ICSharpCode.SharpZipLib.dll
DLLs
ICSharpCode.SharpZipLib.dll
Input
In.xlsx
obj
Output
Properties
TEMP
Template.xlsx
XlsxReadWrite
XlsxReadWrite
bin
Release
ICSharpCode.SharpZipLib.dll
XlsxReadWrite.exe
Input
In.xlsx
Output
Properties
References
ICSharpCode.SharpZipLib.dll
TEMP
Template.xlsx
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ZeljkoS
President Gemmeus Ltd
United Kingdom United Kingdom
Zeljko Svedic, the owner of a component vendor company called GemBox Software.
GemBox Software provides .NET developers a GemBox components that enable easy and efficient processing of office files:
  • GemBox.Spreadsheet, a .NET component for reading, writing, converting and printing spreadsheet files (XLSX, XLS, ODS, CSV, HTML, PDF and XPS).
  • GemBox.Document, a .NET component for reading, writing, converting and printing document files (DOCX, DOC, PDF, HTML, XPS, RTF and TXT).
  • GemBox.Email, a .NET component for composing, receiving and sending email messages using IMAP, POP and SMTP.

You may also be interested in...

Pro
Pro
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160929.1 | Last Updated 31 Jul 2011
Article Copyright 2006 by ZeljkoS
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid