Sharon Niles
|
| Posted: 07/02/2003, 10:31 AM |
|
Hi Everyone. I've been able to download my grid into Excel. No problem. But in Excel, the number columns are text. You can view and sort the number columns. But you can't add, subtract, etc. The number values are 0. Any suggestions?
|
|
|
 |
rrodgers
|
| Posted: 07/02/2003, 11:04 AM |
|
are you using cc or ccs?
If ccs try my export class at
www.sylvancomputing.com/ccs
It exports as csv. When this opens in excel it lets me do calculations on the cells with numbers. You can also change the export type from csv to excel if you wish by changing the content type by using the Contenttype property and you can change the file name that is exported by using the HeaderValue() method.
obj.Contenttype = "application/vnd.ms-excel"
obj.HeaderValue("Content-Disposition","filename=YourFavoriteNameHere.xls")
rob
|
|
|
|
rrodgers
|
| Posted: 07/02/2003, 11:28 AM |
|
>>obj.HeaderValue("Content-Disposition","filename=YourFavoriteNameHere.xls")
That should have been
obj.AddHeader("Content-Disposition","filename=YourFavoriteNameHere.xls")
rob
|
|
|
|
GreggB
|
| Posted: 07/02/2003, 11:52 AM |
|
I had the same problem and if I remember correctly, CCS sometimes puts a leading " " (space) in front of the variable/tag in the grid. So, in design mode make sure there are no spaces/hidden characters by deleting before and after the variable and tags.
GreggB
|
|
|
|
Sharon Niles
|
| Posted: 07/02/2003, 3:35 PM |
|
Dear Rob and Gregg, Thank you both for responding. Rob, I did download the Excel Export example, but I have almost 30 reports that will be downloaded for my client. I was hoping for a solution that didn't require so much coding.
For each report I have two pages. One page they can view online and print the report. The second page they can download to excel. The only difference between the two is that the export page has the following under Page_AfterInitializeL
Response.ContentType = "application/excel"
Response.AddHeader "content-disposition","attachment;filename=billingsummary.xls"
Gregg, you mentioned "CCS sometimes puts a leading " " (space) in front of the variable/tag in the grid. So, in design mode make sure there are no spaces/hidden characters by deleting before and after the variable and tags." Where do I check for this. All are from a control source.
BTW, I'm using the newest version of CCS.
Thanks again,
Sharon
|
|
|
|
rrodgers
|
| Posted: 07/02/2003, 4:26 PM |
|
>>For each report I have two pages. One page they can view online and print the report. The second page they can download to excel. The only difference between the two is that the export page has the following under Page_AfterInitializeL
<<
It will export from your class in 4 lines, plus you don't need to maintain a second page.
Dim x
Set x = New clsExportCSV
x.ExportGridRecords MyGrid.Recordset
Set x = Nothing
|
|
|
|
Sharon Niles
|
| Posted: 07/02/2003, 9:51 PM |
|
Hi Rob,
I appreciate your reply. Where do I write the 4 lines? Page_AfterInitialize or Page_BeforeUnload?
Do I need to include this file?
<!-- #INCLUDE FILE="scclasses.asp" -->
What do you mean by "it will download from your CLASS?"
Thank you,
Sharon Niles
|
|
|
|
Sharon Niles
|
| Posted: 07/02/2003, 10:59 PM |
|
Hi Rob,
I got it to work, but the export is different than I expected. In your method, (which does allow me to manipulate the number columns) the excel file is the "Rough Draft" Access query. Whereas the other method:
Response.ContentType = "application/excel"
Response.AddHeader "content-disposition","attachment;filename=billingsummary.xls"
gave a replica in Excel of the entire on-line page. Of course, the big drawback is that the number columns are text.
Anyways, I appreciate your solution. I'm going to go with it. But if you have any other ideas, feel free to pass them on.
Sincerely,
Sharon Niles
|
|
|
|
rpoole
|
| Posted: 07/03/2003, 5:38 AM |
|
Sharon,
I would be interested in how you implemented the code suggestion, I too am currently using the 2-page approach as was your original solution. I us W2K, IIS, ASP, VB Script, and latest version of CCS. Thanks.
Randy
|
|
|
|
Sharon Niles
|
| Posted: 07/03/2003, 7:10 AM |
|
Hi Randy,
Go to Rob's website: www.sylvancomputing.com/ccs for the working example. You can also download his example.
Sharon
|
|
|
|
GreggB
|
| Posted: 07/03/2003, 7:11 AM |
|
If you are still interested in the fix for the excel export:
There is a space added after every variable by CCS in a Grid. For instance, you have a grid with a column named Cost and in the row of the column “Cost” you have the variable [A]{price}[A] for the product. The extra space that CCS adds is at the right just after the [A]_ on the right. You can observe this by seeing the left [A] is closer to the cell wall than the right [A]. Delete the space on the right and the cell wall moves closer to the [A]. This space is what makes Excel treat the numbers as text.
Grid Column “Cost”
___ Cost___
[A]{price}[A]_ <- Space to be deleted.
^
Delete the trailing space and Excel will handle the column as numbers.
I use this method because exported spreadsheets are .xml. By using this method I am able to define many of the settings in the spreadsheet all the way to “Page Setup” before exporting. This eliminates the need for the user to make formatting changes, etc. The spreadsheet is mostly ready to use and print with very little or any user intervention.
Here’s the basic code I use excluding the Header.
With Response
.Buffer = True
.ContentType="application/vnd.ms-excel"
.Flush
.Clear
End With
The downside as Rob stated is you need to keep two grids. One grid for display and the other one for exporting.
GreggB
|
|
|
|
Sharon Niles
|
| Posted: 07/03/2003, 9:20 PM |
|
Hi GreggB,
You're wonderful. That's the exact solution I was looking for. You should publish it in the Tips and Articles section.
Thanks,
Sharon
|
|
|
|
Ashiff
|
| Posted: 07/05/2003, 9:46 AM |
|
I had the same problem and when I wanted a solution not only did I get the question listed by Sharon but also the answer by rogers and GreggB.
Thanks guys if not for you it would have not been over in 10 minutes.
Keep up the good work
|
|
|
|
Fred
|
| Posted: 07/13/2003, 6:57 AM |
|
How do you change the formatting in CC 2.0 to eliminate the space
|
|
|
|
GreggB
|
| Posted: 07/14/2003, 10:10 AM |
|
I'm not sure, but the CC .html page with the grid has spaces as I described above. Try removing the spaces at the end of the variables in the cells of the grid you are using for the Excel export.
GreggB
|
|
|
|
