/// <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 }); }