问题描述
上星期做的C#导入excel到sqlserver解决了重复导入信息的问题，加多一个ID列，但是发现导入了第一个excel文件的数据之后第二个导入不了了。求大神指点或解决。我的winform里只有三个控件：两个botton和一个dataGridView1usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Windows.Forms;usingSystem.Data.OleDb;usingSystem.Data.SqlClient;usingSystem.Text.RegularExpressions;usingKQ;usingSZCID.Environment;usingSZCID.Environment.Tools;namespaceWindowsFormsApplication59{publicpartialclassForm1:Form{stringfile=null;publicForm1(){InitializeComponent();}privatevoidbutton1_Click(objectsender,EventArgse){OpenFileDialogfileDialog=newOpenFileDialog();fileDialog.Multiselect=true;fileDialog.Title="请选择文件";fileDialog.Filter="所有文件(*.*)|*.*";stringfileName=fileDialog.SafeFileName;//获取文件名if(fileDialog.ShowDialog()==DialogResult.OK){file=fileDialog.FileName;try{using(ExcelexcelHelper=newExcel(file)){DataTabledt=excelHelper.ExcelToDataTable(fileName,true);dataGridView1.DataSource=dt;}}catch(Exceptionex){Console.WriteLine("Exception:"+ex.Message);}}}privatevoidForm1_Load(objectsender,EventArgse){}privatevoidbutton2_Click(objectsender,EventArgse){DataTabledt=newDataTable();stringconnstring="server=.;database=shoucao;uid=zxf;pwd=zxf123";SqlConnectionconn=newSqlConnection(connstring);conn.Open();//DataRowdr=null;if(this.dataGridView1.DataSource!=null){intallrow=this.dataGridView1.Rows.Count;//总行数stringzdbh=null;stringjyrqsj=null;stringzzh=null;stringfkh=null;stringjyje=null;stringshfy=null;stringjsje=null;stringxtckh=null;stringxtgzh=null;stringjyqd=null;stringjyle=null;intid=0;//intA=0;try{for(inti=0;i<dataGridView1.Rows.Count;i++){zdbh=this.dataGridView1.Rows[i].Cells[0].Value.ToString();jyrqsj=this.dataGridView1.Rows[i].Cells[1].Value.ToString();zzh=this.dataGridView1.Rows[i].Cells[2].Value.ToString();fkh=this.dataGridView1.Rows[i].Cells[3].Value.ToString();jyje=this.dataGridView1.Rows[i].Cells[4].Value.ToString();shfy=this.dataGridView1.Rows[i].Cells[5].Value.ToString();jsje=this.dataGridView1.Rows[i].Cells[6].Value.ToString();xtckh=this.dataGridView1.Rows[i].Cells[7].Value.ToString();xtgzh=this.dataGridView1.Rows[i].Cells[8].Value.ToString();jyqd=this.dataGridView1.Rows[i].Cells[9].Value.ToString();jyle=this.dataGridView1.Rows[i].Cells[10].Value.ToString();++id;stringsqlInsert="insertintodataGridViewvalues('"+id+"','"+zdbh+"','"+jyrqsj+"','"+zzh+"','"+fkh+"','"+jyje+"','"+shfy+"','"+jsje+"','"+xtckh+"','"+xtgzh+"','"+jyqd+"','"+jyle+"')";SqlCommandcmd=newSqlCommand(sqlInsert,conn);cmd.ExecuteNonQuery();}}catch(Exception){//MessageBox.Show("Exception:"+ex.Message);}}if(dataGridView1.Rows.Count>0)//把数据库表中的数据显示到表中，可判断有没有数据{MessageBox.Show("导入成功！");}else{MessageBox.Show("没有数据！");}}privatevoiddataGridView1_CellContentClick(objectsender,DataGridViewCellEventArgse){}}}Excel.csusingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.IO;usingSystem.Data;usingSystem.Text.RegularExpressions;usingNPOI.SS.UserModel;usingNPOI.XSSF.UserModel;usingNPOI.HSSF.UserModel;namespaceKQ{classExcel:IDisposable{privatestringfileName=null;privateIWorkbookworkbook=null;privateFileStreamfs=null;privatebooldisposed;publicExcel(stringfileName){this.fileName=fileName;disposed=false;}///<summary>///将excel中的数据导入到DataTable中///</summary>///<paramname="sheetName">excel工作薄sheet的名称</param>///<paramname="isFirstRowColumn">第一行是否是DataTable的列名</param>///<returns>返回的DataTable</returns>publicDataTableExcelToDataTable(stringsheetName,boolisFirstRowColumn){ISheetsheet=null;DataTabledata=newDataTable();intstartRow=0;try{fs=newFileStream(fileName,FileMode.Open,FileAccess.Read);if(fileName.IndexOf(".xlsx")>0)//2007版本workbook=newXSSFWorkbook(fs);elseif(fileName.IndexOf(".xls")>0)//2003版本workbook=newHSSFWorkbook(fs);if(sheetName!=null){sheet=workbook.GetSheet(sheetName);if(sheet==null){sheet=workbook.GetSheetAt(0);//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet}}else{sheet=workbook.GetSheetAt(0);}//获取并显示设置列if(sheet!=null){//获取第一行，并设置为列IRowfirstRow=sheet.GetRow(0);intcellCount=firstRow.LastCellNum;//一行最后一个cell的编号即总的列数if(isFirstRowColumn){for(inti=firstRow.FirstCellNum;i<cellCount;++i){ICellcell=firstRow.GetCell(i);if(cell!=null){stringcellValue=cell.StringCellValue;if(cellValue!=null){string[]s=Regex.Replace(cellValue.Trim(),"\s+","").Split('');//将多余的空格转成一个空格，然后变成数组for(inta=0;a<s.Length;a++){if(s[a].ToString()!=null&&s[a].ToString().Length!=0){DataColumncolumn=newDataColumn(s[a]);data.Columns.Add(column);//赋值到当前行的每一列，直接给dataTable}}}}}startRow=sheet.FirstRowNum+1;}else{startRow=sheet.FirstRowNum;}//最后一列的标号//获取每一行的数据introwCount=sheet.LastRowNum;for(inti=startRow;i<=rowCount;++i){IRowrow=sheet.GetRow(i);if(row==null)continue;//没有数据的行默认是null DataRowdataRow=data.NewRow();for(intj=row.FirstCellNum;j<cellCount;++j){if(row.GetCell(j)!=null)//同理，没有数据的单元格都默认是null{//循环列读取string[]s=Regex.Replace(row.GetCell(j).StringCellValue,"\s+","").Split('');for(inta=0;a<s.Length;a++){if(s[a].ToString()!=null&&s[a].ToString().Length!=0){DataColumncolumn=newDataColumn(s[a]);//将当前行的数据赋给dataRow，然后将dataRow给dataTabledataRow[a]=column.ToString();}}}}data.Rows.Add(dataRow);}}returndata;}catch(Exceptionex){Console.WriteLine("Exception:"+ex.Message);returnnull;}}publicvoidDispose(){thrownewException("Themethodoroperationisnotimplemented.");}}}
解决方案
解决方案二：
把ID列删除了也能导入，但是就是可能重复导入，求大神教