Keeping only selected Columns and from Selected Workshets

Aspose.Cells Product Family
1

We have purchased the product. Kindly let me know if there are other forums for paid members.

I had a requirement where I was supposed to save only selected columns from selected sheets.

Let me explain.

Let’s say I have a workbook.

It has 4 worksheets–>book1,book2 book3 and book4.

I need only selected columns from selected sheets.

book1–>column A to Column D.
Book2–>column B to Column E.
Book3–>All columns.
Book4–>Not required

Now I want to save it in 2 formats which is pdf and .xlsx[this decision will be made at run time].

The original workbook should not be impacted, when I am doing these modifications.

What I have tried?

  1. For deep copy I have used .copy.
  2. For selecting only selected columns I have used .PrintArea–>But this does not work when I save as .xlsx.

If I use .HideColumns, it works when I save as pdf as well as .xlsx. But Its tricky because I have what columns I need and not what columns I don’t need.

2

@arya220,

Thanks for providing us details.

Well, when you set PrintArea of the sheets, it will only help/working in rendering to PDF file format. The PDF pages are rendered based on what is shown in the print preview for different sheets in the workbook. I think a simple way to accomplish your task is via copy range(s). The process should be simple as following:

  1. Create a parallel empty new (destination) workbook (it should contain three sheets or you will add three empty sheets in it).
  2. Create a range i.e., range1 (e.g for A1:D10 - you may change the range accordingly) based on original workbook’s book1(sheet) cells. Create similar sized range (e.g r1) in new (empty) workbook’s sheet1 cells.
  3. Create another range i.e., range2 (e.g for B1:E10 - you may change the range accordingly) based on original workbook’s book2(sheet) cells. Create similar sized range (e.g r2) in destination workbook’s sheet2 cells.
  4. copy the ranges b/w workbook, i.e., r1.Copy(range1) and r2.copy(range2)
  5. Copy the book3 sheet to destination workbook’s third sheet.
  6. Save the destination workbook to XLSX and PDF file format.

See the document on how to copy ranges for your reference:

Hope, this helps a bit.

3

@arya220,

The best approach for your requirement is you can delete those unwanted columns (the columns which you do not want to keep) from the workbook, then save the workbook to PDF or Excel file. See the document on how to delete rows and columns for your reference:

Also, when saving to Excel file format, just use a different file path or name from the original template file to avoid the template being overwritten. This way the task should be more easier and efficient.

4

Thanks. This Helps.