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 = string.Empty;
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);
}
}
}
}
}
/// <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 void Import(XmlReader reader)
{
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
if (xa.LocalName == "Name" && xa.HasValue)
{
this.Name = xa.Value;
}
if (xa.LocalName == "StyleID" && xa.HasValue)
{
this.Style = this.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, this.GetParentBook(), this);
break;
}
case "Table":
{
this.ImportTable(reader);
break;
}
case "WorksheetOptions":
{
this.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)
{
// Document Properties
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="TMP.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="TMP.ExcelXml.Cell"/>
/// except <see cref="TMP.ExcelXml.Cell"/> and
/// <see cref="TMP.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];
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;
// case "System.String": <-- default covers this...
default:
{
sheet[columnNumber, rowNumber].Value = row[columnNumber].ToString();
break;
}
}
}
}
}
return(book);
}