string fileName = this.FileUpload1.PostedFile.FileName.ToString();//得到上传文件的物理路径
string oleDBConnString = String.Empty;
oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
oleDBConnString += "Data Source=";
oleDBConnString += fileName;
oleDBConnString += ";Extended Properties=Excel 8.0;";
OleDbConnection oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
DataTable m_tableName = new DataTable();
m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sqlMaster = " SELECT * FROM [Sheet1$]";//指定某列开始读取:Sheet1$B1:B5
OleDbDataAdapter oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
DataSet ds = new DataSet();
oleAdMaster.Fill(ds, "m_tableName");
//以上是把EXCEL读出来
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Student st= new Student();
st.AddNew();
st.SName = ds.Tables[0].Rows[i][1].ToString();
st.Save(); //此处是自己写的方法,就是向Student表插入数据,
}-----------------------------------------------------------------------------
以上代码是正确的,并且本地可以插入到Student表中去,可其他非本机的局域网用户却无法导入数据,不知道是都哪里设置的问题? 请赐教,谢谢。
string oleDBConnString = String.Empty;
oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
oleDBConnString += "Data Source=";
oleDBConnString += fileName;
oleDBConnString += ";Extended Properties=Excel 8.0;";
OleDbConnection oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
DataTable m_tableName = new DataTable();
m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sqlMaster = " SELECT * FROM [Sheet1$]";//指定某列开始读取:Sheet1$B1:B5
OleDbDataAdapter oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
DataSet ds = new DataSet();
oleAdMaster.Fill(ds, "m_tableName");
//以上是把EXCEL读出来
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Student st= new Student();
st.AddNew();
st.SName = ds.Tables[0].Rows[i][1].ToString();
st.Save(); //此处是自己写的方法,就是向Student表插入数据,
}-----------------------------------------------------------------------------
以上代码是正确的,并且本地可以插入到Student表中去,可其他非本机的局域网用户却无法导入数据,不知道是都哪里设置的问题? 请赐教,谢谢。
是这个意思
就相当于普通用户把自己的EXCEL表导入到我本地的Student表
请问如何解决?
lispo:文件路径本地是可以的;权限怎么搞?请明示。
是这个意思
就相当于普通用户把自己的EXCEL表导入到我本地的Student表
请问如何解决?
lispo:文件路径本地是可以的;权限怎么搞?请明示。-----------------------------
我以为是他已经把文件传入服务器上面了.在服务器不能读写应该是权限问题(能传上去就有文件写的权限了,是不是数据库权限就不晓得了).判断错误,不好意思哈.其实一般的做法是把excel上传到服务器上导入数据的.同时建议LZ采用预缆的方式.
有没什么代码 参考下啊 谢谢
[email protected]
Imports System.Text.RegularExpressions
Partial Class addmuban
Inherits System.Web.UI.Page
Public logic_url, temp_url, FileName As String
Dim ss As New CJ
'''文件上传
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
FileName = ""
If FileUpload1.PostedFile.ContentLength <> 0 Then
' 获取文件名()
Dim Temps() As String = Split(FileUpload1.PostedFile.FileName, "\")
FileName = Temps(Temps.Length - 1)
'保存文件
FileUpload1.PostedFile.SaveAs(Server.MapPath("") & "\" & DropDownList2.SelectedValue & "\" & FileName)
'显示上传结果
End If
logic_url = HttpContext.Current.Server.MapPath("in\")
temp_url = HttpContext.Current.Server.MapPath(DropDownList2.SelectedValue & "\" & FileName)
Label2.Text = logic_url
Label3.Text = temp_url
Label4.Text = FileName
Label5.Text = DropDownList1.SelectedValue
Dim sr As StreamReader
Dim str As String = ""
Try
sr = New StreamReader(temp_url, System.Text.Encoding.GetEncoding("utf-8"))
str = sr.ReadToEnd()
Catch ex As Exception
HttpContext.Current.Response.Write(ex.Message)
HttpContext.Current.Response.End()
sr.Close()
End Try
''数据筛选
Dim dt As System.Data.DataTable = New System.Data.DataTable
Dim dr As System.Data.DataRow
dt.Columns.Add(New System.Data.DataColumn("table", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("para", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("status", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("sql", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("height", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("width", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("other", GetType(String)))
'替换过程,写数组实现
'检查权限专用{checklogin}
Dim r As New Regex("\{[a-zA-Z0-9_]+:[a-zA-Z0-9_]+:[1-4]:[0-9]*:[0-9]*:[0-9]*}")
Dim gnums() As Integer = r.GetGroupNumbers()
Dim i, j As Integer
Dim m As Match = r.Match(str)
While m.Success
For i = 0 To gnums.Length - 1
Dim g As Group = m.Groups(gnums(i))
Dim cc As CaptureCollection = g.Captures
For j = 0 To cc.Count - 1
'MsgBox(cc(j).Value)
Dim kk() As String = Split(cc(j).Value, ":")
dr = dt.NewRow
dr(0) = Replace(kk(0), "{", "")
dr(1) = kk(1)
dr(2) = kk(2)
dr(3) = ""
dr(4) = kk(3)
dr(5) = kk(4)
dr(6) = Replace(kk(5), "}", "")
dt.Rows.Add(dr)
Next
Next
m = m.NextMatch()
End While
Dim dv As System.Data.DataView = New System.Data.DataView(dt)
GridView1.DataSource = dv
GridView1.DataBind()
Panel1.Visible = False
GridView1.Visible = True
Label1.Visible = True
Button2.Visible = True
' Response.Redirect("in/" + htmlfilename)
End Sub
'''写入操作
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sr As StreamReader
Dim sw As StreamWriter
Dim i, TopID, TopBiaoID As Integer
Dim aa(GridView1.Rows.Count, 7) As String
Dim bll As New tbl_para
Dim str As String = ""
logic_url = Label2.Text
temp_url = Label3.Text
FileName = Label4.Text
Try
sr = New StreamReader(temp_url, System.Text.Encoding.GetEncoding("utf-8"))
str = sr.ReadToEnd()
Catch ex As Exception
HttpContext.Current.Response.Write(ex.Message)
HttpContext.Current.Response.End()
sr.Close()
End Try
Dim htmlfilename As String = Replace(Replace(FileName, ".html", ".aspx"), ".htm", ".aspx")
'執行插入操作
bll.insert(FileName, str, logic_url, "in/" & htmlfilename, "1", Val(Label5.Text))
TopID = bll.GetID()
TopBiaoID = bll.GetBiaoID
For i = 0 To GridView1.Rows.Count - 1
aa(i, 0) = GridView1.Rows(i).Cells(0).Text
aa(i, 1) = GridView1.Rows(i).Cells(1).Text
aa(i, 2) = GridView1.Rows(i).Cells(2).Text
aa(i, 3) = Replace(GridView1.Rows(i).Cells(3).Text, " ", "")
aa(i, 4) = Replace(GridView1.Rows(i).Cells(4).Text, " ", "")
aa(i, 5) = Replace(GridView1.Rows(i).Cells(5).Text, " ", "")
aa(i, 6) = CType(GridView1.Rows(i).FindControl("TextBox1"), TextBox).Text
bll.para_insert(TopID, aa(i, 1), aa(i, 0), aa(i, 2), aa(i, 6))
Dim str1 As String = "{" & aa(i, 0) & ":" & aa(i, 1) & ":" & aa(i, 2) & ":" & aa(i, 3) & ":" & aa(i, 4) & ":" & aa(i, 5) & "}"
'替換操作
Select Case Val(aa(i, 2))
Case 1
str = Replace(str, str1, ss.CreateTextBox(aa(i, 1)))
Case 2
str = Replace(str, str1, aa(i, 1))
Case 3
str = Replace(str, str1, ss.CreateDropdownlist(aa(i, 1), aa(i, 6)))
Case 4
str = Replace(str, str1, ss.CreateDateList(aa(i, 1)))
Case Else
str = Replace(str, str1, "")
End Select
Next
str = Replace(str, "action=""""", "action=""../info_deal.aspx?requestid=" & TopBiaoID & """")
Try
sw = New StreamWriter(logic_url + htmlfilename, False, System.Text.Encoding.GetEncoding("utf-8"))
sw.Write(str)
sw.Flush()
Catch ex As Exception
HttpContext.Current.Response.Write(ex.Message)
HttpContext.Current.Response.End()
Finally
sw.Close()
End Try
Response.Redirect("in/" + htmlfilename)
End Sub Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If e.Row.Cells(2).Text <> "3" And e.Row.RowIndex > -1 Then
e.Row.Cells(6).Text = ""
End If
End SubEnd Class
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="addexcel.aspx.vb" Inherits="addexcel" %><!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>ECXEL文件上傳</title>
<link href="tab.CSS" rel="stylesheet" type="text/css" />
<link href="datagrid.css" rel="stylesheet" type="text/css" />
</head>
<body style="text-align: center">
<form id="form1" runat="server">
<asp:Panel ID="Panel1" runat="server" Height="94px" Width="587px">
<asp:Label ID="Label1" runat="server" Font-Bold="True" Text="临时工EXCEL表单上传" Width="180px"></asp:Label><br />
<br />
<asp:Label ID="Label2" runat="server" ForeColor="#000033" Text="请选择上传文件"></asp:Label><asp:FileUpload
ID="FileUpload1" runat="server" /><br />
<asp:Button ID="Button1" runat="server" Text="上传" Width="45px" />
</asp:Panel>
<asp:Panel ID="Panel2" runat="server" Height="146px" Visible="False" Width="100%">
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">
<FooterStyle BackColor="White" ForeColor="#000066" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
</asp:GridView>
<br />
<asp:Button ID="Button2" runat="server" Text="导入并统计临时工数据" Font-Bold="True" Width="159px" />
</asp:Panel>
<asp:Panel ID="Panel3" runat="server" Height="146px" Visible="False" Width="100%">
<strong><span style="font-size: 14pt; color: #000099">短期工所在部门信息统计报表</span></strong><br />
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataKeyNames="ID" DataSourceID="AccessDataSource1">
<FooterStyle BackColor="White" ForeColor="#000066" />
<Columns>
<asp:BoundField DataField="ID" HeaderText="系统编号" InsertVisible="False" ReadOnly="True"
SortExpression="ID" />
<asp:BoundField DataField="chu" HeaderText="处级" SortExpression="chu" />
<asp:BoundField DataField="bu" HeaderText="部级" SortExpression="bu" />
<asp:BoundField DataField="ke" HeaderText="课级" SortExpression="ke" />
<asp:BoundField DataField="zu" HeaderText="组级" SortExpression="zu" />
<asp:BoundField DataField="iszuke" HeaderText="显示组" SortExpression="iszuke" />
<asp:BoundField DataField="shortna" HeaderText="短期工(男)" SortExpression="shortna" />
<asp:BoundField DataField="shortnv" HeaderText="短期工(女)" SortExpression="shortnv" />
<asp:BoundField DataField="szshortnan" HeaderText="上周短期工(男)" SortExpression="szshortnan" />
<asp:BoundField DataField="szshortnv" HeaderText="上周短期工(女)" SortExpression="szshortnv" />
<asp:BoundField DataField="last_update_time" HeaderText="更新日期" HtmlEncode="False"
SortExpression="last_update_time" />
</Columns>
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
</asp:GridView>
<asp:Button ID="Button3" runat="server" Text="确认统计" /><asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/renzi.mdb"
SelectCommand="SELECT * FROM [depinfo]"></asp:AccessDataSource>
<span style="font-size: 10pt">如果发现与实际不相符的部分,请检查统计资料是否完整,红色表示与上周人数不同</span></asp:Panel>
</form>
</body>
</html>
Imports System.Data.SqlClient
Partial Class addexcel
Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If FileUpload1.PostedFile.ContentLength = 0 Then
Label1.Text = "<FONT COLOR=RED>上传失败或文件不存在!</FONT>"
ElseIf Right(FileUpload1.PostedFile.FileName, 4) <> ".xls" Then
Label1.Text = "<FONT COLOR=RED>文件格式错误!</FONT>"
Else
' 获取文件名()
Dim Temp() As String = Split(FileUpload1.PostedFile.FileName, "\")
Dim FileName As String = Temp(Temp.Length - 1)
'保存文件
FileUpload1.PostedFile.SaveAs(Server.MapPath("") & "/excel/lishi.xls")
'显示上传结果
Label1.Text = "文件上传成功!<br>上传文件:" & FileName
End If
Dim sql As String = "select * from [Sheet1$]"
Dim conn As String = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Server.MapPath("") + "/excel/lishi.xls;Extended Properties=Excel 8.0"
Dim thisconnection As OleDb.OleDbConnection = New OleDb.OleDbConnection(conn)
thisconnection.Open()
Dim mycommand As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, thisconnection)
Dim ds As New DataSet
mycommand.Fill(ds, "[Sheet1$]")
ds.Tables("[Sheet1$]").Columns.RemoveAt(12)
ds.Tables("[Sheet1$]").Columns.RemoveAt(11)
ds.Tables("[Sheet1$]").Columns.RemoveAt(10)
ds.Tables("[Sheet1$]").Columns.RemoveAt(9)
ds.Tables("[Sheet1$]").Columns.RemoveAt(8)
ds.Tables("[Sheet1$]").Columns.RemoveAt(7)
ds.Tables("[Sheet1$]").Columns.RemoveAt(6)
ds.Tables("[Sheet1$]").Columns.RemoveAt(5)
ds.Tables("[Sheet1$]").Columns.RemoveAt(4)
ds.Tables("[Sheet1$]").Columns.RemoveAt(2)
ds.Tables("[Sheet1$]").Columns.Add("部级核对")
ds.Tables("[Sheet1$]").Columns.Add("课级核对")
ds.Tables("[Sheet1$]").Columns.Add("组级核对")
thisconnection.Close()
GridView1.DataSource = ds
GridView1.DataBind()
Panel1.Visible = False
Panel2.Visible = True
End Sub Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
Dim kk As New renzibll
If e.Row.RowIndex > -1 Then
If e.Row.Cells(3).Text <> "" Then
e.Row.Cells(6).Text = kk.Getbu(e.Row.Cells(3).Text)
End If
If e.Row.Cells(4).Text <> "" Then
e.Row.Cells(7).Text = kk.GetKe(Replace(e.Row.Cells(4).Text, "课", ""))
End If
If e.Row.Cells(5).Text <> "" Then
e.Row.Cells(8).Text = kk.GetZu(e.Row.Cells(5).Text)
End If
If e.Row.Cells(6).Text = "" Or e.Row.Cells(7).Text = "" Or e.Row.Cells(8).Text = "" Then
Button2.Enabled = False
Button2.Text = "请修正核对部门"
End If
End If
End Sub Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sql As String = "select * from [Sheet1$]"
Dim conn As String = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Server.MapPath("") + "/excel/lishi.xls;Extended Properties=Excel 8.0"
Dim thisconnection As OleDb.OleDbConnection = New OleDb.OleDbConnection(conn)
thisconnection.Open()
Dim mycommand As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, thisconnection)
Dim ds As New DataSet
mycommand.Fill(ds, "[Sheet1$]")
thisconnection.Close()
Dim i = 0, rowcount As Integer = ds.Tables("[Sheet1$]").Rows.Count
Dim tt As New renzibll
Try
tt.ini()
For i = 0 To rowcount - 1
If GridView1.Rows(i).Cells(8).Text <> "" Then
tt.zu_add(GridView1.Rows(i).Cells(8).Text, GridView1.Rows(i).Cells(3).Text)
End If
Next
Catch ex As Exception
Response.Write(ex)
End Try
Dim ll As New linshi
ll.IsUpdate()
Panel2.Visible = False
Panel3.Visible = True
End Sub Protected Sub GridView2_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView2.RowDataBound
If e.Row.RowIndex > -1 Then
e.Row.Cells(10).Text = Format(Convert.ToDateTime(e.Row.Cells(10).Text), "yyyy年MM月dd日")
If e.Row.Cells(5).Text = 1 Then
e.Row.Cells(5).Text = "是"
Else
e.Row.Cells(5).Text = "否"
End If
If e.Row.Cells(6).Text <> e.Row.Cells(8).Text Then
e.Row.Cells(6).Text = "<font color=red>" & e.Row.Cells(6).Text & "</font>"
End If
If e.Row.Cells(7).Text <> e.Row.Cells(9).Text Then
e.Row.Cells(7).Text = "<font color=red>" & e.Row.Cells(7).Text & "</font>"
End If
End If
End Sub
End Class