Excel Manager
//http://karbel.codeplex.com/
//Common Development and Distribution License (CDDL)
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Configuration;
using System.Diagnostics;
using System.Reflection;
using System.Data;
using System.Collections;
using System.IO;
namespace Karbel.Excel
{
public class ExcelManager
{
#region Excel
string filePath;
public string FilePath
{
get { return filePath; }
set { filePath = value; }
}
public ExcelManager(string FilePath,bool CreateNew,bool IsVisible)
{
Application = new Microsoft.Office.Interop.Excel.Application();
this.FilePath = FilePath;
this.IsVisible = IsVisible;
if (CreateNew)
{
Workbook = Application.Workbooks.Add(miss);
}
else
{
Workbook = Application.Workbooks.Open(FilePath, false, false, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
}
}
private Missing miss = System.Reflection.Missing.Value;
public ExcelManager(string FilePath, bool IsVisible,int WaitSecond)
{
Application = new Microsoft.Office.Interop.Excel.Application();
IsVisible = IsVisible;
Workbook = Application.Workbooks.Open(FilePath, miss, miss, miss, miss, miss, true, miss, miss, false, false, miss, miss, miss, miss);
System.Threading.Thread.Sleep(1000 * WaitSecond);
}
public bool IsVisible
{
get
{
return Application.Visible;
}
set
{
Application.Visible = value;
}
}
#endregion
#region Application
private Microsoft.Office.Interop.Excel.Application application;
public Microsoft.Office.Interop.Excel.Application Application
{
get { return application; }
set { application = value; }
}
#endregion
#region Workbook
private Microsoft.Office.Interop.Excel.Workbook workbook;
public Microsoft.Office.Interop.Excel.Workbook Workbook
{
get { return workbook; }
set { workbook = value; }
}
#endregion
#region Save
public void Save()
{
if (Workbook != null)
{
Workbook.SaveAs(FilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
public void SaveAs(string FileName)
{
if (Workbook != null)
{
Workbook.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
#endregion
#region GetSheet
public Microsoft.Office.Interop.Excel.Worksheet GetSheet(int Index)
{
return (Microsoft.Office.Interop.Excel.Worksheet)Workbook.Worksheets.get_Item(Index);
}
public int SheetCount
{
get
{
return Workbook.Worksheets.Count;
}
}
#endregion
#region Border
private void Border(Range range)
{
range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, System.Type.Missing);
}
#endregion
#region Kill
public void Kill()
{
if (Application == null)
{
return;
}
FileInfo fi = new FileInfo(FilePath);
//To prevent asking file to save
Random rnd = new Random();
string tempFileName;
try
{
tempFileName = Environment.GetFolderPath(Environment.SpecialFolder.InternetCache) + "\\" + fi.Name.Replace(fi.Name, "devDelable" + rnd.Next(1, 10000000) + "_" + fi.Name);
Application.Save(tempFileName);
}
catch (Exception)
{
tempFileName = fi.FullName.Replace(fi.Name, "devDelable" + rnd.Next(1, 10000000) + "_" + fi.Name);
Application.Save(tempFileName);
}
try
{
FileInfo fTemp = new FileInfo(tempFileName);
foreach (FileInfo tmpFile in fTemp.Directory.GetFiles("devDelable*.*"))
{
tmpFile.Delete();
}
}
catch
{ }
Application.Workbooks.Close();
Application.Quit();
//Purpose: Get the process ID of the Excel instance. This is used to prevent orphaned Excel processes.
Process[] processes = Process.GetProcesses();
int processID = 0;
bool originalVisibility;
int i;
originalVisibility = Application.Visible;
Application.Visible = true;
for (i = 0; i <= processes.GetUpperBound(0); i++)
{
if (processes[i].MainWindowHandle.ToString() == Application.Hwnd.ToString())
{
processID = processes[i].Id;
break;
}
}
Application.Visible = originalVisibility;
//Purpose: Look for an Excel process matching the process id
Process process = null;
for (i = 0; i <= processes.GetUpperBound(0); i++)
{
if (processes[i].Id == processID)
{
process = processes[i];
break;
}
}
//Make sure we have a matching process
if (process != null)
{
//Make sure it is an excel process
if (process.ProcessName.ToUpper() == "EXCEL")
{
//Make sure the process has not exited
if (!process.HasExited)
{
//Make sure the process no longer has a main window.
//if (aProcess.MainWindowHandle.ToString() == "0")
process.Kill();
//else
//MessageBox.Show("Excel is still open but not longer being used by this program. You may close Excel if you are no longer using it.");
}
}
}
Application = null;
}
#endregion
#region DataSource2Array
private object[,] DataSource2Array(System.Data.DataTable datatable)
{
object[,] arrData;
arrData = new object[datatable.Rows.Count, datatable.Columns.Count];
for (int i = 0; i < datatable.Rows.Count; i++)
{
for (int j = 0; j < datatable.Columns.Count; j++)
{
arrData[i, j] = datatable.Rows[i][j];
}
}
return arrData;
}
private object[,] DataSource2Array(System.Data.DataView dataSource)
{
object[,] arrData;
int rowCount = dataSource.Count;
int colCount = dataSource.Table.Columns.Count;
arrData = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
arrData[i, j] = dataSource[i][j];
}
}
return arrData;
}
#endregion
private System.Data.DataTable Object2Datatable(object value)
{
object[,] values;
bool IsArray = value.GetType().IsArray;
if (IsArray)
{
values = (object[,])value;
return Array2Datatable(values);
}
else
{
return Array2Datatable(value);
}
}
#region Array2Datatable
private System.Data.DataTable Array2Datatable(object[,] array)
{
System.Data.DataTable dt = new System.Data.DataTable();
for (int j = 0; j < array.GetLength(1); j++)
{
dt.Columns.Add("Col" + (j+1));
}
for (int i = 0; i < array.GetLength(0) ; i++)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
for (int j = 0; j < array.GetLength(1); j++)
{
dt.Rows[i][j] = array[i+1, j+1];
}
}
return dt;
}
private System.Data.DataTable Array2Datatable(object value)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Col1");
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
dt.Rows[0][0] = value;
return dt;
}
#endregion
public System.Data.DataTable GetRangeValue(int SheetIndex, string From,string To)
{
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet) Workbook.Sheets[SheetIndex];
Range rng = sheet.get_Range(From, To);
Range cell;
object[,] value = (object[,]) rng.Value2;
for (int i = 0; i < rng.EntireRow.Count; i++)
{
for (int j = 0; j < rng.EntireColumn.Count; j++)
{
cell = (Range)rng.Cells[i + 1, j + 1];
}
}
return Array2Datatable(value);
}
#region SetRangeValue
public void SetRangeValue(int ColumnIndex, int RowIndex, Microsoft.Office.Interop.Excel.Worksheet sheet, System.Data.DataTable datatable, bool AutoFit)
{
object[,] data = DataSource2Array(datatable);
SetRangeValue(ColumnIndex, RowIndex, sheet, data, AutoFit);
}
public void SetRangeValue(int ColumnIndex, int RowIndex, Microsoft.Office.Interop.Excel.Worksheet sheet, System.Data.DataView dataSource, bool AutoFit)
{
object[,] data = DataSource2Array(dataSource);
SetRangeValue(ColumnIndex, RowIndex, sheet, data, AutoFit);
}
#endregion
public Microsoft.Office.Interop.Excel.Worksheet CopyToEnd(Microsoft.Office.Interop.Excel.Workbook book, int Index)
{
Worksheet source = ((Worksheet)book.Worksheets[Index]);
Worksheet lastSheet = ((Worksheet)book.Worksheets[book.Worksheets.Count]);
source.Copy(Missing.Value, lastSheet);
lastSheet = ((Worksheet)book.Worksheets[book.Worksheets.Count]);
return lastSheet;
}
#region SetRangeValue
public void SetRangeValue(int ColumnIndex, int RowIndex, Microsoft.Office.Interop.Excel.Worksheet sheet, object[,] data, bool AutoFit)
{
Range rng = sheet.get_Range(sheet.Cells[RowIndex, ColumnIndex], sheet.Cells[RowIndex + data.GetLength(0) - 1, ColumnIndex + data.GetLength(1) - 1]);
rng.Value2 = data;
if (AutoFit)
{
rng.EntireColumn.AutoFit();
}
}
#endregion
#region SetRangeValue
public void SetCellValue(int ColumnIndex, int RowIndex, Microsoft.Office.Interop.Excel.Worksheet sheet, object data)
{
sheet.get_Range(sheet.Cells[RowIndex, ColumnIndex], sheet.Cells[RowIndex, ColumnIndex]).Value2 = data;
}
public void SetCellValue(int ColumnIndex, int RowIndex, Microsoft.Office.Interop.Excel.Worksheet sheet, object data, bool AutoFit, XlHAlign HorizantalAlignment)
{
Range rng = sheet.get_Range(sheet.Cells[RowIndex, ColumnIndex], sheet.Cells[RowIndex, ColumnIndex]);
rng.Value2 = data;
rng.HorizontalAlignment = HorizantalAlignment;
if (AutoFit)
{
rng.EntireColumn.AutoFit();
}
}
#endregion
public System.Data.DataTable GetDefinedNameValue(string Name)
{
object value = workbook.Names.Item(Name, miss, miss).RefersToRange.Value2;
return Object2Datatable(value);
}
public string GetDefinedNameCellValue(string Name)
{
object value = workbook.Names.Item(Name, miss, miss).RefersToRange.Value2;
return value.ToString();
}
#region SetDefinedNameValue
public void SetDefinedNameValue(string Name, Microsoft.Office.Interop.Excel.Workbook workbook, object data)
{
workbook.Names.Item(Name, miss, miss).RefersToRange.Value2 = data;
}
public void SetDefinedNameValue(string Name, Microsoft.Office.Interop.Excel.Worksheet worksheet, object data)
{
worksheet.Names.Item(Name, miss, miss).RefersToRange.Value2 = data;
}
public void DeleteColumns(Worksheet ws, string From, string To)
{
ws.get_Range(From + ":" + From, To + ":" + To).Delete(XlDeleteShiftDirection.xlShiftToLeft);
}
public double GetSum(Worksheet ws, string From, string To)
{
Range TotalSumCell = ws.get_Range("DD1", "DD1");
TotalSumCell.FormulaLocal = "=TOPLA(" + From + ":" + To + ")";
double TotalSum = (double)TotalSumCell.Value2;
TotalSumCell.FormulaLocal = "";
return TotalSum;
}
#endregion
public void DeleteWorksheet(Workbook workbook, int Index)
{
Worksheet ws = (Worksheet)workbook.Worksheets[Index];
ws.Delete();
}
}
}
Related examples in the same category