using System;
using System.Collections.Generic;
using System.Text;
using Excel;
using System.IO;
using System.Reflection;
/// <summary>
/// Excel 操作类，用来根据模板导出复杂Excel
/// 例：MyExcel excel = new MyExcel();
/// excel.TempFile = "模板文件";
/// excel.OutputFile = "输出文件";
/// excel.CopyTempFile();//拷贝临时文件，写数据
/// excel.OpenExecl();//打开临时文件
/// excel.Save();//保存输出文件
/// excel.Close();//关闭Excel
///
/// .......................huwei
/// </summary>
public class MyExcel
{
#region 属性
private string _tempFile;
/// <summary>
/// 模板文件(全路径)
/// </summary>
public string TempFile
{
get { return _tempFile; }
set { _tempFile = value; }
}
private string _outputFile;
/// <summary>
/// 输出文件(全路径)
/// </summary>
public string OutputFile
{
get { return _outputFile; }
set { _outputFile = value; }
}
#endregion
object missing = Missing.Value;
/// <summary>
/// Excel实例
/// </summary>
public Excel.Application _excel ;
private string _tempFileNme = "";//临时文件，为了释放模板文件
private Excel.Workbook _workBook = null;
/// <summary>
/// 当前操作的sheet
/// </summary>
private Excel.Worksheet _sheet = null;//当前操作的sheet
/// <summary>
/// Excel操作
/// </summary>
public MyExcel()
{
_excel = new Application();
}
/// <summary>
/// Excel操作
/// </summary>
/// <param name="tempFile">模板文件</param>
/// <param name="outputFile">输出文件</param>
public MyExcel(string tempFile, string outputFile):this()
{
this.TempFile = tempFile;
this.OutputFile = outputFile;
}
/// <summary>
/// 拷贝一个输出模板
/// </summary>
/// <returns></returns>
public bool CopyTempFile()
{
try
{
FileInfo f = new FileInfo(OutputFile);
_tempFileNme = f.Directory + "\\" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";//临时文件
if (!File.Exists(TempFile))
{
return false;
}
if (File.Exists(_tempFileNme))
{
//删除原有的文件
File.Delete(_tempFileNme);
}
File.Copy(TempFile, _tempFileNme);
return true;
}
catch { return false; }
}
/// <summary>
/// 打开excel输出文件，准备写入数据
/// </summary>
public void OpenExecl()
{
_workBook = _excel.Workbooks.Open(_tempFileNme, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
_excel.Visible = false;
if (_sheet == null)
{
_sheet = ((Excel.Worksheet)_excel.Worksheets.get_Item(1));//默认第一个,注意 索引从1开始
}
}
/// <summary>
/// 设置下面的工作在哪个sheet中完成，索引从1开始
/// </summary>
/// <param name="index"></param>
public void SetSheet(int index)
{
_sheet = ((Excel.Worksheet)_excel.Worksheets.get_Item(index));
}
/// <summary>
/// 设置下面的工作在哪个sheet中完成
/// </summary>
/// <param name="sheetName"></param>
public void SetSheet(string sheetName)
{
_sheet = ((Excel.Worksheet)_excel.Worksheets[sheetName]);
}
/// <summary>
/// 保存
/// </summary>
public void Save()
{
if (File.Exists(OutputFile))
{
File.Delete(OutputFile);
}
//_excel.SaveWorkspace(OutputFile);
_workBook.SaveAs(OutputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing);
}
/// <summary>
/// 关闭excel，释放资源
/// </summary>
public void Close()
{
_excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(_excel);
_excel = null;
GC.Collect();
try
{
File.Delete(_tempFileNme);
}
catch { }
}
/// <summary>
/// 设置sheet名称
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="sheetName"></param>
public void SetSheetName(int sheetIndex,string sheetName)
{
Excel.Worksheet sheet = ((Excel.Worksheet)_excel.Worksheets.get_Item(sheetIndex));
sheet.Name = sheetName;
}
/// <summary>
/// 添加一个sheet并设置sheet名称
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public bool AddASheet(string sheetName)
{
_excel.Sheets.Add(missing, _excel.Sheets.get_Item(_excel.Sheets.Count), 1, missing);
SetSheetName(_excel.Sheets.Count, sheetName);
return false;
}
/// <summary>
/// 获取一个sheet
/// </summary>
/// <param name="sheetIndex"></param>
/// <returns></returns>
public Excel.Worksheet GetSheet(int sheetIndex)
{
return ((Excel.Worksheet)_excel.Worksheets.get_Item(sheetIndex));
}
/// <summary>
/// 获取一个sheet
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public Excel.Worksheet GetSheet(string sheetName)
{
return ((Excel.Worksheet)_excel.Worksheets[sheetName]);
}
public void CopySheet(int fromIndex,string toName)
{
((Excel.Worksheet)_excel.Worksheets.get_Item(fromIndex)).Copy(missing, _excel.Worksheets[_excel.Worksheets.Count]);
SetSheetName(_excel.Worksheets.Count, toName);
}
public void WriteOneCell(int row, int col, string val)
{
_sheet.Cells[row, col] = val;
}
public void WriteOneCell(int row, int col, string val,int sheetIndex)
{
GetSheet(sheetIndex).Cells[row, col] = val;
}
/// <summary>
/// 拷贝一个区域到另一个区域
/// </summary>
/// <param name="fromCell1">开始单元格(A1)</param>
/// <param name="fromCell2">结束单元格(U9)</param>
/// <param name="toCell1">目标开始单元格(A20)</param>
/// <param name="toCell2">目标结束单元格(U29)</param>
public void CopyRange(string fromCell1, string fromCell2, string toCell1, string toCell2)
{
_sheet.get_Range(fromCell1, fromCell2).Copy(_sheet.get_Range(toCell1, toCell2));
}
/// <summary>
/// 指定行上 插入 N行
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="rowCount"></param>
public void InsertRows(int rowIndex,int rowCount)
{
Excel.Range range = (Excel.Range)_sheet.Rows[rowIndex, missing];
for (int i = 0; i < rowCount; i++)
{
range.Insert(Excel.XlDirection.xlDown);
}
}
/// <summary>
/// 拷贝一个区域到另外一个区域，持跨sheet
/// </summary>
/// <param name="fromSheetIndex"></param>
/// <param name="fromCell1"></param>
/// <param name="fromCell2"></param>
/// <param name="toSheetIndex"></param>
/// <param name="toCell1"></param>
/// <param name="toCell2"></param>
public void CopyRange(int fromSheetIndex, string fromCell1, string fromCell2, int toSheetIndex, string toCell1, string toCell2)
{
GetSheet(fromSheetIndex).get_Range(fromCell1, fromCell2).Copy(GetSheet(toSheetIndex).get_Range(toCell1, toCell2));
}
/// <summary>
/// 根据已有的区域(模板内容)生成二维数组
/// </summary>
/// <param name="startCell"></param>
/// <param name="endCell"></param>
/// <returns></returns>
public object[,] GetRangeArray(string startCell,string endCell)
{
//Console.WriteLine(GetRange(startCell, endCell).Cells.Count);
//Console.WriteLine(GetRange(startCell, endCell).Rows.Count);
//Console.WriteLine(GetRange(startCell, endCell).Columns.Count);
int rowCount = GetRange(startCell, endCell).Rows.Count;
int columnCount = GetRange(startCell, endCell).Columns.Count;
object[,] datas = new object[rowCount, columnCount];
Excel.Range range = GetRange(startCell, endCell);
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
datas[i, j] = ((Excel.Range)range.Cells[i + 1, j + 1]).Text;
}
}
return datas;
}
public Excel.Range GetRange(string cell1,string cell2)
{
return _sheet.get_Range(cell1, cell2);
}
public void Test()
{
}
}
Excel.dll下载地址:http://download.csdn.net/source/2980437
- 本文标题： ASP.NET Excel操作class类
- 文章分类：【.NET/Web】
- 非特殊说明，本文版权归【胡同里的砖头】个人博客 所有，转载请注明出处.