导出EXCEL文件后，前面第一个0没有了？

Question

我有一个邮编是字段是VARCHAR，有0开头的邮编，导成EXCEL出来后，前面的0没了？这个问题大家是怎么解决的？

Answer 1

同意楼上的做法，我也是这么做的，导入到Excel中的这种数字型的字符串信息就被认为是数字了，第一个0被省略掉了

Answer 2

二楼正解，这和excel显示数据有关。
excel只有“’00000122”才会显示00000122，如果不加'会被认为是数字而把前面的0去掉。

Answer 3

难道就是传说中的这样：SELECT '''' + GWR AS Expr1
FROM Tab？

Answer 4

 public bool ExportDataGridview(DataGridView gridView, bool isShowExcele)
        {
            if (gridView.Rows.Count == 0)
            {
                return false;
            }
            //建立Excel对象
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcele;
            int rowCount = gridView.Rows.Count;
            int colCount = gridView.Columns.Count;
            object[,] dataArray = new object[rowCount, colCount];
            for (int i = 0; i < gridView.ColumnCount; i++)
            {
                dataArray[0, i] = gridView.Columns[i].HeaderText;
            }
            for (int i = 0; i < rowCount-1; i++)
            {
                for (int j = 0; j < colCount; j++)
                {
                    if (gridView[j, i].Value.GetType() == typeof(string))
                    {
                        dataArray[i+1, j] = "'" + gridView[j,i].Value.ToString();//在字符型的变量前加'
                    }
                    else
                    {
                        dataArray[i+1, j]  = gridView[j, i].Value.ToString();
                    }
                }
            }
            excel.get_Range("A1", excel.Cells[rowCount, colCount]).Value2 = dataArray;
            excel.Cells.EntireColumn.AutoFit();
            return true;
        }        private void button1_Click(object sender, EventArgs e)
        {
            this.ExportDataGridview(dataGridView1, true);
        }

Answer 5

不是每一列都加上引号。
先判断在gridView中的值的类型。

Answer 6

你的问题主要是在导入到EXCEL的时候，EXCEL默认单元格格式是通用格式不是文本格式，那么“01”就会变成“1”；
如果你是导出到模版那么把模版那列设置成文本就可以解决。如人是动态，上面是中方法。也可以在导出前先设置你的
EXCEL单元格格式。办法如下：
 worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).NumberFormat = "@";//文本格式
//以下是水平居中和垂直居中设置
worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;呵呵，下面给出其他的格式设置 switch (DG.Columns[i].ValueType.ToString())
                            {
                                case "System.String":
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).NumberFormat = "@";
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                                    break;
                                case "System.Decimal":
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                                    break;
                                case "System.DateTime":
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).NumberFormat = "yyyy-mm-dd";
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                                    break;
                                default:
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).NumberFormatLocal = "G/通用格式";
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                                    worksheet.get_Range(worksheet.Cells[起始单元格列索引,起始单元格行索引], worksheet.Cells[终止单元格列索引,终止单元格行索引]).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                                    break;
                            }

Answer 7

 导出用 XML 然后改成XLS就行了.
    //---将表格写成XML文件---不用样式表
         public static void ExportXls(DataGridView dgv,string filename)
        {
            try
            {
                filename = filename.Replace("xls", "xml"); ;
                System.Xml.XmlTextWriter writer = null;
                writer = new System.Xml.XmlTextWriter(filename, null);
                ////为使文件易读，使用缩进
                writer.Formatting = System.Xml.Formatting.Indented;                ////写XML声明
                writer.WriteStartDocument(true);                //写入注释
               // writer.WriteComment("xml To xls ＝ xml文件转换成xls文件");                writer.WriteStartElement("Workbook");
                writer.WriteAttributeString("xmlns:msxsl", "urn:schemas-microsoft-com:xslt");
                writer.WriteAttributeString("xmlns:user", "urn:my-scripts");
                writer.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
                writer.WriteAttributeString("xmlns:o", "urn:schemas-microsoft-com:office:office");
                writer.WriteAttributeString("xmlns:x", "urn:schemas-microsoft-com:office:excel");
                writer.WriteAttributeString("xmlns:ss", "urn:schemas-microsoft-com:office:spreadsheet");
                writer.WriteAttributeString("xmlns:html", "http://www.w3.org/TR/REC-html40");
                //--单元格格式---
                writer.WriteStartElement("Styles");
                writer.WriteStartElement("Style");
                writer.WriteAttributeString("ss:ID", "s0");
                //--以下是对齐样式
                writer.WriteStartElement("Alignment");
                writer.WriteAttributeString("ss:Horizontal", "Center");
                writer.WriteAttributeString("ss:Vertical", "Bottom");
                writer.WriteEndElement();
                //--以下是字体样式
                writer.WriteStartElement("Font");
                writer.WriteAttributeString("ss:Bold", "1"); //黑体
                writer.WriteEndElement();                writer.WriteEndElement();    //endStyle
                writer.WriteEndElement();    //endStyles
                //------------------------------设置工作表-----------
                writer.WriteStartElement("Worksheet");
                ////属性
                writer.WriteAttributeString("ss:Name", "sheet1"); //sheet1的名字
                writer.WriteStartElement("Table");
                writer.WriteAttributeString("ss:ExpandedColumnCount", dgv.Columns.Count.ToString ());                //---设置每列的宽度－－－
                for(int i=0;i<dgv.Columns.Count;i++)
                {
                    writer.WriteStartElement("Column");
                    writer.WriteAttributeString("ss:AutoFitWidth", "0");
                    writer.WriteAttributeString("ss:Width", "100");               
                    writer.WriteEndElement();
                }
                //--列头字段
                writer.WriteStartElement("Row");
                for (int i = 0; i < dgv.Columns.Count;i++ )
                {
                    writer.WriteStartElement("Cell");
                    writer.WriteAttributeString("ss:StyleID", "s0");
                    writer.WriteStartElement("Data");
                    writer.WriteAttributeString("ss:Type", "String");
                    writer.WriteValue(dgv.Columns[i].HeaderText);
                    writer.WriteEndElement();
                    writer.WriteEndElement();//endCell
                }
                writer.WriteEndElement();//endRow
                //－－数据字段
                for (int i = 0; i < dgv.Rows .Count ;i++ )
                {
                    writer.WriteStartElement("Row");                    for(int j=0;j<dgv.Columns .Count ;j++ )
                    {
                        writer.WriteStartElement("Cell");
                        writer.WriteStartElement("Data");
                        writer.WriteAttributeString("ss:Type", "String");
                        writer.WriteValue(dgv.Rows[i].Cells [j].Value .ToString ());
                        writer.WriteEndElement();
                        writer.WriteEndElement();
                    }
                    writer.WriteEndElement();
                }
                writer.WriteEndDocument();//endTable                //缓冲器内的内容写入文件
                writer.Flush();
                writer.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "创建XML出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (!System.IO.File.Exists(filename))
            {
                return;
            }
            //--以下是给文件改名
            System.Diagnostics.Process p2 = new System.Diagnostics.Process();
            p2.StartInfo.FileName = "cmd.exe";
            string newfile = filename.Substring(filename.LastIndexOf('\\') + 1).Replace("xml", "xls");
            string command = "ren  \"" + filename + "\" " + newfile;
            p2.StartInfo.Arguments = "/c " + command;
            p2.StartInfo.UseShellExecute = false;
            p2.StartInfo.RedirectStandardInput = true;   //重定向標準輸入
            p2.StartInfo.RedirectStandardOutput = true;  //重定向標準輸出
            p2.StartInfo.RedirectStandardError = true;   //重定向錯誤輸出
            p2.StartInfo.CreateNoWindow = true;          //設置不顯示窗口
            p2.Start();
            // p2.StandardInput.WriteLine(command);     //写上这句的话， p2.StartInfo.Arguments不要了
            // p2.StandardInput.WriteLine("exit");      //多条语句的话，一定要写上这句
            //MessageBox.Show(p2.StandardOutput.ReadToEnd()); //参数显示
            MessageBox.Show("导出成功！", "表格导出到文件",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);        }
    }

Answer 8

有个方法，设定StringWriter tw的写入方式
string strStyle = "<style>td{mso-number-format:\"\\@\";}</style>";//写入方式
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
tw.WriteLine(strStyle);//使用定义的strStyle方式写入
this.GridView1.RenderControl(hw);