CODEWITHSUNDEEP
c#excel
irusul
irusul

Reputation: 221

DataSet to Excel using OpenXML

I have a DataSet with several tables on it. I want to export it to Excel, where each table will be on a different worksheet. How can I do it using OpenXML but I want to persist the DataType of the columns (ex: string, date, numeric, etc.)? Currently I have:

using (SpreadsheetDocument workbook = SpreadsheetDocument.Create(fileName,
    SpreadsheetDocumentType.Workbook)){
workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook {Sheets = new Sheets()};

uint sheetId = 1;

var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet(sheetData);

var sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

if (sheets.Elements<Sheet>().Any())
{
    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}

var sheet = new Sheet
{
    Id = relationshipId,
    SheetId = sheetId,
    Name = exportedSheet
};
sheets.Append(new List<OpenXmlElement> {sheet});

var headerRow = new Row();

var columns = new List<string>();
foreach (DataColumn column in extractedData.Columns)
{
    columns.Add(column.ColumnName);

    var cell = new Cell
    {
        DataType = CellValues.String,
        CellValue = new CellValue(column.ColumnName)
    };
    headerRow.AppendChild(cell);
}

sheetData.AppendChild(headerRow);

foreach (DataRow dsrow in extractedData.Rows)
{
    var newRow = new Row();
    DataRow dsrow1 = dsrow;
    foreach (Cell cell in columns.Select(col => new Cell
    {
        DataType = CellValues.String,
        CellValue = new CellValue(dsrow1[col].ToString())
    }))
    {
        newRow.AppendChild(cell);
    }

    sheetData.AppendChild(newRow);
}}

Upvotes: 1

Views: 2141

Answers (1)

Michael Gunter
Michael Gunter

Reputation: 12811

You just need to map the value of each column to a corresponding CellValues value. Here's the rough idea.

// store this somewhere (a static field?)
var columnTypeToCellDataTypeMap = new Dictionary<Type, CellValues>
    {
        { typeof(Boolean), CellValues.Boolean },
        { typeof(Byte), CellValues.Number },
        { typeof(Char), CellValues.String },
        { typeof(DateTime), CellValues.Date },
        { typeof(Double), CellValues.Number },
        { typeof(Decimal), CellValues.Number },
        { typeof(Int16), CellValues.Number },
        { typeof(Int32), CellValues.Number },
        { typeof(Int64), CellValues.Number },
        { typeof(SByte), CellValues.Number },
        { typeof(Single), CellValues.Number },
        { typeof(String), CellValues.String },
        { typeof(UInt16), CellValues.Number },
        { typeof(UInt32), CellValues.Number },
        { typeof(UInt64), CellValues.Number },
    }

// and use it like this:
foreach (DataRow row in table.Rows)
{
    foreach (DataColumn column in table.Columns)
    {
        // map the column type to an OpenXML SDK CellValues type
        CellValues cellDataType;
        if (!columnTypeToCellDataTypeMap.TryGetValue(column.DataType, out cellDataType))
            cellDataType = CellValues.String;

        // get the cell value
        object value = row[column];
        string cellValue = (value != null ? value.ToString() : "");

        // construct the cell
        var cell = new Cell { DataType = cellDataType, CellValue = value };

        // etc
        ...
    }
}

Upvotes: 3

Related Questions