Excel导入导出

• 前提：有两个按钮，一个导入，一个导出，DataGridView控件，

• 引用ExcelIO.cs  记得改命名空间　        

•  

•  

  ExcelIO.cs

• using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Data;
  //using Excel = Microsoft.Office.Interop.Excel;
  using System.Data.OleDb;

      //Excel文件导入导出,需引用Microsoft Excel 11.0 Object Library
      /*
               无法嵌入互操作类型“Microsoft.Office.Interop.Excel.ApplicationClass”。请改用适用的接口。

              解决办法是将引用的DLL：Microsoft.Office.Interop.Excel;的嵌入互操作类型改为false,就可以了
       */
  namespace jznZsProject2014.UCardmanage_Z
  {
      class ExcelIO
      {
          private int _ReturnStatus;
          private string _ReturnMessage;
          /// <summary>
          /// 执行返回状态
          /// </summary>
          public int ReturnStatus
          {
              get { return _ReturnStatus; }
          }
          /// <summary>
          /// 执行返回信息
          /// </summary>
          public string ReturnMessage
          {
              get { return _ReturnMessage; }
          }
          public ExcelIO()
          {
          }
          /// <summary>
          /// 导入EXCEL到DataSet
          /// </summary>
          /// <param name="fileName">Excel全路径文件名</param>
          /// <returns>导入成功的DataSet</returns>
          public DataSet ImportExcel(string fileName)
          {
              //判断是否安装EXCEL
              Excel.Application xlApp = new Excel.ApplicationClass();
              if (xlApp == null)
              {
                  _ReturnStatus = -1;
                  _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
                  return null;
              }
              //判断文件是否被其他进程使用
              Excel.Workbook workbook;
              try
              {
                  workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
              }
              catch
              {
                  _ReturnStatus = -1;
                  _ReturnMessage = "Excel文件处于打开状态，请保存关闭";
                  return null;
              }

              //获得所有Sheet名称
              int n = workbook.Worksheets.Count;
              string[] SheetSet = new string[n];
              System.Collections.ArrayList al = new System.Collections.ArrayList();
              for (int i = 1; i <= n; i++)
              {
                  SheetSet[i - 1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name;
              }

              //释放Excel相关对象
              workbook.Close(null, null, null);
              xlApp.Quit();
              if (workbook != null)
              {
                  System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                  workbook = null;
              }
              if (xlApp != null)
              {
                  System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                  xlApp = null;
              }
              GC.Collect();

              //把EXCEL导入到DataSet
              DataSet ds = new DataSet();
              string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";
              using (OleDbConnection conn = new OleDbConnection(connStr))
              {
                  conn.Open();
                  OleDbDataAdapter da;
                  for (int i = 1; i <= n; i++)
                  {
                      string sql = "select * from [" + SheetSet[i - 1] + "$] ";
                      da = new OleDbDataAdapter(sql, conn);
                      da.Fill(ds, SheetSet[i - 1]);
                      da.Dispose();
                  }
                  conn.Close();
                  conn.Dispose();
              }
              return ds;
          }
          /// <summary>
          /// 把DataTable导出到EXCEL
          /// </summary>
          /// <param name="reportName">报表名称</param>
          /// <param name="dt">数据源表</param>
          /// <param name="saveFileName">Excel全路径文件名</param>
          /// <returns>导出是否成功</returns>
          public bool ExportExcel(string reportName, DataTable dt, string saveFileName)
          {
              if(dt==null)
              {
              _ReturnStatus = -1;
              _ReturnMessage = "数据集为空！";
              return false;
              }
              bool fileSaved=false;
              Excel.Application xlApp=new Excel.ApplicationClass();
              if(xlApp==null)
              {
              _ReturnStatus = -1;
              _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
              return false;
              }
              Excel.Workbooks workbooks=xlApp.Workbooks;
              Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
              Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
              worksheet.Cells.Font.Size = 10;
              Excel.Range range;
              long totalCount=dt.Rows.Count;
              long rowRead=0;
              float percent=0;
              worksheet.Cells[1,1]=reportName;
              ((Excel.Range)worksheet.Cells[1,1]).Font.Size = 12;
              ((Excel.Range)worksheet.Cells[1,1]).Font.Bold = true;
              //写入字段
              for(int i=0;i<dt.Columns.Count;i++)
              {
              worksheet.Cells[2,i+1]=dt.Columns[i].ColumnName;
              range=(Excel.Range)worksheet.Cells[2,i+1];
              range.Interior.ColorIndex = 15;
              range.Font.Bold = true;
              }
              //写入数值
              for(int r=0;r<dt.Rows.Count;r++)
              {
              for(int i=0;i<dt.Columns.Count;i++)
              {
              worksheet.Cells[r+3,i+1]=dt.Rows[r][i].ToString();
              }
              rowRead++;
              percent=((float)(100*rowRead))/totalCount;
              }

              range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]);
              range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
              if( dt.Rows.Count > 0)
              {
              range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
              range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
              range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
              }
              if(dt.Columns.Count>1)
              {
              range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic;
              range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
              range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
              }
              //保存文件
              if(saveFileName!="")
              {
              try
              {
              workbook.Saved =true;
              workbook.SaveCopyAs(saveFileName);
              fileSaved=true;
              }
              catch(Exception ex)
              {
              fileSaved=false;
              _ReturnStatus = -1;
              _ReturnMessage = "导出文件时出错,文件可能正被打开！\n"+ex.Message;
              }
              }
              else
              {
              fileSaved=false;
              }

              //释放Excel对应的对象
              if(range != null)
              {
              System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
              range = null;
              }
              if(worksheet != null)
              {
              System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
              worksheet = null;
              }
              if(workbook != null)
              {
              System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
              workbook = null;
              }
              if(workbooks != null)
              {
              System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
              workbooks = null;
              }
              xlApp.Application.Workbooks.Close();
              xlApp.Quit();
              if(xlApp != null)
              {
              System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
              xlApp = null;
              }
              GC.Collect();
              return fileSaved;
          }

      }
  }
  

   

• 导入

• 　　　 if (btnPrint.Text == "打印")
              {
                  btnPrint.Text = "导入数据";
                  openFileDialog1.FileName = "";
                  //openFileDialog1.Filter = "excel表格|*.xls|*.docm|*.dotx|";
                  openFileDialog1.Filter = "文本文件(*.txt)|*.txt|所有文件(*.*)|*.*";
                  if (openFileDialog1.ShowDialog() == DialogResult.OK)
                  {
                      //  File.Copy(openFileDialog1.FileName, Application.StartupPath + "\\" + "" + txtcxxx.Text.Trim(), true);
                      a = openFileDialog1.FileName;
                  }
                 ExcelIO eio = new ExcelIO();
                  // dgvdc.DataSource=eio.ImportExcel("E:\\Book1.xls");
                 dst = eio.ImportExcel(a);
                  // System.Diagnostics.Process.Start(System.Windows.Forms.Application.StartupPath + "\\工作簿.xls");
                  dgvCardHandle.DataSource = dst.Tables[0];
              }
              else
              {
                  int i;

                  for (i = 0; i < dst.Tables[0].Rows.Count; i++)
                  {
                      string bb = dst.Tables[0].Rows[i][0].ToString();
                      if (bbc.daorudaochu(bb))
                      {
                          MessageBox.Show("成功");
                      }
                  }
              }

• 导出 方法一
•          

          string a;
          DataSet dst;
          public void xrsj()
          {
               dt = dgvCardHandle.DataSource as System.Data.DataTable;           //把表格里的数据回传到DataSet中
          }
          System.Data.DataTable dt;

•         public Excel.Sheets ExcelSheets = null;
          public void GetConnect()
          {
              xrsj();
              try
              {
                  Excel.Application excel = new Excel.Application();//开启excel
                //excel.Application.Workbooks.Add(true);
                //ExcelSheets = excel.Worksheets;//建立一个新的工作表
                  ExcelIO eio = new ExcelIO();
                  object missing = System.Reflection.Missing.Value;
                  excel.Visible = true;//excel文件可见
                  Range range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 2]);
                  //设置字体加粗
                  range.Font.Bold = true;
                  //设置字体颜色
                  range.Font.ColorIndex = 0;
                  //设置颜色背景
                  range.Interior.ColorIndex = 15;
                  //设置边框样式
                  range.Borders.LineStyle = XlLineStyle.xlContinuous;
                  SaveFileDialog saveFileDialog = new SaveFileDialog();
                  saveFileDialog.Filter = "Excel?files(*.xls)|*.xls|All?files(*.*)|*.*";
                  saveFileDialog.FilterIndex = 1;
                  saveFileDialog.RestoreDirectory = true;
              //  excel.Workbooks.Close();
              //  excel.Quit();//关闭excel程序
              }
              catch (System.Exception e)
              {
                  System.Console.WriteLine("something?wrong?happened?about?excel?excution?or?dababase?operation?", e);
              }
          }

      private void btnPrint_Click(object sender, EventArgs e)
          {
              GetConnect();
              ExcelIO eio = new ExcelIO();
              eio.ExportExcel(openFileDialog1.SafeFileName,dt,openFileDialog1.FileName);
              System.Diagnostics.Process.Start(openFileDialog1.FileName);
            //  DataGridviewShowToExcel(dgvCardHandle,true); //导出方法二，只把数据显示到Excel中
          }
• 导出方法二，把数据显示到Excel中
          #region DataGridView数据显示到Excel
          /// <summary>
          /// 打开Excel并将DataGridView控件中数据导出到Excel
          /// </summary>
          /// <param name="dgv">DataGridView对象 </param>
          /// <param name="isShowExcle">是否显示Excel界面 </param>
          /// <remarks>
          /// add com "Microsoft Excel 11.0 Object Library"
          /// using Excel=Microsoft.Office.Interop.Excel;
          /// </remarks>
          /// <returns> </returns>
          public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
          {
              if (dgv.Rows.Count == 0)
                  return false;
              //建立Excel对象
              Excel.Application excel = new Excel.Application();
              excel.Application.Workbooks.Add(true);
              excel.Visible = isShowExcle;
              //生成字段名称
              for (int i = 0; i < dgv.ColumnCount; i++)
              {
                  excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
              }
              //填充数据
              for (int i = 0; i < dgv.RowCount - 1; i++)
              {
                  for (int j = 0; j < dgv.ColumnCount; j++)
                  {
                      if (dgv[j, i].ValueType == typeof(string))
                      {
                          excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
                      }
                      else
                      {
                          excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
                      }
                  }
              }
              return true;
          }
          #endregion