62,046
社区成员
发帖
与我相关
我的任务
分享分享一个将DataTable导出成Excel的方法, 另请教保留前导"0"的方法
分享一个将DataTable导出成Excel的方法, 不过还有一个问题没有解决, 那就是如果表中字段是"000123"这样的字符串时, 到Excel中, 都会把它变成数字"123", 而略掉了前面的"0", 大家帮看看有什么好的方法解决(最好在此类的基础上改).
/// <summary>
/// 将数据导出至Excel中
/// </summary>
/// <param name="caption">标题</param>
/// <param name="heading">行标题</param>
/// <param name="dtSource">要导出的DataTable</param>
/// <param name="targetPath">保存的路径(含文件名)</param>
public static void ToExcel(string caption, string[] heading, DataTable dtSource, string targetPath)
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRang;
try
{
GC.Collect();
oXL = new Excel.Application();
oXL.Visible = false;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)(oWB.ActiveSheet);
oRang = oSheet.get_Range("A1", oSheet.Cells[1, dtSource.Columns.Count]);
object optional = System.Reflection.Missing.Value;
oRang.Merge(null);
oRang.Value2 = caption;
oRang.Font.Size = "26";
oRang.Font.Bold = true;
oRang.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;
oRang.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
for (int i = 0; i < heading.Length; i++)
{
oSheet.Cells[2, i + 1] = heading[i];
}
int iRow = 3;
for (int r = 0; r < dtSource.Rows.Count; r++)
{
for (int c = 0; c < dtSource.Columns.Count; c++)
{
oSheet.Cells[iRow, c + 1] = dtSource.Rows[r][c].ToString();
}
iRow++;
}
oSheet.get_Range("A2", "H2").Font.Bold = true;
oSheet.get_Range("A2", "H2").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A2", "H2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
oRang = oSheet.get_Range("A2", "H2");
oRang.EntireColumn.AutoFit();
//oRang.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
oWB.SaveAs(targetPath, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
oWB.Close(null, null, null);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
...全文
请发表友善的回复…
发表回复
Name_456 2011-10-30
- 打赏
- 举报
顶，学习了。。。
winbq5 2011-10-30
- 打赏
- 举报
如何退出EXCEL呢？
cnwin 2011-10-08
- 打赏
- 举报
学习一下
程序猿GG 2010-01-27
- 打赏
- 举报
找到方法了:oSheet.get_Range("A3", string.Format("F{0}", (dtSource.Rows.Count + 2).ToString().Trim())).NumberFormatLocal = "@";
/// <summary>
/// 将数据导出至Excel中
/// </summary>
/// <param name="caption">标题</param>
/// <param name="heading">行标题</param>
/// <param name="dtSource">要导出的DataTable</param>
/// <param name="targetPath">保存的路径(含文件名)</param>
public static void ToExcel(string caption, string[] heading, System.Data.DataTable dtSource, string targetPath)
{
Interop.Excel.Application oXL;
Interop.Excel._Workbook oWB;
Interop.Excel._Worksheet oSheet;
Interop.Excel.Range oRang;
try
{
GC.Collect();
oXL = new Interop.Excel.Application();
oXL.Visible = false;
oWB = (Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Interop.Excel._Worksheet)(oWB.ActiveSheet);
oRang = oSheet.get_Range("A1", oSheet.Cells[1, dtSource.Columns.Count]);
object optional = System.Reflection.Missing.Value;
oRang.Merge(null);
oRang.Value2 = caption;
oRang.Font.Size = "26";
oRang.Font.Bold = true;
oRang.VerticalAlignment = Interop.Excel.XlVAlign.xlVAlignBottom;
oRang.HorizontalAlignment = Interop.Excel.XlHAlign.xlHAlignCenter;
oSheet.get_Range("A3", string.Format("F{0}", (dtSource.Rows.Count + 2).ToString().Trim())).NumberFormatLocal = "@";
for (int i = 0; i < heading.Length; i++)
{
oSheet.Cells[2, i + 1] = heading[i];
}
int iRow = 3;
for (int r = 0; r < dtSource.Rows.Count; r++)
{
for (int c = 0; c < dtSource.Columns.Count; c++)
{
oSheet.Cells[iRow, c + 1] = dtSource.Rows[r][c].ToString();
}
iRow++;
}
oSheet.get_Range("A2", "T2").Font.Bold = true;
oSheet.get_Range("A2", "T2").VerticalAlignment = Interop.Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A2", "T2").HorizontalAlignment = Interop.Excel.XlHAlign.xlHAlignCenter;
oRang = oSheet.get_Range("A2", "T2");
oRang.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
oWB.SaveAs(targetPath, Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Interop.Excel.XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
oWB.Close(false, null, null);
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
oWB = null;
oXL = null;
GC.Collect();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
苏飞论坛 2009-12-11
- 打赏
- 举报
http://www.cnblogs.com/sufei/archive/2009/05/23/1487540.html 看了这个你会明白的
koukoujiayi 2009-12-11
- 打赏
- 举报
楼主的导出代码太多了,给个简单点的试试!!
private void TableToExcel(System.Data.DataTable tb)
{
string Filename = "aa";
System.Web.HttpContext context = System.Web.HttpContext.Current;
if ((tb != null))
{
context.Response.Clear();
context.Response.Charset = "GB2312";
context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
context.Response.ContentType = "application/ms-excel";
context.Response.AppendHeader("content-disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(Filename, System.Text.Encoding.GetEncoding("GB2312")) + ".xls\"");
CultureInfo cult = new CultureInfo("zh-CN", true);
StringWriter sw = new StringWriter(cult);
HtmlTextWriter htw = new HtmlTextWriter(sw);
DataGrid dgrid = new DataGrid();
dgrid.DataSource = tb.DefaultView;
dgrid.AllowPaging = false;
dgrid.DataBind();
htw.WriteLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=GB2312\">");
dgrid.RenderControl(htw);
context.Response.Write(sw.ToString());
context.Response.End();
}
}
woshifou 2009-12-11
- 打赏
- 举报
学习。
lxiron 2009-12-11
- 打赏
- 举报
学习了，并帮顶！
Aderlee 2009-12-11
- 打赏
- 举报
第一次看到这个方法,不过感觉很繁琐,很不爽.给我肯定不用.
网上不是有很多更简单的方法吗?
至于怎么保留前面的0,建议加样式: 就是在你循环把dt的值给excel的时候,让它一直当成文本格式就可以了.
即加个空格或"@"在前面
网上不是有很多更简单的方法吗?
至于怎么保留前面的0,建议加样式: 就是在你循环把dt的值给excel的时候,让它一直当成文本格式就可以了.
即加个空格或"@"在前面
程序猿GG 2009-12-11
- 打赏
- 举报
顶一下.
lfcms 2009-11-10
- 打赏
- 举报
char
happy664618843 2009-11-10
- 打赏
- 举报
友情帮顶
yx5131421 2009-11-10
- 打赏
- 举报
楼主在字符串前加空字符 空格键，即可解决问题
程序猿GG 2009-11-10
- 打赏
- 举报
protected void Get_Meal_Voucher_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "Get_Meal_Voucher")
{
try
{
string caption = string.Format("{0}年{1}月份员工就餐及补贴汇总表", e.CommandArgument.ToString().Substring(0, 4), e.CommandArgument.ToString().Substring(4, 2));
DataTable dtSource = bll.Get_Meal_Voucher(e.CommandArgument.ToString()).Tables[0];
string[] heading = new string[] { "部门", "工号", "姓名", "职务", "购餐种类", "总餐数", "自付金额", "公司补助金额" };
string targetPath = Server.MapPath("~") + "\\temp\\Meal_Voucher.xls";
if (System.IO.File.Exists(targetPath))
{
System.IO.File.Delete(targetPath);
}
CommonFun.ToExcel(caption, heading, dtSource, targetPath);
Response.Redirect(@"~\temp\Meal_Voucher.xls");
}
catch(Exception ex)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "", "alert('未完成下载, 请重试或联系软件部!');", true);
}
}
}
}这是我用上面的方法做的一个实例, 在VS.net2005中运行很正常, 但是发布后(包括发布到本机的另一个目录下),都没办法在在Server.MapPath("~") + "\\temp\\" 目录下创建Meal_Voucher.xls; 也不会提示错误.
???????????????????
