//1.新增引用-〉com-〉microsoft excel 11.0
//2.若出現錯誤:名稱空間“Microsoft.Office”中不存在型別或名稱空間名稱“Interop”(是缺少程式集引用嗎?)
//解決方法：先刪除引用中的Excel,然後找到檔案Microsoft.Office.Interop.Excel.dll，手動新增該檔案的引用
using System;
using System.Data;
using System.Reflection;
using System.IO;
using Microsoft.Office.Core;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace Wage.Common
{
/// <summary>
/// 作者 Li Aimin (原創)
/// 功能描述：C#對Excel報表進行操作
/// 建立時間：2006－01－17, 修改時間：２００７－１－１4
/// 說明：在工程中需要新增 Excel11.0物件庫的引用（Office 2000為Excel9.0，Office XP為Excel10.0）；
/// 需要在Dcom中配置Excel應用程式的許可權；
/// 伺服器需要安裝Office2003
/// </summary>
public class ExcelLib
{
//http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfexcelapplicationobject.asp
#region Variables
private Excel.Application excelApplication = null;
private Excel.Workbooks excelWorkBooks = null;
private Excel.Workbook excelWorkBook = null;
private Excel.Worksheet excelWorkSheet = null;
private Excel.Range excelRange = null;//Excel Range Object,多種用途
private Excel.Range excelCopySourceRange = null;//Excel Range Object
private int excelActiveWorkSheetIndex; //活動工作表索引
private string excelOpenFileName = ""; //操作Excel的路徑
private string excelSaveFileName = ""; //儲存Excel的路徑
#endregion
#region Properties
public int ActiveSheetIndex
{
get
{
return excelActiveWorkSheetIndex;
}
set
{
excelActiveWorkSheetIndex = value;
}
}
public string OpenFileName
{
get
{
return excelOpenFileName;
}
set
{
excelOpenFileName = value;
}
}
public string SaveFileName
{
get
{
return excelSaveFileName;
}
set
{
excelSaveFileName = value;
}
}
#endregion
//
//--------------------------------------------------------------------------------------------------------
/// <summary>
/// 建構函式；
/// </summary>
public ExcelLib()
{
excelApplication = null;//Excel Application Object
excelWorkBooks = null;//Workbooks
excelWorkBook = null;//Excel Workbook Object
excelWorkSheet = null;//Excel Worksheet Object
ActiveSheetIndex = 1; //預設值活動工作簿為第一個；設定活動工作簿請參閱SetActiveWorkSheet()
}
/// <summary>
/// 以excelOpenFileName為模板新建Excel檔案
/// </summary>
public bool OpenExcelFile()
{
if (excelApplication != null) CloseExcelApplication();
//檢查檔案是否存在
if (excelOpenFileName == "")
{
throw new Exception("請選擇檔案！");
}
if (!File.Exists(excelOpenFileName))
{
throw new Exception(excelOpenFileName + "該檔案不存在！");//該異常如何處理，由什麼處理？？？？
}
try
{
excelApplication = new Excel.ApplicationClass();
excelWorkBooks = excelApplication.Workbooks;
excelWorkBook = ((Excel.Workbook)excelWorkBooks.Open(excelOpenFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets[excelActiveWorkSheetIndex];
excelApplication.Visible = false;
return true;
}
catch (Exception e)
{
CloseExcelApplication();
MessageBox.Show("(1)沒有安裝Excel 2003；(2)或沒有安裝Excel 2003 .NET 可程式設計性支援；\n詳細資訊："
+e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//throw new Exception(e.Message);
return false;
}
}
/// <summary>
/// 讀取一個Cell的值
/// </summary>
/// <param name="CellRowID">要讀取的Cell的行索引</param>
/// <param name="CellColumnID">要讀取的Cell的列索引</param>
/// <returns>Cell的值</returns>
public string getOneCellValue(int CellRowID, int CellColumnID)
{
if (CellRowID <= 0)
{
throw new Exception("行索引超出範圍！");
}
string sValue = "";
try
{
sValue = ((Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID]).Text.ToString();
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
return (sValue);
}
/// <summary>
/// 讀取一個連續區域的Cell的值(矩形區域，包含一行或一列,或多行，多列)，返回一個一維字串陣列。
/// </summary>
/// <param name="StartCell">StartCell是要寫入區域的左上角單元格</param>
/// <param name="EndCell">EndCell是要寫入區域的右下角單元格</param>
/// <returns>值的集合</returns>
public string[] getCellsValue(string StartCell, string EndCell)
{
string[] sValue = null;
//try
//{
excelRange = (Excel.Range)excelWorkSheet.get_Range(StartCell, EndCell);
sValue = new string[excelRange.Count];
int rowStartIndex = ((Excel.Range)excelWorkSheet.get_Range(StartCell, StartCell)).Row; //起始行號
int columnStartIndex = ((Excel.Range)excelWorkSheet.get_Range(StartCell, StartCell)).Column; //起始列號
int rowNum = excelRange.Rows.Count; //行數目
int columnNum = excelRange.Columns.Count; //列數目
int index = 0;
for (int i = rowStartIndex; i < rowStartIndex + rowNum; i++)
{
for (int j = columnStartIndex; j < columnNum + columnStartIndex; j++)
{
//讀到空值null和讀到空串""分別處理
sValue[index] = ((Excel.Range)excelWorkSheet.Cells[i, j]).Text.ToString();
index++;
}
}
//}
//catch (Exception e)
//{
// CloseExcelApplication();
// throw new Exception(e.Message);
//}
return (sValue);
}
/// <summary>
/// 讀取所有單元格的資料(矩形區域)，返回一個datatable.假設所有單元格靠工作表左上區域。
/// </summary>
public DataTable getAllCellsValue()
{
int columnCount = getTotalColumnCount();
int rowCount = getTotalRowCount();
DataTable dt = new DataTable();
//設定datatable列的名稱
for (int columnID = 1; columnID <= columnCount; columnID++)
{
dt.Columns.Add(((Excel.Range)excelWorkSheet.Cells[1, columnID]).Text.ToString());
}
for (int rowID = 2; rowID <= rowCount; rowID++)
{
DataRow dr = dt.NewRow();
for (int columnID = 1; columnID <= columnCount; columnID++)
{
dr[columnID - 1] = ((Excel.Range)excelWorkSheet.Cells[rowID, columnID]).Text.ToString();
//讀到空值null和讀到空串""分別處理
}
dt.Rows.Add(dr);
}
return (dt);
}
public int getTotalRowCount()
{//當前活動工作表中有效行數(總行數)
int rowsNumber = 0;
try
{
while (true)
{
if (((Excel.Range)excelWorkSheet.Cells[rowsNumber + 1, 1]).Text.ToString().Trim() == "" &&
((Excel.Range)excelWorkSheet.Cells[rowsNumber + 2, 1]).Text.ToString().Trim() == "" &&
((Excel.Range)excelWorkSheet.Cells[rowsNumber + 3, 1]).Text.ToString().Trim() == "")
break;
rowsNumber++;
}
}
catch
{
return -1;
}
return rowsNumber;
}
/// <summary>
/// 當前活動工作表中有效列數(總列數)
/// </summary>
/// <param></param>
public int getTotalColumnCount()
{
int columnNumber = 0;
try
{
while (true)
{
if (((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 1]).Text.ToString().Trim() == "" &&
((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 2]).Text.ToString().Trim() == "" &&
((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 3]).Text.ToString().Trim() == "")
break;
columnNumber++;
}
}
catch
{
return -1;
}
return columnNumber;
}
/// <summary>
/// 向一個Cell寫入資料
/// </summary>
/// <param name="CellRowID">CellRowID是cell的行索引</param>
/// <param name="CellColumnID">CellColumnID是cell的列索引</param>
///<param name="Value">要寫入該單元格的資料值</param>
public void setOneCellValue(int CellRowID, int CellColumnID, string Value)
{
try
{
excelRange = (Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID];
excelRange.Value2 = Value;//Value2?
//Gets or sets the value of the NamedRange control.
//The only difference between this property and the Value property is that Value2 is not a parameterized property.
excelRange = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
/// <summary>
/// 設定活動工作表
/// </summary>
/// <param name="SheetIndex">要設定為活動工作表的索引值</param>
public void SetActiveWorkSheet(int SheetIndex)
{
if (SheetIndex <= 0)
{
throw new Exception("索引超出範圍！");
}
try
{
ActiveSheetIndex = SheetIndex;
excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets[ActiveSheetIndex];
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
/// <summary>
/// 向連續區域一次性寫入資料；只有在區域連續和寫入的值相同的情況下可以使用方法
/// </summary>
/// <param name="StartCell">StartCell是要寫入區域的左上角單元格</param>
/// <param name="EndCell">EndCell是要寫入區域的右下角單元格</param>
/// <param name="Value">要寫入指定區域所有單元格的資料值</param>
public void setCellsValue(string StartCell, string EndCell, string Value)
{
try
{
excelRange = excelWorkSheet.get_Range(StartCell, EndCell);
excelRange.Value2 = Value;
excelRange = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}