|
#region
public class ExcelHelper : IExcelHelper
{
protected String filePath;
protected Application excel;
protected Workbook workBook;
protected OleDbConnection connection = null;
protected string connStr = null;
public ExcelHelper()
{
}
public ExcelHelper(String filePath,bool ReadOnly)
{
this.filePath = filePath;
this.connStr = getConnectionStr(filePath);
LoadWorkbook(ReadOnly);
}
///
/// 从Excel中加载workbook
///
private void LoadWorkbook(bool ReadOnly)
{
excel = new Application();
excel.Visible = false;
workBook = excel.Workbooks.Open(this.filePath, Missing.Value, ReadOnly,
Missing.Value, Missing.Value, Missing.Value, true, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
}
///
/// 创建Excel工作薄
///
public void CreateExcel()
{
excel = new Application();
workBook = excel.Application.Workbooks.Add(true);
}
///
/// 将数据写入Excel
///
/// 要写入的字符串
/// 写入的行
/// 写入的列
public void WriteData(string sheetName, string data, int row, int column)
{
Worksheet worksheet = (Worksheet)excel.Worksheets[sheetName];
worksheet.Cells[row, column] = data;
}
///
/// 将一个数据表写入一个sheet中
///
/// 表名
/// 数据表
/// 起始行，从1开始
/// 起始列，从1开始
public void WriteData(string sheetName, System.Data.DataTable data,
int startRow, int startColumn)
{
Worksheet worksheet = (Worksheet)excel.Worksheets[sheetName];
for (int i = 0; i <= data.Rows.Count - 1; i++)
{
for (int j = 0; j <= data.Columns.Count - 1; j++)
{
worksheet.Cells[startRow + i, startColumn + j] = data.Rows[i][j].ToString();
}
}
}
///
/// 新建工作表
///
/// 工作表名
public void CreateWorkSheet(string sheetName)
{
Worksheet newWorksheet = (Worksheet)workBook.Worksheets.Add(
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
newWorksheet.Name = sheetName;
}
///
/// 激活工作表
///
/// 工作表名
public void ActivateSheet(string sheetName)
{
Worksheet worksheet = (Worksheet)excel.Worksheets[sheetName];
worksheet.Activate();
}
///
/// 激活工作表
///
/// 工作表序号
public void ActivateSheet(int sheetNum)
{
Worksheet worksheet = (Worksheet)excel.Worksheets[sheetNum];
worksheet.Activate();
}
///
/// 删除一个工作表
///
/// 删除的工作表名
public void DeleteSheet(int sheetNum)
{
((Worksheet)workBook.Worksheets[sheetNum]).Delete();
}
///
/// 删除一个工作表
///
/// 删除的工作表序号
public void DeleteSheet(string sheetName)
{
((Worksheet)workBook.Worksheets[sheetName]).Delete();
}
///
/// 判断一个工作表是否存在
///
///
///
public bool isExistSheet(string sheetName)
{
if (workBook.Worksheets[sheetName] != null)
{
return true;
}
else return false;
}
///
/// 从Excel中获取一个Sheet，转化为DataSet
///
/// 表名
///
public DataSet getDataSet(String sheetName)
{
if (!isExistSheet(sheetName))
{
return null;
}
connection = new OleDbConnection(connStr);
connection.Open();
OleDbDataAdapter dataAdapter =
new OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", connection);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
connection.Close();
return dataSet;
}
private String getConnectionStr(String filePath)
{
if (filePath.EndsWith(".xlsx"))
{
// 2007以上的Excel版本
return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};"
+"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", filePath);
}
return "provider=microsoft.jet.oledb.4.0;data source="
+ filePath + ";extended properties='Excel 8.0;HDR=yes;IMEX=2'";
}
///
/// 保存Excel
///
/// 保存成功返回True
public bool Save()
{
if (filePath == "")
{
return false;
}
else
{
try
{
workBook.Save();
return true;
}
catch (Exception)
{
return false;
}
}
}
///
/// Excel文档另存为
///
/// 保存完整路径加文件名
/// 保存的Excel文件版本
/// 保存成功返回True
public bool SaveAs(string fileName, XlFileFormat version)
{
try
{
workBook.SaveAs(fileName, version, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
return true;
}
catch (Exception)
{
return false;
}
}
///
/// 关闭Excel
///
/// 是否保存
public void Close(bool isSave)
{
workBook.Close(isSave, Type.Missing, Type.Missing);
excel.Quit();
workBook = null;
excel = null;
GC.Collect();
}
///
/// 关闭Excel
///
/// 是否保存
/// 存储文件名
public void Close(bool isSave, string fileName)
{
workBook.Close(isSave, fileName, Type.Missing);
excel.Quit();
workBook = null;
excel = null;
GC.Collect();
}
}
#endregion
|
