导出规则的EXCEL文件并下载

[wang520d]

我用EXCEL组件导出了标准的EXCEL文件 请问怎么在导出完这个文件的同事弹出下载框让用户下载到本地？

//// <summary>
        /// 导出 Excel 文件
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的文件名</param>
        public static void ExportExcel(DataSet ds, string strExcelFileName)
        {
            object objOpt = Missing.Value;
            Application excel = new Application();
            excel.Visible = true;
            _Workbook wkb = excel.Workbooks.Add(objOpt);
            _Worksheet wks = (_Worksheet)wkb.ActiveSheet;

            wks.Visible = XlSheetVisibility.xlSheetVisible;

            int rowIndex = 1;
            int colIndex = 0;

            System.Data.DataTable table = ds.Tables[0];
            foreach (DataColumn col in table.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }

            foreach (DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                }
            }
            //excel.Sheets[0] = "sss";
            wkb.SaveAs(strExcelFileName, objOpt, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            wkb.Close(false, objOpt, objOpt);
            excel.Quit();
        }
        #endregion

一般的导出后都带有格式 在用户修改完后无法进行导入操作 所以我希望导出的文件是规则的EXCEL文件不带格式

[c11_11_11]

我这个最简单 直接Response.Write(sb.ToString());

[wang520d]

问题还是没解决。。希望能提供一个比较完美的导出规则的EXCEL并下载的程序。。

[suyiming]

标准的 高效的 导出excel方法： 用存储过程导出 具体如下：

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Maticsoft.DBUtility;
using System.Data.SqlClient;
public partial class admin_BugSystem : System.Web.UI.Page
{

    public static string connectionString1 = PubConstant.ConnectionString1;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

        }
    }
    //导出Excel
    protected void Button3_Click(object sender, EventArgs e)
    {
        #region 方法一
        string Types = "";
        if (rall.Checked == true)
        {
            Types = "1=1";
        }
        else
        {
            Types = "1=1";
        }
        DateTime Time11 = Convert.ToDateTime(Time1.Text.Trim());
        DateTime Time22 = Convert.ToDateTime(Time2.Text.Trim());
        if (DateTime.Compare(Time22, Time11) > 0)
        {
            Types = Types + "and BUGStartDateTime BETWEEN '" + Time11 + "' and '" + Time22 + "'";
            DataSet ds = new DataSet();
            //查询语句,如果查询语句中使用了order by ,请加上top 100 percent
            string SqlStr = "select UserName,GameRole,ServiceName,BUGQuestion,BUGDescription,BUGAnswer from bugsystem where " + Types;
            SqlParameter[] paramers =
                {
                    new SqlParameter("@sqlses",SqlDbType.NVarChar,1000)
                };
            paramers[0].Value = SqlStr;
            ds = DbHelperSQL.RunProcedure("UP_Outloadbugtoexcel", paramers, "Dss");
            if (ds.Tables[0].Rows.Count == 0)
            {
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('你选择的时间段没记录！请重新选择时间！');</script>");
            }
            else if (ds.Tables[0].Rows.Count <= 65530 && ds.Tables[0].Rows.Count >= 0)
            {
                //文件存放目录 盘的目录
                string Path = ConfigurationManager.AppSettings["BUGOPLOAD"].ToString();
                //生成当天的excel文件名
                string ExcenlNamae = DateTime.Now.ToString("yyyyMMddhhmmss");
                //文件名称
                string Fname = ExcenlNamae;
                string SheetName = "BUG";
                string FileName = Path + ExcenlNamae;
                try
                {
                    OutputExcel(SqlStr, Path, Fname, SheetName);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                Response.Redirect("../Bugexcel/" + ExcenlNamae + ".xls");
                Response.End();
            }
            else
            {
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('要导出来BUG的条数大于Excel的最大容量，请选适中的时间段再试试！');</script>");
            }
            ds.Dispose();
            GC.Collect();
        }
        else
        {
            Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('结束时间必须大于开始时间');</script>");
        }
        #endregion

    }
    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <param name="tableName">DataSet结果中的表名</param>
    /// <returns>DataSet</returns>
    public void RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
    {
        string connectionString = connectionString1;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            DataSet dataSet = new DataSet();
            connection.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
            sqlDA.Fill(dataSet, tableName);
            connection.Close();
        }
        GC.Collect();
    }

    /// <summary>
    /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <returns>SqlCommand</returns>
    private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
    {
        SqlCommand command = new SqlCommand(storedProcName, connection);
        command.CommandType = CommandType.StoredProcedure;
        foreach (SqlParameter parameter in parameters)
        {
            if (parameter != null)
            {
                // 检查未分配值的输出参数,将其分配以DBNull.Value.
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                command.Parameters.Add(parameter);
            }
        }

        return command;
    }

    //导出excel方法存储过程方法
    public void OutputExcel(string SqlStr, string Path, string Fname, string SheetName)
    {
        SqlParameter[] paramers =
            {
                 new SqlParameter("@SqlStr",SqlDbType.VarChar,8000),
                 new SqlParameter("@Path",SqlDbType.NVarChar,1000),
                 new SqlParameter("@Fname",SqlDbType.NVarChar,250),
                 new SqlParameter("@SheetName",SqlDbType.VarChar,250)
            };
        paramers[0].Value = SqlStr;
        paramers[1].Value = Path;
        paramers[2].Value = Fname;
        paramers[3].Value = SheetName;
        RunProcedure("p_exporttb", paramers, "BUG");
    }

}

[suyiming]

用到的存储过程

create proc p_exporttb
@sqlstr varchar(8000),   --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),   --文件存放目录
@fname nvarchar(250),   --文件名
@sheetname varchar(250)=''  --要创建的工作表名,默认为文件名
as 
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')

--检查文件是否已经存在
if right(@path,1)<>'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql

--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
 set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
       +';CREATE_DB="'+@sql+'";DBQ='+@sql
else
 set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
    +';DATABASE='+@sql+'"'

--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr

--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)

select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
 ,@sql=@sql+',['+a.name+'] '
  +case 
   when b.name like '%char' 
   then case when a.length>255 then 'memo'
    else 'text('+cast(a.length as varchar)+')' end
   when b.name like '%int' or b.name='bit' then 'int'
   when b.name like '%datetime' then 'datetime'
   when b.name like '%money' then 'money'
   when b.name like '%text' then 'memo'
   else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','sql_variant','varbinary','binary','timestamp')
 and a.id=(select id from tempdb..sysobjects where name=@tbname)

if @@rowcount=0 return

select @sql='create table ['+@sheetname
 +']('+substring(@sql,2,8000)+')'
 ,@fdlist=substring(@fdlist,2,8000)

exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
   ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')

set @sql='drop table ['+@tbname+']'

exec(@sql)
return


lberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
 select @sql,@constr,@fdlist






GO

注意 请以sa身份登陆 才能进行操作

[aiyumen]

你导出来了,其实就是文件下载的问题

private void Download(string FileName) 
    { 

        System.IO.FileStream r = new System.IO.FileStream(FileName, System.IO.FileMode.Open); 
        //设置基本信息  
        System.Web.HttpContext.Current.Response.Buffer = false; 
        System.Web.HttpContext.Current.Response.AddHeader("Connection", "Keep-Alive"); 
        System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream"; 
        System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.IO.Path.GetFileName(FileName)); 
        System.Web.HttpContext.Current.Response.AddHeader("Content-Length", r.Length.ToString()); 
        while (true) 
        { 
            //开辟缓冲区空间  
            byte[] buffer = new byte[1024]; 
            //读取文件的数据  
            int leng = r.Read(buffer, 0, 1024); 
            if (leng == 0)//到文件尾，结束  
                break; 
            if (leng == 1024)//读出的文件数据长度等于缓冲区长度，直接将缓冲区数据写入  
                System.Web.HttpContext.Current.Response.BinaryWrite(buffer); 
            else 
            { 
                //读出文件数据比缓冲区小，重新定义缓冲区大小，只用于读取文件的最后一个数据块  
                byte[] b = new byte[leng]; 
                for (int i = 0; i < leng; i++) 
                    b[i] = buffer[i]; 
                System.Web.HttpContext.Current.Response.BinaryWrite(b); 
            } 
        } 
        r.Close();//关闭下载文件  
        System.IO.File.Delete(FileName); 
        System.Web.HttpContext.Current.Response.End();//结束文件下载  
    } 

[shoushii]

 /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="dgExport">要导出的GridView</param>
    /// <param name="dtData">导出的数据源</param>
    public static void Export(GridView dgExport, DataTable dtData)
    {
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
        // IO用于导出并返回excel文件 
        System.IO.StringWriter strWriter = null;
        System.Web.UI.HtmlTextWriter htmlWriter = null;

        if (dtData != null)
        {
            // 设置编码和附件格式 
            curContext.Response.Clear();
            curContext.Response.ClearContent();
            curContext.Response.Buffer = true;
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");//utf-7可以解决一定问题
            curContext.Response.Charset = "GB2312";
            curContext.Response.AppendHeader("content-disposition", "filename=\"" + System.Web.HttpUtility.UrlEncode(dtData.TableName, System.Text.Encoding.UTF8) + ".xls\"");


            // 导出excel文件 
            strWriter = new System.IO.StringWriter();
            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

            HtmlForm frm = new HtmlForm();
            frm.Attributes["runat"] = "server";
            frm.Controls.Add(dgExport);


            dgExport.DataSource = dtData.DefaultView;
            dgExport.DataBind();
            // 返回客户端 
            dgExport.RenderControl(htmlWriter);
            curContext.Response.Write(strWriter.ToString());
            curContext.Response.End();
        }
    }

[zjx2388]

很想帮下楼主，可惜我们用的是不同的开发语言
如果楼主要java实现导出Excel，并提供客户端的下载可以找本人索要
联系方式QQ:154166621,请加的时候注明来自csdn,需要代码

[luckyboy101]

生成excel分服务器和客户端编程，不知道你想哪种

[wangdetian168]

好东西

[wangdetian168]

http://www.cnblogs.com/wangdetian168/archive/2008/06/20/1226973.html
把文件下载下来就行了
Missing.Value;
是什么呢

[evjen]

小弟学习学习啊

[jinlong5200]

protected void ButSave_Click(object sender, EventArgs e) 
    { 
        sendTableName = "Phone, Illness, See_Illness,Leechdom,Notes"; 
        sendStrSQL = this.Label8.Text; 
        dataBase(); 
        DataView dv = new DataView(ds.Tables[0]); 
        OutputExcel(dv,"我的报表"); 
    } 
    public void OutputExcel(DataView dv, string str) 
    { 
        // 
        // TODO: 在此处添加构造函数逻辑 
        // 
        //dv为要输出到Excel的数据，str为标题名称 
        GC.Collect(); 
        //Application excel;// = new Application(); 
        int rowIndex = 2; 
        int colIndex = 0; 
        int SUM = 0; 
        int number = 0; 
        _Workbook xBk; 
        _Worksheet xSt; 

        Excel.ApplicationClass excel = new Excel.ApplicationClass(); 
        xBk = excel.Workbooks.Add(true); 

        xSt = (_Worksheet)xBk.ActiveSheet; 

        // 
        //取得标题 
        // 
        foreach (DataColumn col in dv.Table.Columns) 
        { 
            colIndex++; 
            excel.Cells[2, colIndex] = col.ColumnName; 
            xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[2, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐 
        } 

        // 
        //取得表格中的数据 
        // 
        foreach (DataRowView row in dv) 
        { 
            rowIndex++; 
            colIndex = 0; 
            foreach (DataColumn col in dv.Table.Columns) 
            { 
                colIndex++; 
                //if (col.DataType == System.Type.GetType("System.DateTime")) 
                //{ 
                //    excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); 
                //    xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐 
                //} 
                //else 
                    if (col.DataType == System.Type.GetType("System.String")) 
                    { 
                      
                        excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString(); 
                          
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐 
                    } 
                    else 
                    { 
                        excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); 
                        if (col.ColumnName.ToString() == "用药数量") 
                        { 
                            number = colIndex; 
                        } 
                    } 
            } 
        } 
        // 
        //加载一个合计行 
        // 
        int rowSum = rowIndex + 1; 
        int colSum = 1; 
        if (number != 0) 
        { 
            excel.Cells[rowSum, number] = this.lblnumber.Text; 
        } 
        excel.Cells[rowSum, 1] = "合计"; 
        xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter; 
      
        //if (row[col.ColumnName].ToString() = "用药数量") 
        //{ 

        //} 
        // 
        //设置选中的部分的颜色 
        // 
        xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select(); 
        xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种 
        // 
        //取得整个报表的标题 
        // 
        excel.Cells[1, 1] = str; 
        // 
        //设置整个报表的标题格式 
        // 
        //xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Bold = true; 
        xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 16; 
        // 
        //设置报表表格为最适应宽度 
        // 
        xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Select(); 
        xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Columns.AutoFit(); 
        // 
        //设置整个报表的标题为跨列居中 
        // 
        xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Select(); 
        xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; 
        // 
        //绘制边框 
        // 
        xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1; 
        xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, 1]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗 
        xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗 
        xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗 
        xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗 
        // 
        //显示效果 
        // 
        excel.Visible = true; 

        xBk.SaveCopyAs(Server.MapPath(".") + "\\" + "2008.xls"); 

        ds = null; 
        xBk.Close(false, null, null); 

        excel.Quit(); 
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); 
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); 
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); 
        xBk = null; 
        excel = null; 
        xSt = null; 
        GC.Collect(); 
        string path = Server.MapPath("2008.xls"); 

        System.IO.FileInfo file = new System.IO.FileInfo(path); 
        Response.Clear(); 
        Response.Charset = "GB2312"; 
        Response.ContentEncoding = System.Text.Encoding.UTF8; 
        // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); 
        // 添加头信息，指定文件大小，让浏览器能够显示下载进度 
        Response.AddHeader("Content-Length", file.Length.ToString()); 

        // 指定返回的是一个不能被客户端读取的流，必须被下载 
        Response.ContentType = "application/ms-excel"; 
      
        // 把文件流发送到客户端 
        Response.WriteFile(file.FullName); 
        // 停止页面的执行 
        
        Response.End(); 
      
    }

[floadcloud]

mark

[pdsnet]

[Quote=引用 16 楼 floadcloud 的回复:]
mark
[/Quote]
同上

[ilovecookie]

不错，
谢了

[jimodushi]

学习！

[zi_wu_xian]

http://www.kehansoft.com/soaexcel/login.asp
看看这个效果怎么样，先在客户端生成excel文件，然后客户另存就可以了

[qjhli]

asp.net下载大文件代码
public bool Down(string filepath, string code)
{
bool succ = false;

System.IO.Stream iStream = null;

// Buffer to read 10K bytes in chunk:
byte[] buffer = new Byte[10000];

// Length of the file:
int length;

// Total bytes to read:
long dataToRead;

// Identify the file to download including its path.
// string filepath = "DownloadFileName";

// Identify the file name.
string filename = System.IO.Path.GetFileName(filepath);

try
{
// Open the file.
iStream = new System.IO.FileStream(filepath, System.IO.FileMode.Open,
System.IO.FileAccess.Read, System.IO.FileShare.Read);


// Total bytes to read:
dataToRead = iStream.Length;

Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);

// Read the bytes.
while (dataToRead > 0)
{
// Verify that the client is connected.
if (Response.IsClientConnected)
{
// Read the data in buffer.
length = iStream.Read(buffer, 0, 10000);

// Write the data to the current output stream.
Response.OutputStream.Write(buffer, 0, length);

// Flush the data to the HTML output.
Response.Flush();

buffer = new Byte[10000];
dataToRead = dataToRead - length;
}
else
{
//prevent infinite loop if user disconnects
dataToRead = -1;
}
}
}
catch (Exception ex)
{
// Trap the error, if any.
Response.Write("Error : " + ex.Message);
}
finally
{
if (iStream != null)
{
//Close the file.
iStream.Close();
}
}


return succ;
}

[zcl24]

Excel一个cell一个Cell写太慢，可以用二维数组写入 很快。

    public class ExcelHelper
    {
        #region 成员变量
        private string templetFile = null;
        private string outputFile = null;
        private object missing = Missing.Value;
        private DateTime beforeTime;			//Excel启动之前时间
        private DateTime afterTime;				//Excel启动之后时间
        Excel.Application app;
        Excel.Workbook workBook;
        Excel.Worksheet workSheet;
        Excel.Range range;
        Excel.Range range1;
        Excel.Range range2;
        Excel.TextBox textBox;
        private int sheetCount = 1;			//WorkSheet数量
        private string sheetPrefixName = "页";


        public string SheetPrefixName
        {
            set { this.sheetPrefixName = value; }
        }

        public void SetCalculation(bool CalculationAutomatic)
        {
            if (CalculationAutomatic)
            {
                app.Calculation = Excel.XlCalculation.xlCalculationAutomatic; 
                app.CalculateBeforeSave = true;
            }
            else
            {
                app.CalculateBeforeSave = false;
                app.Calculation = Excel.XlCalculation.xlCalculationManual;
            }
        }

        public int WorkSheetCount
        {
            get { return workBook.Sheets.Count; }
        }

        public string TempletFilePath
        {
            set { this.templetFile = value; }
        }

        public string OutputFilePath
        {
            set { this.outputFile = value; }
        }


        public ExcelHelper(string fileName)
        {
            if (!File.Exists(fileName))
                throw new Exception("指定路径的Excel文件不存在！");

            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            app = new Excel.ApplicationClass();
            app.Visible = false;
            afterTime = DateTime.Now;

            //打开一个WorkBook
            workBook = app.Workbooks.Open(fileName,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            //得到WorkSheet对象
            workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

        }

        public ExcelHelper()
        {
            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            app = new Excel.ApplicationClass();
            app.Visible = false;
            afterTime = DateTime.Now;

            //新建一个WorkBook
            workBook = app.Workbooks.Add(Type.Missing);

            //得到WorkSheet对象
            workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

        }


        public void DataTableToExcel(DataTable dt, int rows,int top,int left)
        {
            int rowCount = dt.Rows.Count;		//DataTable行数
            int colCount = dt.Columns.Count;	//DataTable列数
            sheetCount = this.GetSheetCount(rowCount, rows);	//WorkSheet个数
            //			StringBuilder sb;

            //复制sheetCount-1个WorkSheet对象
            for (int i = 1; i < sheetCount; i++)
            {
                workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                workSheet.Copy(missing, workBook.Worksheets[i]);
            }

            for (int i = 1; i <= sheetCount; i++)
            {
                int startRow = (i - 1) * rows;		//记录起始行索引
                int endRow = i * rows;			//记录结束行索引

                //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
                if (i == sheetCount)
                    endRow = rowCount;

                //获取要写入数据的WorkSheet对象，并重命名
                workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                workSheet.Name = sheetPrefixName + "-" + i.ToString();

			}

                //利用二维数组批量写入
                int row = endRow - startRow;
                object[,] ss = new object[row, colCount];

                for (int j = 0; j < row; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        ss[j, k] = dt.Rows[startRow + j][k];
                    }
                }

                range = (Excel.Range)workSheet.Cells[top, left];
                range = range.get_Resize(row, colCount);
                range.Value = ss;

                #region 利用Windwo粘贴板批量拷贝数据（在Web下面行不通）
                /*sb = new StringBuilder();

				for(int j=0;j<endRow-startRow;j++)
				{
					for(int k=0;k<colCount;k++)
					{
						sb.Append( dt.Rows[startRow + j][k].ToString() );
						sb.Append("\t");
					}

					sb.Append("\n");
				}

				System.Windows.Forms.Clipboard.SetDataObject(sb.ToString());

				range = (Excel.Range)workSheet.Cells[top,left];
				workSheet.Paste(range,false);*/
                #endregion

            }
        }

        public void DataTableToExcel(DataTable dt, int top,int left)
        {
            int rowCount = dt.Rows.Count;		//DataTable行数
            int colCount = dt.Columns.Count;	//DataTable列数

            //利用二维数组批量写入
            object[,] arr = new object[rowCount, colCount];

            for (int j = 0; j < rowCount; j++)
            {
                for (int k = 0; k < colCount; k++)
                {
                    if (dt.Columns[k].DataType == typeof(string))
                        arr[j, k] = "'" + dt.Rows[j][k].ToString();
                    else
                        arr[j, k] = dt.Rows[j][k];
                }
            }

            range = (Excel.Range)workSheet.Cells[top, left];
            if (rowCount > 0)
            {
                range = range.get_Resize(rowCount, colCount);
                range.Value = arr;
            }
        }
       public void DataTableToExcel(DataTable dt, int rows,int top,int left,int mergeColumnIndex)
        {
            int rowCount = dt.Rows.Count;		//源DataTable行数
            int colCount = dt.Columns.Count;	//源DataTable列数
            sheetCount = this.GetSheetCount(rowCount, rows);	//WorkSheet个数
            //			StringBuilder sb;

            //复制sheetCount-1个WorkSheet对象
            for (int i = 1; i < sheetCount; i++)
            {
                workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                workSheet.Copy(missing, workBook.Worksheets[i]);
            }

            for (int i = 1; i <= sheetCount; i++)
            {
                int startRow = (i - 1) * rows;		//记录起始行索引
                int endRow = i * rows;			//记录结束行索引

                //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
                if (i == sheetCount)
                    endRow = rowCount;

                //获取要写入数据的WorkSheet对象，并重命名
                workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                workSheet.Name = sheetPrefixName + "-" + i.ToString();


                //利用二维数组批量写入
                int row = endRow - startRow;
                object[,] ss = new object[row, colCount];

                for (int j = 0; j < row; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        if (dt.Columns[k].DataType == typeof(string))
                            ss[j, k] = "'" + dt.Rows[j][k].ToString();
                        else
                            ss[j, k] = dt.Rows[j][k];
                    }
                }

                range = (Excel.Range)workSheet.Cells[top, left];
                range = range.get_Resize(row, colCount);
                range.Value = ss;

                //合并相同行
                this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);

            }
        }

[yangganggood]

15楼，不错~