Home>

problem:Recently encountered in the project,Different clients install different office versions, and an error occurs when exporting excel.

Can't find excel com component, error message is as follows.

Could not load file or assembly "microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c" or one of its dependencies.The system can not find the file specified.

Solution:

1. Reference the higher version of excel.dll components, the latest version 14.0.0 prevents customers from installing higher versions such as office cannot export.

(Dll components can be compatible with lower versions,Not compatible with higher versions)

2. Right-click the dll attribute and insert the referenced excel.dll component into the interop type as true and the specific version=false. This step is very critical.

When the embedded interoperation type is changed to true, the existing code that calls excel may generate an error when generating,Quote the microsoft.csharp namespace,Can resolve this issue.

3. Reference excel 14.0.0 dll component method,vs2012 right click add reference->assembly->extension->microsoft.office.interop.excel

Other methods:

1. Use npoi.dll open source components,You can read and write excel files without installing office software.

The calling method is as follows:

Export code:

///<summary>
 ///datatable export to excel's memorystream export ()
 ///</summary>
 ///<param name="dtsource">datatable data source</param>
 ///<param name="strheadertext">excel header text (example:vehicle list)
 public static memorystream export (datatable dtsource, string strheadertext)
 {
 hssfworkbook workbook=new hssfworkbook ();
 isheet sheet=workbook.createsheet ();
 #region Right-click file attribute information
 {
  documentsummaryinformation dsi=propertysetfactory.createdocumentsummaryinformation ();
  dsi.company="npoi";
  workbook.documentsummaryinformation=dsi;
  summaryinformation si=propertysetfactory.createsummaryinformation ();
  si.author="File author information";//Add xls file author information
  si.applicationname="Create application information";//Add xls file to create application information
  si.lastauthor="last saver information";//add the last saver information of the xls file
  si.comments="Author Information";//Add xls file author information
  si.title="Title Information";//Add XLS File Title Information
  si.subject="Subject information";//Add file subject information
  si.createdatetime=system.datetime.now;
  workbook.summaryinformation=si;
 }
 #endregion
 icellstyle datestyle=workbook.createcellstyle ();
 idataformat format=workbook.createdataformat ();
 datestyle.dataformat=format.getformat ("yyyy-mm-dd");
 //Get column width
 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 create a new table, populate the header,Fill the column headers,style
  if (rowindex == 65535 || rowindex == 0)
  {
  if (rowindex!=0)
  {
   sheet=workbook.createsheet ();
  }
  #region Headers and styles
  {
   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 Column Header and Style
  {
   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;
   //Set the column width
   sheet.setcolumnwidth (column.ordinal, (arrcolwidth [column.ordinal] + 1) * 256);
   }
  }
  #endregion
  rowindex=2;
  }
  #endregion
  #region stuff
  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"://String type
   newcell.setcellvalue (drvalue);
   break;
   case "system.datetime"://date type
   system.datetime datev;
   system.datetime.tryparse (drvalue, out datev);
   newcell.setcellvalue (datev);
   newcell.cellstyle=datestyle;//formatted display
   break;
   case "system.boolean"://Boolean
   bool boolv=false;
   bool.tryparse (drvalue, out boolv);
   newcell.setcellvalue (boolv);
   break;
   case "system.int16"://integer
   case "system.int32":
   case "system.int64":
   case "system.byte":
   int intv=0;
   int.tryparse (drvalue, out intv);
   newcell.setcellvalue (intv);
   break;
   case "system.decimal"://float
   case "system.double":
   double doubv=0;
   double.tryparse (drvalue, out doubv);
   newcell.setcellvalue (doubv);
   break;
   case "system.dbnull"://null processing
   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;
 }
 }

Import code:

///<summary>
 ///read excel, default first line header
 ///</summary>
 ///<param name="strfilename">excel document path</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. Use c#launch mode to call excel without reference to excel.dll component. This method is not recommended,Too much trouble, also need to install office.

The calling method is as follows:

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) //Hidden columns that are not displayed are initialized to 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;
c
  • Previous jQuery get and set the height of the iframe
  • Next Python scraping framework Scrapy architecture