菜鸟求高手指教将excel导入sqlserver数据库问题
using System;
using System.Collections.Generic;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Configuration;
using System.IO;
//using System.Collections;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
namespace WindowsFormsApplication1
{
public partial class Form2 : Form
{
public string strFileName;
public Form2()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
System.Windows.Forms.OpenFileDialog fg = new OpenFileDialog();
fg.ShowDialog();
strFileName = fg.FileName;
}
private void button2_Click(object sender, EventArgs e)
{
string strCell;
string strCell2;
string strCell3;
string strCell4;
string strCell5;
string strCell6;
string strCell7;
string strCell8;
string strCell9;
string strCell10;
string strCell11;
string strCell12;
string strCell13;
string strCell14;
string strCell15;
string strCell16;
string strCell17;
string strCell18;
string[] cellsrng1;
int iFlg = 0;
int iCol;//列
int iRow;//行
string aa;
int maxRow;//最大行数
//创建Application对象
Excel.Application xApp = new Excel.Application();
xApp.Visible = false;
//打开工作表
Excel.Workbook xBook = xApp.Workbooks.Add(strFileName);
//工作表最大有效行数列数
Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1];
maxRow = xSheet.Application.get_Range("A65536", Type.Missing).get_End(Excel.XlDirection.xlUp).Row;[/color] SqlConnection sc1 = new SqlConnection();
sc1.ConnectionString = "Data Source=PC-201007291119;Initial Catalog=sb;Integrated Security=True";
string sql5 = "TRUNCATE TABLE sbtz";
try
{
sc1.Open();
//SqlCommand cmd0 = new SqlCommand("delete * from 月度成本表", sc);
//cmd0.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand(sql5, sc1);
cmd.ExecuteNonQuery();
sc1.Close();
}
catch (SqlException sqlexp)
{
sc1.Close();
MessageBox.Show(sqlexp.Message);
MessageBox.Show(sql5);
}
for (iRow = 3; iRow <= maxRow; iRow++)
{
strCell = "A" + iRow;//序
strCell2 = "B" + iRow;//类别
strCell3 = "C" + iRow; //设备编号
strCell4 = "D" + iRow;//设备名称
strCell5 = "E" + iRow;//型号规格
strCell6 = "F" + iRow;//制造国厂
strCell7 = "G" + iRow;//制造日期
strCell8 = "H" + iRow; //原值(元)
strCell9 = "I" + iRow;//类别1
strCell10 = "J" + iRow;//主
strCell11 = "K" + iRow;//F机
strCell12 = "L" + iRow;//F电
strCell13 = "M" + iRow; //使用单位
strCell14 = "N" + iRow;//出厂编号
strCell15 = "O" + iRow;//标准页号
strCell16 = "p" + iRow;//状况
strCell17 = "Q" + iRow;//令号
strCell18 = "R" + iRow; //备注
Microsoft.Office.Interop.Excel.Range rng1 = xSheet.get_Range(strCell, Type.Missing); Microsoft.Office.Interop.Excel.Range rng2 = xSheet.get_Range(strCell2, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng3 = xSheet.get_Range(strCell3, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng4 = xSheet.get_Range(strCell4, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng5 = xSheet.get_Range(strCell5, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng6 = xSheet.get_Range(strCell6, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng7 = xSheet.get_Range(strCell7, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng8 = xSheet.get_Range(strCell8, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng9 = xSheet.get_Range(strCell9, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng10 = xSheet.get_Range(strCell10, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng11 = xSheet.get_Range(strCell11, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng12 = xSheet.get_Range(strCell12, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng13 = xSheet.get_Range(strCell13, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng14 = xSheet.get_Range(strCell14, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng15 = xSheet.get_Range(strCell15, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng16 = xSheet.get_Range(strCell16, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng17 = xSheet.get_Range(strCell17, Type.Missing);
Microsoft.Office.Interop.Excel.Range rng18 = xSheet.get_Range(strCell18, Type.Missing);
SqlConnection sc = new SqlConnection();
sc.ConnectionString = "Data Source=PC-201007291119;Initial Catalog=sb;Integrated Security=True";
//strFileName = strFileName.Substring(0, strFileName.Length - 4);
//数据库插入语句
string sql1 = "insert into sbtz (xu,leibie,sbbh,sbmc,xhgg,zzgc,zzrq,yz,leibie1,zhu,fj,fd,sydw,ccbh,bzyh,zk,lh,bz) values('" + rng1.Value2 + "' ,'" + rng2.Value2 + "','" + rng3.Value2 + "','" + rng4.Value2 + "','" + rng5.Value2 + "','" + rng6.Value2 + "','" + rng7.Value2 + "','" + rng8.Value2 + "','" + rng9.Value2 + "','" + rng10.Value2 + "','" + rng11.Value2 + "','" + rng12.Value2 + "','" + rng13.Value2 + "','" + rng14.Value2 + "','" + rng15.Value2 + "','" + rng16.Value2 + "','" + rng17.Value2 + "','" + rng18.Value2 + "')";
try
{
sc.Open();
//SqlCommand cmd0 = new SqlCommand("delete * from 月度成本表", sc);
//cmd0.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand(sql1, sc);
cmd.ExecuteNonQuery();
sc.Close();
}
catch (SqlException sqlexp)
{
sc.Close();
MessageBox.Show(sqlexp.Message);
MessageBox.Show(sql1);
}
}
}
}
}
以上代码红色部分望高手讲解下，另外我导入时会出错，因为格式转换不正确，这里rang.value2的数据格式为什么都是dynamic类型，怎样设置成数据库相应字段定义的类型，急求高手！！！！！！
[解决办法]
strCell 会EXCEL应该明白
Type.Missing 就是 null
这是COM遗留
