操作EXCEL代码，c#完全版

2023-06-18 03:44•css•阅读 562

using System;

using System.Collections;

using Excel=Microsoft.Office.Interop.Excel;

namespace WindowsApplication1

{

///

/// 对Excel进行操作的类。

///

public class JointExcel

{

#region 私有成员

private Excel.ApplicationClass m_objExcel;//Excel应用程序对象

private Excel.Workbooks m_objBooks;//Excel的Books对象

private Excel.Workbook m_objBook;//当前Book对象

private Excel.Worksheet m_objSheet;//当前Sheet对象

private Excel.Range m_Range;//当前Range对象

private System.Reflection.Missing miss =

System.Reflection.Missing.Value;//空数据变量

private Excel.Font m_Font;//当前单元格的字体属性对象

private Excel.Borders m_Borders;//当前单元格或者区域的边框属性对象

//单元格的四条边框对象

private Excel.Border m_BorderTop;

private Excel.Border m_BorderBottom;

private Excel.Border m_BorderLeft;

private Excel.Border m_BorderRight;

private 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"};

#endregion

///

/// 本类使用在web application中时，请在Web.Config中添加

///

public JointExcel()

{

m_objExcel = new Excel.ApplicationClass();

m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));

m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;

}

~JointExcel()

{

//释放所有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)

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);

GC.Collect();

}

#region 选定单元格

private string GetCell(int ColNum, int RowNum)

{

string temp = "A";

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();

}

if (i0 == 0 && i1 > 0)

{

return m_colString[i1] + row.ToString();

}

else

{

//return temp + m_colString[i0] + row.ToString();

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[Excel.XlBordersIndex.xlEdgeTop];

m_BorderBottom = m_Borders[Excel.XlBordersIndex.xlEdgeBottom];

m_BorderLeft = m_Borders[Excel.XlBordersIndex.xlEdgeLeft];

m_BorderRight = m_Borders[Excel.XlBordersIndex.xlEdgeRight];

m_cellRange = m_Range;

}

///

/// 选择相应的区域

///

/// 起始单元格列号

/// 起始单元格行号

/// 结束单元格列号

/// 结束单元格行号

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[Excel.XlBordersIndex.xlEdgeTop];

m_BorderBottom = m_Borders[Excel.XlBordersIndex.xlEdgeBottom];

m_BorderLeft = m_Borders[Excel.XlBordersIndex.xlEdgeLeft];

m_BorderRight = m_Borders[Excel.XlBordersIndex.xlEdgeRight];

m_cellRange = m_Range;

}

#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;

m_Range.Font.Name = "Arial";

m_Range.Font.Size = 9;

}

///

/// 合并选定区域后给其附值

///

/// 起始行号

/// 起始列号

/// 结束行号

/// 结束列号

/// 值

public void SetCellValue(int startRow, int startCol, int endRow, int

endCol, string value)

{

Merge(startRow, startCol, endRow, endCol);

m_Range.Value2 = value;

m_Range.Font.Size = 9;

m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

}

#endregion

public void SetCellbolk(int row, int col)

{

SetRange(col, row);

m_Range.Font.Bold = true;

}

#region 设定单元格对齐方式

///

/// 设定单元格中文字的对齐方式

///

/// 对齐方式

//public void SetHorizontal(JointEmun.ExcelAlign ea)

//{

// if (m_Range == null) throw new System.Exception("没有设定单元格或者区域");

// switch (ea.ToString())

// {

// case "Left":

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

// break;

// case "Right":

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

// break;

// case "center":

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

// break;

// default:

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

// break;

// }

//}

///

/// 设定单元格中文字的对齐方式

///

/// 单元格行号

/// 单元格列号

/// 对齐方式

// public void SetHorizontal(int rowIndex, int columnIndex, JointEmun.ExcelAlign

//ea)

// {

// SetRange(columnIndex, rowIndex);

// switch (ea.ToString())

// {

// case "Left":

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

// break;

// case "Right":

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

// break;

// case "center":

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

// break;

// default:

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

// break;

// }

///

/// 设定选定区域的对齐方式

///

/// 起始行号

/// 起始列号

/// 结束行号

/// 结束列号

/// 对齐方式

// public void SetHorizontal(int startRowIndex, int startColumnIndex, int

//endRowIndex, int endColumnIndex, JointEmun.ExcelAlign ea)

// {

// SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);

// switch (ea.ToString())

// {

// case "Left":

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

// break;

// case "Right":

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

// break;

// case "center":

// m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

// break;

// default:

// m_Range.HorizontalAlignment = 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

public void setcolor(int rowSum, int colSum, int endrowSum, int endcolIndex,int color)

{

m_objSheet.get_Range(m_objExcel.Cells[rowSum, colSum], m_objExcel.Cells[endrowSum, endcolIndex]).Select();

m_objSheet.get_Range(m_objExcel.Cells[rowSum, colSum], m_objExcel.Cells[endrowSum, endcolIndex]).Interior.ColorIndex = color;//设置为浅黄色，共

}

//畫邊框

public void setline(int row, int col)

{

//xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;

//xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗

//xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗

//xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗

//xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗

SetRange(col, row);

m_Range.Borders.LineStyle = 1;

}

public void setline(int srow, int scol, int erow, int ecol, int linetype)

{

m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders.LineStyle = linetype;

}

public void setlinebold(int srow, int scol, int erow, int ecol, int linetype)

{

m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders.LineStyle = linetype;

m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗

m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗

m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗

m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗

}

public void setline_left(int srow, int scol, int erow, int ecol, int linetype)

{

m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = linetype;

}

public void setline_right(int srow, int scol, int erow, int ecol, int linetype)

{

m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = linetype;

}

public void setline_top(int srow, int scol, int erow, int ecol, int linetype)

{

m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = linetype;

}

public void setline_btoon(int srow, int scol, int erow, int ecol, int linetype)

{

m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = linetype;

}

//賦值2

public void SetCellValue2(int srow, int scol, int erow, int ecol,string value)

{

m_Range=m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]);

m_Range.Value2 = value;

m_Range.Font.Name = "Arial";

m_Range.Font.Size = 9;

}

public void SetCellbolk2(int srow, int scol, int erow, int ecol)

{

m_Range = m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]);

m_Range.Font.Bold = true;

}

public void save()

{

m_objBook.SaveAs(@"E:\Demo.xls", miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);

m_objBook.Close(miss, miss, miss);

m_objExcel.Quit();

GC.Collect();

}

上一篇 »C#操作NPOI实现Excel数据导入导出
下一篇 »ACCESS数据库C#操作类，SQLHELPER修改版——转自网上

操作EXCEL代码，c#完全版

相关推荐

Excel-DNA项目只用1个文件实现Ribbon CustomUI和CustomTaskpane定制【VB.Net版】

利用C#实现对excel的写操作

C#操作Excel总结

C#开发中使用Npoi操作excel实例代码

C# 操作Excel基础篇，读取Excel、写入Excel

使用C#和Excel进行报表开发，六－操作行和列

使用C#和Excel进行报表开发，五－操作单元格边框和颜色

[Excel] C# ExcelHelper操作类 ，转载

[Excel] C# ExcelHelper操作类，转载