Let C # Excel import export support for different versions of office_c# tutorials

problem: recently encountered in the project, different clients install different versions of Office, an error occurred while exporting Excel.
The Excel COM component could not be found with the following error message.
failed to load file or assembly "Microsoft.Office.Interop.Excel, version=12.0.0.0, Culture=neutral, publickeytoken=71e9bce111e9429c "Or one of its dependencies. The system could not find the specified file.

Workaround:

1. Refer to the high version of the Excel.dll component, the latest version 14.0.0 prevent customers from installing a high version such as Office cannot export.
(DLL components can be compatible with low versions, not compatible with high versions)

2. Right-key DLL properties, the Excel.dll component to be referenced, the embedded interop type is true, and the specific version =false. This step is critical.
After the embedding interop type is changed to True, the code that calls excel at the time of the build may complain, referencing the Microsoft.csharp namespace, to resolve the problem.

3. Referencing the Excel 14.0.0 DLL component method, vs2012 right-add Reference-> assembly-> extension->microsoft.office.interop.excel
Excel.dll:http://xiazai.jb51.net/201608/yuanma/excel (jb51.net). rar

Other methods:
1. Using the NPOI.DLL open source component, you can read and write Excel files without installing Office software.
Npio.dll:http://xiazai.jb51.net/201608/yuanma/npoi (jb51.net). rar

The method is invoked as follows:

Export code:

<summary>///DataTable exported to Excel MemoryStream Export ()///</summary>///<param name= "Dtsource" > DataTable Data Source </param>///<param name= "Strheadertext" >excel header text (for example: Vehicle list) </param> public static
 MemoryStream Export (DataTable dtsource, String strheadertext) {Hssfworkbook workbook = new Hssfworkbook (); Isheet sheet = workbook.
 
 Createsheet ();
  #region Right-click the file attribute information {documentsummaryinformation DSi = propertysetfactory.createdocumentsummaryinformation ();
  Dsi.company = "Npoi"; Workbook.

  documentsummaryinformation = DSi;
  SummaryInformation si = propertysetfactory.createsummaryinformation (); Si. Author = "file author information"; Fill in xls file author information si. ApplicationName = "Create program information"; Fill in XLS file creation program information Si. Lastauthor = "Last Saved by information"; Fill in the XLS file last saved by the information si.comments = "Author information"; Fill in xls file author information si. title = "header Information"; Fill in the XLS file header information Si. Subject = "Subject information"//fill in the document 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 int Temp = 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 new table, fill header, fill column header, style if (RowIndex = = 65535 | | rowIndex = 0) {if (R Owindex!= 0) {sheet = workbook.
  Createsheet (); #region Header and Style {IRow HeaderRow = sheet.
   CreateRow (0);
   Headerrow.heightinpoints = 25; Headerrow.createcell (0).

   Setcellvalue (Strheadertext); IcellstyleHeadstyle = 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 headers and styles {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; Sets the column width sheet. Setcolumnwidth (column. Ordinal, (Arrcolwidth[column.
   Ordinal] + 1) * 256);
 }} #endregion RowIndex = 2; #endregion #region Fill content 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;//format shows break;
   Case "System.Boolean"://boolean bool Boolv = FALSE; bool.
   TryParse (Drvalue, out boolv);
   Newcell.setcellvalue (BOOLV);
   Break
   Case "system.int16"://Whole case "System.Int32": Case "System.Int64": Case "System.Byte": int intv = 0; Int.
   TryParse (Drvalue, out INTV);
   Newcell.setcellvalue (INTV);
   Break
   Case "System.Decimal"://Floating-point case "system.double": Double DOUBV = 0; Double.
   TryParse (Drvalue, out DOUBV); Newcell.setcelLValue (DOUBV);
   Break
   Case "System.DBNull"://Null value 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 behavior header///</summary>///<param name= "strFileName" >excel document Path </param >///<returns></returns> public static DataTable Import (String strFileName) {DataTable dt = new Datat

 Able ();
 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 method to invoke Excel without reference to the Excel.dll component. This approach is not recommended, too cumbersome, and requires the installation of Office. The
calls the method 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)//Do not show hidden columns 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 the 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 ' 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");


 }
 }

Copy Code code as follows:

System.Type Exceltype = System.Type.GetTypeFromProgID ("Excel.Application");
Microsoft.Office.Interop.Excel.Application obj = activator.createinstance (exceltype) as Microsoft.Office.Interop.Excel.Application;

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.