コード例 #1
0
 internal ExcelCommentCollection(ExcelPackage pck, ExcelWorksheet ws, XmlNamespaceManager ns)
 {
     CommentXml = new XmlDocument();
     CommentXml.PreserveWhitespace = false;
     NameSpaceManager=ns;
     Worksheet=ws;
     CreateXml(pck);
     AddCommentsFromXml();
 }
コード例 #2
0
        /// <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);

        }
コード例 #3
0
 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;
     }
 }
コード例 #4
0
 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);
 }
コード例 #5
0
		/// <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();
		}
コード例 #6
0
        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;
            }
        }
コード例 #7
0
        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;
        }
コード例 #8
0
        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;
            }
        }
コード例 #9
0
        public Stream SaveAsStream()
        {
            var ms = new MemoryStream();
            package.SaveAs(ms);

            package = null;
            sheet = null;
            return ms;
        }
コード例 #10
0
 public void Init(object data)
 {
     package = new ExcelPackage();
     sheet = package.Workbook.Worksheets.Add("sheet1");
 }