导出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);
    }