Excel 文件读取
/// <summary>
/// 读取Excel数据
/// </summary>
private void NewMethod()
{
string str = @"Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=" + textBox1.Text + ";Extended Properties='Excel 12.0;HDR=Yes'";
OleDbConnection ole = new OleDbConnection(str);
#region 代码未完成
//DataTable ol= ole.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// if (ol == null)
// {
// }
#endregion
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [IT外协信息$]", ole);//", ole);
DataTable dt = new DataTable();
adapter.Fill(dt);
this.dataGridView1.DataSource = dt.DefaultView;
}
private void FileOpen()
{
OpenFileDialog dlg = new OpenFileDialog();
dlg.Filter = "Excel文件|*.xls;*.xlsx|所有文件|*.*";
if (dlg.ShowDialog() == DialogResult.OK)
{
this.textBox1.Text = dlg.FileName;
dlg.Dispose();
#region 未完成代码
//dc.DBName = textBox1.Text;
//dsExcelFile = new ExcelDBSource(dc);
//cbbSheets.DataSource = dsExcelFile.GetTableNames();
//cbbSheets.SelectedIndex = 0;
#endregion
}
}
//Excel保存
/// <summary>
/// 保存更改货权数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void toolStripButton1_Click(object sender, EventArgs e)
{ private int rowMax = 0;
dgSplitData.CurrentCell = dgSplitData[0, 0];
string tempStrSQL;
//rowMax为当前DataGridView中最大行号
for (int i = rowMax; rowMax < dgSplitData.Rows.Count; rowMax++)
{
if (this.dgSplitData.Rows[rowMax].Cells[0].Value == null)
{
break;
}
else
{
string cargoCoName = dgSplitData.Rows[rowMax].Cells[0].Value.ToString().Trim();
string splitWeight = dgSplitData.Rows[rowMax].Cells[1].Value.ToString().Trim();
string shipName = dgSplitData.Rows[rowMax].Cells["ShipName"].Value.ToString().Trim();
string column3 = dgSplitData.Rows[rowMax].Cells["Column3"].Value.ToString().Trim();
string remark = dgSplitData.Rows[rowMax].Cells["Remark"].Value.ToString().Trim();
string sendValues = "('" + cargoCoName + "','" + splitWeight + "','" + shipName + "','" + column3 + "' ,'" + remark + "')";
tempStrSQL = "insert into t_SplitData (CargoCompany,Weight,ShipName,Commodity,Remark) values" + sendValues; App.UpdateDataBase(tempStrSQL);
}
}
MessageBox.Show("保存成功", "信息");
dgSplitData.Rows.Clear();
}
public static int UpdateDataBase(string tempStrSQL)
{
using (SqlConnection con = GetCon())
{
SqlCommand tempSqlcommand = new SqlCommand(tempStrSQL, con);
con.Open();
int intNumber = tempSqlcommand.ExecuteNonQuery();
return intNumber;
}
}