/// <summary>
/// Imports a excel xml workbook into a ExcelXmlWorkbook instance
/// </summary>
/// <param name="importFile">File to import</param>
/// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
public static ExcelXmlWorkbook Import(string importFile)
{
if (!File.Exists(importFile))
{
return(null);
}
Stream stream;
try
{
stream = new FileStream(importFile, FileMode.Open, FileAccess.Read);
}
catch (IOException)
{
return(null);
}
catch (SecurityException)
{
return(null);
}
catch (UnauthorizedAccessException)
{
return(null);
}
ExcelXmlWorkbook book = Import(stream);
stream.Close();
stream.Dispose();
return(book);
}
internal static void ImportNamedRanges(XmlReader reader, ExcelXmlWorkbook book, Worksheet ws)
{
if (!reader.IsEmptyElement)
{
while (reader.Read() && !(reader.Name == "Names" && reader.NodeType == XmlNodeType.EndElement))
{
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name == "NamedRange")
{
Range range = null;
string name = "";
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
if (xa.LocalName == "Name" && xa.HasValue)
{
name = xa.Value;
}
if (xa.LocalName == "RefersTo" && xa.HasValue)
{
range = new Range(xa.Value);
}
}
NamedRange nr = new NamedRange(range, name, ws);
book.NamedRanges.Add(nr);
}
}
}
}
}
internal Column(Worksheet parent)
{
if (parent == null)
{
throw new ArgumentNullException("parent");
}
ParentBook = parent.ParentBook;
}
internal string RangeReference(Cell cell)
{
if (CellFrom == null)
{
return(UnresolvedRangeReference);
}
if (CellFrom.ParentRow == null)
{
return("#N/A");
}
if (CellTo != null && CellTo.ParentRow == null)
{
return("#N/A");
}
if (cell == null)
{
throw new ArgumentNullException("cell");
}
string range;
if (Absolute)
{
range = AbsoluteReference();
}
else
{
if (CellTo != null)
{
range = String.Format(CultureInfo.InvariantCulture, "R[{0}]C[{1}]:R[{2}]C[{3}]", CellFrom.ParentRow.RowIndex - cell.ParentRow.RowIndex, CellFrom.CellIndex - cell.CellIndex, CellTo.ParentRow.RowIndex - cell.ParentRow.RowIndex, CellTo.CellIndex - cell.CellIndex);
}
else
{
range = String.Format(CultureInfo.InvariantCulture, "R[{0}]C[{1}]", CellFrom.ParentRow.RowIndex - cell.ParentRow.RowIndex, CellFrom.CellIndex - cell.CellIndex);
}
}
string sheetReference = "";
if (CellFrom.ParentRow.ParentSheet != cell.ParentRow.ParentSheet)
{
sheetReference = CellFrom.ParentRow.ParentSheet.Name;
ExcelXmlWorkbook workBook = CellFrom.GetParentBook();
if (workBook != cell.GetParentBook())
{
throw new ArgumentException("External workbook references are not supported");
}
}
if (!sheetReference.IsNullOrEmpty())
{
range = "'" + sheetReference + "'!" + range;
}
return(range);
}
internal Worksheet(ExcelXmlWorkbook parent)
{
if (parent == null)
{
throw new ArgumentNullException("parent");
}
ParentBook = parent;
PrintOptions = new PrintOptions();
PrintOptions.Layout = PageLayout.None;
PrintOptions.Orientation = PageOrientation.None;
_Rows = new List <Row>();
_Columns = new List <Column>();
_MergedCells = new List <Range>();
TabColor = -1;
PrintOptions.FitHeight = 1;
PrintOptions.FitWidth = 1;
PrintOptions.Scale = 100;
PrintOptions.ResetMargins();
}
internal void Import(XmlReader reader)
{
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
if (xa.LocalName == "Name" && xa.HasValue)
{
Name = xa.Value;
}
if (xa.LocalName == "StyleID" && xa.HasValue)
{
Style = ParentBook.GetStyleByID(xa.Value);
}
}
while (reader.Read() && !(reader.Name == "Worksheet" && reader.NodeType == XmlNodeType.EndElement))
{
if (reader.NodeType == XmlNodeType.Element)
{
switch (reader.Name)
{
case "Names":
{
ExcelXmlWorkbook.ImportNamedRanges(reader, GetParentBook(), this);
break;
}
case "Table":
{
ImportTable(reader);
break;
}
case "WorksheetOptions":
{
ImportOptions(reader);
break;
}
}
}
}
}
/// <summary>
/// Imports a excel xml workbook into a ExcelXmlWorkbook instance
/// </summary>
/// <param name="stream">Stream to import</param>
/// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
public static ExcelXmlWorkbook Import(Stream stream)
{
XmlReaderSettings settings = new XmlReaderSettings();
settings.CloseInput = false;
settings.IgnoreComments = true;
settings.IgnoreProcessingInstructions = true;
settings.IgnoreWhitespace = true;
if (!stream.CanRead)
{
return(null);
}
XmlReader reader = XmlReader.Create(stream, settings);
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
book.Styles.Clear();
int workSheet = 0;
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element)
{
switch (reader.Name)
{
case "DocumentProperties":
{
if (!reader.IsEmptyElement)
{
book.Properties.Import(reader);
}
break;
}
case "Styles":
{
if (!reader.IsEmptyElement)
{
book.ImportStyles(reader);
}
break;
}
case "Names":
{
ImportNamedRanges(reader, book, null);
break;
}
case "Worksheet":
{
if (!reader.IsEmptyElement)
{
book[workSheet++].Import(reader);
}
break;
}
}
}
}
book.ResolveNamedRangeReferences();
book.ResolveCellReferences();
reader.Close();
stream.Close();
stream.Dispose();
return(book);
}
/// <summary>
/// Converts a dataset to a work book
/// </summary>
/// <param name="source">The source dataset to convert to a work book</param>
/// <returns>Returns the <see cref="Colosoft.Excel.ExcelXml.ExcelXmlWorkbook"/>
/// for the dataset.</returns>
/// <remarks>All the tables are converted into sheets with sheet names as table + table number,
/// eg. "Table0" "Table1" etc. Supported types which can be successfully
/// converted to cells are the same as described in <see cref="Colosoft.Excel.ExcelXml.Cell"/>
/// except <see cref="Colosoft.Excel.ExcelXml.Cell"/> and
/// <see cref="Colosoft.Excel.ExcelXml.Formula"/></remarks>
public static ExcelXmlWorkbook DataSetToWorkbook(DataSet source)
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
for (int tableNumber = 0; tableNumber < source.Tables.Count; tableNumber++)
{
Worksheet sheet = book[tableNumber];
if (!string.IsNullOrEmpty(source.Tables[tableNumber].TableName))
{
sheet.Name = source.Tables[tableNumber].TableName;
}
else
{
sheet.Name = "Table" + tableNumber.ToString(CultureInfo.InvariantCulture);
}
int columnCount = source.Tables[tableNumber].Columns.Count;
for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
{
sheet[columnNumber, 0].Value = source.Tables[tableNumber].Columns[columnNumber].ColumnName;
sheet[columnNumber, 0].Font.Bold = true;
}
int rowNumber = 0;
foreach (DataRow row in source.Tables[tableNumber].Rows)
{
rowNumber++;
for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
{
string rowType = row[columnNumber].GetType().FullName;
switch (rowType)
{
case "System.DateTime":
{
sheet[columnNumber, rowNumber].Value = (DateTime)row[columnNumber];
break;
}
case "System.Boolean":
{
sheet[columnNumber, rowNumber].Value = (bool)row[columnNumber];
break;
}
case "System.SByte":
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
case "System.UInt16":
case "System.UInt32":
case "System.UInt64":
case "System.Single":
case "System.Double":
case "System.Decimal":
{
sheet[columnNumber, rowNumber].Value = Convert.ToDecimal(row[columnNumber], CultureInfo.InvariantCulture);
break;
}
case "System.DBNull":
break;
default:
{
sheet[columnNumber, rowNumber].Value = row[columnNumber].ToString();
break;
}
}
}
}
}
return(book);
}
