EXCEL导入导出！

    操作EXCEL一般分两种，其一就是用类似连接数据库的方式，将EXCEL文件当作小型数据库操作，这种方式我想大多数程序员都很喜欢；另一种就是用Microsoft.Office.Interop.Excel组件，这种方式操作的时候比较细致。

    比较：

    第一种：适合读取EXCEL文件，很方便，直接就可将sheet的内容转为DATATABLE;当然它也可以将DATATABLE进行导出，生成EXCEL文件。这种导出是利用

'select .....into [excel连接的文件] from 数据库'的形式，一看便知道其实很局限，必须连接数据库，从数据库中查询出一个DATATABLE才能将整表生成EXCEL文件。

   第二种：显然在读取文件的方面不如第一种；但是在将DATATABLE导出生成为EXCEL文件这一步，很强大，效果也很好。

以下是自己总结的几个方法：

1.读取EXCEL文件

 //返回所有sheet名
       public string[] ImportExcel(string filePath)
       {
           string strConn = FatherForm.xml._excConn1 + filePath + FatherForm.xml._excConn2;
           OleDbConnection conn = new OleDbConnection(strConn);
           conn.Open();

           DataTable dt=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"table"});
           int len=dt.Rows.Count;
           string[] sheetNames = new string[len];
           int start;
           for (start = 0; start < len; start++)
           {
               string allName=dt.Rows[start]["TABLE_NAME"].ToString();
               sheetNames[start] = allName.Replace("$","");//remove $ sysmbol
           }          
           conn.Close();
           return sheetNames;
       }
       //以datatable的形式返回sheet的内容
       public DataTable ShowSheet(string filePath, string sheetName)
       {
           string strConn = FatherForm.xml._excConn1 + filePath + FatherForm.xml._excConn2;
           OleDbConnection conn = new OleDbConnection(strConn);
           conn.Open();
           string sql = "select * from [" + sheetName + "$]";
           OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
           DataTable dt = new DataTable();
           da.Fill(dt);
           conn.Close();
           return dt;
       }

2.将EXCEL文件读取后存储到数据库

    这个必须得说一下，在这个步骤里方法很多，最常用的就是InsertCommand,SqlDataAdapter.Update,SqlBulkCopy，这三种里面我实现了后两种，然后只用了第二种。
    2.1Adapter.Update

 //这个办法可以将datatable存储至数据库，格式不一样也没事，要么补齐（这里采用补齐），要么就在数据库可空字段显示为null
       public bool ImportToDataBase(DataTable dt, string sql, string destinationTatbleName,string whichOperator)
       {
           if (dt == null || dt.Rows.Count == 0)
           {
               return false;
           }
           else
           {
               string strConn = FatherForm.xml._dbConn;
               SqlConnection conn = new SqlConnection(strConn);
               conn.Open();
              
               SqlDataAdapter da = new SqlDataAdapter(sql,conn);
               SqlCommandBuilder comm = new SqlCommandBuilder(da);
               DataTable table = new DataTable();
               da.Fill(table);

               int start;
               for (start = 0; start < dt.Rows.Count; start++)
               { //KHMC,YHDM,GSMC,NSRSBH,ZCDZ,LXDH,SSBM,KHHMC,YHZH,REMARK,POSTADDRESS
                   DataRow dr = table.NewRow();
                   dr["SEARIL"] =Guid.NewGuid().ToString("N").ToUpper();
                   dr["OPERATOR"] =whichOperator;
                   for (int i = 0; i < dt.Columns.Count; i++)
                   {
                       dr[i+1]=dt.Rows[start][i].ToString();
                   }
                       table.Rows.Add(dr.ItemArray);
               }
               da.Update(table);
               return true;
           }
       }

很明显，这种方式批量插入时需要先sql一下原始数据库用以获得dataset(或者datareader、datatable，总之就是数据源啦),然后将新得到的datatable(比如从EXCEL文件的sheet1中得到的)嫁接到之前的数据源上，更新即可。效果一般，我数据不大，也感觉不出来。


    2.2SqlBulkCopy

 //这个办法可以将datatable存储至数据库，但是导入的datatable的结构必须与数据库表的结构一致，比较严格，但效率好，暂时没用到
       public bool ImportToDataBase(DataTable  dt,string destinationTatbleName)
       {
           if (dt == null || dt.Rows.Count==0)
           {
               return false;
           }
           else
           {
               string strConn = FatherForm.xml._dbConn;
               SqlBulkCopy bkc = new SqlBulkCopy(strConn, SqlBulkCopyOptions.UseInternalTransaction);
               bkc.DestinationTableName = destinationTatbleName;
               bkc.WriteToServer(dt);           
               return true;
           }
       }

这种方式很牛逼，若有两个地方的数据库表结构一致，并且希望数据从一个地方COPY到另一个地方的的话用这个办法最好。但是没仔细研究到底能不能结构不一样的进行writetoserver，实在没时间，如果可以那这确实是很优秀的方式。

3.导出生成EXCEL文件

  方法1.

  

 //该方法可已经将datatable导出成excel，但是在导出完成的时候会弹出保存框，效果不很好
       public bool ExportExcel(DataTable dtSource,string filePath)
       {
           if (dtSource.Rows.Count == 0)
           {
               return false;
           }
           else
           {
               Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
               excel.Application.Workbooks.Add(true);
               excel.Visible = true;
               int row, colu;
               for (row = 0; row < dtSource.Rows.Count; row++)
               {
                   for (colu = 0; colu < dtSource.Columns.Count; colu++)
                   {
                       excel.Cells[row + 1, colu + 1] = dtSource.Rows[row][colu].ToString();
                   }
               }
               excel.DisplayAlerts = false;
               excel.AlertBeforeOverwriting = false;
               excel.Application.Workbooks.Add(true).Save();
               excel.Save(filePath);
               excel.Quit();
               excel = null;
               return true;
           }

    方法2.

  
  //导出按钮
        private void btnExport_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFile = new SaveFileDialog();
            saveFile.Filter = ("Excel2003文件(*.xls)|*.xls|Excel2007文件(*.xlsx)|*.xlsx");
            if (saveFile.ShowDialog() == DialogResult.OK)
            {
                string filePath = saveFile.FileName;
                if (System.IO.File.Exists(filePath))
                {
                    System.IO.File.Delete(filePath);
                }
                bool status = exc.ExportExcel(this.dataGridViewX1, filePath);
                if (!status)
                    MessageBox.Show("空文件无法导出！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                else
                    MessageBox.Show("已成功导出！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
//该方法是目前测试用的最好的方法，保存时可以传递存储路径，不会弹出保存框
       public bool ExportExcel(DataGridViewX dtSource, string saveFileName)
       {
           DataTable dt = (DataTable)dtSource.DataSource;
           if (dt.Rows.Count == 0||dt==null)
           {
               return false;
           }
           else
           {
               Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
               Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
               Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//创建sheet
               Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//写死，只操作sheet1
               //添加列标题
               for (int i = 0; i < dt.Columns.Count; i++)
               {
                   worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
               }
               //添加内容
               for (int r = 0; r < dt.Rows.Count; r++)
               {
                   for (int i = 0; i < dt.Columns.Count; i++)
                   {
                       worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
                   }
                   System.Windows.Forms.Application.DoEvents();
               }
               worksheet.Columns.EntireColumn.AutoFit();//自适应列宽，很实用
               Microsoft.Office.Interop.Excel.Range rg = worksheet.Range[worksheet.Cells[2, 2], worksheet.Cells[dt.Rows.Count + 1, 2]];
               rg.NumberFormat = "00000000";
               if (saveFileName != "")
               {
                   try
                   {
                       workbook.Saved = true;
                       workbook.SaveCopyAs(saveFileName);
                   }
                   catch (Exception ex)
                   {
                       throw new Exception(ex.Message);
                   }
               }
               xlApp.Quit();
               GC.Collect();
               return true;
           }
       }

肯定还有其他办法，再次mark。

补充：

1.报错“未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序。

解决办法：下载office2007驱动程序http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe

2.VS自带的组件为MS EXCEL 12.O OBJECT LIBARAY，2007基本兼容所有2003操作，所以主要2007搞定了，低版本肯定没问题。