时间:2021-07-01 10:21:17 帮助过:8人阅读
代码如下:
/// <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;
}
}
方法二:
代码如下:
- <br> public void ImportExcel(Page page, DataSet ds)<br> {<br><br> try<br><br> {<br><br> string filename = Guid.NewGuid().ToString() + ".xls";<br><br> string webFilePath = page.Server.MapPath("/" + filename);<br><br> CreateExcelFile(webFilePath, ds);<br><br> using (FileStream fs = new FileStream(webFilePath, FileMode.OpenOrCreate))<br><br> {<br><br> //让用户输入下载的本地地址<br><br> page.Response.Clear();<br><br> page.Response.Buffer = true;<br><br> page.Response.Charset = "GB2312";<br><br> <br><br> //page.Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls");<br><br> page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);<br><br> page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");<br><br> page.Response.ContentType = "application/ms-excel";<br><br> <br><br> // 读取excel数据到内存<br><br> byte[] buffer = new byte[fs.Length - 1];<br><br> fs.Read(buffer, 0, (int)fs.Length - 1);<br><br> <br><br> // 写到aspx页面<br><br> page.Response.BinaryWrite(buffer);<br><br> page.Response.Flush();<br><br> //this.ApplicationInstance.CompleteRequest(); //停止页的执行<br><br> <br><br> <br><br> fs.Close();<br><br> fs.Dispose();<br><br> <br><br> //删除临时文件<br><br> File.Delete(webFilePath);<br><br> }<br><br> <br><br> }<br><br> catch (Exception ex)<br><br> {<br><br> throw ex;<br><br> }<br><br> }<br><br> 方法三:<br><span><u></u></span> 代码如下: <br> public void ImportExcel(Page page, DataTable dt1, DataTable dt2, string conditions)<br><br> {<br><br> try<br><br> {<br><br> <br><br> string filename = Guid.NewGuid().ToString() + ".xls";<br><br> string webFilePath = page.Server.MapPath("/" + filename);<br><br> CreateExcelFile(webFilePath, dt1, dt2, conditions);<br><br> using (FileStream fs = new FileStream(webFilePath, FileMode.OpenOrCreate))<br><br> {<br><br> //让用户输入下载的本地地址<br><br> page.Response.Clear();<br><br> page.Response.Buffer = true;<br><br> page.Response.Charset = "GB2312";<br><br> <br><br> //page.Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls");<br><br> page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);<br><br> page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");<br><br> page.Response.ContentType = "application/ms-excel";<br><br> <br><br> // 读取excel数据到内存<br><br> byte[] buffer = new byte[fs.Length - 1];<br><br> fs.Read(buffer, 0, (int)fs.Length - 1);<br><br> <br><br> // 写到aspx页面<br><br> page.Response.BinaryWrite(buffer);<br><br> page.Response.Flush();<br><br> //this.ApplicationInstance.CompleteRequest(); //停止页的执行<br><br> <br><br> <br><br> fs.Close();<br><br> fs.Dispose();<br><br> <br><br> //删除临时文件<br><br> File.Delete(webFilePath);<br><br> }<br><br> <br><br> }<br><br> catch (Exception ex)<br><br> {<br><br> throw ex;<br><br> }<br><br> }<br><br> 方法四:<br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br> private void CreateExcelFile(string filePath, DataTable dt)<br><br> {<br><br> if (File.Exists(filePath))<br><br> {<br><br> File.Delete(filePath);<br><br> }<br><br> OleDbConnection oleDbConn = new OleDbConnection();<br><br> OleDbCommand oleDbCmd = new OleDbCommand();<br><br> <br><br> try<br><br> {<br><br> string sSql = "";<br><br> oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";<br><br> oleDbConn.Open();<br><br> oleDbCmd.CommandType = CommandType.Text;<br><br> oleDbCmd.Connection = oleDbConn;<br><br> //写列名<br><br> sSql = "CREATE TABLE sheet1(";<br><br> for (int i = 0; i < dt.Columns.Count; i++)<br><br> {<br><br> if (i < dt.Columns.Count - 1)<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "String")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Text,";<br><br> }<br><br> else if (dt.Columns[i].DataType.Name == "DateTime")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";<br><br> }<br><br> else<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";<br><br> }<br><br> }<br><br> else<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "String")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Text)";<br><br> }<br><br> else if (dt.Columns[i].DataType.Name == "DateTime")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";<br><br> }<br><br> else<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";<br><br> }<br><br> }<br><br> }<br><br> oleDbCmd.CommandText = sSql;<br><br> oleDbCmd.ExecuteNonQuery();<br><br> <br><br> for (int j = 0; j < dt.Rows.Count; j++)<br><br> {<br><br> sSql = "INSERT INTO sheet1 VALUES(";<br><br> for (int i = 0; i < dt.Columns.Count; i++)<br><br> {<br><br> if (i < dt.Columns.Count - 1)<br><br> {<br><br> if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br> {<br><br> sSql += "NULL,";<br><br> }<br><br> else<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "Decimal")<br><br> {<br><br> sSql += dt.Rows[j][i].ToString() + ",";<br><br> }<br><br> else<br><br> {<br><br> sSql += "'" + dt.Rows[j][i].ToString() + "',";<br><br> }<br><br> }<br><br> }<br><br> else<br><br> if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br> {<br><br> sSql += "NULL)";<br><br> }<br><br> else<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "Decimal")<br><br> {<br><br> sSql += dt.Rows[j][i].ToString() + ")";<br><br> }<br><br> else<br><br> {<br><br> sSql += "'" + dt.Rows[j][i].ToString() + "')";<br><br> }<br><br> }<br><br> }<br><br> oleDbCmd.CommandText = sSql;<br><br> oleDbCmd.ExecuteNonQuery();<br><br> }<br><br> }<br><br> catch (System.Exception ex)<br><br> {<br><br> throw ex;<br><br> }<br><br> finally<br><br> {<br><br> //断开连接<br><br> oleDbCmd.Dispose();<br><br> oleDbConn.Close();<br><br> oleDbConn.Dispose();<br><br> }<br><br> }<br><br> 方法五:<br><span><u></u></span> 代码如下: <br> private void CreateExcelFile(string filePath, DataSet ds)<br><br> {<br><br> if (File.Exists(filePath))<br><br> {<br><br> File.Delete(filePath);<br><br> }<br><br> OleDbConnection oleDbConn = new OleDbConnection();<br><br> OleDbCommand oleDbCmd = new OleDbCommand();<br><br> <br><br> try<br><br> {<br><br> string sSql = "";<br><br> oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";<br><br> oleDbConn.Open();<br><br> oleDbCmd.CommandType = CommandType.Text;<br><br> oleDbCmd.Connection = oleDbConn;<br><br> //写列名<br><br> for(int k=0;k<ds.Tables.Count;k++)<br><br> {<br><br> DataTable dt = ds.Tables[k];<br><br> sSql = "CREATE TABLE sheet" + (k + 1).ToString() + "(";<br><br> for (int i = 0; i < dt.Columns.Count; i++)<br><br> {<br><br> if (i < dt.Columns.Count - 1)<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "String" || dt.Columns[i].DataType.Name=="Guid")<br><br> {<br><br> sSql += "["+dt.Columns[i].ColumnName + "] Text,";<br><br> }<br><br> else if (dt.Columns[i].DataType.Name == "DateTime")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";<br><br> }<br><br> else<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";<br><br> }<br><br> }<br><br> else<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "String")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Text)";<br><br> }<br><br> else if (dt.Columns[i].DataType.Name == "DateTime")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";<br><br> }<br><br> else<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";<br><br> }<br><br> }<br><br> }<br><br> oleDbCmd.CommandText = sSql;<br><br> oleDbCmd.ExecuteNonQuery(); for (int j = 0; j < dt.Rows.Count; j++)<br><br> {<br><br> sSql = "INSERT INTO sheet" + (k + 1).ToString() + " VALUES(";<br><br> for (int i = 0; i < dt.Columns.Count; i++)<br><br> {<br><br> if (i < dt.Columns.Count - 1)<br><br> {<br><br> if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br> {<br><br> sSql += "NULL,";<br><br> }<br><br> else<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "Decimal")<br><br> {<br><br> sSql += dt.Rows[j][i].ToString() + ",";<br><br> }<br><br> else<br><br> {<br><br> sSql += "'" + dt.Rows[j][i].ToString().Replace("'", "''") + "',";<br><br> }<br><br> }<br><br> }<br><br> else<br><br> if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br> {<br><br> sSql += "NULL)";<br><br> }<br><br> else<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "Decimal")<br><br> {<br><br> sSql += dt.Rows[j][i].ToString() + ")";<br><br> }<br><br> else<br><br> {<br><br> sSql += "'" + dt.Rows[j][i].ToString().Replace("'","''") + "')";<br><br> }<br><br> }<br><br> }<br><br> oleDbCmd.CommandText = sSql;<br><br> oleDbCmd.ExecuteNonQuery();<br><br> }<br><br> }<br><br> }<br><br> catch (System.Exception ex)<br><br> {<br><br> throw ex;<br><br> }<br><br> finally<br><br> {<br><br> //断开连接<br><br> oleDbCmd.Dispose();<br><br> oleDbConn.Close();<br><br> oleDbConn.Dispose();<br><br> }<br><br> }<br><br> 方法六:<br><span><u></u></span> 代码如下: <br> private void CreateExcelFile(string filePath, DataTable dt1,DataTable dt2,string conditions)<br><br> {<br><br> if (File.Exists(filePath))<br><br> {<br><br> File.Delete(filePath);<br><br> }<br><br> OleDbConnection oleDbConn = new OleDbConnection();<br><br> OleDbCommand oleDbCmd = new OleDbCommand();<p></p></li><li><p> try<br><br> {<br><br> string sSql = "";<br><br> oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";<br><br> oleDbConn.Open();<br><br> oleDbCmd.CommandType = CommandType.Text;<br><br> oleDbCmd.Connection = oleDbConn;<br><br> //写列名<br><br> sSql = "CREATE TABLE sheet1(";<br><br> DataTable dt = dt1.Copy();<br><br> dt.Columns.Remove("MGUID");<br><br> for (int i = 0; i < dt.Columns.Count; i++)<br><br> {<br><br> if (i < dt.Columns.Count - 1)<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "String")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Text,";<br><br> }<br><br> else if (dt.Columns[i].DataType.Name == "DateTime")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";<br><br> }<br><br> else<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";<br><br> }<br><br> }<br><br> else<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "String")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Text)";<br><br> }<br><br> else if (dt.Columns[i].DataType.Name == "DateTime")<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";<br><br> }<br><br> else<br><br> {<br><br> sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";<br><br> }<br><br> }<br><br> }<br><br> oleDbCmd.CommandText = sSql;<br><br> oleDbCmd.ExecuteNonQuery();<br><br> DataView dv = new DataView();<br><br> dv.Table = dt;<br><br> DataView dv1 = new DataView();<br><br> dv1.Table = dt1;<br><br> if (conditions != "")<br><br> {<br><br> dv.RowFilter = conditions;<br><br> dv1.RowFilter = conditions;<br><br> }<br><br> dt = dv.ToTable();<br><br> dt1 = dv1.ToTable();<br><br> string MGUIDs = "";<br><br> for (int j = 0; j < dt.Rows.Count; j++)<br><br> {<br><br> MGUIDs += ",'" + dt1.Rows[j]["MGUID"].ToString() + "'";<br><br> sSql = "INSERT INTO sheet1 VALUES(";<br><br> for (int i = 0; i < dt.Columns.Count; i++)<br><br> {<br><br> if (i < dt.Columns.Count - 1)<br><br> {<br><br> if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br> {<br><br> sSql += "NULL,";<br><br> }<br><br> else<br><br> {<br><br> if (dt.Columns[i].DataType.Name == "Decimal")<br><br> {<br><br> sSql += dt.Rows[j][i].ToString() + ",";<br><br> }<br><br> else<br><br> {<br><br> sSql += "'" + dt.Rows[j][i].ToString() + "',";<br><br> }<br><br> }<br><br> }<br><br> else<br><br> if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br> {<br><br> sSql += "NULL)";<br><br> }<br><br> & </p></li></ol></pre>