ASP.NET之Excel下载模板、导入、导出操作

本文介绍了asp.net下excel下载模板、导入、导出操作，供大家参考，具体内容如下

1.下载模板功能

protected void btndownload_click(object sender, eventargs e)
{
 var path = server.mappath(("upfiles\\") + "test.xlt"); //upfiles-文件夹 test.xlt-文件
 var name = "test.xlt";

 try
 {
 var file = new fileinfo(path);
 response.clear();
 response.charset = "gb2312";
 response.contentencoding = system.text.encoding.utf8;
 response.addheader("content-disposition", "attachment; filename=" + server.urlencode(name)); //头信息，指定默认文件名
 response.addheader("content-length", file.length.tostring());//显示下载进度
 response.contenttype = "application/ms-excel"; // 指定返回的是一个不能被客户端读取的流，必须被下载
 response.writefile(file.fullname); // 把文件流发送到客户端
  
 httpcontext.current.applicationinstance.completerequest();
 }
 catch (exception ex)
 {
 response.write("<script>alert('错误:" + ex.message + ",请尽快与管理员联系')</script>");
 }
}

2.导入数据
excel数据导入到数据库中。 

protected void btnimport_click(object sender, eventargs e)
{
 if (fileupload1.hasfile == false) //判断是否包含一个文件
 {
 response.write("<script>alert('请您选择excel文件！')</script>");//未上传就点击了导入按钮
 return;
 }
 string isxls = path.getextension(fileupload1.filename).tostring().tolower();//获得文件的扩展名
 var extenlen = isxls.length;

 if (!isxls.contains(".xls")) //判断是否 是excel文件
 {
 response.write("<script>alert('只可以选择excel文件！')</script>");
 return;
 }

 string filename = fileupload1.filename;  //获取excle文件名
 string savepath = server.mappath(("upfiles\\") + filename);//server.mappath 获得虚拟服务器相对路径
 string savepath2 = server.mappath(("upfiles\\"));

 if (!directory.exists(savepath2)) //如果不存在upfiles文件夹则创建
 {
 directory.createdirectory(savepath2);
 }
 fileupload1.saveas(savepath); //saveas 将上传的文件内容保存在服务器上
 var ds = excelsqlconnection(savepath, filename);  //将excel转成dataset
 var dtrows = ds.tables[0].rows.count;
 var dt = ds.tables[0];
 if (dtrows == 0)
 {
 response.write("<script>alert('excel表无数据！')</script>");
 return;
 }
 try
 {
 for(int i = 0; i < dt.rows.count; i++)
 {
  string ve = dt.rows[i]["车号"].tostring();
  if (string.isnullorempty(ve)) //因数据库中车号不能为空 所以表格中车号为空的跳过这行
  {
  continue;
  }
  //用自己的方式保存进数据库ado/ef/...
  var model = new test(); //实体
  model.id = 1;
  model.ve = ve;
  model.name = dt.rows[i]["姓名"].tostring();
  model.update();
 }
 }catch (exception ex)
 {
 response.write("<script>alert('" + ex.message + "')</script>"); 
 }
 
}

private dataset excelsqlconnection(string savepath, string tablename)
{
 //string strcon = "provider=microsoft.jet.oledb.4.0;data source=" + savepath + ";extended properties='excel 8.0;hdr=yes;imex=1'";
 string strcon = "provider=microsoft.ace.oledb.12.0;" + "data source=" + savepath + ";extended properties='excel 12.0; hdr=yes; imex=1'"; //hdr=yes excel文件的第一行是列名而不是数据 imex=1可必免数据类型冲突
 var excelconn = new oledbconnection(strcon);
 try
 {
 string strcom = string.format("select * from [sheet1$]");
 excelconn.open();
 oledbdataadapter mycommand = new oledbdataadapter(strcom, excelconn);
 dataset ds = new dataset();
 mycommand.fill(ds, "[" + tablename + "$]");
 excelconn.close();
 return ds;
 }
 catch (exception)
 {
 excelconn.close();
 //response.write("<script>alert('" + ex.message + "')</script>");
 return null;
 }

}

3.导出数据到excel中
插件采用myxls.
以下代码大部分基本不用改。 

private void export()
{
 xlsdocument xls = new xlsdocument();
 org.in2bits.myxls.cell cell;
 int rowindex = 2;

 xls.filename = datetime.now.tostring().replace("-", "").replace(":", "").replace(" ", "") + httputility.urlencode("test") + ".xls"; //test要改
 worksheet sheet = xls.workbook.worksheets.addnamed("test");//状态栏标题名称
 org.in2bits.myxls.cells cells = sheet.cells;

 #region 表头
 mergearea area = new mergearea(1, 1, 1, 2); //mergearea(int rowmin, int rowmax, int colmin, int colmax)
 org.in2bits.myxls.cell celltitle = cells.addvaluecell(1, 1, "test"); //excel 第一行第1到2列显示test
 sheet.addmergearea(area);
 celltitle.font.height = 20 * 20;
 celltitle.font.bold = true;//设置标题行的字体为粗体
 celltitle.font.fontfamily = fontfamilies.roman;//设置标题行的字体为fontfamilies.roman
 celltitle.horizontalalignment = horizontalalignments.centered;

 area = new mergearea(2, 2, 1, 1);
 celltitle = cells.addvaluecell(2, 1, "车号"); //第二行第一列 显示车号
 sheet.addmergearea(area);
 celltitle.font.bold = true;
 celltitle.font.height = 16 * 16;
 celltitle.font.fontfamily = fontfamilies.roman;
 celltitle.horizontalalignment = horizontalalignments.centered;
 celltitle.verticalalignment = verticalalignments.centered;
 celltitle.toplinestyle = 1;
 celltitle.bottomlinestyle = 1;
 celltitle.leftlinestyle = 1;
 celltitle.rightlinestyle = 1;

 area = new mergearea(2, 2, 2, 2);
 celltitle = cells.addvaluecell(2, 2, "姓名");
 sheet.addmergearea(area);
 celltitle.font.bold = true;
 celltitle.font.height = 16 * 16;
 celltitle.font.fontfamily = fontfamilies.roman;
 celltitle.horizontalalignment = horizontalalignments.centered;
 celltitle.verticalalignment = verticalalignments.centered;
 celltitle.toplinestyle = 1;
 celltitle.bottomlinestyle = 1;
 celltitle.leftlinestyle = 1;
 celltitle.rightlinestyle = 1;

 #endregion

 var list = getlist(); //获取数据

 for (int i = 0; i < list.count; i++)
 {
 rowindex++;
 cell = cells.addvaluecell(rowindex, 1, list[i].vehicleno); //车号
 cell.toplinestyle = 1;
 cell.bottomlinestyle = 1;
 cell.leftlinestyle = 1;
 cell.rightlinestyle = 1;

 cell = cells.addvaluecell(rowindex, 2, list[i].name); //姓名
 cell.toplinestyle = 1;
 cell.bottomlinestyle = 1;
 cell.leftlinestyle = 1;
 cell.rightlinestyle = 1;

 }
 xls.send();
}

4.错误-未在本地计算机上注册“microsoft.ace.oledb.12.0”提供程序
01.将平台换成x86
02.安装 accessdatabaseengine.exe(点击下载) 

5.错误-服务器无法在发送http标头之后设置内容类型
给导出按钮增加'全局刷新'的能力。本文例子是aspx做的在<asp:updatepanel> 标签中 增加如下代码即可
 

<triggers>
    <%--<asp:asyncpostbacktrigger controlid="" />--%> <%--局部刷新 值刷新updatepanel内部 --%>
   <asp:postbacktrigger controlid="btnexport" /> <%--全部刷新 --%> <%--2016年7月1日 解决点击导出按钮报错“服务器无法在发送http标头之后设置内容类型”的错误--%>
</triggers>

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。