How to delete a whole table

Aspose.Cells Product Family
1

Hi

I have a worksheet which contains a table which I want to completely remove . I start with the list object to get all the range information of that table and call Delete Range and save the workbook , however when I try to open it , I get a message that it is unreadable and do I want to recover , and when I click yes I see that the columns headers still remain.

what I need is to COMPLETELY remove the whole table from the excel worksheet (including the column headers of the table. (I was excepting some way to delete the whole listObject but couldnt find anything)

please help.

THanks.

foreach(ListObject listObject in worksheet.ListObjects)
{
if(listObject.DisplayName == "TableForRemoval")
{
worksheet.Cells.DeleteRange(listObject.StartRow, listObject.StartColumn, listObject.EndRow, listObject.EndColumn, ShiftType.Up);
}
}v

2

Hi,

Thanks for your posting.

If I am not mistaken, you are actually trying to delete all the sheet contents as you mentioned in your previous post.

As a quick workaround, would you like to delete the entire sheet and add new sheet with the same name? This will have a same effect of deleting all the rows. list tables, headers and everything inside a sheet.

Please see the code below. I have attached the source and output xlsx workbook for your reference.

If you will open the output xlsx file, you will see that your old sheet exists but everything inside it has been deleted, it is totally empty.

C#


string path = @“F:\MyBook.xlsx”;


Workbook workbook = new Workbook(path);


//Access your old worksheet

Worksheet oldSheet = workbook.Worksheets[0];


//Access old sheet

string oldSheetName = oldSheet.Name;


//Add new worksheet

int idx= workbook.Worksheets.Add();

Worksheet newSheet = workbook.Worksheets[idx];


//Delete old sheet

workbook.Worksheets.RemoveAt(oldSheetName);


//Rename the new sheet with the same old name

newSheet.Name = oldSheetName;


//Save the workbook

workbook.Save(path + “.out.xlsx”);

Screenshot:

3
Hi,

You can delete a complete list object by removing it from the ListObjectCollection as follow:

ListObjectCollection loc = worksheet.ListObjects;

int iListObjectIndex = 0;

foreach(ListObject listObject in loc)

{

if(listObject.DisplayName == "TableForRemoval")
{
loc.RemoveAt(iListObjectIndex);
break;
}
iListObjectIndex++;
}

4

Hi,

Please download and try the latest version/fix: Aspose.Cells for .NET (Latest Version)

I have tested your case with my simplest code and it works fine, the output file (attached) does remove the whole table (list object) fine.

Sample code:

Workbook wb = new Workbook(“e:\test2\MyBook.xlsx”);

Worksheet worksheet = wb.Worksheets[0];

ListObject listObject = worksheet.ListObjects[“TableForRemoval”];

worksheet.Cells.DeleteRange(listObject.StartRow, listObject.StartColumn, listObject.EndRow, listObject.EndColumn, ShiftType.Up);

wb.Save(“e:\test2\outMybook.xlsx”);

Thank you.

5

Hi Amjad

there seems to be something wrong with the download link you sent. can you please check

THanks.

6

Hi,


I have fixed it and you can also download the latest fix v7.2.1.3 here:
https://forum.aspose.com/t/113169

Let me know if you still find any issue.

Thank you.

7

Hi Amjad

There is good news and bad news.I tested your code with a version I downloaded yesterday 7.2.1.1

The good news is that for the specific file I sent it works.HOWEVER , for some reason I get a System.NullReferenceException within the Save method when trying to save a real excel book that was created by application.

This is REGARDLESS of removing a table.All I do is Open the workbook and save without doing anything elese and I get an exception . I have attached our excel file for you to check . please check this exception .

Workbook b = new Workbook((@"C:\tmp\Book1.xlsx"));

b.Save(@"C:\tmp\saved.xlsx") --->EXCEPTION

Please help

Thanks.

System.NullReferenceException was unhandled Message=Object reference not set to an instance of an object. Source=Aspose.Cells StackTrace:

at Aspose.Cells.Workbook.Save(String fileName, SaveOptions saveOptions)

at Aspose.Cells.Workbook.Save(String fileName) at ExcelAsposeTest.Form1.buttonOpen_Click(Object sender, EventArgs e) in C:\Work\Sample Projects\ExcelAsposeTest\ExcelAsposeTest\Form1.cs:line 47 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at ExcelAsposeTest.Program.Main() in C:\Work\Sample Projects\ExcelAsposeTest\ExcelAsposeTest\Program.cs:line 18 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException: System.NullReferenceException was unhandled Message=Object reference not set to an instance of an object. Source=Aspose.Cells StackTrace: at Aspose.Cells.Workbook.Save(String fileName, SaveOptions saveOptions) at Aspose.Cells.Workbook.Save(String fileName) at ExcelAsposeTest.Form1.buttonOpen_Click(Object sender, EventArgs e) in C:\Work\Sample Projects\ExcelAsposeTest\ExcelAsposeTest\Form1.cs:line 47 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at ExcelAsposeTest.Program.Main() in C:\Work\Sample Projects\ExcelAsposeTest\ExcelAsposeTest\Program.cs:line 18 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:

8

Hi,

Thanks for providing the template file.

Yes, I can find the issue as you have mentioned by simply opening and re-saving your template file “Book1.xlsx”. I have logged a ticket for your issue with an id: CELLSNET-40696. We will soon look into it.

Thank you.

9

Hi Amjad

Thanks . waiting eagerly for a fix since we will not be able to work without it.

BTW please note that our excel files contain custom properties , just might be related , (or maybe not).

Thanks

10

Hi,

Thanks for your posting.

We will soon update you when the fix will be released by posting here.

Also, we have logged your comment against the issue id: CELLSNET-40696.

It could be helpful for development team in sorting out this issue.

11

Hi

1. How exactly do I login to the issue id link (in jira) . It doesnt seem to work with my regular user password.

2. When fixing this issue, please make sure dev also checks that the file can be opened again from excel after the save , because I noticed that with Apose ver 6.0 , the save doesnt crash however when trying to open the saved file afterwards via excel , excel gives an error message that the data is corrupt.

Thanks.

12

Hi,


Well you can not login to JIRA as it is specific to Aspose staff members only. However, we will provide you any update from JIRA if there is anything available from our development team.

Regarding your issue of “Data is corrupt”, your provided workbook file is already under analysis. I have logged your concerns in our database again for the logged issue, and our development team will look into it.

13

Hi,

You cannot access JIRA issue. It is for our internal discussion with development team.

However, when we release the major version, we also publish these JIRA ids that mention which of the issues have been fixed.

Also, we keep track of your issue with these JIRA ids.

Whenever your require some update or need to provide further information, you can post on your thread and we will provide you update and log your information.

Please see the sample release notes at the following link. I have also attached the screenshot for your reference.

Release Notes - Aspose.Cells for .NET 7.2.1

Screenshot:

14

Hi,


We have fixed this issue. Please download and use this latest fix of Aspose.Cells for .Net 7.2.1.4, and let us know your feedback.

For your reference, latest version can be downloaded here:
Aspose.Cells for .NET (Latest Version)

15

Hi

Great work , it does fix this issue.

However now I found a new issue .... I will open a seperate thread for it.

Thanks for the great work on this defect.

16

Hi,

Thanks for your feedback.

It’s good to know that your issue is resolved now. Please feel free to post your additional issues.

We will help you asap.

17

The issues you have found earlier (filed as CELLSNET-40696) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.