Convert Excel to PDF with Aspose.Cells for .NET in C#

[amjad.sahi]

Hi,

Well, your code looks ok if not the best. Also, you may further refine the code segment as following:

e.g

Sample code:

Dim lastRow as Integer

Dim lastColumn as Integer

lastColumn = ws.Cells.MaxColumn

lastRow = ws.Cells.MaxRow

For lp = lastColumn to 0 step -1

If ws.Cells.IsColumnHidden(lp) = True

ws.Cells.DeleteColumn(lp)

End If

Next

For lp = lastRow to 0 step -1

If ws.Cells.IsRowHidden(lp) = True

ws.Cells.DeleteRow(lp)

End If

Next

If you still have any issue, kindly do provide your template Excel file, so we could evaluate your issue properly.

Regarding adding ListBox control to the form in VS.NET, you may refer to MSDN or browse internet to get plenty of helping materials for your complete reference. For example, the following docs may help you a bit:

http://stackoverflow.com/questions/3819350/list-boxes-in-vb-net

http://www.codeproject.com/Articles/8390/Best-Practice-for-Binding-WinForms-ListControls

Hope, this helps a bit.

Thank you.

[bharrgavi.vijaykumar]

I'm working of ListBox.

Will back back fo rany issues.

Thanks a lot

[bharrgavi.vijaykumar]

Hello Amjad,

I succesfully worked on ListBox and deleting hidden rows also wrks fine.

Now I want to delete the unused columns,rows and also worksheets in excel.

Any suggestion will be helpful.

Thanks

[amjad.sahi]

Hi,

“unused rows, columns” - do you mean blank rows or columns in the worksheet? If this is true, you may use try to use DeleteBlankRows and DeleteBlankColumns methods accordingly:

e.g

Sample code:

…

worksheet.Cells.DeleteBlankRows();

worksheet.Cells.DeleteBlankColumns();

Regarding removing your unwanted worksheets in the workbook, you may use WorksheetCollection.RemoveAt() attribute, see the document for your reference:

https://docs.aspose.com/display/cellsnet/Manage+Worksheets#ManageWorksheets-RemovingWorksheetsusingSheetName

Hope, this helps a bit.

Thank you.

[bharrgavi.vijaykumar]

I'm using the below code to delete the unused sheets. I'm looping through all the sheets, but it is deleting only the 1st found unused sheet. Please let me know where i'm wrong.

Dim ws As Worksheet
For Each ws In wb.Worksheets
Dim sheetName As String = ""
sheetName = ws.Name
ws = wb.Worksheets(sheetName)

Dim lastRow As Integer
Dim lastColumn As Integer

lastColumn = ws.Cells.MaxColumn
lastRow = ws.Cells.MaxRow

If lastColumn = -1 Then
If lastRow = -1 Then
wb.Worksheets.RemoveAt(sheetName)
wb.Save(TextLine)'TextLine is the workbook name
End If
End If

Thanks in advance

[amjad.sahi]

Hi,

Thanks for the sample code segment.

Could you try to change the lines of code:

i.e.,

lastColumn = ws.Cells.MaxColumn
lastRow = ws.Cells.MaxRow

…

to:

lastColumn = ws.Cells.MaxDataColumn

lastRow = ws.Cells.MaxDataRow

…

if it works fine.

If you still find the issue, kindly attach your template Excel file here, we will check it soon.

Thank you.

[bharrgavi.vijaykumar]

Thanks Amjad.

The deletion works for the 1st sheet and throws an exception saying " the collection is modified and can be execute".

[amjad.sahi]

Hi,

As requested earlier, could you please provide us your template Excel file, we will check your issue and help you to figure it out soon.

Thank you.

[bharrgavi.vijaykumar]

Please find the attached excel file.

Thanks in advance.

[amjad.sahi]

Hi,

Thanks for the template file.

Please see the following sample code for your requirements for your reference:

e.g

Sample code:

[C#]

Workbook workbook = new Workbook(“e:\test2\Test_simple.xls”);

int i = workbook.Worksheets.Count - 1;

do

{

Worksheet worksheet = workbook.Worksheets[i];

int mRows = worksheet.Cells.MaxDataRow;

int mCols = worksheet.Cells.MaxDataColumn;

if (mRows == -1 & mCols == -1)

{

workbook.Worksheets.RemoveAt(i);

}

i–;

} while (i >= 0);

workbook.Save(“e:\test2\out1.xls”);

[VB]

Dim workbook As New Workbook(“e:\test2\Test_simple.xls”)

Dim i As Integer = workbook.Worksheets.Count - 1

Do

Dim worksheet As Worksheet = workbook.Worksheets(i)

Dim mRows As Integer = worksheet.Cells.MaxDataRow

Dim mCols As Integer = worksheet.Cells.MaxDataColumn

If mRows = -1 And mCols = -1 Then

workbook.Worksheets.RemoveAt(i)

End If

i -= 1

Loop While i >= 0

workbook.Save(“e:\test2\out1.xls”)

I also noticed one of your worksheet is hidden, so you may also use take appropriate actions if needed by adding further evaluating conditions etc.

Hope, this helps a bit.

Thank you.

[bharrgavi.vijaykumar]

Thanks Amjad, Deleting unused sheets works fine, also i have coded to delete the hidden files.

I would like to do the same without aspose.cells.

I tried but no result.

Here's my code to delete unused excel sheets and hidden excel sheets without aspose.cells

Dim i As Integer = oWorkbook.Worksheets.Count - 1
Do
Dim worksheet As Worksheet = oWorkbook.Worksheets(i)
Dim mRows As Integer = worksheet.Rows.Count
Dim mCols As Integer = worksheet.Columns.Count
If mRows = 1 And mCols = 1 Then
oWorkbook.Worksheets(i).Delete()
End If
'delete hidden sheet
If oWorkbook.Worksheets(i).Visible = False Then
oWorkbook.Worksheets(i).Delete()
'wb.Worksheets(i).VisibilityType = VisibilityType.Visible
End If
i -= 1
Loop While i >= 0
oWorkbook.Save()

'delete hidden worksheet
If oWorksheet.Visible = False Then
oWorksheet.Delete()
End If
oWorkbook.Save()

Any help/suggestions on this pls

[amjad.sahi]

Hi,

Good to know that you have accomplished your desired task/ requirements via Aspose.Cells APIs.

And, I am afraid, these forums are specific to Aspose APIs, so we cannot help you much if you find any issue(s) using Office automation/ VSTO or any other component.

Should you have any query or issue with Aspose.Cells, feel free to contact us, we will be happy to assist you soon.

Thank you.

[bharrgavi.vijaykumar]

Hello Amjad, I understand your concerns.

I would extend my application with Aspose.cells for .Net with converting Outlook emails to pdfs.

The search sould be done within the emails to check for attachments and if .msg files found, convert them also into pdf Inside a mail folder for each parent .msg files.

Any suggestions of this?

Thanks in advance.

[amjad.sahi]

Hi,

Well, for converting Outlook emails to PDF, you would require the following two products provided by Aspose to accomplish the task in two steps:

1) Aspose.Email

2) Aspose.Words

You will first use Aspose.Email component to convert MSG (emails) to MHTML file format and then use Aspose.Words component to convert the MHTML file to PDF file format.

For any confusion, issue or help regarding Aspose.Email Aspose.Words products, feel free to post into Aspose.Total forum here:

https://forum.aspose.com/c/total

Thank you.

[bharrgavi.vijaykumar]

Thanks for the links. Will check it.

One more query about aspose.cells.

Pls find the attached. the excel contains textbox, when conveting to pdf, the textbox is not copied to pdf.

[amjad.sahi]

Hi,

Thanks for the template file.

After an initial test, I observed the issue as you mentioned. I found the template Excel file that contains a WordArt text when converted to PDF file format, this text does not render at all.

e.g
Sample code:

Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(filePath);
wb.Save(“out1.pdf”);

I have logged a ticket with an id “CELLSNET-43907” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.

[bharrgavi.vijaykumar]

Thanks a lot.

[amjad.sahi]

@bharrgavi.vijaykumar,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

[aspose.notifier]

The issues you have found earlier (filed as CELLSNET-43907) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao