VS 2005 C# WinForm 实现生成和打开Excel文件(转) (Office 2003,要引用 Office 11 Excel.exe)
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
namespace ExportExcel
{
class ExportBaseExcel
{
#region 私有成员
private Microsoft.Office.Interop.Excel.Application m_objExcel;//Excel应用程序对象
private Microsoft.Office.Interop.Excel.Workbooks m_objBooks;//Excel的Books对象
private Microsoft.Office.Interop.Excel.Workbook m_objBook;//当前Book对象
private Microsoft.Office.Interop.Excel.Worksheet m_objSheet;//当前Sheet对象
private Microsoft.Office.Interop.Excel.Range m_Range;//当前Range对象
private System.Reflection.Missing miss = System.Reflection.Missing.Value;//空数据变量
private Microsoft.Office.Interop.Excel.Font m_Font;//当前单元格的字体属性对象
private Microsoft.Office.Interop.Excel.Borders m_Borders;//当前单元格或者区域的边框属性对象
//单元格的四条边框对象
private Microsoft.Office.Interop.Excel.Border m_BorderTop;
private Microsoft.Office.Interop.Excel.Border m_BorderBottom;
private Microsoft.Office.Interop.Excel.Border m_BorderLeft;
private Microsoft.Office.Interop.Excel.Border m_BorderRight;
private Microsoft.Office.Interop.Excel.Range m_cellRange;//单元格Range对象，用来取得对象的Rows和Columns属性对象
//单元格列号数组
private string[] m_colString = new string[26] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
public enum eHorizontal
{
eLeft = 0,
eRight = 1,
eCenter = 2
}
#endregion
public ExportBaseExcel()
{
m_objExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Microsoft.Office.Interop.Excel.Workbook)(m_objBooks.Add(miss));
m_objSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet;
}
~ExportBaseExcel()
{
//释放所有Com对象
if (m_cellRange != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_cellRange);
if (m_BorderTop != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderTop);
if (m_BorderBottom != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderBottom);
if (m_BorderLeft != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderLeft);
if (m_BorderRight != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderRight);
if (m_Borders != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Borders);
if (m_Font != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Font);
if (m_Range != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Range);
if (m_objSheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
if (m_objBook != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
if (m_objBooks != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
if (m_objExcel != null) //m_objExcel.Quit();
{
//m_objExcel.Quit();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
GC.Collect();
}
#region 选定单元格
private string GetCell(int ColNum, int RowNum)
{
int row = RowNum + 1;
if (ColNum < 0 || ColNum > 255)
{
throw new Exception("行号错误");
}
int i0, i1 = 0;
i0 = Math.DivRem(ColNum, 25, out i1);
if (i0 == 0 && i1 == 0)
{
return "A" + row.ToString();
}
else if (i0 == 0 && i1 > 0)
{
return m_colString[i1] + row.ToString();
}
else
{
return m_colString[i0] + m_colString[i1] + row.ToString();
}
}
///
/// 选定相应单元格
///
/// int 列号
/// int 行号
public void SetRange(int ColNum, int RowNum)
{
m_Range = m_objSheet.get_Range((object)GetCell(ColNum, RowNum), miss);
m_Font = m_Range.Font;
m_Borders = m_Range.Borders;
m_BorderTop = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop];
m_BorderBottom = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom];
m_BorderLeft = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft];
m_BorderRight = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight];
m_cellRange = m_Range;
//m_Range.NumberFormatLocal = "@";
}
public void SetRange(int ColNum, int RowNum,object oFormat)
{
m_Range = m_objSheet.get_Range((object)GetCell(ColNum, RowNum), miss);
m_Font = m_Range.Font;
m_Borders = m_Range.Borders;
m_BorderTop = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop];
m_BorderBottom = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom];
m_BorderLeft = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft];
m_BorderRight = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight];
m_cellRange = m_Range;
//m_Range.NumberFormatLocal = oFormat.ToString();
m_Range.Areas[0].NumberFormat = "[红色]-0.00";
}
///
/// 选择相应的区域
///
/// 起始单元格列号
/// 起始单元格行号
/// 结束单元格列号
/// 结束单元格行号
public void SetRange(int startColNum, int startRowNum, int endColNum, int endRowNum)
{
m_Range = m_objSheet.get_Range((object)GetCell(startColNum, startRowNum), (object)GetCell(endColNum, endRowNum));
m_Font = m_Range.Font;
m_Borders = m_Range.Borders;
m_BorderTop = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop];
m_BorderBottom = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom];
m_BorderLeft = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft];
m_BorderRight = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight];
m_cellRange = m_Range;
}
public void SetRange(int startColNum, int startRowNum, int endColNum, int endRowNum, object oFormat)
{
m_Range = m_objSheet.get_Range((object)GetCell(startColNum, startRowNum), (object)GetCell(endColNum, endRowNum));
m_Font = m_Range.Font;
m_Borders = m_Range.Borders;
m_BorderTop = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop];
m_BorderBottom = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom];
m_BorderLeft = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft];
m_BorderRight = m_Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight];
m_cellRange = m_Range;
//m_cellRange.NumberFormat = "0.000%";
if (oFormat == null)
{
m_cellRange.NumberFormat = "@";
}
else
{
m_cellRange.NumberFormat = oFormat.ToString();
}
}
#endregion
//开始具体的Excel操作
#region 给单元格附值
///
/// 给选定单元格附值
///
/// 值
public void SetCellValue(string value)
{
if (m_Range == null) throw new System.Exception("没有设定单元格或者区域");
m_Range.Value2 = value;
}
///
/// 给选定单元格附值
///
/// 列号
/// 行号
/// 值
public void SetCellValue(int row, int col, string value)
{
SetRange(col, row);
m_Range.Value2 = value;
}
///
/// 合并选定区域后给其附值
///
/// 起始行号
/// 起始列号
/// 结束行号
/// 结束列号
/// 值
public void SetCellValue(int startRow, int startCol, int endRow, int endCol, string value)
{
Merge(startRow, startCol, endRow, endCol);
m_Range.Value2 = value;
}
#endregion
#region 设定单元格对齐方式
///
/// 设定单元格中文字的对齐方式
///
/// 对齐方式
public void SetHorizontal(eHorizontal eH)
{
if (m_Range == null) throw new System.Exception("没有设定单元格或者区域");
switch (eH)
{
case eHorizontal.eLeft:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
break;
case eHorizontal.eRight:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
break;
case eHorizontal.eCenter:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
break;
default:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
break;
}
}
///
/// 设定单元格中文字的对齐方式
///
/// 单元格行号
/// 单元格列号
/// 对齐方式
public void SetHorizontal(int rowIndex, int columnIndex, eHorizontal eH)
{
SetRange(columnIndex, rowIndex);
switch (eH)
{
case eHorizontal.eLeft:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
break;
case eHorizontal.eRight:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
break;
case eHorizontal.eCenter:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
break;
default:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
break;
}
}
///
/// 设定选定区域的对齐方式
///
/// 起始行号
/// 起始列号
/// 结束行号
/// 结束列号
/// 对齐方式
public void SetHorizontal(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, eHorizontal eH)
{
SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
switch (eH)
{
case eHorizontal.eLeft:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
break;
case eHorizontal.eRight:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
break;
case eHorizontal.eCenter:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
break;
default:
m_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
break;
}
}
#endregion
#region 设置行高和列宽
///
/// 设置列宽
///
/// 列宽度
public void SetColumnWidth(float columnWidth)
{
m_Range.ColumnWidth = columnWidth;
}
///
/// 设置列宽
///
/// 列号
/// 列宽度
public void SetColumnWidth(int columnIndex, float columnWidth)
{
SetRange(columnIndex, 0);
m_Range.ColumnWidth = columnWidth;
}
///
/// 设置行高
///
/// 行宽度
public void SetRowHeigh(float rowHeigh)
{
m_Range.RowHeight = rowHeigh;
}
///
/// 设置行高
///
/// 行号
/// 行宽度
public void SetRowHeigh(int rowIndex, float rowHeigh)
{
SetRange(0, rowIndex);
m_Range.RowHeight = rowHeigh;
}
#endregion
#region 合并单元格
///
/// 将选定区域中的单元格合并
///
public void Merge()
{
m_Range.Merge(null);
}
///
/// 将选定区域中的单元格合并
///
/// 起始行号
/// 起始列号
/// 结束行号
/// 结束列号
public void Merge(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
{
SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
m_Range.Merge(null);
}
#endregion
#region 设置字体名称、大小
///
/// 设置区域内的字体
///
/// 起始行号
/// 起始列号
/// 结束行号
/// 结束列号
/// 字体名称
public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, string fontName)
{
SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
m_Font.Name = fontName;
}
///
/// 设置区域内的字号（文字大小）
///
/// 起始行号
/// 起始列号
/// 结束行号
/// 结束列号
/// 字号
public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, int fontSize)
{
SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
m_Font.Size = fontSize;
}
///
/// 设置区域内的字体以及字号
///
/// 起始行号
/// 起始列号
/// 结束行号
/// 结束列号
/// 字体名称
/// 字号
public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, string fontName, int fontSize)
{
SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
m_Font.Name = fontName;
m_Font.Size = fontSize;
}
///
/// 设置单元格的字体和字号
///
/// 行号
/// 列号
/// 字体
/// 字号
public void SetFont(int rowIndex, int columnIndex, string fontName, int fontSize)
{
SetRange(columnIndex, rowIndex);
m_Font.Name = fontName;
m_Font.Size = fontSize;
}
///
/// 设置单元格的字体
///
/// 行号
/// 列号
/// 字体
public void SetFont(int rowIndex, int columnIndex, string fontName)
{
SetRange(columnIndex, rowIndex);
m_Font.Name = fontName;
}
///
/// 设置单元格的字号
///
/// 行号
/// 列号
/// 字号
public void SetFont(int rowIndex, int columnIndex, int fontSize)
{
SetRange(columnIndex, rowIndex);
m_Font.Size = fontSize;
}
///
/// 设定字体
///
/// 字体
public void SetFont(string fontName)
{
m_Font.Name = fontName;
}
#endregion
#region 具体操作
public void UserControl(bool usercontrol)
{
if (m_objExcel == null) { return; }
m_objExcel.UserControl = usercontrol;
m_objExcel.DisplayAlerts = usercontrol;
m_objExcel.Visible = usercontrol;
}
public void SaveAs(string FileName)
{
m_objBook.SaveAs(FileName, miss, miss, miss, miss,
miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges,
miss, miss, miss, miss);
//m_objBook.Close(false, miss, miss);
}
public void ReleaseExcel()
{
if (m_cellRange != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_cellRange);
if (m_BorderTop != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderTop);
if (m_BorderBottom != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderBottom);
if (m_BorderLeft != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderLeft);
if (m_BorderRight != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderRight);
if (m_Borders != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Borders);
if (m_Font != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Font);
if (m_Range != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Range);
if (m_objSheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
if (m_objBook != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
if (m_objBooks != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
if (m_objExcel != null)
{
//System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
m_objExcel.Quit();
}
GC.Collect();
}
public void OpenExcelFile(string filename)
{
if(m_objExcel !=null)
{ m_objExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();}
UserControl(false);
m_objExcel.Workbooks.Open(
filename,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss);
m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = m_objExcel.ActiveWorkbook;
m_objSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet;
}
#endregion
#region 读取Excel单元格的文本 和 值
/// <summary>
/// 读取Excel单元格的文本
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <returns></returns>
public object GetText(int row, int col)
{
if (m_objSheet != null)
{
return m_objSheet.get_Range((object)GetCell(col, row), miss).Text;
}
else
{
return "";
}
}
/// <summary>
/// 读取Excel单元格的值
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <returns></returns>
public object GetValue(int row, int col)
{
if (m_objSheet != null)
{
return m_objSheet.get_Range((object)GetCell(col, row), miss).Value2;
}
else
{
return "";
}
}
}
}
浙公网安备 33010602011771号