传统 excel 导入 与 导出代码
来源:互联网 发布:阿里云防御100g多少钱 编辑:程序博客网 时间:2024/06/10 14:27
/// <summary>
/// 导出
/// </summary>
/// <param name="ds"></param>
protected void OracelInPutExcel(DataSet ds)
{
string DataTiem = System.DateTime.Now.Date.ToString("yyyy-MM-dd");
string outputFileName = "INF_HGPRICE_" + DataTiem + ".xls";
int i = ds.Tables[0].Rows.Count;
DataTable DT = ds.Tables[0];
//生成将要存放结果的Excel文件的名称
string NewFileName = DataTiem + ".xls";
//转换为物理路径
NewFileName = Server.MapPath("config/" + NewFileName);
//根据模板正式生成该Excel文件
File.Copy(Server.MapPath("config/INF_HGPRICE.xls"), NewFileName, true);
//建立指向该Excel文件的数据库连接
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + NewFileName + ";Extended Properties='Excel 8.0;'";
OleDbConnection Conn = new OleDbConnection(strConn);
//打开连接,为操作该文件做准备
Conn.Open();
OleDbCommand Cmd = new OleDbCommand("", Conn);
foreach (DataRow DR in DT.Rows)
{
string XSqlString = "insert into [INF_HGPRICE$]";
XSqlString += "([公司编码],[公司名称],[互供料名称],[同一地区互供价],[不同地区互供价],[发布日期]) values(";
XSqlString += "'" + DR["COMPCODE"] + "',";
XSqlString += "'" + DR["COMPANYNAME"] + "',";
XSqlString += "'" + DR["PRODUCT_NAME"] + "',";
XSqlString += "'" + DR["PRICE1"] + "',";
XSqlString += "'" + DR["PRICE2"] + "',";
XSqlString += "'" + DR["PUBLISHDATE"] + "')";
Cmd.CommandText = XSqlString;
Cmd.ExecuteNonQuery();
}
//操作结束,关闭连接
Conn.Close();
//打开要下载的文件,并把该文件存放在FileStream中
System.IO.FileStream Reader = System.IO.File.OpenRead(NewFileName);
//文件传送的剩余字节数:初始值为文件的总大小
long Length = Reader.Length;
Response.Buffer = false;
Response.AddHeader("Connection", "Keep-Alive");
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(outputFileName));
Response.AddHeader("Content-Length", Length.ToString());
byte[] Buffer = new Byte[10000]; //存放欲发送数据的缓冲区
int ByteToRead; //每次实际读取的字节数
while (Length > 0)
{
//剩余字节数不为零,继续传送
if (Response.IsClientConnected)
{
//客户端浏览器还打开着,继续传送
ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据
Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
Response.Flush(); //立即写入客户端
Length -= ByteToRead; //剩余字节数减少
}
else
{
//客户端浏览器已经断开,阻止继续循环
Length = -1;
}
}
//关闭该文件
Reader.Close();
//删除该Excel文件
File.Delete(NewFileName);
}
//////----------------------------------导入---------------------------------------
/// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnInPut_Click(object sender, EventArgs e)
{
InputExcel(gvINF_HgPrice);
BindData();
}
private void InputExcel(GridView gridview)
{
try
{
string strConn;
string filename = this.txtFileName.Text.Replace("&", "//");
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source= '" + filename + "';" + "Extended Properties=/"Excel 8.0;IMEX=1;/"";
string sqlselect = " SELECT 公司编码 as COMPCODE,公司名称 as COMPANYNAME,互供料名称 as PRODUCT_NAME,同一地区互供价 as PRICE1," +
" 不同地区互供价 as PRICE2,发布日期 as PUBLISHDATE " +
" FROM [INF_HGPRICE$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(sqlselect, strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
int j = myDataSet.Tables[0].Rows.Count;
List<string> sqlList = new List<string>();
string sql = string.Empty;
DataSet ds = (DataSet)ViewState["ds"];
Decimal Price1 = 0;
Decimal Price2 = 0;
for (int i = 0; i < myDataSet.Tables[0].Rows.Count; i++)
{
string compcode = myDataSet.Tables[0].Rows[i]["COMPCODE"].ToString();
string companyname = myDataSet.Tables[0].Rows[i]["COMPANYNAME"].ToString();
string product_name = myDataSet.Tables[0].Rows[i]["PRODUCT_NAME"].ToString();
if (myDataSet.Tables[0].Rows[i]["PRICE1"].ToString() != "")
{
Price1 = Convert.ToDecimal(myDataSet.Tables[0].Rows[i]["PRICE1"]);
}
else
{
Price1 = 0;
}
if (myDataSet.Tables[0].Rows[i]["PRICE2"].ToString() != "")
{
Price2 = Convert.ToDecimal(myDataSet.Tables[0].Rows[i]["PRICE2"]);
}
else
{
Price2 = 0;
}
string publishdate = myDataSet.Tables[0].Rows[i]["PUBLISHDATE"].ToString();
if (ds.Tables[0].Select("COMPCODE ='" + compcode + "' and PUBLISHDATE = '" + publishdate + "'").Length > 0)
{
sql = "update PPMS_INF_HGPRICE set PRICE1 =" + Price1 + ",PRICE2 =" + Price2 + " " +
" where COMPCODE ='" + compcode + "' and PUBLISHDATE = to_date('" + publishdate + "','yyyy-mm-dd hh24:mi:ss')";
}
else
{
sql = "insert into PPMS_INF_HGPRICE(COMPCODE,PRODUCT_ID,PRODUCT_NAME,PRICE1,PRICE2,PUBLISHDATE) " +
" values ('" + compcode + "','P_SNY','"+product_name+"," + Price1 + "', " +
" " + Price2 + ",to_date('" + publishdate + "','yyyy-mm-dd hh24:mi:ss'))";
}
sqlList.Add(sql);
}
logic.CrudepriceExcel(sqlList);
this.ClientScript.RegisterStartupScript(this.GetType(), "input", "<script>alert('导入成功')</script>");
}
catch (Exception ex)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "input", "<script>alert('" + ex.Message + "')</script>");
}
}
- 传统 excel 导入 与 导出代码
- Excel导出导入代码
- Excel导入导出提示代码
- sqlserver 与access,excel互相导入导出代码
- 简单Excel导出与导入
- SQL与Excel导入导出
- excel的导出与导入
- excel的导入与导出
- Excel的导入与导出
- C#导出导入到Excel的代码
- vs2010导入导出excel表格代码
- js导入导出excel(实例代码)
- Oracle数据泵导出导入与传统导出导入的区别
- Oracle数据泵导出导入与传统导出导入的区别
- Oracle数据泵导出导入与传统导出导入的区别
- Oracle数据泵导出导入与传统导出导入的区别
- Excel 与 SQL 数据库互相导入导出
- Excel文件的导出与导入
- C#核心概念--装箱和拆箱(什么是装箱和拆箱)
- Linux系统中,read文件过程分析
- Struts.properties
- 职场白领需要学会管理“数码痕迹”
- 快两个月了
- 传统 excel 导入 与 导出代码
- BitBlt
- 数据库链接服务器
- centos linux 下VncServer 配置与 Win 下 Vnc 客户端
- Windows Phone 7平台上提供Xbox Live游戏服务
- Occam
- StretchBlt、SetSTretchBltMode
- fopen和open的区别
- ORA-01950: no privileges on tablespace 'xxxxxx'