C# ExcelPackage.Save方法代码示例

    private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
      FileInfo newFile = new FileInfo(fileName);
      if (newFile.Exists) {
        newFile.Delete();
        newFile = new FileInfo(fileName);
      }
      var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);

      using (ExcelPackage package = new ExcelPackage(newFile)) {
        ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
        FormatModelSheet(modelWorksheet, solution, formulaParts);

        ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
        WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);

        ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
        WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);

        if (solution is IRegressionSolution) {
          ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
          WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as IRegressionSolution);

          ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
          AddCharts(chartsWorksheet);
        }
        package.Workbook.Properties.Title = "Excel Export";
        package.Workbook.Properties.Author = "HEAL";
        package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";

        package.Save();
      }
    }

        public void ExportExcel(IList<dynamic> items, string fileName)
        {
            using (var excelPackage = new ExcelPackage(new FileStream(fileName, FileMode.CreateNew)))
            {
                var currentIndex = 0;
                var sheetIndex = 1;
                do
                {
                    var take = Math.Min(1000000, items.Count - currentIndex);
                    var currentSet = items.Skip(currentIndex).Take(take).ToList();
                    currentIndex += currentSet.Count;

                    var sheet = excelPackage.Workbook.Worksheets.Add("Sheet " + sheetIndex);
                    var arrayData = new List<object[]>();
                    foreach (var item in currentSet)
                    {
                        arrayData.Add(((IDictionary<string, object>)item).Values.ToArray());
                    }

                    sheet.Cells["A1"].LoadFromArrays(arrayData);

                    sheetIndex++;
                    excelPackage.Save();
                }
                while (currentIndex < items.Count);
            }
        }

        /// <summary>
        /// Экспортирует массив данных в XLSX формат с учетом выбранной локали
        /// </summary>
        /// <param name="path">Путь к файлу, в который нужно сохранить данные</param>
        /// <param name="localisation">Локализация</param>
        /// <returns>Успешное завершение операции</returns>
        public override bool Export(String path, Localisation localisation)
        {
            try
            {
                if (!path.EndsWith(".xlsx"))
                    path += ".xlsx";

                log.Info(String.Format("Export to .xlsx file to: {0}", path));
                var timer = new Stopwatch();
                timer.Start();
                var file = new FileInfo(path);
                using (var pck = new ExcelPackage(file))
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
                    ws.Cells["A1"].LoadFromDataTable(dataTable, true);
                    ws.Cells.AutoFitColumns();
                    pck.Save();
                }

                timer.Stop();
                log.Info(String.Format("Export complete! Elapsed time: {0} ms", timer.Elapsed.Milliseconds));
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Can't export to .xlsx file!", ex);
                return false;
            }
        }

        /// <summary>
        /// 保存excel文件，覆盖相同文件名的文件
        /// </summary>
        public static void SaveExcel(string FileName, string sql, string SheetName)
        {

            FileInfo newFile = new FileInfo(FileName);
            if (newFile.Exists)
            {
                newFile.Delete();
                newFile = new FileInfo(FileName);
            }
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                try
                {
                    ExcelWorksheet ws = package.Workbook.Worksheets.Add(SheetName);

                    IDataReader reader = DBConfig.db.DBProvider.ExecuteReader(sql);
                    ws.Cells["A1"].LoadFromDataReader(reader, true);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                package.Save();
            }
         
        }

 /// <summary>
 /// 单表格导出到多excel工作簿，分页版本
 /// </summary>
 public static int ExportExcel(string TabelName, int PageSize, string filename)
 {
     if (filename != null)
     {
         Stopwatch watch = Stopwatch.StartNew();
         watch.Start();
         int RecordCount = DBConfig.db.DBProvider.ReturnTbCount(TabelName);
         string sql = "select * from  [" + TabelName + "]";
         int WorkBookCount = (RecordCount - 1) / PageSize + 1;
         FileInfo newFile = new FileInfo(filename);
         for (int i = 1; i <= WorkBookCount; i++)
         {
             string s = filename.Substring(0, filename.LastIndexOf("."));
             StringBuilder newfileName = new StringBuilder(s);
             newfileName.Append(i + ".xlsx");
             newFile = new FileInfo(newfileName.ToString());
             if (newFile.Exists)
             {
                 newFile.Delete();
                 newFile = new FileInfo(newfileName.ToString());
             }
             using (ExcelPackage package = new ExcelPackage(newFile))
             {
                 DataTable dt = DBConfig.db.DBProvider.ReturnDataTable(sql, PageSize * (i - 1), PageSize);
                 SaveExcel(TabelName, dt, package);
                 package.Save();
             }
         }
         watch.Stop();
         return Convert.ToInt32(watch.ElapsedMilliseconds / 1000);
     }
     return -1;
 }

    protected void btn_Excel_Click(object sender, EventArgs e)
    {
        GetRecoredForExcelfile();
        string newFilePath = Server.MapPath("ExcelFile/ErrorList.xlsx");
        string templateFilePath = Server.MapPath("ExcelFile/ErrorListtemplate.xlsx");
        FileInfo newFile = new FileInfo(newFilePath);
        FileInfo template = new FileInfo(templateFilePath);
        using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
        {

            foreach (ExcelWorksheet aworksheet in xlPackage.Workbook.Worksheets)
            {
                aworksheet.Cell(1, 1).Value = aworksheet.Cell(1, 1).Value;
            }

            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["Sheet1"];
            int startrow = 5;
            int row = 0;
            int col = 0;

            for (int j = 0; j < Dt.Columns.Count; j++)
            {
               // col = col + j;
                col++;
                for (int i = 0; i < Dt.Rows.Count; i++)
                {
                    row = startrow + i;
                    ExcelCell cell = worksheet.Cell(row, col);
                    cell.Value = Dt.Rows[i][j].ToString();
                    xlPackage.Save();
                }
            }
        }
    }

        public void Write(int rows, int cols)
        {
            String file = "testWs.xlsx";
            if (File.Exists(file)) { File.Delete(file); }

            TimeSpan start;

            // Write
            Console.WriteLine();
            Console.WriteLine("Test: " + rows + " x " + cols);
            Console.WriteLine("=======");
            Console.WriteLine("Writing");
            start = Process.GetCurrentProcess().TotalProcessorTime;
            using (ExcelPackage package = new ExcelPackage(new FileInfo(file)))
            {
                int div = Math.Max(1, rows / 20);
                ExcelWorksheet ws = package.Workbook.Worksheets.Add("Stress", rows, cols);

                TimeRestart("Create", ref start);
                for (int row = 0; row < rows; row++)
                {
                    for (int col = 0; col < cols; col++)
                    {
                        ws.Cell(row + 1, col + 1).Value = GetVal(row, col);
                    }
                    if (row % div == 0) { Console.Write("*"); }
                }
                Console.WriteLine("done");

                TimeRestart("Write", ref start);

                package.Save();
            }
            TimeRestart("Save", ref start);

            // Read
            Console.WriteLine("Reading");
            start = Process.GetCurrentProcess().TotalProcessorTime;
            using (ExcelPackage package = new ExcelPackage(new FileInfo(file)))
            {
                int div = Math.Max(1, rows / 20);
                ExcelWorksheet ws = package.Workbook.Worksheets["Stress"];

                TimeRestart("Open", ref start);
                for (int row = 0; row < rows; row++)
                {
                    for (int col = 0; col < cols; col++)
                    {
                        ExcelCell cell = ws.Cell(row + 1, col + 1);
                        String val = cell.Value;
                        Assert.AreEqual(GetVal(row, col), cell.Value, "@" + (row + 1) + ", " + (col + 1));
                    }
                    if (row % div == 0) { Console.Write("*"); }
                }
                Console.WriteLine("done");

                TimeRestart("Read", ref start);
            }
            Console.WriteLine(" "); // skip
        }

        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();
            }
        }

        public static void CreateReport()
        {
            // Set the file name and get the output directory
            var fileName = "Report-" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx";
            var outputDir = "../../../Reports/ExcelReports/";

            // Create the file
            var file = new FileInfo(outputDir + fileName);

            using (var package = new ExcelPackage(file))
            {
                // add a new worksheet to the empty workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Profit per month");

                // Set headers here
                worksheet.Cells[1, 1].Value = "Month";
                worksheet.Cells[1, 2].Value = "Income";
                worksheet.Cells[1, 3].Value = "Outcome";
                worksheet.Cells[1, 4].Value = "Profit";

                int numberOfColumns = 4;

                //Headers styling
                FormatHeaders(worksheet, numberOfColumns);

                FillData(worksheet, numberOfColumns);
                package.Save();
            }
        }

 public bool InsertNewRow(string FolderPath, string DestinationFileName,int AddNewRowAfter, int NumberOfRowsToAdd)
 {
     try
     {
         var File = new FileInfo(FolderPath + "\\" + DestinationFileName);
         using (ExcelPackage package = new ExcelPackage(File))
         {
             ExcelWorkbook workBook = package.Workbook;
             if (workBook != null)
             {
                 if (workBook.Worksheets.Count > 0)
                 {
                     ExcelWorksheet cSheet = workBook.Worksheets.First();
                     cSheet.InsertRow(AddNewRowAfter, NumberOfRowsToAdd);
                 }
             }
             package.Save();
         }
         return true;
     }
     catch
     {
         return false;
     }
 }

        public bool UpdateExcelTemplate(string FolderPath, string SourceFileName, string DestinationFileName, bool DeleteExistingCopy, string CellName, string CellValue)
        {
            if (System.IO.File.Exists(FolderPath + "\\" + DestinationFileName) && DeleteExistingCopy)
            {
                System.IO.File.Delete(FolderPath + "\\" + DestinationFileName);
                System.IO.File.Copy(FolderPath + "\\" + SourceFileName, FolderPath + "\\" + DestinationFileName);
            }
            else if (!System.IO.File.Exists(FolderPath + "\\" + DestinationFileName))
            {
                System.IO.File.Copy(FolderPath + "\\" + SourceFileName, FolderPath + "\\" + DestinationFileName);
            }

            var File = new FileInfo(FolderPath + "\\" + DestinationFileName);
            using (ExcelPackage package = new ExcelPackage(File))
            {
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        ExcelWorksheet cSheet = workBook.Worksheets.First();

                        cSheet.Cells[CellName].Value = CellValue;
                    }
                }
                package.Save();
            }
            return false;
        }

        public void Schedule(int number)
        {
            // Sprawdzenie czy poprzedni tydzień był już wykonany w całości
            //		Jeżeli nie to sprawdzamy czy stworzenie nowego harmonogramu miałobyć wymuszone
            //			Jeżeli nie to wyświetlamy komunikat, że nie można utworzyć nowego tygdnia bo poprzedni nie był zrobiony
            //
            // Tworzymy nowy harmonogram z nr tygodnia
            //

            var directoryPath = Path.Combine(ConfigurationManager.AppSettings["Scheduling"], number + " Week");
            if (Directory.Exists(directoryPath))
                throw new InvalidOperationException(string.Format("Can't add new week because directory ({0}) exist",directoryPath));

            Directory.CreateDirectory(directoryPath);
            var path = Path.Combine(directoryPath, "Raport.xlsx");
            var file = new FileInfo(path);
            using (var package = new ExcelPackage(file))
            {
                var worksheet = package.Workbook.Worksheets.Add("Zadania do wykonania");
                var tasks = _taskGenerator.GenerateWeekTasks();
                var actualRow = 0;
                foreach (var task in tasks)
                {
                    worksheet.Cells[++actualRow, 1].Value = task;
                }
                worksheet.Column(1).AutoFit();
                package.Save();
            }
        }

        void Access_Excel(ITestCaseCollection testcases)
        {
            try
            {

                FileInfo new_file = new FileInfo(File_Name.Text);

               // FileInfo template = new FileInfo(System.Windows.Forms.Application.StartupPath + "\\Resources\\Test_Case_Template.xlsx");
                FileInfo template = new FileInfo(Directory.GetCurrentDirectory() + "\\Test_Case_Template.xlsx");
                using (ExcelPackage xlpackage = new ExcelPackage(new_file,template))
                {
                    ExcelWorksheet worksheet = xlpackage.Workbook.Worksheets["Test Case"];

                    GetTestCases(testcases, worksheet);
                    xlpackage.Save();

                    MessageBox.Show("File has been saved at " + File_Name.Text);
            }

            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                MessageBox.Show(errorMessage, "Error");

            }
        }

        private void CreateExcelFile(string fileName, IList<SqliteExcelFormat> data)
        {
            if (File.Exists(fileName)) File.Delete(fileName);
            using (var excel = new ExcelPackage(new FileInfo(fileName)))
            {
                var ws = excel.Workbook.Worksheets.Add("Sheet1");
                ws.Cells[1, 1].Value = "Vendor";
                ws.Cells[1, 2].Value = "Incomes";
                ws.Cells[1, 3].Value = "Expenses";
                ws.Cells[1, 4].Value = "Taxes";
                ws.Cells[1, 5].Value = "Financial Result";

                for (int i = 0; i < data.Count; i++)
                {
                    var tax = (data[i].TaxPercentage / 100) * data[i].Incomes;
                    ws.Cells[i + 2, 1].Value = data[i].Vendor;
                    ws.Cells[i + 2, 2].Value = data[i].Incomes;
                    ws.Cells[i + 2, 3].Value = data[i].Expenses;
                    ws.Cells[i + 2, 4].Value = Math.Round((decimal)tax, 2);
                    ws.Cells[i + 2, 5].Value = Math.Round((decimal)(data[i].Incomes - data[i].Expenses - tax), 2);
                }
                            
                ws.Column(1).AutoFit();
                ws.Column(2).AutoFit();
                ws.Column(3).AutoFit();
                ws.Column(4).AutoFit();
                ws.Column(5).AutoFit();
                excel.Save();
            }
        }

    protected void btn_download_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt.TableName = "All_Contacts";
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dlgf"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("[CTX].[sp_Select_All_List]", conn);
            conn.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(dt);
            conn.Close();
        }

        string dir = Server.MapPath("All_List_output");
        string milli = DateTime.Now.Millisecond.ToString();
        FileInfo newFile = new FileInfo(dir + @"\CTX_"+ milli + ".xlsx");
        if (newFile.Exists)
        {
            newFile.Delete();  // ensures we create a new workbook
            newFile = new FileInfo(dir + @"\CTX_" + milli + ".xlsx");
        }

        using (ExcelPackage pack = new ExcelPackage(newFile))
        {
            ExcelWorksheet ws = pack.Workbook.Worksheets.Add(dt.TableName.ToString());
            ws.Cells["A1"].LoadFromDataTable(dt, true);
            pack.Save();
        }

        string path = dir + @"\" + newFile.Name;
        Response.ContentType = "application/excel";
        Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + newFile.Name + "\""); //file name must be in double quotes to allow spaces in the filename
        Response.TransmitFile(path);
        Response.End();
    }