/// <summary>
/// ОТКРЫТЬ ДОКУМЕНТ
/// </summary>
/// <param name="name"></param>
public void OpenDocument(string name)
{
WorkBooks = oExcel.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, oExcel, null);
WorkBook = WorkBooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, WorkBooks, new object[] { name, true });
WorkSheets = WorkBook.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, WorkBook, null);
WorkSheet = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { 1 });
}
private void btnTest_Click(object sender, EventArgs e)
{
WorkSheets wWorkSheetsList = new WorkSheets();
wWorkSheetsList = ImportFunctions.SearchWorkSheets(@"C:\PruebaExportacion.xls");
workSheetsBindingSource.DataSource = wWorkSheetsList;
}
/// <summary>
/// 读取数据到二维数组
/// </summary>
/// <param name="sheetName">WorkSheet名</param>
/// <param name="range">读取的区域，如"D1:F15"</param>
/// <returns>一个二维数组</returns>
/// 例如：Object[,] val = excel.getValues("Sheet1", "D1:D18");
public Object[,] getValues(string sheetName, string range)
{
Object[,] rtnValue;
try
{
WorkSheet = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { sheetName });
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range });
if (range == "A1:A1")
{
//定义一个只包含一个[1,1]元素的二维数组,数组下标从1开始
Array array = Array.CreateInstance(typeof(Object), new int[] { 1, 1 }, new int[] { 1, 1 });
//把range中的唯一元素赋值给数组
array.SetValue(Range.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, Range, null), 1, 1);
rtnValue = (Object[, ])array; //把Array类型的二维数组转换成Object类型后赋值rtnValue
}
else
{
rtnValue = (Object[, ])Range.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, Range, null);
}
return(rtnValue);
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// 重命名表
/// </summary>
/// <param name="n"></param>
/// <param name="Name"></param>
public void ReNamePage(int n, string Name)
{
//Range.Interior.ColorIndex
object Page = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { n });
Page.GetType().InvokeMember("Name", BindingFlags.SetProperty, null, Page, new object[] { Name });
}
/// <summary>
/// 加载工作簿
/// </summary>
/// <param name="fileName">文件名称，绝对路径</param>
protected override void LoadWorkbook(string fileName)
{
FileStream fs = File.OpenRead(fileName);
switch (ExcelFormat)
{
case ExcelFormat.Xls:
_workbook = new HSSFWorkbook(fs);
break;
case ExcelFormat.Xlsx:
_workbook = new XSSFWorkbook(fs);
break;
default:
throw new Exception("未知 Excel 格式文件");
}
fs.Close();
// 读取当前表数据
var sheetNum = _workbook.NumberOfSheets;
for (int i = 0; i < sheetNum; i++)
{
ISheet sheet = _workbook.GetSheetAt(i);
var worksheet = new NpoiWorkSheet(sheet);
WorkSheets.Add(worksheet);
}
}
/// <summary>
/// 插入工作表
/// </summary>
/// <param name="sheetName">工作表名称</param>
/// <returns></returns>
public override IWorkSheet InsertSheet(string sheetName)
{
var sheet = _workbook.CreateSheet(sheetName);
var worksheet = new NpoiWorkSheet(sheet);
WorkSheets.Add(worksheet);
return(worksheet);
}
/// <summary>
/// 添加一个工作表
/// </summary>
/// <param name="Name">页面标题</param>
public void AddNewSheet(string Name)
{
//Worksheet.Add.Item
WorkSheet = WorkSheets.GetType().InvokeMember("Add", BindingFlags.GetProperty, null, WorkSheets, null);
object Page = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { 1 });
Page.GetType().InvokeMember("Name", BindingFlags.SetProperty, null, Page, new object[] { Name });
}
/// <summary>
/// 得到Excel文件中指定列的最大行数
/// </summary>
/// <param name="sheetName">WorkSheet的名字</param>
/// <param name="columnName">列标号(如A、B、C等)</param>
/// <returns>返回columnName列的最大行数</returns>
public int GetMaxRowNumber(string sheetName, string columnName)
{
WorkSheet = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { sheetName });
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { columnName + "65535" });
Range = Range.GetType().InvokeMember("End", BindingFlags.GetProperty, null, Range, new object[] { -4162 });
int RowMax = Convert.ToInt32(Range.GetType().InvokeMember("Row", BindingFlags.GetProperty, null, Range, null));
return(RowMax);
}
// НОВЫЙ ДОКУМЕНТ
public void NewDocument()
{
WorkBooks = oExcel.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, oExcel, null);
WorkBook = WorkBooks.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, WorkBooks, null);
WorkSheets = WorkBook.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, WorkBook, null);
WorkSheet = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { 1 });
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[1] {
"A1"
});
}
private object getWorkSheet(string sheetName)
{
object objRes = null;
try {
objRes = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { sheetName });
} catch (Exception e) {
Logging.Logg().Exception(e, string.Format("MSExcelIO::getWorkSheet (наименование={0}) - ...", sheetName), Logging.INDEX_MESSAGE.NOT_SET);
}
return(objRes);
}
/// <summary>
/// 取得Excel指定WorkSheet中指定单元格的值
/// </summary>
/// <param name="sheetName">WorkSheet的名字</param>
/// <param name="range">当前工作表中的某个单元格的值</param>
/// <returns></returns>
public string GetValue(string sheetName, string range)
{
try
{
WorkSheet = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { sheetName });
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range });
var rtn = Range.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, Range, null);
return(rtn == null ? "" : rtn.ToString());
}
catch (Exception)
{
return(null);
}
}
public string[] GetWorkSheetsnames()
{
int cnt = (int)WorkSheets.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, WorkSheets, null);
string[] s = new string[cnt];
for (int i = 1; i <= cnt; i++)
{
object Sheet = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { i });
string str = (string)Sheet.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, Sheet, null);
s[i - 1] = str;
}
return(s);
}
//Ctrl+ * 可以获得当前sheet的最大的行与列
/// 以A1:C3为基础选择,Selection.CurrentRegion.Select
/// <summary>
/// 取得指定工作表的内容的最大行数
/// </summary>
/// <param name="sheetName">工作表名</param>
/// <returns></returns>
public int?getMaxRows(string sheetName)
{
int?rtn = null;
try
{
WorkSheet = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { sheetName });
object range1 = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[1] {
"A1:C3"
});
object range = range1.GetType().InvokeMember("CurrentRegion", BindingFlags.GetProperty, null, range1, null);
//object range = WorkSheet.GetType().InvokeMember("UsedRange", BindingFlags.GetProperty, null, WorkSheet, null);
object rows = range.GetType().InvokeMember("Rows", BindingFlags.GetProperty, null, range, null);
rtn = (int)rows.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, rows, null);
return(rtn);
}
catch (Exception)
{
return(rtn);
}
}
private void btnTest_Click(object sender, EventArgs e)
{
WorkSheets wWorkSheetsList =new WorkSheets();
wWorkSheetsList = ImportFunctions.SearchWorkSheets(@"C:\PruebaExportacion.xls");
workSheetsBindingSource.DataSource = wWorkSheetsList;
}
/// <summary> /// Создать лист с названием. /// </summary> /// <param name="name">Название листа.</param> /// <returns>IExcelWorksheet.</returns> public IExcelWorksheet CreateWorksheet(string name) => new EpPlusWorksheet(WorkSheets.Add(name));
/// <summary> /// Применить стили к листам книги. /// </summary> private void ApplyStyles() => WorkSheets.ToList().ForEach(ApplyStyle);
/// <summary>
/// 获取工作表
/// </summary>
/// <param name="sheetName">工作表名称</param>
/// <returns></returns>
public override IWorkSheet GetSheet(string sheetName)
{
return(WorkSheets.Find(x => x.SheetName.Equals(sheetName, StringComparison.OrdinalIgnoreCase)));
}
private object getWorkSheet(string sheetName)
{
return(WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { sheetName }));
}
/// <summary>
/// Получить лист книги с указанным номером (1 - по умолчанию)
/// </summary>
/// <param name="num">Номер листа (1-ый - по умолчанию)</param>
/// <returns></returns>
private object getWorkSheet(int num = 1)
{
return(WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { num }));
}
public void SetActiveSheet(string Name)
{
WorkSheet = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { Name });
}
