using System;
using System.Collections.Generic;
using System.Text;
#endregion
namespace CommonUtilities.Office
{
/// <summary>
/// Excel操作接口
/// </summary>
public interface IExcel
{
#region 为处理Excel中图片临时创建的文件夹的绝对路径
/// <summary>
/// 为处理Excel中图片临时创建的文件夹的绝对路径
/// </summary>
string TempFolderPath
{
get;
}
#endregion
#region 读取Excel单元格的文本内容
/// <summary>
/// 读取Excel单元格的文本内容
/// </summary>
/// <param name="sheetName">工作表,如"sheet1"</param>
/// <param name="position">单元格的位置,如"A1"</param>
string ReadText( string sheetName, string position );
#endregion
#region 读取Excel单元格的图片内容
/// <summary>
/// 读取Excel文件中的图片内容,读取成功返回true,该方法只支持Excel文件中仅有一个图片的情况。
/// </summary>
/// <param name="sheetName">工作表,如"sheet1"</param>
/// <param name="image">读出的图片二进制流</param>
bool ReadImage( string sheetName, out byte[] image );
#endregion
#region 将文本写入Excel单元格
/// <summary>
/// 将文本写入Excel单元格
/// </summary>
/// <param name="sheetName">工作表,如"sheet1"</param>
/// <param name="position">单元格的位置,如"A1"</param>
/// <param name="text">写入的文本</param>
void WriteText( string sheetName, string position, string text );
/// <summary>
/// 将文本写入Excel单元格
/// </summary>
/// <param name="sheetName">工作表,如"sheet1"</param>
/// <param name="rowIndex">单元格的行索引,如5</param>
/// <param name="columnIndex">单元格的列索引,如2</param>
/// <param name="text">写入的文本</param>
void WriteText( string sheetName, int rowIndex, int columnIndex, string text );
#endregion
#region 将图片写入Excel单元格
/// <summary>
/// 将图片写入Excel单元格
/// </summary>
/// <param name="sheetName">工作表,如"sheet1"</param>
/// <param name="position">单元格的位置,如"A1"</param>
/// <param name="image">写入的图片二进制流</param>
/// <param name="imageWidth">图片宽度</param>
/// <param name="imageHeight">图片高度</param>
void WriteImage( string sheetName, string position,byte[] image,float imageWidth,float imageHeight );
/// <summary>
/// 将图片写入Excel单元格
/// </summary>
/// <param name="sheetName">工作表,如"sheet1"</param>
/// <param name="position">单元格的位置,如"A1"</param>
/// <param name="imagePath">待写入图片的绝对路径</param>
/// <param name="imageWidth">图片宽度</param>
/// <param name="imageHeight">图片高度</param>
void WriteImage( string sheetName, string position, string imagePath, float imageWidth, float imageHeight );
#endregion
#region 保存文件
/// <summary>
/// 保存文件
/// </summary>
void Save();
/// <summary>
/// 保存文件
/// </summary>
/// <param name="filePath">文件保存的绝对路径</param>
void Save( string filePath );
#endregion
#region 解除保护
/// <summary>
/// 解除保护
/// </summary>
/// <param name="sheetName">要读取的Excel页,如"sheet1"</param>
/// <param name="password">密码</param>
void Unprotect( string sheetName, string password );
#endregion
#region 关闭Excel操作对象
/// <summary>
/// 关闭Excel操作对象
/// </summary>
void Close();
#endregion
}
}
/** 1. 功能:操作Excel2003的类
* 2. 作者:何平
* 3. 创建日期:2008-7-23
* 4. 最后修改日期:2008-8-1
**/
#region 命名空间引用
using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.IO;
using Excel;
using System.Windows.Forms;
using System.Drawing;
using System.Drawing.Imaging;
using Microsoft.Office.Core;
#endregion
namespace CommonUtilities.Office
{
/// <summary>
/// 操作Excel2003的类
/// </summary>
public class Excel2003Helper : IExcel
{
#region 字段定义
/// <summary>
/// 要操作的Excel文件的全路径
/// </summary>
private string _filePath;
/// <summary>
/// 要操作的Excel文件的名字
/// </summary>
private string _fileName;
/// <summary>
/// Excel操作对象,设置为静态变量的原因是在批量操作时只实例化一次,以提高性能
/// </summary>
private static Excel.Application _excel;
/// <summary>
/// Excel工作表,即Excel文件。
/// </summary>
private Workbook _workbook;
/// <summary>
/// 临时目录的绝对路径
/// </summary>
private string _tempFolderPath = SysHelper.GetPhysicalPath( @"~/TempFolder" );
#endregion
#region 静态构造函数
static Excel2003Helper()
{
//创建Excel操作对象
_excel = new Excel.Application();
_excel.Visible = false;
}
#endregion
#region 构造函数
/// <summary>
/// 创建操作Excel的公共类实例
/// </summary>
/// <param name="filePath">要操作的Excel文件的相对路径 </param>
public Excel2003Helper( string filePath )
{
//为字段赋值
this._filePath = SysHelper.GetPhysicalPath( filePath );
try
{
//获取工作表,即Excel文件。
_workbook = _excel.Workbooks.Add( _filePath );
this._fileName = _workbook.Name;
}
catch ( Exception ex )
{
LogHelper.WriteTraceLog( TraceLogLevel.Error, ex.Message );
throw ex;
}
}
#endregion
#region 为处理Excel中图片临时创建的文件夹的绝对路径
/// <summary>
/// 为处理Excel中图片临时创建的文件夹的绝对路径
/// </summary>
public string TempFolderPath
{
get
{
return _tempFolderPath;
}
}
#endregion
#region 读取Excel单元格的文本内容
/// <summary>
/// 读取Excel单元格的文本内容
/// </summary>
/// <param name="sheetName">Excel页,如"sheet1"</param>
/// <param name="position">单元格的位置,如"A1"</param>
public string ReadText( string sheetName, string position )
{
try
{
//获取指定工作表
Worksheet sheet = _workbook.Worksheets.get_Item( sheetName ) as Worksheet;
//获取工作区
Range range = sheet.get_Range( position, Type.Missing ) as Range;
//读取文本
return range.Text.ToString();
}
catch ( Exception ex )
{
Close();
//抛出异常
throw ex;
}
}
#endregion
#region 读取Excel文件中的图片内容
/// <summary>
/// 读取Excel文件中的图片内容,读取成功返回true,该方法只支持Excel文件中仅有一个图片的情况。
/// </summary>
/// <param name="sheetName">Excel页,如"sheet1"</param>
/// <param name="image">读取的图片二进制流</param>
public bool ReadImage( string sheetName, out byte[] image )
{
if ( BaseInfo.AppType == AppType.CS )
{
return ReadImage_CS( sheetName, out image );
}
else
{
return ReadImage_BS( sheetName, out image );
}
}
#region 在windows Form中读取Excel图片
/// <summary>
/// 在windows Form中读取Excel图片
/// </summary>
/// <param name="sheetName">要读取的Excel页,如"sheet1"</param>
/// <param name="image">读取的图片二进制流</param>
private bool ReadImage_CS( string sheetName, out byte[] image )
{
try
{
//获取指定工作表
Worksheet sheet = _workbook.Worksheets.get_Item( sheetName ) as Worksheet;
//查找Excel文件中是否存在图片
if ( sheet.Shapes.Count == 0 )
{
//未找到图片
image = new byte[] { };
return false;
}
else
{
//获取Excel文件中的第一个图片
Excel.Shape pic = sheet.Shapes.Item( 1 ) as Excel.Shape;
//将图片copy到剪贴板
pic.CopyPicture( XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap );
//创建PictureBox对象,暂存图片
PictureBox pb = new PictureBox();
pb.Image = (Image)Clipboard.GetData( DataFormats.Bitmap );
//使用PictureBox将剪贴板中的数据写到内存流
using ( MemoryStream ms = new MemoryStream() )
{
pb.Image.Save( ms, ImageFormat.Bmp );
image = ms.ToArray();
}
return true;
}
}
catch ( Exception ex )
{
Close();
//抛出异常
throw ex;
}
}
#endregion
#region 在Web Form中读取Excel图片
/// <summary>
/// 在Web Form中读取Excel图片
/// </summary>
/// <param name="sheetName">要读取的Excel页,如"sheet1"</param>
/// <param name="image">读取的图片二进制流</param>
private bool ReadImage_BS( string sheetName, out byte[] image )
{
try
{
//获取指定工作表
Worksheet sheet = _workbook.Worksheets.get_Item( sheetName ) as Worksheet;
//创建临时目录
CreateTempFolder();
//将该Excel另存为HTML文件,并保存在指定临时目录中
StringBuilder htmlPath = new StringBuilder();
htmlPath.AppendFormat( @"{0}/{1}.htm", _tempFolderPath, _fileName );
//sheet.Unprotect( "123" );
sheet.SaveAs( htmlPath.ToString(), XlFileFormat.xlHtml, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );
//获取存储图片的文件夹
StringBuilder imageFolder = new StringBuilder();
imageFolder.AppendFormat( @"{0}/{1}.files", _tempFolderPath, _fileName );
//获取图片的路径
if ( FileHelper.Contains( imageFolder.ToString(), "*.jpg" ) )
{
//由于一个图片会产生两张图片,选择容量大的那个图片
string imagePath = FileHelper.GetFileNames( imageFolder.ToString(), "*.jpg", false )[0];
//获取图片
image = FileHelper.FileToBytes( imagePath );
return true;
}
else
{
//获取图片失败
image = new byte[] { };
return false;
}
}
catch ( Exception ex )
{
Close();
//抛出异常
throw ex;
}
}
#region 创建临时目录
/// <summary>
/// 创建临时目录
/// </summary>
private void CreateTempFolder()
{
//创建临时目录
if ( !FileHelper.IsExistDirectory( _tempFolderPath ) )
{
FileHelper.CreateDirectory( _tempFolderPath );
}
//如果临时目录非空则清空
if ( !FileHelper.IsEmptyDirectory( _tempFolderPath ) )
{
FileHelper.ClearDirectory( _tempFolderPath );
}
}
#endregion
#endregion
#endregion
#region 将文本写入Excel单元格
/// <summary>
/// 将文本写入Excel单元格
/// </summary>
/// <param name="sheetName">Excel页,如"sheet1"</param>
/// <param name="position">单元格的位置,如"A1"</param>
/// <param name="text">写入的文本</param>
public void WriteText( string sheetName, string position, string text )
{
//获取指定工作表
Worksheet sheet = _workbook.Worksheets.get_Item( sheetName ) as Worksheet;
//获取工作区
Range range = sheet.get_Range( position, Type.Missing ) as Range;
//将文本写入Excel单元格
range.set_Value( XlRangeValueDataType.xlRangeValueDefault, text );
}
/// <summary>
/// 将文本写入Excel单元格
/// </summary>
/// <param name="sheetName">Excel页,如"sheet1"</param>
/// <param name="rowIndex">单元格的行索引,如5</param>
/// <param name="columnIndex">单元格的列索引,如2</param>
/// <param name="text">写入的文本</param>
public void WriteText( string sheetName, int rowIndex, int columnIndex, string text )
{
//获取指定工作表
Worksheet sheet = _workbook.Worksheets.get_Item( sheetName ) as Worksheet;
//将文本写入Excel单元格
sheet.Cells[rowIndex, columnIndex] = text;
}
#endregion
#region 将图片写入Excel单元格
/// <summary>
/// 将图片写入Excel单元格
/// </summary>
/// <param name="sheetName">工作表,如"sheet1"</param>
/// <param name="position">单元格的位置,如"A1"</param>
/// <param name="image">写入的图片二进制流</param>
/// <param name="imageWidth">图片宽度</param>
/// <param name="imageHeight">图片高度</param>
public void WriteImage( string sheetName, string position,byte[] image,float imageWidth,float imageHeight )
{
try
{
//获取指定工作表
Worksheet sheet = _workbook.Worksheets.get_Item( sheetName ) as Worksheet;
//获取工作区的左边距与上边距
Range range = sheet.get_Range( position, Type.Missing ) as Range;
float left = ConvertHelper.ToFloat( range.Left ) + 6;
float top = ConvertHelper.ToFloat( range.Top ) + 6;
//创建临时目录
CreateTempFolder();
//将图片二进制流写入临时目录的文件
string tempFile = this._tempFolderPath + @"/image" + DateTime.Now.ToString( "d" ) + ".jpg";
FileHelper.CreateFile( tempFile, image );
//将图片添加到Excel中
sheet.Shapes.AddPicture( tempFile, MsoTriState.msoFalse, MsoTriState.msoTrue, left, top, imageWidth, imageHeight );
//清空临时目录
FileHelper.ClearDirectory( this._tempFolderPath );
}
catch ( Exception ex )
{
throw ex;
}
}
/// <summary>
/// 将图片写入Excel单元格
/// </summary>
/// <param name="sheetName">工作表,如"sheet1"</param>
/// <param name="position">单元格的位置,如"A1"</param>
/// <param name="imagePath">待写入图片的绝对路径</param>
/// <param name="imageWidth">图片宽度</param>
/// <param name="imageHeight">图片高度</param>
public void WriteImage( string sheetName, string position, string imagePath, float imageWidth, float imageHeight )
{
try
{
//获取指定工作表
Worksheet sheet = _workbook.Worksheets.get_Item( sheetName ) as Worksheet;
//获取工作区的左边距与上边距
Range range = sheet.get_Range( position, Type.Missing ) as Range;
float left = ConvertHelper.ToFloat( range.Left ) + 6;
float top = ConvertHelper.ToFloat( range.Top ) + 6;
//将图片添加到Excel中
sheet.Shapes.AddPicture( imagePath, MsoTriState.msoFalse, MsoTriState.msoTrue, left, top, imageWidth, imageHeight );
}
catch ( Exception ex )
{
throw ex;
}
}
#endregion
#region 保存文件
/// <summary>
/// 保存文件
/// </summary>
public void Save()
{
//删除原文件
FileHelper.DeleteFile( _filePath );
//重新保存
this._workbook.SaveAs( _filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );
}
/// <summary>
/// 保存文件
/// </summary>
/// <param name="filePath">文件保存位置的绝对路径</param>
public void Save( string filePath )
{
this._workbook.SaveAs( filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );
}
#endregion
#region 解除保护
/// <summary>
/// 解除保护
/// </summary>
/// <param name="sheetName">要读取的Excel页,如"sheet1"</param>
/// <param name="password">密码</param>
public void Unprotect( string sheetName, string password )
{
//获取指定工作表
Worksheet sheet = _workbook.Worksheets.get_Item( sheetName ) as Worksheet;
//解除保护
sheet.Unprotect( password );
}
#endregion
#region 关闭Excel操作对象
/// <summary>
/// 关闭Excel操作对象
/// </summary>
public void Close()
{
//关闭工作表
_workbook.Close( false, null, null );
//释放Excel操作对象
_excel.Quit();
}
#endregion
}
}