C#导出数据到Excel类型问题

[wu19870828]

格式如下:
代理商号 学校编号 学校名称 班级编号 学生姓名 交费手机号码 业务号码 定制日期
15 每日测幼儿园 150801 123 13840021814 -X15A 2008-07-30
00015 每日测幼儿园 000150801 123 13840021814 -FKA 2008-08-04
00015 每日测幼儿园 000150801 123 13840021814 -X15A 2008-08-08
宏鑫园 00017 沈阳市大东区教育局幼儿园 000170801 小测 15710558888 -X15A 2008-08-11
00015 每日测幼儿园 000150801 123 13840021814 -X15A 2008-08-11
00015 每日测幼儿园 000150801 123 13840021814 -X8A 2008-08-27

第一行的学校编号和班级编号自动转换了类型.
怎么让他和下面的数据格式一样.
我是用输出流的方式写的.
请高手帮助帮助.
要是有例子最好,
请发送到[e-mail] 十分感谢.

[jiangshun]

从哪里导出的？

[Software]

曾经我是在前面加了个“'”符号

[sdav]

#region ToExecl
/// <summary>
/// 将页面存储为EXECL文件
/// </summary>
/// <param name="FileName">文件名</param>
/// <param name="TempObject">要存储为EXECL的页面</param>
public void ToExecl(string FileName,System.Web.UI.Page TempObject)
{
//UTF-8
//GB2312
HttpContext.Current.Response.Charset.ToString();
HttpContext.Current.Response.Charset = "GB2312";//编码，根据需要修改
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ FileName +".xls");
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
TempObject.Page.EnableViewState =false;
TempObject.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
/// <summary>
/// 将HtmlGenericControl存储为EXECL文件
/// </summary>
/// <param name="FileName">文件名</param>
/// <param name="TempObject">要存储为EXECL的HtmlGenericControl</param>
public void ToExecl(string FileName,System.Web.UI.HtmlControls.HtmlGenericControl TempObject)
{
HttpContext.Current.Response.Charset ="UTF-8";//编码，根据需要修改
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ FileName +".xls");
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
TempObject.Page.EnableViewState =false;
TempObject.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
/// <summary>
/// 将HtmlGenericControl存储为EXECL文件
/// </summary>
/// <param name="FileName">文件名</param>
/// <param name="TempObject">要存储为EXECL的HtmlGenericControl</param>
public void ToExecl(string FileName,System.Web.UI.WebControls.DataGrid TempObject, string codetype)
{
HttpContext.Current.Response.Charset =codetype;//编码，根据需要修改
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding(codetype);
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ FileName +".xls");
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
TempObject.Page.EnableViewState =false;
TempObject.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
/// <summary>
/// 将HtmlTable存储为EXECL文件
/// </summary>
/// <param name="FileName">文件名</param>
/// <param name="TempObject">要存储为EXECL的HtmlTable</param>
public void ToExecl(string FileName,System.Web.UI.HtmlControls.HtmlTable TempObject)
{
HttpContext.Current.Response.Charset ="GB2312";//编码，根据需要修改
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ FileName +".xls");
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
TempObject.Page.EnableViewState =false;
TempObject.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
/// <summary>
/// 将DataGrid存储为EXECL文件
/// </summary>
/// <param name="FileName">文件名</param>
/// <param name="TempObject">要存储为EXECL的DataGrid</param>
public void ToExecl(string FileName,System.Web.UI.WebControls.DataGrid TempObject)
{
HttpContext.Current.Response.Charset ="GB2312";//编码，根据需要修改
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ FileName +".xls");
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
TempObject.Page.EnableViewState =false;
TempObject.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
#endregion

[chenwei175528]

可以尝试把数据转换成一个GridView(注意不是绑定到GridView上，而是在后台生成一个GridView)

然后利用GridView自带的导出Excel功能应该可以

具体代码google"GridView Excel"

[erytbc]

页面的命名空间引用 using Excel;

1 #region 不使用模板生成Excel表
2 case "ReportByNone":
3 {
4
5 DataView dv = Cache["ReportByNone"] as DataView;
6 //建立一个Excel.Application的新进程
7 Excel.Application app = new Excel.Application();
8 if (app == null)
9 {
10 return;
11 }
12 app.Visible = false;
13 app.UserControl = true;
14 Workbooks workbooks = app.Workbooks;
15 _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);//这里的Add方法里的参数就相当于继承了一个空模板(暂这样理解吧)
16 Sheets sheets = workbook.Worksheets;
17 _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
18 if (worksheet == null)
19 {
20 return;
21 }
22
23 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]).Merge(Missing.Value); //横向合并
24 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Value2 = "导出EXCEL测试一";
25 excelOperate.SetBold(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //黑体
26 excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中
27 excelOperate.SetBgColor(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], System.Drawing.Color.Red);// 背景色
28 excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 16);//字体大小
29 excelOperate.SetRowHeight(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 32.25);//行高
30 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); //黑色连续边框
31
32 worksheet.Cells[2, 1] = "序号";
33 worksheet.Cells[2, 2] = "公司";
34 worksheet.Cells[2, 3] = "部门";
35 excelOperate.SetBold(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]); //黑体
36 worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
37 excelOperate.SetHAlignRight(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]);
38 excelOperate.SetBgColor(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3], System.Drawing.Color.Silver);// 背景色
39 int rowNum = 0;
40 for (int i = 0; i < dv.Count; i++)
41 {
42 rowNum = i + 1;
43 worksheet.Cells[3 + i, 1] = rowNum;
44 worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString();
45 worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString();
46
47 excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑体
48 excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);// 居中
49 worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); //设置边框颜色，不然打印预览，会非常不雅观
50
51 }
52 excelOperate.SetColumnWidth(worksheet, "A", 10);
53 excelOperate.SetColumnWidth(worksheet, "B", 20);
54 excelOperate.SetColumnWidth(worksheet, "C", 20);
55 worksheet.Name = "导出EXCEL测试一";
56
57 tick = DateTime.Now.Ticks.ToString();
58 save_path = temp_path + "\\"+ tick + ".xls";
59 workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
60 excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
61
62 }
63 break;