excel

using System;
using OWC;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Data;
using System.Data.SqlClient;
using Applegold.Modules;


namespace Applegold.UserManage.Modules
{
    /**//// <summary>
    /// ConvertToExcel 的摘要说明。
    /// </summary>
    public class ConvertToExcel
    {
        public ConvertToExcel()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        导出Excel函数ImportExcel#region 导出Excel函数ImportExcel
        /**//*
         * 功能:从数据库表中导出Excel
         * 参数
         * ----------------
         * QueryStr:    要导出Excel表的SQL语句
         * TableName:    主要用来保存导出的Excel的表名
         * page:        宿主服务器请求页面
         * ----------------
         */         
        public static void ImportExcel(string QueryStr,string TableName,Page page)
        {
            
//            string     strConn = GetSqlConn(page);
            string strconn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
            SqlConnection conn = new SqlConnection(strconn);
            DataSet ds= new DataSet(); 
            try
            {
                
                //随便输入SQL语句
                SqlDataAdapter adapter= new SqlDataAdapter(QueryStr,conn); 
                
                adapter.Fill(ds,"Customer"); 
            
                string sFileName = TableName+DateTime.Now.ToFileTime().ToString() + ".xls";
                OWC.SpreadsheetClass xlsheet = new OWC.SpreadsheetClass();
                DataTable dt    = ds.Tables[0];
                int numbercols = dt.Columns.Count;

                //插入列名
                for (int i = 0 ; i < numbercols ; i++)
                {
                    xlsheet.ActiveSheet.Cells[1,i+1] = dt.Columns[i].ColumnName;
                    //xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Bold =true;
                    //xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Color="red";
                    //xlsheet.get_Range(xlsheet.Cells[1,i+1],xlsheet.Cells[1,i+1]).Borders.LineStyle=1;
                
                }
                //xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Bold =true;
                //xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Color="red";

                //插入数据
                for (int k = 0 ; k < dt.Rows.Count ; k++)
                    for (int i=0;i<numbercols;i++) 
                        xlsheet.ActiveSheet.Cells[k+2,i+1] = dt.Rows[k][i].ToString();

                xlsheet.DisplayColHeaders=true;
                xlsheet.DisplayRowHeaders = true;
                xlsheet.AutoFit = true;

                //保存为本地临时文件，用户下载完后删除
                DirectoryInfo di = null; 
                
                try
                {
                    di = new DirectoryInfo("c:\\ExportExcel");
                    if(!di.Exists)
                    {
                        di.Create();
                    }
                }
                catch
                {
                    conn.Close();
                    conn.Dispose();
                    ds.Dispose();
                    return;
                }
                xlsheet.ActiveSheet.Export("c:\\ExportExcel\\" + sFileName,OWC.SheetExportActionEnum.ssExportActionNone);

            
                DownloadFiles("c:\\ExportExcel\\" + sFileName);
                RemoveFiles("c:\\ExportExcel\\" + sFileName);
            } 
            
            catch(Exception e)
            {
                conn.Close();
                conn.Dispose();
                ds.Dispose();
//                Message(e.ToString(),page);
                return;
            }
            finally 
            { 
                conn.Close();
                conn.Dispose();
            } 

        } 

        public static void ImportExcel(string QueryStr,string FileName,DataTable dtt)
        {
            DataTable dt = new DataTable();
            dt = dtt;
            string sFileName = FileName + ".xls";
            string VirtualPath=( (HttpContext.Current.Request.ApplicationPath=="/") ? "" : HttpContext.Current.Request.ApplicationPath) + "/Register/UserManage/datafile";
            string strPath = HttpContext.Current.Server.MapPath(VirtualPath) + "\\xlsheetTemp";
            try
            {        
                
                OWC.SpreadsheetClass xlsheet = new OWC.SpreadsheetClass();
                int numbercols = dt.Columns.Count;

                

                //插入列名
                for (int i = 0 ; i < numbercols ; i++)
                {
                    xlsheet.ActiveSheet.Cells[1,i+1] = dt.Columns[i].ColumnName;
                    //xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Bold =true;
                    //xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Color="red";
                    //xlsheet.get_Range(xlsheet.Cells[1,i+1],xlsheet.Cells[1,i+1]).Borders.LineStyle=1;
                
                }
                //xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Bold =true;
                //xlsheet.get_Range(xlsheet.Cells[1,1],xlsheet.Cells[1,i+1]).Font.Color="red";

                //插入数据
                for (int k = 0 ; k < dt.Rows.Count ; k++)
                    for (int i=0;i<numbercols;i++) 
                        xlsheet.ActiveSheet.Cells[k+2,i+1] = dt.Rows[k][i].ToString();

                xlsheet.DisplayColHeaders=true;
                xlsheet.DisplayRowHeaders = true;
                xlsheet.AutoFit = true;

                    //保存为本地临时文件，用户下载完后删除
                    DirectoryInfo di = null; 
                
                try
                {
                    

                    di = new DirectoryInfo(strPath);
                    if(!di.Exists)
                    {
                        di.Create();
                    }
                }
                catch
                {                    
                    return;
                }
                xlsheet.ActiveSheet.Export(strPath + "\\" + sFileName,OWC.SheetExportActionEnum.ssExportActionNone);

            
                DownloadFiles(strPath + sFileName);
                RemoveFiles(strPath + sFileName);

            } 
            
            catch(Exception e)
            {                
                throw e;            
                return;
            }             

        } 
        
        下载文件DownloadFiles#region 下载文件DownloadFiles
        /**//*
         * 功能:从服务器下载文件至本地
         * 参数
         * ----------------
         * strPath:        要保存至本地的路径
         * page:        宿主服务器请求页面
         * ----------------
         */
        private static void DownloadFiles(string strPath)
        {            
            try
            {
                FileInfo fi=new FileInfo(strPath);
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ClearHeaders();
                HttpContext.Current.Response.Buffer = false;
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=" +HttpUtility.UrlEncode(fi.FullName,System.Text.Encoding.UTF8));
                HttpContext.Current.Response.AppendHeader("Content-Length",fi.Length.ToString());
                HttpContext.Current.Response.WriteFile(fi.FullName);
                HttpContext.Current.Response.Flush();
            }
            catch(Exception e)
            {
                HttpContext.Current.Response.End();
            }
        }
        #endregion 
        
        删除文件RemoveFiles#region 删除文件RemoveFiles
        /**//*
         * 功能:删除文件
         * 参数
         * ----------------
         * strPath:        文件路径
         * ----------------
         */
        private static void RemoveFiles(string strPath)
        { 
            FileInfo fi = new FileInfo(strPath);
            fi.Delete();
            HttpContext.Current.Response.End();
            
        }
        #endregion 
        #endregion
    }
}