copy only visible rows

[SerPivas]

Hello!

I need create a pivot table. When I insert cell range, pivot table take value from hidden rows, but I don't need it. Can I delete hidden rows, or copy only visible rows to another sheet? cuz I created autofilter, and don't know cell range

[Annika.Zhou]

Hello,

Thank you for your inquiry.
To copy only the visible rows from a sheet, you can refer to the following code:

Code: Select all

// Create a new Workbook object
Workbook workbook = new Workbook();

// Load an existing workbook from file
workbook.LoadFromFile("input.xlsx");

// Get the first worksheet from the workbook
Worksheet worksheet = workbook.Worksheets[0];

// Add a new worksheet to the workbook
Worksheet newSheet = workbook.Worksheets.Add("AddSheet");

// Initialize a variable to keep track of the row index in the new sheet
int index = 0;

// Loop through each row in the original worksheet
for (int i = 1; i <= worksheet.Rows.Length; i++)
{
    // Check if the row is hidden
    if (worksheet.GetRowIsHide(i))
    {
        // Skip the hidden row and continue to the next iteration
        continue;
    }
    else
    {
        // Insert a new row at the specified index in the new sheet
        newSheet.InsertRow(index + 1);

        // Copy the entire row from the original worksheet to the new sheet
        worksheet.Copy(worksheet.Rows[i - 1], newSheet.Rows[index], true, true, true);

        // Increment the index for the new sheet
        index++;
    }
}

// Save the modified workbook to a new file in the Excel 2016 format
workbook.SaveToFile("result.xlsx", FileFormat.Version2016);

To delete only the visible rows from a sheet, you can refer to the following code:

Code: Select all

// Create a new Workbook object
Workbook workbook = new Workbook();

// Load an existing workbook from file
workbook.LoadFromFile("input.xlsx");

// Get the first worksheet from the workbook
Worksheet worksheet = workbook.Worksheets[0];

// Loop through each row in the worksheet
for (int i = 1; i <= worksheet.Rows.Length; i++)
{
    // Check if the row is hidden
    if (worksheet.GetRowIsHide(i))
    {
        // Delete the current row and decrement the loop counter to account for the deleted row
        worksheet.DeleteRow(i);
        i--;
    }
}
// Save the modified workbook to a new file in the Excel 2016 format
workbook.SaveToFile("result.xlsx", FileFormat.Version2016);

If you have any further questions or need assistance with anything else, please feel free to ask.

Sincerely,
Annika
E-iceblue support team

[SerPivas]

thanks!

Could you help me pls
How can i group columns by day?

[Annika.Zhou]

Hello,

Thank you for your feedback.
We regret to inform you that our current product does not support the Grouping feature for Pivot Tables. However, we have taken note of this functionality and it has been added to our upgrade list with the reference number SPIREXLS-765.
Once this feature is implemented in our product in the future, we will promptly notify you.
If you have any further questions or need assistance with any other features, please feel free to let us know.
Thank you for your understanding.

Sincerely,
Annika
E-iceblue support team

[William.Zhang]

Hello,

Thanks for your patience.
Glad to inform that we just released the latest Spire.XLS Pack(Hotfix) Version:14.3.3 which has implemented pivot table grouping function(SPIREXLS-765). Please update and refer to the following code snippet for testing.
Website link: https://www.e-iceblue.com/Download/down ... t-now.html
Nuget link: https://www.nuget.org/packages/Spire.XLS

Code: Select all

XlsPivotTable pt = worksheet.PivotTables[0] as XlsPivotTable;
IPivotField field = pt.RowFields[0];
DateTime start = new DateTime(2024, 5, 6);
DateTime end = new DateTime(2024, 10, 6);
PivotGroupByTypes[] types = new PivotGroupByTypes[]{ PivotGroupByTypes.Days };
field.CreateGroup(start, end, types, 1);


Sincerely,
William
E-iceblue support team