Excel Operation

最新推荐文章于 2022-09-08 08:17:54 发布
最新推荐文章于 2022-09-08 08:17:54 发布
阅读量130 收藏
点赞数
文章标签： runtime

using System;
using System.Collections;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;

namespace forecast.components
{
/// <summary>
/// Summary description for Class1.
/// </summary>
///
public enum COLOR
{
SpringGreen= 0x7FFF00,
LightTur= 0xFFFFCC,
White= 0xFFFFFF,
Red= 0x0000FF,
Yellow= 0x00FFFF,
Green= 0x66CC99,
Blue= 0xFF0000,
Gray1= 0x808080,
Gray2       = 0x999999,
Gray3= 0xDCDCDC,
DarkGray= 0xA9A9A9,
Black= 0x000000,
YellGreen= 0x32CD9A,
RoyalBlue= 0xE16941,
}

public enum POSITION
{
HCenter= 0,
HLeft= 1,
HRight= 2,
VCenter = 3,
VTop    = 4,
VBottom= 5,
}

public class ExcelOperate
{
private bool m_bolFlag;
protected Application m_appExcel;
protected Workbooks m_wkbs;
protected Workbook m_wkb;
protected Sheets m_sheets;
protected Worksheet m_wksheet;
private string m_strFileName;

public ExcelOperate()
{
m_strFileName = "";
m_bolFlag = false;
}

public bool Open()
{
bool bol;

bol = true;
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");

try
{
GC.Collect();// clean up any other excel guys hangin' around...
m_appExcel = new Application();
m_appExcel.Visible = false;
m_wkbs = m_appExcel.Workbooks;
m_wkb = m_wkbs.Add(XlWBATemplate.xlWBATWorksheet);
m_sheets = m_wkb.Worksheets;
}
catch
{
MemoryClear();
bol = false;
}
return bol;
}

public bool Open (string p_strFileName)
{
bool bol;
this.m_strFileName = p_strFileName;
bol = true;
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");

try
{
GC.Collect();// clean up any other excel guys hangin' around...
m_appExcel = new Application();
m_appExcel.Visible = false;
m_wkbs = m_appExcel.Workbooks;
m_wkb = m_wkbs.Open(p_strFileName,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value);
m_sheets = m_wkb.Worksheets;
m_wksheet = (Worksheet)m_sheets.get_Item(1);


}
catch
{
MemoryClear();
bol = false;
}
return bol;
}

public void SetProtection(string p_strPassword)
{
try
{
m_wksheet.Protect(p_strPassword,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value);
}
catch(Exception err)
{
MemoryClear();
throw err;
}

}


public void SetRangeProtection(string p_strRangeBegin,string p_strRangeEnd,bool p_blnProtection)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Locked = p_blnProtection;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}


public string GetCellData(string strPosition)
{
try
{
string strvalue = "";
Range rng = m_wksheet.get_Range(strPosition,strPosition);
if (rng.value2 != null)
strvalue = Convert.ToString(rng.value2);
return strvalue;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void Close()
{
//m_wkbs.Close();
//if(m_strFileName != "")
//m_wkb.Close(true,m_strFileName,Missing.value);

if(m_appExcel != null)
MemoryClear();

}

protected void MemoryClear()
{

m_appExcel.Quit();
if (m_appExcel != null)System.Runtime.InteropServices.Marshal.ReleaseComObject (m_appExcel);
if (m_wksheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject (m_wksheet);
if (m_wkb != null) System.Runtime.InteropServices.Marshal.ReleaseComObject (m_wkb);
m_wkbs = null;
m_wkb = null;
m_sheets = null;
m_appExcel = null;
m_wksheet = null;
GC.Collect();  // force final cleanup!
}

public void AddNextSheet()
{
try
{
m_sheets.Add(Missing.value,Missing.value,1,XlSheetType.xlWorksheet);
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void GetWorkSheet(string p_strName)
{
try
{
if(m_bolFlag == true)
m_sheets.Add(Missing.value,Missing.value,1,XlSheetType.xlWorksheet);
m_wksheet = (Worksheet)m_sheets.get_Item(1);
m_wksheet.Name = p_strName;
m_bolFlag = true;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SaveExcel(string p_strFileName)
{
try
{
File.Delete(p_strFileName);
m_wkb.SaveAs(p_strFileName,XlFileformat.xlWorkbookNormal,
Missing.value,Missing.value,false,false,
XlSaveAsAccessMode.xlShared,Missing.value,Missing.value,
Missing.value,Missing.value,Missing.value);
m_strFileName = p_strFileName;
m_wkb.Close(true,p_strFileName,Missing.value);
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SaveExcelExclusive(string p_strFileName)
{
try
{
File.Delete(p_strFileName);
m_wkb.SaveAs(p_strFileName,XlFileformat.xlWorkbookNormal,
Missing.value,Missing.value,false,false,
XlSaveAsAccessMode.xlExclusive,Missing.value,Missing.value,
Missing.value,Missing.value,Missing.value);
m_strFileName = p_strFileName;
m_wkb.Close(true,p_strFileName,Missing.value);
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void CloseWorkbook()
{
try
{
m_wkb.Close(false,this.m_strFileName,Missing.value);
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}


public void WriteLineData(string p_strRangeBegin,string p_strRangeEnd,Array p_aryData)
{
object[] args = new object[1];
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
args[0] = p_aryData;
range.GetType().InvokeMember("value",BindingFlags.SetProperty,null,range,args);
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeBackColor(string p_strRangeBegin,string p_strRangeEnd,COLOR p_enuColor)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Interior.Color = p_enuColor;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeFontName(string p_strRangeBegin,string p_strRangeEnd,string p_strFontName)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Font.Name = p_strFontName;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeSummary(string p_strRangeBegin,string p_strRangeEnd)
{
Range range;

try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.value2 = "=SUM(" + p_strRangeBegin + ":" + p_strRangeEnd + ")";
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeHyperLink(string p_strRangeBegin,string p_strRangeEnd,string p_strHyperLink)
{
Range range;
string strvalue;
strvalue = "";
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
strvalue = Convert.ToString(range.value2);
range.Hyperlinks.Add(range,"",p_strHyperLink,"",strvalue);
range.Font.Name = "Arial";
range.Font.Bold = true;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeFontColor(string p_strRangeBegin,string p_strRangeEnd,COLOR p_enuColor)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Font.Color = p_enuColor;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeFontBold(string p_strRangeBegin,string p_strRangeEnd,bool p_bolBold)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Font.Bold = p_bolBold;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeFontSize(string p_strRangeBegin,string p_strRangeEnd,int p_intSize)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Font.Size = p_intSize;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeWrapText(string p_strRangeBegin,string p_strRangeEnd)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Cells.WrapText = true;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetSheetZoom(int p_intZoom,string p_strName)
{
try
{
m_wksheet.Application.ActiveWindow.Zoom = p_intZoom;
m_wksheet.Cells.Font.Name = p_strName;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeFontPosition(string p_strRangeBegin,string p_strRangeEnd,POSITION p_enuHType,POSITION p_enuVType)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
//Horizontal
switch(p_enuHType)
{
case POSITION.HCenter:
range.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
break;
case POSITION.HLeft:
range.Cells.HorizontalAlignment = XlHAlign.xlHAlignLeft;
break;
case POSITION.HRight:
range.Cells.HorizontalAlignment = XlHAlign.xlHAlignRight;
break;
default:
break;
}
//Vertical
switch(p_enuVType)
{
case POSITION.VCenter:
range.Cells.VerticalAlignment = XlVAlign.xlVAlignCenter;
break;
case POSITION.VTop:
range.Cells.VerticalAlignment = XlVAlign.xlVAlignTop;
break;
case POSITION.VBottom:
range.Cells.VerticalAlignment = XlVAlign.xlVAlignBottom;
break;
default:
break;
}
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeHeight(string p_strRangeBegin,string p_strRangeEnd,double p_fltHeight)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.RowHeight = p_fltHeight;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeWidth(string p_strRangeBegin,string p_strRangeEnd,double p_fltWidth)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.ColumnWidth = p_fltWidth;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}
      
public void SetRangeformatType(string p_strRangeBegin,string p_strRangeEnd,string p_strformat)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Cells.Numberformat = p_strformat;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeWidthHeight(string p_strRangeBegin,string p_strRangeEnd,double p_fltWidth,double p_fltHeight)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.ColumnWidth = p_fltWidth;
range.RowHeight = p_fltHeight;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeBorders(string p_strRangeBegin,string p_strRangeEnd,COLOR p_enuColor)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Cells.Borders.Color = p_enuColor;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeItatic(string p_strRangeBegin,string p_strRangeEnd)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Font.Italic = true;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeCell(string p_strRangeBegin,string p_strRangeEnd,double p_fltHeight,string p_strFontName,object p_strvalue,bool p_bolBold,int p_intSize)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.RowHeight = p_fltHeight;
range.Font.Name = p_strFontName;
range.value2 = p_strvalue;
range.Font.Bold = p_bolBold;
range.Font.Size = p_intSize;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetMergeRange(string p_strRangeBegin,string p_strRangeEnd,
COLOR p_enuBackColor,COLOR p_enuFondColor,COLOR p_enuBrold,int p_intFontSize,
bool p_bolBold,object p_strvalue,POSITION p_enuHType,POSITION p_enuVType)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Merge(Missing.value);
range.value2 = p_strvalue;
switch(p_enuHType)
{
case POSITION.HCenter:
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
break;
case POSITION.HLeft:
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
break;
case POSITION.HRight:
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
break;
default:
break;
}
//Vertical
switch(p_enuVType)
{
case POSITION.VCenter:
range.VerticalAlignment = XlVAlign.xlVAlignCenter;
break;
case POSITION.VTop:
range.VerticalAlignment = XlVAlign.xlVAlignTop;
break;
case POSITION.VBottom:
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
break;
default:
break;
}
range.Borders.Color = p_enuBrold;
range.Font.Bold = p_bolBold;
range.Interior.Color = p_enuBackColor;
range.Font.Color = p_enuFondColor;
range.Font.Size = p_intFontSize;
range.Font.Name  = "Arial";

}
catch(Exception err)
{
MemoryClear();
throw err;
}
}
}
}

weixin_34396902
关注
ExcleOperation
04-17
使用MFC操作Excel的小工具，这里是使用VS2008操作Office2007。这里注意，VS2005不能很好的支持对Office2007的操作； MFC对Excel的操作使用ole方法，包括对Excel的读写、单元格的合并、设置列宽等功能，其中如果加载com .h的方法参考网上通用的教程； 里面主要代码是IllusionExcelFile和Excle，为了便于阅读上传整个程序；
NPOI.ExcelOperation.rar
09-10
NPOI操作Excel，包含后缀xls、xlsx方式Excel的读取，以及xlsx方式存储。完整C#示例代码，简单易懂。
Operation_Excel.py （util 包）
SnakeSHESUQIN的博客
12-14 313
# coding:utf-8 # 导入 xlrd 包，用来操作excel的 import xlrd from xlutils.copy import copy class OperExcel: def __init__(self, file_name=None, sheet_id=None): if file_name: self.file...
c#操作excel封装的类
程序猿
04-28 544
using System; using System.Collections.Generic; using System.Text; using Excel = Microsoft.Office.Interop.Excel; using System.Diagnostics; using System.Windows.Forms; namespace OfficeManager {
excel_operation.zip
06-05
这个"excel_operation.zip"压缩包包含三个Excel文件：excel2.xlsx、excel3.xlsx、excel1.xlsx，很可能是为了演示或教学不同Excel功能和操作。虽然没有具体的标签来指示具体内容，但我们可以根据常见的Excel使用场景...
Java-operation-of-Excel.zip_operation_operationjava
09-19
在Java编程中，处理Excel文件是一项常见的任务，特别是在数据分析、报表生成或数据导入导出等场景。本主题主要探讨了两种流行的Java库：Apache POI和JExcelAPI（简称jxl），它们允许开发者操作Microsoft Excel文件。...
Use-MFC-EXCEL-file-operation.rar_Excel MFC_MFC_c excel_excel_mfc
09-24
在Microsoft Visual Studio中，MFC（Microsoft Foundation Classes）是一个C++...在提供的“Use MFC EXCEL file operation.pdf”文档中，你应该能找到更详细、具体的示例代码和解释，帮助你更好地理解和实践这些概念。
Java_implementation_of_excel_file_operation_code.r_java excel_op
09-24
Java implementation of the excel file operation code
Generic-Database-Operation:使用 Excel 表对任何表执行 InsertUpdate 操作的通用实用程序
06-23
#Generic DB Operation generic.updation.tablelist=Persons 选择表格后，单击下载模板复选框下载模板。 将下载 Excel 文件，您需要在其中针对提到的列指定数据。 为了识别表格的列组，我们在excel中使用名称范围的...
Solution to a excel operation problem
weixin_30699831的博客
07-17 121
Manipulate the EXCEL in .Net, but got the warning: "Old format or invalid type library. (Exception from HRESULT:0x80028018 (TYPE_E_INVDATAREAD))". The issue is due to the locale unmatch, and I found s...
C++通过COM操作EXCEL
热门推荐
litterCooker的博客
08-09 1万+
** C++通过COM操作EXCEL ** 　　C++操作EXCEL网上有很多方法，我觉得通过微软自带的COM操作还是比较方便并且兼容性比较好的，总结了网上各个网页的博客分享，整理了一份我自己的理解。 一、封装Excel库 　　1、环境配置：VS2013加EXCEL2013 　　2、封装步骤 　　　1、新建一个MFC项目 　　　２、右击项目选址类向导 ...
Java实现操作excel表格(基于poi.jar)
wolfking0608的博客
06-10 406
首先导入了一个poi.jar 网上有很多这个资源可以下载 XSSFSheet sheet=null; XSSFWorkbook book=null; 一:查  （查找本地指定位置的excel表格，在控制台输出）　 ? 1 2 3 4 5 6 7 8 9 10 11 12 1
c#操作excel文件整理
guoguo19811025的专栏
06-16 2443
一、准备工作 ExcelOperation 的摘要说明。操作excel需要借助Excel.dll文件，生成Excel.dll文件的方法如下：1.将office安装目录下的Excel.exe文件拷贝到C:/Program Files/Microsoft Visual Studio .NET 2003/SDK/v1.1/Bin下2.然后打开命令窗口 运行 TlbImp.exe Ex
python实现接口自动化测试_operationExcel文件内容
QQSSDD的博客
02-12 311
#作者:Administrator #时间:2019/10/19 import xlrd from be.utils.public import public from be.utils.excel_data import excel_data class operationExcel: #初始化 def init(self): self.exdata=excel_data() self.publ...
Python读写Excel简单案例及调试说明文档
09-08 2370
工作中经常有处理Excel文件的需求，尤其是不懂写程序的业务人员。在人工处理Excel文件多了之后就想着自己能写点程序自动处理该多省事。好，下面机会来了，两分钟带你入坑Python操作Excel文件中的数据。
C#读取Excel表
weixin_30363509的博客
11-23 83
C#读取Excel表内容存于dataset（工作中一个同事做的任务源码读了一天，累。。记录下来以后可能用得着） View Code 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI;...
从EXCEL数据读到数据库
ZXK_CUIT的博客
07-11 478
这两天在看一个问题，就是将Excel里的数据导入数据库里面，并且不能有重复。网上有很多方法，我这只是做个总结，算不上全是原创。   其实思路是很简单的，首先从Excel 读到内存里，再从内存里面导入数据库里面，中间要借助Dataset ，再导入时不用管数据重复的问题，导后我们再数据库里面把重复的去掉。  界面像这样 显示使用dataGridView 组件，这个组件只能显示一个表？？我只让它显
C#对Excel的操作
濃(龍)情派對的专栏
02-26 2539
第一步:添加引用-->Com-->Microsoft Excel 11.0 Object Library 第二步:在文件最上方加:using Excel = Microsoft.Office.Interop.Excel;第三步:编辑下面两个方法/// /// 执行完Excel操作方法后执行资源回收操作,必须与Excel操作的方法分开/// private void ExcelWrite
excel表格vba代码
最新发布
07-27
以下是一些Excel表格VBA代码的示例： 1. 获取工作簿中指定工作表的数据并存入数组： ```vba Function get_arr(file, sh_name) Dim wb As Workbook Set wb = Workbooks.Open(file) wb.Sheets(sh_name).Select row_num = \[b65536\].End(xlUp).Row col_num = ActiveSheet.UsedRange.Columns.Count col_str = get_col(col_num) '获取列名称 arr = Sheets(sh_name).Range("a1:" & col_str & row_num) wb.Close False Set wb = Nothing get_arr = arr End Function ``` 2. 将当前工作表中的公式转换为数值： ```vba Sub shuzhi() row_num = \[a65536\].End(xlUp).Row col_num = ActiveSheet.UsedRange.Columns.Count col_str = get_col(col_num) Range("A1:" & col_str & row_num).Copy Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End Sub ``` 3. 添加新工作表并删除旧数据： ```vba Function add_sheet(sh_name) '删除旧数据 Application.DisplayAlerts = False For Each sht In Sheets If sht.Name = sh_name Then sht.Delete End If Next sht Application.DisplayAlerts = True '添加新工作表 Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = sh_name End Function Sub ffa() row_num = \[a65536\].End(xlUp).Row col_num = ActiveSheet.UsedRange.Columns.Count col_str = get_col(col_num) arr = Range("A1:" & col_str & row_num) add_sheet("删除后") Sheets("删除后").Range("a1").Resize(row_num, UBound(arr, 2)) = arr For i = row_num To 1 Step -1 If Cells(1, i) = "同比" Or Cells(1, i) = "" Then '此处填写条件 Columns(i).Delete End If Next i End Sub ``` 希望以上代码能够帮到您。如果您有其他问题，请随时提问。 #### 引用[.reference_title] - *1* *2* *3* [OFFICE Excel表格中常用的vba代码集锦](https://blog.csdn.net/qq_41780234/article/details/98598325)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值