public
class
ImportExportToExcel
... {
private string strConn;
public ImportExportToExcel()
...{
//
// TODO: 在此处添加构造函数逻辑
//
}
//从指定的Excel文件导入
public DataSet ImportFromExcel(string strFileName)
...{
DataSet ds = new DataSet();
ds = doImport(strFileName);
return ds;
}
//执行导入
private DataSet doImport(string strFileName)
...{
if(strFileName=="") return null;
strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+strFileName+";Extended Properties=Excel 8.0";
OleDbDataAdapter excelDA = new OleDbDataAdapter("select * from [Sheet1$]",this.strConn);//连接字符串
DataSet ExcelDs = new DataSet();//建立数据集,用于存放导入Excel的数据
try
...{
excelDA.Fill(ExcelDs,"ExcelInfo");//填充DataSet
}
catch(Exception ee)
...{
System.Windows.Forms.MessageBox.Show(ee.Message);
}
return ExcelDs;//返回DataSet
}
public void ExportToExcel(DataSet ds,string strExcelFileName)
...{
if(ds.Tables.Count == 0|| strExcelFileName=="") return;
doExport(ds,strExcelFileName);
}
//执行导出
public void doExport(DataSet ds , string strExcelFileName)
...{
Excel.ApplicationClass excel = new Excel.ApplicationClass();//建立Excel对象
int rowIndex = 1;
int colIndex=0;
excel.Application.Workbooks.Add(true);//Excel表为添加状态
System.Data.DataTable table = ds.Tables[0];//建立DataTable
foreach(DataColumn col in table.Columns)//填充表头
...{
colIndex++;//列索引值递增
excel.Cells[1,colIndex] = col.ColumnName;//指定列填充数据
}
foreach(DataRow row in table.Rows)//填充数据
...{
rowIndex++;
colIndex=0;
foreach(DataColumn col in table.Columns)
...{
colIndex++;
excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
excel.Visible = false;
//excel.Visible = true;
excel.DisplayAlerts = false;
excel.ActiveWorkbook.SaveAs(strExcelFileName,Excel.XlFileFormat.xlExcel7,Type.Missing,Type.Missing, Type.Missing, Type.Missing,Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel.Quit();
excel=null;
GC.Collect();//垃圾回收
}
}
... {
private string strConn;
public ImportExportToExcel()
...{
//
// TODO: 在此处添加构造函数逻辑
//
}
//从指定的Excel文件导入
public DataSet ImportFromExcel(string strFileName)
...{
DataSet ds = new DataSet();
ds = doImport(strFileName);
return ds;
}
//执行导入
private DataSet doImport(string strFileName)
...{
if(strFileName=="") return null;
strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+strFileName+";Extended Properties=Excel 8.0";
OleDbDataAdapter excelDA = new OleDbDataAdapter("select * from [Sheet1$]",this.strConn);//连接字符串
DataSet ExcelDs = new DataSet();//建立数据集,用于存放导入Excel的数据
try
...{
excelDA.Fill(ExcelDs,"ExcelInfo");//填充DataSet
}
catch(Exception ee)
...{
System.Windows.Forms.MessageBox.Show(ee.Message);
}
return ExcelDs;//返回DataSet
}
public void ExportToExcel(DataSet ds,string strExcelFileName)
...{
if(ds.Tables.Count == 0|| strExcelFileName=="") return;
doExport(ds,strExcelFileName);
}
//执行导出
public void doExport(DataSet ds , string strExcelFileName)
...{
Excel.ApplicationClass excel = new Excel.ApplicationClass();//建立Excel对象
int rowIndex = 1;
int colIndex=0;
excel.Application.Workbooks.Add(true);//Excel表为添加状态
System.Data.DataTable table = ds.Tables[0];//建立DataTable
foreach(DataColumn col in table.Columns)//填充表头
...{
colIndex++;//列索引值递增
excel.Cells[1,colIndex] = col.ColumnName;//指定列填充数据
}
foreach(DataRow row in table.Rows)//填充数据
...{
rowIndex++;
colIndex=0;
foreach(DataColumn col in table.Columns)
...{
colIndex++;
excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
excel.Visible = false;
//excel.Visible = true;
excel.DisplayAlerts = false;
excel.ActiveWorkbook.SaveAs(strExcelFileName,Excel.XlFileFormat.xlExcel7,Type.Missing,Type.Missing, Type.Missing, Type.Missing,Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel.Quit();
excel=null;
GC.Collect();//垃圾回收
}
}