导出excel文件 解决科学计数法问题 datagridview导出字符串变数字问题

在winform程序开发时，处理的办法就是在导出的过程中，开始试了 处理excel对象的格式 mysheet.Cells.NumberFormat = "@";
 后来没有成功。最后还是用了逐条纪录进行字符格式转化的方法，即添加“'”.
   

  /// <summary> 
         ///方法，导出DataGridView中的数据到Excel文件 
         /// </summary> 
         /// <remarks>
         /// add com "Microsoft Excel 11.0 Object Library"
         /// using Excel=Microsoft.Office.Interop.Excel;
         /// using System.Reflection;
         /// </remarks>
         /// <param name= "dgv"> DataGridView </param> 
         public static void dv2excel_bar(DataGridView dgv)
         {        #region   验证可操作性
        //申明保存对话框 
         SaveFileDialog dlg = new SaveFileDialog();
         //默然文件后缀 
         dlg.DefaultExt = "xls ";
         //文件后缀列表 
         dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
         //默然路径是系统当前路径 
         dlg.InitialDirectory = Directory.GetCurrentDirectory();
         //打开保存对话框 
         if (dlg.ShowDialog() == DialogResult.Cancel) return;
         //返回文件路径 
         string fileNameString = dlg.FileName;
         //验证strFileName是否为空或值无效 
         if (fileNameString.Trim() == " ")
         { return; }
         //定义表格内数据的行数和列数 
         int rowscount = dgv.Rows.Count;
         int colscount = dgv.Columns.Count;
         //行数必须大于0 
         if (rowscount <= 0)
         {
         MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
         return;
         }        //列数必须大于0 
         if (colscount <= 0)
         {
         MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
         return;
         }        //行数不可以大于65536 
         if (rowscount > 65536)
         {
         MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
         return;
         }        //列数不可以大于255 
         if (colscount > 255)
         {
         MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
         return;
         }        //验证以fileNameString命名的文件是否存在，如果存在删除它 
         FileInfo file = new FileInfo(fileNameString);
         if (file.Exists)
         {
         try
         {
             file.Delete();
         }
         catch (Exception error)
         {
             MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
             return;
         }
         }
         #endregion
         Microsoft.Office.Interop.Excel.Application objExcel = null;
         Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
         Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
         try
         {
         //申明对象 
         objExcel = new Microsoft.Office.Interop.Excel.Application();
         objWorkbook = objExcel.Workbooks.Add(Missing.Value);
         objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
         objsheet.UsedRange.NumberFormatLocal = "@";
           //设置EXCEL不可见 
         objExcel.Visible = false;        向Excel中写入表格的表头 
         int displayColumnsCount = 1;
         //for (int i = 0; i <= dgv.ColumnCount - 1; i++)
         //{
         //    if (dgv.Columns[i].Visible == true)
         //    {
         //        objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
         //        displayColumnsCount++;
         //    }
         //}
         //设置进度条 
         //tempProgressBar.Refresh(); 
         //tempProgressBar.Visible   =   true; 
         //tempProgressBar.Minimum=1; 
         //tempProgressBar.Maximum=dgv.RowCount; 
         //tempProgressBar.Step=1; 
         //向Excel中逐行逐列写入表格中的数据 
         for (int row = 0; row <= dgv.RowCount - 1; row++)
         {
             //tempProgressBar.PerformStep();            displayColumnsCount = 1;
             for (int col = 0; col < colscount; col++)
             {
                 if (dgv.Columns[col].Visible == true)
                 {
                     try
                     {
                         if (col == 2)
                         {
                             objExcel.Cells[row + 2, displayColumnsCount] = "'"+dgv.Rows[row].Cells[col].Value.ToString().Trim();
                         }
                         else
                         {
                             objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
                         }
                         displayColumnsCount++;
                     }
                     catch (Exception)
                     {                    }
                }
             }
         }
         //隐藏进度条 
         //tempProgressBar.Visible   =   false; 
         //保存文件 
         objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                 Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                 Missing.Value, Missing.Value);
         }
         catch (Exception error)
         {
         MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
         return;
         }
         finally
         {
         //关闭Excel应用 
         if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
         if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
         if (objExcel != null) objExcel.Quit();        objsheet = null;
         objWorkbook = null;
         objExcel = null;
         }
         MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);        }
 
        //导出到XML(整个数据源)
        public static void ExportTOXML(DataGridView gridView,string filename)
         {            DataSet objSet = new DataSet();
             DataTable dt = gridView.DataSource as  DataTable;
             if (gridView.Rows.Count == 0)
             {
                 MessageBox.Show("没有数据可供导出！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                 return;
             }
             else
             {
                 //saveFileDialog2.Filter = "XML files (*.xml)|*.xml";
                 //saveFileDialog2.FilterIndex = 0;
                 //saveFileDialog2.RestoreDirectory = true;
                 saveFileDialog2.CreatePrompt = true;
                 //saveFileDialog2.Title = "导出文件保存路径";
                 //saveFileDialog2.FileName = null;
                 //saveFileDialog2.ShowDialog();
                 //string FileName = saveFileDialog2.FileName;
                 objSet.Tables.Add(dt.Copy());
                 if (filename.Length != 0)
                 {
                 
                     objSet.WriteXml(filename);
                  
                     MessageBox.Show("恭喜您!数据初始化已经完成：" + filename, "定稿导出完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
                 
                 }
             }
         }

 
 
  
　　   无论在做web还是在写winform程序是老是在导出excel数据是遇到科学计数法问题，如果字符太长（如身份证号）在导出的excel 文件中就会出现长字符串的科学计数法表示，反复导数据是就会出现错误 。
         我解决的办法是在到处是或者存储将要导出时，每条记录字符串形式处理
    　　在asp.net 中 我一般都是将要导出的数据放到gridview网格里，首先对网格邦定数据时 字符串形式处理，然后再用普通的形　　　　式导出excel就把问题解决了。
   　　 我的代码非常简单：在邦定gridview控件时在rowdatabound事件中队数据格式化        
 protected void gError_RowDataBound(object sender, GridViewRowEventArgs e)
     {
            //1）  文本：vnd.ms-excel.numberformat:@
           //2）  日期：vnd.ms-excel.numberformat:yyyy/mm/dd
          //3）  数字：vnd.ms-excel.numberformat:#,##0.00
          //4）  货币：vnd.ms-excel.numberformat:￥#,##0.00
          //5）  百分比：vnd.ms-excel.numberformat: #0.00%
         for (int i = 0; i < e.Row.Cells.Count; i++)
         {
             if (e.Row.RowType == DataControlRowType.DataRow)
                 e.Row.Cells[i ].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
         }
            
     }
    然后执行到处操作就不会出现问题了
  protected void btnOut_Click(object sender, EventArgs e)
     {
         
        Response.Clear();
        Response.Buffer = true;
         Response.Charset = "GB2312";
        Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
      
         Response.ContentEncoding = System.Text.Encoding.UTF7;
       //设置输出文件类型为excel文件。 
       Response.ContentType = "application/ms-excel";
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.gError.RenderControl(oHtmlTextWriter);
         Response.Output.Write(oStringWriter.ToString());
         Response.Flush();
         Response.End();
     }
  
 public override void VerifyRenderingInServerForm(Control control)
     {
         //base.VerifyRenderingInServerForm(control);
     }