最近整理了下前段时间做的东西，关于Excel的读写，在网上也看到不少关于这方面的代码，还是觉得不是很完善，就自己整理了一个Excel文件读写的类。代码如下：
/// <summary>
/// 标题：Excel文件助手类
/// 描述：1.读取指定条件的Excel信息到内存中
/// 2.将内存中的信息导出到Excel文件中
/// </summary>
class Excel
{
//类成员
private string m_filepath;//文件路径
public OleDbConnection pOleDbconnection; //文件链接
string strConnection;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="filepath">excel文件路径</param>
public Excel (string filepath)
{
m_filepath = filepath;
}
public Excel ()
{
}
/// <summary>
/// 获取数据源
/// </summary>
/// <param name="filepath_">Excel文件路径</param>
/// <returns>Excel表格数据集</returns>
/// <example>
/// Provider代表连接驱动4.0版本
/// Data Source代表Excel的路径
/// Extended Properties代表连接Excel的版本，对于Excel 97以上版本都用Excel 8.0
/// HDR代表默认Excel第一行是否列名，Yse代表是可以直接读取，No反之1
/// </example>
public DataSet Excel2DataSet (string filepath_)
{
//获取文件扩展名
string fileType = System.IO.Path.GetExtension(filepath_);
if(fileType==".xls")
{
strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_filepath + ";Extended Properties=Excel 8.0";
}
else if(fileType == ".xlsx")
{
strConnection = @"Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + m_filepath + ";Extended Properties=Excel 12.0";
}
else if(string.IsNullOrEmpty(fileType))
{
return null;
}
DataSet dataSet = new DataSet();
System.Data.DataTable dtSheetName = new System.Data.DataTable();
OleDbDataAdapter dataAdapter;//数据适配器
try
{ //初始化链接并打开
pOleDbconnection = new OleDbConnection(strConnection);
pOleDbconnection.Open();
//获取数据源的表定义元数据
string SheetName = "";
dtSheetName = pOleDbconnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null,null,null,"TABLE" });
//初始化适配器
dataAdapter = new OleDbDataAdapter();
for(int i = 0;i < dtSheetName.Rows.Count;i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
string sql_F = @"Select * FROM [" + SheetName + "]";//SQL
//筛选有效表
if(SheetName.Contains("$") || SheetName.Replace("'","").EndsWith("$"))
{
dataAdapter.SelectCommand = new OleDbCommand(sql_F,pOleDbconnection);
DataSet dsItem = new DataSet();
dataAdapter.Fill(dsItem,"[" + SheetName + "]");
dataSet.Tables.Add(dsItem.Tables[0].Copy());
}
}
}
catch(Exception ex)
{
System.Windows.Forms.MessageBox.Show("链接Excel出错！"+ex.Message);
}
return dataSet;
}
/// <summary>
/// 将DataTable导出到Excel
/// </summary>
/// <param name="excelTable">DataTable</param>
/// <param name="strFilepath">文件路径</param>
public void DataTable2Excel (System.Data.DataTable excelTable,string strFilepath)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbook wBook = app.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet wSheet = wBook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
if(excelTable.Rows.Count > 0)
{
int row = 0;
row = excelTable.Rows.Count;
int col = excelTable.Columns.Count;
for(int i = 0;i < row;i++)
{
for(int j = 0;j < col;j++)
{
string str = excelTable.Rows[i][j].ToString();
wSheet.Cells[i + 2,j + 1] = str;
}
}
}
int size = excelTable.Columns.Count;
for(int i = 0;i < size;i++)
{
wSheet.Cells[1,1 + i] = excelTable.Columns[i].ColumnName;
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wBook.Save();
//保存excel文件
app.Save(strFilepath);
app.SaveWorkspace(strFilepath);
app.Quit();
app = null;
}
catch(Exception err)
{
System.Windows.Forms.MessageBox.Show("导出Excel出错！错误原因：" + err.Message,"提示信息",
System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Information);
}
finally
{
}
}
}