下面的程序,是自己经常用到的excel操作类,本程序与别的对excel操作唯一改进的地方是对excel数据的填充方式上。
网上经常看到的填充方式有:
1:按单元格
2:按区域(range)
本程序,也还是按区域来填充的,只需要参数是一个datatable就行了
using
System;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
namespace KernelCore.Utility.Excel2007
{
#region 操作Excle应用程序类
/*
//调用例子:
Excel._Application spExcelApp=new Excel.ApplicationClass();
//模板文件
Excel._Workbook spMouleBook = CExcelApp.OpenExcel(spExcelApp,"c:/Empty.xls");
// Excel._Worksheet spMouleSheet=CExcelApp.GetSheetItem(spMouleBook,1);
//输出文件
Excel._Workbook spOutBook =CExcelApp.AddSheetPage(spExcelApp);
Excel._Worksheet spOutSheet =CExcelApp.GetSheetItem(spOutBook,1);
Excel._Worksheet spOutSheet1 =CExcelApp.GetSheetItem(spOutBook,2);
spOutSheet.Name="SheetName";
//更新所有列宽
//CExcelApp.SetColumnWidth(spMouleSheet,1,spOutSheet,1,11);
//整行拷贝
//Excel.Range spSou = CExcelApp.GetRangeByRow(spMouleSheet,1,1000);
//Excel.Range spDes =CExcelApp.GetRangeByRow(spOutSheet,1);
//CExcelApp.CopyRange(spSou,spDes);
object spRgBeg = CExcelApp.GetRange(spOutSheet,1,1);
object spRgEnd = CExcelApp.GetRange(spOutSheet,60000,18);
spOutSheet.get_Range(spRgBeg,spRgEnd).set_Value(System.Reflection.Missing.Value,CExcelApp.GetRangeDataArr(GetJsTable()));
object spRgBeg1 = CExcelApp.GetRange(spOutSheet1,1,1);
object spRgEnd1 = CExcelApp.GetRange(spOutSheet1,60000,18);
spOutSheet1.get_Range(spRgBeg1,spRgEnd1).set_Value(System.Reflection.Missing.Value,CExcelApp.GetRangeDataArr(GetJsTable()));
//CExcelApp.Show(spExcelApp,true); //是否显示Excel
CExcelApp.SaveAs(spOutBook,"d:/z.xls");
CExcelApp.CloseBook(spMouleBook,false);
CExcelApp.CloseBook(spOutBook,false);
CExcelApp.ReleaseSheet(spOutSheet);
CExcelApp.ReleaseSheet(spOutSheet1);
CExcelApp.ReleaseBook(spOutBook);
CExcelApp.ReleaseBook(spMouleBook);
CExcelApp.ReleaseExcelApp(spExcelApp);
GC.Collect();
*/
/// <summary>
/// CExcelComm 的摘要说明。
/// </summary>
public class CExcelApp
{
/// <summary>
/// 创建Excel相关对象
/// </summary>
static public Excel._Workbook OpenExcel(Excel._Application spExcelApp, string strFilePath)
{
try
{
if (strFilePath == "" )
return null ;
Object vtMissing = System.Reflection.Missing.Value;
if (spExcelApp == null )
{
return null ;
}
Excel._Workbook m_spWorkBook = spExcelApp.Workbooks.Open(strFilePath, vtMissing, vtMissing, vtMissing,
vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,
vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, 0 );
return m_spWorkBook;
}
catch (Exception ex)
{
string strError = ex.ToString().Trim();
return null ;
}
}
/// <summary>
/// 是否显示Excel
/// </summary>
static public void Show(Excel._Application pApp, bool bShow)
{
if (pApp == null ) return ;
pApp.Visible = bShow ? true : false ;
}
/// <summary>
/// 根据Sheet的Index得到WorkSheet对象
/// </summary>
static public Excel._Worksheet GetSheetItem(Excel._Workbook spWorkBook, int iIndex)
{
try
{
if (spWorkBook == null )
{
return null ;
}
return (Excel._Worksheet)spWorkBook.Sheets.get_Item(iIndex);
}
catch (Exception ex)
{
string strError = ex.ToString().Trim();
return null ;
}
}
/// <summary>
/// 得到打开Excel模板的Sheet个数
/// </summary>
static public int GetSheetCount(Excel._Workbook spWorkBook)
{
try
{
if (spWorkBook == null )
{
return - 1 ;
}
return spWorkBook.Sheets.Count;
}
catch (Exception ex)
{
string strError = ex.ToString().Trim();
return - 1 ;
}
}
/// <summary>
/// 从页中获得对应值
/// </summary>
static public bool GetValueFromSheet(Excel._Worksheet pSheet, long nRow, long nCol, ref string sValue)
{
return CExcelApp.GetCellValue(pSheet, nRow, nCol, ref sValue);
// return true;
}
/// <summary>
/// 设置值到指定页上
/// </summary>
using System.Runtime.InteropServices;
using System.Reflection;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
namespace KernelCore.Utility.Excel2007
{
#region 操作Excle应用程序类
/*
//调用例子:
Excel._Application spExcelApp=new Excel.ApplicationClass();
//模板文件
Excel._Workbook spMouleBook = CExcelApp.OpenExcel(spExcelApp,"c:/Empty.xls");
// Excel._Worksheet spMouleSheet=CExcelApp.GetSheetItem(spMouleBook,1);
//输出文件
Excel._Workbook spOutBook =CExcelApp.AddSheetPage(spExcelApp);
Excel._Worksheet spOutSheet =CExcelApp.GetSheetItem(spOutBook,1);
Excel._Worksheet spOutSheet1 =CExcelApp.GetSheetItem(spOutBook,2);
spOutSheet.Name="SheetName";
//更新所有列宽
//CExcelApp.SetColumnWidth(spMouleSheet,1,spOutSheet,1,11);
//整行拷贝
//Excel.Range spSou = CExcelApp.GetRangeByRow(spMouleSheet,1,1000);
//Excel.Range spDes =CExcelApp.GetRangeByRow(spOutSheet,1);
//CExcelApp.CopyRange(spSou,spDes);
object spRgBeg = CExcelApp.GetRange(spOutSheet,1,1);
object spRgEnd = CExcelApp.GetRange(spOutSheet,60000,18);
spOutSheet.get_Range(spRgBeg,spRgEnd).set_Value(System.Reflection.Missing.Value,CExcelApp.GetRangeDataArr(GetJsTable()));
object spRgBeg1 = CExcelApp.GetRange(spOutSheet1,1,1);
object spRgEnd1 = CExcelApp.GetRange(spOutSheet1,60000,18);
spOutSheet1.get_Range(spRgBeg1,spRgEnd1).set_Value(System.Reflection.Missing.Value,CExcelApp.GetRangeDataArr(GetJsTable()));
//CExcelApp.Show(spExcelApp,true); //是否显示Excel
CExcelApp.SaveAs(spOutBook,"d:/z.xls");
CExcelApp.CloseBook(spMouleBook,false);
CExcelApp.CloseBook(spOutBook,false);
CExcelApp.ReleaseSheet(spOutSheet);
CExcelApp.ReleaseSheet(spOutSheet1);
CExcelApp.ReleaseBook(spOutBook);
CExcelApp.ReleaseBook(spMouleBook);
CExcelApp.ReleaseExcelApp(spExcelApp);
GC.Collect();
*/
/// <summary>
/// CExcelComm 的摘要说明。
/// </summary>
public class CExcelApp
{
/// <summary>
/// 创建Excel相关对象
/// </summary>
static public Excel._Workbook OpenExcel(Excel._Application spExcelApp, string strFilePath)
{
try
{
if (strFilePath == "" )
return null ;
Object vtMissing = System.Reflection.Missing.Value;
if (spExcelApp == null )
{
return null ;
}
Excel._Workbook m_spWorkBook = spExcelApp.Workbooks.Open(strFilePath, vtMissing, vtMissing, vtMissing,
vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,
vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, 0 );
return m_spWorkBook;
}
catch (Exception ex)
{
string strError = ex.ToString().Trim();
return null ;
}
}
/// <summary>
/// 是否显示Excel
/// </summary>
static public void Show(Excel._Application pApp, bool bShow)
{
if (pApp == null ) return ;
pApp.Visible = bShow ? true : false ;
}
/// <summary>
/// 根据Sheet的Index得到WorkSheet对象
/// </summary>
static public Excel._Worksheet GetSheetItem(Excel._Workbook spWorkBook, int iIndex)
{
try
{
if (spWorkBook == null )
{
return null ;
}
return (Excel._Worksheet)spWorkBook.Sheets.get_Item(iIndex);
}
catch (Exception ex)
{
string strError = ex.ToString().Trim();
return null ;
}
}
/// <summary>
/// 得到打开Excel模板的Sheet个数
/// </summary>
static public int GetSheetCount(Excel._Workbook spWorkBook)
{
try
{
if (spWorkBook == null )
{
return - 1 ;
}
return spWorkBook.Sheets.Count;
}
catch (Exception ex)
{
string strError = ex.ToString().Trim();
return - 1 ;
}
}
/// <summary>
/// 从页中获得对应值
/// </summary>
static public bool GetValueFromSheet(Excel._Worksheet pSheet, long nRow, long nCol, ref string sValue)
{
return CExcelApp.GetCellValue(pSheet, nRow, nCol, ref sValue);
// return true;
}
/// <summary>
/// 设置值到指定页上
/// </summary>