using System;
using System.Data;
using System.Configuration;
using System.Web;
namespace Storm2.CommonLib
{
/// <summary>
/// ExcelEdit
/// </summary>
public class ExcelEdit : IDisposable
{
public string mFileName;
private Microsoft.Office.Interop.Excel.Application oAP;
private Microsoft.Office.Interop.Excel.Workbooks oWBS;
private Microsoft.Office.Interop.Excel.Workbook oWB;
public ExcelEdit()
{
}
//********************************************************
//** Description 丗 Creat Excel
//** Parameter 丗
//** Return 丗
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public void Create()
{
oAP = new Microsoft.Office.Interop.Excel.Application();
oWBS = oAP.Workbooks;
oWB = oWBS.Add(true);
}
//********************************************************
//** Description 丗 Open Excel
//** Parameter 丗 FileName - open file name
//** Return 丗
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public void Open(string fileName)
{
oAP = new Microsoft.Office.Interop.Excel.Application();
oWBS = oAP.Workbooks;
oWB = oWBS.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
mFileName = fileName;
}
//********************************************************
//** Description 丗 Get Excel Sheet
//** Parameter 丗 SheetName - get sheet name
//** Return 丗 workSheet - Object
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string sheetName)
{
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[sheetName];
return workSheet;
}
//********************************************************
//** Description 丗 Addition Excel Sheet
//** Parameter 丗 SheetName - append sheet name
//** Return 丗 workSheet - Object
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public object AddSheet(string sheetName)
{
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workSheet.Name = sheetName;
return workSheet;
}
//********************************************************
//** Description 丗 Deletion Excel Sheet
//** Parameter 丗 SheetName - deletion sheet name
//** Return 丗
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public void DelSheet(string sheetName)
{
((Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[sheetName]).Delete();
}
//********************************************************
//** Description 丗 Copy Excel Sheet
//** Parameter 丗 OldSheetName - old sheet name
// NewSheetName - copy new sheet
//** Return 丗 workSheet - Object
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public object RenameSheet(string oldSheetName, string newSheetName)
{
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[oldSheetName];
workSheet.Name = newSheetName;
return workSheet;
}
public object RenameSheet(Microsoft.Office.Interop.Excel.Worksheet sheet, string newSheetName)
{
sheet.Name = newSheetName;
return sheet;
}
//********************************************************
//** Description 丗 Get Cell Value
//** Parameter 丗 workSheet - get sheet name
// x - row
// y - loc
//** Return 丗 Cell's Value
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public string GetCellValue(Microsoft.Office.Interop.Excel.Worksheet workSheet, int x, int y)
{
//return ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[x, y]).Text.ToString();
return SetNullToEmpty(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[x, y]).Text);
}
public string GetCellValue(string workSheetName, int x, int y)
{
//return ((Microsoft.Office.Interop.Excel.Range)GetSheet(workSheetName).Cells[x, y]).Text.ToString();
return SetNullToEmpty(((Microsoft.Office.Interop.Excel.Range)GetSheet(workSheetName).Cells[x, y]).Text);
}
//********************************************************
//** Description 丗 Set Cell Value
//** Parameter 丗 workSheet - get sheet name
// x - row
// y - loc
// value - object value
//** Return 丗
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet workSheet, int x, int y, object value)
{
workSheet.Cells[x, y] = value;
}
public void SetCellValue(string workSheet, int x, int y, object value)
{
GetSheet(workSheet).Cells[x, y] = value;
}
//*******************************************************
//** Description 丗 Set Cell Property
//** Parameter 丗 workSheet ---sheet object
//** 丗 Startx ---row start position
//** 丗 Starty ---col start position
//** 丗 Endx ---row start position
//** 丗 Endy ---col start position
//** 丗 name ---sheet name
//** 丗 size ---font size
//** 丗 color ---font color
//** 丗 bold ---font bold
//** 丗 hAlignment ---Horizontal position
//** Return 丗
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet workSheet, int Startx, int Starty, int Endx, int Endy, string name, int size, int color, bool bold, object hAlignment
)
{
workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Name = name;
workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Bold = bold;
workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Size = size;
workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Color = color;
workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).HorizontalAlignment = hAlignment;
}
public void SetCellProperty(string workSheetName, int Startx, int Starty, int Endx, int Endy, string name, int size, int color, bool bold, object hAlignment
)
{
Microsoft.Office.Interop.Excel.Worksheet workSheet = GetSheet(workSheetName);
workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Name = name;
workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Bold = bold;
workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Size = size;
workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Color = color;
workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).HorizontalAlignment = hAlignment;
}
//********************************************************
//** Description 丗 Sheet Unite Cells
//** Parameter 丗 workSheet - sheet object
// x1 - row start position
// y1 - col start position
// x2 - row end position
// y2 - col end position
//** Return 丗
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public void MergeCells(Microsoft.Office.Interop.Excel.Worksheet workSheet, int x1, int y1, int x2, int y2)
{
workSheet.get_Range(workSheet.Cells[x1, y1], workSheet.Cells[x2, y2]).Merge(Type.Missing);
}
public void MergeCells(string workSheetName, int x1, int y1, int x2, int y2)
{
GetSheet(workSheetName).get_Range(GetSheet(workSheetName).Cells[x1, y1], GetSheet(workSheetName).Cells[x2, y2]).Merge(Type.Missing);
}
//********************************************************
//** Description 丗 more information Insert Sheet
//** Parameter 丗 dt - DataTable
// workSheetName - sheet name
// startX - row start position
// startY - col start position
//** Return 丗
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public void InsertTable(DataTable dt, string workSheetName, int startX, int startY)
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
GetSheet(workSheetName).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
}
}
}
public void InsertTable(DataTable dt, Microsoft.Office.Interop.Excel.Worksheet workSheet, int startX, int startY)
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
workSheet.Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
}
}
}
//********************************************************
//** Description 丗 Save File
//** Parameter 丗
//** Return 丗 True - success
// False - failure
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public bool Save()
{
if (string.IsNullOrEmpty(mFileName))
{
return false;
}
else
{
oWB.Save();
return true;
}
}
//********************************************************
//** Description 丗 Save File
//** Parameter 丗 FileName - save file name
//** Return 丗 True - success
// False - failure
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public bool SaveAs(string fileName)
{
if (string.IsNullOrEmpty(fileName))
{
return false;
}
else
{
mFileName = fileName;
oWB.SaveAs(mFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
return true;
}
}
public void Dispose()
{
if (oWB != null)
oWB = null;
if (oWBS != null)
oWBS = null;
if (oAP != null)
{
oAP.Quit();
oAP = null;
}
GC.Collect();
}
//********************************************************
//** Description 丗 Close File
//** Parameter 丗
//** Return 丗
//** Created By 丗 ShenJiXian
//** Created Date丗 Sept 25, 2008
//********************************************************
public void Close()
{
oWB.Close(true, mFileName, Type.Missing);
oWBS.Close();
oAP.Quit();
oWB = null;
oWBS = null;
oAP = null;
GC.Collect();
}
private string SetNullToEmpty(Object mValue)
{
if (null == mValue || string.Empty.Equals(mValue))
{
return string.Empty;
}
else
{
return mValue.ToString();
}
}
}
}