转帖: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(导入导出) (转帖)

C#操作Excel(导入导出) 转载地址:http://www.pconline.com.cn/pcedu/empolder/net/cs/0507/674430.html  有很多朋友说需要C#导出...
  • Firestone2003
  • Firestone2003
  • 2006年06月09日 02:14
  • 4012

操作EXCEL代码(c#完全版)

using System;     using System.Collections;     using Excel=Microsoft.Office.Interop.Excel;    ...
  • my98800
  • my98800
  • 2016年11月08日 09:26
  • 1143

C#操作Excel类,功能比较全

原文出处: http://www.cnblogs.com/wangchuang/p/5546345.html /// 常用工具类——Excel操作类 ///  --------...
  • wuyuander
  • wuyuander
  • 2017年06月06日 11:13
  • 512

c# 操作excel生成pdf

//将excel文档转换成PDF格式           private bool Convert2PDF(string sourcePath, string targetPath, XlFixedF...
  • wk2133
  • wk2133
  • 2017年01月06日 14:50
  • 389

C#对Excel的基本操作

Office2007尚未普及,Office2000~2003仍然是主流。本文介绍一些C#处理Excel得方法,都是自己封装的~1. 添加COM组件的Reference2. 引入名字空间using Mi...
  • fallriver
  • fallriver
  • 2008年04月24日 14:53
  • 1664

自己用的c#操作excel类。

下面的程序,是自己经常用到的excel操作类,本程序与别的对excel操作唯一改进的地方是对excel数据的填充方式上。网上经常看到的填充方式有:1:按单元格2:按区域(range)本程序,也还是按区...
  • yangang0201
  • yangang0201
  • 2007年07月13日 09:56
  • 4119

C#(源码)操作Excel大全1

C#对excel的操作
  • u011981242
  • u011981242
  • 2015年09月24日 10:35
  • 392

C#学习之操作excel表格

-------------------自己的实践方案          今天开始学习C#使用Excel。          首先,要先添加引用:   若发现没有这个项,可以重新安装office,我...
  • HK_5788
  • HK_5788
  • 2015年10月18日 01:21
  • 3431

C#中操作Excel单元格合并

这里主要是对于一个已知的EXCEL进行合并里面的单元格的方法 string savePath="C:\\Users\\Administrator\\Desktop\\xxx.xls";//exc...
  • gongzizoule
  • gongzizoule
  • 2015年06月11日 11:52
  • 2374

c# excel转PDF

PS: 公司的业务中有个超级大的作业就是把OFFICE文档转成PDF,我猜之前没程序猿们,公司那些人应该是一个个手动转。强烈为猿们感叹,帮你们做了这么多事,还在那抱怨....无法满足你们的需求啊; ...
  • chenqiangdage
  • chenqiangdage
  • 2014年03月03日 23:54
  • 4171
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:转帖:C#操作Excel
举报原因:
原因补充:

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