internal ExcelCommentCollection(ExcelPackage pck, ExcelWorksheet ws, XmlNamespaceManager ns)
{
CommentXml = new XmlDocument();
CommentXml.PreserveWhitespace = false;
NameSpaceManager=ns;
Worksheet=ws;
CreateXml(pck);
AddCommentsFromXml();
}
/// <summary>
/// Provides access to all the office document properties.
/// </summary>
/// <param name="package"></param>
/// <param name="ns"></param>
internal OfficeProperties(ExcelPackage package, XmlNamespaceManager ns) :
base(ns)
{
_package = package;
_coreHelper = XmlHelperFactory.Create(ns, CorePropertiesXml.SelectSingleNode("cp:coreProperties", NameSpaceManager));
_extendedHelper = XmlHelperFactory.Create(ns, ExtendedPropertiesXml);
_customHelper = XmlHelperFactory.Create(ns, CustomPropertiesXml);
}
private void CreateXml(ExcelPackage pck)
{
var commentParts = Worksheet.Part.GetRelationshipsByType(ExcelPackage.schemaComment);
bool isLoaded=false;
CommentXml=new XmlDocument();
foreach(var commentPart in commentParts)
{
Uri = PackUriHelper.ResolvePartUri(commentPart.SourceUri, commentPart.TargetUri);
Part = pck.Package.GetPart(Uri);
XmlHelper.LoadXmlSafe(CommentXml, Part.GetStream());
RelId = commentPart.Id;
isLoaded=true;
}
//Create a new document
if(!isLoaded)
{
CommentXml.LoadXml("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\" ?><comments xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><authors /><commentList /></comments>");
Uri = null;
}
}
private ExcelVmlDrawingPicture AddImage(Image Picture, string id, ExcelPackage.ImageInfo ii)
{
double width = Picture.Width * 72 / Picture.HorizontalResolution, //Pixel --> Points
height = Picture.Height * 72 / Picture.VerticalResolution; //Pixel --> Points
//Add VML-drawing
return _ws.HeaderFooter.Pictures.Add(id, ii.Uri, "", width, height);
}
public static MemoryStream ListToExcel(object list, Dictionary<string, string> titles, bool IsExportAllCol)
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("sheet1");
var fields = titles.Keys.ToList();
const int startIndex = 1;
var currentRow = startIndex;
var currentCell = 0;
EachHelper.EachListHeader(list, (i, name, type) =>
{
if (!fields.Contains(name))
{
if (IsExportAllCol)
fields.Add(name);
else
return;
}
currentCell = fields.IndexOf(name) + startIndex;
sheet.Cells[currentRow, currentCell].Value = titles[name] ?? name;
sheet.Column(currentCell).AutoFit();
});
EachHelper.EachListRow(list, (rowIndex, rowData) =>
{
currentRow = rowIndex + startIndex + 1;
EachHelper.EachObjectProperty(rowData, (i, name, value) =>
{
if (!fields.Contains(name))
{
if (IsExportAllCol)
fields.Add(name);
else
return;
}
currentCell = fields.IndexOf(name) + startIndex;
if (ZGeneric.IsTypeIgoreNullable<DateTime>(value))
sheet.Column(currentCell).Style.Numberformat.Format = "yyyy-MM-dd hh:mm:ss";
sheet.Cells[currentRow, currentCell].Value = value ?? string.Empty;
});
});
currentCell = startIndex + fields.Count - 1;
using (var head = sheet.Cells[startIndex, startIndex, startIndex, currentCell]) // set head style
{
head.Style.Font.Bold = true;
head.Style.Font.Size = 12;
head.Style.Font.Name = "Arial";
head.Style.Border.Top.Style = ExcelBorderStyle.Thin;
head.Style.Border.Top.Color.SetColor(Color.Gray);
head.Style.Border.Right.Style = ExcelBorderStyle.Thin;
head.Style.Border.Right.Color.SetColor(Color.Gray);
head.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
head.Style.Border.Bottom.Color.SetColor(Color.Gray);
head.Style.Border.Left.Style = ExcelBorderStyle.Thin;
head.Style.Border.Left.Color.SetColor(Color.Gray);
head.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
head.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
head.Style.Fill.PatternType = ExcelFillStyle.Solid;
head.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
}
using (var data = sheet.Cells[startIndex + 1, startIndex, currentRow, currentCell])// set data style
{
data.Style.Font.Name = "Arial";
data.Style.Font.Size = 11;
data.Style.Border.Top.Style = ExcelBorderStyle.Thin;
data.Style.Border.Top.Color.SetColor(Color.Gray);
data.Style.Border.Right.Style = ExcelBorderStyle.Thin;
data.Style.Border.Right.Color.SetColor(Color.Gray);
data.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
data.Style.Border.Bottom.Color.SetColor(Color.Gray);
data.Style.Border.Left.Style = ExcelBorderStyle.Thin;
data.Style.Border.Left.Color.SetColor(Color.Gray);
}
var ms = new MemoryStream();
package.SaveAs(ms);
return ms;
}
}
public static DataTable ReadByExcelLibrary(Stream xlsStream)
{
var table = new DataTable();
using (var package = new ExcelPackage(xlsStream))
{
var sheet = package.Workbook.Worksheets[1];
var colCount = sheet.Dimension.End.Column;
var rowCount = sheet.Dimension.End.Row;
for (ushort j = 1; j <= colCount; j++)
{
table.Columns.Add(new DataColumn(sheet.Cells[1, j].Value.ToString()));
}
for (ushort i = 2; i <= rowCount; i++)
{
var row = table.NewRow();
for (ushort j = 1; j <= colCount; j++)
{
row[j - 1] = sheet.Cells[i, j].Value;
}
table.Rows.Add(row);
}
}
return table;
}
public static MemoryStream CreateByExcelLibrary(DataTable table)
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("sheet111");
var colCount = table.Columns.Count;
for (var i = 0; i < colCount; i++)
{
sheet.Cells[1, i + 1].Value = table.Columns[i].Caption;
}
var k = 2;
foreach (DataRow row in table.Rows)
{
for (var i = 0; i < colCount; i++)
{
sheet.Cells[k, i + 1].Value = row[i];
}
k++;
}
var ms = new MemoryStream();
package.SaveAs(ms);
return ms;
}
}
/// <summary>
/// Creates a new instance of the ExcelWorkbook class.
/// </summary>
/// <param name="package">The parent package</param>
/// <param name="namespaceManager">NamespaceManager</param>
internal ExcelWorkbook(ExcelPackage package, XmlNamespaceManager namespaceManager) :
base(namespaceManager)
{
_package = package;
WorkbookUri = new Uri("/xl/workbook.xml", UriKind.Relative);
SharedStringsUri = new Uri("/xl/sharedStrings.xml", UriKind.Relative);
StylesUri = new Uri("/xl/styles.xml", UriKind.Relative);
_names = new ExcelNamedRangeCollection(this);
_namespaceManager = namespaceManager;
TopNode = WorkbookXml.DocumentElement;
SchemaNodeOrder = new string[] { "fileVersion", "fileSharing", "workbookPr", "workbookProtection", "bookViews", "sheets", "functionGroups", "functionPrototypes", "externalReferences", "definedNames", "calcPr", "oleSize", "customWorkbookViews", "pivotCaches", "smartTagPr", "smartTagTypes", "webPublishing", "fileRecoveryPr", };
GetSharedStrings();
}
