你的位置:首页 > ASP.net教程

[ASP.net教程]让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,这一步非常关键。

    改成True后,生成时可能现有调用Excel的代码会报错,引用Microsoft.CSharp 命名空间,可以解决此问题。

      3.引用Excel 14.0.0 DLL组件方法,vs2012 右键添加引用->程序集->扩展->Microsoft.Office.Interop.Excel

其他方法:

  1.使用NPOI.DLL开源组件,可以不安装Office软件,进行读写Excel文件。

调用方法如下:

导出:

 

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;    }  }

 

导入:

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 private void Export2Excel(DataGridView datagridview, bool captions) 2     { 3       object objApp_Late; 4       object objBook_Late; 5       object objBooks_Late; 6       object objSheets_Late; 7       object objSheet_Late; 8       object objRange_Late; 9       object[] Parameters; 10  11       string[] headers = new string[datagridview.DisplayedColumnCount(true)]; 12       string[] columns = new string[datagridview.DisplayedColumnCount(true)]; 13       string[] colName = new string[datagridview.DisplayedColumnCount(true)]; 14  15       int i = 0; 16       int c = 0; 17       int m = 0; 18  19       for (c = 0; c < datagridview.Columns.Count; c++) 20       { 21         for (int j = 0; j < datagridview.Columns.Count; j++) 22         { 23           DataGridViewColumn tmpcol = datagridview.Columns[j]; 24           if (tmpcol.DisplayIndex == c) 25           { 26             if (tmpcol.Visible) //不显示的隐藏列初始化为tag=0 27             { 28               headers[c - m] = tmpcol.HeaderText; 29               i = c - m + 65; 30               columns[c - m] = Convert.ToString((char)i); 31               colName[c - m] = tmpcol.Name; 32             } 33             else 34             { 35               m++; 36             } 37             break; 38           } 39         } 40       } 41  42       try 43       { 44         // Get the class type and instantiate Excel. 45         Type objClassType; 46         objClassType = Type.GetTypeFromProgID("Excel.Application"); 47         objApp_Late = Activator.CreateInstance(objClassType); 48         //Get the workbooks collection. 49         objBooks_Late = objApp_Late.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, objApp_Late, null); 50         //Add a new workbook. 51         objBook_Late = objBooks_Late.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, objBooks_Late, null); 52         //Get the worksheets collection. 53         objSheets_Late = objBook_Late.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, objBook_Late, null); 54         //Get the first worksheet. 55         Parameters = new Object[1]; 56         Parameters[0] = 1; 57         objSheet_Late = objSheets_Late.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, objSheets_Late, Parameters); 58  59         if (captions) 60         { 61           // Create the headers in the first row of the sheet 62           for (c = 0; c < datagridview.DisplayedColumnCount(true); c++) 63           { 64             //Get a range object that contains cell. 65             Parameters = new Object[2]; 66             Parameters[0] = columns[c] + "1"; 67             Parameters[1] = Missing.Value; 68             objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, Parameters); 69             //Write Headers in cell. 70             Parameters = new Object[1]; 71             Parameters[0] = headers[c]; 72             objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objRange_Late, Parameters); 73           } 74         } 75  76         // Now add the data from the grid to the sheet starting in row 2 77         for (i = 0; i < datagridview.RowCount; i++) 78         { 79           c = 0; 80           foreach (string txtCol in colName) 81           { 82             DataGridViewColumn col = datagridview.Columns[txtCol]; 83             if (col.Visible) 84             { 85               //Get a range object that contains cell. 86               Parameters = new Object[2]; 87               Parameters[0] = columns[c] + Convert.ToString(i + 2); 88               Parameters[1] = Missing.Value; 89               objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, Parameters); 90               //Write Headers in cell. 91               Parameters = new Object[1]; 92               //Parameters[0] = datagridview.Rows[i].Cells[headers[c]].Value.ToString(); 93               Parameters[0] = datagridview.Rows[i].Cells[col.Name].Value.ToString(); 94               objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objRange_Late, Parameters); 95               c++; 96             } 97  98           } 99         }100 101         //Return control of Excel to the user.102         Parameters = new Object[1];103         Parameters[0] = true;104         objApp_Late.GetType().InvokeMember("Visible", BindingFlags.SetProperty,105         null, objApp_Late, Parameters);106         objApp_Late.GetType().InvokeMember("UserControl", BindingFlags.SetProperty,107         null, objApp_Late, Parameters);108       }109       catch (Exception theException)110       {111         String errorMessage;112         errorMessage = "Error: ";113         errorMessage = String.Concat(errorMessage, theException.Message);114         errorMessage = String.Concat(errorMessage, " Line: ");115         errorMessage = String.Concat(errorMessage, theException.Source);116 117         MessageBox.Show(errorMessage, "Error");118       }119     }