Click here to Skip to main content
11,484,941 members (72,328 online)
Click here to Skip to main content

Tagged as

Convert your Data to Excel Spreadsheet

, 24 Feb 2011 CPOL 9.4K 206 8
Rate this:
Please Sign up or sign in to vote.
The solution offered here consists of an ExcelConverter that converts the data represented by set of classes into Excel 2003 XML Format.

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
{
    /// <span class="code-SummaryComment"><summary>
</span>    /// Value should have one of the types:
    /// string, DateTime, int, unsigned int etc.
    /// <span class="code-SummaryComment"></summary>
</span>    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);

    /// <span class="code-SummaryComment"><summary>
</span>    /// Creates a random timetable with classes from 1 to 7 and
    /// day from Monday to Friday
    /// <span class="code-SummaryComment"></summary>
</span>    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

License

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

Share

About the Author

ibogi
Software Developer Meter&Control, Zemun, Serbia
Serbia Serbia
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.

Comments and Discussions

 
Generalgood effort Pin
Pranay Rana25-Feb-11 4:32
memberPranay Rana25-Feb-11 4:32 
GeneralMy vote of 3 Pin
Slacker00724-Feb-11 8:37
memberSlacker00724-Feb-11 8:37 
GeneralMy vote of 2 Pin
MDL=>Moshu24-Feb-11 7:50
memberMDL=>Moshu24-Feb-11 7:50 
QuestionWhere's the beef? Pin
PIEBALDconsult24-Feb-11 5:53
memberPIEBALDconsult24-Feb-11 5:53 
AnswerRe: Where's the beef? Pin
ibogi24-Feb-11 8:57
memberibogi24-Feb-11 8:57 
GeneralRe: Where's the beef? Pin
PIEBALDconsult24-Feb-11 14:58
memberPIEBALDconsult24-Feb-11 14:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150520.1 | Last Updated 24 Feb 2011
Article Copyright 2011 by ibogi
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid