在几年的工作中,不时的会有关于EXCEL的操作的要求,我总结了一下以前用过或研究过的导出导入Excel的方法
:
1)把HTML里的Table写入到文件中,然后把文件后缀名改为XLS。
2)把一个页面Response成一个EXCEL文件。
3)从各种Grid列表里直接导出生成EXCEL。
4)通过水晶报表(或其它报表)导出生成EXCEL。
5)通过Excel.Application,Excel.Workbooks ,Excel.Range 来操作EXCEL
6)通过OleDB连接,用SQL语句(如:Update [Sheet1$A2:G2] set F1='XX',...F7='xx')来操作EXCEL。
7)能过OleDB边接,调用批量更新或插入方法。
下面,举列一下各个方法:
一、第一种方法
在数据量不是特别大等的时侯,还是可以的,但他要支持多个Sheet的话,就比较麻烦,导出后
的数据不能用于导入,因为他跟EXCEL原生文件还是会有区别的,他只是利用了EXCEL的支持网页的功能(如果是
一个比较复杂的EXCEL表,你可以借用EXCEL表导出成页面的源代码,把你生成的Table按一定格式来。)。
这个方法的实现只要你懂操作文件就OK,如下:
using (StreamWriter sw = File.CreateText(Application.StartupPath + "/text_temp.xls"))
{
string html = "<table
border='1'><tr><td>title1</td><td>title2</td></tr><tr><td>val1</td><td>val2</td></tr></table>";
sw.Write(html);
sw.Flush();
}
二、这个方法如下:
private void Export(string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.AppendHeader("Content-Disposition", "attachment;filename=" +
HttpUtility.UrlEncode("导出的excel名称.xls", Encoding.UTF8).ToString());
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
Response.Write("EXCEL的主体,可以是一个Table列表或Grid列表");
Response.End();
}
这个页面打开后会自动转换成一个EXCEL文件,你可以保存,也可以打开。
三、第三种方法
其实这个方法跟(1、2)差不多,最终都是把一个Table列表转换成EXCEL格式,只不过过程及调用的元件有
所不同。
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=导出的excel名称.xls");
Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
//GRidView1.AllowPage=false;
//Response.OutPut.Write(sw.ToString());//这个会自动弹出保存对话框。
Response.End();
并且还需要override一下VerifyRenderingInServerForm方法(这一点非常重要,否则在点击按钮后会报错,译者
注),代码如下:
public override void VerifyRenderingInServerForm(Control control)
{
}
四、第四种方法
水晶报表可以导出成很多种格式的文件,水晶报表的功能还是很强大的。
水晶报表可导出的格式如下:
1. PDF (Portable Document Format)
2. DOC (MS Word Document)
3. XLS (MS Excel Spreadsheet)
4. HTML (Hyper Text Markup Language – 3.2 or 4.0 compliant)
5. RTF (Rich Text Format)
rpt是一个水晶报表。
rpt.SetDataSource(dataTable);
//导出到临时文件
CrystalDecisions.Shared.DiskFileDestinationOptionsdisk=newCrystalDecisions.Shared.DiskFileDestinati
onOptions();
rpt.ExportOptions.ExportDestinationType=CrystalDecisions.Shared.ExportDestinationType.DiskFile;
rpt.ExportOptions.ExportFormatType=CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
disk.DiskFileName="c://EXCEL文件名.xls";
rpt.ExportOptions.DestinationOptions=disk;
rpt.Export()
五、第五种方法
这个方法直接就是操作EXCEL,很多时侯的问题就是Process的关闭问题,我没有用过这个法子,所以从网上复一个ASP.net的例子如下:
' <summary>
' 下载Excel方法2(用office-Excel-Com组件对象实现)
' </summary>
' <param name="dt">要转换为Excel文件的表</param>
' <param name="page">页面Page对象,用法: 将me.Page传递过来即可</param>
Public Sub DownLoadExcelToClient2(ByVal dt As DataTable)
'生成Excel操作相关对象
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
'xlSheet.Range("A1:B1").Merge(0) '合并单元格
'xlSheet.Cells(1, 1) = "员工资料信息:"
'赋标题(Excel文件中的标题)
Dim rowIndex As Integer = 2
Dim colIndex As Integer = 0
Dim Col As DataColumn
Dim Row As DataRow
For Each Col In dt.Columns
colIndex = colIndex + 1
xlApp.Cells(rowIndex, colIndex) = Col.ColumnName
Next
'将表dt的所有行写入xlApp对象(Excel文件中的内容)
For Each Row In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each Col In dt.Columns
colIndex = colIndex + 1
xlApp.Cells(rowIndex, colIndex) = Row(Col.ColumnName)
Next
Next
xlSheet.Application.Visible = True '置为可见
'建立一个专门存放Excel文件的目录
If Directory.Exists(Page.Server.MapPath("ExcelFolder")) = False Then
Directory.CreateDirectory(Page.Server.MapPath("ExcelFolder"))
End If
'删除服务端临时文件: download.xls
If File.Exists(Page.Server.MapPath(".") & "/ExcelFolder/download.xls") = True Then
File.Delete(Page.Server.MapPath(".") & "/ExcelFolder/download.xls")
End If
'在服务端保存download.xls
xlSheet.SaveAs(Page.Server.MapPath(".") & "/ExcelFolder/download.xls")
'杀死Excel进程
Dim myproc As System.Diagnostics.Process = New System.Diagnostics.Process
Dim proc As Process
Dim procs() As Process = Process.GetProcessesByName("excel") '得到所有打开的进程
Try
For Each proc In procs
If Not proc.CloseMainWindow() Then
proc.Kill()
End If
Next
Catch
End Try
六、第六种方法
这个方法我在数据量不是特别大时常用,而且可以支持多个工作簿(Sheet),可以灵活的使用模板,预定义表头。但每个工作簿数据量大于5千后,速度会变得越来越慢!例如下:
string olesql = " Update [" + name + "$A" + bexcelcol + ":G" + bexcelcol + "]";
olesql += " set F1='" + UNO + "',F2='" + UName + "',F3='" + NowTime + "',F4='" + Ka1 + "',F5='" + Ka2 + "',F6='" + Ka3 + "',F7='" + Ka4 + "' ";
InsertOledb(olesql, ref oconn,out Msg);
//bexcelcol每写入一个后,要手动的加1,其实就是行数,你可以定义从那一行开始写入数据。
//Oledb执行方法
private bool InsertOledb(string sql,ref OleDbConnection oconn,out string Msg)
{
bool check = false;
OleDbCommand ocomm = new OleDbCommand(sql, oconn);
ocomm.CommandType = CommandType.Text;
try
{
int i = ocomm.ExecuteNonQuery();
check = true;
}
catch(Exception ex)
{
Msg= ex.Message;
}
finally { if (ocomm != null)ocomm.Dispose(); ocomm = null; }
return check;
}
七、第七种方法
现在多数时侯我都使用的这个方法,使用了.NET的批量更新修改功能。同样多个工作簿(Sheet),可以灵活的使用模板,预定义表头。经测试5万多个行数据都可以在5分钟内导出(我机子比较差)。
string sql = "select * from [" + name + "$]" ;
OleDbCommand ocomm = new OleDbCommand(sql, oconn);
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(ocomm);
//调用模版的结构到DataSet
oda.FillSchema(ds, SchemaType.Source, name);
///把数据先写入到DATASET里。
DataRow _row = ds.Tables[name].NewRow();
_row[0]=UNO;
_row[1]=UName;
_row[2]=NowTime;
_row[3]=Ka1;
_row[4]=Ka2;
_row[5]=Ka3;
_row[6]=Ka4;
ds.Tables[name].Rows.Add(_row);
///
//自动生成SQL语句
OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(oda);
//把列名用[]括起来,不然很容易出错。
myCommandBuilder.QuotePrefix = "[";
myCommandBuilder.QuoteSuffix = "]";
//现在要做的是插入的操作,生成插入SQL语句。
oda.InsertCommand = myCommandBuilder.GetInsertCommand(true);
//oda.UpdateCommand = myCommandBuilder.GetUpdateCommand(false);
oda.Update(ds, name);
当然,还有其它别的我没有用过的方法,如果大家的更好的,或我这里没有的方法,欢迎都来交流交流。