按钮:
private void btnExport_Click(object sender, EventArgs e)
{
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog(); //打开文件对话框
fd.Filter = @"Excel文件 (*.xls; *.xlsx)|*.xls; *.xlsx|
All Files (*.*)|*.*"; //过滤exl文件
if (fd.ShowDialog() == DialogResult.OK)
{
string fileName = fd.FileName;//文件名
try
{
ImportExcel(fileName);
// LoadData();
}
catch (Exception ex)
{
throw;
}
finally
{
}
}
}
导入核心:
public DataSet ImportExcel(string filePath)
{
DataSet ds=null;
DataTable dt=null;
Microsoft.Office.Interop.Excel.Application excel=new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook=null;
Microsoft.Office.Interop.Excel.Worksheet worksheet=null;
Microsoft.Office.Interop.Excel.Sheets sheets=null;
Microsoft.Office.Interop.Excel.Range range=null;
object missing=System.Reflection.Missing.Value;
try
{
if (excel == null)
{
return null;
}
//打开Excel文件
workbook = excel.Workbooks.Open(
filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//获取所有的sheet表
sheets = workbook.Worksheets;
ds = new DataSet();
for (int i = 1; i <= sheets.Count; i++)
{
//获取第一个表
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;
int rowIndex = 1;
int colIndex = 1;
DataColumn dc;
dt = new DataTable();
dt.TableName = "table" + i.ToString();
//读取列名
for (int j = 0; j < colCount; j++)
{
range = worksheet.Cells[rowIndex, colIndex + j];
dc = new DataColumn();
dc.DataType = Type.GetType("System.String");
dc.ColumnName = range.Text.ToString().Trim();
//添加列
dt.Columns.Add(dc);
}
//读取行数据
for (int k = 1; k < rowCount; k++)
{
DataRow dr = dt.NewRow();
for (int l = 0; l < colCount; l++)
{
range = worksheet.Cells[rowIndex + k, colIndex + l];
dr[l] = range.Text.ToString();
}
dt.Rows.Add(dr);
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
workbook.Close();
excel.Quit();
//Marshal.ReleaseComObject(worksheet);
//Marshal.ReleaseComObject(workbook);
//Marshal.ReleaseComObject(excel);
worksheet=null;
workbook=null;
excel=null;
GC.Collect();
}
return ds;
}
参照链接:https://www.cnblogs.com/zzp0320/p/6956940.html