正文

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

︶ㄣ米米素材网 メ
米米素材网V管理员 / 阅读
0708

让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：

其他方法：
1.使用NPOI.DLL开源组件，可以不安装Office软件，进行读写Excel文件。
NPIO.dll：  

调用方法如下： 

导出代码：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
/// <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;
 }
 }

导入代码：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
/// <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。
调用方法如下：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
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");
  }
 }

 

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

 

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持米米素材网。