DotNet 操纵Excel总结之三
/// <summary>
/// 从选择的Excel文件导入
/// </summary>
/// <returns>DataSet</returns>
public DataSet ImportFromExcel()
{
DataSet ds=new DataSet();
string file = "e:\\text.xls";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";" +
"Extended Properties=Excel 8.0;";
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
try
{
ExcelDA.Fill(ExcelDs, "ExcelInfo");
}
catch(Exception err)
{
System.Console.WriteLine( err.ToString() );
}
}
/// <summary>
/// 导出用户选择的Excel文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToExcel(DataSet ds)
{
string file = "e:\\text.xls";
Excel.Application excel= new Excel.Application();
int rowIndex=1;
int colIndex=0;
System.Data.DataTable table=ds.Tables[0] ;
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.Sheets[0] = "sss";
excel.ActiveWorkbook.SaveAs
strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
//wkbNew.SaveAs strBookName
//excel.Save(strExcelFileName);
excel.Quit();
excel=null;
}