[原创]新鲜出炉滴 Excel 文件读写控件 COM 版本，无需安装Ms Excel
一、在asp.net中滴调用方法：
C#代码：
1、如何创建xls文件，支持单个Sheet最大维数:60000*255；
(1)、生成数据量超过：2000*255 推荐使用在服务器上生成xls文件，然
后给出下载链接；
xlsReadWriteObj.Biff8ReadWrite xlsRwObj;
xlsRwObj = new xlsReadWriteObj.Biff8ReadWrite();
Response.Write("编译日期=" + xlsRwObj.Build + "<BR>");
Response.Write("版本=" + xlsRwObj.Version);
CurrTime=DateTime.Now;
Response.Write("开始时间：" + CurrTime.ToString());
string ErrMsg = xlsRwObj.NewXlsFile("c:\\TempDir\\Test.xls");
//新建XLS文件 不指定文件名称 下载时可以指定 注意：c:\TempDir\目录必须为可写入
if ((ErrMsg == "") || (ErrMsg == null))
{
xlsRwObj.AddSheet("工作薄1");
//增加一个新工作薄 可以指定名称 为空 默认为Sheet(N>1)
for (int i = 1; i <=60000; i++)
{
for (int j = 1; j <= 255; j++)
{
xlsRwObj.WriteCell(i, j, i.ToString()+" ,"+j.ToString() );
}
}
xlsRwObj.AddSheet("工作薄2");
xlsRwObj.WriteCell(1, 1, "1,1，我是第二个工作薄单元格1,1");
xlsRwObj.WriteEnd();//结束写入，在服务器c:\TempDir\目录下保存xls文件
Response.Write("结束时间：" + DateTime.Now.ToString());
Response.Write("花费时间：" +(CurrTime-DateTime.Now).ToString());
Response.Write("<BR>文件保存完毕！");
}
else
{
Response.Write(ErrMsg);//输出错误信息
}
(2)、生成数据量不超过：2000*255 推荐直接下载 服务器上不产生临时文件直接在浏览器输出；
xlsReadWriteObj.Biff8ReadWrite xlsRwObj;
xlsRwObj = new xlsReadWriteObj.Biff8ReadWrite();
string ErrMsg = xlsRwObj.NewXlsFile("c:\\TempDir\\Test.xls");
//新建XLS文件 不指定文件名称 下载时可以指定 注意：c:\TempDir\目录必须为可写入
if ((ErrMsg == "") || (ErrMsg == null))
{
xlsRwObj.AddSheet("工作薄1");
//增加一个新工作薄 可以指定名称 为空 默认为Sheet(N>1)
for (int i = 1; i <=1000; i++)
{
for (int j = 1; j <= 255; j++)
{
xlsRwObj.WriteCell(i, j, i.ToString()+" ,"+j.ToString() );
}
}
xlsRwObj.AddSheet("工作薄2");
xlsRwObj.WriteCell(1, 1, "1,1，我是第二个工作薄单元格1,1");
Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8"; //定义文档类型、字符编码
//attachment 参数表示作为附件下载, 可以改成 online 在线打开
//filename = 指定输出文件的名称, 注意其扩展名和指定文件类型相符, 可为:.doc .xls .txt .htm
Response.AppendHeader("Content-Disposition", "attachment;filename=" + "Test.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
byte[] xlData = null;
xlData = xlsRwObj.Content() as byte[];//取得生成xls文件二进制内容
if (xlData!=null)
Response.BinaryWrite(xlData);//输出文件内容
Response.End();
}
else
{
Response.Write(ErrMsg);//输出错误信息
}
2、如何读取xls文件；
xlsReadWriteObj.Biff8ReadWrite xlsRwObj;
xlsRwObj = new xlsReadWriteObj.Biff8ReadWrite();
string ErrMsg2 = xlsRwObj.OpenXlsFile("c:\\TempDir\\Test.xls");
if ((ErrMsg2 == "") || (ErrMsg2 == null))
{
xlsRwObj.ActiveSheetIndex = 1;
int icol, irow;
Response.Write("工作薄个数=" + xlsRwObj.SheetCount.ToString() + " 使用列=" +
xlsRwObj.UseCol.ToString() + " 使用行=" + xlsRwObj.UseRow.ToString() + "<br>");
for (irow = 1; irow <= xlsRwObj.UseRow; irow++)
{
for (icol = 1; icol <= xlsRwObj.UseCol; icol++)
{
Response.Write(xlsRwObj.ReadCell(irow, icol) + "<br>");//输出单元格值
}
}
xlsRwObj.ReadEnd();//输出结束
}
else
{
Response.Write(ErrMsg2);
}
二、ASP中滴调用方法：
<HTML>
<BODY>
<TITLE> XLS文件写组件v1.5测试 for ASP </TITLE>
<CENTER>
<H3> XLS文件写组件v1.5测试 </H3>
</CENTER>
<HR>
<%
Set xlsRwObj = Server.CreateObject("xlsReadWriteObj.Biff8ReadWrite")
'xlsRwObj.{Insert Method name here}
Response.Write "编译日期="+xlsRwObj.Build+"<BR>"
Response.Write "版本="+xlsRwObj.Version
ErrMsg=xlsRwObj.NewXlsFile("c:\TempDir\Test.xls")
//新建XLS文件
if ErrMsg="" then
xlsRwObj.AddSheet "工作薄1"
'增加一个新工作薄 可以指定名称 为空 默认为Sheet1
xlsRwObj.WriteCell 1, 1, "1,1"
xlsRwObj.WriteCell 1, 2, "1,2"
xlsRwObj.WriteCell 1, 3, "1,3"
xlsRwObj.WriteCell 1, 4, "1,4"
xlsRwObj.WriteCell 2, 1, "2,1"
xlsRwObj.WriteCell 2, 2, "2,2"
xlsRwObj.WriteCell 2, 3, "2,3"
xlsRwObj.WriteCell 2, 4, "2,4"
xlsRwObj.WriteCell 3, 1, "3,1"
xlsRwObj.WriteCell 3, 2, "3,2"
xlsRwObj.WriteCell 3, 3, "3,3"
xlsRwObj.WriteCell 3, 4, "3,4"
xlsRwObj.WriteCell 4, 1, "4,1"
xlsRwObj.WriteCell 4, 2, "4,2"
xlsRwObj.WriteCell 4, 3, "4,3"
xlsRwObj.WriteCell 4, 4, "4,4"
xlsRwObj.WriteEnd
'结束写入保存内容
Response.Write("<BR>文件保存完毕！")
Else
Response.Write ErrMsg
End if
%>
<HR>
</BODY>
</HTMLL>
三、Borland Delphi 调用
1、用Import Type Library...引入DLL，生成xlsReadWriteObj_TLB.pas
2、引用代码：
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, OleServer, xlsReadWriteObj_TLB, Grids, RzStatus,
ExtCtrls;
type
TForm1 = class(TForm)
Biff8ReadWrite1: TBiff8ReadWrite;
Button1: TButton;
Edit1: TEdit;
Label1x: TLabel;
Button2: TButton;
StringGrid1: TStringGrid;
Panel1: TPanel;
ps1: TRzProgressStatus;
Label1: TLabel;
Take_Time: TLabel;
Label2: TLabel;
Label3: TLabel;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
var
ErrMsg: string;
begin
ShowMessage('编译日期=' + Biff8ReadWrite1.Build);
ShowMessage('版本=' + Biff8ReadWrite1.Version);
ErrMsg := Biff8ReadWrite1.NewXlsFile(Edit1.Text);
//新建XLS文件
if ErrMsg <> '' then
begin
Showmessage(ErrMsg);
exit;
end;
Biff8ReadWrite1.AddSheet('工作薄1');
//增加一个新工作薄 可以指定名称 为空 默认为Sheet1
with Biff8ReadWrite1 do
begin
WriteCell(1, 1, '1, 1 ');
WriteCell(1, 2, '1, 2');
WriteCell(1, 3, '1, 3');
WriteCell(1, 4, '1, 4');
WriteCell(2, 1, '2, 1');
WriteCell(2, 2, '2, 2');
WriteCell(2, 3, '2, 3');
WriteCell(2, 4, '2, 4');
WriteCell(3, 1, '3, 1');
WriteCell(3, 2, '3, 2');
WriteCell(3, 3, '3, 3');
WriteCell(3, 4, '3, 4');
WriteCell(4, 1, '4, 1');
WriteCell(4, 2, '4, 2');
WriteCell(4, 3, '4, 3');
WriteCell(4, 4, '4, 4');
WriteEnd;
//结束写入保存内容
end;
ShowMessage('文件保存完毕！');
end;
procedure TForm1.Button2Click(Sender: TObject);
var
i, j, k, l, IRow, ICol: integer;
StartTime, EndTime: integer;
FileName: string;
ErrMsg: string;
begin
ps1.TotalParts := 0;
Take_Time.Caption := '费时：?';
if Edit1.Text <> '' then
FileName := Edit1.Text
else Exit;
StartTime := gettickcount;
ErrMsg := Biff8ReadWrite1.OpenXlsFile(FileName); //'c:\test1.xls'
if ErrMsg <> '' then
begin
Showmessage(ErrMsg);
exit;
end;
Label3.Caption :='工作簿个数：' + IntToStr(Biff8ReadWrite1.SheetCount);
//工作簿个数
Biff8ReadWrite1.ActiveSheetIndex := 1;
//设置当前工作簿索引
IRow := Biff8ReadWrite1.UseRow;
ICol := Biff8ReadWrite1.UseCol;
k := IRow * ICol; //取出总共使用滴单元格数目
ps1.TotalParts := k;
Label1.Caption := '共有Cell数：' + IntToStr(IRow) + '*' + IntToStr(ICol) + '=' + IntToStr(K);
l := 1;
for i := 1 to IRow do
begin
StringGrid1.Cells[0, i] := IntToStr(i);
for j := 1 to ICol do
begin
//Application.ProcessMessages;
ps1.PartsComplete := l;
l := l + 1;
StringGrid1.Cells[j, i] := Biff8ReadWrite1.ReadCell(i, j); //取出单元格文本
end;
end;
//Biff8ReadWrite1.ReadEnd;
//关闭读取
EndTime := gettickcount;
Take_Time.Caption := '费时：' + FloatToStr((EndTime - StartTime) / 1000) + ' 秒';
end;
end.
四、其他语言(待写)
...
五、运行图片
另类欺骗浏览器方式输出excel文件
一、定义文档类型、字符编码
Response.Clear();
Response.Buffer= true;
Response.Charset="utf-8";
//下面这行很重要， attachment 参数表示作为附件下载，您可以改成 online在线打开
//filename=FileFlow.xls 指定输出文件的名称，注意其扩展名和指定文件类型相符，可以为：.doc || .xls || .txt ||.htm
Response.AppendHeader("Content-Disposition","attachment;filename=FileFlow.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("utf-8");
//Response.ContentType指定文件类型 可以为application/ms-excel || application/ms-word || application/ms-txt || application/ms-html || 或其他浏览器可直接支持文档
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
二、定义一个输入流
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
三、将目标数据绑定到输入流输出
this.RenderControl(oHtmlTextWriter);
//this 表示输出本页，你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件
Response.Write(oStringWriter.ToString());
Response.End();
