标签：nal www. 包含 oid 创建时间 添加引用 add alignment 无效
//1.添加引用-〉com-〉microsoft excel 11.0
//2.若出现错误:命名空间“Microsoft.Office”中不存在类型或命名空间名称“Interop”(是缺少程序集引用吗?)
//解决方法：先删除引用中的Excel,然后找到文件Microsoft.Office.Interop.Excel.dll，手动添加该文件的引用
using System;
using System.Data;
using System.Reflection;
using System.IO;
using Microsoft.Office.Core;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace Wage.Common
{
///
/// 作者 Li Aimin (原创)
/// 功能描述：C#对Excel报表进行操作
/// 创建时间：2006－01－17, 修改时间：２００７－１－１4
/// 说明：在工程中需要添加 Excel11.0对象库的引用（Office 2000为Excel9.0，Office XP为Excel10.0）；
/// 需要在Dcom中配置Excel应用程序的权限；
/// 服务器需要安装Office2003
///
public class ExcelLib
{
//http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfexcelapplicationobject.asp
#region Variables
private Excel.Application excelApplication = null;
private Excel.Workbooks excelWorkBooks = null;
private Excel.Workbook excelWorkBook = null;
private Excel.Worksheet excelWorkSheet = null;
private Excel.Range excelRange = null;//Excel Range Object,多种用途
private Excel.Range excelCopySourceRange = null;//Excel Range Object
private int excelActiveWorkSheetIndex; //活动工作表索引
private string excelOpenFileName = ""; //操作Excel的路径
private string excelSaveFileName = ""; //保存Excel的路径
#endregion
#region Properties
public int ActiveSheetIndex
{
get
{
return excelActiveWorkSheetIndex;
}
set
{
excelActiveWorkSheetIndex = value;
}
}
public string OpenFileName
{
get
{
return excelOpenFileName;
}
set
{
excelOpenFileName = value;
}
}
public string SaveFileName
{
get
{
return excelSaveFileName;
}
set
{
excelSaveFileName = value;
}
}
#endregion
//
//--------------------------------------------------------------------------------------------------------
///
/// 构造函数；
///
public ExcelLib()
{
excelApplication = null;//Excel Application Object
excelWorkBooks = null;//Workbooks
excelWorkBook = null;//Excel Workbook Object
excelWorkSheet = null;//Excel Worksheet Object
ActiveSheetIndex = 1; //默认值活动工作簿为第一个；设置活动工作簿请参阅SetActiveWorkSheet()
}
///
/// 以excelOpenFileName为模板新建Excel文件
///
public bool OpenExcelFile()
{
if (excelApplication != null) CloseExcelApplication();
//检查文件是否存在
if (excelOpenFileName == "")
{
throw new Exception("请选择文件！");
}
if (!File.Exists(excelOpenFileName))
{
throw new Exception(excelOpenFileName + "该文件不存在！");//该异常如何处理，由什么处理？？？？
}
try
{
excelApplication = new Excel.ApplicationClass();
excelWorkBooks = excelApplication.Workbooks;
excelWorkBook = ((Excel.Workbook)excelWorkBooks.Open(excelOpenFileName, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets[excelActiveWorkSheetIndex];
excelApplication.Visible = false;
return true;
}
catch (Exception e)
{
CloseExcelApplication();
MessageBox.Show("(1)没有安装Excel 2003；(2)或没有安装Excel 2003 .NET 可编程性支持；\n详细信息："
+e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//throw new Exception(e.Message);
return false;
}
}
///
/// 读取一个Cell的值
///
/// 要读取的Cell的行索引
/// 要读取的Cell的列索引
/// Cell的值
public string getOneCellValue(int CellRowID, int CellColumnID)
{
if (CellRowID
/// 读取一个连续区域的Cell的值(矩形区域，包含一行或一列,或多行，多列)，返回一个一维字符串数组。
///
/// StartCell是要写入区域的左上角单元格
/// EndCell是要写入区域的右下角单元格
/// 值的集合
public string[] getCellsValue(string StartCell, string EndCell)
{
string[] sValue = null;
//try
//{
excelRange = (Excel.Range)excelWorkSheet.get_Range(StartCell, EndCell);
sValue = new string[excelRange.Count];
int rowStartIndex = ((Excel.Range)excelWorkSheet.get_Range(StartCell, StartCell)).Row; //起始行号
int columnStartIndex = ((Excel.Range)excelWorkSheet.get_Range(StartCell, StartCell)).Column; //起始列号
int rowNum = excelRange.Rows.Count; //行数目
int columnNum = excelRange.Columns.Count; //列数目
int index = 0;
for (int i = rowStartIndex; i
/// 读取所有单元格的数据(矩形区域)，返回一个datatable.假设所有单元格靠工作表左上区域。
///
public DataTable getAllCellsValue()
{
int columnCount = getTotalColumnCount();
int rowCount = getTotalRowCount();
DataTable dt = new DataTable();
//设置datatable列的名称
for (int columnID = 1; columnID
/// 当前活动工作表中有效列数(总列数)
///
///
public int getTotalColumnCount()
{
int columnNumber = 0;
try
{
while (true)
{
if (((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 1]).Text.ToString().Trim() == "" &&
((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 2]).Text.ToString().Trim() == "" &&
((Excel.Range)excelWorkSheet.Cells[1, columnNumber + 3]).Text.ToString().Trim() == "")
break;
columnNumber++;
}
}
catch
{
return -1;
}
return columnNumber;
}
///
/// 向一个Cell写入数据
///
/// CellRowID是cell的行索引
/// CellColumnID是cell的列索引
///要写入该单元格的数据值
public void setOneCellValue(int CellRowID, int CellColumnID, string Value)
{
try
{
excelRange = (Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID];
excelRange.Value2 = Value;//Value2?
//Gets or sets the value of the NamedRange control.
//The only difference between this property and the Value property is that Value2 is not a parameterized property.
excelRange = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
///
/// 设置活动工作表
///
/// 要设置为活动工作表的索引值
public void SetActiveWorkSheet(int SheetIndex)
{
if (SheetIndex
/// 向连续区域一次性写入数据；只有在区域连续和写入的值相同的情况下可以使用方法
///
/// StartCell是要写入区域的左上角单元格
/// EndCell是要写入区域的右下角单元格
/// 要写入指定区域所有单元格的数据值
public void setCellsValue(string StartCell, string EndCell, string Value)
{
try
{
excelRange = excelWorkSheet.get_Range(StartCell, EndCell);
excelRange.Value2 = Value;
excelRange = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
///
/// 给一行写数据
///
public void setOneLineValues(int LineID, int StartCellColumnID, int EndCellColumnID, string[] Values)////已经测试
{
//用1-19号元素
//if (Values.Length!=EndCellColumnID-StartCellColumnID)
//{
// throw new Exception("单元格数目与提供的值的数目不一致！");
//}
for (int i = StartCellColumnID; i
/// 设置一个Cell的数据格式
///
/// CellRowID是cell的行索引
/// CellColumnID是cell的列索引
///数据格式
public void setOneCellNumberFormat(int CellRowID, int CellColumnID, string numberFormat)
{
try
{
excelRange = (Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID];
excelRange.NumberFormatLocal = numberFormat;
excelRange = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
public void SetRowHeight(string startCell, string endCell, int size)
{
//设置某个范围内的单元格的行的高度
excelRange = excelWorkSheet.get_Range(startCell, endCell);
excelRange.RowHeight = size;
}
public void SetRowHeight(int CellRowID, int CellColumnID, float size)
{
//设置某个范围内的单元格的行的高度
excelRange = (Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID];
excelRange.RowHeight = size;
}
public void SetOneCellRowHeight(int CellRowID, int CellColumnID, int size)
{
//设置某个单元格的行的高度
excelRange = (Excel.Range)excelWorkSheet.Cells[CellRowID, CellColumnID];
excelRange.RowHeight = size;
}
///
/// 拷贝区域.限制：在同一个工作表中复制
///
/// 源区域的左上角单元格
/// 源区域的右下角单元格
/// 目标区域的左上角单元格
/// 目标区域的右下角单元格
public void CopyCells(string SourceStart, string SourceEnd, string DesStart, string DesEnd)
{
try
{
excelCopySourceRange = excelWorkSheet.get_Range(SourceStart, SourceEnd);
excelRange = excelWorkSheet.get_Range(DesStart, DesEnd);
excelCopySourceRange.Copy(excelRange);
excelCopySourceRange = null;
excelRange = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
public void CopyWorksheet(int SourceWorksheetIndex, int DesWorksheetIndex)
{
try
{
// Sheets("Sheet2").Select
//Sheets("Sheet2").Copy After:=Sheets(3)
Excel.Worksheet sheetSource = (Excel.Worksheet)excelWorkBook.Worksheets[SourceWorksheetIndex];
sheetSource.Select(Missing.Value);
Excel.Worksheet sheetDest = (Excel.Worksheet)excelWorkBook.Worksheets[DesWorksheetIndex];
sheetSource.Copy(Missing.Value, sheetDest);
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
///
/// 插入一行
///
/// 要插入所在行的索引位置，插入后其原有行下移
/// 要插入行的个数
public void InsertRow(int CellRowID, int RowNum)//插入空行
{
if (CellRowID
/// 保存Excel文件
///
public Excel.Range FindFirstRange(Excel.Range xlRange, string FindText)//查找//没有测试
{
//查找第一个满足的区域
//Search for the first match
Excel.Range firstFind = null;
firstFind = xlRange.Find(FindText, Missing.Value, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Missing.Value, Missing.Value);
return firstFind; //如果没找到，返回空
}
//http://msdn.microsoft.com/library/en-us/dv_wrcore/html/wrtskHowToSearchForTextInWorksheetRanges.asp?frame=true
///
/// 当前活动工作表中有效行数(总行数)
///
///
///
/// 判断单元格是否有数据
///
public bool CellValueIsNull(int CellLineID, int CellColumnID)////已经测试
{
//判断单元格是否有数据
if ((((Excel.Range)excelWorkSheet.Cells[CellLineID, CellColumnID]).Text.ToString().Trim() != ""))
return false;
return true;
}
public void newWorkbook(string excelTemplate, string fileName)
{
//以excelTemplate为模板新建文件fileName
//excelApplication.
excelWorkBook = excelWorkBooks.Add(excelTemplate);
SaveFileName = "";
SaveExcel();
}
public void newWorksheet()
{
excelWorkBook.Worksheets.Add(Missing.Value, Missing.Value, 1, Missing.Value);
}
public void setWorksheetName(int sheetIndex, string worksheetName)
{
// Missing.Value
Excel._Worksheet sheet = (Excel._Worksheet)(excelWorkBook.Worksheets[(object)sheetIndex]);
sheet.Name = worksheetName;
}
public void mergeOneLineCells(string startCell, string endCell)
{
//合并一行单元格
excelRange = excelWorkSheet.get_Range(startCell, endCell);
//excelRange.Merge(true);
excelRange.MergeCells = true;
}
public void HorizontalAlignmentCells(string startCell, string endCell, Excel.Constants alignment)
{
//水平对齐一行单元格
excelRange = excelWorkSheet.get_Range(startCell, endCell);
excelRange.HorizontalAlignment = alignment;
}
public void VerticalAlignmentCells(string startCell, string endCell, Excel.Constants alignment)
{
//垂直对齐一行单元格
excelRange = excelWorkSheet.get_Range(startCell, endCell);
excelRange.VerticalAlignment = alignment;
}
//实现列号-〉字母 (26-〉Z,27->AA)
private string ConvertColumnIndexToChar(int columnIndex)
{
if (columnIndex 256)
{
MessageBox.Show("columnIndex=" + columnIndex + ",超出了有效范围（1-256）");
return "A";
}
if (columnIndex >= 1 && columnIndex = 27 && columnIndex
/// 保存Excel文件，格式xml.
///
public void SaveExcelAsXML()
{
if (excelSaveFileName == "")
{
throw new Exception("未指定要保存的文件名");
}
try
{
//excelWorkSheet.SaveAs(excelSaveFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value);
excelWorkSheet.SaveAs(excelSaveFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
//--------------------------------------------------------------------------------------------------------
///
/// 关闭Excel文件，释放对象；最后一定要调用此函数，否则会引起异常
///
///
public void CloseExcelApplication()
{
try
{
excelWorkBooks = null;
excelWorkBook = null;
excelWorkSheet = null;
excelRange = null;
if (excelApplication != null)
{
excelApplication.Workbooks.Close();
//Object missing = Type.Missing;
excelApplication.Quit();
excelApplication = null;
//ReleaseAllRef(excelApplication);//Error
}
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void ReleaseAllRef(Object obj)
{//ReleaseComObject()方法可以使RCW减少一个对COM组件的引用，并返回减少一个引用后RCW对COM组件的剩余引用数量。
//我们用一个循环，就可以让RCW将所有对COM组件的引用全部去掉。
try
{
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 1) ;
}
finally
{
obj = null;
}
}
}
}
无法嵌入互操作类型“Microsoft.Office.Interop.Excel.ApplicationClass”。请改用适用的接口。
winform下对datagridview进行导出时候，写了一句：
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
导致结果如下：
类型“Microsoft.Office.Interop.Excel.ApplicationClass”未定义构造函数
无法嵌入互操作类型“Microsoft.Office.Interop.Excel.ApplicationClass”。请改用适用的接口。
解决办法是将引用的DLL：Microsoft.Office.Interop.Excel;的嵌入互操作类型改为false,就可以了。
下载地址：https://files.cnblogs.com/files/emanlee/ExcelLib.rar
转载链接：http://www.cnblogs.com/emanlee/archive/2007/05/31/766520.html
引用代码
static void Main(string[] args)
{
ExcelLib excel = new ExcelLib();
excel.OpenFileName = @"C:\Users\lc\Desktop\test.xlsx";
excel.SaveFileName = @"C:\Users\lc\Desktop\test002.xlsx";
excel.OpenExcelFile();
int columns = excel.getTotalColumnCount();
int rows = excel.getTotalRowCount();
string value = excel.getOneCellValue(1, 1);
for (int row = 1; row
[转]C#对Excel报表进行操作（读写和基本操作）
标签：nal www. 包含 oid 创建时间 添加引用 add alignment 无效
原文地址：https://www.cnblogs.com/luohengstudy/p/8531464.html