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表里单元格的内容前面有'号,回车之后才不见了,
不知道这个问题怎么解决.
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表里单元格的内容前面有'号,回车之后才不见了,
不知道这个问题怎么解决.
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.vbImports System
Imports System.Data
Imports System.Data.OleDb
Imports OWCPublic 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
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 SubEnd Class
将excel中身份证号码列设置为字符格式
用循环语句的时候,因为数据量太大,所以嫌慢
用我的程序直接导的时候,1万多条记录直接导,不到2分钟的
但是他直接生成excel文件,你所说的设置excel的单元格能不能在程序里实现
却说SpreadsheetClass没有定义呢
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();
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啦
{
if(e.Item.ItemType != ListItemType.Item || e.Item.ItemType == ListItemType.Item)
{
e.Item.Cells[1].Attributes.Add("style","vnd.ms-excel.numberformat: @");
}
}