excel导入导出

[buzhunmeiyouni]

Private Sub Command1_Click()
Dim strconn As String
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
CommonDialog1.Filter = ".xls|*.xls"
CommonDialog1.DialogTitle = "请选择要导入的文件"
CommonDialog1.ShowOpen
strtemp = "[odbc;driver={SQL server};server=127.0.0.1;database=AIS20170828184882;uid=sa;pwd=123456]"
'strconn = "provider=microsoft.ace.oledb.4.0;data source=" & CommonDialog1.FileName & ";extended properties=excel 8.0"
If UCase(Right(ExcelFileAddress, 4)) = UCase(".xls") Then
strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CommonDialog1.FileName & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';Persist Security Info=False"
Else
strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CommonDialog1.FileName & ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';Persist Security Info=False"
End If
cn.Open strconn
strsql = "insert into" & strtemp & ".aa select * from [sheet1$]"
cn.Execute strsql
MsgBox "ddd", vbExclamation + vbOKOnly
cn.Close
Set cn = Nothing

End Sub
/************/
Private Sub Command2_Click()
Dim conn As String
Dim rs As New ADODB.Recordset
Dim Excel As Object
Dim workbook As Object
Dim sheet As Object
Dim i&, j&
'SQL数据库的连接字串
conn = "Provider=SQLOLEDB.1;Data source=127.0.0.1;initial catalog=AIS20170828184882;User Id=sa;Password=123456"
'打开到sql的连接，读取xxx表数据到rs
rs.Open "select '学号','名称','课程'", conn, adOpenKeyset, adLockReadOnly
'excel对象、工作簿、工作表
Set Excel = CreateObject("Excel.Application")
Set workbook = Excel.Workbooks().Add()
Set sheet = workbook.sheets(1)
sheet.Name = "课程"

Excel.Visible = True
'循环读取rs并写入到excel
For i = 1 To rs.Fields.Count
j = 1
rs.MoveFirst
Do While Not rs.EOF
j = j + 1
sheet.Cells(j, i) = rs.Fields(i - 1).Value
rs.MoveNext

Loop
Next
rs.Close
Set rs = Nothing

End Sub

这两段代码怎么合并啊，excel数据导入数据库表中，先导出一个空的excel，在里面插入数据，再把这个excel导入到数据库表中

[buzhunmeiyouni]

各位大神人呢

[buzhunmeiyouni]

一个人都没有

[赵4老师]

另存为.csv

[qq_38115572]

private void showtoexcel(DataGridView dgv) { SaveFileDialog dlg = new SaveFileDialog(); //默然文件后缀 dlg.DefaultExt = "xls "; //文件后缀列表 dlg.Filter = "EXCEL文件(*.XLS)|*.xls "; //默然路径是系统当前路径 dlg.InitialDirectory = Directory.GetCurrentDirectory(); //打开保存对话框 if (dlg.ShowDialog() == DialogResult.Cancel) return; //返回文件路径 string fileNameString = dlg.FileName; //验证strFileName是否为空或值无效 if (fileNameString.Trim() == "") { return; } //定义表格内数据的行数和列数 int rowscount = dgv.Rows.Count; int colscount = dgv.Columns.Count; //行数必须大于0 if (rowscount <= 0) { MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //列数必须大于0 if (colscount <= 0) { MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //行数不可以大于65536 if (rowscount > 65536) { MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //列数不可以大于255 if (colscount > 255) { MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //验证以fileNameString命名的文件是否存在，如果存在删除它 FileInfo file = new FileInfo(fileNameString); if (file.Exists) { try { file.Delete(); } catch (Exception error) { MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } } Excel.Application objExcel = null; Excel.Workbook objWorkbook = null; Excel.Worksheet objsheet = null; try { //申明对象 objExcel = new Microsoft.Office.Interop.Excel.Application(); objWorkbook = objExcel.Workbooks.Add(Missing.Value); // objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet; //设置EXCEL不可见 objExcel.Visible = false; //向Excel中写入表格的表头 int displayColumnsCount = 1; for (int i = 0; i <= dgv.ColumnCount - 1; i++) { if (dgv.Columns[i].Visible == true) { objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim(); displayColumnsCount++; } } //设置进度条 //tempProgressBar.Refresh(); //tempProgressBar.Visible = true; //tempProgressBar.Minimum=1; //tempProgressBar.Maximum=dgv.RowCount; //tempProgressBar.Step=1; //向Excel中逐行逐列写入表格中的数据 for (int row = 0; row <= dgv.RowCount - 1; row++) { //tempProgressBar.PerformStep(); displayColumnsCount = 1; for (int col = 0; col < colscount; col++) { if (dgv.Columns[col].Visible == true) { try { objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim(); displayColumnsCount++; } catch (Exception) { } } } } objExcel.Columns.EntireColumn.AutoFit();//列宽自适应 //隐藏进度条 //tempProgressBar.Visible = false; //保存文件 objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch (Exception error) { MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } finally { //关闭Excel应用 if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value); if (objExcel.Workbooks != null) objExcel.Workbooks.Close(); if (objExcel != null) objExcel.Quit(); objsheet = null; objWorkbook = null; objExcel = null; } MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); } //导出excel