前台页面
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="test2.aspx.cs" Inherits="test2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="fileDataInput" runat="server" Width="400px" Height="25px" />
<asp:Button ID="btn_DataInPut" runat="server" Text="导入数据" Height="25px"
OnClick="btn_DataInPut_Click" />
</div>
</form>
</body>
</html>
<%=Session["Msg"] %>
后台cs
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;
using System.Data.OleDb;
using yueue.ADOKeycap;
using System.Data.Common;
public partial class test2 : System.Web.UI.Page
{
private Database dbSys = DataBase.GetData();
private System.IO.Ports.SerialPort serialPort1;
private System.ComponentModel.IContainer components;
private OleDbConnection conn = null;
protected void Page_Load(object sender, EventArgs e)
{
}
//打开Excel连接,读取sheet页数据
private DataSet ExcelToDataBase(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;IMEX=1;'";
conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "[sheet1]");
return ds;
}
//关闭连接
private void CloseConn()
{
conn.Close();
}
protected void btn_DataInPut_Click(object sender, EventArgs e)
{
string filePath = fileDataInput.PostedFile.FileName;
string ExcelPath = filePath;
if (ExcelPath.Length > 0)
{
DataSet ds = ExcelToDataBase(ExcelPath);
CloseConn();
int rowsCount = ds.Tables[0].Rows.Count;
int columnsCount = ds.Tables[0].Columns.Count;
if (rowsCount > 0) // 有数据存在
{
if (columnsCount > 0)
{
GetDataFromExcel(ds, rowsCount); //读取Excel表中的数据,组合成Sql语句,并插入到数据库
}
else
{
Session["Msg"] = "<script>alert('Excel文件中数据格式有问题,请核查后再导入数据!')</script>";
return;
}
}
Session["Msg"] = "<script>alert('数据导入成功!')</script>";
return;
}
else
{
Response.Write("<script>alert('读取Excel文件时出现异常错误');</script>");
return;
}
//System.IO.FileInfo fi = new FileInfo(ExcelPath);
//StreamReader sr = fi.OpenText();
//string strSql = sr.ReadToEnd(); //全读出来
//if (strSql.Length > 0)
//{
// if (dbSys.ExecuteNonQuery(strSql) > 0)
// {
// Session["Msg"] = "<script>alert('数据导入成功!')</script>";
// return;
// }
//}
//else
//{
// Session["Msg"] = "<script>alert('导入失败!')</script>";
// return;
//}
}
// 读取Excel表中的数据,组合成Sql语句,并插入到数据库
private void GetDataFromExcel(DataSet ds, int rowsCount)
{
string strSql = "";
try
{
for (int m = 0; m < rowsCount; m++)
{
//strSql += "update t_sys_Menu set ";
//strSql += "FileFolder = '" + ds.Tables[0].Rows[m][5].ToString().Trim() + "'";
strSql += "FileName = '" + ds.Tables[0].Rows[m][5].ToString().Trim() + "',";
strSql += "WSMC = '" + ds.Tables[0].Rows[m][13].ToString().Trim() + "',";
strSql += "nWidth = '" + ds.Tables[0].Rows[m][14].ToString().Trim() + "',";
strSql += "nHeight = '" + ds.Tables[0].Rows[m][15].ToString().Trim() + "',";
strSql += "JBXX = '" + ds.Tables[0].Rows[m][16].ToString().Trim() + "'";
//strSql += "where id='"+ ds.Tables[0].Rows[m][1].ToString().Trim() +"'";
string strtempp1 = ds.Tables[0].Rows[m][1].ToString().Trim();//221205
string strtempp5 = ds.Tables[0].Rows[m][5].ToString().Trim();
string strXZDJ = "";
string strLDLX = "";
string strSSZD = "";
string strSSDD = "";
strSql += " insert into T_DLDM(ID,DLDM,DLMC,DLLX,DLXX,LMJG,XZDJ,LDLX,FFSS,SSZD,XZQH,SSDD) values ( ";
strSql += " '" + Common.GetNo() + "','" + ds.Tables[0].Rows[m][0].ToString().Trim() + "','" + ds.Tables[0].Rows[m][1].ToString().Trim() + "','" + ds.Tables[0].Rows[m][2].ToString().Trim() + "','" + ds.Tables[0].Rows[m][3].ToString().Trim() + "',";
strSql += " '" + ds.Tables[0].Rows[m][4].ToString().Trim() + "','" + strXZDJ + "','" + strLDLX + "','" + ds.Tables[0].Rows[m][5].ToString().Trim() + "','" + strSSZD + "','" + ds.Tables[0].Rows[m][6].ToString().Trim() + "','" + strSSDD + "')";
}
if (strSql.Length > 0)
{
dbSys.ExecuteNonQuery(strSql);
}
}
catch
{
}
}
private void InitializeComponent()
{
this.components = new System.ComponentModel.Container();
this.serialPort1 = new System.IO.Ports.SerialPort(this.components);
}
}