I am using the Open XML SDK to open an Excel file to retrieve the cell values from all of the rows and columns that contain data within the worksheet. Here is my code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace ReadingExcel
{
class RetrieveListOfExcelValues
{
static void Main(string[] args)
{
String fileName = @"C:\Users\Documents\TestReadingExcel.xlsx";
// Comment one of the following lines to test the method separately.
ReadExcelFileDOM(fileName); // DOM
} //end Main
static void ReadExcelFileDOM(string fileName)
{
// Open the spreadsheet document for read-only access.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
// Retrieve a reference to the workbook part.
WorkbookPart wbPart = document.WorkbookPart;
//Worksheet name that is the the workbook
string sheetName = "Sheet1";
// Find the sheet with the supplied name, and then use that
// Sheet object to retrieve a reference to the first worksheet.
Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
Where(s => s.Name == sheetName).FirstOrDefault();
// Throw an exception if there is no sheet.
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
// Retrieve a reference to the worksheet part.
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
string parseCellValue;
foreach (Row r in theSheet.Elements<Row>())
{
foreach (Cell c in r.Elements<Cell>())
{
Cell theCell = wsPart.Worksheet.Descendants<Cell>().FirstOrDefault();
parseCellValue = GetCellValue(theCell, wbPart);
Console.Write(parseCellValue + " ");
//text = c.CellValue.Text;
//Console.Write(text + " ");
}
}
Console.WriteLine();
Console.ReadKey();
}
} //end ReadExcelFileDOMMethod
public static string GetCellValue(Cell cell, WorkbookPart wbPart)
{
string value = null;
if (cell != null)
{
value = cell.InnerText;
// If the cell represents an integer number, you are done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and
// Booleans individually. For shared strings, the code
// looks up the corresponding value in the shared string
// table. For Booleans, the code converts the value into
// the words TRUE or FALSE.
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the
// shared strings table.
var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>()
.FirstOrDefault();
// If the shared string table is missing, something
// is wrong. Return the index that is in
// the cell. Otherwise, look up the correct text in
// the table.
if (stringTable != null)
{
value = stringTable.SharedStringTable.ElementAt(
int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value =
"FALSE";
break;
default:
value =
"TRUE";
break;
}
break;
}
}
}
return value;
}
}
}
The text does not display on the console and I cannot find out exactly what I am doing wrong in this code. This is my first attempt at using Open XML. I may be overlooking something or few lines of code might need to be moved around.
How I can get the all of the cell values from the rows and columns within the worksheet to display on the console?
Excel Format
A1 B1 C1
Fruit Lot Date
Banana 4 4/13/2014
Orange 6 5/01/2014
Apple 9 3/14/2014
Best Answer
Give this a try