首先，看一下我的窗体设计：
要插入的Excel表：
| 编码 | 名称 | 联系人 | 电话 | 省市 | 备注 |
| 100 | 100线 | 张三 | 12345678910 | 北京 | 测试 |
| 101 | 101线 | 张三 | 12345678910 | 上海 | 测试 |
| 102 | 102线 | 张三 | 12345678910 | 深圳 | 测试 |
| 103 | 103线 | 张三 | 12345678910 | 广州 | 测试 |
| 104 | 104线 | 张三 | 12345678910 | 福建 | 测试 |
| 105 | 105线 | 张三 | 12345678910 | 厦门 | 测试 |
| 106 | 106线 | 李四 | 12345678910 | 台湾 | 测试 |
| 107 | 107线 | 李四 | 12345678910 | 石家庄 | 测试 |
| 108 | 108线 | 李四 | 12345678910 | 合肥 | 测试 |
| 109 | 109线 | 李四 | 12345678910 | 东城 | 测试 |
| 110 | 110线 | 李四 | 12345678910 | 丰台 | 测试 |
| 111 | 111线 | 李四 | 12345678910 | 朝阳 | 测试 |
| 112 | 112线 | 王五 | 12345678910 | 海淀 | 测试 |
| 113 | 113线 | 王五 | 12345678910 | 昌平 | 测试 |
| 114 | 114线 | 王五 | 12345678910 | 大兴 | 测试 |
| 115 | 115线 | 王五 | 12345678910 | 西城 | 测试 |
| 116 | 116线 | 王五 | 12345678910 | 通州 | 测试 |
| 117 | 117线 | 赵六 | 12345678910 | 石景山 | 测试 |
| 118 | 118线 | 赵六 | 12345678910 | 门头沟 | 测试 |
| 119 | 119线 | 赵六 | 12345678910 | 房山 | 测试 |
/
private void btnFindFile_Click(object sender, EventArgs e)
{
#region 选择文件按钮事件
openFile.Multiselect = true;
openFile.FileName = "";
openFile.Filter = "(*.xlsx,*.xls)|*.xlss;*.xls";
openFile.ShowDialog();
openFile.Reset();
#endregion
}
private void openFile_FileOk(object sender, CancelEventArgs e)
{
#region 打开文件(确定事件)
try
{
if (dgvData.Rows.Count > 0)
{
dgvData.Rows.Clear();
}
strFileName = openFile.FileNames;
strFilePath = openFile.SafeFileNames;
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
strFilePaths.Clear();//清空上次要导入的文件
for (int j = 0; j < strFileName.Length; j++)
{
string FileName = strFileName[j];
int rows = this.dgvData.Rows.Count;
this.dgvData.Rows.Add(1);//添加一行，不然数据不显示
//获取工作表名
object Nothing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook wbs = null;
//获取EXCEL中的第一张工作簿名称
wbs = excel1.Workbooks.Open(FileName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing,Nothing, Nothing, Nothing, Nothing, Nothing);
Microsoft.Office.Interop.Excel.Worksheet SHT1 = (Microsoft.Office.Interop.Excel.Worksheet)wbs.Sheets[1];
string sheetname = SHT1.Name;
dgvData.Rows[j].Cells["XuHao"].Value=j+1;
dgvData.Rows[j].Cells["Lujing"].Value = FileName;
dgvData.Rows[j].Cells["Gongzuobiao"].Value = sheetname;
dgvData.Rows[j].Cells["Zhuangtai"].Value = "待导入";
}
Kill(excel1);
}
catch
{
return;
}
#endregion
}
private void btnStartIn_Click(object sender, EventArgs e)
{
#region 开始导入按钮
try
{
int row = 0;
int DataNum = dgvData.Rows.Count;//将要导入的表的数量
if (DataNum == 0)
{
PublicClass.ShowMessage("请先选择文件");
return;
}
DataSet[] dss = new DataSet[DataNum];
Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workBooks = m_objExcel.Workbooks;
Object missing = 1;
//显示进度条
grbIn.Visible = true;
proJinDu.Visible = true;
string textName = "";//获取选择文件的名称
string sheetName = "";//获得选中工作表名称
// DataSet das;
for (int i = 0; i < DataNum;i++ )
{
textName = dgvData.Rows[i].Cells[1].Value.ToString(); //获得选中的文件的地址
sheetName = dgvData.Rows[i].Cells[2].Value.ToString(); //获得选中工作表名称
workBooks.Open(textName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Sheets objSheets = m_objExcel.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets[1];
//读取excel数据
dss[i] = GetDataBySheetName07(sheetName, textName);
row += dss[i].Tables[0].Rows.Count;
}
#region 循环表
int numcout = 0;//记录上一张工作表导入成功的记录条数
for (int j = 0; j < DataNum; j++)
{
DataSet ds = new DataSet();
ds=dss[j];
int ii = 1;//导入工作表中的当前行
textName = dgvData.Rows[j].Cells[1].Value.ToString(); //获得选中的文件的地址
sheetName = dgvData.Rows[j].Cells[2].Value.ToString(); //获得选中工作表名称
//声明导入对象
workBooks.Open(textName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Sheets objSheets = m_objExcel.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets[1];
//读取excel数据
ds = GetDataBySheetName07(sheetName, textName);
//row = ds.Tables[0].Rows.Count;
proJinDu.Minimum = 0;
proJinDu.Value = 0;
proJinDu.Maximum = row;
if (row == 0)
{
PublicClass.ShowMessage("当前选择的工作表没有数据信息!");
return;
}
#region 循环每张表的记录
for (int m = 0; m < ds.Tables[0].Rows.Count; m++)
{
try
{
ii++;
int_in++;//正在导入第XX条记录
string CarriersCode = ds.Tables[0].Rows[m][0].ToString().Trim();//承运商编码
string CarriersName = ds.Tables[0].Rows[m][1].ToString().Trim();//名称
string CarriersCity = ds.Tables[0].Rows[m][4].ToString().Trim();//省、市
//检查编码、名称是否为空
if (CarriersCode.Equals(""))
{
objSheet.Cells[ii, 7] = "编码不能为空!";
int_wrong++;
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
continue;
}
if (CarriersName.Equals(""))
{
objSheet.Cells[ii, 7] = "名称不能为空!";
int_wrong++;
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
continue;
}
if (CarriersCity.Equals(""))
{
objSheet.Cells[ii, 7] = "省市不能为空!";
int_wrong++;
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
continue;
}
//检查编码、名称是否存在
string ssql = "select * from T_Base_Carriers where Carriers_Code='" + CarriersCode + "' or Carriers_Name='" + CarriersName + "'";
DataSet ddss = DataAccess.GetDataSet(ssql, false, null);
if (ddss.Tables[0].Rows.Count > 0)
{
if (CarriersCode == ddss.Tables[0].Rows[0]["Carriers_Code"].ToString())
{
objSheet.Cells[ii, 7] = "编码已存在!";
int_wrong++;
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
continue;
}
if (CarriersName == ddss.Tables[0].Rows[0]["Carriers_Name"].ToString())
{
objSheet.Cells[ii, 7] = "名称已存在!";
int_wrong++;
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
continue;
}
}
object[] obj = new object[8];
obj[0] = doubleid;
obj[1] = CarriersCode;
obj[2] = CarriersName;
obj[3] = CarriersCity;
obj[4] = ds.Tables[0].Rows[m][2].ToString();
obj[5] = ds.Tables[0].Rows[m][3].ToString();
obj[6] = ds.Tables[0].Rows[m][5].ToString();
obj[7] = 1;
//obj[7] = Frm_Login.userID;
int hh = Convert.ToInt32(DataAccess.ExecuteNonQuery("P_Base_Carriers_Save", true, obj));
if (hh > 0)
{
int_right++;
}
else
{
int_wrong++;
}
}
catch
{
m_objExcel.Visible = true;
throw;
}
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
this.proJinDu.Value = int_in;
}
#endregion
lblRecord.Text = "共" + row + "条记录,导入成功" + (int_in - int_wrong) + "条记录,错误" + int_wrong + "条！";
this.proJinDu.Maximum = row - int_wrong;
this.proJinDu.Value = row - int_wrong;
numcout = int_right-numcout;
if (numcout < ds.Tables[0].Rows.Count)
{
dgvData.Rows[j].Cells[3].Value = "有错误记录";
}
else
{
dgvData.Rows[j].Cells[3].Value = "导入成功！";
}
}
#endregion
if (int_right < row)
{
//设置进度条不显示
grbIn.Visible = false;
proJinDu.Visible = false;
PublicClass.ShowMessage("共" + row + "条记录,导入成功" + int_right + "条记录,错误" + int_wrong + "条！");
m_objExcel.Visible = true;
System.Windows.Forms.Application.DoEvents();
return;
}
System.Windows.Forms.Application.DoEvents();
m_objExcel.Quit();
Kill(m_objExcel);
m_objExcel = null;
for (int s = 0; s < DataNum; s++)
{
dss[s].Dispose();
}
GC.Collect();
//设置进度条不显示
grbIn.Visible = false;
proJinDu.Visible = false;
PublicClass.ShowMessage("导入成功! 共" + row + "条记录,导入成功" + (int_in - int_wrong) + "条记录,错误" + int_wrong + "条！");
nowpage = 1;
DataBind();
PageState();
btnCancel.Focus();
}
catch (Exception ex)
{
PublicClass.ShowMessage(ex.Message);
}
#endregion
}
效果图：
备注：
1>代码多处调用了自定类，出于机密原因，不能贴出来，敬请原谅。
2>给需要帮助的人们一个思路，里面肯定有许多要完善的地方，大家共同学习。
版权声明：本文为博主原创文章，未经博主允许不得转载。
1293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
