欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

asp.net实现数据从DataTable导入到Excel文件并创建表的方法

程序员文章站 2023-12-18 10:59:04
本文实例讲述了asp.net实现数据从datatable导入到excel文件并创建表的方法。分享给大家供大家参考,具体如下: ///

本文实例讲述了asp.net实现数据从datatable导入到excel文件并创建表的方法。分享给大家供大家参考,具体如下:

/// <summary>
/// 把数据从datatable导入到excel文件里
/// </summary>
/// <param name="datatable">数据源</param>
/// <param name="absoluteexcelfilepath">excel文件的绝对路径</param>
/// <param name="tblcolname">tbl里对应的列名</param>
/// <param name="columnname">excel中对应的列名</param>
/// <returns>操作成功返回true,失败返回false</returns>
public static bool exportdatatoexcel(datatable datatable, string absoluteexcelfilepath, string[] tblcolname, string[] columnname)
{
  int k = 0;
  if (datatable == null) return false;
  oledbconnection conn = new oledbconnection();
  try
  {
   string strconn = "provider=microsoft.jet.oledb.4.0;data source=" + absoluteexcelfilepath + ";mode=share deny none;extended properties=excel 8.0;jet oledb:create system database=true";
   conn = new oledbconnection(strconn);
   conn.open();
   oledbcommand command = conn.createcommand();
   string strsql = "";
   if (datatable.columns != null)
   {
    //建表
    strsql = "create table " + datatable.tablename + "(";
    for (int i = 0; i < columnname.length; i++)
    {
     strsql += columnname[i] + " text,";
    }
    strsql = strsql.substring(0, strsql.length - 1);
    strsql += ")";
    command.commandtext += strsql;
    command.executenonquery();
    if (datatable.rows.count > 0)
    {
     //导入数据
     foreach (datarow row in datatable.rows)
     {
      strsql = "insert into " + datatable.tablename + "(";
      for (k = 0; k < tblcolname.length; k++)
      {
       strsql += columnname[k] + ",";
      }
      strsql = strsql.substring(0, strsql.length - 1);
      strsql += ") values( ";
      for (k = 0; k < tblcolname.length; k++)
      {
       strsql += "'" + row[tblcolname[k]] + "',";
      }
      strsql = strsql.substring(0, strsql.length - 1);
      strsql += ")";
      command.commandtext = strsql;
      command.executenonquery();
     }
    }
   }
  }
  catch (exception ex)
  {
   conn.close();
   throw new exception(ex.message);
   return false;
  }
  conn.close();
  return true;
}

调用方法:

dataset ds = (dataset)session["listmobile"];//获得要导出的表格的值
if (ds.tables[0].rows.count <= 0)
{
 page.registerstartupscript("", "<mce:script type="text/javascript"><!--
alert('没有内容不能导出!')
// --></mce:script>");
}
else
{
 //excel页面的名称
 string[] tablename = { "["+datetime.now.tostring("yyyymmddhhmmss")+"]" };
 string filename = tools.createid() + ".xls";
 string filepath = server.mappath("..//downloadfiles//" + filename);
 if (tools.exportdatatoexcel(ds, filepath, tablename)==true)
 {
  response.clear();
  response.buffer = true;
  response.charset = "gb2312";
  response.appendheader("content-disposition", "attachment;filename=" + filename);
  response.contenttype = "application/vnd.ms-excel";
  this.enableviewstate = false;
  response.writefile(filepath);
  response.flush();
  if (system.io.file.exists(filepath)) system.io.file.delete(filepath);
  response.redirect(this.request.urlreferrer.absoluteuri, true);
  response.end();
 }
}

希望本文所述对大家asp.net程序设计有所帮助。

上一篇:

下一篇: