winform datagrideview导出到excel 账单号如1212121212333333,会被科学计算 如何解决? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 如果把超过15位的文本数字复制到EXCEL后,EXCEL将会自动转为科学计算或指数格式,把数字格式转换为文本或邮编格式后数字15位后即成0显示,导致数字出错,以上情况常在身份证号码在EXCEL中时出现. 我的解决办法如下:1.在表格中加一列,随便加入一个字符,如: a ,然后将有字符的列与数字的列一起复制到.txt文件中,这时会出现字符与数字间存在空格,可以用编辑-替换把空格去除,得到如:a321258.......2.选中字符及数字复制到EXCEL文件中,用SUBSTITUTE函数将字符替换为无字符或空格.即得到所要正确的文本数字.3.复制转换后的数字,选中要粘贴的列,用选择性粘贴中的数值粘贴,即可.或者,在账单好前面加一个 ' ,自动转换为文本。 转换时加上'或者存完excel后打开后选中此列,设置单元格格式为特殊就行了。 //用gridview导出的row.Cells[num].Attributes.Add("style","vnd.ms-excel.numberformat:@"); //如果是代码输出的 xls_sheet.get_Range(xls_exp.Cells[rowindex, colindex], xls_exp.Cells[rowindex, colindex]).NumberFormatLocal = "@"; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.ContentEncoding = Encoding.UTF7; HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("生产单列表.xls")); HttpContext.Current.Response.Charset = "gb2312"; HttpContext.Current.Response.ContentType = "application/vnd.xls"; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); if (ViewState["ProductionBills"] != null) { List<ProductionBill> productionBill = ViewState["ProductionBills"] as List<ProductionBill>; if (productionBill.Count > 0) { gvProductionBills.DataSource = productionBill; gvProductionBills.DataBind(); gvProductionBills.HeaderRow.Cells.RemoveAt(7); gvProductionBills.HeaderRow.Cells.RemoveAt(7); foreach (GridViewRow row in gvProductionBills.Rows) { row.Cells.RemoveAt(7); row.Cells.RemoveAt(7); gvProductionBills.HeaderRow.Cells[2].Text = "下单日期" ; gvProductionBills.HeaderRow.Cells[3].Text = "要求日期"; LinkButton lbtnPNumber = row.FindControl("lbtnPNumber") as LinkButton; Label lblPNumber = new Label(); lblPNumber.Text = lbtnPNumber.Text.Trim(); LinkButton lbtnPRe = row.FindControl("lbtnPRe") as LinkButton; Label lblPRe = new Label(); lblPRe.Text = lbtnPRe.Text.Trim(); row.Cells[6].Controls.Add(lblPRe); row.Cells[6].Controls.Remove(lbtnPRe); row.Cells[6].Text = lbtnPRe.ToolTip; row.Cells[1].Controls.Add(lblPNumber); row.Cells[1].Controls.Remove(lbtnPNumber); row.Cells[1].Text = lbtnPNumber.ToolTip; row.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); } gvProductionBills.RenderControl(htmlWrite); } } HttpContext.Current.Response.Write(stringWrite.ToString()); HttpContext.Current.Response.End(); 擦!没变色,再来一次HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.ContentEncoding = Encoding.UTF7; HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("生产单列表.xls")); HttpContext.Current.Response.Charset = "gb2312"; HttpContext.Current.Response.ContentType = "application/vnd.xls"; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); if (ViewState["ProductionBills"] != null) { List<ProductionBill> productionBill = ViewState["ProductionBills"] as List<ProductionBill>; if (productionBill.Count > 0) { gvProductionBills.DataSource = productionBill; gvProductionBills.DataBind(); gvProductionBills.HeaderRow.Cells.RemoveAt(7); gvProductionBills.HeaderRow.Cells.RemoveAt(7); foreach (GridViewRow row in gvProductionBills.Rows) { row.Cells.RemoveAt(7); row.Cells.RemoveAt(7); gvProductionBills.HeaderRow.Cells[2].Text = "下单日期" ; gvProductionBills.HeaderRow.Cells[3].Text = "要求日期"; LinkButton lbtnPNumber = row.FindControl("lbtnPNumber") as LinkButton; Label lblPNumber = new Label(); lblPNumber.Text = lbtnPNumber.Text.Trim(); LinkButton lbtnPRe = row.FindControl("lbtnPRe") as LinkButton; Label lblPRe = new Label(); lblPRe.Text = lbtnPRe.Text.Trim(); row.Cells[6].Controls.Add(lblPRe); row.Cells[6].Controls.Remove(lbtnPRe); row.Cells[6].Text = lbtnPRe.ToolTip; row.Cells[1].Controls.Add(lblPNumber); row.Cells[1].Controls.Remove(lbtnPNumber); row.Cells[1].Text = lbtnPNumber.ToolTip; row.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); } gvProductionBills.RenderControl(htmlWrite); } } HttpContext.Current.Response.Write(stringWrite.ToString()); HttpContext.Current.Response.End(); 找了个类还是不行我的是用流写的加'效果不好,看还有别的方法没/// <summary> /// 将DataGridView中的数据导出到Excel中,并加载显示出来(无加载模板) /// 只用于一般的导出Excel /// </summary> /// <param name="caption">要显示的页头</param> /// <param name="date">打印日期</param> /// <param name="dgv">要进行导出的DataGridView</param> public static void ExportToExcel(string caption, string date, DataGridView dgv) { //Microsoft.Office.Interop.Excel.Range range = null; //range.NumberFormatLocal = "@"; //DataGridView可见列数 int visiblecolumncount = 0; for (int i = 0; i < dgv.Columns.Count; i++) { if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn)) { visiblecolumncount++; } } try { //当前操作列的索引 int currentcolumnindex = 1; //当前操作行的索引 Microsoft.Office.Interop.Excel.ApplicationClass Mylxls = new Microsoft.Office.Interop.Excel.ApplicationClass(); Mylxls.Application.Workbooks.Add(true); //Mylxls.Cells.Font.Size = 10.5; //设置默认字体大小 //设置标头 Mylxls.Caption = caption; //显示表头 Mylxls.Cells[1, 1] = caption; //显示时间 Mylxls.Cells[2, 1] = date; for (int i = 0; i < dgv.Columns.Count; i++) { if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn)) //如果显示 { Mylxls.Cells[3, currentcolumnindex] = dgv.Columns[i].HeaderText; Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //设置边框 Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).ColumnWidth = dgv.Columns[i].Width / 8; //Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //粗体 //Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中显示 currentcolumnindex++; } } Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //合并单元格 Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30; //行高 //Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "黑体"; //Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14; //字体大小 Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中显示 Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //合并 Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //左边显示 //Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12; //列宽度 object[,] dataArray = new object[dgv.Rows.Count, visiblecolumncount]; //当前操作列的索引 //int currentcolumnindex = 1; //当前操作行的索引 for (int i = 0; i < dgv.Rows.Count; i++) //循环填充数据 { currentcolumnindex = 1; for (int j = 0; j < dgv.Columns.Count; j++) { if (dgv.Columns[j].Visible == true && (dgv.Columns[j] is DataGridViewTextBoxColumn)) { if (dgv[j, i].Value != null) //如果单元格内容不为空 { dataArray[i, currentcolumnindex - 1] = dgv[j, i].Value.ToString(); } currentcolumnindex++; } } } Mylxls.get_Range(Mylxls.Cells[4, 1], Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Value2 = dataArray; //设置边框 Mylxls.get_Range(Mylxls.Cells[4, 1], Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Cells.Borders.LineStyle = 1; //设置边框 Mylxls.Visible = true; } catch(Exception ex) { //MessageBox.Show(ex.ToString()); MessageBox.Show("信息导出失败,请确认你的机子上装有Microsoft Office Excel 2007!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { } } 按照你的写法:Mylxls.get_Range(Mylxls.Cells[4, 1],Mylxls.Cells[dgv.Rows.Count + 3], visiblecolumncount]).NumberFormatLocal = "@";//设置文本格式 Mylxls.get_Range(Mylxls.Cells[4, 1],Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).NumberFormatLocal = "@";//设置文本格式 设置它的最佳宽度呢?Mylxls.get_Range(Mylxls.Cells[4, 1],Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Columns.AutoFit(); 我知道了,刚刚自己写过的,刚好我也是这个问题你在你要写入的单元格数据前加'比如:你的Excel列="'"+你的数据 简单点,将那个数字转化为字符串后才加载到excel 比如你的方式:Mylxls.Cells[行,列]="'"+DataTable.Rows[下标][下标].ToString();绝对可以 水晶报表老是只显示一半 如何计算渐变色中某一点的具体颜色 C#2005 父窗体与子窗体的问题 PB能调用c#写的.dll吗? 一个Socket的问题,请个位高手帮忙 怎样在winform中动态展开树,并定位在相应的节点? MoveForm.dll 这个第三方控件咋用?请教! 线程间操作无效,大家帮忙来看看吧! shell32 做upzip 总是报错 在线求助 如何在服务器空间中每隔一段时间自动执行一个程序,现在有的条件是服务器空间,没有服务器内部的权限,不能通过iis,求大神指导。 Winform Panel 深复制 datagridview绑定值
或者,在账单好前面加一个 ' ,自动转换为文本。
或者存完excel后打开后选中此列,设置单元格格式为特殊就行了。
row.Cells[num].Attributes.Add("style","vnd.ms-excel.numberformat:@");
//如果是代码输出的
xls_sheet.get_Range(xls_exp.Cells[rowindex, colindex], xls_exp.Cells[rowindex, colindex]).NumberFormatLocal = "@";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentEncoding = Encoding.UTF7;
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("生产单列表.xls"));
HttpContext.Current.Response.Charset = "gb2312";
HttpContext.Current.Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
if (ViewState["ProductionBills"] != null)
{
List<ProductionBill> productionBill = ViewState["ProductionBills"] as List<ProductionBill>;
if (productionBill.Count > 0)
{
gvProductionBills.DataSource = productionBill;
gvProductionBills.DataBind(); gvProductionBills.HeaderRow.Cells.RemoveAt(7);
gvProductionBills.HeaderRow.Cells.RemoveAt(7);
foreach (GridViewRow row in gvProductionBills.Rows)
{
row.Cells.RemoveAt(7);
row.Cells.RemoveAt(7);
gvProductionBills.HeaderRow.Cells[2].Text = "下单日期" ;
gvProductionBills.HeaderRow.Cells[3].Text = "要求日期";
LinkButton lbtnPNumber = row.FindControl("lbtnPNumber") as LinkButton;
Label lblPNumber = new Label();
lblPNumber.Text = lbtnPNumber.Text.Trim(); LinkButton lbtnPRe = row.FindControl("lbtnPRe") as LinkButton;
Label lblPRe = new Label();
lblPRe.Text = lbtnPRe.Text.Trim(); row.Cells[6].Controls.Add(lblPRe);
row.Cells[6].Controls.Remove(lbtnPRe);
row.Cells[6].Text = lbtnPRe.ToolTip;
row.Cells[1].Controls.Add(lblPNumber);
row.Cells[1].Controls.Remove(lbtnPNumber);
row.Cells[1].Text = lbtnPNumber.ToolTip;
row.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); }
gvProductionBills.RenderControl(htmlWrite);
}
}
HttpContext.Current.Response.Write(stringWrite.ToString());
HttpContext.Current.Response.End();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentEncoding = Encoding.UTF7;
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("生产单列表.xls"));
HttpContext.Current.Response.Charset = "gb2312";
HttpContext.Current.Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
if (ViewState["ProductionBills"] != null)
{
List<ProductionBill> productionBill = ViewState["ProductionBills"] as List<ProductionBill>;
if (productionBill.Count > 0)
{
gvProductionBills.DataSource = productionBill;
gvProductionBills.DataBind(); gvProductionBills.HeaderRow.Cells.RemoveAt(7);
gvProductionBills.HeaderRow.Cells.RemoveAt(7);
foreach (GridViewRow row in gvProductionBills.Rows)
{
row.Cells.RemoveAt(7);
row.Cells.RemoveAt(7);
gvProductionBills.HeaderRow.Cells[2].Text = "下单日期" ;
gvProductionBills.HeaderRow.Cells[3].Text = "要求日期";
LinkButton lbtnPNumber = row.FindControl("lbtnPNumber") as LinkButton;
Label lblPNumber = new Label();
lblPNumber.Text = lbtnPNumber.Text.Trim(); LinkButton lbtnPRe = row.FindControl("lbtnPRe") as LinkButton;
Label lblPRe = new Label();
lblPRe.Text = lbtnPRe.Text.Trim(); row.Cells[6].Controls.Add(lblPRe);
row.Cells[6].Controls.Remove(lbtnPRe);
row.Cells[6].Text = lbtnPRe.ToolTip;
row.Cells[1].Controls.Add(lblPNumber);
row.Cells[1].Controls.Remove(lbtnPNumber);
row.Cells[1].Text = lbtnPNumber.ToolTip;
row.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); }
gvProductionBills.RenderControl(htmlWrite);
}
}
HttpContext.Current.Response.Write(stringWrite.ToString());
HttpContext.Current.Response.End();
我的是用流写的加'效果不好,看还有别的方法没
/// <summary>
/// 将DataGridView中的数据导出到Excel中,并加载显示出来(无加载模板)
/// 只用于一般的导出Excel
/// </summary>
/// <param name="caption">要显示的页头</param>
/// <param name="date">打印日期</param>
/// <param name="dgv">要进行导出的DataGridView</param>
public static void ExportToExcel(string caption, string date, DataGridView dgv)
{
//Microsoft.Office.Interop.Excel.Range range = null;
//range.NumberFormatLocal = "@";
//DataGridView可见列数
int visiblecolumncount = 0;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn))
{
visiblecolumncount++;
}
} try
{
//当前操作列的索引
int currentcolumnindex = 1;
//当前操作行的索引
Microsoft.Office.Interop.Excel.ApplicationClass Mylxls = new Microsoft.Office.Interop.Excel.ApplicationClass();
Mylxls.Application.Workbooks.Add(true);
//Mylxls.Cells.Font.Size = 10.5; //设置默认字体大小
//设置标头
Mylxls.Caption = caption;
//显示表头
Mylxls.Cells[1, 1] = caption;
//显示时间
Mylxls.Cells[2, 1] = date;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn)) //如果显示
{
Mylxls.Cells[3, currentcolumnindex] = dgv.Columns[i].HeaderText;
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //设置边框
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).ColumnWidth = dgv.Columns[i].Width / 8;
//Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //粗体
//Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中显示
currentcolumnindex++;
}
}
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //合并单元格
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30; //行高
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "黑体";
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14; //字体大小
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中显示
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //合并
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //左边显示
//Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12; //列宽度 object[,] dataArray = new object[dgv.Rows.Count, visiblecolumncount]; //当前操作列的索引
//int currentcolumnindex = 1;
//当前操作行的索引
for (int i = 0; i < dgv.Rows.Count; i++) //循环填充数据
{
currentcolumnindex = 1;
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible == true && (dgv.Columns[j] is DataGridViewTextBoxColumn))
{
if (dgv[j, i].Value != null) //如果单元格内容不为空
{
dataArray[i, currentcolumnindex - 1] = dgv[j, i].Value.ToString();
}
currentcolumnindex++;
}
}
}
Mylxls.get_Range(Mylxls.Cells[4, 1], Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Value2 = dataArray; //设置边框
Mylxls.get_Range(Mylxls.Cells[4, 1], Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Cells.Borders.LineStyle = 1; //设置边框
Mylxls.Visible = true; }
catch(Exception ex)
{
//MessageBox.Show(ex.ToString());
MessageBox.Show("信息导出失败,请确认你的机子上装有Microsoft Office Excel 2007!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{ } }
按照你的写法:Mylxls.get_Range(Mylxls.Cells[4, 1],Mylxls.Cells[dgv.Rows.Count + 3], visiblecolumncount]).NumberFormatLocal = "@";//设置文本格式
Mylxls.get_Range(Mylxls.Cells[4, 1],Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).NumberFormatLocal = "@";//设置文本格式
设置它的最佳宽度呢?Mylxls.get_Range(Mylxls.Cells[4, 1],Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Columns.AutoFit();
你在你要写入的单元格数据前加'比如:你的Excel列="'"+你的数据
Mylxls.Cells[行,列]="'"+DataTable.Rows[下标][下标].ToString();
绝对可以