sadier

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
一个自己测试通过的操作EXCEL
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Reflection;



namespace WebApplication3
{
    /// <summary>
    /// WebForm1 的摘要说明。
    /// </summary>
    public class WebForm1 : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid DataGrid1;
        protected System.Web.UI.WebControls.Button Button1;
    
        private void Page_Load(object sender, System.EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
        }

        #region Web 窗体设计器生成的代码
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
            //
            InitializeComponent();
            base.OnInit(e);
        }
        
        /// <summary>
        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
        /// 此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {    
            this.Button1.Click += new System.EventHandler(this.Button1_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

        private void Button1_Click(object sender, System.EventArgs e)
        {
            SaveToExcel();
        }

        private string [,] myData= 

         {

              {"车牌号","类型","品 牌","型 号","颜 色","附加费证号","车架号"},

              {"浙KA3676","危险品","货车","铁风SZG9220YY","白","1110708900","022836"},

              {"浙KA4109","危险品","货车","解放CA4110P1K2","白","223132","010898"},

              {"浙KA0001A","危险品","货车","南明LSY9190WS","白","1110205458","0474636"},

              {"浙KA0493","上普货","货车","解放LSY9190WS","白","1110255971","0094327"},

              {"浙KA1045","普货","货车","解放LSY9171WCD","蓝","1110391226","0516003"},

              {"浙KA1313","普货","货车","解放9190WCD","蓝","1110315027","0538701"},

              {"浙KA1322","普货","货车","解放LSY9190WS","蓝","24323332","0538716"},

              {"浙KA1575","普货","货车","解放LSY9181WCD","蓝","1110314149","0113018"},

              {"浙KA1925","普货","货车","解放LSY9220WCD","蓝","1110390626","00268729"},

              {"浙KA2258","普货","货车","解放LSY9220WSP","蓝","111048152","00320"}

         };


        private void SaveToExcel()
        {
            //创建一个Excel文件

            Excel.Application myExcel = new Excel.Application ( ) ;

            myExcel.Application.Workbooks.Add ( true ) ;

            //让Excel文件可见

            myExcel.Visible=true;

            //第一行为报表名称

            myExcel.Cells[1,4]="普通报表";

            //逐行写入数据，

            for(int i=0;i<11;i++)
            {
                for(int j=0;j<7;j++)
                {
                    //以单引号开头，表示该单元格为纯文本
                    myExcel.Cells[2+i,1+j]="'"+myData[i,j];
                }
            }
        }


    }
}


2

        private string SaveToExcel(DataTable dtDetail)
        {

//            string urlPath = HttpContext.Current.Request.ApplicationPath ;
//            string physicPath = HttpContext.Current.Server.MapPath(urlPath); 
//            string strFullName = System.IO.Path.Combine(physicPath,xmlFileName.P_TempXlsFile); 
            //创建一个Excel文件
            
            Excel.Application myExcel = new Excel.Application ( ) ;

//            myExcel.Application.Workbooks.Open(strFullName);
//            myExcel.Cells.Clear();

            myExcel.Application.Workbooks.Add ( true ) ;

//            myExcel.ActiveWorkbook.Open(strFullName);

//
//            myExcel.AutoRecover = true;
//            myExcel.ActiveWorkbook.Save();

            //让Excel文件可见

            //myExcel.Visible=true;

            //第一行为报表名称

            myExcel.Cells[1,4]="材料统计";

            //逐行写入数据，
            myExcel.Cells[2,1]= "材料编号";
            myExcel.Cells[2,2]= "项目分类";
            myExcel.Cells[2,3]= "期初数量";
            myExcel.Cells[2,4]= "入库数量";
            myExcel.Cells[2,5]= "出库数量";
            myExcel.Cells[2,6]= "损益量";
            myExcel.Cells[2,7]= "期末数量";
            myExcel.Cells[2,8]= "订单损耗";
            myExcel.Cells[2,9]= "损益比";
            myExcel.Cells[2,10]= "备注";

            DataView dv = dtDetail.DefaultView;
            dv.Sort = "NewSort,SortOrder";

            for(int i = 0 ;   i < dv.Count  ; i++)
            {
                for(int j = 0; j < dv.Table.Columns.Count; j++)
                {
                    //以单引号开头，表示该单元格为纯文本
                    //myExcel.Cells[2+i,1+j]="'" + dtDetail.Rows[i][j].ToString().Trim();

                    if (dv.Table.Columns[j].ColumnName == "MaterialNo")
                    {
                        if (!dv.Table.Rows[i].IsNull(j))
                            myExcel.Cells[3+i,1]= dv[i][j].ToString().Trim();
                    }
                    else if (dv.Table.Columns[j].ColumnName == "NewSort")
                    {
                        if (!dv.Table.Rows[i].IsNull(j))
                            myExcel.Cells[3+i,2]= dv[i][j].ToString().Trim();
                    }
                    else if (dv.Table.Columns[j].ColumnName == "PeriodStartAmount")
                    {
                        if (!dv.Table.Rows[i].IsNull(j))
                            myExcel.Cells[3+i,3]= dv[i][j].ToString().Trim();
                    }
                    else if (dv.Table.Columns[j].ColumnName == "InComeSumByMat")
                    {
                        if (!dv.Table.Rows[i].IsNull(j))
                            myExcel.Cells[3+i,4]= dv[i][j].ToString().Trim();
                    }
                    else if (dv.Table.Columns[j].ColumnName == "TheoryOut")
                    {
                        if (!dv.Table.Rows[i].IsNull(j))
                            myExcel.Cells[3+i,5]= dv[i][j].ToString().Trim();
                    }
                    else if (dv.Table.Columns[j].ColumnName == "Waste")
                    {
                        if (!dv.Table.Rows[i].IsNull(j))
                            myExcel.Cells[3+i,6]= dv[i][j].ToString().Trim();
                    }
                    else if (dv.Table.Columns[j].ColumnName == "PeriodEndAmount")
                    {
                        if (!dv.Table.Rows[i].IsNull(j))
                            myExcel.Cells[3+i,7]= dv[i][j].ToString().Trim();
                    }
                    else if (dv.Table.Columns[j].ColumnName == "WorkSumByMat")
                    {
                        if (!dv.Table.Rows[i].IsNull(j))
                            myExcel.Cells[3+i,8]= dv[i][j].ToString().Trim();
                    }
                    else if (dv.Table.Columns[j].ColumnName == "WasteRate")
                    {
                        if (!dv.Table.Rows[i].IsNull(j))
                            myExcel.Cells[3+i,9]= dv[i][j].ToString().Trim();
                    }
                    else if (dv.Table.Columns[j].ColumnName == "Memo")
                    {
                        if (!dv.Table.Rows[i].IsNull(j))
                            myExcel.Cells[3+i,10]= dv[i][j].ToString().Trim();
                    }


                }
            }

            string urlPath = HttpContext.Current.Request.ApplicationPath + "/Temp/";
            string physicPath = HttpContext.Current.Server.MapPath(urlPath); 
            string fileName = Guid.NewGuid() + ".xls"; 

            string strFullName = physicPath + fileName;

            //myExcel.ActiveWorkbook.SaveAs(strFullName,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Excel.XlSaveAsAccessMode.xlShared, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing );
            
            myExcel.SaveWorkspace(strFullName);
            myExcel.ActiveWorkbook.Close(true,strFullName,Type.Missing);

            myExcel.AlertBeforeOverwriting = false;
            
            //myExcel.Save(strFullName);

            myExcel.Quit();
            
        
            return strFullName;

        }


        
        private void StartOutPort()
        {    
            //获得从表
            DataSet dsDetail = new DataSet();
            GetDsForDetailTJ(ref dsDetail);    
            if (dsDetail == null || dsDetail.Tables.Count <= 0)
                return;

            DataTable Detaildt1 = null;

            Detaildt1 = GetFullDt(ref dsDetail,"detail");

            string strFullfileName = SaveToExcel(Detaildt1);

//            dsDetail.WriteXml(physicPath+fileName);

            HttpResponse response = HttpContext.Current.Response; 
            response.Clear(); 
            response.WriteFile(strFullfileName); 
            string httpHeader="attachment;filename=newFile.xls"; 
            response.AppendHeader("Content-Disposition", httpHeader); 
            response.Flush(); 

            System.IO.File.Delete(strFullfileName);//删除临时文件 
            response.End(); 
        }

3
        private void SaveToExcel()
        {

            string urlPath = HttpContext.Current.Request.ApplicationPath + "/Temp/";

            string physicPath = HttpContext.Current.Server.MapPath(urlPath);

            string fileName = Guid.NewGuid() + ".Xls"; 

            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName +";Extended Properties=Excel 8.0;";

            OleDbConnection objConn = new OleDbConnection(connString);
            OleDbCommand objCmd = new OleDbCommand();
            objCmd.Connection = objConn;
            objConn.Open();

            //建立表结构

            objCmd.CommandText = @"CREATE TABLE TBL_Customer (CustomerName varchar,CustomerNo varchar)";

            objCmd.ExecuteNonQuery();

            objCmd.CommandText = "INSERT INTO TBL_Customer(CustomerName, CustomerNo) VALUES ('毛消化', 'good')";

            objCmd.ExecuteNonQuery();

            //提供下载

            objCmd.Dispose();
            objConn.Dispose();
            

            HttpResponse response = HttpContext.Current.Response; 
            response.Clear();
            response.WriteFile(physicPath + fileName);
            string httpHeader="attachment;filename=backup.Xls";
            response.AppendHeader("Content-Disposition", httpHeader);
            response.Flush();

            System.IO.File.Delete(physicPath + fileName);//删除临时文件
            response.End();        
        
        }
posted on 2005-08-18 09:28  毛小华  阅读(2208)  评论(0)  编辑  收藏  举报