Reading dates from OpenXml Excel files

 

OpenXml을 이용하여 엑셀 파일의 날짜 필드(셀) 값 읽어 오기

 

엑셀에서 2011-05-12 와 같이 날짜를 입력하게 되면, 해당 셀은 날짜형 포멧으로 변경되게 됩니다.

 

이를 OpenXml을 이용하여 읽는 방법은 아래와 같습니다.

 

먼저 해당 Excel 파일을 압출 해제 하고 Xml을 확인하게 되면 Sheet1.xml 상에서는 아래와 같이 입력되어 있습니다.

<row r="3" spans="1:3" x14ac:dyDescent="0.3">
	<c r="A3" s="1">
		<v>40310</v>
	</c>
</row>

일반 텍스트의 경우 <c r=”A3” t=”s”>와 같은 형태로 되어  있으나 날짜 형의 경우는 S=”1” S로 시작하게 됩니다.

 

값이 40310 이라는 UInt32Value의 형태로 입력되게 됩니다.

 

변경하는 방법은 C#에서 아래와 같이하면 됩니다.

string strDate = String.Empty;
DateTime cellDate = DateTime.FromOADate(Convert.ToDouble(cell.CellValue.InnerText));

if (cellDate != null)
	strDate = cellDate.ToString("yyyy-MM-dd");

 

위와 같이 변경하는 방법은 standard date의 경우에만 확인하였으며, Formatted date 형의 경우에는 확인하지 못하였습니다.
반대로 적용할 경우에는  DateTime.ToOADate Method로 이용을 하시면 됩니다.

 

해당 date의 number format 확인 방법은 Xml의 Style.xml에서 확인 가능하며, 아래 xml의 numFmtID 값으로 구분하면 됩니다.

<cellXfs count="2">
	<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0">
		<alignment vertical="center"/>
	</xf>
	<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1">
		<alignment vertical="center"/>
	</xf>
</cellXfs>

 

numFmtId가 14~ 22 Standard

numFmtId가 160~ 이상의 값은 Fomatted date 형이라고 보시면 될 것 같습니다.

 

전체 Sample은 아래에서 확인 할 수 있습니다.

protected void Page_Load(object sender, EventArgs e)
{
	using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(@"C:\test.xlsx", true))
	{
		IEnumerable<Sheet> sheets = myDoc.WorkbookPart.Workbook.Descendants<Sheet>();
		WorksheetPart worksheetPart1 = myDoc.WorkbookPart.GetPartById(sheets.First().Id) as WorksheetPart;
		Worksheet sheet = worksheetPart1.Worksheet;
		IEnumerable<Row> datarow = from row in sheet.Descendants<Row>()
									where row.RowIndex > 0
									select row;

		StringBuilder sbData = new StringBuilder();
		foreach (Row row in datarow)
		{
			foreach (Cell cell in row.Descendants<Cell>())
			{
				string strData = String.Format("cell.CellReference={0}, cell.value={1}", GetColumnName(cell.CellReference), GetCellValue(myDoc, cell, myDoc));
				sbData.AppendLine(strData);
			}
		}

		lblTest.Text = sbData.ToString();
	}
}

private string GetColumnName(string cellReference)
{
	Regex regex = new Regex("[A-Za-z]+");
	Match match = regex.Match(cellReference);

	return match.Value;
}

private string GetCellValue(SpreadsheetDocument document, Cell cell, SpreadsheetDocument myDoc)
{
	SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
	string value = cell.CellValue != null ? cell.CellValue.InnerXml : String.Empty;

	if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
		return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
	else if (cell.StyleIndex.InnerText.Equals("1"))
	{
		string strDate = String.Empty;
		DateTime cellDate = DateTime.FromOADate(Convert.ToDouble(cell.CellValue.InnerText));

		if (cellDate != null)
			strDate = cellDate.ToString("yyyy-MM-dd");

		return strDate;
	}
	else
		return value;
}

 

 

이상입니다.

감사합니다.