/***********************************
* DAL METHODS
************************************/
// Read
internal static HeaderFooter ReadHeaderFooterFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
HeaderFooter headerFooter = new HeaderFooter(worksheet);
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "alignWithMargins":
headerFooter.AlignWithMargins = attribute.GetBoolValue();
break;
default:
throw new Exception(string.Format("HeaderFooter attribute {0} not coded", attribute.LocalName));
}
}
while (reader.ReadToEndElement<OpenXmlSpreadsheet.HeaderFooter>())
{
if (reader.IsStartElementOfType<OpenXmlSpreadsheet.OddHeader>())
headerFooter.OddHeader = reader.GetText();
else if (reader.IsStartElementOfType<OpenXmlSpreadsheet.OddFooter>())
headerFooter.OddFooter = reader.GetText();
}
return headerFooter;
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static PrintOptions ReadPrintOptionsFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
PrintOptions printOptions = new PrintOptions(worksheet);
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "horizontalCentered":
printOptions.HorizontalCentered = attribute.GetBoolValue();
break;
case "verticalCentered":
printOptions.VerticalCentered = attribute.GetBoolValue();
break;
case "gridLines":
printOptions.GridLines = attribute.GetBoolValue();
break;
case "headings":
printOptions.Headings = attribute.GetBoolValue();
break;
default:
throw new Exception(string.Format("PrintOptions attribute {0} not coded", attribute.LocalName));
}
}
return printOptions;
}
private static void UpdateCellHyperlinkFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
string address = "";
string display = "";
string location = "";
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "ref":
address = attribute.Value;
break;
case "display":
display = attribute.Value;
break;
case "location":
location = attribute.Value;
break;
}
}
Cell cell = worksheet.Cells[address];
Cell targetCell = GetTargetCellByTargetLocation(worksheet, location);
if (cell != null && targetCell != null)
{
cell.HyperlinkToCell = targetCell;
cell.Value = display;
}
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static void UpdateCellHyperlinksFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
while (reader.ReadToEndElement<OpenXmlSpreadsheet.Hyperlinks>())
{
if (reader.IsStartElementOfType<OpenXmlSpreadsheet.Hyperlink>())
UpdateCellHyperlinkFromReader(reader, worksheet);
}
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static SheetViews ReadSheetViewsFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
SheetViews sheetViews = new SheetViews(worksheet);
while (reader.ReadToEndElement<OpenXmlSpreadsheet.SheetViews>())
{
// only read the first sheetView element.
if (reader.IsEndElementOfType<OpenXmlSpreadsheet.SheetView>())
break;
if (reader.IsStartElementOfType<OpenXmlSpreadsheet.SheetView>())
{
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "tabSelected":
sheetViews.Selected = attribute.GetBoolValue();
break;
}
}
}
else if (reader.IsStartElementOfType<OpenXmlSpreadsheet.Pane>())
{
int ySplit = 0;
int xSplit = 0;
bool isFrozen = false;
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "ySplit":
ySplit = attribute.GetIntValue();
break;
case "xSplit":
xSplit = attribute.GetIntValue();
break;
case "state":
isFrozen = (attribute.Value == "frozen");
break;
}
}
if (isFrozen)
{
if (ySplit > 0) sheetViews.FrozenRow = ySplit;
if (xSplit > 0) sheetViews.FrozenRow = xSplit;
}
}
}
return sheetViews;
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static WorksheetFormat ReadWorksheetFormatFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
WorksheetFormat worksheetFormat = new WorksheetFormat(worksheet);
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "defaultRowHeight":
worksheetFormat.DefaultRowHeight = attribute.GetDoubleValue();
break;
}
}
return worksheetFormat;
}
internal static Drawing ReadDrawingFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
string relationshipId = "";
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "id":
relationshipId = attribute.Value;
break;
}
}
return new Drawing(worksheet, relationshipId);
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static Row ReadRowFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
Row row = new Row(worksheet);
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "r":
row.Index = attribute.GetIntValue();
break;
case "s":
row.StyleIndex = attribute.GetIntValue();
break;
case "ht":
row.Height = attribute.GetDoubleValue();
break;
}
}
row.Cells = CellCollection.ReadCellsFromReader(reader, row);
return row;
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static PageSetup ReadPageSetupFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
PageSetup pageSetup = new PageSetup(worksheet);
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "blackAndWhite":
pageSetup.BlackAndWhite = attribute.GetBoolValue();
break;
case "copies":
pageSetup.Copies = attribute.GetIntValue();
break;
case "draft":
pageSetup.Draft = attribute.GetBoolValue();
break;
case "firstPageNumber":
pageSetup.FirstPageNumber = attribute.GetIntValue();
break;
case "fitToHeight":
pageSetup.FitToHeight = attribute.GetIntValue();
break;
case "fitToWidth":
pageSetup.FitToWidth = attribute.GetIntValue();
break;
case "horizontalDpi":
pageSetup.HorizontalDpi = attribute.GetIntValue();
break;
case "verticalDpi":
pageSetup.VerticalDpi = attribute.GetIntValue();
break;
case "id":
pageSetup.RelationshipId = attribute.Value;
break;
case "orientation":
switch (attribute.Value)
{
case "landscape":
pageSetup.Orientation = WorksheetOrientation.Landscape;
break;
case "portrait":
pageSetup.Orientation = WorksheetOrientation.Portrait;
break;
}
break;
case "paperSize":
pageSetup.PaperSize = attribute.GetIntValue();
break;
case "scale":
pageSetup.Scale = attribute.GetIntValue();
break;
case "useFirstPageNumber":
pageSetup.UseFirstPageNumber = attribute.GetBoolValue();
break;
case "usePrinterDefaults":
pageSetup.UsePrinterDefaults = attribute.GetBoolValue();
break;
default:
throw new Exception(string.Format("PageSetup attribute {0} not coded", attribute.LocalName));
}
}
return pageSetup;
}
private static Size GetSizeFromReader(CustomOpenXmlReader reader)
{
Size size = new Size();
size.Width = reader.Attributes["cx"].GetIntValue();
size.Height = reader.Attributes["cy"].GetIntValue();
return size;
}
private static Point GetPositionFromReader(CustomOpenXmlReader reader)
{
Point position = new Point();
position.X = reader.Attributes["x"].GetIntValue();
position.Y = reader.Attributes["y"].GetIntValue();
return position;
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static Picture ReadPictureFromReader(CustomOpenXmlReader reader, Drawing drawing)
{
string filePath = "";
string description = "";
string name = "";
int id = 0;
string blipRelationshipId = "";
Size size = new Size();
Point position = new Point();
while (reader.ReadToEndElement<OpenXmlDrawingSpreadsheet.Picture>())
{
if (reader.IsStartElementOfType<OpenXmlDrawingSpreadsheet.NonVisualPictureProperties>())
{
while (reader.ReadToEndElement<OpenXmlDrawingSpreadsheet.NonVisualPictureProperties>())
{
if (reader.IsStartElementOfType<OpenXmlDrawingSpreadsheet.NonVisualDrawingProperties>())
{
description = OpenXmlUtilities.GetAttributeValueFromReader(reader, "descr");
name = OpenXmlUtilities.GetAttributeValueFromReader(reader, "name");
id = reader.Attributes["id"].GetIntValue();
}
}
}
else if (reader.IsStartElementOfType<OpenXmlDrawingSpreadsheet.BlipFill>())
{
while (reader.ReadToEndElement<OpenXmlDrawingSpreadsheet.BlipFill>())
{
if (reader.IsStartElementOfType<OpenXmlDrawing.Blip>())
{
blipRelationshipId = reader.Attributes["embed"].Value;
filePath = GetFilePathFromBlip(reader, drawing, blipRelationshipId);
}
}
}
else if (reader.IsStartElementOfType<OpenXmlDrawingSpreadsheet.ShapeProperties>())
{
while (reader.ReadToEndElement<OpenXmlDrawingSpreadsheet.ShapeProperties>())
{
if (reader.IsStartElementOfType<OpenXmlDrawing.Offset>())
position = GetPositionFromReader(reader);
else if (reader.IsStartElementOfType<OpenXmlDrawing.Extents>())
size = GetSizeFromReader(reader);
}
}
}
return new Picture(drawing, filePath, description, name, id, size, position, blipRelationshipId);//, sourceRectangle);
}
private static object GetCellValueFromReader(CustomOpenXmlReader reader, CellDataType cellDataType, Row row)
{
object cellValue = null;
while (reader.ReadToEndElement<OpenXmlSpreadsheet.Cell>())
{
if (reader.IsStartElementOfType<OpenXmlSpreadsheet.CellValue>())
{
string rawValue = reader.GetText();
if (rawValue != "")
{
switch (cellDataType)
{
case CellDataType.Boolean:
cellValue = (rawValue == "1" ? true : false);
break;
case CellDataType.SharedString:
int sharedStringIndex = int.Parse(rawValue);
cellValue = row.Worksheet.Workbook.SharedStrings[sharedStringIndex];
break;
case CellDataType.Number:
double doubleValue = 0;
if (double.TryParse(rawValue, out doubleValue))
cellValue = doubleValue;
break;
case CellDataType.String:
cellValue = rawValue.ToString();
break;
default:
throw new ArgumentOutOfRangeException();
}
}
}
}
return cellValue;
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static Cell ReadCellFromReader(CustomOpenXmlReader reader, Row row)
{
CellDataType cellDataType = CellDataType.Number;
int styleIndex = CellFormat.DefaultStyleIndex;
string address = "";
object value = null;
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "t":
cellDataType = GetCellDataTypeFromAttributeValue(attribute.Value);
break;
case "s":
styleIndex = attribute.GetIntValue();
break;
case "r":
address = attribute.Value;
break;
}
}
value = GetCellValueFromReader(reader, cellDataType, row);
if (value == null)
cellDataType = CellDataType.Blank;
// Address doesn't technically need to be included. Needs handling, but keep simple for now.
int columnIndex = ExcelUtilities.GetColumnIndexConverter().GetColumnIndexFromCellAddress(address);
Cell cell = new Cell(row, row.Worksheet.Columns[columnIndex], value, cellDataType, styleIndex);
return cell;
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static PageMargins ReadPageMarginsFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
PageMargins pageMargins = new PageMargins(worksheet);
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "left":
pageMargins.Left = attribute.GetDoubleValue();
break;
case "right":
pageMargins.Right = attribute.GetDoubleValue();
break;
case "top":
pageMargins.Top = attribute.GetDoubleValue();
break;
case "bottom":
pageMargins.Bottom = attribute.GetDoubleValue();
break;
case "header":
pageMargins.Header = attribute.GetDoubleValue();
break;
case "footer":
pageMargins.Footer = attribute.GetDoubleValue();
break;
default:
throw new Exception(string.Format("PageMargins attribute {0} not coded", attribute.LocalName));
}
}
return pageMargins;
}
/***********************************
* PRIVATE METHODS
************************************/
/***********************************
* DAL METHODS
************************************/
// Read
internal static ColumnRange ReadColumnRangeFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
int min = 0;
int max = 0;
int styleIndex = CellFormat.DefaultStyleIndex;
double width = -1;
bool isBestFit = false;
bool isCustomWidth = false;
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "min":
min = attribute.GetIntValue();
break;
case "max":
max = attribute.GetIntValue();
break;
case "style":
styleIndex = attribute.GetIntValue();
break;
case "width":
width = attribute.GetDoubleValue();
break;
case "bestFit":
isBestFit = attribute.GetBoolValue();
break;
case "customWidth":
isCustomWidth = attribute.GetBoolValue();
break;
}
}
ColumnRange columnRange = new ColumnRange(worksheet, min, max, styleIndex, width, isBestFit, isCustomWidth);
return columnRange;
}
private static string GetFilePathFromBlip(CustomOpenXmlReader reader, Drawing drawing, string relationshipId)
{
OpenXmlPackaging.OpenXmlPart mediaPart = Picture.GetMediaPartFromDrawing(drawing, relationshipId);
if (mediaPart != null)
return mediaPart.Uri.ToString();
return "";
}
private static void UpdateCellMergeCellFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
// ignore reading in merged cells for the time being.
//string range = "";
//foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
//{
// switch (attribute.LocalName)
// {
// case "ref":
// range = attribute.Value;
// break;
// }
//}
//string[] cellAddresses = range.Split(',');
//Cell cellFrom = worksheet.Cells[cellAddresses[0]];
//Cell cellTo = worksheet.Cells[cellAddresses[1]];
}
