欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C# 操作Excel代码总结

程序员文章站 2023-12-17 17:15:34
本文分享了c#操作excel的相关代码,还是比较全面的,其实无外乎存取,增删改查等操作,参考下。 具体代码: //引用microsoft.office.inte...

本文分享了c#操作excel的相关代码,还是比较全面的,其实无外乎存取,增删改查等操作,参考下。

具体代码:

//引用microsoft.office.interop.excel.dll文件 
//添加using
using microsoft.office.interop.excel;
using excel=microsoft.office.interop.excel;
 
//设置程序运行语言
system.globalization.cultureinfo currentci = system.threading.thread.currentthread.currentculture;
system.threading.thread.currentthread.currentculture = new system.globalization.cultureinfo("en-us");
//创建application
excel.application xlapp = new excel.application();
//设置是否显示警告窗体
excelapp.displayalerts = false;
//设置是否显示excel
excelapp.visible = false;
//禁止刷新屏幕
excelapp.screenupdating = false;
//根据路径path打开
excel.workbook xlsworkbook = excelapp.workbooks.open(path, system.type.missing, system.type.missing, system.type.missing,
system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing,
system.type.missing, system.type.missing, system.type.missing, system.type.missing);
//获取worksheet对象
excel.worksheet xlsworksheet = (worksheet)xlsworkbook.worksheets["sales plan"];
 
***获取最后一行、一列的两种方法***
//获取已用的范围数据
int rowscount = xlsworksheet.usedrange.rows.count;
int colscount = xlsworksheet.usedrange.columns.count;
int rowscount = xlsworksheet.get_range("a65536", "a65536").get_end(microsoft.office.interop.excel.xldirection.xlup).row;
int colscount = xlsworksheet.get_range("zz1", "zz1").get_end(microsoft.office.interop.excel.xldirection.xltoleft).column;
 
***将excel数据存入二维数组***
//rowscount:最大行  colscount:最大列
microsoft.office.interop.excel.range c1 = (microsoft.office.interop.excel.range)xlsworksheet.cells[1, 1];
microsoft.office.interop.excel.range c2 = (microsoft.office.interop.excel.range)xlsworksheet.cells[rowscount, colscount];
range rng = (microsoft.office.interop.excel.range)xlsworksheet.get_range(c1, c2);
object[,] exceldata = (object[,])rng.get_value(microsoft.office.interop.excel.xlrangevaluedatatype.xlrangevaluedefault);
 
//在第一列的左边插入一列
excel.range xlscolumns = (excel.range)xlsworksheet.columns[1, system.type.missing];
xlscolumns.insert(xlinsertshiftdirection.xlshifttoright, type.missing);
//xlssheettemplatemajor_meisai.cells.get_range(xlssheettemplatemajor_meisai.cells[1, 1], xlssheettemplatemajor_meisai.cells[65535, 1]).insert(type.missing, type.missing);
 excel.range rng;
      rng = worksheet.get_range("a:a", "a:a");
      rng.insert(excel.xldirection.xltoright, excel.xlinsertformatorigin.xlformatfromleftorabove);
excel.range rng = (microsoft.office.interop.excel.range)xlsworksheet.columns[12, type.missing];
rng.insert(xlinsertshiftdirection.xlshifttoright, xlinsertformatorigin.xlformatfromleftorabove);
 
 
//删除行
range deleterng = (range)xlsworksheetsapexcel.rows[2, system.type.missing];
deleterng.delete(excel.xldeleteshiftdirection.xlshiftup);
 
//删除一列数据
((microsoft.office.interop.excel.range)xlworksheet.cells[1, 11]).select();
((microsoft.office.interop.excel.range)xlworksheet.cells[1, 11]).entirecolumn.delete(0);
((excel.range)xlssheetsharemajor_meisai.cells[1, 3]).entirecolumn.delete (0);
       
//设置背景色为红色
xlsworksheet.get_range("a1", "a1").interior.colorindex = 3;
 
//设置format属性  属性值可以通过在vba中录宏得到
microsoft.office.interop.excel.range range1 = xlsworksheetadd.get_range("j1", "j65535");
range1.numberformat = "@";//文本格式
range1 = xlsworksheetadd.get_range("l1", "l65535");
range1.numberformat = "0.00";//保留两位小数
excel.range rng = xlssheetsharemajor_meisai.columns["i", system.type.missing] as excel.range;
rng.numberformatlocal =@"yyyy/m/d";//设置日期格式
 
 
//替换
range drng = xlsworksheettemplate.get_range("d1", "d65535");
drng.replace(" ", "", xllookat.xlpart, xlsearchorder.xlbycolumns, system.type.missing, system.type.missing, system.type.missing, system.type.missing);
drng.texttocolumns(drng, excel.xltextparsingtype.xldelimited, excel.xltextqualifier.xltextqualifiersinglequote, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing);
 
//分列处理 设置为文本
range texttocolumnrng = xlsworksheet.get_range("e1", "e65535");
xlsworksheet.get_range("e1", "e65535").texttocolumns(texttocolumnrng, excel.xltextparsingtype.xldelimited, excel.xltextqualifier.xltextqualifiersinglequote, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing);
 
//设置公式
rng = xlmergefileworksheet.get_range("d2", "d" + rowcount);//设置列范围
rng.formula = @"=if(rc[-3]=""h"",""survivor"",""donor"")";//设置公式  @的问题
//rng.numberformat = "$0.00";//设置格式
copyrng = xlssheettemplatemajor_meisai.get_range("n3", "n" + lastrowtemplate);
copyrng.formula = "=vlookup(rc[-12],ar残!c[-13]:c[-11],2,0)";
 
//通过行、列的索引获取值
string f = convert.tostring(xlssheetsharemajor_meisai.get_range(xlssheetsharemajor_meisai.cells[2, 1], xlssheetsharemajor_meisai.cells[2,1]).value2);
 
//筛选
//确定筛选范围
d1_rng = d1_temsheet.cells.get_range(d1_temsheet.cells[1, 1], d1_temsheet.cells[1, 50]);
//执行筛选动作
rng.autofilter(5, "s", microsoft.office.interop.excel.xlautofilteroperator.xlfiltervalues, type.missing, true);
rng.autofilter(6, "h", microsoft.office.interop.excel.xlautofilteroperator.xlor, "f", true);
d1_rng.autofilter(d1_column + 2, "#n/a", microsoft.office.interop.excel.xlautofilteroperator.xlfiltervalues,"#n/a",false);
 
//复制粘贴
d2_rng.copy(type.missing);
d2_temsheet.cells.get_range(d2_temsheet.cells[2, (d2_column + 1)], d2_temsheet.cells[2, (d2_column + 1)]).pastespecial(excel.xlpastetype.xlpastevalues, excel.xlpastespecialoperation.xlpastespecialoperationnone, false, false);
 
 
//find查找
rng = mysheet.get_range("a1", "iv10").find(arrlabel[j], type.missing,
            microsoft.office.interop.excel.xlfindlookin.xlvalues, microsoft.office.interop.excel.xllookat.xlwhole,
            microsoft.office.interop.excel.xlsearchorder.xlbyrows,
            microsoft.office.interop.excel.xlsearchdirection.xlnext, false, type.missing, type.missing);
 
//文字占满单元格
range.entirecolumn.autofit();
 
//另存
xlsworkbook.saveas(filename, type.missing, type.missing, type.missing, type.missing, type.missing, xlsaveasaccessmode.xlnochange, type.missing, type.missing, type.missing, type.missing, type.missing);
 
***关闭对象***
system.runtime.interopservices.marshal.releasecomobject(xlsworksheet);
system.runtime.interopservices.marshal.releasecomobject(xlsworkbook);
excelapp.quit();
kill(excelapp);//调用方法关闭进程
gc.collect();
 
/// <summary>
/// 关闭excel进程
/// </summary>
public class keymyexcelprocess
{
  [dllimport("user32.dll", charset = charset.auto)]
  public static extern int getwindowthreadprocessid(intptr hwnd, out int id);
  public static void kill(microsoft.office.interop.excel.application excel)
  {
    try
    {
      intptr t = new intptr(excel.hwnd);  //得到这个句柄,具体作用是得到这块内存入口
      int k = 0;
      getwindowthreadprocessid(t, out k);  //得到本进程唯一标志k
      system.diagnostics.process p = system.diagnostics.process.getprocessbyid(k);  //得到对进程k的引用
      p.kill();   //关闭进程k
    }
    catch (system.exception ex)
    {
      throw ex;
    }
  }
}
   
   
//关闭打开的excel方法
 public void closeexcel(microsoft.office.interop.excel.application excelapplication, microsoft.office.interop.excel.workbook excelworkbook)
{
  excelworkbook.close(false, type.missing, type.missing);
  excelworkbook = null;
  excelapplication.quit();
  gc.collect();
  keymyexcelprocess.kill(excelapplication);
}

总结

以上就是本文关于c# 操作excel代码总结的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题,如有不足之处,欢迎留言指出。感谢朋友们对本站的支持!

上一篇:

下一篇: