这两天做东西,用到了Excel。需要将Excel中的数据读取到内存中,经过处理后,保存到数据库中。在网上找了好多方法,但都有错误。现总结下:
1.引用Microsoft.Office.Interop.Excel
2.代码:
- /// <summary>
- /// 导入Excel文件,并显示在列表
- /// </summary>
- /// <param name="sender"></param>
- /// <param name="e"></param>
- 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的工作表名称
- //创建Excel实例,获取worksheet Name
- 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;//获取worksheet Name
- oExcel.Quit();
- #endregion
- dataTable.Rows.Clear();
- //将Excel表中的数据导入到Datatable中
- DataTable table = LoadExcelToDataTable(file.FileName, worksheetname);
- }
3.代码:
- /// <summary>
- /// 加载Excel表到DataTable,跟原始Excel表形式一样,需要筛选自己有用的数据
- /// </summary>
- /// <param name="filename">需要读取的Excel文件路径</param>
- /// <param name="sheetname">工作表名称</param>
- /// <returns>DataTable</returns>
- 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",并且这句必须用引号引住,这样就会把混合类型的数据同一当作文本读取,不会出现丢数据的现象。
本文出自 “小狼” 博客,请务必保留此出处http://canyan3073.blog.51cto.com/1576306/383374