问题：我有一个邮编是字段是VARCHAR，有0开头的邮编，导成EXCEL出来后，前面的0没了？
1.导出时，在字符串的数据前面加多个'号
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);
}
2.
你的问题主要是在导入到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;
}
3.
导出用 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);
}
}
1.导出时，在字符串的数据前面加多个'号
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); }2.
你的问题主要是在导入到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; }3.
导出用 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);
}
}
