Exporting strange characteres to Excel from SQL Server

Joined
Jun 29, 2010
Messages
4
Reaction score
0
Hi guys,

I got 2 days trying to figure out what is happening. I hope, that you can help me.

I got this table:

USE [Bicentenario]
GO
/****** Object: Table [bice].[indices] Script Date: 07/12/2010 14:21:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bice].[indices](
[id_i] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nombre] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fuente] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nota] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[id_t] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

I am getting basiclly the columds "nombre", "fuente" and "nota" from the code behind from a id_i which I get via POST. Once I populate the strings. I build a StringBuilder and HTML is beign inserted on it.

Therefore I add the contentType like text/html and declare the charset like this: Response.ContentEncoding = Encoding.UTF8;

Because I need to use "ñ" character and the acent "á,é,í,ó,ú" because the spanish language.

At the end I just display the Open/Save dialog adding a attachment header as content-disposition, like this.

Response.AddHeader("Content-Disposition", "attachment; filename=Indicador.xls");

Eveything is working fine, in fact get the data from the database on the Excel file exactyl as I want. The problem that I get is, because sometimes on the excel file, I got strange characteres because the "á,é,í,ó,ú" or "ñ" sentences. Note: I have 2 buttons, one for displaying the data on HTML and other for exporting as xls file. The funny stuff is that as HTML always is beign displayed right (it means there should nt be any trouble with the table). The problem it's the excel file.

If I open the xls file, save it like CSV, edit the csv file on notepad and pulse "SaveAs". There, the codeification it's already UTF-8 (the one that I need) and then overwrite the file (leave the UTF-8 codification).

Afterwards I will bea able to open the file with the charset that I need, but of course I don't want the user to do this whole proccess. As I said, it happens only with some rows of the same table and the rest work perfectly.

I cannot figure out why with some rows it's saved right and sometimes it doesnt.

The code-behind cycle is always the same for every row on my table.

what it could be guys? Maybe the table definition?

Thank you in advance

On a few rows whereby I get the wrong data
 
Joined
Jun 29, 2010
Messages
4
Reaction score
0
junk.rail said:
Hi guys,

I got 2 days trying to figure out what is happening. I hope, that you can help me.

I got this table:

USE [Bicentenario]
GO
/****** Object: Table [bice].[indices] Script Date: 07/12/2010 14:21:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bice].[indices](
[id_i] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nombre] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fuente] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nota] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[id_t] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

I am getting basiclly the columds "nombre", "fuente" and "nota" from the code behind from a id_i which I get via POST. Once I populate the strings. I build a StringBuilder and HTML is beign inserted on it.

Therefore I add the contentType like text/html and declare the charset like this: Response.ContentEncoding = Encoding.UTF8;

Because I need to use "ñ" character and the acent "á,é,í,ó,ú" because the spanish language.

At the end I just display the Open/Save dialog adding a attachment header as content-disposition, like this.

Response.AddHeader("Content-Disposition", "attachment; filename=Indicador.xls");

Eveything is working fine, in fact get the data from the database on the Excel file exactyl as I want. The problem that I get is, because sometimes on the excel file, I got strange characteres because the "á,é,í,ó,ú" or "ñ" sentences. Note: I have 2 buttons, one for displaying the data on HTML and other for exporting as xls file. The funny stuff is that as HTML always is beign displayed right (it means there should nt be any trouble with the table). The problem it's the excel file.

If I open the xls file, save it like CSV, edit the csv file on notepad and pulse "SaveAs". There, the codeification it's already UTF-8 (the one that I need) and then overwrite the file (leave the UTF-8 codification).

Afterwards I will bea able to open the file with the charset that I need, but of course I don't want the user to do this whole proccess. As I said, it happens only with some rows of the same table and the rest work perfectly.

I cannot figure out why with some rows it's saved right and sometimes it doesnt.

The code-behind cycle is always the same for every row on my table.

what it could be guys? Maybe the table definition?

Thank you in advance

On a few rows whereby I get the wrong data

I just solved it using a Response.Write(sb)

I realize that the content is corrupted when I modify some tag's like

sb.append("<td><center>" + value + "</center></td>");

to

sb.append("<td align='right'>" + value + "</td>");

What it could be.
 
Last edited:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top