private void button2_Click(object sender, EventArgs e)
{
using (SqlConnection connection = new SqlConnection(dbConnectionString))
{
DataTable dt = GetDataFromExcelByCom(true); //使用 Microsoft.Office.Interop.Excel导入EXCEL数据到datatable里
dataGridView1.DataSource = dt; //dataGridview显示数据源
if (dataGridView1.Rows.Count < 0)
{
MessageBox.Show("没有数据！");
return;
}
DataRow dr = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
insertToSql(dr);
}
changeColor();
MessageBox.Show(this, "EXCEL表导入成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
dt = null;
dr = null;
}
}
private void insertToSql(DataRow dr)
{
//excel表中的列名和数据库中的列名一定要对应
int uid = Convert.ToInt32(dr["uid"].ToString());
string hth = dr["hth"].ToString();
string xm = dr["xm"].ToString();
decimal je = Convert.ToDecimal(dr["je"].ToString()); //对应SQL Money类型
string jedx = dr["jedx"].ToString();
decimal ll = Convert.ToDecimal(dr["ll"].ToString()); //对应SQL Money类型
int qx = Convert.ToInt32(dr["qx"].ToString());
int zfzq = Convert.ToInt32(dr["zfzq"].ToString());
int zffs = Convert.ToInt32(dr["zffs"].ToString());
DateTime Stime = Convert.ToDateTime(dr["Stime"].ToString());
DateTime Etime = Convert.ToDateTime(dr["Etime"].ToString());
DateTime? iszftime; // ?可以用加问号的可空值类型
if (dr["iszftime"].ToString() != "")
{
iszftime = Convert.ToDateTime(dr["iszftime"].ToString());
}
else
{
iszftime = null;
}
bool zt = Convert.ToBoolean(dr["zt"].ToString()); //对应SQL Bit类型
string bz = dr["bz"].ToString();
string ywy = dr["ywy"].ToString();
decimal tcbl;
if (dr["tcbl"].ToString() != "")
{
tcbl = Convert.ToDecimal(dr["tcbl"].ToString());
}
else
{
tcbl = 0;
}
int? zhanqi; //?可以用加问号的可空值类型
if (dr["zhanqi"].ToString() != "")
{
zhanqi = Convert.ToInt32(dr["zhanqi"].ToString());
}
else
{
zhanqi = null;
}
string strSQL =@"insert into Tabjiekuan(uid,hth,xm,je,jedx,ll,qx,zfzq,zffs,Stime,Etime,iszftime,zt,bz,ywy,tcbl,zhanqi)
values('" + uid + "','" + hth + "','" + xm + "','" + je + "','" + jedx + "','" + ll + "','" + qx + "','" + zfzq + "', '" + zffs + "','" + Stime + "','" + Etime + "','" + iszftime + "','" + zt + "','" + bz + "','" + ywy + "','" + tcbl + "','" + zhanqi + "')";
DbHelper db = new DbHelper();
DbCommand cmd = db.GetSqlStringCommond(strSQL);
db.ExecuteNonQuery(cmd);
}
DataTable GetDataFromExcelByCom(bool hasTitle = false) //引用Microsoft.Office.Interop.Excel 导入EXCEL
{
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
openFile.Multiselect = false;
if (openFile.ShowDialog() == DialogResult.Cancel) return null;
var excelFilePath = openFile.FileName;
Excel.Application app = new Excel.Application();
Excel.Sheets sheets;
object oMissiong = System.Reflection.Missing.Value;
Excel.Workbook workbook = null;
DataTable dt = new DataTable();
try
{
if (app == null) return null;
workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
sheets = workbook.Worksheets;
//将数据读入到DataTable中
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//读取第一张表
if (worksheet == null) return null;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
this.toolStripStatusLabel5.Maximum = iRowCount; //设置进度条的最大值(最大行数)********************************************************
//生成列头
for (int i = 0; i < iColCount; i++)
{
var name = "column" + i;
if (hasTitle)
{
var txt = ((Excel.Range)worksheet.Cells[1, i + 1]).Text.ToString();
if (!string.IsNullOrWhiteSpace(txt)) name = txt;
}
while (dt.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。
dt.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行数据
Excel.Range range;
int rowIdx = hasTitle ? 2 : 1;
for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Excel.Range)worksheet.Cells[iRow, iCol];
dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
}
dt.Rows.Add(dr);
this.toolStripStatusLabel5.Value = iRow; //进度条递增，每行
a = iRow;
b = iRowCount;
pNow = Convert.ToInt32((a / b)*100);
toolStripStatusLabel6.Text = pNow.ToString()+"%"; //计算百比分进度条
}
return dt;
}
catch { return null; }
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
dt = null;
}
}
