悠闲小风专栏

SharePoint & Workflow 解决方案

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

转载自:飞鸟的博客 C# Excel操作类

 

经常碰到需要操作Excel的情况,特别是涉及到DataTable和GridView之类东东的时候,导入导出Excel,并定制样式,调整字段等等的操作就成了家常便饭.

//引入Excel的COM组件

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Office.Interop;
using Microsoft.Office.Core;


namespace ExcelEdit
{
    
/// <summary>
    
/// ExcelEdit 的摘要说明
    
/// </summary>
    public class ExcelEdit
    {
        
public string mFilename;
        
public Excel.Application app;
        
public Excel.Workbooks wbs;
        
public Excel.Workbook wb;
        
public Excel.Worksheets wss;
        
public Excel.Worksheet ws;
        
public ExcelEdit()
        {
            
//
            
// TODO: 在此处添加构造函数逻辑
            
//
        }
        
public void Create()//创建一个Excel对象
        {
            app 
= new Excel.Application();
            wbs 
= app.Workbooks;
            wb 
= wbs.Add(true);
        }
        
public void Open(string FileName)//打开一个Excel文件
        {
            app 
= new Excel.Application();
            wbs 
= app.Workbooks;
            wb 
= wbs.Add(FileName);
            
//wb = wbs.Open(FileName,  0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true,Type.Missing,Type.Missing);
            
//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
            mFilename = FileName;
        }
        
public Excel.Worksheet GetSheet(string SheetName)//获取一个工作表
        {
            Excel.Worksheet s 
= (Excel.Worksheet)wb.Worksheets[SheetName];
            
return s;
        }
        
public Excel.Worksheet AddSheet(string SheetName)//添加一个工作表
        {
            Excel.Worksheet s 
= (Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            s.Name 
= SheetName;
            
return s;
        }

        
public void DelSheet(string SheetName)//删除一个工作表
        {
            ((Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
        }
        
public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
        {
            Excel.Worksheet s 
= (Excel.Worksheet)wb.Worksheets[OldSheetName];
            s.Name 
= NewSheetName;
            
return s;
        }

        
public Excel.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二
        {

            Sheet.Name 
= NewSheetName;

            
return Sheet;
        }

        
public void SetCellValue(Excel.Worksheet ws, int x, int y, object value)//ws:要设值的工作表     X行Y列     value   值 
        {
            ws.Cells[x, y] 
= value;
        }
        
public void SetCellValue(string ws, int x, int y, object value)//ws:要设值的工作表的名称 X行Y列 value 值
        {

            GetSheet(ws).Cells[x, y] 
= value;
        }

        
public void SetCellProperty(Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)//设置一个单元格的属性   字体,   大小,颜色   ,对齐方式
        {
            name 
= "宋体";
            size 
= 12;
            color 
= Excel.Constants.xlAutomatic;
            HorizontalAlignment 
= Excel.Constants.xlRight;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name 
= name;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size 
= size;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color 
= color;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment 
= HorizontalAlignment;
        }

        
public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)
        {
            
//name = "宋体";
            
//size = 12;
            
//color = Excel.Constants.xlAutomatic;
            
//HorizontalAlignment = Excel.Constants.xlRight;

            Excel.Worksheet ws 
= GetSheet(wsn);
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name 
= name;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size 
= size;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color 
= color;

            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment 
= HorizontalAlignment;
        }


        
public void UniteCells(Excel.Worksheet ws, int x1, int y1, int x2, int y2)//合并单元格
        {
            ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
        }

        
public void UniteCells(string ws, int x1, int y1, int x2, int y2)//合并单元格
        {
            GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);

        }


        
public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
        {

            
for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                
for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    GetSheet(ws).Cells[startX 
+ i, j + startY] = dt.Rows[i][j].ToString();

                }

            }

        }
        
public void InsertTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置二
        {

            
for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                
for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {

                    ws.Cells[startX 
+ i, j + startY] = dt.Rows[i][j];

                }

            }

        }


        
public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置一
        {

            
for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                
for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {

                    GetSheet(ws).Cells[i 
+ startX, j + startY] = dt.Rows[i][j];

                }

            }

        }
        
public void AddTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置二
        {


            
for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                
for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {

                    ws.Cells[i 
+ startX, j + startY] = dt.Rows[i][j];

                }
            }

        }
        
public void InsertPictures(string Filename, string ws)//插入图片操作一
        {
            GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 
1010150150);//后面的数字表示位置
        }

        
//public void InsertPictures(string Filename, string ws, int Height, int Width)//插入图片操作二
        
//{
        
//    GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
        
//    GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;
        
//    GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;
        
//}
        
//public void InsertPictures(string Filename, string ws, int left, int top, int Height, int Width)//插入图片操作三
        
//{

        
//    GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
        
//    GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left);
        
//    GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top);
        
//    GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;
        
//    GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;
        
//}

        
public void InsertActiveChart(Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Excel.XlRowCol ChartDataType)//插入图表操作
        {
            ChartDataType 
= Excel.XlRowCol.xlColumns;
            wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            {
                wb.ActiveChart.ChartType 
= ChartType;
                wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType);
                wb.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws);
            }
        }
        
public bool Save()//保存文档
        {
            
if (mFilename == "")
            {
                
return false;
            }
            
else
            {
                
try
                {
                    wb.Save();
                    
return true;
                }

                
catch (Exception ex)
                {
                    
return false;
                }
            }
        }
        
public bool SaveAs(object FileName)//文档另存为
        {
            
try
            {
                wb.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                
return true;

            }

            
catch (Exception ex)
            {
                
return false;

            }
        }
        
public void Close()//关闭一个Excel对象,销毁对象
        {
            
//wb.Save();
            wb.Close(Type.Missing, Type.Missing, Type.Missing);
            wbs.Close();
            app.Quit();
            wb 
= null;
            wbs 
= null;
            app 
= null;
            GC.Collect();
        }
    }
}

 

 

posted on 2010-11-24 16:46  陈典洪  阅读(672)  评论(0编辑  收藏  举报