winform excel导入mysql_Winform导入Excel数据到数据库

public partial classImportExcel : Form

{

AceessHelpers accessHelper= newAceessHelpers();publicImportExcel()

{

InitializeComponent();

}private void btn_importExcelData(objectsender, EventArgs e)

{

openFileDialog1.Title= "打开文件";

openFileDialog1.ShowHelp= true;

openFileDialog1.RestoreDirectory= true;//openFileDialog1.Filter = "Excel文件(*.xlsx)|(*.xls)";

if (openFileDialog1.ShowDialog() ==DialogResult.OK)

{this.txt_path.Text =openFileDialog1.FileName;

}

}private void btn_upload_Click(objectsender, EventArgs e)

{string path =openFileDialog1.FileName;

System.Data.DataTable dt=GetDataTable(path);int result = 0;for (int i = 1; i < dt.Rows.Count; i++)

{string sql = "insert into Products (ProductName,Area,ProductCategory,Customer,Style,StructureStyle,Width,Square,Other) values ('" +Convert.ToString(dt.Rows[i]["F1"]) + "','" + Convert.ToString(dt.Rows[i]["F2"]) + "','" + Convert.ToString(dt.Rows[i]["F3"]) + "','" +Convert.ToString(dt.Rows[i]["F4"]) + "','" + Convert.ToString(dt.Rows[i]["F5"]) + "','" + Convert.ToString(dt.Rows[i]["F6"]) + "','" +Convert.ToString(dt.Rows[i]["F7"]) + "','" + Convert.ToString(dt.Rows[i]["F8"]) + "','" + Convert.ToString(dt.Rows[i]["F9"]) + "')";int j =accessHelper.ReturnSql(sql);

result= j++;

}if (result > 0)

{this.Close();

}

}///

///读取excel指定页中的内容///

/// excel路径

///

protected System.Data.DataTable GetDataTable(stringstrExcelFileName)

{//DataSet myDs = new DataSet();

System.Data.DataTable dt = newSystem.Data.DataTable();

DataRow myRow;object oMissing =System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Application application= newMicrosoft.Office.Interop.Excel.Application(); ;

Microsoft.Office.Interop.Excel.Workbooks workbooks;//Microsoft.Office.Interop.Excel.Worksheet worksheet;

Microsoft.Office.Interop.Excel.Workbook workbook;

workbooks=application.Workbooks;

workbook=returnworkbook(strExcelFileName, workbooks);//worksheet = (Worksheet)workbook.Sheets[1];

for (int sheetint = 1; sheetint < 2; sheetint++)//DateTime.Now.Day Convert.ToDateTime(TextBox4.Text.Trim()).Day;//myBook.Worksheets.Count;//能得到sheet的数量

{

Worksheet mySheet=(Worksheet)workbook.Worksheets[sheetint];int rowsint = mySheet.UsedRange.Cells.Rows.Count; //得到行数

int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数

if (sheetint == 1)

{for (int i = 1; i <= columnsint; i++)

{

dt.Columns.Add("F" + i.ToString(), System.Type.GetType("System.String"));

}

}for (int i = 2; i <= rowsint; i++) //第一行为标题，不读取

{

myRow=dt.NewRow();for (int j = 1; j <= columnsint; j++)

{

Range r=(Range)mySheet.Cells[i, j];string strValue =r.Text.ToString();string columnname = "F" +j.ToString();

myRow[columnname]= (strValue.Length == 0 || strValue.Contains("#")) ? " ": strValue;

}try{

dt.Rows.Add(myRow);

}catch{ }

}

}

workbook.Close(oMissing, oMissing, oMissing);

workbooks.Close();

application.Quit();returndt;

}private Workbook returnworkbook(string filename, Workbooks works) //这里是打一开一个工作表

{

Microsoft.Office.Interop.Excel.Workbook wk=works.Open(

filename, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing);returnwk;

}

}