Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Read and write Open XML files (MS Office 2007)

, 31 Jul 2011
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
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Text;
using System.Xml;
using ICSharpCode.SharpZipLib.Zip;

namespace XlsxReadWrite
{
    internal static class XlsxRW
    {
        public static void DeleteDirectoryContents(string directory)
        {
            var info = new DirectoryInfo(directory);

            foreach (var file in info.GetFiles())
                file.Delete();

            foreach (var dir in info.GetDirectories())
                dir.Delete(true);
        }

        public static void UnzipFile(string zipFileName, string targetDirectory)
        {
            new FastZip().ExtractZip(zipFileName, targetDirectory, null);
        }

        public static void ZipDirectory(string sourceDirectory, string zipFileName)
        {
            new FastZip().CreateZip(zipFileName, sourceDirectory, true, null);
        }

        public static IList<string> ReadStringTable(Stream input)
        {
            var stringTable = new List<string>();

            using (var reader = XmlReader.Create(input))
                for (reader.MoveToContent(); reader.Read(); )
                    if (reader.NodeType == XmlNodeType.Element && reader.Name == "t")
                        stringTable.Add(reader.ReadElementString());

            return stringTable;
        }

        public static void ReadWorksheet(Stream input, IList<string> stringTable, DataTable data)
        {
            using (var reader = XmlReader.Create(input))
            {
                DataRow row = null;
                int columnIndex = 0;
                string type;
                int value;

                for (reader.MoveToContent(); reader.Read(); )
                    if (reader.NodeType == XmlNodeType.Element)
                        switch (reader.Name)
                        {
                            case "row":
                                row = data.NewRow();
                                data.Rows.Add(row);

                                columnIndex = 0;

                                break;

                            case "c":
                                type = reader.GetAttribute("t");
                                reader.Read();
                                value = int.Parse(reader.ReadElementString(), CultureInfo.InvariantCulture);

                                if (type == "s")
                                    row[columnIndex] = stringTable[value];
                                else
                                    row[columnIndex] = value;

                                columnIndex++;

                                break;
                        }
            }
        }

        public static IList<string> CreateStringTables(DataTable data, out IDictionary<string, int> lookupTable)
        {
            var stringTable = new List<string>();
            lookupTable = new Dictionary<string, int>();

            foreach (DataRow row in data.Rows)
                foreach (DataColumn column in data.Columns)
                    if (column.DataType == typeof(string))
                    {
                        var value = (string)row[column];

                        if (!lookupTable.ContainsKey(value))
                        {
                            lookupTable.Add(value, stringTable.Count);
                            stringTable.Add(value);
                        }
                    }

            return stringTable;
        }

        public static void WriteStringTable(Stream output, IList<string> stringTable)
        {
            using (var writer = XmlWriter.Create(output))
            {
                writer.WriteStartDocument(true);

                writer.WriteStartElement("sst", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                writer.WriteAttributeString("count", stringTable.Count.ToString(CultureInfo.InvariantCulture));
                writer.WriteAttributeString("uniqueCount", stringTable.Count.ToString(CultureInfo.InvariantCulture));

                foreach (var str in stringTable)
                {
                    writer.WriteStartElement("si");
                    writer.WriteElementString("t", str);
                    writer.WriteEndElement();
                }

                writer.WriteEndElement();
            }
        }

        public static string RowColumnToPosition(int row, int column)
        {
            return ColumnIndexToName(column) + RowIndexToName(row);
        }

        public static string ColumnIndexToName(int columnIndex)
        {
            var second = (char)(((int)'A') + columnIndex % 26);

            columnIndex /= 26;

            if (columnIndex == 0)
                return second.ToString();
            else
                return ((char)(((int)'A') - 1 + columnIndex)).ToString() + second.ToString();
        }

        public static string RowIndexToName(int rowIndex)
        {
            return (rowIndex + 1).ToString(CultureInfo.InvariantCulture);
        }

        public static void WriteWorksheet(Stream output, DataTable data, IDictionary<string, int> lookupTable)
        {
            using (XmlTextWriter writer = new XmlTextWriter(output, Encoding.UTF8))
            {
                writer.WriteStartDocument(true);

                writer.WriteStartElement("worksheet");
                writer.WriteAttributeString("xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                writer.WriteAttributeString("xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

                writer.WriteStartElement("dimension");
                var lastCell = RowColumnToPosition(data.Rows.Count - 1, data.Columns.Count - 1);
                writer.WriteAttributeString("ref", "A1:" + lastCell);
                writer.WriteEndElement();

                writer.WriteStartElement("sheetViews");
                writer.WriteStartElement("sheetView");
                writer.WriteAttributeString("tabSelected", "1");
                writer.WriteAttributeString("workbookViewId", "0");
                writer.WriteEndElement();
                writer.WriteEndElement();

                writer.WriteStartElement("sheetFormatPr");
                writer.WriteAttributeString("defaultRowHeight", "15");
                writer.WriteEndElement();

                writer.WriteStartElement("sheetData");
                WriteWorksheetData(writer, data, lookupTable);
                writer.WriteEndElement();

                writer.WriteStartElement("pageMargins");
                writer.WriteAttributeString("left", "0.7");
                writer.WriteAttributeString("right", "0.7");
                writer.WriteAttributeString("top", "0.75");
                writer.WriteAttributeString("bottom", "0.75");
                writer.WriteAttributeString("header", "0.3");
                writer.WriteAttributeString("footer", "0.3");
                writer.WriteEndElement();

                writer.WriteEndElement();
            }
        }

        public static void WriteWorksheetData(XmlTextWriter writer, DataTable data, IDictionary<string, int> lookupTable)
        {
            var rowsCount = data.Rows.Count;
            var columnsCount = data.Columns.Count;
            string relPos;

            for (int row = 0; row < rowsCount; row++)
            {
                writer.WriteStartElement("row");
                relPos = RowIndexToName(row);
                writer.WriteAttributeString("r", relPos);
                writer.WriteAttributeString("spans", "1:" + columnsCount.ToString(CultureInfo.InvariantCulture));

                for (int column = 0; column < columnsCount; column++)
                {
                    object value = data.Rows[row][column];

                    writer.WriteStartElement("c");
                    relPos = RowColumnToPosition(row, column);
                    writer.WriteAttributeString("r", relPos);

                    var str = value as string;
                    if (str != null)
                    {
                        writer.WriteAttributeString("t", "s");
                        value = lookupTable[str];
                    }

                    writer.WriteElementString("v", value.ToString());

                    writer.WriteEndElement();
                }

                writer.WriteEndElement();
            }
        }
    }
}

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)

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 | Mobile
Web04 | 2.8.140721.1 | Last Updated 31 Jul 2011
Article Copyright 2006 by ZeljkoS
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid