这两天在看一个问题，就是将Excel里的数据导入数据库里面，并且不能有重复。网上有很多方法，我这只是做个总结，算不上全是原创。
其实思路是很简单的，首先从Excel 读到内存里，再从内存里面导入数据库里面，中间要借助Dataset ，再导入时不用管数据重复的问题，导后我们再数据库里面把重复的去掉。
界面像这样
(1)将数据库导入导dataset里面
代码如下
public Form1()
{
InitializeComponent();
}
DataSet ds; //用它来存表
private void button1_Click(object sender, EventArgs e)
{
System.Windows.Forms.OpenFileDialog fd = new System.Windows.Forms.OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)
{
string filename = fd.FileName;
build(filename);
}
}
private void build(string FileName)
{
string errorstr;
ds = ExcelOperation.ReadExcel(FileName,1,out errorstr,datatype.String,datatype.String, datatype.String, datatype.String, datatype.String, datatype.String, datatype.String); //好丑 的读入七个string
DataTableCollection collection = ds.Tables;
DataTable dt = collection[6];
this.dataGridView1.DataSource = dt;
}dataset 是全局的。
这部分中 dialog 只是为得到文件路径 fd.FileName.
ExceplOperation是封装成一个类
using System;
using System.Text;
using System.Data;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace EXCEL_操作新
{
class ExcelOperation
{
#region OPEN WORKBOOK VARIABLES
private static object vk_missing = System.Reflection.Missing.Value;
private static object vk_visible = true;
private static object vk_false = false;
private static object vk_true = true;
private static object vk_update_links = 0;
private static object vk_read_only = vk_true;
private static object vk_format = 1;
private static object vk_password = vk_missing;
private static object vk_write_res_password = vk_missing;
private static object vk_ignore_read_only_recommend = vk_true;
private static object vk_origin = vk_missing;
private static object vk_delimiter = vk_missing;
private static object vk_editable = vk_false;
private static object vk_notify = vk_false;
private static object vk_converter = vk_missing;
private static object vk_add_to_mru = vk_false;
private static object vk_local = vk_false;
private static object vk_corrupt_load = vk_false;
#endregion
/// <summary>
/// 获取单元格值(string类型)
/// </summary>
/// <param name="excelSheet">excel的worksheet</param>
/// <param name="row">行号</param>
/// <param name="col">列号</param>
/// <returns>单元格内值</returns>
public static string getCellValue(Worksheet excelSheet, int row, int col)
{
string value = string.Empty;
if (col != 0)//不是字段
{
value = excelSheet.get_Range(excelSheet.Cells[row, col], excelSheet.Cells[row, col]).Text.ToString().Trim();
}
if (value.ToUpper().Equals("NULL") || value.Equals("无") || value.Equals("暂无"))
{
value = string.Empty;
}
return value;
}
/// <summary>
/// 关闭excel进程
/// </summary>
/// <param name="excelAp">需要关闭的excel进程</param>
public static void closeExcel(Microsoft.Office.Interop.Excel.Application excelAp)
{
if (excelAp != null)
{
excelAp.DisplayAlerts = false; //必须先关掉
excelAp.Workbooks.Close();
excelAp.Quit();
}
}
/// <summary>
/// 开始一个excel进程
/// </summary>
/// <returns>启动的excel进程</returns>
public static Microsoft.Office.Interop.Excel.Application getExcelApplication()
{
Microsoft.Office.Interop.Excel.Application excelAp = new Microsoft.Office.Interop.Excel.Application();
excelAp.Visible = false;
excelAp.DisplayAlerts = true;
excelAp.UserControl = true;
return excelAp;
}
/// <summary>
/// 获取一个excel的第一个worksheet页
/// </summary>
/// <param name="excelAp">excel进程</param>
/// <param name="excelLocation">打开的excel的文件位置</param>
/// <param name="excelBook">返回的打开的excel文件</param>
/// <returns>excel文件的第一页</returns>
public static Worksheet getWorksheet(Microsoft.Office.Interop.Excel.Application excelAp, string excelLocation, out Workbook excelBook)
{
excelBook = null;
if (!File.Exists(excelLocation))
{
return null;
}
try
{
Worksheet excelSheet = null;
excelBook = excelAp.Workbooks.Open(excelLocation, vk_update_links, vk_missing, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
foreach (Worksheet displayWorksheet in excelBook.Sheets)
{
excelSheet = displayWorksheet; //只获取一次
break;
}
if (excelSheet == null)
{
excelBook.Sheets.Add(Type.Missing, excelBook.Sheets[excelBook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet = excelBook.Sheets[excelBook.Sheets.Count] as Worksheet;
}
return excelSheet;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 获取一个excel的指定的worksheet页，如果不存在此页名，则用此名创建一个新页
/// </summary>
/// <param name="excelAp">excel进程</param>
/// <param name="excelLocation">打开的excel的文件位置</param>
/// <param name="sheetName">页名</param>
/// <param name="excelBook">返回的打开的excel文件</param>
/// <returns>excel文件的指定页</returns>
public static Worksheet getWorksheet(Microsoft.Office.Interop.Excel.Application excelAp, string excelLocation, string sheetName, out Workbook excelBook)
{
excelBook = null;
if (!File.Exists(excelLocation))
{
return null;
}
try
{
Worksheet excelSheet = null;
excelBook = excelAp.Workbooks.Open(excelLocation, vk_update_links, vk_missing, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
foreach (Worksheet displayWorksheet in excelBook.Sheets)
{
if (displayWorksheet.Name.Equals(sheetName))
{
excelSheet = displayWorksheet;
break;
}
}
if (excelSheet == null)
{
excelBook.Sheets.Add(Type.Missing, excelBook.Sheets[excelBook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet = excelBook.Sheets[excelBook.Sheets.Count] as Worksheet;
excelSheet.Name = sheetName;
}
return excelSheet;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 将dataset中的数据导入到excel中
/// </summary>
/// <param name="ds">存放原始数据的dataset，可从数据库获取</param>
/// <param name="excelFileFullPath">excel路径</param>
/// <param name="sheetName">excel的sheet页名</param>
/// <param name="errormsg">运行过程中产生的错误信息，如果成功则为空</param>
/// <returns>导入成功则为true,否则为false</returns>
public static bool exportExcel(DataSet ds, string excelFileFullPath, string sheetName, out string errormsg)
{
errormsg = string.Empty;
if (ds.Tables.Count == 0 || string.IsNullOrEmpty(excelFileFullPath))
{
errormsg = "没有数据";
return false;
}
bool bexistfile = false;
if (File.Exists(excelFileFullPath))
{
bexistfile = true;
}
Microsoft.Office.Interop.Excel.Application excelAp = getExcelApplication();
try
{
int rowindex = 1, colindex = 0;
Workbook excelBook;
Worksheet excelSheet;
if (bexistfile)
{
excelSheet = getWorksheet(excelAp, excelFileFullPath, sheetName, out excelBook);
}
else
{
excelBook = excelAp.Workbooks.Add(true);
excelBook.Sheets.Add(Type.Missing, excelBook.Sheets[excelBook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet = excelBook.Sheets[1] as Worksheet;
excelSheet.Name = sheetName;
}
System.Data.DataTable ddt = ds.Tables[0];
foreach (DataColumn col in ddt.Columns)
{
colindex++;
excelSheet.Cells[1, colindex] = col.ColumnName;
}
foreach (DataRow row in ddt.Rows)
{
rowindex++;
colindex = 0;
foreach (DataColumn col in ddt.Columns)
{
colindex++;
excelSheet.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
}
}
XlFileFormat xlff = XlFileFormat.xlWorkbookNormal;
if (excelFileFullPath.EndsWith("xls"))
{
xlff = XlFileFormat.xlExcel8;
}
else if (excelFileFullPath.EndsWith("xlsx"))
{
xlff = XlFileFormat.xlExcel12;
}
if (bexistfile)
{
excelBook.Save();
}
else
{
excelBook.SaveAs(excelFileFullPath, xlff, null, null, false, false, XlSaveAsAccessMode.xlNoChange,
null, null, null, null, null);
}
closeExcel(excelAp);
return true;
}
catch (Exception ex)
{
errormsg = ex.Message;
closeExcel(excelAp);
return false;
}
}
public static DataSet ReadExcel(string excelFileFullPath, int startRow, out string errormsg, params datatype[] ColumnDataType)
{
errormsg = string.Empty;
int ertime = 0;
int intime = 0;
DataSet ds = new DataSet("ds");
DataRow dr;
StringBuilder sb = new StringBuilder();
Microsoft.Office.Interop.Excel.Application excelAp = getExcelApplication(); //打开
Workbook excelBook = excelAp.Workbooks.Open(excelFileFullPath, vk_update_links, vk_missing, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
//得到第一个sheet
//Worksheet excelsheet = excelBook.Sheets[0]; //这还是只读入一张表
int n = excelBook.Sheets.Count;
int tablecount = 1;
foreach (Worksheet excelsheet in excelBook.Sheets)
{
System.Data.DataTable dt = new System.Data.DataTable("dt"+tablecount);
int j;
for (j = 0; j < ColumnDataType.Length; j++) //必须要先创建表才可以往里面插值
dt.Columns.Add("c" + j, Type.GetType("System.String")); //添加 字段哪一行 往数据库里面写时可以删掉
Range range = excelsheet.Rows;
string name = excelsheet.Name;
//string temp = ((Range)excelsheet.Cells[3,7]).Text;
for (int i = 3/*数据是从第三行开始的*/; SafeConverter.ConvertToString(((Range)range.Cells[i, 7]).Text) != ""; i++) //excelsheet.Rows.Count 不能直接这样写会把空读进来, 只想到了这个方法
{
string temp = SafeConverter.ConvertToString(((Range)range.Cells[i, 7]).Text);
try
{
dr = dt.NewRow();
for (j = 0; j < ColumnDataType.Length; j++) //"c" + ColumnDataType.Length 说明给了多少个参数，就读几列数据 从零开始的
dr[j] = GetCellData(ColumnDataType[j], range,i, j + 1).ToString(); //这个读入细节可以改改 全部变成 string 能不能直接保存原数据
dt.Rows.Add(dr);
intime++;
}
catch (Exception er)
{
ertime++;
sb.Append(string.Format("第{0}行出错：{1}\r\n", i + 1, er.Message));
continue;
}
}
ds.Tables.Add(dt);
tablecount++;
}
int count = ds.Tables.Count;
if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
return ds;
}
#region 得到不同数据类型单元格的数据
/// <summary>
/// 得到不同数据类型单元格的数据
/// </summary>
/// <param name="datatype">数据类型</param>
/// <param name="row">数据中的一行</param>
/// <param name="column">哪列</param>
/// <returns></returns>
public static object GetCellData(datatype datatype, Range range, int rownum , int column )
{
switch (datatype)
{
case datatype.String:
try { return SafeConverter.ConvertToString(((Range)range.Cells[rownum, column]).Text); }
catch { return SafeConverter.ConvertToInt(((Range)range.Cells[rownum, column]).Text); }
//try { return ((Range)range.Cells[rownum,column]).Text.tosting; }
//catch { return row.Rows[column].NumericCellValue; }
case datatype.Bool:
try { return SafeConverter.ConvertToBool(((Range)range.Cells[rownum, column]).Text); }
catch { return SafeConverter.ConvertToString(((Range)range.Cells[rownum, column]).Text); }
//try { return row.Rows[column].BooleanCellValue; }
//catch { return row.Rows[column].StringCellValue; }
case datatype.Datetime:
try { return SafeConverter.ConvertToDateTime(((Range)range.Cells[rownum, column]).Text); }
catch { return SafeConverter.ConvertToString(((Range)range.Cells[rownum, column]).Text); }
//try { return row.Rows[column].DateCellValue; }
//catch { return row.Rows[column].StringCellValue; }
case datatype.Double:
try { return SafeConverter.ConvertToDouble(((Range)range.Cells[rownum, column]).Text); }
catch { return SafeConverter.ConvertToString(((Range)range.Cells[rownum, column]).Text); }
//try { return row.Rows[column].NumericCellValue; }
//catch { return row.Rows[column].StringCellValue; }
// return (Richtext)
//try { return row.Rows[column].RichStringCellValue; }
//catch { return row.Rows[column].StringCellValue; }
default: return "";
}
}
#endregion
}
#region 枚举（单元格数据类型）
/// <summary>
/// 枚举（单元格数据类型）
/// </summary>
public enum datatype
{
/// <summary>
/// String=1
/// </summary>
String = 1,
/// <summary>
/// Bool=2
/// </summary>
Bool = 2,
/// <summary>
/// Datetime=3
/// </summary>
Datetime = 3,
/// <summary>
/// Double=4
/// </summary>
Double = 4,
/// <summary>
/// Richtext=5
/// </summary>
Richtext = 5
}
#endregion
}
这个代码的前半段是从其他人哪里拷贝的，ReadExcel方法是自己实现的params datatype[] ColumnDataType 是参数数组，再调用时可以写入任意数量的参数。但是它只能写在最后面。使用时就是一个数组。
Microsoft.Office.Interop.Excel.Application excelAp = getExcelApplication(); 得到操作excel的类 （首先你要安装excel ，然后再引用里面加入引用）
Workbook excelBook = excelAp.Workbooks.Open(excelFileFullPath, vk_update_links, vk_missing, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
嗯，有很多设置。
哎，就这样我们把excel打开了。
下一步我们就把一张工作薄的所有表读进dataset 里面
excelBook.Sheets 就是工作薄的所有表的集合 ，你可以用foreach 来遍历它门
但是你不能把直接表写入dataset 你还要用datatable 来一行行的读，再用add 方法把表加入到dataset 里面。
foreach里面每张表 datatable 都要 创建和设置 还不能重名 不然 再add进dataset 时是会出错的（datatable 以存在在dataset里面）
System.Data.DataTable dt = new System.Data.DataTable("dt"+tablecount);
int j;
for (j = 0; j < ColumnDataType.Length; j++) //必须要先创建表才可以往里面插值
dt.Columns.Add("c" + j, Type.GetType("System.String"));
Range range = excelsheet.Rows;
string name = excelsheet.Name;string name = excelsheet.Name; 你可以在调试时看看读的时那张表 dt.Columns.Add("c" + j, Type.GetType("System.String")); 是每行的名字，它有类型 但是 它会不会影响它下面的数据类型，这个我不清楚。 我觉得是有关系的，下面就可以直接将不同数据读进 dr[i]
range 是个区间相当。通过cells[,] 来索引取值。
for (int i = 3/*数据是从第三行开始的*/; SafeConverter.ConvertToString(((Range)range.Cells[i, 7]).Text) != ""; i++) //excelsheet.Rows.Count 不能直接这样写会把空读进来, 只想到了这个方法
{
try
{
dr = dt.NewRow();
for (j = 0; j < ColumnDataType.Length; j++) //"c" + ColumnDataType.Length 说明给了多少个参数，就读几列数据 从零开始的
dr[j] = GetCellData(ColumnDataType[j], range,i, j + 1).ToString(); //这个读入细节可以改改 全部变成 string 能不能直接保存原数据
dt.Rows.Add(dr);
intime++;
}
catch (Exception er)
{
ertime++;
sb.Append(string.Format("第{0}行出错：{1}\r\n", i + 1, er.Message));
continue;
}
}
ds.Tables.Add(dt);
tablecount++;
}
从第几行开始，根据自己的表来开始。对了 cells 的索引是从1开始的。但是row 和 col 是从0开始的。GetCellData() 就是将数据转换了返回对应了类型，这里我只是string类型。应该可以（我还没有试过233）不用tostring
dt.Rows.Add(dr); 每行添加 dr一定要先定义
ds.Tables.Add(dt);每张表添加
这样就把表全读进去了。
int count = ds.Tables.Count;
if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
return ds;
最后返回ds。
2.从dataset读入数据库
private void button2_Click(object sender, EventArgs e)
{
string connString = "server = .; uid = sa; pwd = sa; database = Y_DSSDB"; //名字自己改
// string comd = ""; //sql 命令
SqlConnection conn; //连接
conn = new SqlConnection(connString);
conn.Open();
DataTableCollection tablecollection = ds.Tables;
foreach (DataTable table in tablecollection)
{
if (table.Rows.Count > 0)
{
DataRow dr = null; //按行读取
for (int i = 1; i < table.Rows.Count; i++)
{
dr = table.Rows[i];
SqlOperation.insertToSql(dr, conn);
}
}
}
conn.Close();
MessageBox.Show("导入成功！");
}
我的数据库是 sql sever 2008 r2
当然用sql 语句啦
string connString = "server = .; uid = sa; pwd = sa; database = Y_DSSDB"; //名字自己改
sql 的操作有几个类 在这里
sqlconnect 是一个连接类（从名字都可以看出来，废话），不要忘了open它哦：）
再把ds 里的表读进去吧。DataTableCollection tablecollection = ds.Tables; DataTableCollection 是一个集合类
SqlOperation类里面是·=这样的
class SqlOperation
{
static int count = 1;
private SqlOperation() { } //私有是不想让它实例化
public static void insertToSql(DataRow row, SqlConnection conn)
{
if (row[6].ToString() == "") //这个应该不存在错误了
{
return;
}
string phylum = row[0].ToString();
string _class = row[1].ToString();
string order = row[2].ToString();
string family = row[3].ToString();
string genus = row[4].ToString();
string chinaes = row[5].ToString();
string latin = row[6].ToString().Trim();
//object phylum = row[0];
//object _class = row[1];
//object order = row[2];
//object family = row[3];
//object genus = row[4];
//object chinaes = row[5];
//object latin = row[6];
string sql = "insert into yss_microbe values('" + phylum + "','" + _class + "','" + order + "','" + family + "','" + genus + "','" + chinaes + "','" + latin + "')"; // + "','" + latin
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
count++;
}
}
row[6].ToString() == "" 只是我对应的表情况。
string phylum = row[0].ToString();
string _class = row[1].ToString();
string order = row[2].ToString();
string family = row[3].ToString();
string genus = row[4].ToString();
string chinaes = row[5].ToString();
string latin = row[6].ToString().Trim();
string _class = row[1].ToString();
string order = row[2].ToString();
string family = row[3].ToString();
string genus = row[4].ToString();
string chinaes = row[5].ToString();
string latin = row[6].ToString().Trim();
其实我觉得没有必要 再我读入时时可以按数据类型读入的 row里面应该也是对应的类型，再这里几个字段都是string 所以就这样。
string sql = "insert into yss_microbe values('" + phylum + "','" + _class + "','" + order + "','" + family + "','" + genus + "','" + chinaes + "','" + latin + "')"; 是用字符串拼接的sql语句
然后就通过 sqlcommand 来连接sql和conn对象 不要忘记调用ExecuteNonQuery()来执行 ，返回的是受影响的行数。
这样就读到数据库里面了
3.到数据库里面去删除重复数据
点击打开链接这个大哥讲的很清楚 ；
（1）查看同名的数据 分组查看
select * from [Y_DSSDB].[dbo].[yss_microbe] where microbe_latin in (select microbe_latin from [Y_DSSDB].[dbo].[yss_microbe] group by microbe_latin having count(*) > 1)
[Y_DSSDB].[dbo].[yss_microbe] 是我的表名，microbe_latin 是表中会重复的数据。
（图片找不到了） ：（
它会将相同的分再以前显示
（2） 查看相同的数量
select count(username) as '重复次数',username from xi group by username having count(*)>1 order by username desc
count是sql 的统计函数 desc 意思是 降序
sa 可以创建一个例叫 重复次数
效果如下
（3）将多余的数据删除只留下 id最小的
delete from [Y_DSSDB].[dbo].[yss_microbe]
where microbe_latin in (select microbe_latin from [Y_DSSDB].[dbo].[yss_microbe] group by microbe_latin having count(microbe_latin) > 1)
and microbe_ID not in (select min(microbe_ID) from [Y_DSSDB].[dbo].[yss_microbe] group by microbe_latin having count(microbe_latin )>1)
where microbe_latin in (select microbe_latin from [Y_DSSDB].[dbo].[yss_microbe] group by microbe_latin having count(microbe_latin) > 1)
and microbe_ID not in (select min(microbe_ID) from [Y_DSSDB].[dbo].[yss_microbe] group by microbe_latin having count(microbe_latin )>1)
ok了
菜鸟一个，大家斧正。
再分享一个代码 可以用来换类型 。
class SafeConverter
{
public static char ConvertToChar(object sValue)
{
char iV = '0';
if (sValue == null) return iV;
try
{
iV = Convert.ToChar(sValue);
}
catch
{
}
return iV;
}
public static byte ConvertToByte(object sValue)
{
byte iV = 0;
if (sValue == null) return iV;
try
{
iV = Convert.ToByte(sValue);
}
catch
{
}
return iV;
}
public static short ConvertToShort(object sValue)
{
short iV = 0;
if (sValue == null) return iV;
try
{
iV = Convert.ToInt16(sValue);
}
catch
{
}
return iV;
}
public static ushort ConvertToUShort(object sValue)
{
ushort iV = 0;
if (sValue == null) return iV;
try
{
iV = Convert.ToUInt16(sValue);
}
catch
{
}
return iV;
}
public static int ConvertToInt(object sValue)
{
int iV = 0;
if (sValue == null) return iV;
try
{
iV = Convert.ToInt32(sValue);
}
catch
{
}
return iV;
}
public static uint ConvertToUInt(object sValue)
{
uint iV = 0;
if (sValue == null) return iV;
try
{
iV = Convert.ToUInt32(sValue);
}
catch
{
}
return iV;
}
public static long ConvertToLong(object sValue)
{
long iV = 0;
if (sValue == null) return iV;
try
{
iV = Convert.ToInt64(sValue);
}
catch
{
}
return iV;
}
public static ulong ConvertToULong(object sValue)
{
ulong iV = 0;
if (sValue == null) return iV;
try
{
iV = Convert.ToUInt64(sValue);
}
catch
{
}
return iV;
}
public static float ConvertToFloat(object sValue)
{
float iV = 0;
if (sValue == null) return iV;
try
{
iV = Convert.ToSingle(sValue);
}
catch
{
}
return iV;
}
public static double ConvertToDouble(object sValue)
{
double iV = 0;
if (sValue == null) return iV;
try
{
iV = Convert.ToDouble(sValue);
}
catch
{
}
return iV;
}
public static bool ConvertToBool(object sValue)
{
bool iV = false;
if (sValue == null) return iV;
try
{
iV = Convert.ToBoolean(sValue);
}
catch
{
}
return iV;
}
public static DateTime ConvertToDateTime(object sValue)
{
DateTime iV = new DateTime();
if (sValue == null) return iV;
try
{
iV = Convert.ToDateTime(sValue);
}
catch
{
}
return iV;
}
public static string ConvertToString(object sValue)
{
string iV = "";
if (sValue == null) return iV;
try
{
iV = Convert.ToString(sValue);
}
catch
{
}
return iV;
}
}
4909
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
