C导出Excel方法全套整合文档格式.docx
- 文档编号:22065504
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:11
- 大小:18.34KB
C导出Excel方法全套整合文档格式.docx
《C导出Excel方法全套整合文档格式.docx》由会员分享,可在线阅读,更多相关《C导出Excel方法全套整合文档格式.docx(11页珍藏版)》请在冰豆网上搜索。
Response.Write(tw.ToString());
Response.End();
//如果没有下面方法会报错类型“GridView”的控件“GridView1”必须放在具有runat=server的窗体标记内
publicoverridevoidVerifyRenderingInServerForm(Controlcontrol)
还有由于是文件操作所以要引入名称空间IO和Text
后台代码:
usingSystem;
usingSystem.Data;
usingSystem.Configuration;
usingSystem.Web;
usingSystem.Web.Security;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
usingSystem.Web.UI.WebControls.WebParts;
usingSystem.Web.UI.HtmlControls;
usingSystem.Data.SqlClient;
usingSystem.Drawing;
usingSystem.IO;
usingSystem.Text;
publicpartialclassDefault7:
System.Web.UI.Page
{
SqlConnectionsqlcon;
SqlCommandsqlcom;
stringstrCon="
DataSource=(local);
Database=education;
Uid=sa;
Pwd=sa"
protectedvoidPage_Load(objectsender,EventArgse)
if(!
IsPostBack)
bind();
publicvoidbind()
stringsqlstr="
select*from学生信息表名"
sqlcon=newSqlConnection(strCon);
SqlDataAdaptermyda=newSqlDataAdapter(sqlstr,sqlcon);
DataSetmyds=newDataSet();
sqlcon.Open();
myda.Fill(myds,"
学生信息表名"
GridView1.DataSource=myds;
GridView1.DataKeyNames=newstring[]{"
学号"
};
GridView1.DataBind();
sqlcon.Close();
protectedvoidButton1_Click(objectsender,EventArgse)
}
前台:
<
asp:
GridViewID="
GridView1"
runat="
server"
AutoGenerateColumns="
False"
CellPadding="
3"
BackColor="
White"
BorderColor="
#CCCCCC"
BorderStyle="
None"
BorderWidth="
1px"
Font-Size="
12px"
>
<
FooterStyleBackColor="
ForeColor="
#000066"
/>
Columns>
BoundFieldDataField="
HeaderText="
ReadOnly="
True"
姓名"
出生日期"
专业"
学院"
/Columns>
RowStyleForeColor="
SelectedRowStyleBackColor="
#669999"
Font-Bold="
PagerStyleBackColor="
HorizontalAlign="
Left"
CssClass="
ms-formlabelDataGridFixedHeader"
/>
HeaderStyleBackColor="
#006699"
/asp:
GridView>
ButtonID="
Button1"
OnClick="
Button1_Click"
Text="
导出"
读取Excel数据的代码
privateDataSetCreateDataSource()
stringstrCon;
strCon="
Provider=Microsoft.Jet.OLEDB.4.0;
DataSource="
+Server.MapPath("
excel.xls"
)+"
ExtendedProperties=Excel8.0;
"
OleDbConnectionolecon=newOleDbConnection(strCon);
OleDbDataAdaptermyda=newOleDbDataAdapter("
SELECT*FROM[Sheet1$]"
strCon);
myda.Fill(myds);
returnmyds;
GridView1.DataSource=CreateDataSource();
方法2,直接调用
页面增加一个按钮btnExcel,单击btnExcel事件添加如下方法:
主要替换GridView名称和写你的GridView数据绑定方法BindGrid()就行了。
protectedvoidbtnExcel_Click(objectsender,EventArgse)
ToExcel(this.GridView1);
//GridView1为要导出数据的GridView名称
protectedvoidToExcel(GridViewgv)
HttpContext.Current.Response.AppendHeader("
filename=导出EXCEL名字.xls"
HttpContext.Current.Response.Charset="
utf-8"
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.GetEncoding("
HttpContext.Current.Response.ContentType="
//image/JPEG;
text/HTML;
image/GIF;
vnd.ms-excel/msword
gv.AllowPaging=false;
BindGrid();
//GridView1绑定方法,把数据绑定到GridView中去。
gv.Page.EnableViewState=false;
System.IO.StringWritertw=newSystem.IO.StringWriter();
System.Web.UI.HtmlTextWriterhw=newSystem.Web.UI.HtmlTextWriter(tw);
gv.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
gv.AllowPaging=true;
方法3:
intId=0;
stringName="
测试"
stringFileName="
d:
\\abc.xls"
System.Data.DataTabledt=newSystem.Data.DataTable();
longtotalCount=dt.Rows.Count;
longrowRead=0;
floatpercent=0;
OleDbParameter[]parm=newOleDbParameter[dt.Columns.Count];
stringconnString="
+FileName+"
ExtendedProperties=Excel8.0;
OleDbConnectionobjConn=newOleDbConnection(connString);
OleDbCommandobjCmd=newOleDbCommand();
objCmd.Connection=objConn;
objConn.Open();
//建立表结构
objCmd.CommandText=@"
CREATETABLESheet1(序号Integer,名称varchar)"
objCmd.ExecuteNonQuery();
//建立插入动作的Command
objCmd.CommandText="
INSERTINTOSheet1("
+Id+"
"
+Name+"
)"
parm[0]=newOleDbParameter("
@Id"
OleDbType.Integer);
objCmd.Parameters.Add(parm[0]);
parm[1]=newOleDbParameter("
@Company"
OleDbType.VarChar);
objCmd.Parameters.Add(parm[1]);
//遍历DataTable将数据插入新建的Excel文件中
for(inti=0;
i<
dt.Rows.Count;
i++)
{
parm[0].Value=i+1;
for(intj=1;
j<
parm.Length;
j++)
parm[j].Value=dt.Rows[i][j];
rowRead++;
percent=((float)(100*rowRead))/totalCount;
//this.FM.CaptionText.Text="
正在导出数据,已导出["
+percent.ToString("
0.00"
%]..."
if(i==dt.Rows.Count-1)
请稍后......"
System.Windows.Forms.Application.DoEvents();
objConn.Close();
方法4:
此方法速度也是超级快,只不过导出的格式非标准的Excel格式,默认工作表名与文件名相同
FileStreamobjFileStream;
StreamWriterobjStreamWriter;
stringstrLine="
objFileStream=newFileStream(FileName,FileMode.OpenOrCreate,FileAccess.Write);
objStreamWriter=newStreamWriter(objFileStream,System.Text.Encoding.Unicode);
dt.Columns.Count;
strLine=strLine+dt.Columns[i].ColumnName.ToString()+Convert.ToChar(9);
objStreamWriter.WriteLine(strLine);
strLine="
strLine=strLine+(i+1)+Convert.ToChar(9);
strLine=strLine+dt.Rows[i][j].ToString()+Convert.ToChar(9);
objStreamWriter.Close();
objFileStream.Close();
方法5:
此方法调用com组件,速度都慢于以上3个方法
usingExcel;
Excel.ApplicationxlApp=null;
xlApp=newExcel.Application();
Excel.Workbooksworkbooks=xlApp.Workbooks;
Excel.Workbookworkbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheetworksheet=(Excel.Worksheet)workbook.Worksheets[1];
//取得sheet1
Excel.Rangerange;
//写入字段
worksheet.Cells[1,i+1]=dt.Columns[i].ColumnName;
range=(Excel.Range)worksheet.Cells[1,i+1];
for(intr=0;
r<
r++)
worksheet.Cells[r+2,1]=r+1;
//worksheet.Cells[r+2,i+1]=dt.Rows[r][i];
if(i+1!
=dt.Columns.Count)
worksheet.Cells[r+2,i+2]=dt.Rows[r][i+1];
range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]);
workbook.Saved=true;
workbook.SaveCopyAs(FileName);
方法6:
利用剪贴板,有人说此方法很快,但是我用时,这种方法最慢,请高手指点.
stringfilePath=@"
\abc.xls"
objectoMissing=System.Reflection.Missing.Value;
Excel.ApplicationClassxlApp=newExcel.ApplicationClass();
try
xlApp.Visible=false;
xlApp.DisplayAlerts=false;
Excel.WorkbooksoBooks=xlApp.Workbooks;
Excel._WorkbookxlWorkbook=null;
xlWorkbook=oBooks.Open(filePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,
oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);
Excel.WorksheetxlWorksheet;
//添加入一个新的Sheet页。
xlWorksheet=(Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
//以TableName作为新加的Sheet页名。
xlWorksheet.Name="
企业名录"
//取出这个DataTable中的所有值,暂存于stringBuffer中。
stringstringBuffer="
for(intj=0;
j<
j++)
for(intk=0;
k<
k++)
stringBuffer+=dt.Rows[j][k].ToString();
if(k<
dt.Columns.Count-1)
stringBuffer+="
\t"
\n"
//利用系统剪切板
System.Windows.Forms.Clipboard.SetDataObject("
//将stringBuffer放入剪切板。
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
//选中这个sheet页中的第一个单元格
((Excel.Range)xlWorksheet.Cells[1,1]).Select();
//粘贴!
xlWorksheet.Paste(oMissing,oMissing);
//清空系统剪切板。
System.Windows.Forms.Clipboard.SetDataO
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 导出 Excel 方法 全套 整合