Hi,
I'm new to Aspose and I have just downloaded the Aspose for .net version 8.5.2. I want to convert excel files to pdf formates with vb.net programming. Could some one let me know how to start using Aspose. I'm using Microsoft Visual Basic 2010 express.
Thanks in advance.
Hi,
Please see the document for your reference here:
Let us know if I can be of any further help.
Thank you.
Thanks forthe reply. I tried the code in the link provided, and I get the errors.
I have attached an image of the errors.
.
Hi,
Did you first add reference to Aspose.Cells.Dll from your installation folder, please make sure to add reference to the Dll to your project in VS.NET solution explorer, see the document for your reference:
If you still could not evaluate, kindly create a simple console application, zip it and post us here, we will check it soon.
Also, to attach your attachments, please click on Reply button, now you will Add/Update button on lower bottom left corner below this frame/text area, so you may attach your attachments before clicking the Post button to send your reply in the thread.
Thank you.
Hi Bharrgavi,
Thank you for sharing the screenshot. By looking at the image, we suspect that the project is not able to find the references of Aspose.Cells.Workbook class & Aspose.Cells.SaveFormat enumeration. Please try the same scenario using the attached sample project. Please note, the archive includes the assembly from Aspose.Cells for .NET 8.5.2 (net2.0 folder) therefore you just need to extract the contents of the archive, run the project in Visual Studio and change the file path locations of input & output files.
Thanks a lot, The reference was already set, but not from the right location. After setting from .Net reference, worked well.
I'm trying to use the below code:
Dim oWorksheet As Excel.Worksheet
oWorksheet = oApplication.Worksheets(1)
oWorksheet.PageSetup.FitToPagesTall = 1
oWorksheet.PageSetup.FitToPagesWide = 1
oWorksheet.PageSetup.Zoom = False
but when I use it with
Dim wb As Aspose.Cells.Workbook = New Aspose.Cells.Workbook(designerFile)
I'm getting not a member of Aspose.Cells.Workbook error.
Hi,
Do you still have any issue now?
bharrgavi.vijaykumar:
.......I'm trying to use the below code:
Dim oWorksheet As Excel.Worksheet
oWorksheet = oApplication.Worksheets(1)
oWorksheet.PageSetup.FitToPagesTall = 1
oWorksheet.PageSetup.FitToPagesWide = 1
oWorksheet.PageSetup.Zoom = False
but when I use it with
Dim wb As Aspose.Cells.Workbook = New Aspose.Cells.Workbook(designerFile)
I'm getting not a member of Aspose.Cells.Workbook error.
Could you create a simple console application using your code with v8.5.2, zip it and post us here with all the files, we will check it soon.
Thank you.
I have attached a zip folder with sample excel file. I have added the codes that i want to incorporate with this application and also added few comments.
Thanks in advance.
hi,I have added 2.0 as reference,tried with Babar's code it worked to convert excel to pdf. I added the below code from the reference link provided by you to draw borders. It draws borders, but for a specified Range. I want to select the used range of a sheet and then do the border for the entire sheet. Need to do PageSetUp, and also select the folder from FolderBrowserDialog.
Please find the attached zip file with my project.
Waiting for some response. Thanks in advance.
Hi Bharrgavi,
It is good to know that you are able to by pass the previously reported problems. Regarding your recent concerns, please consider the piece of code provided at the last of the post that draws the border around the max display range of a worksheet before saving the result in XLS & PDF formats. Please give it a try on your side. In case you face any difficulty, please provide us the input spreadsheet along with your desired results where the desired output can be generated manually using Excel application. This will help us to understand your goals correctly, and we will be able to provide the customized code snippet accordingly.
Regarding the PageSetup class, please check the
detailed article on the subject.
VB
'Load input spreadsheet
Dim workbook =
New Workbook(“D:/book1.xls”)
'Accessing first worksheet
Dim sheet = workbook.Worksheets(0)
'Creating a range that spans over the all data cells of a worksheet
Dim range = sheet.Cells.MaxDisplayRange
'Adding a thick top border with blue line
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Blue)
'Adding a thick bottom border with blue line
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Blue)
'Adding a thick left border with blue line
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Blue)
'Adding a thick right border with blue line
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thick, Color.Blue)
'Saving the Excel file
workbook.Save(“D:/output.xls”)
'Saving the PDF file
workbook.Save(“D:/output.pdf”, SaveFormat.Pdf)
Thanks for the code. It draws border for the entire Range, but I want to draw lines for all cells of the entire range. Sorry if I have miscommunicated in my previous post.
Hi,
Please see the updated sample code (shared by Babar) for your requirements for your reference:
e.g
Sample code:
'Load input spreadsheet
Dim workbook = New Workbook(“e:/test2/book1.xls”)
'Accessing first worksheet
Dim sheet = workbook.Worksheets(0)
'Creating a range that spans over the all data cells of a worksheet
Dim range = sheet.Cells.MaxDisplayRange
'Create a Style object.
Dim colstyle As Style = workbook.CreateStyle()
colstyle.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
colstyle.Borders(BorderType.LeftBorder).Color = Color.Blue
colstyle.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin
colstyle.Borders(BorderType.RightBorder).Color = Color.Blue
colstyle.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
colstyle.Borders(BorderType.TopBorder).Color = Color.Blue
colstyle.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
colstyle.Borders(BorderType.BottomBorder).Color = Color.Blue
Dim flag As New StyleFlag()
flag.Borders = True
range.ApplyStyle(colstyle, flag)
'Saving the Excel file
workbook.Save(“e:/test2/output.xlsx”)
'Saving the PDF file
workbook.Save(“e:/test2/output.pdf”, SaveFormat.Pdf)
Hope, this helps you a bit.
Thank you.
Thanks a lot Amjad and Babar. The borders worked well. My current requirement is to get the folder location dynamically and not manually writting it in code.
Any suggestions on this?
Thanks in advance
Hi,
Good to know that the borders’ issue is sorted out now.
To get directory (for the files) dynamically, you will use your own .NET code. I think you may try to use (.NET) System.IO APIs to get directory/folder information and location accordingly.
Thank you.
Thanks for the reply. Will try it.
How to find the hidden rows and columns and delete them and also unhide them?
Successfully done with FolderDialog. How to add a listbox to the form?
Also not able to get a work around with hidden columns and rows.
Thanks in advance
Hi,
1) Well, you may simply try to loop through your dataset/rows in the worksheet and use Row/Column.IsHidden attribute to evaluate if it a row/column hidden, so you may delete it accordingly, see the following sample code segment for your reference:
e.g
Sample code:
…
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
int rowCount = worksheet.Cells.MaxRow;
int colCount = worksheet.Cells.MaxColumn;
int i = 0;
…
for (i = rowCount; i >= 0; i–) {
Row row= cells.Rows[i];
if(row.IsHidden)
{
cells.DeleteRow(i)
}
}
…
Similarly you may write code segment to detect hidden columns and delete them accordingly.
2) See the document on how to hide/unhide the row(s)/ column(s) for your requirements:
3) See the document on how to add/update controls including ListBox etc. for your requirements:
Hope, this helps a bit.
Thank you.
This is my code in vb.net, let me know where I'm wrong.
Dim lastRow as Integer
Dim lastColumn as Integer
For lastRow = 1 to ws.Cells.MaxDataRow
For lastColumn = 1 to ws.Cells.MaxDataColumn
lastColumn = + lastColumn
Next
lastRow = +lastRow
Next
For lp = lastColumn to 1 step -1
If ws.Cells.IsColumnHidden(lp) = True
ws.Cells.DeleteColumn(lp)
End If
Next
For lp = lastRow to 1 step -1
If ws.Cells.IsRowHidden(lp) = True
ws.Cells.DeleteRow(lp)
End If
Next
For ListBox, I'm using listBox in VS Form and not in Excel.
Need your guidence.Thanks
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:
Hope, this helps a bit.
Thank you.
I'm working of ListBox.
Will back back fo rany issues.
Thanks a lot