导出EXCEL文件后，前面第一个0没有了？
我有一个邮编是字段是VARCHAR，有0开头的邮编，导成EXCEL出来后，前面的0没了？这个问题大家是怎么解决的？
[解决办法]
邮编0开头？？？
导出时，在字符串的数据前面加多个'号就可以了。
[解决办法]
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); }
[解决办法]
写入单元格的时候，前面加一个单引号
[解决办法]
导出用 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);
}
}