首先要说一下自己写的一个帮助类ExcelHelper。可以用两种方式访问Excel文件,一种是以Excel的Com组件的形式,一种是最简单的把Excel看作是数据库。
using System;
using System.IO;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace com.urp.command.FileFrameWork.Helper
{
/**//// <summary>
/// Class1 的摘要说明。
/// </summary>
public class ExcelHelper
{
private readonly string readConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source={0};"
+"Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
private readonly string writeConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source={0};"
+"Extended Properties='Excel 8.0;HDR=YES;'";
private OleDbConnection _conn = null;
private OleDbCommand _command = null;
private OleDbDataAdapter _adapter = null;
private OleDbCommandBuilder _builder = null;
private Excel.Application _application = null;
private Excel._Workbook _workBook = null;
private Excel._Worksheet _workSheet = null;
private Excel.Range _range = null;
private int _columnCount;
private int _rowCount;
private object _missingValue = System.Reflection.Missing.Value;
public ExcelHelper(string path)
{
readConn = String.Format(readConn, path);
writeConn = String.Format(writeConn, path);
}
public ExcelHelper()
{
}
ComExcel#region ComExcel
public int UsedColumnCount
{
get
{
if(_columnCount == 0)
_columnCount = _workSheet.UsedRange.Columns.Count;
return _columnCount;
}
}
public int UsedRowCount
{
get
{
if(_rowCount == 0)
_rowCount = _workSheet.UsedRange.Rows.Count;
return _rowCount;
}
}
//打开一个Excel线程
public void OpenWorkBook(string path)
{
try
{
_application = new Excel.ApplicationClass();
_application.Visible = false;
_workBook = _application.Workbooks.Open(path, 0, true, 5, "", "", false,
Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}
catch(Exception ex)
{
throw ex;
}
}
//新建一个Excel线程
public void NewWorkBook()
{
_application = new Excel.ApplicationClass();
_application.Visible = false;
_workBook = (Excel._Workbook)(_application.Workbooks.Add(_missingValue));
}
//根据名字获取sheet
public bool OpenWorkSheet(string sheetName)
{
_workSheet = (Excel._Worksheet)_workBook.Worksheets.get_Item(sheetName);
if(_workSheet != null)
return true;
return false;
}
//默认获得第一个sheet
public bool OpenWorkSheet(int sheetIndex)
{
_workSheet = (Excel._Worksheet)_workBook.Worksheets.get_Item(sheetIndex + 1);
if(_workSheet != null)
return true;
return false;
}
//保存
public void SaveSheet(string path)
{
_workBook.SaveAs(path, _missingValue, _missingValue, _missingValue, _missingValue,
_missingValue, Excel.XlSaveAsAccessMode.xlNoChange, _missingValue, _missingValue,
_missingValue, _missingValue, _missingValue);
}
//保存并关闭
public void CloseSaveSheet(string path)
{
// Remove(path);
this.SaveSheet(path);
this.CloseSheet();
}
//关闭
public void CloseSheet()
{
if(_workBook != null)
_workBook.Close(false, _missingValue, _missingValue);
if(_application != null)
_application.Quit();
if(_range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(_range);
_range = null;
}
if(_workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(_workSheet);
_workSheet = null;
}
if(_workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(_workBook);
_workBook = null;
}
if(_application != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(_application);
_application = null;
}
GC.Collect();
}
CellByCell#region CellByCell
public object GetCellByCell(int rowIndex, int columnIndex)
{
_range = _workSheet.get_Range(CellStr(rowIndex, columnIndex), _missingValue);
return _range.Value2;
}
public void SetCellByCell(int rowIndex, int columnIndex, object value)
{
_range = _workSheet.get_Range(CellStr(rowIndex, columnIndex), _missingValue);
_range.set_Value(_missingValue, value);
}
//自动将行数加一
private string CellStr(int rowIndex, int columnIndex)
{
return (char)(65 + columnIndex) + (++rowIndex).ToString();
}
#endregion
range#region range
public Array GetByRangeArray2(int rowStart, int columnStart, int rowEnd, int columnEnd)
{
_range = _workSheet.get_Range(CellStr(rowStart, columnStart), CellStr(rowEnd, columnEnd));
return (Array)_range.Value2;
}
public void SetByRangeArray2(int rowStart, int columnStart, int rowEnd, int columnEnd, Array values)
{
_range = _workSheet.get_Range(CellStr(rowStart, columnStart), CellStr(rowEnd, columnEnd));
_range.Value2 = values;
}
#endregion
#endregion
ADO.Net#region ADO.Net
//以非导入的方式打开连接
public void OpenReadConnection()
{
_conn = new OleDbConnection(readConn);
_conn.Open();
}
//以非导入的方式打开连接
public void OpenWriteConnection()
{
_conn = new OleDbConnection(writeConn);
_conn.Open();
}
//在excel中先建一个sheet并写入列名
public void NewTable(DataTable dataTable)
{
StringBuilder columnNames = new StringBuilder("Create Table ");
columnNames.Append("sheet1(");
foreach(DataColumn column in dataTable.Columns)
{
columnNames.Append(column.ColumnName);
columnNames.Append(" varchar, ");
}
columnNames.Replace(", ", ")", columnNames.Length - 2 , 2);
Command.CommandText = columnNames.ToString();
Command.ExecuteNonQuery();
}
//关闭连接
public void CloseConnection()
{
if(_conn != null)
_conn.Close();
_conn = null;
_command = null;
_adapter = null;
_builder = null;
}
DataReader#region DataReader
public IDataReader GetByDataReader()
{
Command.CommandText = "Select * From [sheet1$]";
return Command.ExecuteReader();
}
public IDataReader GetByDataReader(int rowStart, int columnStart, int rowEnd, int columnEnd)
{
string range = CellStr(rowStart, columnStart) + ":" + CellStr(rowEnd, columnEnd);
Command.CommandText = "Select * From [sheet1$" + range + "]";
return Command.ExecuteReader();
}
#endregion
DataTable#region DataTable
public DataTable GetByDataTable()
{
Command.CommandText = "select * from [sheet1$]";
DataTable dt = new DataTable();
Builder = new OleDbCommandBuilder(Adapter);
Adapter.Fill(dt);
return dt;
}
public void SetByDataTable(DataTable dataTable)
{
try
{
Command.CommandText = "select * from [sheet1$]";
DataTable dt = new DataTable("sheet1");
Builder = new OleDbCommandBuilder(Adapter);
Adapter.Fill(dt);
Adapter.InsertCommand = Builder.GetInsertCommand();
Adapter.Update(dataTable);
dt = null;
}
catch(OleDbException ex)
{
throw ex;
}
}
private OleDbCommand Command
{
get
{
if(_command == null)
{
_command = new OleDbCommand();
_command.Connection = _conn;
}
return _command;
}
}
private OleDbDataAdapter Adapter
{
get
{
if(_adapter == null)
{
_adapter = new OleDbDataAdapter();
_adapter.SelectCommand = Command;
}
return _adapter;
}
}
private OleDbCommandBuilder Builder
{
get
{
return _builder;
}
set
{
_builder = value;
_builder.QuotePrefix="["; //获取insert语句中保留字符(起始位置)
_builder.QuoteSuffix="]"; //获取insert语句中保留字符(结束位置)
}
}
#endregion
#endregion
}
}