Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

T

I have problem with exporting data from grid(True DB grid) to excel from a VB application. Here the catch is, if grid has more than 65,500 records (max row for an excel sheet), the records in excess of 65,500 should be dumped into next sheet of the same xls file. Similarly, if limit of the second sheet is exhausted, the control should be transferred to the third sheet and so on and so forth. Again we want to provide cancel export option to halt the export mid way through if required. We, have tried Vaspread, but in this case control cannot be passed on to next sheet on exceeding the threshold limit of 65,500 , on using tidestone control cancel functionality could not be implemented as "writeRange" function was not working for xls.

         Can anyone provide a solution to this problem. A third party control or custom code to accompolish this seemingly daunting task..;).
We used Spread Control to achieve the functionality but it requires excel to be installed on the machine. The client requirement is that it should work with out having excel installed on the machine. It would be great, if anyone can provide us a solution wherein without installing excel on the machine, we can still use the spread contolr.
2) We also used Tidestone control but here we are unable to write "Cancel" functionality and while exporting to tab delimited file, we have to export unlimited records. Currently it is accepting 65k rows and above that it writes into another file. Client doesn't want the data in multiple files.

Need solution ASAP.

         
0
ptani
Asked:
ptani
  • 5
  • 3
  • 2
  • +1
1 Solution
 
nmcdermaidCommented:
You could export the data programatically from the grid as CSV. However this does not have any formatting and will not split into different sheets.

If you want to save a file, without Excel installed, that Excel will open, that splits data over sheets, it will have to be an XML file. (Maybe others too)

So it is concievable that you could write some code to expor the data grid data as XML in a format that Excel will recognise.

There may be a solution outside your requirements though..... why not just export it directly from the database rather than a grid control? Why does it have to be in Excel... no user can concievably go through that many rows of data.

If you want more details, let me know.
0
 
nmcdermaidCommented:
Giving it more thought... you can save a recordset as XML. Then you could concievably use XSLT to convert your recordsets XML to Excel XML.

You might even be able to download the XSLT from Microsoft.
0
 
Dang123Commented:
You could try looping through your data and use ADO to insert your data, controling the sheet limit yourself.

This may help you get started:

How to update an Excel spreadsheet using ADO
http://www.vbusers.com/code/codeget.asp?ThreadID=368&PostID=1&NumReplies=0

Copy data from an Access database into an Excel spreadsheet
http://www.vb-helper.com/howto_access_to_excel.html

Copy data from an Excel spreadsheet into an Access database
http://www.vb-helper.com/howto_excel_to_access.html

Using the Excel Object Library
http://www.thescarms.com/vbasic/ExcelExport.asp
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ptaniAuthor Commented:
Thanks all of you for the support. The customer doesn't want to install excel on their server.
Now  i have a new query for you guys..
When I click on the export button, it writes the data to text file and then I save it as .xls. Here the long number is written in exponential format in excel file. Is there any solution to store the number as string in the excel file as is. For eg if the acct # is 111111133333344, then in the excel file it is stored as 1.23 +E1.2 instead of 111111133333344. I want to store it as it is and not in the exponential form.
Any quick solutions will be appreciated.
Thanks,
Ptani
0
 
nmcdermaidCommented:
You should really close this question and ask a new one.

but anyway...

None of my solutions require Excel on the server.


As for your second problem, it's just the formatting in the sheet. If you manually format it as a number it will come through OK.

Also if you precede your value with a single quote, it will appear exactly as you require. However you won't be able to add the figures up.

ie if you export 1000000000000000000 to an Excel sheet it will autoamtically be formatted as 1E+10 (or whatever)

if you export this:

'100000000000000000000000

it will appear as this:

100000000000000000000

but you won't be able to use it in numeric formulas such as SUM or AVG, or be able to add it up.


The only other option is to manually format it, but the only way I know how to do this is write VBA to do it.
0
 
nmcdermaidCommented:
And upon reading your question again, you are never going to add up accounts so the single quote should do the trick.
0
 
ptaniAuthor Commented:
Hello nmcdermaid,
Should I write the formatting code i.e adding single-quote in the VB code?
Again excel is not installed on the machine. Only after save as .xls the customer will be able to see the data.
0
 
ptaniAuthor Commented:
Hello nmcdermaid,
Should I write the formatting code i.e adding single-quote in the VB code?
Again excel is not installed on the machine. Only after save as .xls the customer will be able to see the data.
If you have a sample VB code, pls. send it to me.
0
 
nmcdermaidCommented:
I'm not sure I understand what you're getting at about installing Excel.... the server doesn't have it installed, the client machines do, the server will be creating the Excel file. Is that right?

Yep that is where you would put it.. in your VB export routine.

I don't have any sample code for you because I don't have a 'True DB Grid'.

But it really is a simple matter to loop through the rows and save each row to a text file

The other option as Dang123 suggested is to use the Excel provider, which comes with MDAC, to export the data. I've never done this but it sounds like a good idea.



0
 
Dang123Commented:
I am curious how ptani made out with this.
0
 
Computer101Commented:
PAQed - no points refunded (of 20)

Computer101
E-E Admin
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now