Home » VB.Net

Export To Excel With Column Formatting


Please...Help save what little hair I have left :)

I need to export data from SQL Server Express 2008 to Excel.
The Excel file needs to have headers.
I need numbers to be exported as numbers and not stored as text.  This is my problem.

I've Googled, searched and read for hours, but no solution actually seems to work.  The code below is what I have.  Which works except for it storing everything as text.  I need IDField and Quantity to be stored as numbers.  I have an existing XLS template file I'm exporting the data into.  All suggestions are warmly welcomed :)

Edit:  If I put example data under my header in row 2 of the Excel file, it then exports correctly.  So then I'm left with an extra line.  As a hacky solution, anyone know how to delete a row of Excel data from SQL Server?


SET @FileName =''
SET @SQL =''

SET @FileName ='Excel 8.0;Database='+ @FileNameTwo +''

+ @FileName +''',

''SELECT * FROM [Sheet1$]'') SELECT IDField, Code, Quantity FROM tblMyTable'

Exec (@SQL)


31 Answers Found


Answer 1

One thing you could do is use a template excel  sheet with the headers already in place. Under ssis, I would create a script task that makes a copy of your file and to adjust your connection manager to point to the new file. You can do all of this in code. Then, export  your data  to the new sheet (using the modified connection manager). Be sure to omit your first line if that contains header data.


Answer 2


Thanks for the reply.  Excuse my ignorance here.  But I'm already exporting to a template file with headers in it and that doesn't work.  Would doing what you suggest, which I have no clue how to do :), be any different?



Answer 3

I am going to give you another suggestion... Assuming you want to use openrowset, Export your data  as a csv. Then, open the csv from within excel, specifying the datatype for yoru columns. Another approach that may work is a format file: http://msdn.microsoft.com/en-us/library/ms178129.aspx.



Answer 4

I don't want  to use OpenRowSet.  I want to use anything that works!  I can't export  the data  to a CSV, it has to be an Excel file.

Thanks for the link but BCP can't export column  headers, that I'm aware of, so that won't work either.  :(

Answer 5

Then I would suggest using SSIS as the way to export  your data  to excel. You will find that ssis provides a lot of flexibility that is not found in other approaches. Check out this resource: http://www.docstoc.com/docs/9745851/SSIS-Tutorial If you are new to SSIS, this should help.  Here are links for the various tech walkthroughs on ssis.


Answer 6

Thanks again but with SQL Express you can't use SSIS as you can't save  packages.  So I can't use that either.

Answer 7

Sorry about that...Missed the fact you were talking about Express.. How comfortable are you with automation? You could spin through a datatable and run the data  into Excel that way. That would give you complete control over the process.

Answer 8

Comfortable with anything that works.  But I know nothing about automation.  Can you point me in the right direction so I can check it out?

Answer 9

You can do a couple of things here.  First for the header you can either specify hdr=1, if you header exists in your spreadsheet, or you can use UNION ALL to append a header to your result set.  Now for the numbers being treated as text.  Excel should automatically convert any columns with numeric values to numbers automatically.  If it is not, are you sure your column  is comprised of numbers strictly?  You can try using the value() function in your output statement, as shown below.

		@sql VARCHAR(500)

SET @FileName = ''
SET @SQL = ''

SET @FileName = 'Excel 8.0;hrd=Yes;IMEX=1;Database=' + @FileNameTwo + ''
--hdr is the header,IMEX=1 transforms columns with text and numeric values to text

SET @SQL = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''' + @FileName + ''', 
''SELECT * FROM [Sheet1$]'')  SELECT ''=VALUE('' + IDField + ''), Code, Quantity FROM tblMyTable'
--note you may need a convert the idfield to varchar if it is actually stored as a number.

--Exec (@SQL)
PRINT @sql


Answer 10

Hi Adam

It all sounds so simple!  'VALUE Solution'  I can't have =VALUE(X) in my cells.  I just have to have the number.  And from what you're saying it sounds like I don't need that as Excel will do what I want automatically.

So I'll make it simple, the proc below does not work for me, does it work for anyone else?  The numbers are exported as text. 

You'll need a dummy XLS file with 3 textual headers in it.

Some points:
* If I don't have a header in the XLS, regardless of the setting of HDR, I get the error "Column name or number of supplied values does not match table definition."
* If I set IMEX=1 I get the errors (setting IMEX=0 produces no error):

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server  "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Msg 7343, Level 16, State 2, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.Jet.OLEDB.4.0]".

What am I doing wrong here? 







DECLARE @FileName VarChar(4000)

DECLARE @FileNameTwo VarChar(4000)

DECLARE @SQL VarChar(4000)

SET @FileName =''

SET @FileNameTwo ='C:\Test.XLS'

SET @SQL =''

SET @FileName ='Excel 8.0;hrd=no;IMEX=1;Database='+ @FileNameTwo +''

SET @SQL ='INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', '''+ @FileName +''',

''SELECT * FROM [Sheet1$]'') SELECT 1,2,3'




Answer 11



Answer 12

Hi Ye11ow

I can get you a little way along: You are using a parameter @FileName in your OPENROWSET statement - it should be @FileNameTwo.

I don't have the Jet provider installed though, so can't test it here.


Answer 13

Hi Ewan

Thanks for they reply.

@FileName is correct as it's holds the file name C:\Test.xls and the connection string parameters.  The code above works, except that it does not export  the numbers as numbers, it exports them as text.


Answer 14

Ooops. Sorry about that.


Answer 15

Looks like IMEX will not solve your problem: http://support.microsoft.com/kb/194124

Found a test box with Jet and managed to play around with some variations on this. IMEX=2 doesn't work either, headers or not.


Answer 16

Unless I set IMEX=0 it doesn't work at all for me.

I get the same behaviour if I export  an ADO.net dataset to Excel.  There HAS to be a way of exporting a number as a number.  I can't be the only person in the world who wants to export this way....can I? :)

I've spent days on this now.  I've read about IMEX, MAXSCANROWS.  It all does nothing.  I've read that Microsoft says Excel guesses what the column  format will be... http://support.microsoft.com/kb/316934  Guesses!  Well it isn't doing a very good job is it.


Answer 17

Is there anything in your column  that is not a number?  What exactly is happening to the numeric value, is it being trimmed, is it null?  You may want to consider using SSIS, as you can more granularly control data  types and formatting  of data.  You can try running the code below to see if any id are not numbers.

select * from mytable where isnumeric(MyId) = 0

I also did a little research and it seems you cannot specify imex =1 for an insert, so you need to have imex set to either 0 or 2. So that is my bad. 

SQL to excel  via SSIS

Answer 18

Hi Adam

Everything is a number.  100%.  When the data  is exported it's exported as text, with the Excel tag 'Convert To Number'.

Looking at the sproc above, 'XXXXX'.  That doesn't work and it has numbers 1, 2 and 3 as the output.  They're definitely numbers, so should be exported as such?  Does that work for you?

I can't use SSIS as I can't save  packages.


Answer 19

The numbers are stored as text and not numbers, even though the column  type is numeric.  I think what you are hitting is a limitation of the driver. Sorry about the SSIS reference, I forgot you are using Express.  The only solution I can find is to add a dummy record right underneath your header columns, like all zeros. 

So your Excel should look like this:

col1     col2     col3
0         0         0

You can then code your openrowset as such.

INSERT INTO openrowset('Microsoft.Jet.OLEDB.4.0' ,   
'Excel 8.0;Database=c:\test.xls;hdr=Yes,IMEX=2','Select * from [Sheet1$]')   
select 1,2,3

Answer 20

Thanks Adam, but that is where I was in my my first post.  I can't say to our client, "oh just ignore that dummy line of data".  They won't accept it.  And rightly so. 

This is embarrasing, what a limitation this is to not be able to export  a number as a number.

The 'solutions' I have come up with:

1:  Go with the dummy line of data.  UPDATE the first row of Excel data  (The zeroes) to be the first row of the output.  Then INSERT everything excluding the first row of data.  This should work, but I'm not sure I trust Excel/SQL server  to always export the data as a number even with a dummy line as I have had incosistant results even with a dummy line of data.

2:  Do what I didn't want to do and run this from VB.net and send a dataset to an Excel worksheet.  Full details of how to do that are here http://support.microsoft.com/kb/316934

Thanks all for all your help.  If anyone has a solution to this I'd be very happy to hear it because I don't (can't?!) believe it isn't possible.



Answer 21

<<This is embarrasing, what a limitation this is to not be able to export  a number as a number.

Well.... this is not technically the case.  SQL is submitting the column  as a number; however, the problem is Excel is treating it as a text  This seems like a driver/Excel limitation to me, not SQL.  Another thing you have to remember is that the Jet driver does not work in a 64 bit SQL Server install. If any of your clients are using the above code on a 64 bit SQL install, the code will simply not run and a error stating they must register the driver will occur.  I hope that someone else can provide support because I am fresh out of ideas.



Answer 22

OK this is what I did.  In my post above I can't get option 2 to work with SQL server.  So I've gone with option number 1.

Create a linked server  to your existing XLS sheet.  I have 1 worksheet, Sheet1, and 3 columns ColA, ColB and ColC.  My linked server is called 'Final'.  In the XLS, I have 0, A and 0, in the respective columns as I want numeric, text and numeric.

SET ColA =(SELECTTOP 1 MyLocation FROM tblLocations ORDERBY Location)
WHERE ColA = 0

SET ColB =(SELECTTOP 1 MyArea FROM tblLocations ORDERBY Location)

SET ColC =(SELECTTOP 1 MyNumber FROM tblLocations ORDERBY Location)
WHERE ColC = 0

INTO Final...Sheet1$
SELECTTOP (100)PERCENT dbo.tblLocations.MyLocation, dbo.tblLocations.MyArea, dbo.tblLocations.MyNumber
FROM dbo.tblLocations INNERJOIN
(SELECTTOP (1) MyLocation, MyArea, MyNumber
FROM dbo.tblLocations AS tblLocations_1
ORDERBY Location)AS derivedtbl_1 ON dbo.tblLocations.Location <> derivedtbl_1.Location
ORDERBY dbo.tblLocations.Location

This is a lot of messing about, probably not terribly efficient, but right now I have no other choice.  And this seems to be working right now for me.


EDIT:  I've altered this slightly by using OPENROWSET instead of a linked server.  So now I'm using something like this for each of the 3 UPDATE statements.


OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\Test.xls;HDR=yes','SELECT * FROM [Sheet1$]') SET ColA = NewValue WHERE ColA = OldValue


Answer 23

Is this what you are looking for:
      Query Results
         sql  Server
            Results to Grid
               Check the box titled "Include column  headers when copying or saving the results"
               Click the OK button

Note you will probably get the message:
"SQL server  Results to Grid option changes will only be applied to new SQL Server Query Editor windows."
Click OK


Answer 24


The problem wasn't that I couldn't export  column headers, it was that numbers are exported as text (Or Excel reads them as such) when exported from SQL Server.



Answer 25

Hi Yellow, i have the same problem, i am trying to export  numbers in a Excel Sheet and these are exported as text , i was checking this forum but for me solution is not clear , may be i miss  something, i would appreciate your help  telling me how did you solve the problem?

Thanks a lot

This is basically what i got:

    Select @fn = 'C:\invoice.xls '
    set @provider = 'Microsoft.Jet.OLEDB.4.0'
    set @ExcelString = 'Excel 8.0;Database=' + @fn

exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT id1, id2  FROM  [Invoices$]'')

    SELECT DISTINCT  id1,id2 from Table1 ')


Answer 26

why not you consider the Excel Macro Report ??

Like this ?

Private Sub GenerateReportCommandButton_Click()
    Dim SQLStatement
    Dim MyConn As ADODB.Connection
    Dim MyRS As ADODB.Recordset
    Dim Ws As Worksheet
    Dim Country As String
    Dim Region As String
    Dim Service As String
    Dim ServiceText As String
    Dim Date1 As Date
    Dim Date2 As Date
    Dim passDate1 As String
    Dim passDate2 As String
    Set MyConn = New ADODB.Connection
    Set Ws = Sheets("AcerUpdateDetails")
    Country = Sheets("Input").Range("A5").Value
    Region = Sheets("Input").Range("A9").Value
    Service = Sheets("Input").Range("A12").Value
    ServiceText = Sheets("Input").Range("B12").Value
    Date1 = Range("B4").Value
    Date2 = Range("B5").Value
    If Date1 > Date2 Then
        MsgBox "From date Cannot be greater than To date.", vbCritical, "BIOS Update Summary Report"
        Exit Sub
    End If
    Ws.Range("B2").Value = Date1
    Ws.Range("B3").Value = Date2
    Ws.Range("B4").Value = Region
    Ws.Range("B5").Value = Country
    Ws.Range("B6").Value = ServiceText
    Ws.Range("B7").Value = Now()
    If Country = "All" Then Country = ""
    If Region = "All" Then Region = ""
    If Service = "All" Then Service = ""
    Application.ScreenUpdating = False
    LASTROW = Range("D65535").End(xlUp).Row
    Set MyConn = New ADODB.Connection
    Set Ws = Sheets("AcerUpdateDetails")
    con_String = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=UserID;PWD=Password;Initial Catalog=DB;Data Source=SERVER"

    MyConn.Open con_String
    dest_sheet = "Sheet1"
    Set Ws = Sheets(dest_sheet)
    dest_cell = "A11"
    Ws.Cells(10, 1) = "ID"
    Ws.Cells(10, 2) = "Model"
    Ws.Cells(10, 3) = "Number"
    Ws.Cells(10, 4) = "SNID"
    Ws.Cells(10, 5) = "Request Created Date/Time"
    Ws.Cells(10, 6) = "Created Month"
    Ws.Cells(10, 7) = "Operating System"
    Ws.Cells(10, 8) = "Option"
    Ws.Cells(10, 9) = "Contact"
    Ws.Cells(10, 11) = "First Name"
    Ws.Cells(10, 12) = "Last Name"
    Ws.Cells(10, 13) = "Address"
    Ws.Cells(10, 14) = "City"
    Ws.Cells(10, 15) = "State"
    Ws.Cells(10, 16) = "Country"
    Ws.Cells(10, 17) = "Email"
    Ws.Cells(10, 18) = "Phone"
    Date2 = Date2 + 1
    passDate1 = Format(Date1, "YYYY-MM-DD")
    passDate2 = Format(Date2, "YYYY-MM-DD")
    SQLStatement = "Select * From Table1"
    MyConn.CommandTimeout = 0
    Set MyRS = MyConn.Execute(SQLStatement)
    Ws.Range(dest_cell).CopyFromRecordset MyRS
    Set MyRS = Nothing
    Set MyConn = Nothing

    Application.ScreenUpdating = True
End Sub

Answer 27

 Hi oscarmiguel

What you need is a template XLS sheet. 

Put the column  headers into it in row A.

In row B put a line of dummy data.  If the data  is to be numeric, the put a 0, if it's going to be text but an A.

You then need to UPDATE each column in that first row of the Excel sheet.  So you need to order your data your going to insert so the first record you require is first in the recordset.

For example:

 SET @SQL = 'UPDATE OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
  ''Excel 8.0;Database=' + @FileName + ';HDR=yes'',
  ''SELECT * FROM [Sheet1$]'')
  SET ColumnA = (SELECT TOP (1) AField FROM dbo.tblMyTable ORDER BY IDField) WHERE ID = 0'
Then update the second column.  For example:
 SET @SQL = 'UPDATE OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
  ''Excel 8.0;Database=' + @FileName + ';HDR=yes'',
  ''SELECT * FROM [Sheet1$]'')
  SET ColumnB = (SELECT TOP (1) AnotherField FROM dbo.tblMyTable ORDER BY IDField) WHERE ID = 0'
And so on.  Once you've repeated this for each column in that first row, you need to export  the rest of the recordset, which will be every row, except the first row as you've already done that.

For example:

		''Excel 8.0;Database=' + @FileName + ';HDR=yes'',
		''SELECT * FROM [Sheet1$]'')
		SELECT     TOP (100) PERCENT AllMyFields
		FROM         dbo.tblMyTable INNER JOIN
							  (SELECT     TOP (1) AllMyFields													FROM          dbo.tblMyTable AS tblMyTable_1
								ORDER BY IDField) AS derivedtbl_1 ON dbo.tblMyTable.IDField <> derivedtbl_1.IDField
		ORDER BY dbo.tblMyTable.IDField'
And that's it.

Why do we have a postage size window to work with in these forums.  Sorry this post is a mess but I can't format it and I can't get rid of the code box on the first paragraph.

Hope that helps.



Answer 28

Here is my simple solution. I write the results to file and that will give me a .rpt file. Then I open it in Excel, format, save  it, and I'm done.


Answer 29

Abdshall - That's fine if you want to do it manually.  But if you want it to be automated you need another solution.

Answer 30

check this link

How to import MS Excel data  to sql  Server table using c#.net


Answer 31


with this Excel VB.NET and Excel C# library you can easily export DataSet to Excel .

It is much more faster and easier to use than Excel Interop .

First you will need to export  data from database to DataSet.

Then you manually add headers into appropriate worksheets.

Then you use this sample code to export DataSet to Excel:

// Create new ExcelFile.var ef = new ExcelFile();

// Imports all the tables from DataSet to new file.foreach (DataTable dataTable in dataSet.Tables)
  // Add new worksheet to the file.var ws = ef.Worksheets.Add(dataTable.TableName);

  // Insert the data  from DataTable to the worksheet starting at cell "A1".
  ws.InsertDataTable(dataTable, "B1", true);

// Save the file to XLS format.



Please...Help save what little hair I have left :)

I need to export data from SQL Server Express 2008 to Excel.
The Excel file needs to have headers.
I need numbers to be exported as numbers and not stored as text.  This is my problem.

I've Googled, searched and read for hours, but no solution actually seems to work.  The code below is what I have.  Which works except for it storing everything as text.  I need IDField and Quantity to be stored as numbers.  I have an existing XLS template file I'm exporting the data into.  All suggestions are warmly welcomed :)

Edit:  If I put example data under my header in row 2 of the Excel file, it then exports correctly.  So then I'm left with an extra line.  As a hacky solution, anyone know how to delete a row of Excel data from SQL Server?


SET @FileName =''
SET @SQL =''

SET @FileName ='Excel 8.0;Database='+ @FileNameTwo +''

+ @FileName +''',

''SELECT * FROM [Sheet1$]'') SELECT IDField, Code, Quantity FROM tblMyTable'

Exec (@SQL)


Hi All,

I am exporting gridview data into excel sheet. I have a column  which is a code for which data is like 012,013 etc. But when this data is being exported , in the excel sheet I can see the data as 12,13,14. but actually it should be 012.... Is there any way to specify the format of such columns in my datatable so that they are in the same manner as in the database? Please provide some information on this.

Thanks in advance.


I am exporting a set of records (Part Numbers, Expected Due Date, a message in C(250) format, Confirmed Delivery Dates) and a blank for Supplier Comments to an EXCEL Spreadsheet using the


command.  Due_Date and Dock_Date are VFP Date type fields, all others are character type fields.  When opening the XLS in MS Excel, all fields appear fine, observing that the XLS contains only General type fields, and all fields are 'Locked'.

Originally, none of this was a problem (Earlier this year).  Lately, when importing the same XLS with Dock_Dates filled in, the Dock_Date becomes a blank when


I tried to Unlock the date field, insure that the incoming XLS is type MS Excel 5.0/95 Format, but no luck, the XLS date becomes a blank when importing.

What do I do to import XLS Numeric, Character and Date type fields from a MS Excel spreadsheet with reliability?

(lcExportName) TYPE XL5 FIELDS Part_No, Due_Date, Message, Dock_Date, Splr_Comment


we are working with MOSS 2007, Infopath Forms Services, KPIs and Excel 2003.

There is a view for some data of the Infopath forms which has to be exported to Excel by our users, but some columns are not exported - without any error message.

If I try to export it to Excel 2007 everything is exported as expected, but our users have to use Excel 2003.

Any idea?



I'm running into issues with a report that I have created when exporting to XLS format.  For some reason, there are merged cells showing up when I export the file, forcing end users to delete the extra columns before Excel will allow them to sort.

I am using a matrix in the report, with embedded textboxes for some of my header data.  The last column of the report expands with additional information as needed, but the first 9 columns remain the same.

Has anyone else ran into this problem?



When exporting report output from Visual Studio 2005 to Excel 2003, the format of certain number cells is being defined in Excel as "[$-1010409]General".  A user is trying to export the Excel spreadsheet to Access, and with this format, Access is interpreting the field as Currency instead of General.  Is there a way to force plain old "General" as the format for the field?
we have several processes that export from access to macro eabled excel spreadsheets.  But we are no longaer able to do this in Access 2007.


My program is a C# app that will send some data into excel and then save the worksheet that will crunch that data. For Office 2007 I will be able to save it as a pdf but some of my users might have older versions of office.

If there is an older version there is no complete read only format. Even a jpeg would do. Are there any free/open source libraries that will allow me to this? Export one worksheet into a pdf.



-Gideon MCTS:Windows Development and C# geek.

I managed to export my gridview to excel... or at least to get close to this. I am allowed to save the file but when I open it I receuve this error: "The file you are trying to open, '[filename]', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"  (Yes | No | Help)

How can I fix this?


I was hoping to find an easy answer to this question:

I've designed several reports. In Report Viewer, the fields/cells are set to auto increase in size and height. What my users are having an issue with is when they export the report to Excel or PDF. One of the fields is a DESCRIPTION field that can have an extrmely long character string and can take up anywhere from one to two lines. I looks fine in Report Viewer, but the user has to do a ton of reformatting once the report is exported.

They would like to have the format of the report to be the same as in Report Viewer.

I found one possible solution that requires me to save the Excel docment that has the correct formatting as an SSML(?), and then take that "code" and modify the .rdl of the report. Is this the only way?

Any help would be appreciated!


I'm trying to do some custom formatting when exporting to PDF and to Excel and have run int o a couple of issues. I wasn't sure if I should put them both into one post, so I apologize ahead of time!

One fof trhe first things I would like to do is to create a second PDF export option that exports the report in landscape on legal (8 1/2 x 14) paper. One of the reports is quite wide and it looks terrible when printing on multiple sheets. In addition to printing on legal, I'd like to accomplish the following:

- Set a default font and size when exporting to PDF

- Set a default column width on the columns

- Provide a file name based on certain variables in the report

- Repeat column headers on each page

The other issue is with formatting an Excel export. I've found a couple of articles on how to do this, but I get stuck on a couple of things. The article I found said I should export the report, perform the manual formatting of the workbook, and then save the workbook as "something I can't remember", and then go through and manually adjust the RDL. Once that's done, I need to place that xml(?) in a certain directory?

I know that's not making the most sense, but if that rings a bell with anyone, I would truly appreciate any help!

Lastly, how can I set it up so that the Excel filename is actually the value of certain variables in the report? For eaxample, if one of the variables is @employee and the other is @date, how can I configure the export to create a file name of @employee + @date .xls

Again, any help would be appreciated!! 


I'm exporting data to Excel but where the currency values are formatted I get an  before the currency symbol.

Any ideas how I can get rid?

Here is my code:

        Response.Buffer = True

        Response.ContentType = "application/vnd.ms-excel"
        Dim MyStrWriter As New StringWriter
        Dim MyHtmlWriter As New HtmlTextWriter(MyStrWriter)
        Response.AddHeader("content-disposition", "attachment;filename=Test.xls")


        'Get Invoice
        Dim SqlGetInvoice As String = "SELECT Invoices.InvoiceDate, Invoices.PhaseNumber, Contractor.CompanyName, Invoices.InvoiceAmount, Invoices.VATAmount FROM Invoices INNER JOIN Contractor ON Invoices.ContractorID = Contractor.ContractorID WHERE InvoiceID = @InvoiceID"
        Dim GetInvoiceCmd As New SqlCommand(SqlGetInvoice, con)

        GetInvoiceCmd.Parameters.AddWithValue("@InvoiceID", Request.QueryString("InID"))

        Dim ObjDataReaderGetInvoice As SqlDataReader = GetInvoiceCmd.ExecuteReader(Data.CommandBehavior.CloseConnection)

        While ObjDataReaderGetInvoice.Read()
            Response.Write("<td>" & ObjDataReaderGetInvoice("InvoiceDate") & "</td></tr>")
            If Not IsDBNull(ObjDataReaderGetInvoice("PhaseNumber")) Then
                Response.Write("<tr><td>" & ObjDataReaderGetInvoice("PhaseNumber") & "</td></tr>")
            End If
            Response.Write("<tr><td>" & ObjDataReaderGetInvoice("CompanyName") & "</td></tr>")
            Response.Write("<tr><td>" & FormatCurrency(ObjDataReaderGetInvoice("InvoiceAmount")).ToString() & "</td></tr>")
            Response.Write("<tr><td>" & FormatCurrency(ObjDataReaderGetInvoice("VATAmount")) & "</td></tr>")
            Response.Write("<tr><td>" & FormatCurrency(ObjDataReaderGetInvoice("InvoiceAmount") + ObjDataReaderGetInvoice("VATAmount")) & "</td></tr>")
        End While




i develop one report  and i am using ssrs2008R2

when i exporting into excel sheet its dispaly perferct in my mechine (means printview shows perferct with looose any column ) when my tester exporting into excel sheet its dispaly different meas missing last coloums in report  

Both are using WINDOWS7 Enterprice ,office2007 ,ssrs2008R2  

Please note that the reports gets generated in my machine, say System A, as expected. But as I figured out that in some machines, say System B,  the reports generated are having alignment problems.
I had compared the System A and System B machines in the following parameters :
1) System Resolution - 1024x768 (Same)
2) Microsoft Office Excel Versions - 2007 (Same)
3) Remote Desktop Login into System B from System A -- Reports are properly displayed
    Remote Desktop Login into System A from System B -- Reports are not properly displayed

can Plz help me as possible as earliy

my mail address :praveen6867@gmail.com


I'm facing this issue just in a particular desktop user based on Europe.

We are using a standard view from a Sharepoint list to produce our excel report but on his machine all currency fields are showing like trillions instead of the right format we have on the sharepoint. i.e. 420,000 is showing like 420.000.000.

We tried to edit the regional settings on his machine and even after we setup his desktop just like all other users machine I'm still afcing that issue, is there any other confirguration that I can try to fix that ?

Best regards,







I am exporting a datagrid to Excel sucessfully using this method:



Response.Buffer = True

Response.ContentType = "application/octet-stream"

Response.AddHeader("content-disposition", "attachment;filename=Conference.xls")

'Response.ContentType = "application/vnd.ms-excel"

Response.Charset = ""

Me.EnableViewState = False

Dim oStringWriter AsNew System.IO.StringWriter

Dim oHtmlTextWriter AsNew System.Web.UI.HtmlTextWriter(oStringWriter)







But one of the fileds I am exporting is a Zip Code and unfortunately we are in a part of the country where leading zeros abound.  i.e. 04103

So how can i get the zeros to stick?

Thanks in advance for any help!



Hi,I have i big Doubt, My power users wants  to see pretty reports  on reportviewer with headers, footers,drilldown, etc, but when they want to export the report they want an exce l only with columns headers and data , Is any way to customize the format of the report and put it on the new option on  export menu?, I dont have any clue how i cant do this,( I have see a comercial solution , but a cant find the  way to doit) Any help is welcome


Thanks in advance


I want to Export datagrid to Excel in 3 format (xls/csv/xml) all should show in the save dialogue box.Can anybody provide the sample code.I am using Silverlight 4 Datagrid with PRISM and MVVM Pattern.


I want to Export datagrid to Excel in 3 format (xls/csv/xml) all should show in the save dialogue box.Can anybody provide the sample code.I am using Silverlight 4 Datagrid with PRISM and MVVM Pattern.



I am using WSS 3.0 and Office-2007 ( excel 2007).  When I click view all responses in survey, it shows certain columns . I modified that view in Designer and placed all of my columns in that view . SO now I can see all of my column for survey and summarise the whole Survey for one ID.

WHen I export this survey to excel it exports only few columns, even though My view shows all the comulns. ANy idea why?




I am doing Import/Export. While export i want to disable some column in excel sheet, so during upload or import same primary key I can use, instead of user modify such column.






<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure