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
{
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);
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
- 24th February, 2011: Initial version
My name is Ivica Bogosavljevic, I am an embedded software engineer from Serbia currently working mostly in C# and C++ programming languages designing Windows CE based reliable embedded systems.