Convert your Data to Excel Spreadsheet

• Download source - 12.34 KB

Introduction

For some applications, there is a need to export data into Excel format. Using Office PIA is cumbersome and requires a lot of know-how, although very powerful. In simple cases, when there is no need for advanced Excel features, exporting to Microsoft Excel 2003 XML format might be the solution.

The solution offered here consists of an ExcelConverter that converts the data represented by set of classes into Excel 2003 XML Format. The programmer uses a set of interfaces that represent Workbook, Worksheet, Row and Cell to wrap his data and ExcelConverter then parses the data and writes down Excel file.

Using the Code

The relevant interfaces the programmer needs to implement are given below:

public interface IExcelWorkbook
{
    IEnumerable<IWorksheet> GetWorksheets();
}

public interface IWorksheet
{
    string GetWorksheetName();

    IEnumerable<IRow> GetRows();
}

public interface IRow
{
    IEnumerable<IDatum> GetData();
}

public interface IDatum
{
    /// <summary>
    /// Value should have one of the types:
    /// string, DateTime, int, unsigned int etc.
    /// </summary>
    object GetValue();
}  

IExcelWorkbook contains a list of IWorksheet. IWorksheet contains a list of IRow, and IRow contains a list of IDatum. The programmer needs to implement all these interfaces and create an appropriate hierarchy of classes, as already described. The programmer then calls ExcelConverter.Conver to convert the data to Excel File.

Illustration of how the classes are used is given here. A Student has a name and a timetable of subjects he must attend. Timetable is a matrix which is indexed using day of week and class. The declaration of Subject and Timetable classes is as follows:

public class Student
{
    public string Name
    {  get; set; }

    public Timetable Timetable
    {  get; set; }
}

public class Timetable
{
    public void SetSubject(Subject subject, DayOfWeek day, int class0);

    public bool GetSubject(DayOfWeek day, int class0, out Subject subject);

    /// <summary>
    /// Creates a random timetable with classes from 1 to 7 and
    /// day from Monday to Friday
    /// </summary>
    public static Timetable CreateRandomTimeTable();

}

The goal here is to export data about students to an Excel file. Each student gets a separate worksheet in Excel workbook with his own name, and each worksheet contains a timetable for that student.

From these requirements, there should be an implementation of IExcelWorkbook that takes as an input array of Student. Also, there should be an implementation of IWorksheet that is a wrapper for a single Student. There should also be an implementation of interface IRow that is actually a row from a Timetable.The example implementation is given:

public class StudentsToExcel: IExcelWorkbook
{
    private Student[] students;

    public StudentsToExcel(params Student[] students)
    {
        this.students = students;
    }

    #region IExcelData Members

    public IEnumerable<IWorksheet> GetWorksheets()
    {
        foreach (Student s in students)
        {
            yield return new StudentWorksheet(s);
        }
    }

    #endregion
}

public class StudentWorksheet: IWorksheet
{
    private Student student;

    public StudentWorksheet(Student s)
    {
        student = s;
    }

    #region IWorksheet Members

    public string GetWorksheetName()
    {
        return student.Name;
    }

    public IEnumerable<IRow> GetRows()
    {
        yield return new HeaderRow();

        for (int class0 = 1; class0 <= 7; class0++)
        {
            yield return new TimetableRow(student.Timetable, class0);
        }
    }

    #endregion
}

public class HeaderRow: IRow
{
    #region IRow Members

    public IEnumerable<IDatum> GetData()
    {
        yield return new Datum(string.Empty);

        for (DayOfWeek day = DayOfWeek.Monday; day <= DayOfWeek.Friday; day++)
        {
            yield return new Datum(day.ToString());
        }
    }

    #endregion
}

public class TimetableRow: IRow
{
    private Timetable timetable;
    private int class0;

    public TimetableRow(Timetable t, int class0)
    {
        this.timetable = t;
        this.class0 = class0;
    }

    #region IRow Members

    public IEnumerable<IDatum> GetData()
    {
        yield return new Datum(class0);

        for (DayOfWeek day = DayOfWeek.Monday; day <= DayOfWeek.Friday; day++)
        {
            Subject subject;
            if (!timetable.GetSubject(day, class0, out subject))
            {
                throw new Exception();
            }

            yield return new Datum(subject.ToString());
        }
    }

    #endregion
}

public class Datum: IDatum
{
    private object v;

    public Datum(object value)
    {
        v = value;
    }

    #region IDatum Members

    public object GetValue()
    {
        return v;
    }

    #endregion
}  

It is easy to see that data converters are basically iterators over already existing collections.

Example of code usage is shown below:

Student s1 = new Student();
s1.Name = "Dusan Pokrajcic";
s1.Timetable = Timetable.CreateRandomTimeTable();

Student s2 = new Student();
s2.Name = "Milosav Milovanovic";
s2.Timetable = Timetable.CreateRandomTimeTable();

ExcelConverter.Convert(new StudentsToExcel(s1, s2), @"c:\out.xml");

History

• 24^th February, 2011: Initial version