C# ExcelPackage.Dispose方法代码示例

        private static void CreateDeletePackage(int Sheets, int rows)
        {
            List<object> row = new List<object>();
            row.Add(1);
            row.Add("Some text");
            row.Add(12.0);
            row.Add("Some larger text that has completely no meaning.  How much wood can a wood chuck chuck if a wood chuck could chuck wood.  A wood chuck could chuck as much wood as a wood chuck could chuck wood.");

            FileInfo LocalFullFileName = new FileInfo(Path.GetTempFileName());
            LocalFullFileName.Delete();
            package = new ExcelPackage(LocalFullFileName);

            try
            {
                for (int ca = 0; ca < Sheets; ca++)
                {
                    CreateWorksheet("Sheet" + (ca+1), row, rows);
                }

                package.Save();
            }
            finally
            {
                LocalFullFileName.Refresh();
                if (LocalFullFileName.Exists)
                {
                    LocalFullFileName.Delete();
                }

                package.Dispose();
                package = null;

                GC.Collect();
            }
        }

        private void writeHeader(string filename)
        {
            ExcelPackage ep;

            ep = new ExcelPackage(new FileInfo(filename));
            var workbook = ep.Workbook;

            ExcelWorksheet akWorksheet = null;
            foreach (ExcelWorksheet worksheet in workbook.Worksheets)
            {
                if (worksheet.Name == TableName)
                    akWorksheet = worksheet;
            }
            if (akWorksheet == null)
            {
                akWorksheet = workbook.Worksheets.Add(TableName);
            }

            int n = 1;

            foreach (DatasetConfigRow myFeld in datasetConfig.DatasetConfigRows)
            {

                akWorksheet.Cells[1, n].Value = myFeld.DatabaseField;
                n++;
            }

            ep.Save();
            ep.Dispose();
        }

        protected void ImgExportToExcell_Click(object sender, System.Web.UI.ImageClickEventArgs e)
        {
            long RecordCount;
            var kids = KidsUser_DataProvider.GetGeustUser(out RecordCount,
                                                        Name: txtSearchName.Text,
                                                        Family: txtSearchFamily.Text,
                                                        MelliCode: txtSearchMelliCode.Text,
                                                        EmailAddress: txtSearchEmailAddress.Text,
                                                        MobileNumber: txtSearchMobileNumber.Text,
                                                        SelectDistinct: true,
                                                        PageSize: 100000
                                                        );

            string templatefilePath = Server.MapPath("~/AdminCP/Files/GeustUser/GeustUserReport.xlsx");
            FileInfo templateFileInfo = new FileInfo(templatefilePath);

            string NewfilePath = string.Format("~/AdminCP/Files/GeustUser/Temp/GeustUserReport_{0}.xlsx", PersianDateTime.Now.ToLongDateTimeString().Replace("/", "-").Replace(":", "-"));
            FileInfo NewfileInfo = new FileInfo(Server.MapPath(NewfilePath));

            ExcelPackage xlPackage = new ExcelPackage(templateFileInfo, true);

            ExcelWorksheet workSheetGeustUser = xlPackage.Workbook.Worksheets["GeustUser"];
            int i = 2;
            foreach (var user in kids)
            {
                FillExcellRow(user, workSheetGeustUser, i);
                i++;
            }
            xlPackage.SaveAs(NewfileInfo);
            xlPackage.Dispose();
            ClientRedirect(NewfilePath, 2000);
        }

        void Handle(Input.Import Action)
        {
            FileInfo fi = new FileInfo(this.ImportPath);
            ExcelPackage package = new ExcelPackage(fi);
            Importer importer = new Importer();

            importer.Import(package, true);
            this.Message = "The file has been successfully imported!";
            package.Dispose();
        }

        public void AllDrawingsInsideMarkupCompatibility()
        {
            string workbooksDir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\workbooks");
            // This is codeplex issue 15028: Making an unrelated change to an Excel file that contains drawings that ALL exist
            // inside MarkupCompatibility/Choice nodes causes the drawings.xml file to be incorrectly garbage collected
            // when an unrelated change is made.
            string path = Path.Combine(workbooksDir, "AllDrawingsInsideMarkupCompatibility.xlsm");

            // Load example document.
            _pck = new ExcelPackage(new FileInfo(path));
            // Verify the drawing part exists:
            Uri partUri = new Uri("/xl/drawings/drawing1.xml", UriKind.Relative);
            Assert.IsTrue(_pck.Package.PartExists(partUri));

            // The Excel Drawings NamespaceManager from ExcelDrawing.CreateNSM:
            NameTable nt = new NameTable();
            var xmlNsm = new XmlNamespaceManager(nt);
            xmlNsm.AddNamespace("a", ExcelPackage.schemaDrawings);
            xmlNsm.AddNamespace("xdr", ExcelPackage.schemaSheetDrawings);
            xmlNsm.AddNamespace("c", ExcelPackage.schemaChart);
            xmlNsm.AddNamespace("r", ExcelPackage.schemaRelationships);
            xmlNsm.AddNamespace("mc", ExcelPackage.schemaMarkupCompatibility);

            XmlDocument drawingsXml = new XmlDocument();
            drawingsXml.PreserveWhitespace = false;
            XmlHelper.LoadXmlSafe(drawingsXml, _pck.Package.GetPart(partUri).GetStream());

            // Verify that there are the correct # of drawings:
            Assert.AreEqual(drawingsXml.SelectNodes("//*[self::xdr:twoCellAnchor or self::xdr:oneCellAnchor or self::xdr:absoluteAnchor]", xmlNsm).Count, 5);

            // Make unrelated change. (in this case a useless additional worksheet)
            _pck.Workbook.Worksheets.Add("NewWorksheet");

            // Save it out.
            string savedPath = Path.Combine(workbooksDir, "AllDrawingsInsideMarkupCompatibility2.xlsm");
            _pck.SaveAs(new FileInfo(savedPath));
            _pck.Dispose();

            // Reload the new saved file.
            _pck = new ExcelPackage(new FileInfo(savedPath));

            // Verify the drawing part still exists.
            Assert.IsTrue(_pck.Package.PartExists(new Uri("/xl/drawings/drawing1.xml", UriKind.Relative)));

            drawingsXml = new XmlDocument();
            drawingsXml.PreserveWhitespace = false;
            XmlHelper.LoadXmlSafe(drawingsXml, _pck.Package.GetPart(partUri).GetStream());

            // Verify that there are the correct # of drawings:
            Assert.AreEqual(drawingsXml.SelectNodes("//*[self::xdr:twoCellAnchor or self::xdr:oneCellAnchor or self::xdr:absoluteAnchor]", xmlNsm).Count, 5);
            // Verify that the new worksheet exists:
            Assert.IsNotNull(_pck.Workbook.Worksheets["NewWorksheet"]);
            // Cleanup:
            File.Delete(savedPath);
        }

        public void CreateWorkbook(string output, DataTable dt)
        {
            if (File.Exists(output)) File.Delete(output);
            FileInfo newFile = new FileInfo(output);

            ExcelPackage xlPackage = new ExcelPackage(newFile);
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Data");

            ExcelRange header = worksheet.Cells[1, 1, 1, dt.Columns.Count];
            worksheet.View.FreezePanes(2, 1);
            header.Style.Fill.PatternType = ExcelFillStyle.Solid;
            header.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
            header.Style.Font.Bold = true;
            for (int i = 1; i <= dt.Columns.Count; ++i)
                header[1, i].Value = dt.Columns[i - 1].ColumnName;
            header.Dispose();

            ExcelRange data = worksheet.Cells[2, 1, dt.Rows.Count + 1, dt.Columns.Count];
            for (int i = 2; i <= dt.Rows.Count + 1; ++i)
            {
                for (int j = 1; j <= dt.Columns.Count; ++j)
                {
                    data[i, j].Value = dt.Rows[i - 2][j - 1];
                }
            }
            data.Dispose();

            for (int i = 1; i <= dt.Columns.Count; ++i)
            {
                var format = "@";
                if (dt.Columns[i - 1].DataType == typeof(DateTime))
                {
                    format = "[$-409]d/m/yy h:mm AM/PM;@";
                }
                else if (dt.Columns[i - 1].DataType == typeof(int))
                {
                    format = "#,##0";
                }
                worksheet.Cells[2, i, dt.Rows.Count + 1, i].Style.Numberformat.Format = format;
                if (format != "@") worksheet.Column(i).AutoFit();
            }

            xlPackage.Save();
            xlPackage.Dispose();
        }

        private void PrintForm()
        {
            if (Request["uid"].IsInt64())
            {
                var user = KidsUser_DataProvider.GetKidsUser(Request["uid"].ToLong()).FirstOrDefault();
                if (user == null)
                    return;

                string templatefilePath = Server.MapPath("~/AdminCP/Files/KidsAccForm/528_2.xlsx");
                FileInfo templateFileInfo = new FileInfo(templatefilePath);

                string NewfilePath = string.Format("~/AdminCP/Files/KidsAccForm/Temp/528_2_{0}.xlsx", PersianDateTime.Now.ToLongDateTimeString().Replace("/", "-").Replace(":", "-"));
                FileInfo NewfileInfo = new FileInfo(Server.MapPath(NewfilePath));

                ExcelPackage xlPackage = new ExcelPackage(templateFileInfo, true);

                ExcelWorksheet workSheetReal_1 = xlPackage.Workbook.Worksheets["حقيقي-1"];
                FillExcellRow(user, workSheetReal_1);

                ExcelWorksheet workSheetReal_2 = xlPackage.Workbook.Worksheets["حقيقي-2"];
                FillExcellRow2(user, workSheetReal_2);


                ExcelWorksheet w_MelliCard = xlPackage.Workbook.Worksheets["کارت ملی"];
                ExcelWorksheet w_Identity = xlPackage.Workbook.Worksheets["شناسنامه"];

                var w = JpegImage.CmToPx(20).ToInt32();
                var h = JpegImage.CmToPx(30).ToInt32();
                CreateImage(w_Identity, JpegImage.GetPicFromDB(user, JpegImage.ImageActType.ChildIdentityPic, false, w, h), 0, 0);


                w = JpegImage.CmToPx(10).ToInt32();
                h = JpegImage.CmToPx(20).ToInt32();
                CreateImage(w_MelliCard, JpegImage.GetPicFromDB(user, JpegImage.ImageActType.ChildNationalCardFaceUPPic, false, w, h), 0, 0);
                CreateImage(w_MelliCard, JpegImage.GetPicFromDB(user, JpegImage.ImageActType.ChildNationalCardFaceDownPic, false, w, h), 17, 0);




                xlPackage.SaveAs(NewfileInfo);
                xlPackage.Dispose();
                ClientRedirect(NewfilePath, 2000);
            }
        }

 public void FileLockedProblem()
 {
     using (ExcelPackage pck = new ExcelPackage(new FileInfo(@"c:\temp\url.xlsx")))
     {
         pck.Workbook.Worksheets[1].DeleteRow(1, 1);
         pck.Save();
         pck.Dispose();
     }
 }

 public static void dispose(ExcelPackage oExcel)
 {
     oExcel.Dispose();
 }

        private List<String> GetAllColumnsName(ExcelPackage packeg)
        {
            ExcelWorksheet ws = packeg.Workbook.Worksheets.First();
            List<String> listFile = new List<String>();
            DataTable dt = new DataTable(ws.Name);
            int totalCols = ws.Dimension.End.Column;
            int totalRows = ws.Dimension.End.Row;

            foreach (var firstRowCell in ws.Cells[1, 1, 1, totalCols])
            {
                listFile.Add(firstRowCell.Text);
            }
            packeg.Dispose();
            return listFile;
        }

 public void VbaError()
 {
     DirectoryInfo workingDir = new DirectoryInfo(@"C:\epplusExample\folder");
     if (!workingDir.Exists) workingDir.Create();
     FileInfo f = new FileInfo(workingDir.FullName + "//" + "temp.xlsx");
     if (f.Exists) f.Delete();
     ExcelPackage myPackage = new ExcelPackage(f);
     myPackage.Workbook.CreateVBAProject();
     ExcelWorksheet excelWorksheet = myPackage.Workbook.Worksheets.Add("Sheet1");
     ExcelWorksheet excelWorksheet2 = myPackage.Workbook.Worksheets.Add("Sheet2");
     ExcelWorksheet excelWorksheet3 = myPackage.Workbook.Worksheets.Add("Sheet3");
     FileInfo f2 = new FileInfo(workingDir.FullName + "//" + "newfile.xlsm");
     ExcelVBAModule excelVbaModule = myPackage.Workbook.VbaProject.Modules.AddModule("Module1");
     StringBuilder mybuilder = new StringBuilder(); mybuilder.AppendLine("Sub Jiminy()");
     mybuilder.AppendLine("Range(\"D6\").Select");
     mybuilder.AppendLine("ActiveCell.FormulaR1C1 = \"Jiminy\"");
     mybuilder.AppendLine("End Sub");
     excelVbaModule.Code = mybuilder.ToString();
     myPackage.SaveAs(f2);
     myPackage.Dispose();
 }

        private void writeToExcel(string path, string name, string val)
        {
            ExcelPackage p = new ExcelPackage(new FileInfo(path));
            ExcelWorksheet worksheet = p.Workbook.Worksheets[2];

            for (var i = 1; i < 10000000; i++)
            {
                var range = worksheet.Cells[i, 1];
                if (range.Value == null)
                {
                    worksheet.Cells[i, 1].Value = name;
                    worksheet.Cells[i, 2].Value = val;
                    break;
                }
            }

            p.Save();
            p.Dispose();
        }

        private static void WriteToXLSX(DataCell[][] cells, string filename)
        {
            if (File.Exists(filename))
            {
                File.Delete(filename);
            }

            var package = new ExcelPackage(new FileInfo(filename));
            var sheet = package.Workbook.Worksheets.Add("Content");

            foreach (var cell in new ExcelCellEnumerator(cells.GetLength(0), cells.Max(x => x.Length)))
            {
                var dataRow = cells[cell.Row - 1];
                if (cell.Column >= dataRow.Length) continue;
                var data = dataRow[cell.Column];
                var excelCell = sheet.Cells[cell.Value];
                excelCell.Value = data.Content;
                switch (data.Type)
                {
                    case DataCellType.HeadingBig:
                        excelCell.Style.Font.Bold = true;
                        excelCell.Style.Font.Size = 18;
                        sheet.Row(cell.Row).Merged = true;
                        break;
                    case DataCellType.Heading:
                        excelCell.Style.Font.Bold = true;
                        excelCell.Style.Font.Size = 12;
                        break;
                    case DataCellType.ResultGood:
                        XLSXDataSerializer.SetResultCell(excelCell, Color.FromArgb(0xBC, 0xED, 0x91));
                        break;
                    case DataCellType.ResultNeutral:
                        XLSXDataSerializer.SetResultCell(excelCell, Color.FromArgb(0xFF, 0xEC, 0xB3));
                        break;
                    case DataCellType.ResultBad:
                        XLSXDataSerializer.SetResultCell(excelCell, Color.FromArgb(0xFF, 0x8A, 0x65));
                        break;
                    case DataCellType.Number:
                        excelCell.Value = double.Parse(excelCell.Value.ToString().Replace(",", "."),
                            CultureInfo.InvariantCulture);
                        break;
                }

                if (data.Outline.HasFlag(DataCellOutline.Top))
                {
                    excelCell.Style.Border.Top.Style = ExcelBorderStyle.Medium;
                    excelCell.Style.Border.Top.Color.SetColor(Color.Black);
                }

                if (data.Outline.HasFlag(DataCellOutline.Bottom))
                {
                    excelCell.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
                    excelCell.Style.Border.Bottom.Color.SetColor(Color.Black);
                }

                if (data.Outline.HasFlag(DataCellOutline.Left))
                {
                    excelCell.Style.Border.Left.Style = ExcelBorderStyle.Medium;
                    excelCell.Style.Border.Left.Color.SetColor(Color.Black);
                }

                if (data.Outline.HasFlag(DataCellOutline.Right))
                {
                    excelCell.Style.Border.Right.Style = ExcelBorderStyle.Medium;
                    excelCell.Style.Border.Right.Color.SetColor(Color.Black);
                }
            }

            for (var i = 1; i < cells.Max(x => x.Length); i++)
            {
                sheet.Cells[2, i, sheet.Dimension.Rows, i].AutoFitColumns(10.71);
                    // 10.71 represents the default excel cell width, we do not want to go below that
            }

            package.Save();
            package.Dispose();
        }

        private void refreshExcel()
        {
            FileInfo excelFileInfo = new FileInfo(Program.excelFile);
            ExcelPackage package = new ExcelPackage(new FileInfo(Program.excelFile));

            foreach (ExcelWorksheet sheet in package.Workbook.Worksheets)
            {
                foreach (var cell in sheet.Cells)
                {
                    if (cell.Start.Column == 2)
                    {
                        cell.Style.Numberformat.Format = "hh:mm:ss";
                    }
                }
            }

            if (package.Workbook.Worksheets.Count() == 0)
            {
                package.Workbook.Worksheets.Add("Times");
            }

            package.Save();
            package.Dispose();
            package.Stream.Close();
        }

        /// <summary>
        /// 讀取 Excel 檔案作為初始化資料
        /// </summary>
        /// <param name="excelFileName"></param>
        public void Import(string excelFileName)
        {
            DB = new CustomerDB();
            List<CustomerInfo> result = DB.Items;

            //開檔
            FileStream fs = new FileStream(excelFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);

            //載入Excel檔案
            ExcelPackage ep = new ExcelPackage(fs);
            ExcelWorksheet sheet = ep.Workbook.Worksheets["Sheet1"];//取得Sheet1
            int startRowNumber = sheet.Dimension.Start.Row;//起始列編號，從1算起
            int endRowNumber = sheet.Dimension.End.Row;//結束列編號，從1算起
            bool isHeader = true;
            if (isHeader)//有包含標題
            {
                startRowNumber += 1;
            }

            //寫入標題文字
            //sheet.Cells[1, 11].Value = "緯度";
            //sheet.Cells[1, 12].Value = "經度";
            for (int i = startRowNumber; i <= endRowNumber; i++)
            {
                //讀值
                string cellValue1 = sheet.Cells[i, 1].Value.ToString();
                string cellValue2 = sheet.Cells[i, 2].Value.ToString();

                result.Add(new CustomerInfo()
                        {
                            Customer_Code = cellValue1
                            , Customer_Name=cellValue2
                        }
                    );
                //寫值
                //sheet.Cells[i, 1].Value = cellValue + "test";
            }

            fs.Close();
            ////建立檔案
            //fs = new FileStream(@"D:\目標檔案.xlsx", FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
            //ep.SaveAs(fs);//存檔
            //fs.Close();
            //關閉資源
            ep.Dispose();
            ep = null;

            return ;
        }