这两天做东西,用到了Excel。需要将Excel中的数据读取到内存中,经过处理后,保存到数据库中。在网上找了好多方法,但都有错误。现总结下:
1.引用Microsoft.Office.Interop.Excel
2.代码:
-
-
-
-
-
- private void btnImport_Click(object sender, EventArgs e)
- {
- string worksheetname = string.Empty;
- importtpye = cmbTicketType.Text;
- supplier = cmbSupplier.SelectedValue.ToString();
-
- #region 导入本地Excel文件
-
-
- OpenFileDialog file = new OpenFileDialog();
- file.Filter = "文档(*.xls)|*.xls";
- if (file.ShowDialog() == DialogResult.OK)
- btnImport.Tag = file.FileName;
-
- if (file.FileName.Length == 0)
- {
- MessageBox.Show("请选择要导入的文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return;
- }
-
- #endregion
-
- #region 获取Excel的工作表名称
-
- Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
- object objMissing = System.Reflection.Missing.Value;
- Microsoft.Office.Interop.Excel.Workbook myBook = (Microsoft.Office.Interop.Excel.Workbook)oExcel.Workbooks.Open(file.FileName, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
- Microsoft.Office.Interop.Excel.Sheets sheets = myBook.Worksheets;
- Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
- worksheetname = worksheet.Name;
- oExcel.Quit();
- #endregion
-
- dataTable.Rows.Clear();
-
- DataTable table = LoadExcelToDataTable(file.FileName, worksheetname);
-
- }
3.代码:
-
-
-
-
-
-
- public static DataTable LoadExcelToDataTable(string filename, string worksheetname)
- {
- DataTable table;
-
- String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
- OleDbConnection myConn = new OleDbConnection(sConnectionString);
- string strCom = " SELECT * FROM [" + worksheetname + "$]";
- myConn.Open();
- OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
- table = new DataTable();
- myCommand.Fill(table);
- myConn.Close();
- return table;
- }
4.注意:有的人的连接字符串写成这样:
-
- String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" +
-
- filename + ";" + "Extended Properties=Excel 8.0;";
这样也可以读取到数据,但是,如果Excel表里的某一列中既有文本类型的值,又有数字类型的值,即混合类型的列,那么就会只读取到一种类型的值,另一种类型的就会为NULL。
-
- String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
这样,后边加上"HDR=Yse;IMEX=1",并且这句必须用引号引住,这样就会把混合类型的数据同一当作文本读取,不会出现丢数据的现象。