Reading Excel Spreadsheet using DocumentFormat.OpenXML skips columns that don't have data in them

I have an Excel spreadsheet (xlsx) with the following columns:
A - RefTypeID
B - Submitted Date
C - Pol #
D - ST
E - Referral Category
F - Agent
G - Loc
H - Supervisor

The Submitted Date column does not have to have a date/time in the column and in the test file I am using the column is blank except for the first row which contains the header data.

I am reading the spreadsheet into a DataTable in c#.  I read the first row and retrieve each cell and use the value in the cell for the Column in the DataTable.  This is working and the DataTable columns get created so I can refer to them via the column name rather than the column position.

The problem is when I start reading row 2.  The first column has the RefTypeID in it and is read in successfully.  However when I get what is supposed to be Column 2 - Submitted Date, the data returned is the value that I am expecting in Column 3 - Pol # and all subsequent columns are off by 1.  Here is the code that I am using to read the spreadsheet and setup the DataTable.  Any help with this problem is greatly appreciated!

        private static DataTable GetDataTableFromSpreadsheet(Stream myExcelStream)
        {
            var dt = new DataTable();
            using (var sDoc = SpreadsheetDocument.Open(myExcelStream, false))
            {
                var sheets = sDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                var relationshipId = sheets.First().Id.Value;
                var worksheetPart = (WorksheetPart)sDoc.WorkbookPart.GetPartById(relationshipId);
                var workSheet = worksheetPart.Worksheet;
                var sheetData = workSheet.GetFirstChild<SheetData>();
                var rows = sheetData.Descendants<Row>().ToList();
                if (rows.Count == 0) return dt;

                foreach (var cell in rows.ElementAt(0).Cast<Cell>())
                {
                    dt.Columns.Add(GetCellValue(sDoc, cell));
                }

                foreach (var row in rows)
                {
                    var tempRow = dt.NewRow();

                    for (var i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        tempRow[i] = GetCellValue(sDoc, row.Descendants<Cell>().ElementAt(i));
                    }

                    dt.Rows.Add(tempRow);
                }
            }
            return dt;
        }

        public static string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            if (cell == null || cell.ChildElements.Count == 0) return null;
            var stringTablePart = document.WorkbookPart.SharedStringTablePart;
            var value = cell.CellValue.InnerText;
            if (cell.DataType != null && cell.DataType == CellValues.SharedString)
                value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            return value;
        }

Open in new window

dyaroshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
I believe that the problem is in your GetCellValue method.  Instead of returning null just return an empty string where the cell is null or the cell has no child items to resolve.

Proof of concept -

From the following SpreadSheet -Capture.JPGWith the following code -
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace EE_Q28732660
{
	class Program
	{
		static void Main(string[] args)
		{
			var myData = SpreadsheetDocument.Open("EE_Q28732660.xlsm", false).GetDataTableFromSpreadSheet();
			foreach (DataRow row in myData.Rows)
			{
				foreach (var item in row.ItemArray)
					Console.Write("{0,10}", item);
				Console.WriteLine();
			}
			Console.ReadLine();
		}
	}

	static class Extensions
	{
		public static DataTable GetDataTableFromSpreadSheet(this SpreadsheetDocument document)
		{
			var results = new DataTable();
			try
			{
				var sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
				var relationshipId = sheets.First().Id.Value;
				var part = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
				var sheet = part.Worksheet;
				var data = sheet.GetFirstChild<SheetData>();
				List<Row> rows = data.Descendants<Row>().ToList();
				if (rows.Count != 0)
				{
					foreach (var cell in rows[0].Cast<Cell>())
						results.Columns.Add(cell.GetValue(document));

					foreach (var row in rows)
						results.Rows.Add((from cell in row.Cast<Cell>() select cell.GetValue(document)).ToArray());
				}
			}
			catch (Exception)
			{
				results = new DataTable();
			}
			return results;
		}

		public static string GetValue(this Cell cell, SpreadsheetDocument document)
		{
			string result = string.Empty;
			try
			{
				if (cell != null && cell.ChildElements.Count != 0)
				{
					var part = document.WorkbookPart.SharedStringTablePart;
					if (cell.DataType != null && cell.DataType == CellValues.SharedString)
						result = part.SharedStringTable.ChildElements[Int32.Parse(cell.CellValue.InnerText)].InnerText;
					else
						result = cell.CellValue.InnerText;
				}
			}
			catch (Exception)
			{
				result = string.Empty;
			}
			return result;
		}
	}
}

Open in new window

I get the following results -Capture.JPG-saige-
0
dyaroshAuthor Commented:
Here is the test file that I am using.  I only care about the 1st 8 columns.  When I check the value of row.Descendants<Cell>().Count() when the 1st row is being processed, the number of columns is 78 which is what the header row has.

The value of row.Descendants<Cell>().Count() for rows 2 - 4 = 75, row 5 = 76, rows 6 - 7 = 75, rows 8 - 9 = 74 and row 10 = 75.

The number of blanks columns is either 3 or 4 depending on whether there is a value in Columns G and H.

Can you run your program against my test file and see if your results are ok?
TestExcelFile.xlsx
0
it_saigeDeveloperCommented:
As the program builds the datatable dynamically, yes, you only need to change the path to the file you are using...  In the meantime, I will download your test file and check against it...

-saige-
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

it_saigeDeveloperCommented:
Works fine for me as a console application.  I can apply the same code to a forms application and check against adding the data to a gridview if you would like.

-saige-
0
it_saigeDeveloperCommented:
Added a switch to the GetDataTableFromSpreadsheet method so that you can decide if you want to include the header row in the datatable or not.

Revised code -
static class Extensions
{
	public static DataTable GetDataTableFromSpreadSheet(this SpreadsheetDocument document, bool exclueHeader = true)
	{
		var results = new DataTable();
		try
		{
			var sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
			var id = sheets.First().Id.Value;
			var part = (WorksheetPart)document.WorkbookPart.GetPartById(id);
			var sheet = part.Worksheet;
			var data = sheet.GetFirstChild<SheetData>();
			var rows = data.Descendants<Row>();
			if (rows.Count() != 0)
			{
				foreach (var cell in rows.First().Cast<Cell>())
					results.Columns.Add(cell.GetValue(document));

				foreach (var row in rows.Skip(Convert.ToInt32(excludeHeader)))
					results.Rows.Add((from cell in row.Cast<Cell>() select cell.GetValue(document)).ToArray());
			}
		}
		catch (Exception)
		{
			results = new DataTable();
		}
		return results;
	}

	public static string GetValue(this Cell cell, SpreadsheetDocument document)
	{
		string result = string.Empty;
		try
		{
			if (cell != null && cell.ChildElements.Count != 0)
			{
				var part = document.WorkbookPart.SharedStringTablePart;
				if (cell.DataType != null && cell.DataType == CellValues.SharedString)
					result = part.SharedStringTable.ChildElements[Int32.Parse(cell.CellValue.InnerText)].InnerText;
				else
					result = cell.CellValue.InnerText;
			}
		}
		catch (Exception)
		{
			result = string.Empty;
		}
		return result;
	}
}

Open in new window


Proof of concept using your test data sheet:

Form1.cs -
using System;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace EE_28732660_Form
{
	public partial class Form1 : Form
	{
		public Form1()
		{
			InitializeComponent();
		}

		private void Form1_Load(object sender, EventArgs e)
		{
			var myData = SpreadsheetDocument.Open("TestExcelFile.xlsx", false).GetDataTableFromSpreadSheet();
			dataGridView1.DataSource = myData;
		}
	}

	static class Extensions
	{
		public static DataTable GetDataTableFromSpreadSheet(this SpreadsheetDocument document, bool excludeHeader = true)
		{
			var results = new DataTable();
			try
			{
				var sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
				var id = sheets.First().Id.Value;
				var part = (WorksheetPart)document.WorkbookPart.GetPartById(id);
				var sheet = part.Worksheet;
				var data = sheet.GetFirstChild<SheetData>();
				var rows = data.Descendants<Row>();
				if (rows.Count() != 0)
				{
					foreach (var cell in rows.First().Cast<Cell>())
						results.Columns.Add(cell.GetValue(document));

					foreach (var row in rows.Skip(Convert.ToInt32(excludeHeader)))
						results.Rows.Add((from cell in row.Cast<Cell>() select cell.GetValue(document)).ToArray());
				}
			}
			catch (Exception)
			{
				results = new DataTable();
			}
			return results;
		}

		public static string GetValue(this Cell cell, SpreadsheetDocument document)
		{
			string result = string.Empty;
			try
			{
				if (cell != null && cell.ChildElements.Count != 0)
				{
					var part = document.WorkbookPart.SharedStringTablePart;
					if (cell.DataType != null && cell.DataType == CellValues.SharedString)
						result = part.SharedStringTable.ChildElements[Int32.Parse(cell.CellValue.InnerText)].InnerText;
					else
						result = cell.CellValue.InnerText;
				}
			}
			catch (Exception)
			{
				result = string.Empty;
			}
			return result;
		}
	}
}

Open in new window

Form1.Designer.cs -
namespace EE_28732660_Form
{
	partial class Form1
	{
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.IContainer components = null;

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
		protected override void Dispose(bool disposing)
		{
			if (disposing && (components != null))
			{
				components.Dispose();
			}
			base.Dispose(disposing);
		}

		#region Windows Form Designer generated code

		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.dataGridView1 = new System.Windows.Forms.DataGridView();
			((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
			this.SuspendLayout();
			// 
			// dataGridView1
			// 
			this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
			this.dataGridView1.Dock = System.Windows.Forms.DockStyle.Fill;
			this.dataGridView1.Location = new System.Drawing.Point(0, 0);
			this.dataGridView1.Name = "dataGridView1";
			this.dataGridView1.RowHeadersVisible = false;
			this.dataGridView1.Size = new System.Drawing.Size(734, 261);
			this.dataGridView1.TabIndex = 0;
			// 
			// Form1
			// 
			this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
			this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
			this.ClientSize = new System.Drawing.Size(734, 261);
			this.Controls.Add(this.dataGridView1);
			this.Name = "Form1";
			this.Text = "Form1";
			this.Load += new System.EventHandler(this.Form1_Load);
			((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
			this.ResumeLayout(false);

		}

		#endregion

		private System.Windows.Forms.DataGridView dataGridView1;
	}
}

Open in new window

Produces the following output -Capture.JPGFor giggles, here is the output when the header row is included -
private void Form1_Load(object sender, EventArgs e)
{
	var myData = SpreadsheetDocument.Open("TestExcelFile.xlsx", false).GetDataTableFromSpreadSheet(false);
	dataGridView1.DataSource = myData;
}

Open in new window

Which produces this output -Capture.JPG-saige-
0
it_saigeDeveloperCommented:
And if you only care about a certain number of columns, the method can be made to return a datatable with only that number of columns; e.g. -
static class Extensions
{
	public static DataTable GetDataTableFromSpreadSheet(this SpreadsheetDocument document, int columns = -1, bool excludeHeader = true)
	{
		var results = new DataTable();
		try
		{
			var sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
			var id = sheets.First().Id.Value;
			var part = (WorksheetPart)document.WorkbookPart.GetPartById(id);
			var sheet = part.Worksheet;
			var data = sheet.GetFirstChild<SheetData>();
			var rows = data.Descendants<Row>();
			if (rows.Count() != 0)
			{
				var colCount = rows.First().Cast<Cell>().Count();
				if (columns > colCount || columns <= 0)
					columns = colCount;

				foreach (var cell in rows.First().Cast<Cell>().Take(columns))
					results.Columns.Add(cell.GetValue(document));

				foreach (var row in rows.Skip(Convert.ToInt32(excludeHeader)))
					results.Rows.Add((from cell in row.Cast<Cell>().Take(columns) select cell.GetValue(document)).ToArray());
			}
		}
		catch (Exception)
		{
			results = new DataTable();
		}
		return results;
	}

	public static string GetValue(this Cell cell, SpreadsheetDocument document)
	{
		string result = string.Empty;
		try
		{
			if (cell != null && cell.ChildElements.Count != 0)
			{
				var part = document.WorkbookPart.SharedStringTablePart;
				if (cell.DataType != null && cell.DataType == CellValues.SharedString)
					result = part.SharedStringTable.ChildElements[Int32.Parse(cell.CellValue.InnerText)].InnerText;
				else
					result = cell.CellValue.InnerText;
			}
		}
		catch (Exception)
		{
			result = string.Empty;
		}
		return result;
	}
}

Open in new window


Sample console usage (with header row and 8 columns) -
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace EE_Q28732660
{
	class Program
	{
		static void Main(string[] args)
		{
			var myData = SpreadsheetDocument.Open("TestExcelFile.xlsx", false).GetDataTableFromSpreadSheet(8, false);
			foreach (DataRow row in myData.Rows)
			{
				foreach (var item in row.ItemArray)
					Console.Write("{0,-20}", item);
				Console.WriteLine();
			}
			Console.ReadLine();
		}
	}

	static class Extensions
	{
		public static DataTable GetDataTableFromSpreadSheet(this SpreadsheetDocument document, int columns = -1, bool excludeHeader = true)
		{
			var results = new DataTable();
			try
			{
				var sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
				var id = sheets.First().Id.Value;
				var part = (WorksheetPart)document.WorkbookPart.GetPartById(id);
				var sheet = part.Worksheet;
				var data = sheet.GetFirstChild<SheetData>();
				var rows = data.Descendants<Row>();
				if (rows.Count() != 0)
				{
					var colCount = rows.First().Cast<Cell>().Count();
					if (columns > colCount || columns <= 0)
						columns = colCount;

					foreach (var cell in rows.First().Cast<Cell>().Take(columns))
						results.Columns.Add(cell.GetValue(document));

					foreach (var row in rows.Skip(Convert.ToInt32(excludeHeader)))
						results.Rows.Add((from cell in row.Cast<Cell>().Take(columns) select cell.GetValue(document)).ToArray());
				}
			}
			catch (Exception)
			{
				results = new DataTable();
			}
			return results;
		}

		public static string GetValue(this Cell cell, SpreadsheetDocument document)
		{
			string result = string.Empty;
			try
			{
				if (cell != null && cell.ChildElements.Count != 0)
				{
					var part = document.WorkbookPart.SharedStringTablePart;
					if (cell.DataType != null && cell.DataType == CellValues.SharedString)
						result = part.SharedStringTable.ChildElements[Int32.Parse(cell.CellValue.InnerText)].InnerText;
					else
						result = cell.CellValue.InnerText;
				}
			}
			catch (Exception)
			{
				result = string.Empty;
			}
			return result;
		}
	}
}

Open in new window

Sample console output -Capture.JPGSample Form usage (without header row and 8 columns) -
using System;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace EE_28732660_Form
{
	public partial class Form1 : Form
	{
		public Form1()
		{
			InitializeComponent();
		}

		private void Form1_Load(object sender, EventArgs e)
		{
			var myData = SpreadsheetDocument.Open("TestExcelFile.xlsx", false).GetDataTableFromSpreadSheet(8);
			dataGridView1.DataSource = myData;
		}
	}

	static class Extensions
	{
		public static DataTable GetDataTableFromSpreadSheet(this SpreadsheetDocument document, int columns = -1, bool excludeHeader = true)
		{
			var results = new DataTable();
			try
			{
				var sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
				var id = sheets.First().Id.Value;
				var part = (WorksheetPart)document.WorkbookPart.GetPartById(id);
				var sheet = part.Worksheet;
				var data = sheet.GetFirstChild<SheetData>();
				var rows = data.Descendants<Row>();
				if (rows.Count() != 0)
				{
					var colCount = rows.First().Cast<Cell>().Count();
					if (columns > colCount || columns <= 0)
						columns = colCount;

					foreach (var cell in rows.First().Cast<Cell>().Take(columns))
						results.Columns.Add(cell.GetValue(document));

					foreach (var row in rows.Skip(Convert.ToInt32(excludeHeader)))
						results.Rows.Add((from cell in row.Cast<Cell>().Take(columns) select cell.GetValue(document)).ToArray());
				}
			}
			catch (Exception)
			{
				results = new DataTable();
			}
			return results;
		}

		public static string GetValue(this Cell cell, SpreadsheetDocument document)
		{
			string result = string.Empty;
			try
			{
				if (cell != null && cell.ChildElements.Count != 0)
				{
					var part = document.WorkbookPart.SharedStringTablePart;
					if (cell.DataType != null && cell.DataType == CellValues.SharedString)
						result = part.SharedStringTable.ChildElements[Int32.Parse(cell.CellValue.InnerText)].InnerText;
					else
						result = cell.CellValue.InnerText;
				}
			}
			catch (Exception)
			{
				result = string.Empty;
			}
			return result;
		}
	}
}

Open in new window

Sample form output -Capture.JPG-saige-
0
dyaroshAuthor Commented:
Your output has the same problem that I'm having.  The values in the Submitted Date column should be in the Pol # column.  Because the Submitted Date isn't present, the data is shifted one column.  For example, the first 8 columns should look like this:

RefTypeID    Submitted Pol #           ST     Referral Category    Agent       Loc              Sup
                      Date
110                                   11111111   TX     Referral Category    Internet        
110                                   11111111   FL     Referral Category    Internet   Lake Mary   Supervisor
0
it_saigeDeveloperCommented:
My mistake.  Not a problem though, we will just implement a couple of additional helper methods:
static class Extensions
{
	/// <summary>Gets the data table from spread sheet.</summary>
	/// <param name="document">The document.</param>
	/// <param name="columns">The columns.</param>
	/// <param name="excludeHeader">if set to <c>true</c> [exclude header].</param>
	/// <returns>DataTable.</returns>
	public static DataTable GetDataTableFromSpreadSheet(this SpreadsheetDocument document, int columns = -1, bool excludeHeader = true)
	{
		var results = new DataTable();
		try
		{
			var sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
			var id = sheets.First().Id.Value;
			var part = (WorksheetPart)document.WorkbookPart.GetPartById(id);
			var sheet = part.Worksheet;
			var data = sheet.GetFirstChild<SheetData>();
			var rows = data.Descendants<Row>();
			if (rows.Count() != 0)
			{
				var colCount = rows.First().Cast<Cell>().Count();
				if (columns > colCount || columns <= 0)
					columns = colCount;

				foreach (var cell in rows.First().Cast<Cell>().Take(columns))
					results.Columns.Add(cell.GetValue(document));

				foreach (var row in rows.Skip(Convert.ToInt32(excludeHeader)))
					results.Rows.Add((from cell in row.GetCells().Take(columns) select cell.GetValue(document)).ToArray());
			}
		}
		catch (Exception)
		{
			results = new DataTable();
		}
		return results;
	}

	/// <summary>Gets the value.</summary>
	/// <param name="cell">The cell.</param>
	/// <param name="document">The document.</param>
	/// <returns>System.String.</returns>
	public static string GetValue(this Cell cell, SpreadsheetDocument document)
	{
		string result = string.Empty;
		try
		{
			if (cell != null && cell.ChildElements.Count != 0)
			{
				var part = document.WorkbookPart.SharedStringTablePart;
				if (cell.DataType != null && cell.DataType == CellValues.SharedString)
					result = part.SharedStringTable.ChildElements[Int32.Parse(cell.CellValue.InnerText)].InnerText;
				else
					result = cell.CellValue.InnerText;
			}
		}
		catch (Exception)
		{
			result = string.Empty;
		}
		return result;
	}

	/// <summary>Gets the cells from a row.</summary>
	/// <param name="row">The row.</param>
	/// <returns>IEnumerable&lt;Cell&gt;.</returns>
	public static IEnumerable<Cell> GetCells(this Row row)
	{
		int count = 0;
		foreach (Cell cell in row.Descendants<Cell>())
		{
			int index = cell.CellReference.GetColumnName().ConvertColumnNameToNumber();
			for (; count < index; count++)
			{
				// Null or empty cell reference encountered, replace with a new cell that contains a default value.
				yield return new Cell() { DataType = null, CellValue = new CellValue(string.Empty) };
			}
			yield return cell;
			count++;
		}
	}

	/// <summary>Gets the name of the column.</summary>
	/// <param name="reference">The cell reference.</param>
	/// <returns>System.String.</returns>
	private static string GetColumnName(this StringValue reference)
	{
		return new Regex("(?i)[a-z]+").Match(reference).Value;
	}

	/// <summary>Converts the column name to number.</summary>
	/// <param name="name">The name.</param>
	/// <returns>System.Int32.</returns>
	/// <exception cref="System.ArgumentException"></exception>
	private static int ConvertColumnNameToNumber(this string name)
	{
		var expression = new Regex("^[A-Z]+$");
		if (!expression.IsMatch(name))
			throw new ArgumentException();

		char[] letters = name.ToCharArray();
		Array.Reverse(letters);

		int result = 0;
		for (int i = 0; i < letters.Length; i++)
			result += ((i == 0) ? letters[i] - 65 : letters[i] - 64) * (int)Math.Pow(26, i);
		return result;
	}
}

Open in new window

Example usage -
using System;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using System.Text.RegularExpressions;
using System.Collections.Generic;

namespace EE_28732660_Form
{
	public partial class Form1 : Form
	{
		public Form1()
		{
			InitializeComponent();
		}

		private void Form1_Load(object sender, EventArgs e)
		{
			var myData = SpreadsheetDocument.Open("TestExcelFile.xlsx", false).GetDataTableFromSpreadSheet(8);
			dataGridView1.DataSource = myData;
		}
	}

	static class Extensions
	{
		/// <summary>Gets the data table from spread sheet.</summary>
		/// <param name="document">The document.</param>
		/// <param name="columns">The columns.</param>
		/// <param name="excludeHeader">if set to <c>true</c> [exclude header].</param>
		/// <returns>DataTable.</returns>
		public static DataTable GetDataTableFromSpreadSheet(this SpreadsheetDocument document, int columns = -1, bool excludeHeader = true)
		{
			var results = new DataTable();
			try
			{
				var sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
				var id = sheets.First().Id.Value;
				var part = (WorksheetPart)document.WorkbookPart.GetPartById(id);
				var sheet = part.Worksheet;
				var data = sheet.GetFirstChild<SheetData>();
				var rows = data.Descendants<Row>();
				if (rows.Count() != 0)
				{
					var colCount = rows.First().Cast<Cell>().Count();
					if (columns > colCount || columns <= 0)
						columns = colCount;

					foreach (var cell in rows.First().Cast<Cell>().Take(columns))
						results.Columns.Add(cell.GetValue(document));

					foreach (var row in rows.Skip(Convert.ToInt32(excludeHeader)))
						results.Rows.Add((from cell in row.GetCells().Take(columns) select cell.GetValue(document)).ToArray());
				}
			}
			catch (Exception)
			{
				results = new DataTable();
			}
			return results;
		}

		/// <summary>Gets the value.</summary>
		/// <param name="cell">The cell.</param>
		/// <param name="document">The document.</param>
		/// <returns>System.String.</returns>
		public static string GetValue(this Cell cell, SpreadsheetDocument document)
		{
			string result = string.Empty;
			try
			{
				if (cell != null && cell.ChildElements.Count != 0)
				{
					var part = document.WorkbookPart.SharedStringTablePart;
					if (cell.DataType != null && cell.DataType == CellValues.SharedString)
						result = part.SharedStringTable.ChildElements[Int32.Parse(cell.CellValue.InnerText)].InnerText;
					else
						result = cell.CellValue.InnerText;
				}
			}
			catch (Exception)
			{
				result = string.Empty;
			}
			return result;
		}

		/// <summary>Gets the cells from a row.</summary>
		/// <param name="row">The row.</param>
		/// <returns>IEnumerable&lt;Cell&gt;.</returns>
		public static IEnumerable<Cell> GetCells(this Row row)
		{
			int count = 0;
			foreach (Cell cell in row.Descendants<Cell>())
			{
				int index = cell.CellReference.GetColumnName().ConvertColumnNameToNumber();
				for (; count < index; count++)
				{
					// Null or empty cell reference encountered, replace with a new cell that contains a default value.
					yield return new Cell() { DataType = null, CellValue = new CellValue(string.Empty) };
				}
				yield return cell;
				count++;
			}
		}

		/// <summary>Gets the name of the column.</summary>
		/// <param name="reference">The cell reference.</param>
		/// <returns>System.String.</returns>
		private static string GetColumnName(this StringValue reference)
		{
			return new Regex("(?i)[a-z]+").Match(reference).Value;
		}

		/// <summary>Converts the column name to number.</summary>
		/// <param name="name">The name.</param>
		/// <returns>System.Int32.</returns>
		/// <exception cref="System.ArgumentException"></exception>
		private static int ConvertColumnNameToNumber(this string name)
		{
			var expression = new Regex("^[A-Z]+$");
			if (!expression.IsMatch(name))
				throw new ArgumentException();

			char[] letters = name.ToCharArray();
			Array.Reverse(letters);

			int result = 0;
			for (int i = 0; i < letters.Length; i++)
				result += ((i == 0) ? letters[i] - 65 : letters[i] - 64) * (int)Math.Pow(26, i);
			return result;
		}
	}
}

Open in new window

Produces the following output -Capture.JPG-saige-
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dyaroshAuthor Commented:
Thank you for all of your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.