Click here to Skip to main content
11,413,648 members (52,178 online)
Click here to Skip to main content
Add your own
alternative version

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.zip
Excel2007ReadWrite
bin
Debug
Release
Excel2007ReadWrite.exe
ICSharpCode.SharpZipLib.dll
DLLs
ICSharpCode.SharpZipLib.dll
Input
In.xlsx
obj
Output
Properties
Settings.settings
TEMP
Template.xlsx
XlsxReadWrite.zip
XlsxReadWrite
XlsxReadWrite
bin
Release
ICSharpCode.SharpZipLib.dll
XlsxReadWrite.exe
Input
In.xlsx
Output
Properties
Settings.settings
References
ICSharpCode.SharpZipLib.dll
TEMP
Template.xlsx
// Excel2007ReadWrite created by Zeljko Svedic. This source code is free to use, modify and 
// incorporate in your software products. This code is not owned by GemBoxSoftware.com or is
// related in any way to GemBox.Spreadsheet Free/Professional .NET component.

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace Excel2007ReadWrite
{
    public partial class ExcelRWForm : Form
    {
        private const string tempDir = @"..\..\TEMP";
        private const string templateFile = @"..\..\Template.xlsx";

        private DataTable data = new DataTable();

        public ExcelRWForm()
        {
            InitializeComponent();

            this.textBoxInput.Text = @"..\..\Input\In.xlsx";
            this.textBoxOutput.Text = @"..\..\Output\Out.xlsx";

            this.data.Columns.Add("ID", Type.GetType("System.Int32"));
            this.data.Columns.Add("FirstName", Type.GetType("System.String"));
            this.data.Columns.Add("LastName", Type.GetType("System.String"));

            this.dataGridView1.DataSource = data;
        }

        private void buttonSelectInput_Click(object sender, EventArgs e)
        {
            this.openFileDialog1.FileName = this.textBoxInput.Text;

            if (this.openFileDialog1.ShowDialog(this) == DialogResult.OK)
                this.textBoxInput.Text = this.openFileDialog1.FileName;
        }

        private void buttonSelectOutput_Click(object sender, EventArgs e)
        {
            this.saveFileDialog1.FileName = this.textBoxOutput.Text;

            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
                this.textBoxOutput.Text = this.saveFileDialog1.FileName;
        }

        private void buttonReadInput_Click(object sender, EventArgs e)
        {
            // Get the input file name from the text box.
            string fileName = this.textBoxInput.Text;

            // Delete contents of the temporary directory.
            ExcelRW.DeleteDirectoryContents(ExcelRWForm.tempDir);

            // Unzip input XLSX file to the temporary directory.
            ExcelRW.UnzipFile(fileName, ExcelRWForm.tempDir);

            // Open XML file with table of all unique strings used in the workbook..
            FileStream fs = new FileStream(ExcelRWForm.tempDir + @"\xl\sharedStrings.xml",
                FileMode.Open, FileAccess.Read);
            // ..and call helper method that parses that XML and returns an array of strings.
            ArrayList stringTable = ExcelRW.ReadStringTable(fs);

            // Open XML file with worksheet data..
            fs = new FileStream(ExcelRWForm.tempDir + @"\xl\worksheets\sheet1.xml",
                FileMode.Open, FileAccess.Read);
            // ..and call helper method that parses that XML and fills DataTable with values.
            ExcelRW.ReadWorksheet(fs, stringTable, this.data);
        }

        private void buttonWriteOutput_Click(object sender, EventArgs e)
        {
            // Get the output file name from the text box.
            string fileName = this.textBoxOutput.Text;

            // Delete contents of the temporary directory.
            ExcelRW.DeleteDirectoryContents(ExcelRWForm.tempDir);

            // Unzip template XLSX file to the temporary directory.
            ExcelRW.UnzipFile(ExcelRWForm.templateFile, ExcelRWForm.tempDir);

            // We will need two string tables; a lookup Hashtable for fast searching and 
            // an ordinary ArrayList where items are sorted by their index.
            Hashtable lookupTable;

            // Call helper methods which creates both tables from input data.
            ArrayList stringTable = ExcelRW.CreateStringTables(this.data, out lookupTable);

            // Create XML file..
            FileStream fs = new FileStream(ExcelRWForm.tempDir + @"\xl\sharedStrings.xml",
                FileMode.Create);
            // ..and fill it with unique strings used in the workbook
            ExcelRW.WriteStringTable(fs, stringTable);

            // Create XML file..
            fs = new FileStream(ExcelRWForm.tempDir + @"\xl\worksheets\sheet1.xml",
                FileMode.Create);
            // ..and fill it with rows and columns of the DataTable.
            ExcelRW.WriteWorksheet(fs, this.data, lookupTable);

            // ZIP temporary directory to the XLSX file.
            ExcelRW.ZipDirectory(ExcelRWForm.tempDir, fileName);

            // If checkbox is checked, show XLSX file in Excel 2007.
            if (this.checkBoxOpenFile.Checked)
                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
Web Developer
Croatia Croatia
Zeljko Svedic works as a lead developer at GemBox Software. He primarily works on GemBox.Spreadsheet, a .NET component for reading and writing native XLS files.

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