让C# Excel导入导出 支持不同版本Office
问题:最近在项目中遇到,不同客户机安装不同office版本,在导出excel时,发生错误。
找不到excel com组件,错误信息如下。
未能加载文件或程序集“microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c”或它的某一个依赖项。系统找不到指定的文件。
解决方法:
1.引用高版本的的excel.dll组件,最新版本14.0.0 防止客户安装高版本如office不能导出。
(dll组件可以兼容低版本,不能兼容高版本)
2.右键dll属性,将引用的excel.dll组件,嵌入互操作类型为true,特定版本=false .这一步非常关键。
嵌入互操作类型 改成true后,生成时可能现有调用excel的代码会报错,引用microsoft.csharp 命名空间,可以解决此问题。
3.引用excel 14.0.0 dll组件方法,vs2012 右键添加引用->程序集->扩展->microsoft.office.interop.excel
excel.dll: http://xiazai.jb51.net/201608/yuanma/excel(jb51.net).rar
其他方法:
1.使用npoi.dll开源组件,可以不安装office软件,进行读写excel文件。
npio.dll: http://xiazai.jb51.net/201608/yuanma/npoi(jb51.net).rar
调用方法如下:
导出代码:
/// <summary> /// datatable导出到excel的memorystream export() /// </summary> /// <param name="dtsource">datatable数据源</param> /// <param name="strheadertext">excel表头文本(例如:车辆列表)</param> public static memorystream export(datatable dtsource, string strheadertext) { hssfworkbook workbook = new hssfworkbook(); isheet sheet = workbook.createsheet(); #region 右击文件 属性信息 { documentsummaryinformation dsi = propertysetfactory.createdocumentsummaryinformation(); dsi.company = "npoi"; workbook.documentsummaryinformation = dsi; summaryinformation si = propertysetfactory.createsummaryinformation(); si.author = "文件作者信息"; //填加xls文件作者信息 si.applicationname = "创建程序信息"; //填加xls文件创建程序信息 si.lastauthor = "最后保存者信息"; //填加xls文件最后保存者信息 si.comments = "作者信息"; //填加xls文件作者信息 si.title = "标题信息"; //填加xls文件标题信息 si.subject = "主题信息";//填加文件主题信息 si.createdatetime = system.datetime.now; workbook.summaryinformation = si; } #endregion icellstyle datestyle = workbook.createcellstyle(); idataformat format = workbook.createdataformat(); datestyle.dataformat = format.getformat("yyyy-mm-dd"); //取得列宽 int[] arrcolwidth = new int[dtsource.columns.count]; foreach (datacolumn item in dtsource.columns) { arrcolwidth[item.ordinal] = encoding.getencoding(936).getbytes(item.columnname.tostring()).length; } for (int i = 0; i < dtsource.rows.count; i++) { for (int j = 0; j < dtsource.columns.count; j++) { int inttemp = encoding.getencoding(936).getbytes(dtsource.rows[i][j].tostring()).length; if (inttemp > arrcolwidth[j]) { arrcolwidth[j] = inttemp; } } } int rowindex = 0; foreach (datarow row in dtsource.rows) { #region 新建表,填充表头,填充列头,样式 if (rowindex == 65535 || rowindex == 0) { if (rowindex != 0) { sheet = workbook.createsheet(); } #region 表头及样式 { irow headerrow = sheet.createrow(0); headerrow.heightinpoints = 25; headerrow.createcell(0).setcellvalue(strheadertext); icellstyle headstyle = workbook.createcellstyle(); headstyle.alignment = horizontalalignment.center; ifont font = workbook.createfont(); font.fontheightinpoints = 20; font.boldweight = 700; headstyle.setfont(font); headerrow.getcell(0).cellstyle = headstyle; sheet.addmergedregion(new npoi.ss.util.cellrangeaddress(0, 0, 0, dtsource.columns.count - 1)); } #endregion #region 列头及样式 { irow headerrow = sheet.createrow(1); icellstyle headstyle = workbook.createcellstyle(); headstyle.alignment = horizontalalignment.center; ifont font = workbook.createfont(); font.fontheightinpoints = 10; font.boldweight = 700; headstyle.setfont(font); foreach (datacolumn column in dtsource.columns) { headerrow.createcell(column.ordinal).setcellvalue(column.columnname); headerrow.getcell(column.ordinal).cellstyle = headstyle; //设置列宽 sheet.setcolumnwidth(column.ordinal, (arrcolwidth[column.ordinal] + 1) * 256); } } #endregion rowindex = 2; } #endregion #region 填充内容 irow datarow = sheet.createrow(rowindex); foreach (datacolumn column in dtsource.columns) { icell newcell = datarow.createcell(column.ordinal); string drvalue = row[column].tostring(); switch (column.datatype.tostring()) { case "system.string"://字符串类型 newcell.setcellvalue(drvalue); break; case "system.datetime"://日期类型 system.datetime datev; system.datetime.tryparse(drvalue, out datev); newcell.setcellvalue(datev); newcell.cellstyle = datestyle;//格式化显示 break; case "system.boolean"://布尔型 bool boolv = false; bool.tryparse(drvalue, out boolv); newcell.setcellvalue(boolv); break; case "system.int16"://整型 case "system.int32": case "system.int64": case "system.byte": int intv = 0; int.tryparse(drvalue, out intv); newcell.setcellvalue(intv); break; case "system.decimal"://浮点型 case "system.double": double doubv = 0; double.tryparse(drvalue, out doubv); newcell.setcellvalue(doubv); break; case "system.dbnull"://空值处理 newcell.setcellvalue(""); break; default: newcell.setcellvalue(""); break; } } #endregion rowindex++; } using (memorystream ms = new memorystream()) { workbook.write(ms); ms.flush(); ms.position = 0; sheet.dispose(); return ms; } }
导入代码:
/// <summary> /// 读取excel ,默认第一行为标头 /// </summary> /// <param name="strfilename">excel文档路径</param> /// <returns></returns> public static datatable import(string strfilename) { datatable dt = new datatable(); hssfworkbook hssfworkbook; using (filestream file = new filestream(strfilename, filemode.open, fileaccess.read)) { hssfworkbook = new hssfworkbook(file); } isheet sheet = hssfworkbook.getsheetat(0); system.collections.ienumerator rows = sheet.getrowenumerator(); irow headerrow = sheet.getrow(0); int cellcount = headerrow.lastcellnum; for (int j = 0; j < cellcount; j++) { icell cell = headerrow.getcell(j); dt.columns.add(cell.tostring()); } for (int i = (sheet.firstrownum + 1); i <= sheet.lastrownum; i++) { irow row = sheet.getrow(i); datarow datarow = dt.newrow(); for (int j = row.firstcellnum; j < cellcount; j++) { if (row.getcell(j) != null) datarow[j] = row.getcell(j).tostring(); } dt.rows.add(datarow); } return dt; }
2.使用c#发射方式调用excel进行,不需要引用excel.dll组件。此种方法不建议,太麻烦,也需要安装office。
调用方法如下:
private void export2excel(datagridview datagridview, bool captions) { object objapp_late; object objbook_late; object objbooks_late; object objsheets_late; object objsheet_late; object objrange_late; object[] parameters; string[] headers = new string[datagridview.displayedcolumncount(true)]; string[] columns = new string[datagridview.displayedcolumncount(true)]; string[] colname = new string[datagridview.displayedcolumncount(true)]; int i = 0; int c = 0; int m = 0; for (c = 0; c < datagridview.columns.count; c++) { for (int j = 0; j < datagridview.columns.count; j++) { datagridviewcolumn tmpcol = datagridview.columns[j]; if (tmpcol.displayindex == c) { if (tmpcol.visible) //不显示的隐藏列初始化为tag=0 { headers[c - m] = tmpcol.headertext; i = c - m + 65; columns[c - m] = convert.tostring((char)i); colname[c - m] = tmpcol.name; } else { m++; } break; } } } try { // get the class type and instantiate excel. type objclasstype; objclasstype = type.gettypefromprogid("excel.application"); objapp_late = activator.createinstance(objclasstype); //get the workbooks collection. objbooks_late = objapp_late.gettype().invokemember("workbooks", bindingflags.getproperty, null, objapp_late, null); //add a new workbook. objbook_late = objbooks_late.gettype().invokemember("add", bindingflags.invokemethod, null, objbooks_late, null); //get the worksheets collection. objsheets_late = objbook_late.gettype().invokemember("worksheets", bindingflags.getproperty, null, objbook_late, null); //get the first worksheet. parameters = new object[1]; parameters[0] = 1; objsheet_late = objsheets_late.gettype().invokemember("item", bindingflags.getproperty, null, objsheets_late, parameters); if (captions) { // create the headers in the first row of the sheet for (c = 0; c < datagridview.displayedcolumncount(true); c++) { //get a range object that contains cell. parameters = new object[2]; parameters[0] = columns[c] + "1"; parameters[1] = missing.value; objrange_late = objsheet_late.gettype().invokemember("range", bindingflags.getproperty, null, objsheet_late, parameters); //write headers in cell. parameters = new object[1]; parameters[0] = headers[c]; objrange_late.gettype().invokemember("value", bindingflags.setproperty, null, objrange_late, parameters); } } // now add the data from the grid to the sheet starting in row 2 for (i = 0; i < datagridview.rowcount; i++) { c = 0; foreach (string txtcol in colname) { datagridviewcolumn col = datagridview.columns[txtcol]; if (col.visible) { //get a range object that contains cell. parameters = new object[2]; parameters[0] = columns[c] + convert.tostring(i + 2); parameters[1] = missing.value; objrange_late = objsheet_late.gettype().invokemember("range", bindingflags.getproperty, null, objsheet_late, parameters); //write headers in cell. parameters = new object[1]; //parameters[0] = datagridview.rows[i].cells[headers[c]].value.tostring(); parameters[0] = datagridview.rows[i].cells[col.name].value.tostring(); objrange_late.gettype().invokemember("value", bindingflags.setproperty, null, objrange_late, parameters); c++; } } } //return control of excel to the user. parameters = new object[1]; parameters[0] = true; objapp_late.gettype().invokemember("visible", bindingflags.setproperty, null, objapp_late, parameters); objapp_late.gettype().invokemember("usercontrol", bindingflags.setproperty, null, objapp_late, parameters); } catch (exception theexception) { string errormessage; errormessage = "error: "; errormessage = string.concat(errormessage, theexception.message); errormessage = string.concat(errormessage, " line: "); errormessage = string.concat(errormessage, theexception.source); messagebox.show(errormessage, "error"); } }
microsoft.office.interop.excel.application obj = activator.createinstance(exceltype) as microsoft.office.interop.excel.application;
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
下一篇: 他是如何绕过注册10字符限制的?