Excel操作類C#版

//引入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, 10, 10, 150, 150);

//後面的數字表示位置

}

//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();

}

Excel操作類C#版

引用通告位址: http://tmsoft.lsxy.com/trackback.php?tbID=415&extra=ba1213

----------------------------------------------------------------------------------------------------------------

引用Microsoft.Office.Interop.Excel出現的問題
08-06-11 14:41 View:1022
操作背景:asp.net操作Excel
出現問題:在本地添加引用(com):Microsoft Office 11.0 Object Library,并寫好程式調試正常,部署到伺服器時,出現異常 Excel.Application不是對象.
初步診斷:伺服器沒有安裝Excel元件
第一步嘗試解決:對伺服器安裝Excel等Office元件,進一步測試程式:失敗!
第二步嘗試解決:将Excel.exe生成Interop.Excel.dll,然後用sdk引用該Dll,編譯成功,測試程式:成功!
原因:本地引用的com不會在程式的bin目錄生成dll檔案,而程式是根據路徑在尋找dll的.部署到伺服器上時,假如Excel等dll與本地路徑不一緻,将會抛出異常,定義的Excel對象肯定是不存在的.
具體方法:
1、如何生成Interop.Excel.dll？
進入你的visual studio的sdk下的bin目錄，找到TlbImp.exe檔案，如果沒有，請用CD光牒安裝此檔案，詳細說明請參照MSDN。
指令行(cmd)進入bin目錄，運作TlbImp /out:Interop.Excel.dll Office安裝目錄+Excel.exe
此時很可能會報錯：TlbImp error: Unable to locate input type library: 'c:/program files/mcrosoft offi
ce/office/EXCEL.EXE'
此問題很有可能是TlbImp的bug，不支援空格式的路徑；（具體原因不明）不要緊，将Excel.exe拷貝入bin目錄，直接運作TlbImp /out:Interop.Excel.dll Excel.exe,提示“Type library imported to Interop.Excel.dll路徑”
在bin目錄下找到Interop.Excel.dll檔案。在你的visual studio裡将其引用即可。
2、如果是excel2000或excel2002怎麼辦？
如果是Excel2000，則将Excel.exe改成Excel9.olb
Excel2002同2003
3、各種版本的引用元件參數如下：
檔案/版本	Interop.Excel.dll	Interop.Office.dll	Interop.VBIDE.dll	添加引用/COM元件
2000	V1.3.0.0	V2.1.0.0	V5.3.0.0	Microsoft Excel 9.0 Object Library(EXCEL9.OLB)
2002(XP)	V1.4.0.0	V2.2.0.0	V5.3.0.0	Microsoft Excel 10.0 Object Library(Excel.EXE檔案)
2003	V1.5.0.0	V2.3.0.0	V5.3.0.0	Microsoft Excel 11.0 Object Library(Excel.EXE檔案)
在office的安裝目錄内找到那個檔案就可以了
，我找到的是Microsoft.Office.Interop.Excel.dll檔案，拷到程式目錄就OK。

Excel操作類C#版

繼續閱讀

GNU科學函數庫[參考手冊][v0.1 Build 090129 Beta][GNU Scientific Library]

GNU科學函數庫[參考手冊][v0.1 Build 090201 Beta][GNU Scientific Library]

C# 位元組數組 byte[] 快速比較算法

C# 位元組數組、字元串轉化位元組數組轉字元串字元串轉位元組數組位元組數組轉整數整數轉位元組數組字元串轉位元組字元串轉數值數值轉字元串

C#用Zlib壓縮或解壓縮位元組數組

C# 比較兩個位元組數組是否相等

對于VS中scanf報錯永久解決，建立源檔案自動添加#define _CRT_SECURE_NO_WARNINGS 1

《C#進階程式設計》讀書筆記 -索引

swift資料合集

CQ V1.0分詞bates(基于雙數組tire樹)—應該是目前最快的中文分詞算法

C#多線程——前台線程和背景線程

QName是什麼

C#+ArcEngine代碼檢測ArcEngine Runtime是否安裝以及是否授權

Android – ListView 中添加按鈕，動态删除添加ItemView的操作

七牛雲-C#SDK-上傳-前期準備

GridView終極用法(一)