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