急,我的DataGrid上的数据导出到excel中后,身份证号显示不正确,等待,揭帖

private void BindReport()

{

DataSet ds=new DataSet();
myConnection.Open();
string strSql ="SELECT * from DsReward1 ";

if (strWhere!="")
strSql=strSql+" where "+strWhere;

SqlDataAdapter MyAdapter = new SqlDataAdapter(strSql,myConnection);
MyAdapter.Fill(ds);

DataView oView=new DataView(ds.Tables[0]);
DataGrid1.DataSource=oView;
DataGrid1.DataBind();
myConnection.Close();

Response.AddHeader("Content-Disposition", "attachment; filename=filename");
Response.Charset = "Default";
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState= false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
this.DataGrid1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();　

}
程序如上,但导出后,身份证号,显示不正确,少了后三位
然后我再数据库里把身份证好这个字段的值加'号后,导出后,
excel表里单元格的内容前面有'号,回车之后才不见了,
不知道这个问题怎么解决.

25 个解决方案

#1

你的身份证字段什么类型？建议用字符类型

#2

在数据库里是字符型的,但倒过去之后,excel自动转化为数值型的好像是

#3

在Web From上输出数据到Excel有两种方法，一个是有数据库直接导出；另外一个方法是由DataGrid直接输出到Excel文件。下面得代码实现了这两个功能。注意：在使用时要引用Microsoft Office Web Components 9.0 COM组件，另外注意设置要保存文件得目录具有匿名可修改的权限。

DataGridToExcel.aspx

<%@ Page Language="vb" EnableViewState="False" AutoEventWireup="false" Codebehind="DataGridToExcel.aspx.vb"
Inherits="aspxWeb.mengxianhui.com.DataGridToExcel"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
  <HEAD>
    <title id="mengxianhui" runat="server"></title>
    <meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
    <meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
    <meta name="vs_defaultClientScript" content="JavaScript">
    <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
  </HEAD>
  <body MS_POSITIONING="GridLayout" style="FONT-SIZE:9pt">
    <form id="Form1" method="post" runat="server">
      <asp:Label id="Label1" runat="server"></asp:Label>
      <asp:TextBox ID="xlfile" Runat="server"></asp:TextBox>
      <br>
      <br>
      <asp:Button ID="ExportDataBase2Excel" Runat="server" />
      <asp:Button ID="ExportDataGrid2Excel" Runat="server" />
      <br>
      <asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False" BorderColor="#CC9966"
       BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4">
        <ItemStyle ForeColor="#330099" BackColor="White"></ItemStyle>
        <HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000"></HeaderStyle>
        <Columns>
          <asp:BoundColumn DataField="Title"></asp:BoundColumn>
          <asp:BoundColumn DataField="Author"></asp:BoundColumn>
        </Columns>
      </asp:DataGrid>
    </form>
  </body>
</HTML>

DataGridToExcel.aspx.vb

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports OWC

Public Class DataGridToExcel
  Inherits System.Web.UI.Page
  Protected WithEvents xlfile As System.Web.UI.WebControls.TextBox
  Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
  Protected WithEvents ExportDataGrid2Excel As System.Web.UI.WebControls.Button
  Protected WithEvents ExportDataBase2Excel As System.Web.UI.WebControls.Button
  Protected WithEvents Label1 As System.Web.UI.WebControls.Label
  Protected mengxianhui As New HtmlGenericControl()

  Private cnn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="_
   + Server.MapPath("Test.mdb"))
  Private sql As OleDbCommand = New OleDbCommand("SELECT TOP 50 Title,Author FROM Document", cnn)

#Region " Web Form Designer Generated Code "

  'This call is required by the Web Form Designer.
  <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

  End Sub

  Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs)_
   Handles MyBase.Init
    'CODEGEN: This method call is required by the Web Form Designer
    'Do not modify it using the code editor.
    InitializeComponent()
  End Sub

#End Region

#4

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
   Handles MyBase.Load
    Label1.Text = "请输入要保存得文件名字："
    ExportDataGrid2Excel.Text = "由DataGrid生成Excel文件"
    ExportDataBase2Excel.Text = "数据库直接生成Excel文件"
    DataGrid1.Columns(0).HeaderStyle.HorizontalAlign = HorizontalAlign.Center
    DataGrid1.Columns(0).HeaderText = "文章名称"
    DataGrid1.Columns(1).HeaderText = "作者"
    DataGrid1.Columns(0).HeaderStyle.Font.Bold = True
    DataGrid1.Style.Add("font-size", "9pt")
    mengxianhui.InnerText = "【孟宪会之精彩世界】－将DataGrid输出到Excel文件"
    Me.BindDataGrid()
  End Sub

  Private Sub BindDataGrid()
    cnn.Open()
    Dim reader As OleDbDataReader = sql.ExecuteReader()
    Me.DataGrid1.DataSource = reader
    Me.DataGrid1.DataBind()
    reader.Close()
    cnn.Close()
  End Sub

  Private Sub WriteDataGrid2Excel()
    Dim xlsheet As New SpreadsheetClass()
    cnn.Open()
    Dim reader As OleDbDataReader = Me.sql.ExecuteReader()
    Dim numbercols As Integer = reader.FieldCount
    Dim row As Integer = 2
    Dim i As Integer = 0
    ' 输出标题
    For i = 0 To numbercols - 1
      xlsheet.ActiveSheet.Cells(1, i + 1) = reader.GetName(i).ToString()
    Next

    ' 输出字段内容
    While (reader.Read())
      For i = 0 To numbercols - 1
        xlsheet.ActiveSheet.Cells(row, i + 1) = reader.GetValue(i).ToString()
      Next
      row = row + 1
    End While
    reader.Close()
    cnn.Close()
    Try
      xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + Me.xlfile.Text,_
       OWC.SheetExportActionEnum.ssExportActionNone)
    Catch e As System.Runtime.InteropServices.COMException
      Response.Write("错误：" + e.Message)
    End Try
  End Sub

  Private Sub WriteDataGrid2Excel2()
    Dim xlsheet As New SpreadsheetClass()
    Dim i As Integer = 0
    Dim j As Integer = 0
    'Response.End()
    ' 输出标题
    Dim oItem As DataGridColumn
    For Each oItem In DataGrid1.Columns
      xlsheet.ActiveSheet.Cells(1, i + 1) = oItem.HeaderText
      'xlsheet.ActiveSheet.Range(xlsheet.ActiveSheet.Cells(1, 1),_
       xlsheet.ActiveSheet.Cells(1, i + 1)).Font.Bold = True
      '设置格式
      xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Bold = True
      xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Color = "red"
      i = i + 1
    Next

    Dim numbercols As Integer = DataGrid1.Items.Item(0).Cells.Count
    ' 输出字段内容
    For j = 0 To DataGrid1.Items.Count - 1
      For i = 0 To numbercols - 1
        xlsheet.Range(xlsheet.Cells(2, 2), xlsheet.Cells(j + 2, i + 1)).Font.Color = "blue"
        'xlsheet.Range("A2:B14").WrapText = True
        xlsheet.Range(xlsheet.Cells(2, 1), xlsheet.Cells(j + 2, i + 1)).AutoFitColumns()
        xlsheet.ActiveSheet.Cells(j + 2, i + 1) = DataGrid1.Items.Item(j).Cells(i).Text.Replace(" ", " ")
      Next
    Next
    Try
      xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + Me.xlfile.Text,_
       OWC.SheetExportActionEnum.ssExportActionNone)
    Catch e As System.Runtime.InteropServices.COMException
      Response.Write("错误：" + e.Message)
    End Try
  End Sub

  Private Sub ExportDataGrid2Excel_Click(ByVal sender As Object,_
   ByVal e As System.EventArgs) Handles ExportDataGrid2Excel.Click
    If (Me.xlfile.Text.Trim() <> "") Then
      Me.WriteDataGrid2Excel2()
    End If
  End Sub

  Private Sub ExportDataBase2Excel_Click(ByVal sender As Object, _
   ByVal e As System.EventArgs) Handles ExportDataBase2Excel.Click
    If (Me.xlfile.Text.Trim() <> "") Then
      Me.WriteDataGrid2Excel()
    End If
  End Sub

End Class

#5

excel认为身份证号码是数字
将excel中身份证号码列设置为字符格式

#6

同意楼上!

#7

有没有C#的程序借鉴一下

#8

回,我思,谁在:
用循环语句的时候,因为数据量太大,所以嫌慢
用我的程序直接导的时候,1万多条记录直接导,不到2分钟的
但是他直接生成excel文件,你所说的设置excel的单元格能不能在程序里实现

#9

http://dotnet.aspx.cc/ShowDetail.aspx?id=BF0A54F9-C7C7-4200-BD9A-802AC1F5DE50

#10

拜托大家能不能看看我真正的意思,不明白我的意思的话我可以解释

#11

请教,请问SpreadsheetClass是怎样来的,我在程序中输入Dim xlsheet As New SpreadsheetClass()
却说SpreadsheetClass没有定义呢

#12

是否前面的0不见了？比如002584124，导出到excel里面变成了：2584124,解决方法如下：

excelsht.Cells.NumberFormatLocal:='@'; //////////单位格的格式为文本

#13

学习

#14

直接将DataGrid导到excel:

//将HTTP头添加到输出流
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=excel.xls");

//指定生成文件的类型
Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
this.Datagrid2.Page.EnableViewState = true;

System.IO.StringWriter tw= new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter hw= new System.Web.UI.HtmlTextWriter(tw);
this.Datagrid1.Visible = true;

//将服务器控件中的内容输出到System.Web.UI.HtmlTextWriter对象中
this.Datagrid1.RenderControl(hw);

HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();

#15

好象前面或后面加个空格就行了，　我以前也遇到过

#16

身份证字段在EXCEL中设置为字符（文本）

#17

help up

#18

这个问题我遇到过，身份证建议使用字符型，然后在DataGrid的ItemDataBound中加入如下代码：
private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemType != ListItemType.Item || e.Item.ItemType == ListItemType.Item)
{
e.Item.Cells[1].Attributes.Add("style","vnd.ms-excel.numberformat: @");
}
}
就OK啦

#19

#20

#21

直覺上lovely_swallow(蓝色夏威夷) 的方法應該可以解決樓主的問題。

登記一下﹐我是用xml加xsl來轉換成excel的﹐原模原樣

#22

心情不好,没分也顶!

#23

数据类型问题，数字型溢出显示就不正常，很字符型就好啦

#24

private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemType != ListItemType.Item || e.Item.ItemType == ListItemType.Item)
{
e.Item.Cells[1].Attributes.Add("style","vnd.ms-excel.numberformat: @");
}
}

#25

在往数据库里录入身份证号的时候可以加一个'号，身份证号的字段长度设成12

#1