C# 操作Excel ——Excel获取数据、时间、图片
来源:互联网 发布:毛笔字在线生成器软件 编辑:程序博客网 时间:2024/05/18 18:02
public class ExcelControl:ConvertObject
{
//Fields
private _Workbook _objBook=null ;
private Application _objExcel=null ;
private object _objOpt=Missing.Value ;
private Range _objRange=null ;
private _Worksheet _objWorkSheet=null ;
private Sheets _objSheets=null ;
private string _savePath;//文件保存路径
private int _sheetIndex=1;//工作表的索引
private string _templatePath;//文件模板路径
//Properties
public string SavePath
{
get { return _savePath; }
set { _savePath = value; }
}
public int SheetIndex
{
get { return _sheetIndex; }
set { _sheetIndex = value; }
}
public string TemplatePath
{
get { return _templatePath; }
set { _templatePath = value; }
}
//Methods
public ExcelControl()
{
this._objExcel = new ApplicationClass();
}
public void CellsAlignment(int startRow, int startColumn, int endRow, int endColumn, XlHAlign hAlign, XlVAlign vAlign)
{
Range range = this._objWorkSheet.get_Range(this._objWorkSheet.Cells[startRow, startColumn], this._objWorkSheet.Cells[endRow, endColumn]);
range.HorizontalAlignment = hAlign;
range.VerticalAlignment = vAlign;
}
public void CellsUnite(int startRow, int startColumn, int endRow, int endColumn)
{
this._objWorkSheet.get_Range(this._objWorkSheet.Cells[startRow, startColumn], this._objWorkSheet.Cells[endRow, endColumn]).MergeCells = true;
}
/// <summary>
/// 关闭excel
/// </summary>
public void Close()
{
try
{
this._objBook.Close(_objOpt, _objOpt, _objOpt);
this._objExcel.Workbooks.Close();
this._objExcel.Quit();
Marshal.ReleaseComObject(_objBook);
Marshal.ReleaseComObject(_objExcel);
this._objBook = null;
this._objExcel = null;
GC.Collect();
}
catch(Exception ex)
{
ErrMessage = ex.Message;
}
}
/// <summary>
/// 获取excel中单元格数据
/// </summary>
/// <param name="range"></param>
/// <returns></returns>
public object GetRange(object range)
{
try
{
this._objRange = this._objWorkSheet.get_Range(range, this._objOpt);
return this._objRange.Value2;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return null;
}
}
/// <summary>
/// 向excel中插入图片
/// </summary>
/// <param name="pictureName">图片名称</param>
/// <param name="left">左边宽度</param>
/// <param name="top">顶部宽度</param>
/// <param name="width">宽度</param>
/// <param name="height">高度</param>
public void InsertPictures(string pictureName, int left, int top, int width, int height)
{
this._objWorkSheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, (float)left, (float)top, (float)width, (float)height);
}
public bool InsertRow(object rowID, object colunmID)
{
try
{
Range range = (Range)this._objWorkSheet.Cells[rowID, colunmID];
range.Select();
range.EntireRow.Insert(this._objOpt, this._objOpt);
return true;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 打开excel
/// </summary>
/// <returns></returns>
public bool Open()
{
try
{
this._objBook = this._objExcel.Workbooks.Open(this._templatePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
this._objSheets = this._objBook.Worksheets;
return true;
}
catch(Exception e)
{
this.ErrMessage = e.Message;
return false;
}
}
/// <summary>
/// 打开模板
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public bool Open(string filePath)
{
this._templatePath = filePath;
try
{
this._objBook = this._objExcel.Workbooks.Open(this._templatePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
this._objSheets = this._objBook.Worksheets;
return true;
}
catch (Exception e)
{
this.ErrMessage = e.Message;
return false;
}
}
/// <summary>
/// 行自动适合宽度
/// </summary>
/// <param name="rowNum"></param>
public void RowAutoFit(int rowNum)
{
Range range = (Range)this._objWorkSheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 保存
/// </summary>
/// <returns></returns>
public bool Save()
{
try
{
this._objBook.Save();
return true;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return false;
}
}
/// <summary>
/// 另存为
/// </summary>
/// <returns></returns>
public bool SaveAs()
{
try
{
this._objBook.SaveAs(this._savePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, XlSaveAsAccessMode.xlNoChange, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
return true;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return false;
}
}
public bool SaveAs(string newPath)
{
this._savePath = newPath;
try
{
this._objBook.SaveAs(this._savePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, XlSaveAsAccessMode.xlNoChange, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
return true;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return false;
}
}
/// <summary>
/// 工作表激活状态
/// </summary>
/// <returns></returns>
public bool SetActiveSheer()
{
try
{
this._objWorkSheet = (_Worksheet)this._objSheets.get_Item(this._sheetIndex);
return true;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return false;
}
}
/// <summary>
///
/// </summary>
/// <param name="activeIndex"></param>
/// <returns></returns>
public bool SetActiveSheer(int activeIndex)
{
this._sheetIndex = activeIndex;
try
{
this._objWorkSheet = (_Worksheet)this._objSheets.get_Item(this._sheetIndex);
return true;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return false;
}
}
/// <summary>
/// 设置显示警告
/// </summary>
/// <param name="b"></param>
public void SetDisplayAlerts(bool b)
{
this._objExcel.DisplayAlerts = b;
}
/// <summary>
/// 向excel中插入值
/// </summary>
/// <param name="range">插入位置</param>
/// <param name="newValue">插入值</param>
public void SetRange(object range, object newValue)
{
try
{
this._objRange = this._objWorkSheet.get_Range(range, this._objOpt);
this._objRange.Value2 = newValue;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
}
}
/// <summary>
/// 获取excel中时间格式
/// </summary>
/// <param name="strDate"></param>
/// <returns></returns>
public DateTime GetConvertDate(string strDate)
{
return DateTime.FromOADate(Convert.ToDouble(strDate));
}
}
{
//Fields
private _Workbook _objBook=null ;
private Application _objExcel=null ;
private object _objOpt=Missing.Value ;
private Range _objRange=null ;
private _Worksheet _objWorkSheet=null ;
private Sheets _objSheets=null ;
private string _savePath;//文件保存路径
private int _sheetIndex=1;//工作表的索引
private string _templatePath;//文件模板路径
//Properties
public string SavePath
{
get { return _savePath; }
set { _savePath = value; }
}
public int SheetIndex
{
get { return _sheetIndex; }
set { _sheetIndex = value; }
}
public string TemplatePath
{
get { return _templatePath; }
set { _templatePath = value; }
}
//Methods
public ExcelControl()
{
this._objExcel = new ApplicationClass();
}
public void CellsAlignment(int startRow, int startColumn, int endRow, int endColumn, XlHAlign hAlign, XlVAlign vAlign)
{
Range range = this._objWorkSheet.get_Range(this._objWorkSheet.Cells[startRow, startColumn], this._objWorkSheet.Cells[endRow, endColumn]);
range.HorizontalAlignment = hAlign;
range.VerticalAlignment = vAlign;
}
public void CellsUnite(int startRow, int startColumn, int endRow, int endColumn)
{
this._objWorkSheet.get_Range(this._objWorkSheet.Cells[startRow, startColumn], this._objWorkSheet.Cells[endRow, endColumn]).MergeCells = true;
}
/// <summary>
/// 关闭excel
/// </summary>
public void Close()
{
try
{
this._objBook.Close(_objOpt, _objOpt, _objOpt);
this._objExcel.Workbooks.Close();
this._objExcel.Quit();
Marshal.ReleaseComObject(_objBook);
Marshal.ReleaseComObject(_objExcel);
this._objBook = null;
this._objExcel = null;
GC.Collect();
}
catch(Exception ex)
{
ErrMessage = ex.Message;
}
}
/// <summary>
/// 获取excel中单元格数据
/// </summary>
/// <param name="range"></param>
/// <returns></returns>
public object GetRange(object range)
{
try
{
this._objRange = this._objWorkSheet.get_Range(range, this._objOpt);
return this._objRange.Value2;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return null;
}
}
/// <summary>
/// 向excel中插入图片
/// </summary>
/// <param name="pictureName">图片名称</param>
/// <param name="left">左边宽度</param>
/// <param name="top">顶部宽度</param>
/// <param name="width">宽度</param>
/// <param name="height">高度</param>
public void InsertPictures(string pictureName, int left, int top, int width, int height)
{
this._objWorkSheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, (float)left, (float)top, (float)width, (float)height);
}
public bool InsertRow(object rowID, object colunmID)
{
try
{
Range range = (Range)this._objWorkSheet.Cells[rowID, colunmID];
range.Select();
range.EntireRow.Insert(this._objOpt, this._objOpt);
return true;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 打开excel
/// </summary>
/// <returns></returns>
public bool Open()
{
try
{
this._objBook = this._objExcel.Workbooks.Open(this._templatePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
this._objSheets = this._objBook.Worksheets;
return true;
}
catch(Exception e)
{
this.ErrMessage = e.Message;
return false;
}
}
/// <summary>
/// 打开模板
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public bool Open(string filePath)
{
this._templatePath = filePath;
try
{
this._objBook = this._objExcel.Workbooks.Open(this._templatePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
this._objSheets = this._objBook.Worksheets;
return true;
}
catch (Exception e)
{
this.ErrMessage = e.Message;
return false;
}
}
/// <summary>
/// 行自动适合宽度
/// </summary>
/// <param name="rowNum"></param>
public void RowAutoFit(int rowNum)
{
Range range = (Range)this._objWorkSheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 保存
/// </summary>
/// <returns></returns>
public bool Save()
{
try
{
this._objBook.Save();
return true;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return false;
}
}
/// <summary>
/// 另存为
/// </summary>
/// <returns></returns>
public bool SaveAs()
{
try
{
this._objBook.SaveAs(this._savePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, XlSaveAsAccessMode.xlNoChange, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
return true;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return false;
}
}
public bool SaveAs(string newPath)
{
this._savePath = newPath;
try
{
this._objBook.SaveAs(this._savePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, XlSaveAsAccessMode.xlNoChange, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
return true;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return false;
}
}
/// <summary>
/// 工作表激活状态
/// </summary>
/// <returns></returns>
public bool SetActiveSheer()
{
try
{
this._objWorkSheet = (_Worksheet)this._objSheets.get_Item(this._sheetIndex);
return true;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return false;
}
}
/// <summary>
///
/// </summary>
/// <param name="activeIndex"></param>
/// <returns></returns>
public bool SetActiveSheer(int activeIndex)
{
this._sheetIndex = activeIndex;
try
{
this._objWorkSheet = (_Worksheet)this._objSheets.get_Item(this._sheetIndex);
return true;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
return false;
}
}
/// <summary>
/// 设置显示警告
/// </summary>
/// <param name="b"></param>
public void SetDisplayAlerts(bool b)
{
this._objExcel.DisplayAlerts = b;
}
/// <summary>
/// 向excel中插入值
/// </summary>
/// <param name="range">插入位置</param>
/// <param name="newValue">插入值</param>
public void SetRange(object range, object newValue)
{
try
{
this._objRange = this._objWorkSheet.get_Range(range, this._objOpt);
this._objRange.Value2 = newValue;
}
catch (Exception ex)
{
this.ErrMessage = ex.Message;
}
}
/// <summary>
/// 获取excel中时间格式
/// </summary>
/// <param name="strDate"></param>
/// <returns></returns>
public DateTime GetConvertDate(string strDate)
{
return DateTime.FromOADate(Convert.ToDouble(strDate));
}
}
- C# 操作Excel ——Excel获取数据、时间、图片
- C# 操作Excel获取数据、时间、图片
- C#获取Excel数据
- C# 操作excel数据
- C#中操作Excel(3)—— Excel中操作文本、图片和批注
- C#获取EXCEL中的数据
- C# 获取Excel中数据
- C#操作读取excel数据
- C# 操作EXCEL(C#中的数据导出EXCEL)
- C# 操作EXCEL(C#中的数据导出EXCEL)
- POI对Excel操作——java读取Excel数据
- 链接服务器——获取EXCEL数据
- 链接服务器——获取EXCEL数据
- C#操作Excel,将DataGridView数据导出到Excel中
- C#操作Excel,将DataGridView数据导出到Excel中
- c#操作Excel和把Dataset数据写入Excel
- C#操作Excel----Excel数据的导入导…
- C# 数据导出Excel,及Excel各种操作
- Python实现在不同Linux主机之间拷贝文件
- 关于宏
- 《三星浴火重生李健熙改革十年》读后感—责任与执行
- VS2010 配置QT
- 类型码,似乎是对于构造成java枚举类似的应用。
- C# 操作Excel ——Excel获取数据、时间、图片
- Spring 2.x AOP声明式配置+声明式事务管理
- Java按行读取文件
- 使用mapjoin效率对比
- 孙鑫VC++学习记录1_windows运行机制
- oracle 自定义函数实例
- SQL Server里的 ISNULL 与 NULLIF
- POJ 2945 Find the Clones
- struts2中文乱码