Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Fri Nov 19, 2021 10:40 am

When I try to copy a sheet (that contains a pivotTable) from a workbook to another new workbook, it creates a corrupt file with empty stuff.

Main:
Code: Select all
excel_manager em = new excel_manager();
em.loadExcelFile(filename3);
                    em.setDebugMode(true);

                    em.clearData("Foaie1");

                    em.CopySheetFrom(filename, "pivot3");

                    em.close_and_save();


class functions:

Code: Select all
public void setDebugMode(bool status)
        {
            sWB.IsHideWindow = !(status);
        }


Code: Select all
internal void clearData(string sheetName)
        {
            CellRange cr = sWB.Worksheets[sheetName].AllocatedRange;
           
            //Clear all objects
            cr.ClearAll();
        }


Code: Select all
  internal void CopySheetFrom(string filename, string sheetname)
        {
            Workbook sourceFile = new Workbook();
            if (File.Exists(filename))
            {
                try
                {
                    sourceFile.LoadFromFile(filename);
                    sourceFile.IsHideWindow = true;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: ", ex.ToString());
                }

            }
            else
            {
                Console.WriteLine("File does not exist");
            }

            this.sWB.Worksheets["Foaie1"].CopyFrom(sourceFile.Worksheets[sheetname]);



        }


Code: Select all
  public void close_and_save()
        {
            sWB.SaveToFile(fileName, ExcelVersion.Version2013);
        }


Whole Class Code:

Code: Select all
class excel_manager
    {
        Workbook sWB;
        Worksheet active_sheet;
        PivotCache cache1;

        String filePath;
        String fileName;
        String[] date_headers = new string[] {"nume","numar", "total",
                                 "ocupate", "dt", "dela-panala"};

        String[] date2_headers = new string[] { "nume", "dt", "libere", "total" };

        int sheetNumber;

        public excel_manager()
        {
            sWB = new Workbook();
            filePath = "";
            fileName = "";
            sheetNumber = 0;
            cache1 = null;
        }

        internal void dispose()
        {
            this.sWB.Dispose();
            this.active_sheet.Dispose();
            this.cache1.Dispose();

            this.filePath = "";
            this.fileName = "";

            this.sheetNumber = 0;
        }
        public void loadExcelFile(String path)
        {

            if (File.Exists(path))
            {
                try
                {
                    this.filePath = path;
                    sWB.LoadFromFile(filePath);
                    sWB.IsHideWindow = true;

                    fileName = sWB.FileName;
                    sheetNumber = sWB.Worksheets.Count;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: ", ex.ToString());
                }
               
            }
            else
            {
                Console.WriteLine("File does not exist");
            }
        }

        public void setDebugMode(bool status)
        {
            sWB.IsHideWindow = !(status);
        }

        public void changeSheet(int index)
        {
            if (index > 0 && index <= sheetNumber)
            {
                try
                {
                    active_sheet = sWB.Worksheets[index];
                    active_sheet.Activate();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: ", ex.ToString());
                }
            }
            else
            {
                Console.WriteLine("Invalid Inex for Sheet!");
            }
           
           
        }

        public void changeSheet(String sheetName)
        {
            try
            {
                active_sheet = sWB.Worksheets[sheetName];
                active_sheet.Activate();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: ", ex.ToString());
            }

        }

        internal void clearData(string sheetName)
        {
            CellRange cr = sWB.Worksheets[sheetName].AllocatedRange;
           
            //Clear all objects
            cr.ClearAll();
        }

        internal void clearData(string sheetName, string cells_range)
        {
            CellRange cr = sWB.Worksheets[sheetName].Range[cells_range];

            //Clear all objects
            cr.ClearAll();
        }

        internal void clearDataSituatie2(String sheetName)
        {
            //Get Datasource "data"
            Worksheet sheet = sWB.Worksheets[sheetName];
            CellRange cr = sheet.Range[sheet.FirstRow + 3, sheet.FirstColumn + 1, sheet.LastRow - 1, sheet.LastColumn - 3];

            cr.ClearAll();
        }

        public void removeSheet(String sheetName, String pivotTableName="")
        {
            try
            {
               // if (sheetName.Contains("pivot"))
               // {
                    //sWB.Worksheets[sheetName].Clear();
                    //sWB.Worksheets[sheetName].ClearData();
                   // sWB.Worksheets[sheetName].PivotTables.Remove(pivotTableName);
               // }

                sWB.Worksheets.Remove(sheetName);

            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: ", ex.ToString());
            }
        }

        public void removeSheet(int index)
        {
            if (index > 0 && index <= sheetNumber)
            {
                try
                {
                    sWB.Worksheets.Remove(index);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: ", ex.ToString());
                }
            }
            else
            {
                Console.WriteLine("Invalid Inex for Sheet!");
            }
        }

       
        public void addSheet(String sheetName)
        {
            sWB.Worksheets.Add(sheetName);
        }

        public void close_and_save()
        {
            sWB.SaveToFile(fileName, ExcelVersion.Version2013);
        }

        internal void addDateHeaders()
        {
            changeSheet("date");

            CellRange range1 = active_sheet.Range[active_sheet.FirstRow, active_sheet.FirstColumn, active_sheet.FirstRow, date_headers.Length];

            int c1 = 0;
            foreach (var i in range1)
            {
                i.Text = date_headers[c1];
                i.Style.Font.IsBold = true;
                c1++;
            }


            changeSheet("date2");

            CellRange range2 = active_sheet.Range[active_sheet.FirstRow, active_sheet.FirstColumn, active_sheet.FirstRow, date2_headers.Length];

            int c2 = 0;
            foreach (var i in range2)
            {
                i.Text = date2_headers[c2];
                i.Style.Font.IsBold = true;
                c2++;
            }
        }

        internal void CopySheetFrom(string filename, string sheetname)
        {
            Workbook sourceFile = new Workbook();
            if (File.Exists(filename))
            {
                try
                {
                    sourceFile.LoadFromFile(filename);
                    sourceFile.IsHideWindow = true;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: ", ex.ToString());
                }

            }
            else
            {
                Console.WriteLine("File does not exist");
            }

            this.sWB.Worksheets["Foaie1"].CopyFrom(sourceFile.Worksheets[sheetname]);



        }

        internal void recalculateFormulas()
        {
            sWB.CalculateAllValue();
        }

        public void fillData(String sheetName, DataTable Data, int firstrow = 1, int firstcolumn = 1)
        {
            changeSheet(sheetName);
            active_sheet.InsertDataTable(Data, true, firstrow, firstcolumn);
        }

        public void create_pivot_table_one(String DataSourceSheetName, String pivotTableName)
        {
            try
            {
                //Re-create worksheet pivot1
                Worksheet pivot1 = sWB.CreateEmptySheet();
                pivot1.Name = "pivot1";
                pivot1.AllocatedRange.AutoFitColumns();
                pivot1.AllocatedRange.AutoFitRows();

                //Get Datasource "data"
                Worksheet datasource1 = sWB.Worksheets[DataSourceSheetName];
                CellRange dataRange = datasource1.Range[datasource1.FirstRow, datasource1.FirstColumn, datasource1.LastRow, datasource1.LastColumn - 1];

                #region Debug_Code
                var a = dataRange.CellList;
                List<String> debug = new List<string>();

                foreach (var index in a)
                {
                    debug.Add(index.DisplayedText);
                }
                #endregion

                //Create Pivot Table 1
                PivotCache cache = sWB.PivotCaches.Add(dataRange);

                cache1 = cache;
                PivotTable pt1 = pivot1.PivotTables.Add(pivotTableName, dataRange, cache);

                #region PivotTable1 Proprieties
                pt1.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark4;
                pt1.ShowColumnGrand = false;
                pt1.ShowRowGrand = false;
                pt1.ShowRowStripes = true;
                pt1.Options.MergeLabels = true;
                #endregion

                #region PivotTable1 Row and Column Field Proprieties
                //creating the rows 
                PivotField r1 = pt1.PivotFields["nume"] as PivotField;
                r1.Axis = AxisTypes.Row;
                pt1.Options.RowHeaderCaption = "Row Labels";




                //creating the columns with dates
                PivotField c = pt1.PivotFields["dt"] as PivotField;
                c.Axis = AxisTypes.Column;
                c.NumberFormat = "dd-mmm";
                c.SortType = PivotFieldSortType.Ascending;


                pt1.DataFields.Add(pt1.PivotFields["numar"], "numar_gh", SubtotalTypes.Count); //this won't work
                pt1.DataFields.Add(pt1.PivotFields["total"], "maxim", SubtotalTypes.Sum);
                pt1.DataFields.Add(pt1.PivotFields["ocupate"], "efectuate", SubtotalTypes.Sum);
                #endregion


                pivot1.SetRowHeight(1, 20);
                pivot1.SetColumnWidth(1, 60);

                //cache.Dispose();
            }
            catch (Exception ex)
            {

            }
        }

        public void create_pivot_table_two(String DataSourceSheetName, String pivotTableName)
        {
            try
            {
                //Re-create worksheet pivot2
                Worksheet pivot2 = sWB.CreateEmptySheet();
                pivot2.Name = "pivot2";
                pivot2.AllocatedRange.AutoFitColumns();
                pivot2.AllocatedRange.AutoFitRows();

                changeSheet("pivot2");

                //Get Datasource "data"
                Worksheet datasource1 = sWB.Worksheets[DataSourceSheetName];
                CellRange dataRange = datasource1.Range[datasource1.FirstRow, datasource1.FirstColumn, datasource1.LastRow, datasource1.LastColumn];

                #region Debug_Code
                var a = dataRange.CellList;
                List<String> debug = new List<string>();

                foreach (var index in a)
                {
                    debug.Add(index.DisplayedText);
                }
                #endregion

                //Create Pivot Table 2
                //PivotCache cache = this.cache1;
                PivotCache cache = sWB.PivotCaches.Add(dataRange);
                PivotTable pt2 = pivot2.PivotTables.Add(pivotTableName, dataRange, cache);
               

                #region PivotTable2 Proprieties
                pt2.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark3;
                pt2.ShowRowStripes = true;

                pt2.ShowColumnGrand = true;
                pt2.ShowRowGrand = true;
                pt2.Options.MergeLabels = true;
                #endregion

                #region PivotTable2 Row and Column Field Proprieties
                //creating the rows 
                PivotField r1 = pt2.PivotFields["nume"] as PivotField;
                r1.Axis = AxisTypes.Row;
                pt2.Options.RowHeaderCaption = "Row Labels";

                PivotField r2 = pt2.PivotFields["numar"] as PivotField;
                r2.Axis = AxisTypes.Row;

                PivotField r3 = pt2.PivotFields["dela-panala"] as PivotField;
                r3.Axis = AxisTypes.Row;




                //creating the columns with dates
                PivotField c = pt2.PivotFields["dt"] as PivotField;
                c.Axis = AxisTypes.Column;
                c.NumberFormat = "dd-mmm";
                c.SortType = PivotFieldSortType.Ascending;


                pt2.DataFields.Add(pt2.PivotFields["ocupate"], "efectuate", SubtotalTypes.Sum);
                pt2.DataFields.Add(pt2.PivotFields["total"], "maxim", SubtotalTypes.Sum);

                #endregion


                pivot2.SetRowHeight(1, 20);
                pivot2.SetColumnWidth(1, 60);

                //cache.Dispose();
            }catch (Exception ex)
            {
            }
           
        }

        public void create_pivot_table_three(String DataSourceSheetName, String pivotTableName)
        {
            try
            {
                //Re-create worksheet pivot3
                Worksheet pivot3 = sWB.CreateEmptySheet();
                pivot3.Name = "pivot3";
                pivot3.AllocatedRange.AutoFitColumns();
                pivot3.AllocatedRange.AutoFitRows();
                pivot3.Range["B2"].Style.WrapText = true;
                pivot3.Range["C2"].Style.WrapText = true;


                changeSheet("pivot3");

                //Get Datasource "data2"
                Worksheet datasource2 = sWB.Worksheets[DataSourceSheetName];
                CellRange dataRange = datasource2.Range[datasource2.FirstRow, datasource2.FirstColumn, datasource2.LastRow, datasource2.LastColumn];

                #region Debug_Code
                var a = dataRange.CellList;
                List<String> debug = new List<string>();

                foreach (var index in a)
                {
                    debug.Add(index.DisplayedText);
                }
                #endregion

               
                PivotCache cache = sWB.PivotCaches.Add(dataRange);
                PivotTable pt3 = pivot3.PivotTables.Add(pivotTableName, dataRange, cache);


                #region PivotTable3 Proprieties
                pt3.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;
                pt3.ShowRowStripes = true;

                pt3.ShowColumnGrand = true;
                pt3.ShowRowGrand = true;
                pt3.Options.MergeLabels = true;
                #endregion

                #region PivotTable3 Row and Column Field Proprieties
                //creating the rows 
                PivotField r1 = pt3.PivotFields["nume"] as PivotField;
                r1.Axis = AxisTypes.Row;
                pt3.Options.RowHeaderCaption = "Etichete de randuri";

                PivotField r2 = pt3.PivotFields["dt"] as PivotField;
                r2.Axis = AxisTypes.Row;
                r2.NumberFormat = "dd-mmm";



                pt3.DataFields.Add(pt3.PivotFields["total"], "Suma de total", SubtotalTypes.Sum);
                pt3.DataFields.Add(pt3.PivotFields["libere"], "Suma de libere", SubtotalTypes.Sum);

                #endregion


                pivot3.SetRowHeight(1, 20);
                pivot3.SetColumnWidth(1, 60);
               
                pivot3.Range["B1"].Text = "";

                //cache.Dispose();
            }
            catch (Exception ex)
            {
            }
        }

        public void update_pivot_table_one()
        {
            XlsPivotTable pt = sWB.Worksheets["pivot1"].PivotTables[0] as XlsPivotTable;
            pt.Cache.IsRefreshOnLoad = true;
        }
    }

rares.deoanca21
 
Posts: 7
Joined: Wed Oct 20, 2021 11:31 am

Mon Nov 22, 2021 3:19 am

Hello,

Thanks for your inquiry!

I simulated some files and tested them with the code you provided. And I did reproduce your issue, this issue has been logged it in our issue tracking system with the ticket SPIREXLS-3546 for further investigation.

We will let you know if there is any update. Sorry for the inconvenience caused.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Thu Dec 02, 2021 12:56 pm

Any update?

rares.deoanca21
 
Posts: 7
Joined: Wed Oct 20, 2021 11:31 am

Fri Dec 03, 2021 1:12 am

Hello,

Thanks for your inquiry!

Our Dev team is still working on your issues, sorry to tell that there is no significant progress so far. Anyway, I urged and requested them to share any possible ETA at their earliest, once there is any good news, we will notify you immediately. Sorry for the inconvenience caused.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Wed Jan 05, 2022 11:35 am

Hello there, any progress made about the copy issue?

rares.deoanca21
 
Posts: 7
Joined: Wed Oct 20, 2021 11:31 am

Thu Jan 06, 2022 1:22 am

Hello,

Thanks for your following up!

I just checked the status of your issue and found that it has been resolved. Now it is in the testing phase, once it passes the test, we will inform you ASAP.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Mon Jan 24, 2022 9:37 am

Hello,

Thanks for your patience!

Glad to inform you that we just released Spire.XLS Pack Version:12.1 which fixes the issue SPIREXLS-3546.

Please download the fix version from the following links to test.

Website link: https://www.e-iceblue.com/Download/down ... t-now.html
Nuget link: https://www.nuget.org/packages/Spire.XLS/12.1.0

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Mon Feb 07, 2022 9:48 am

Hello,

Hope you are doing well!

Has the issue been solved now? Could you please give us some feedback at your convenience?

Thanks in advance.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Return to Spire.XLS