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