C# 操作Excel代码总结

本文分享了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代码总结的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题，如有不足之处，欢迎留言指出。感谢朋友们对本站的支持！