C#Winform版获取Excel文件的内容

时间:2021-05-31 21:05:29
 public MainForm()
{
InitializeComponent();
//this.ofd_in.DefaultExt = ".xlsx";
this.ofd_in.Filter = "Excel文件(*.xlsx)|*.xlsx";
}
// code.GetSheetName getname = new code.GetSheetName();
private void btnDr_Click(object sender, EventArgs e)
{
if (ofd_in.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
this.txtFileName.Text = ofd_in.FileName.ToString();
List<Model.ExcelSheet> lst = code.GetSheetName.ExcelSheetName(ofd_in.FileName.ToString());
cmbBox.DataSource = lst;
cmbBox.DisplayMember = "SheetName";
cmbBox.ValueMember = "Id"; //ImportExcel(ofd_in.FileName, "[Sheet1$]");//传递excel文件名,和表名
}
} /// <summary>
/// 从选择的excel导入
/// </summary>
/// <returns></returns>
public void ImportExcel(string ExcelName, string Tablename)
{
// string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelName + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1';";//连接excel文件的字符串 string strcon = "Provider=Microsoft.Ace.OleDb.12.0;data source=" + ExcelName + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
if (ExcelName == null)
{
return;
}
OleDbConnection odcon = new OleDbConnection(strcon);//建立连接
odcon.Open();//打开连接 //把数据集dataset放到控件中显示
OleDbDataAdapter odda = new OleDbDataAdapter("select * from " + Tablename, odcon);
DataSet ds = new DataSet();
try
{
odda.Fill(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (ds != null)
{ this.dgvdata.DataSource = ds.Tables[]; }
else
{
MessageBox.Show("没有数据");
}
} private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
} /// <summary>
/// 保存,选中的datagridview的值
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSave_Click(object sender, EventArgs e)
{
int selectedCellCount = dgvdata.GetCellCount(DataGridViewElementStates.Selected);
if (selectedCellCount > )
{
if (dgvdata.AreAllCellsSelected(true))
{
MessageBox.Show("All cells are selected", "Selected Cells");
}
else
{
StringBuilder sb = new StringBuilder(); for (int i = ;
i < selectedCellCount; i++)
{
sb.Append("Row: ");
int rows = dgvdata.SelectedCells[i].RowIndex;
sb.Append(rows.ToString());
sb.Append(", Column: ");
int columns = dgvdata.SelectedCells[i].ColumnIndex;
sb.Append(columns.ToString());
sb.Append(",Value: ");
sb.Append(dgvdata.Rows[rows].Cells[columns].Value.ToString());
sb.Append(Environment.NewLine);
} sb.Append("Total: " + selectedCellCount.ToString());
MessageBox.Show(sb.ToString(), "Selected Cells");
}
}
} /// <summary>
/// 加载对应的sheet页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSearch_Click(object sender, EventArgs e)
{
dgvdata.DataSource = null;
var sheetname = this.cmbBox.Text.Trim(); ;
ImportExcel(this.txtFileName.Text, "["+sheetname+"]");
}