CODEWITHSUNDEEP
c#exceldatetimeopenxml
jokarl
jokarl

Reputation: 2255

Export to Excel using OpenXML and C#, Integers and DateTime

I'm writing an application that's supposed to export data from a map application. This application is using Silverlight, and to facilitate export to Excel I am using this library. All of the data is represented in strings by default. When I write to the spreadsheet, I try to parse each string to see which type it is:

string str = kvp.Value;
int i = 0;
long l = 0;
decimal dec = 0;
DateTime dt;
if (int.TryParse(str, out i))
    doc.Workbook.Sheets[0].Sheet.Rows[r].Cells[c].SetValue(i);
else if (decimal.TryParse(str, out dec))
    doc.Workbook.Sheets[0].Sheet.Rows[r].Cells[c].SetValue(dec);
else if (long.TryParse(str, out l))
    doc.Workbook.Sheets[0].Sheet.Rows[r].Cells[c].SetValue(l);
else if (DateTime.TryParse(str, out dt))
    doc.Workbook.Sheets[0].Sheet.Rows[r].Cells[c].SetValue(dt);
else
    doc.Workbook.Sheets[0].Sheet.Rows[r].Cells[c].SetValue(str);

This works great, except for DateTime and when I try to parse a social security number, which in my case is 12 characters long.

The social security number is parsed as a decimal number, and is displayed in scientific form in Excel. From what I've gathered through reading it seems like a limitation in Excel. If I mark the cell however, I see the correct number in the top bar where you can write formulas. I've solved this problem so far by simply putting this number as a string and letting the end user handle it for now, but I'd really like for it to be a number in the finished document. Is this possible?

What really boggles my mind though, is the DateTime. The format of the date comes like this from the application: 10/15/2013 1:10:00 AM. It looks like this in the Excel file: 2455075.

I checked the source code for the date formatting but I don't seem to be adept enough to see if there is anything wrong in it. For anyone intresed, you can check it out here.

The SetValue-function is supposed to identify the following types automatically:

I apologize for the long post. It boils down to these questions:

Upvotes: 1

Views: 4630

Answers (1)

Sebastian Widz
Sebastian Widz

Reputation: 2072

To be set Cell Value in Date format you have to convert DateTime to OLE Automation Date. Also you can create more clear method for writing cell values. Somthing like this:

public bool UpdateValue(WorkbookPart wbPart, string sheetName, string addressName, string value,
                            UInt32Value styleIndex, CellValues dataType)
    {
        // Assume failure.
        bool updated = false;

        Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where(
            (s) => s.Name == sheetName).FirstOrDefault();

        if (sheet != null)
        {
            Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
            Cell cell = InsertCellInWorksheet(ws, addressName);

            if (dataType == CellValues.SharedString)
            {
                // Either retrieve the index of an existing string,
                // or insert the string into the shared string table
                // and get the index of the new item.
                int stringIndex = InsertSharedStringItem(wbPart, value);

                cell.CellValue = new CellValue(stringIndex.ToString());
            }
            else
            {
                cell.CellValue = new CellValue(value);
            }

            cell.DataType = new EnumValue<CellValues>(dataType);

            if (styleIndex > 0)
                cell.StyleIndex = styleIndex;

            // Save the worksheet.
            ws.Save();
            updated = true;
        }

        return updated;
    }

Then call this method like this (first call is for String second is for DateTime):

UpdateValue(workbookPart, wsName, "D14", "Some text", 0, CellValues.String);

UpdateValue(workbookPart, wsName, "H13", DateTime.Parse("2013-11-01").ToOADate().ToString(CultureInfo.InvariantCulture), 0, CellValues.Date);

Upvotes: 2

Related Questions