如何把Winform 和 Excel 连接到一起 。
发布网友
发布时间：2022-04-19 19:30
共2个回答
热心网友
时间：2023-09-06 02:15
1.引用Microsoft.Office.Interop.Excel
2.代码：
/// <summary>
/// 导入Excel文件，并显示在列表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImport_Click(object sender, EventArgs e)
{
string worksheetname = string.Empty;
importtpye = cmbTicketType.Text;
supplier = cmbSupplier.SelectedValue.ToString();
#region 导入本地Excel文件
//导入本地文件
OpenFileDialog file = new OpenFileDialog();
file.Filter = "文档(*.xls)|*.xls";
if (file.ShowDialog() == DialogResult.OK)
btnImport.Tag = file.FileName;
//判断有没有文件导入
if (file.FileName.Length == 0)
{
MessageBox.Show("请选择要导入的文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
#endregion
#region 获取Excel的工作表名称
//创建Excel实例，获取worksheet Name
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
object objMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook myBook = (Microsoft.Office.Interop.Excel.Workbook)oExcel.Workbooks.Open(file.FileName, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
Microsoft.Office.Interop.Excel.Sheets sheets = myBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
worksheetname = worksheet.Name;//获取worksheet Name
oExcel.Quit();
#endregion
dataTable.Rows.Clear();
//将Excel表中的数据导入到Datatable中
DataTable table = LoadExcelToDataTable(file.FileName, worksheetname);
}
3.代码：
/// <summary>
/// 加载Excel表到DataTable，跟原始Excel表形式一样，需要筛选自己有用的数据
/// </summary>
/// <param name="filename">需要读取的Excel文件路径</param>
/// <param name="sheetname">工作表名称</param>
/// <returns>DataTable</returns>
public static DataTable LoadExcelToDataTable(string filename, string worksheetname)
{
DataTable table;
//连接字符串
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
OleDbConnection myConn = new OleDbConnection(sConnectionString);
string strCom = " SELECT * FROM [" + worksheetname + "$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
table = new DataTable();
myCommand.Fill(table);
myConn.Close();
return table;
}
4.注意：有的人的连接字符串写成这样：
//连接字符串
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" +filename + ";" + "Extended Properties=Excel 8.0;";
这样也可以读取到数据，但是，如果Excel表里的某一列中既有文本类型的值，又有数字类型的值，即混合类型的列，那么就会只读取到一种类型的值，另一种类型的就会为NULL。
//连接字符串
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
这样，后边加上"HDR=Yse;IMEX=1"，并且这句必须用引号引住，这样就会把混合类型的数据同一当作文本读取，不会出现丢数据的现象。
二、方法二
/// <summary>
/// ExcelUtils 的摘要说明
/// </summary>
public class ExcelUtils
{
public static DataSet ReadExcelToDS(string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (schemaTable != null)
{
string sheetName = schemaTable.Rows[0][2].ToString().Trim();
//第一张sheet的名称，这个索引为什么是[0][2]也是尝试出来的
if (sheetName != null && sheetName.Length > 0)
{
string strExcel = "select * from [" + sheetName + "]";
OleDbDataAdapter myAdp = new OleDbDataAdapter(strExcel, conn);
DataSet ds = new DataSet();
myAdp.Fill(ds);
return ds;
}
}
return null;
}
catch (Exception e)
{
throw new WebAppException(e.Message) ;
}
}
}
热心网友
时间：2023-09-06 02:15
看看这个有帮助吗？
