using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;
namespace Utility
{
public class ExcelHelper
{
private Application app;
private Workbooks wbks;
private _Workbook _wbk;
private Sheets shs;
private _Worksheet _wsh;
private String filePath;
public ExcelHelper(String filePath)
{
this.filePath = filePath;
create(filePath);
}
public void create(String filePath)
{
app = new Application();
wbks = app.Workbooks;
_wbk = wbks.Add(filePath);
shs = _wbk.Sheets;
if (shs.Count > 0)
_wsh = shs[1];
}
public Boolean setCellsTo(int row, int Column, String value)
{
Range r = _wsh.Cells[row, Column];
r.set_Value(value);
return true;
}
public Boolean setCellsTo(int _WorksheetIndex, int row, int Column, String value)
{
if (!setWorksheet(_WorksheetIndex))
{
return false;
}
return setCellsTo(row, Column, value);
}
public Boolean setCellsTo(String _WorksheetName, int row, int Column, String value)
{
return setCellsTo(getWorksheet(_WorksheetName), row, Column, value);
}
public Boolean setWorksheet(int index)
{
_wsh = shs[index];
return true;
}
public Boolean setWorksheet(String sheetName)
{
int index = getWorksheet(sheetName);
if (index == -1)
return false;
return setWorksheet(index);
}
public int getWorksheet(String sheetName)
{
int index = 1;
for (; index <= shs.Count; index++)
{
_Worksheet w = shs[index];
if (sheetName.Trim().Equals(w.Name.Trim()))
return index;
}
return -1;
}
public String getCellsValue(int row, int Column)
{
Range r = _wsh.Cells[row, Column];
return r.Text;
}
public String getCellsValue(int _WorksheetIndex, int row, int Column)
{
if (!setWorksheet(_WorksheetIndex))
{
throw new Exception("找不到该工作簿");
}
return getCellsValue(row, Column);
}
public String getCellsValue(String _WorksheetIndex, int row, int Column)
{
if (!setWorksheet(_WorksheetIndex))
{
throw new Exception("找不到该工作簿");
}
return getCellsValue(row, Column);
}
public void close()
{
_wbk.Close(null, null, null);
wbks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
public void save()
{
save(filePath);
}
public void save(String filePath)
{
//屏蔽掉系统跳出的Alert
app.AlertBeforeOverwriting = false;
//保存到指定目录
_wbk.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//
public List<String> getRow(int row, int startColumn, int endColumn, String stopStr)
{
List<String> cols = new List<string>();
for (int n = startColumn; startColumn < endColumn; n++)
{
String value = this.getCellsValue(row, n);
if (value.Trim().Equals(stopStr) && stopStr != null)
break;
cols.Add(value);
}
return cols;
}
/// <summary>
/// 遇到value= "" 返回
/// </summary>
/// <param name="row"></param>
/// <param name="startColumn"></param>
/// <returns></returns>
public List<String> getRow(int row, int startColumn)
{
return getRow(row, startColumn, int.MaxValue, "");
}
public String[] getSheetNames()
{
String[] Names = new String[shs.Count];
for (int n = 1; n <= shs.Count; n++)
{
Names[n] = shs[n];
}
return Names;
}
}
}
