/// <summary>
/// Spreadsheet vertical coord range
/// </summary>
/// <param name="min"></param>
/// <param name="max"></param>
public SpreadsheetVerticalCoordinatesRangeAttribute(int min, int max)
{
Min = min;
Max = max;
MinIndex = SpreadsheetHelpers.GetVerticalCoordinateIndex(Min);
MaxIndex = SpreadsheetHelpers.GetVerticalCoordinateIndex(Max);
}
/// <summary>
/// Spreadsheet horizontal coord range
/// </summary>
/// <param name="min"></param>
/// <param name="max"></param>
public SpreadsheetHorizontalCoordinatesRangeAttribute(string min, string max)
{
Min = min;
Max = max;
MinIndex = SpreadsheetHelpers.GetHorizontalCoordinateIndex(Min);
MaxIndex = SpreadsheetHelpers.GetHorizontalCoordinateIndex(Max);
}
public void GetHorizontalCoordinateIndexAA()
{
// Z = 26, AA = 26 + 1
var val = SpreadsheetHelpers.GetHorizontalCoordinateIndex("AA");
Assert.AreEqual(26, val);
}
public void GetHorizontalCoordinateIndexB()
{
// B = 2
var val = SpreadsheetHelpers.GetHorizontalCoordinateIndex("B");
Assert.AreEqual(1, val);
}
public void GetHorizontalCoordinateIndexA()
{
// A = 1
var val = SpreadsheetHelpers.GetHorizontalCoordinateIndex("A");
Assert.AreEqual(0, val);
}
public void GetHorizontalCoordinateIndexLowerCase()
{
var val = SpreadsheetHelpers.GetHorizontalCoordinateIndex("a");
Assert.AreEqual(0, val);
val = SpreadsheetHelpers.GetHorizontalCoordinateIndex("b");
Assert.AreEqual(1, val);
}
public void GetVerticalCoordinateIndexPossitive()
{
var val = SpreadsheetHelpers.GetVerticalCoordinateIndex(1);
Assert.AreEqual(0, val);
val = SpreadsheetHelpers.GetVerticalCoordinateIndex(2);
Assert.AreEqual(1, val);
}
static void Main(string[] args)
{
string filePath = @"C:\Source\CSharp\Office\OpenXML\Sample.xlsx";
SheetData sheetData = null;
SharedStringItem item = null;
WorkbookPart workbookPart = null;
Workbook workbook = null;
WorksheetPart worksheetPart = null;
StringBuilder text = new StringBuilder();
string sheetId = String.Empty;
int id = 0;
#region Without Library
try
{
// Open a SpreadsheetDocument for read-only access based on a filepath.
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
{
workbookPart = spreadsheetDocument.WorkbookPart;
workbook = workbookPart.Workbook;
Console.WriteLine("Found the following worksheets: ");
foreach (var sheet in workbook.Sheets)
{
Console.WriteLine($"\t{((Sheet)sheet).Name}");
if (((Sheet)sheet).Name == "Sheet1")
{
sheetId = ((Sheet)sheet).Id;
}
}
// Print Sheet 1
Console.WriteLine("\nContents of Sheet1:");
worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheetId);
sheetData = worksheetPart.Worksheet.Elements <SheetData>().FirstOrDefault();
foreach (Row r in sheetData.Elements <Row>())
{
text.Append("\t|");
foreach (Cell c in r.Elements <Cell>())
{
if (c.DataType != null)
{
switch (c.DataType.Value)
{
case CellValues.Boolean:
text.Append(Convert.ToInt32(c.InnerText)).Append("|");
break;
case CellValues.SharedString:
if (Int32.TryParse(c.InnerText, out id))
{
item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
text.Append(item.Text.Text).Append("|");
}
else if (item.InnerText != null)
{
text.Append(item.InnerText).Append("|");
}
else if (item.InnerXml != null)
{
text.Append(item.InnerXml).Append("|");
}
}
break;
case CellValues.String:
Console.WriteLine("It's a string");
break;
case CellValues.Date:
Console.WriteLine("It's a date");
break;
case CellValues.Number:
Console.WriteLine("It's a number");
break;
}
}
else
{
text.Append(Convert.ToDecimal(c.InnerText)).Append("|");
}
}
Console.WriteLine($"{text}");
text.Clear();
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception: {ex.Message}");
}
#endregion
#region With Library
try
{
// Open a SpreadsheetDocument for read-only access based on a filepath.
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
{
workbookPart = spreadsheetDocument.WorkbookPart;
workbook = workbookPart.Workbook;
Console.WriteLine("Found the following worksheets: ");
foreach (var sheet in workbook.Sheets)
{
Console.WriteLine($"\t{((Sheet)sheet).Name}");
if (((Sheet)sheet).Name == "Sheet1")
{
sheetId = ((Sheet)sheet).Id;
}
}
// Print Sheet 1
text.Clear();
Console.WriteLine("\nContents of Sheet1:");
worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheetId);
sheetData = SpreadsheetHelpers.GetSheetData(worksheetPart).FirstOrDefault();
foreach (Row r in SpreadsheetHelpers.GetRows(sheetData))
{
text.Append("\t|");
foreach (Cell c in SpreadsheetHelpers.GetCells(r))
{
if (c.DataType != null)
{
switch (c.DataType.Value)
{
case CellValues.Boolean:
text.Append(Convert.ToInt32(c.InnerText)).Append("|");
break;
case CellValues.SharedString:
if (Int32.TryParse(c.InnerText, out id))
{
item = SpreadsheetHelpers.GetSharedStringTable(workbookPart, id);
if (item.Text != null)
{
text.Append(item.Text.Text).Append("|");
}
else if (item.InnerText != null)
{
text.Append(item.InnerText).Append("|");
}
else if (item.InnerXml != null)
{
text.Append(item.InnerXml).Append("|");
}
}
break;
case CellValues.String:
Console.WriteLine("It's a string");
break;
case CellValues.Date:
Console.WriteLine("It's a date");
break;
case CellValues.Number:
Console.WriteLine("It's a number");
break;
}
}
else
{
text.Append(Convert.ToDecimal(c.InnerText)).Append("|");
}
}
Console.WriteLine($"{text}");
text.Clear();
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception: {ex.Message}");
}
#endregion
Console.WriteLine("Press <enter> to end....");
Console.ReadLine();
}
/// <summary>
/// Spreadsheet vertical coord
/// </summary>
/// <param name="value"></param>
public SpreadsheetVerticalCoordinateAttribute(int value)
{
Value = value;
ValueIndex = SpreadsheetHelpers.GetVerticalCoordinateIndex(Value);
}
/// <summary>
/// Spreadsheet horizontal cord
/// </summary>
/// <param name="value"></param>
public SpreadsheetHorizontalCoordinateAttribute(string value)
{
Value = value;
ValueIndex = SpreadsheetHelpers.GetHorizontalCoordinateIndex(Value);
}
public void GetHorizontalCoordinateIndexInvalidChar()
{
Assert.ThrowsException <ArgumentOutOfRangeException>(() => SpreadsheetHelpers.GetHorizontalCoordinateIndex("-"));
Assert.ThrowsException <ArgumentOutOfRangeException>(() => SpreadsheetHelpers.GetHorizontalCoordinateIndex(""));
Assert.ThrowsException <ArgumentOutOfRangeException>(() => SpreadsheetHelpers.GetHorizontalCoordinateIndex("ř"));
}
public void GetVerticalCoordinateIndexNegative()
{
Assert.ThrowsException <ArgumentOutOfRangeException>(() => SpreadsheetHelpers.GetVerticalCoordinateIndex(-1));
}
