ASP.NET中 Execl导出的六种方法实例
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="page"></param>
/// <param name="dt"></param>
//方法一：
public void ImportExcel(Page page, DataTable dt)
{
try
{
string filename = Guid.NewGuid().ToString() + ".xls";
string webFilePath = page.Server.MapPath("/" + filename);
CreateExcelFile(webFilePath, dt);
using (FileStream fs = new FileStream(webFilePath, FileMode.OpenOrCreate))
{
//让用户输入下载的本地地址
page.Response.Clear();
page.Response.Buffer = true;
page.Response.Charset = "GB2312";
//page.Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls");
page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
page.Response.ContentType = "application/ms-excel";
// 读取excel数据到内存
byte[] buffer = new byte[fs.Length - 1];
fs.Read(buffer, 0, (int)fs.Length - 1);
// 写到aspx页面
page.Response.BinaryWrite(buffer);
page.Response.Flush();
//this.ApplicationInstance.CompleteRequest(); //停止页的执行
fs.Close();
fs.Dispose();
//删除临时文件
File.Delete(webFilePath);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void ImportExcel(Page page, DataSet ds)
{
try
{
string filename = Guid.NewGuid().ToString() + ".xls";
string webFilePath = page.Server.MapPath("/" + filename);
CreateExcelFile(webFilePath, ds);
using (FileStream fs = new FileStream(webFilePath, FileMode.OpenOrCreate))
{
//让用户输入下载的本地地址
page.Response.Clear();
page.Response.Buffer = true;
page.Response.Charset = "GB2312";
//page.Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls");
page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
page.Response.ContentType = "application/ms-excel";
// 读取excel数据到内存
byte[] buffer = new byte[fs.Length - 1];
fs.Read(buffer, 0, (int)fs.Length - 1);
// 写到aspx页面
page.Response.BinaryWrite(buffer);
page.Response.Flush();
//this.ApplicationInstance.CompleteRequest(); //停止页的执行
fs.Close();
fs.Dispose();
//删除临时文件
File.Delete(webFilePath);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void ImportExcel(Page page, DataTable dt1, DataTable dt2, string conditions)
{
try
{
string filename = Guid.NewGuid().ToString() + ".xls";
string webFilePath = page.Server.MapPath("/" + filename);
CreateExcelFile(webFilePath, dt1, dt2, conditions);
using (FileStream fs = new FileStream(webFilePath, FileMode.OpenOrCreate))
{
//让用户输入下载的本地地址
page.Response.Clear();
page.Response.Buffer = true;
page.Response.Charset = "GB2312";
//page.Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls");
page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
page.Response.ContentType = "application/ms-excel";
// 读取excel数据到内存
byte[] buffer = new byte[fs.Length - 1];
fs.Read(buffer, 0, (int)fs.Length - 1);
// 写到aspx页面
page.Response.BinaryWrite(buffer);
page.Response.Flush();
//this.ApplicationInstance.CompleteRequest(); //停止页的执行
fs.Close();
fs.Dispose();
//删除临时文件
File.Delete(webFilePath);
}
}
catch (Exception ex)
{
throw ex;
}
}
private void CreateExcelFile(string filePath, DataTable dt)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
OleDbConnection oleDbConn = new OleDbConnection();
OleDbCommand oleDbCmd = new OleDbCommand();
try
{
string sSql = "";
oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";
oleDbConn.Open();
oleDbCmd.CommandType = CommandType.Text;
oleDbCmd.Connection = oleDbConn;
//写列名
sSql = "CREATE TABLE sheet1(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (dt.Columns[i].DataType.Name == "String")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Text,";
}
else if (dt.Columns[i].DataType.Name == "DateTime")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";
}
}
else
{
if (dt.Columns[i].DataType.Name == "String")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Text)";
}
else if (dt.Columns[i].DataType.Name == "DateTime")
{
sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
for (int j = 0; j < dt.Rows.Count; j++)
{
sSql = "INSERT INTO sheet1 VALUES(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (DBNull.Value.Equals(dt.Rows[j][i]))
{
sSql += "NULL,";
}
else
{
if (dt.Columns[i].DataType.Name == "Decimal")
{
sSql += dt.Rows[j][i].ToString() + ",";
}
else
{
sSql += "'" + dt.Rows[j][i].ToString() + "',";
}
}
}
else
if (DBNull.Value.Equals(dt.Rows[j][i]))
{
sSql += "NULL)";
}
else
{
if (dt.Columns[i].DataType.Name == "Decimal")
{
sSql += dt.Rows[j][i].ToString() + ")";
}
else
{
sSql += "'" + dt.Rows[j][i].ToString() + "')";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
//断开连接
oleDbCmd.Dispose();
oleDbConn.Close();
oleDbConn.Dispose();
}
}
private void CreateExcelFile(string filePath, DataSet ds)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
OleDbConnection oleDbConn = new OleDbConnection();
OleDbCommand oleDbCmd = new OleDbCommand();
try
{
string sSql = "";
oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";
oleDbConn.Open();
oleDbCmd.CommandType = CommandType.Text;
oleDbCmd.Connection = oleDbConn;
//写列名
for(int k=0;k<ds.Tables.Count;k++)
{
DataTable dt = ds.Tables[k];
sSql = "CREATE TABLE sheet" + (k + 1).ToString() + "(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (dt.Columns[i].DataType.Name == "String" || dt.Columns[i].DataType.Name=="Guid")
{
sSql += "["+dt.Columns[i].ColumnName + "] Text,";
}
else if (dt.Columns[i].DataType.Name == "DateTime")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";
}
}
else
{
if (dt.Columns[i].DataType.Name == "String")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Text)";
}
else if (dt.Columns[i].DataType.Name == "DateTime")
{
sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery(); for (int j = 0; j < dt.Rows.Count; j++)
{
sSql = "INSERT INTO sheet" + (k + 1).ToString() + " VALUES(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (DBNull.Value.Equals(dt.Rows[j][i]))
{
sSql += "NULL,";
}
else
{
if (dt.Columns[i].DataType.Name == "Decimal")
{
sSql += dt.Rows[j][i].ToString() + ",";
}
else
{
sSql += "'" + dt.Rows[j][i].ToString().Replace("'", "''") + "',";
}
}
}
else
if (DBNull.Value.Equals(dt.Rows[j][i]))
{
sSql += "NULL)";
}
else
{
if (dt.Columns[i].DataType.Name == "Decimal")
{
sSql += dt.Rows[j][i].ToString() + ")";
}
else
{
sSql += "'" + dt.Rows[j][i].ToString().Replace("'","''") + "')";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
}
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
//断开连接
oleDbCmd.Dispose();
oleDbConn.Close();
oleDbConn.Dispose();
}
}
private void CreateExcelFile(string filePath, DataTable dt1,DataTable dt2,string conditions)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
OleDbConnection oleDbConn = new OleDbConnection();
OleDbCommand oleDbCmd = new OleDbCommand();
try
{
string sSql = "";
oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";
oleDbConn.Open();
oleDbCmd.CommandType = CommandType.Text;
oleDbCmd.Connection = oleDbConn;
//写列名
sSql = "CREATE TABLE sheet1(";
DataTable dt = dt1.Copy();
dt.Columns.Remove("MGUID");
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (dt.Columns[i].DataType.Name == "String")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Text,";
}
else if (dt.Columns[i].DataType.Name == "DateTime")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";
}
}
else
{
if (dt.Columns[i].DataType.Name == "String")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Text)";
}
else if (dt.Columns[i].DataType.Name == "DateTime")
{
sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
DataView dv = new DataView();
dv.Table = dt;
DataView dv1 = new DataView();
dv1.Table = dt1;
if (conditions != "")
{
dv.RowFilter = conditions;
dv1.RowFilter = conditions;
}
dt = dv.ToTable();
dt1 = dv1.ToTable();
string MGUIDs = "";
for (int j = 0; j < dt.Rows.Count; j++)
{
MGUIDs += ",'" + dt1.Rows[j]["MGUID"].ToString() + "'";
sSql = "INSERT INTO sheet1 VALUES(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (DBNull.Value.Equals(dt.Rows[j][i]))
{
sSql += "NULL,";
}
else
{
if (dt.Columns[i].DataType.Name == "Decimal")
{
sSql += dt.Rows[j][i].ToString() + ",";
}
else
{
sSql += "'" + dt.Rows[j][i].ToString() + "',";
}
}
}
else
if (DBNull.Value.Equals(dt.Rows[j][i]))
{
sSql += "NULL)";
}
else
{
if (dt.Columns[i].DataType.Name == "Decimal")
{
sSql += dt.Rows[j][i].ToString() + ")";
}
else
{
sSql += "'" + dt.Rows[j][i].ToString() + "')";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
}
if (dt2 != null)
{
sSql = "CREATE TABLE sheet21(";
dt = dt2.Copy();
dt.Columns.Remove("MGUID");
dt.Columns.Remove("DGUID");
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (dt.Columns[i].DataType.Name == "String")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Text,";
}
else if (dt.Columns[i].DataType.Name == "DateTime")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";
}
}
else
{
if (dt.Columns[i].DataType.Name == "String")
{
sSql += "[" + dt.Columns[i].ColumnName + "] Text)";
}
else if (dt.Columns[i].DataType.Name == "DateTime")
{
sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
dv = new DataView();
dv.Table = dt2;
if (MGUIDs != "")
{
dv.RowFilter = "MGUID in(" + MGUIDs.Substring(1) + ")";
}
dt = dv.ToTable();
for (int j = 0; j < dt.Rows.Count; j++)
{
sSql = "INSERT INTO sheet1 VALUES(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (DBNull.Value.Equals(dt.Rows[j][i]))
{
sSql += "NULL,";
}
else
{
if (dt.Columns[i].DataType.Name == "Decimal")
{
sSql += dt.Rows[j][i].ToString() + ",";
}
else
{
sSql += "'" + dt.Rows[j][i].ToString() + "',";
}
}
}
else
if (DBNull.Value.Equals(dt.Rows[j][i]))
{
sSql += "NULL)";
}
else
{
if (dt.Columns[i].DataType.Name == "Decimal")
{
sSql += dt.Rows[j][i].ToString() + ")";
}
else
{
sSql += "'" + dt.Rows[j][i].ToString() + "')";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
}
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
//断开连接
oleDbCmd.Dispose();
oleDbConn.Close();
oleDbConn.Dispose();
}
}
相关推荐
-
C#数据导入/导出Excel文件及winForm导出Execl总结
一.asp.net中导出Execl的方法: 在asp.net中导出Execl有两种方法,一种是将导出的文件存放在服务器某个文件夹下面,然后将文件地址输出在浏览器上:一种是将文件直接将文件输出流写给浏览器.在Response输出时,\t分隔的数据,导出execl时,等价于分列,\n等价于换行. 1.将整个html全部输出execl 此法将html中所有的内容,如按钮,表格,图片等全部输出到Execl中. 复制代码 代码如下: Response.Clear(); Response.Buffer= t
-
ASP.NET中 Execl导出的六种方法实例
复制代码 代码如下: /// <summary> /// 导出Excel /// </summary> /// <param name="page"></param> /// <param name="dt"></param> //方法一: public void ImportExcel(Page page, D
-
ASP.NET中XML转JSON的方法实例
本文实例讲述了ASP.NET中XML转JSON的方法,分享给大家供大家参考.具体如下: 一般在许多应用程序中都将数据存储为XML的格式,而且会将数据以JSON的格式发送到客户端以做进一步处理.要实现这一点,它们必须将XML格式转换为JSON格式. XML转JSON代码如下: 复制代码 代码如下: private static string XmlToJSON(XmlDocument xmlDoc) { StringBuilder sbJSON = new StringBuilder(
-
ASP.NET使用GridView导出Excel实现方法
本文实例讲述了ASP.NET使用GridView导出Excel实现方法.分享给大家供大家参考.具体实现方法如下: 复制代码 代码如下: /// <summary> /// 将DataTable数据导出到EXCEL,调用该方法后自动返回可下载的文件流 /// </summary> /// <param name="dtData">要导出的数据源</param> public static void DataTable1Excel(S
-
asp.net中调用oracle存储过程的方法
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它. 存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程. 不多说了,本文通过两种方法介绍asp.net中调用oracle存储过程的方法,具体内容请看下面代码. 调用oracle存储过程方法一: ORACLE代码 CREATE OR REP
-
java从文件中读取数据的六种方法
目录 1.Scanner 2.Files.lines (Java 8) 3.Files.readAllLines(java8) 4.Files.readString(JDK 11) 5.Files.readAllBytes() 6.经典管道流的方式 本文主要介绍了java从文件中读取数据的六种方法,分享给大家,具体如下: Scanner(Java 1.5) 按行读数据及String.Int类型等按分隔符读数据. Files.lines, 返回Stream(Java 8) 流式数据处理,按行读取
-
Oracle表中重复数据去重的方法实例详解
Oracle表中重复数据去重的方法实例详解 我们在项目中肯定会遇到一种情况,就是表中没有主键 有重复数据 或者有主键 但是部分字段有重复数据 而我们需要过滤掉重复数据 下面是一种解决方法 delete from mytest ms where rowid in (select aa.rid from (select rowid as rid, row_number() over(partition by s.name order by s.id) as nu from mytest s) aa
-
Android 中隐藏虚拟按键的方法实例代码
下面通过一段代码给大家讲解android 隐藏虚拟按键的方法,废话不多说了,大家多多看看代码和注释吧,具体代码如下所示: /** * 隐藏虚拟按键,并且全屏 */ protected void hideBottomUIMenu() { //隐藏虚拟按键,并且全屏 if (Build.VERSION.SDK_INT > 11 && Build.VERSION.SDK_INT < 19) { // lower api View v = this.getWindow().getDec
-
基于TabLayout中的Tab间隔设置方法(实例讲解)
TabLayout和ViewPager搭配使用,是有很多方便性,但是TabLayout这东西还是有很多被人吐槽的地方. 这里只讲怎么设置tab之间的间隔,网上找了一堆方法,什么padding和margin的啥都没用,没办法,想用TabLayout只能自己想办法了.效果如下: 一.实现方法,既然这东西不好设置,那就直接在背景上做点事情,布局代码如下: <android.support.design.widget.TabLayout xmlns:app="http://schemas.andr
-
Vue中遍历数组的新方法实例详解
1.foreach foreach循环对不能使用return来停止循环 search(keyword){ var newList = [] this.urls.forEach(item =>{ if(item.name.indexOf(keyword) != -1){ newList.push(item) } }) return newList } 2.filter item对象就是遍历数组中的一个元素,includes是es6中的新方法,在search方法中直接返回新数组 search(key
-
Vue中使用webpack别名的方法实例详解
在工作中,我们经常会写出这种代码: import MHeader from '../../components/m-header/m-header' @import "../../common/stylus/variable" @import "../../common/stylus/mixin" 即,需要引入公共文件,但是公共文件的文件路径里当前文件很远,那么就会形成上面示例中的那种路径很长的情况. 而因为文件目录是约定俗成的,不可轻易更改,无法修改相对路径.那么