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

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

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

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