让C# Excel导入导出 支持不同版本Office

概述问题：最近在项目中遇到，不同客户机安装不同Office版本，在导出Excel时，发生错误。

问题：最近在项目中遇到，不同客户机安装不同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,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,objBooks_Late,null);  //Get the worksheets collection.  obJsheets_Late = objBook_Late.GetType().InvokeMember("Worksheets",objBook_Late,null);  //Get the first worksheet.  Parameters = new Object[1];  Parameters[0] = 1;  obJsheet_Late = obJsheets_Late.GetType().InvokeMember("Item",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",obJsheet_Late,Parameters);   //Write headers in cell.   Parameters = new Object[1];   Parameters[0] = headers[c];   objRange_Late.GetType().InvokeMember("Value",BindingFlags.SetProperty,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",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",Parameters);    c++;   }   }  }  //Return control of Excel to the user.  Parameters = new Object[1];  Parameters[0] = true;  objApp_Late.GetType().InvokeMember("Visible",Parameters);  objApp_Late.GetType().InvokeMember("UserControl",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");  } }

复制代码 代码如下:System.Type ExcelType = System.Type.GetTypeFromProgID("Excel.Application");
Microsoft.Office.Interop.Excel.Application obj = Activator.CreateInstance(ExcelType) as Microsoft.Office.Interop.Excel.Application;

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持编程小技巧。

总结

以上是内存溢出为你收集整理的让C# Excel导入导出 支持不同版本Office全部内容，希望文章能够帮你解决让C# Excel导入导出 支持不同版本Office所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。