小弟做了个导出excel,在用excel导入SQL,单导出来没问题,但是把导出来的excel在导进SQL就有问题.但是新建一个excel导入SQL又没问题,问题就出在,我导出来的只一个内似与excel的工作薄,我要把工作薄转换为excel表的格式,在把excel表里的名字改成Sheet1在导就行,转换格式没什么问题,但是又要转换excel表里的表里就有点麻烦,我导出来的时候又不能改excel表里的名字。所以请高手帮忙。修改我的代码,和给段源代码都可以,或者给个导出导入excel的小项目也可以,求求大家啦!本人邮箱[email protected] MSN: [email protected]
导出代码:
ds = new DataSet();
ds = FEGCRM.Access.AlanSheetDA.Get("","");
DataTable dt = ds.Tables["Alan"];
dt.Columns["Id"].ColumnName = "ID";
dt.Columns["LotNo"].ColumnName = "订单批号";
dt.Columns["Enterprise"].ColumnName = "业";
StringWriter sw = new StringWriter();
GridView dv = new GridView();
dv.DataSource = dt;
dv.DataBind();
dv.AllowPaging = false; Response.ClearContent();
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=Sheet1.xls");
// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码!!!
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/excel";
HtmlTextWriter htw = new HtmlTextWriter(sw);
dv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
导入代码:
string strFullFileName = DateTime.Now.ToString("yyyymmddhhmmss") + ".xls";
string strpath = Server.MapPath("excelfile") + strFullFileName;//确定一个文件名
fileup.PostedFile.SaveAs(strpath);
string mystring = "provider = microsoft.jet.oledb.4.0 ; data source = " + strpath + ";extended properties=excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myda = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myds = new DataSet();
myda.Fill(myds);
try
{
Edit_data(myds.Tables[0]);//传参数进行导入
}
catch (Exception ex)
{
Label1.Text = "导入数据出现错误...";
}
ds = new DataSet();
ds = FEGCRM.Access.AlanSheetDA.Get("", "");
this.GridItem.DataSource = ds;
if (ds.Tables[0].Rows.Count != 0)
{
string m_SqlNo = ds.Tables[0].Rows[0]["Id"].ToString();
SetOrderDetail(m_SqlNo);
}
以上代码导入肯定没错,导出的时候导的不是非正规的excel表格,请高手指点,还有就是导出的时候如何修改excel表里的表名,就是如何把保存的文件名和excel表里的表里分开因为我导入的时候表名只能用Sheet1,所以我导出的时候excel表里的表里一定要是Sheet1,请高手指点.
导出代码:
ds = new DataSet();
ds = FEGCRM.Access.AlanSheetDA.Get("","");
DataTable dt = ds.Tables["Alan"];
dt.Columns["Id"].ColumnName = "ID";
dt.Columns["LotNo"].ColumnName = "订单批号";
dt.Columns["Enterprise"].ColumnName = "业";
StringWriter sw = new StringWriter();
GridView dv = new GridView();
dv.DataSource = dt;
dv.DataBind();
dv.AllowPaging = false; Response.ClearContent();
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=Sheet1.xls");
// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码!!!
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/excel";
HtmlTextWriter htw = new HtmlTextWriter(sw);
dv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
导入代码:
string strFullFileName = DateTime.Now.ToString("yyyymmddhhmmss") + ".xls";
string strpath = Server.MapPath("excelfile") + strFullFileName;//确定一个文件名
fileup.PostedFile.SaveAs(strpath);
string mystring = "provider = microsoft.jet.oledb.4.0 ; data source = " + strpath + ";extended properties=excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myda = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myds = new DataSet();
myda.Fill(myds);
try
{
Edit_data(myds.Tables[0]);//传参数进行导入
}
catch (Exception ex)
{
Label1.Text = "导入数据出现错误...";
}
ds = new DataSet();
ds = FEGCRM.Access.AlanSheetDA.Get("", "");
this.GridItem.DataSource = ds;
if (ds.Tables[0].Rows.Count != 0)
{
string m_SqlNo = ds.Tables[0].Rows[0]["Id"].ToString();
SetOrderDetail(m_SqlNo);
}
以上代码导入肯定没错,导出的时候导的不是非正规的excel表格,请高手指点,还有就是导出的时候如何修改excel表里的表名,就是如何把保存的文件名和excel表里的表里分开因为我导入的时候表名只能用Sheet1,所以我导出的时候excel表里的表里一定要是Sheet1,请高手指点.
优点是:
1.速度快
2.不容易出错
3.无需安装OFFICE
但是也有缺点,就是导出的报表象EXCEL一样,没有格式化.
因为此种方法就是将EXCEL当成数据库来创建的.
以下为详细的程序,只需要建立一个WIN窗体,将以下代码覆盖原来的全部代码就OK了.不过请自己在BIN目录下放一个数据库,或者将程序里面的连接代码改一下:
Imports System.Data.OleDb
Imports System.Data
Imports Scripting
Public Class Form1
Inherits System.Windows.Forms.Form Private dataset As New dataset
Private mytable As DataTable
Private dbpath As String = Application.StartupPath & "\YJ_Manage.mdb" '这里请自己改
Private dbconnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath Private dataadapter As OleDbDataAdapter
Private dataconnection As OleDbConnection
Private dataset As dataset #Region " Windows 窗体设计器生成的代码 " Public Sub New()
MyBase.New() '该调用是 Windows 窗体设计器所必需的。
InitializeComponent() '在 InitializeComponent() 调用之后添加任何初始化 End Sub '窗体重写 dispose 以清理组件列表。
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub 'Windows 窗体设计器所必需的
Private components As System.ComponentModel.IContainer '注意: 以下过程是 Windows 窗体设计器所必需的
'可以使用 Windows 窗体设计器修改此过程。
'不要使用代码编辑器修改它。
Friend WithEvents btnExport As System.Windows.Forms.Button
Friend WithEvents SaveDl As System.Windows.Forms.SaveFileDialog
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.btnExport = New System.Windows.Forms.Button
Me.SaveDl = New System.Windows.Forms.SaveFileDialog
Me.SuspendLayout()
'
'btnExport
'
Me.btnExport.Location = New System.Drawing.Point(104, 128)
Me.btnExport.Name = "btnExport"
Me.btnExport.Size = New System.Drawing.Size(80, 32)
Me.btnExport.TabIndex = 0
Me.btnExport.Text = "Button1"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
Me.ClientSize = New System.Drawing.Size(292, 273)
Me.Controls.Add(Me.btnExport)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False) End Sub #End Region Private Function getdatafromdb(ByVal sqlstr As String) As dataset
Try
dataconnection = New OleDbConnection
dataconnection.ConnectionString = dbconnstr
dataadapter = New OleDbDataAdapter(sqlstr, dataconnection)
dataset = New DataSet
dataset.Clear()
dataadapter.Fill(dataset)
dataconnection.Close()
Catch ex As Exception
MessageBox.Show(Err.Description, "数据错误", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Function
End Try
If dataset.Tables(0).Rows.Count > 0 Then
Return dataset
Else
Return Nothing
End If
End Function Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
Dim MyOleDbCn As New OleDbConnection
Dim MyOleDbCmd As New OleDbCommand
Dim intRowsCnt, intColsCnt As Integer
Dim strSql As String, strFlName As String
Dim Fso As New FileSystemObject dataset = getdatafromdb("select * from Employee_userinfo_main")
mytable = dataset1.Tables(0)
SaveDl.Title = "保持为"
SaveDl.Filter = "xls工作薄|*.xls" If SaveDl.ShowDialog = DialogResult.OK Then
If SaveDl.FileName <> "" Then
strFlName = SaveDl.FileName()
Else
Exit Sub
End If
Else
Exit Sub
End If Try
Me.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor MyOleDbCn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;" & _
"Data Source=" & strFlName & ";" & _
"Extended ProPerties=""Excel 8.0;HDR=Yes;"""
MyOleDbCn.Open()
MyOleDbCmd.Connection = MyOleDbCn
MyOleDbCmd.CommandType = CommandType.Text '第一行插入列标题
strSql = "CREATE TABLE sheet1("
For intColsCnt = 0 To MyTable.Columns.Count - 1
If intColsCnt <> MyTable.Columns.Count - 1 Then
strSql = strSql & MyTable.Columns(intColsCnt).Caption & " text,"
Else
strSql = strSql & MyTable.Columns(intColsCnt).Caption & " text)"
End If
Next
MyOleDbCmd.CommandText = strSql
MyOleDbCmd.ExecuteNonQuery() '插入各行
For intRowsCnt = 0 To MyTable.Rows.Count - 1
strSql = "INSERT INTO sheet1 VALUES('"
For intColsCnt = 0 To MyTable.Columns.Count - 1
If intColsCnt <> MyTable.Columns.Count - 1 Then
strSql = strSql & MyTable.Rows(intRowsCnt).Item(intColsCnt) & "','"
Else
strSql = strSql & MyTable.Rows(intRowsCnt).Item(intColsCnt) & "')"
End If
Next
MyOleDbCmd.CommandText = strSql
MyOleDbCmd.ExecuteNonQuery()
Next
MessageBox.Show("数据已经成功导入EXCEL文件" & strFlName, "数据导出", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ErrCode As Exception
MsgBox("错误信息:" & ErrCode.Message & vbCrLf & vbCrLf & _
"引发事件:" & ErrCode.TargetSite.ToString, MsgBoxStyle.OKOnly + MsgBoxStyle.Information, "错误来源:" & ErrCode.Source)
Exit Sub
Finally
MyOleDbCmd.Dispose()
MyOleDbCn.Close()
MyOleDbCn.Dispose()
Me.Cursor.Current = System.Windows.Forms.Cursors.Default
End Try
End Sub End Class 将EXCEL导入到access,mssql等数据库,datagrid等容器也是用相反类似的方法,将EXCEL作为数据库来使用.
把EXCEL看做数据库操作吧
有人说做成模板,那么如何做模版,如何填数据?您可以在论坛上给我留言,也可以发邮件给我:[email protected],叩谢