using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows.Forms; using System.Diagnostics; using System.IO; //using Microsoft.Office.Interop.Excel; using System.Data.OleDb; using System.ComponentModel; using System.Data; using System.Drawing; namespace ClothingSales_0._5 { public class Import { //private string fileName; /// <summary> /// 将DataGridView中数据导入到Excel /// </summary> /// <param name="dgv">DataGridView的名称</param> public bool getExcel(DataGridView dgv,Label lbl) { bool fflag = true; OpenFileDialog open = new OpenFileDialog(); open.Title = "请选择要导入的Excel文件"; open.Filter = "Excel文件(*.xls)|*.xls"; if (open.ShowDialog() == DialogResult.OK) { string fileName = open.FileName; //根据路径打开一个Excel文件并将数据填充到DataSet中 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + fileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入 OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = "select * from [sheet1$]"; OleDbDataAdapter comm = new OleDbDataAdapter(strExcel, strConn); DataSet ds = new DataSet(); try { comm.Fill(ds, "table1"); } catch { MessageBox.Show("错误信息:009", "错误"); } comm.Fill(ds, "table1"); //根据DataGridView的列构造一个新的DataTable DataTable dt = new DataTable(); DataTable dt2 = new DataTable(); foreach (DataGridViewColumn dgvc in dgv.Columns) { if (dgvc.Visible) { DataColumn dc = new DataColumn(); dc.ColumnName = dgvc.DataPropertyName; dt.Columns.Add(dc); DataColumn dc2 = new DataColumn(); dc2.ColumnName = dgvc.DataPropertyName; dt2.Columns.Add(dc2); if (dgvc.CellType == typeof(DataGridViewCheckBoxCell)) { dc2.DataType = Type.GetType("System.Boolean"); } } } //根据Excel的行逐一对上面构造的DataTable的列进行赋值 foreach (DataRow excelRow in ds.Tables[0].Rows) { int i = 0; DataRow dr = dt.NewRow(); foreach (DataColumn dc in dt.Columns) { dr[dc] = excelRow[i]; i++; } dt.Rows.Add(dr); } //判断Excel的格式是否正确 int n = 0; for (int j = 0; j < dt.Columns.Count; j++) { bool flag = false; for (int k = n; k < dgv.ColumnCount; k++) { if (dgv.Columns[k].Visible) //隐藏的列 { if (dgv.Columns[k].HeaderText.Trim().ToString() == dt.Rows[0][j].ToString()) { if (dgv.Columns[k].CellType == typeof(DataGridViewCheckBoxCell)) { //list.Add(j); //num++; } flag = true; n = k + 1; break; } } } if (flag == false) { MessageBox.Show("导入的Excel的格式错误", "提示"); fflag = false; return fflag; } } //删除多余的行 int rowCount = (dt.Rows.Count) / 2; for (int i = 0; i <= rowCount; i++) { dt.Rows.RemoveAt(0); } //处理Boolean类型的数据 for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt2.NewRow(); for (int j = 0; j < dt.Columns.Count; j++) { try { dr[j] = dt.Rows[i][j]; } catch { dr[j] = false; } } dt2.Rows.Add(dr); } //导入到dataGridView dgv.DataSource = dt2; lbl.Text = dgv.RowCount.ToString(); } else { fflag = false; } return fflag; } } }