OpenXml and Date format in Excel cell
I am trying to create an Excel file in xlsx format using OpenXML because I need to use that on a web server.
I don’t have any problem to fill the values in the sheets; however I am struggling to set the classic Date format in a cell.
Below a quick test using DocumentFormat.OpenXml and WindowsBase references.
class Program
{
static void Main(string[] args)
{
BuildExel(@"C:\test.xlsx");
}
public static void BuildExel(string fileName)
{
using (SpreadsheetDocument myWorkbook =
SpreadsheetDocument.Create(fileName,
SpreadsheetDocumentType.Workbook))
{
// Workbook Part
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
string relId = workbookPart.GetIdOfPart(worksheetPart);
// File Version
var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
// Style Part
WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
// Sheets
var sheets = new Sheets();
var sheet = new Sheet { Name = "sheetName", SheetId = 1, Id = relId };
sheets.Append(sheet);
// Data
SheetData sheetData = new SheetData(CreateSheetData1());
// Add the parts to the workbook and save
var workbook = new Workbook();
workbook.Append(fileVersion);
workbook.Append(sheets);
var worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
private static Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
var nfs = new NumberingFormats();
var nformatDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(1),
FormatCode = StringValue.FromString("dd/mm/yyyy")
};
nfs.Append(nformatDateTime);
ss.Append(nfs);
return ss;
}
private static List<OpenXmlElement> CreateSheetData1()
{
List<OpenXmlElement> elements = new List<OpenXmlElement>();
var row = new Row();
// Line 1
Cell[] cells = new Cell[2];
Cell cell1 = new Cell();
cell1.DataType = CellValues.InlineString;
cell1.InlineString = new InlineString { Text = new Text { Text = "Daniel" } };
cells[0] = cell1;
Cell cell2 = new Cell();
cell2.DataType = CellValues.Number;
cell2.CellValue = new CellValue((50.5).ToString());
cells[1] = cell2;
row.Append(cells);
elements.Add(row);
// Line 2
row = new Row();
cells = new Cell[1];
Cell cell3 = new Cell();
cell3.DataType = CellValues.Date;
cell3.CellValue = new CellValue(DateTime.Now.ToOADate().ToString());
cell3.StyleIndex = 1; // <= here I try to apply the style...
cells[0] = cell3;
row.Append(cells);
elements.Add(row);
return elements;
}
The code executed creates the Excel document. However when I try to open the document, I receive this message: “Excel found unreadable content in 'test.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.”
If I remove the row:
cell3.StyleIndex = 1;
I can open the document but the date if not formatted, only the number of the date appears.
Thank you for your help to format the date.
This blog helped me: http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/
My problem was that I wanted to add NumberingFormats to the stylesheet rather than adding a new stylesheet altogether. If you want to to that, use
Stylesheet.InsertAt<NumberingFormats>(new NumberingFormats(), 0);
rather than
Stylesheet.AppendChild<NumberingFormats>(new NumberingFormats(), 0);
surprise, order counts..
Another BIG BIG vote for: https://github.com/closedxml/closedxml
After trying to build my own class from the bits and pieces spread around the net, including StackOverFlow, I found the above mentioned library and in a few moments had a fully functional Excel file.
I have pasted my attempt below for the edification of anyone that feels the urge to complete it. It is partially complete and has issues with the date and string cell creation.
Before you try to use this class, first download closedXML and try that first.
Consider yourself warned.
/// <summary>
/// This class allows for the easy creation of a simple Excel document who's sole purpose is to contain some export data.
/// The document is created using OpenXML.
/// </summary>
internal class SimpleExcelDocument : IDisposable
{
SheetData sheetData;
/// <summary>
/// Constructor is nothing special because the work is done at export.
/// </summary>
internal SimpleExcelDocument()
{
sheetData = new SheetData();
}
#region Get Cell Reference
public Cell GetCell(string fullAddress)
{
return sheetData.Descendants<Cell>().Where(c => c.CellReference == fullAddress).FirstOrDefault();
}
public Cell GetCell(uint rowId, uint columnId, bool autoCreate)
{
return GetCell(getColumnName(columnId), rowId, autoCreate);
}
public Cell GetCell(string columnName, uint rowId, bool autoCreate)
{
return getCell(sheetData, columnName, rowId, autoCreate);
}
#endregion
#region Get Cell Contents
// See: http://msdn.microsoft.com/en-us/library/ff921204.aspx
//
#endregion
#region Set Cell Contents
public void SetValue(uint rowId, uint columnId, bool value)
{
Cell cell = GetCell(rowId, columnId, true);
cell.DataType = CellValues.Boolean;
cell.CellValue = new CellValue(BooleanValue.FromBoolean(value));
}
public void SetValue(uint rowId, uint columnId, double value)
{
Cell cell = GetCell(rowId, columnId, true);
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(DoubleValue.FromDouble(value));
}
public void SetValue(uint rowId, uint columnId, Int64 value)
{
Cell cell = GetCell(rowId, columnId, true);
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(IntegerValue.FromInt64(value));
}
public void SetValue(uint rowId, uint columnId, DateTime value)
{
Cell cell = GetCell(rowId, columnId, true);
//cell.DataType = CellValues.Date;
cell.CellValue = new CellValue(value.ToOADate().ToString());
cell.StyleIndex = 1;
}
public void SetValue(uint rowId, uint columnId, string value)
{
Cell cell = GetCell(rowId, columnId, true);
cell.InlineString = new InlineString(value.ToString());
cell.DataType = CellValues.InlineString;
}
public void SetValue(uint rowId, uint columnId, object value)
{
bool boolResult;
Int64 intResult;
DateTime dateResult;
Double doubleResult;
string stringResult = value.ToString();
if (bool.TryParse(stringResult, out boolResult))
{
SetValue(rowId, columnId, boolResult);
}
else if (DateTime.TryParse(stringResult, out dateResult))
{
SetValue(rowId, columnId,dateResult);
}
else if (Int64.TryParse(stringResult, out intResult))
{
SetValue(rowId, columnId, intResult);
}
else if (Double.TryParse(stringResult, out doubleResult))
{
SetValue(rowId, columnId, doubleResult);
}
else
{
// Just assume that it is a plain string.
SetValue(rowId, columnId, stringResult);
}
}
#endregion
public SheetData ExportAsSheetData()
{
return sheetData;
}
public void ExportAsXLSXStream(Stream outputStream)
{
// See: http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx for some ideas...
// See: http://stackoverflow.com/questions/1271520/opening-xlsx-in-office-2003
using (SpreadsheetDocument package = SpreadsheetDocument.Create(outputStream, SpreadsheetDocumentType.Workbook))
{
// Setup the basics of a spreadsheet document.
package.AddWorkbookPart();
package.WorkbookPart.Workbook = new Workbook();
WorksheetPart workSheetPart = package.WorkbookPart.AddNewPart<WorksheetPart>();
workSheetPart.Worksheet = new Worksheet(sheetData);
workSheetPart.Worksheet.Save();
// create the worksheet to workbook relation
package.WorkbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet {
Id = package.WorkbookPart.GetIdOfPart(workSheetPart),
SheetId = 1,
Name = "Sheet 1"
};
package.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild<Sheet>(sheet);
package.WorkbookPart.Workbook.Save();
package.Close();
}
}
#region Internal Methods
private static string getColumnName(uint columnId)
{
if (columnId < 1)
{
throw new Exception("The column # can't be less then 1.");
}
columnId--;
if (columnId >= 0 && columnId < 26)
return ((char)('A' + columnId)).ToString();
else if (columnId > 25)
return getColumnName(columnId / 26) + getColumnName(columnId % 26 + 1);
else
throw new Exception("Invalid Column #" + (columnId + 1).ToString());
}
// Given a worksheet, a column name, and a row index,
// gets the cell at the specified column
private static Cell getCell(SheetData worksheet,
string columnName, uint rowIndex, bool autoCreate)
{
Row row = getRow(worksheet, rowIndex, autoCreate);
if (row == null)
return null;
Cell foundCell = row.Elements<Cell>().Where(c => string.Compare
(c.CellReference.Value, columnName +
rowIndex, true) == 0).FirstOrDefault();
if (foundCell == null && autoCreate)
{
foundCell = new Cell();
foundCell.CellReference = columnName;
row.AppendChild(foundCell);
}
return foundCell;
}
// Given a worksheet and a row index, return the row.
// See: http://msdn.microsoft.com/en-us/library/bb508943(v=office.12).aspx#Y2142
private static Row getRow(SheetData worksheet, uint rowIndex, bool autoCreate)
{
if (rowIndex < 1)
{
throw new Exception("The row # can't be less then 1.");
}
Row foundRow = worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
if (foundRow == null && autoCreate)
{
foundRow = new Row();
foundRow.RowIndex = rowIndex;
worksheet.AppendChild(foundRow);
}
return foundRow;
}
#endregion
#region IDisposable Stuff
private bool _disposed;
//private bool _transactionComplete;
/// <summary>
/// This will dispose of any open resources.
/// </summary>
public void Dispose()
{
Dispose(true);
// Use SupressFinalize in case a subclass
// of this type implements a finalizer.
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
// If you need thread safety, use a lock around these
// operations, as well as in your methods that use the resource.
if (!_disposed)
{
if (disposing)
{
//if (!_transactionComplete)
// Commit();
}
// Indicate that the instance has been disposed.
//_transaction = null;
_disposed = true;
}
}
#endregion
}
https://github.com/closedxml/closedxml is basically the correct answer I think.