[C#] OpenXML을 이용하여 Excel 파일의 빈 칸 읽기

출처 : http://angeleyes.tistory.com/290

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.IO;
using System.Text.RegularExpressions;

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;

namespace WebApplication1.OpenXml
{
    public partial class ReadExcelData : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(@"C:\test.xlsx", true))
            {
                IEnumerable sheets = myDoc.WorkbookPart.Workbook.Descendants();
                WorksheetPart worksheetPart1 = myDoc.WorkbookPart.GetPartById(sheets.First().Id) as WorksheetPart;
                Worksheet sheet = worksheetPart1.Worksheet;
                IEnumerable datarow = from row in sheet.Descendants()
                                           where row.RowIndex > 0
                                           select row;

                DataTable dt = new DataTable("test");

                int i = 0;
                foreach (Row row in datarow)
                {
                    if (i == 0)
                    {
                        foreach (Cell cell in row.Descendants())
                        {
                            dt.Columns.Add(new DataColumn(GetColumnName(cell.CellReference), typeof(string)));
                        }
                    }
                    else
                    {
                        DataRow dr = dt.NewRow();
                        foreach (Cell cell in row.Descendants())
                        {
                            dr[GetColumnName(cell.CellReference)] = GetCellValue(myDoc, cell, myDoc);
                        }

                        dt.Rows.Add(dr);
                    }

                    i++;
                }

                dt.WriteXml(@"C:\test\test11.xml");
            }
        }

        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;
        }
    }
}