转帖:C#操作Excel

转载 2007年09月21日 17:18:00
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();        
        
        }
 

相关文章推荐

【转】 C# 操作excel 大全

A>Read   有两种方法:   a.OleDbConnection 示例代码如下:  1 String connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data...

C#操作EXCEl的chart折线图 封转的类

public class ExcelPapamMolde     {         ///         /// 绑定X轴开始行         ///         public...

C#操作EXCEl的chart折线图 封转的类

public class ExcelPapamMolde     {         ///         /// 绑定X轴开始行         ///         public...

转 C#操作Excel(2)-- 打开-读取Excel文档

本文的开发环境是Visual Studio 2010 ,C#, Excel 2007。 新建C#工程后打开Solution Explorer,可以看到如下图片: 右键点击Refer...

【转】C# 几十万级数据导出Excel,及Excel各种操作

方法1 /// /// 导出速度最快 /// /// ///...

C#操作Excel表格

  • 2017年11月15日 10:30
  • 66KB
  • 下载

C#操作Excel类示例

  • 2017年09月23日 14:07
  • 29KB
  • 下载

C# 操作Excel大全

//引用Microsoft.Office.Interop.Excel.dll文件  //添加using using Microsoft.Office.Interop.Excel; u...

C#操作Excel

  • 2014年02月20日 17:43
  • 328KB
  • 下载

c#操作EXCEL

  • 2014年08月20日 18:02
  • 14KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:转帖:C#操作Excel
举报原因:
原因补充:

(最多只允许输入30个字)