关于Excel文件的导出

首先需要一个数据源，如

DataTable dt=new DataTable();
dt.Columns.Add("Colum1",typeof(string));
dt.Columns.Add("Colum2",typeof(string));
dt.Columns.Add("Colum3",typeof(string));

//给表dt赋值(可以赋值多行)
DataRow dr=dt。NewRow();
dr["Colum1"]="数据1";
dr["Colum2"]="数据2";
dr["Colum3"]="数据3";
dt.Rows.Add(dr);

//赋中文标题
dt.Columns["Colum1"].Caption="列名1";
dt.Columns["Colum2"].Caption="列名2";
dt.Columns["Colum3"].Caption="列名3";

//更改整理列顺序
dt.Columns["Colum1"].SetOrdinal(0);
dt.Columns["Colum2"].SetOrdinal(1);
dt.Columns["Colum3"].SetOrdinal(2);

接着是把数据dt转换成Excel文件并且保存起来，以下代码是将数据保存成Excel2007文件类型

其中dt为我们需要传入的数据源（上述）

using System;
using System.Web;
using System.IO;
using System.Linq;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Drawing;
using System.Data;
using Microsoft.Office.Interop.Excel;

Microsoft.Office.Interop.ExcelApplication excelApp = new ApplicationClass();
//定义Excel工作表名字（也叫工作簿）
string strSheetname="sheet1"
//定义excel文件需要保存的路径XXX为文件夹名字
string excelpath = XElement.Load(System.Web.HttpContext.Current.Server.MapPath(System.Web.HttpContext.
Current.Request.ApplicationPath + "/ExcelPath.xml")).Element("EXPORT").Value + "\\" + "XXX";
//如果该路径存在，则将该文件夹内的所有文件删除
           if (System.IO.Directory.Exists(excelpath))
                {
                    string[] files = System.IO.Directory.GetFiles(excelpath);
                    foreach (string file in files)
                    {
                        if (System.IO.Path.GetExtension(file) == ".xls") 
                        { System.IO.File.Delete(file); }
                    }
                }
//其中ExcelPath.xml文件内容如下
<?xml version="1.0" encoding="utf-8" ?>
<!--注意，请不要更改该xml的结构及位置！-->
<PATH>
  <EXPORT>d:\DOC\ExcelFiles</EXPORT>
  <IMPORT>d:\UPLOAD\ExcelFiles</IMPORT>
</PATH>
//定义完整路径，包括文件名和文件格式
strExcelFullName = excelpath + "\\" + "excel1"+ ".xlsx";
                excelApp.DisplayAlerts = true;
                excelApp.SheetsInNewWorkbook = 1;
                Workbook excelBook = excelApp.Workbooks.Add(Type.Missing);
                Worksheet excelSheet = (Worksheet)excelBook.ActiveSheet;
                excelSheet.Name = strSheetname;
                #region 设置表的默认值
                excelSheet.StandardWidth = 25;
                excelSheet.Rows.HorizontalAlignment = XlVAlign.xlVAlignCenter;
                excelSheet.Rows.VerticalAlignment = XlVAlign.xlVAlignCenter;
                #endregion
                #region 写入Excel
                int nRowIndex = 1;
                if (!string.IsNullOrEmpty(strTitle))
                {
                    string a = (Convert.ToChar(64 + dt.Columns.Count)).ToString() + "1";
                    Range rangeTitle = excelSheet.get_Range("A1", a);
                    rangeTitle.Font.Bold = true;
                    rangeTitle.Font.Name = "宋体";
                    rangeTitle.Font.Size = 13;
                    rangeTitle.RowHeight = 25;
                    rangeTitle.Borders.Color = 0;
                    rangeTitle.Borders.LineStyle = 1;
                    rangeTitle.Merge(0);
                    excelApp.Cells[nRowIndex++, 1] = strTitle;
                }
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string excelcolumnname = dt.Columns[i].Caption;
                    //如果数据列表标题为中文,亦可直接赋值列名
              //列表一般为英文名，Excel一般列名为中文名，这里将列表的Caption作为Excel的列名
                    //excelcolumnname = dt.Columns[i].ColumnName;
                    excelApp.Cells[nRowIndex, i + 1] = excelcolumnname;
                    Range rangeColumn = excelApp.Cells[nRowIndex, i + 1] as Range;
                    rangeColumn.WrapText = true;//是否要自动换行
                    rangeColumn.Font.Bold = true;
                    rangeColumn.Font.Name = "宋体";
                    rangeColumn.Font.Size = 12;
                    rangeColumn.RowHeight = 16;
                    rangeColumn.Borders.Color = 0;
                    rangeColumn.Borders.LineStyle = 1;
                }
                nRowIndex++;
                for (int i = 0; i < dtDataSource.Rows.Count; i++)
                {
                    for (int j = 0; j < dtDataSource.Columns.Count; j++)
                    {
                        excelApp.Cells[nRowIndex, j + 1] = dtDataSource.Rows[i][j];
                        Range rangecell = excelApp.Cells[nRowIndex, j + 1] as Range;
                        rangecell.WrapText = true;//是否要自动换行
                        rangecell.Font.Name = "宋体";
                        rangecell.Font.Size = 11;
                        rangecell.RowHeight = 16;
                        rangecell.Borders.Color = 0;
                        rangecell.Borders.LineStyle = 1;
                    }
                    nRowIndex++;
                }
                #endregion
                excelBook.Saved = true;
                excelBook.SaveCopyAs(strExcelFullName);
            }
            catch (Exception ex)
            {
                SysLog sl = new SysLog();
                sl.WriteErrorLog("Excel", strExcelname, ex);
                return "";
            }
            finally
            {
                #region 后续处理
           excelApp.Workbooks.Close(); //关闭ApplicationClass
                excelApp.Quit(); //停止ApplicationClass
                int generation = System.GC.GetGeneration(excelApp); //获取excelApp在垃圾回收中的代数
           System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                GC.Collect(generation); //强制进行一次generation代的垃圾回收
           #endregion
            }
            return strExcelFullName;
        }

下面方法则为导出Excel2003的方法，其中大部分内容与2007相似

不同地方如下

//完整路径名为
string strExcelFullName=excelpath+"\\"+"excel1"+"xls";

接下来文件生成之后就是将文件下载到本地啦，任务已经完成了80%啦

//当返回的完整路径名（路径+文件名+格式）不为空时(returnFullFileName)
string returnExcelName=System.IO.Path.GetFileName(returnFullFileName);
string returnExcelPath=System.IO.Path.GetDirectoryName(returnFullFileName);
string url=string.Format("~/ViewFile.aspx?FileName={0}&FilePath={1}",System.Web.
HttpEncodeUnicode(returnExcelName),System.Web.HttpUtility.UrlEncodeUnicode(returnExcelPath.Replace("&","*")));
Response.Redirect(url);

其中ViewFile.aspx文件为公共下载页面，需要提供文件名和文件路径连个参数

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
public partial class ViewFile:System.Web.UI.Page
{
    protected FileDownload.FileDownload ptsDownLoadFile;
    private void Page_Load(object sender,System.EventArgs e)
    {
        string strFilePath="";
        string strFileName="";
        if(Request.QueryString["FileName"]!=null)
        {
            strFileName=Request.QueryString["FileName"].ToString();
            strFileName=Server.UrlDecode(strFileName.Replace("*","&"));
        }
        if(Request.QueryString["FilePath"]!=null)
        {
            strFilePath=Request.QueryString["FilePath"].ToString();
            strFilePath=strFilePath.Replace("^^","\\");
            strFilePath=Server.UrlDecode(strFilePath.Replace("*","&"));
        }
        try
        {
            FileDownload.FileDownload fDownload = new FileDownload.FileDownload();
            //文件路径
        fDownload.FileDownloadDirectory=strFilePath+"\\"+strFileName;
            string strFullName=strFileName;
            if(FileLen(strFileName)>155)
            {
                //文件名称长度大于155，截取文件名称，将文件拷贝到临时文件夹下下载
            string strTempDownloadFolder=Server.MapPath("../")+"\\Download"+"\\"+"XX";
                  if(!Directory.Exists(strTempDownloadFolder))
                  {
                       Directory.CreateDirectory(strTempDownloadFolder);     
                  }
                  string[] strFileNameArr=strFileName.split('.');
                  string strExtName="."+strFileNameArr[strFileNameArr.Length-1];
                  //获取文件名，不包括后缀，不使用strFileNameArr[0]是为了防止文件名中含有“.”
                  string strPrevName=strFileName.Replace(strExtName,"");
                  int iLen=100-strExtName.Length;
                  strPrevName=SubStr(strPrevName,iLen);

                  File.Copy(strFilePath+"\\"+strFileName,strTempDownloadFolder+"\\"+strPrevName+strExtName,
true);
                  strFilePath=strTempDownloadFolder;
                  strFileName=strPrevName+strExtName;
            }
            //下载，文件不存在或下载出错时会弹出相应提示，并会在downloadlogfile文件夹下记录日志
            fDownload.DownloadFile(Response,strFullName);
            if(Request["RUrl"]!=null)
            {
                Scriptmanager.RegisterClientScriptBlock(this,Page.GetType(),"javascript",
"window.location.href='"+Request["RUrl"].ToString()+"';",true);
            }
        }
        catch(Exception ex)
        {
            SysLog sl=new SysLog();
            sl.WriteErrorLog("File","下载文件失败：",ex)；
         //此处的try不能去掉，否则第2次点击附件下载时不会弹出下载框，是什么原因目前还不清楚
        }
    }
    //计算文件名称长度
    private int FileLen(string strFileName)
    {
        int iLen=0;
        for(int i=0;i<strFileName.Length;i++)
        {
            string str=strFileName.Substring(i,1);
            if(Convert.ToInt32(Convert.ToChar(str))>255 || Convert.ToInt32(Convert.ToChar(str))<0)
            {
                iLen+=9;
            }
            else
            {
                iLen+=1;
            }
        }
    }
     //截取字符串
    private string SubStr(string strVal,int len)    
      {
            int iLen=0;
            string strRtn="";
            for(int i=0;i<strVal.Length;i++)
            {
                string str=strFileName.Substring(i,1);
                if(Convert.ToInt32(Convert.ToChar(str))>255 || Convert.ToInt32(Convert.ToChar(str))<0)
                {
                    iLen+=9;
                }
                else
                {
                    iLen+=1;
                }
                if(iLen>len)
                {
                    break;
                }
                strRtn+=str;
            }
            return strRtn;
      }

}

ViewFile.aspx中所使用的记录日志的方法和文件下载方法定义如下

文件下载方法

using System；
using System.Web;
namespace FileDownload
{
    public class FileDownload
    {
        public FileDownload();
        public string FileDownloadDirectory{get;set;}
        public bool DownloadFile(HttpResponse Response,string sFileName);
        public bool ExistsFile();
    }
}

记录日志方法

using System;
using System.IO;

namespace XXX.Common
{
    public class SysLog
    {
        public SysLog()
        {
            //在此处添加构造函数逻辑
        }

        public void WriteErrorLog(string strUser,string strLog,Exception ex)
        {
            try
            {
                string strPath=System.Web.HttpContext.Current.Server.MapPath(System.Web.
HttpContext.Current.Request.ApplicationPath+@"/logfile");
                if(!Directory.Exists(strPath))
                {
                    Directory.CreateDirectory(strPath);
                }
                string strFile=strPath+@"/"+strUser+"_"+DateTime.Now.ToString("yyyyMMdd")+".txt";
                if(!File.Exists(strFile))
                {
                    FileStream fs=File.Create(strFile);
                    fs.Close();
                }
                StreamWriter sw=new StreamWriter(strFile,true);
                sw.WriteLine("--------------------------------------------");//TXT文件里的分隔线
           sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                sw.WriteLine(strLog);
                sw.WriteLine(strLog);
                if(ex!=null)
                {
                    sw.Write(ex.Message+"\r\n");
                    sw.Write(ex.Source+"\r\n");
                    sw.Write(ex.StackTrace+"\r\n");
                    sw.Write(ex.TargetSite+"\r\n");
                }
                sw.Close();
            }
            catch
            {}
        }
    }
}