//ЧТЕНИЕ ДАННЫХ ИЗ ВЫБРАННОЙ ЯЧЕЙКИ
public string GetValue(string range)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
return(Range.GetType().InvokeMember("Value", BindingFlags.GetProperty,
null, Range, null).ToString());
}
/// <summary>
/// 设置单元格的背景颜色
/// </summary>
/// <param name="range"></param>
/// <param name="r"></param>
public void SetColor(string range, int r)
{
//Range.Interior.ColorIndex
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range });
Interior = Range.GetType().InvokeMember("Interior", BindingFlags.GetProperty, null, Range, null);
Range.GetType().InvokeMember("ColorIndex", BindingFlags.SetProperty, null, Interior, new object[] { r });
}
//УСТАНОВИТЬ ВЫРАВНИВАНИЕ В ЯЧЕЙКЕ ПО ГОРИЗОНТАЛИ
public void SetHorisontalAlignment(string range, int Alignment)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
object[] args = new object[] { Alignment };
Range.GetType().InvokeMember("HorizontalAlignment", BindingFlags.SetProperty, null, Range, args);
}
//УСТАНОВИТЬ ВЫСОТУ СТРОК
public void SetRowHeight(string range, double Height)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
object[] args = new object[] { Height };
Range.GetType().InvokeMember("RowHeight", BindingFlags.SetProperty, null, Range, args);
}
/// <summary>
/// УСТАНОВКА НАПРАВЛЕНИЯ ТЕКСТА
/// </summary>
/// <param name="range"></param>
/// <param name="Orientation"></param>
public void SetTextOrientation(string range, int Orientation)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
object[] args = new object[] { Orientation };
Range.GetType().InvokeMember("Orientation", BindingFlags.SetProperty, null, Range, args);
}
/// <summary>
/// ЗАПИСАТЬ ЗНАЧЕНИЕ В ЯЧЕЙКУ
/// </summary>
/// <param name="range"></param>
/// <param name="value"></param>
public void SetValue(string range, string value)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
value = value.Replace("=", "-");
Range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, Range, new object[] { value });
}
/// <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);
}
}
//УДАЛЕНИЕ ПРИМЕЧАНИЯ
public void DeleteComment(string range)
{
//Range.ClearComment
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
Range.GetType().InvokeMember("ClearComments", BindingFlags.InvokeMethod, null, Range, null);
}
//УСТАНОВИТЬ ШИРИНУ СТОЛБЦОВ
public void SetColumnWidth(string range, double Width)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
object[] args = new object[] { Width };
Range.GetType().InvokeMember("ColumnWidth", BindingFlags.SetProperty, null, Range, args);
}
/// <summary>
/// ПЕРЕНОС СЛОВ В ЯЧЕЙКЕ
/// </summary>
/// <param name="range"></param>
/// <param name="Value"></param>
public void SetWrapText(string range, bool Value)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
object[] args = new object[] { Value };
Range.GetType().InvokeMember("WrapText", BindingFlags.SetProperty, null, Range, args);
}
/// <summary>
/// 缩放
/// </summary>
/// <param name="Percent"></param>
public void SetZoom(int Percent)
{
//Range.PageSetup.Zoom(打印的范围)
object PageSetup = WorkSheet.GetType().InvokeMember("PageSetup", BindingFlags.GetProperty, null, WorkSheet, null);
PageSetup.GetType().InvokeMember("Zoom", BindingFlags.SetProperty, null, PageSetup, new object[] { Percent });
}
/// <summary>
/// 设置工作表的大小
/// </summary>
/// <param name="Size"></param>
public void SetPaperSize(xlPaperSize Size)
{
//Range.PageSetup.PaperSize(纸张尺寸)
object PageSetup = WorkSheet.GetType().InvokeMember("PageSetup", BindingFlags.GetProperty, null, WorkSheet, null);
PageSetup.GetType().InvokeMember("PaperSize", BindingFlags.SetProperty, null, PageSetup, new object[] { Size });
}
public void SetCellValue(int Row, int Col, object value)
{
if (Row > 0 & Col > 0)
{
object cell = WorkSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, WorkSheet, new object[] { Row, Col });
cell.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, cell, new object[] { value });
}
}
/// <summary>
/// 得到Excel文件中指定列的最大行数
/// </summary>
/// <param name="columnName">列标号(如A、B、C等)</param>
/// <returns>返回columnName列的最大行数</returns>
public int GetMaxRowNumber(string columnName)
{
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);
}
/// <summary>
/// УСТАНОВИТЬ ВИД РАМКИ ВОКРУГ ЯЧЕЙКИ
/// </summary>
/// <param name="range"></param>
/// <param name="Style"></param>
public void SetBorderStyle(string range, int Style)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range });
object[] args = new object[] { Style };
object[] args1 = new object[] { 1 };
object Borders = Range.GetType().InvokeMember("Borders", BindingFlags.GetProperty, null, Range, null);
Borders = Range.GetType().InvokeMember("LineStyle", BindingFlags.SetProperty, null, Borders, args);
}
/// <summary>
/// Установить цвет ячейки
/// </summary>
/// <param name="range"></param>
/// <param name="Value"></param>
public void SetColor(string range, int sColor)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
object oInterior = Range.GetType().InvokeMember("Interior", BindingFlags.GetProperty, null, Range, null);
oInterior.GetType().InvokeMember("ColorIndex", BindingFlags.SetProperty, null, oInterior, new object[] { sColor });
oInterior.GetType().InvokeMember("Pattern", BindingFlags.SetProperty, null, oInterior, new object[] { 1 });
}
//УСТАНОВИТЬ ОРИЕНТАЦИЮ СТРАНИЦЫ
//1 - КНИЖНЫЙ
//2 - АЛЬБОМНЫЙ
public void SetOrientation(int Orientation)
{
//Range.Interior.ColorIndex
object PageSetup = WorkSheet.GetType().InvokeMember("PageSetup", BindingFlags.GetProperty,
null, WorkSheet, null);
PageSetup.GetType().InvokeMember("Orientation", BindingFlags.SetProperty,
null, PageSetup, new object[] { Orientation });
}
/// <summary>
/// 设置单元格的字体
/// </summary>
/// <param name="range"></param>
/// <param name="font"></param>
public void SetFont(string range, Font font)
{
//Range.Font.Name
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range });
object Font = Range.GetType().InvokeMember("Font", BindingFlags.GetProperty, null, Range, null);
Range.GetType().InvokeMember("Name", BindingFlags.SetProperty, null, Font, new object[] { font.Name });
Range.GetType().InvokeMember("Size", BindingFlags.SetProperty, null, Font, new object[] { font.Size });
}
/// <summary>
/// 设置页边距的大小表
/// </summary>
/// <param name="Left"></param>
/// <param name="Right"></param>
/// <param name="Top"></param>
/// <param name="Bottom"></param>
public void SetMargin(double Left, double Right, double Top, double Bottom)
{
object PageSetup = WorkSheet.GetType().InvokeMember("PageSetup", BindingFlags.GetProperty, null, WorkSheet, null);
PageSetup.GetType().InvokeMember("LeftMargin", BindingFlags.SetProperty, null, PageSetup, new object[] { Left }); //Range.PageSetup.LeftMargin
PageSetup.GetType().InvokeMember("RightMargin", BindingFlags.SetProperty, null, PageSetup, new object[] { Right }); //Range.PageSetup.RightMargin
PageSetup.GetType().InvokeMember("TopMargin", BindingFlags.SetProperty, null, PageSetup, new object[] { Top }); //Range.PageSetup.TopMargin
PageSetup.GetType().InvokeMember("BottomMargin", BindingFlags.SetProperty, null, PageSetup, new object[] { Bottom }); //Range.PageSetup.BottomMargin
}
// НОВЫЙ ДОКУМЕНТ
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"
});
}
//СОЗДАНИЕ ПРИМЕЧАНИЯ
public void CreateComment(string range, bool CommentVisible, string Text)
{
//Range.Addcomment
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
Range.GetType().InvokeMember("AddComment", BindingFlags.InvokeMethod, null, Range, null);
object Comment = Range.GetType().InvokeMember("Comment", BindingFlags.GetProperty, null, Range, null);
Comment.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, Comment, new object[] { false });
Comment.GetType().InvokeMember("Text", BindingFlags.InvokeMethod, null, Comment, new object[] { Text });
}
/// <summary>
/// 将源区域的内容剪贴到目标单元格为起始点的相同大小的区域中
/// </summary>
/// <param name="sourceRange">源区域</param>
/// <param name="targetCell">目标单元格</param>
public void CutRange(string sourceRange, string targetCell)
{
//源区域的Range对象
object source = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { sourceRange });
//目标单元格的Range对象
object target = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { targetCell });
//执行剪贴(类似的VBA语句：Range("A1:A10").Cut Range("C10"))
source.GetType().InvokeMember("Cut", BindingFlags.InvokeMethod, null, source, new object[] { target });
}
/// <summary>
/// 格式化单元格中的指定文本
/// </summary>
/// <param name="range"></param>
/// <param name="Start"></param>
/// <param name="Length"></param>
/// <param name="Color"></param>
/// <param name="FontStyle"></param>
/// <param name="FontSize"></param>
public void SelectText(string range, int Start, int Length, int Color, string FontStyle, int FontSize)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range });
object[] args = new object[] { Start, Length };
object Characters = Range.GetType().InvokeMember("Characters", BindingFlags.GetProperty, null, Range, args);
object Font = Characters.GetType().InvokeMember("Font", BindingFlags.GetProperty, null, Characters, null);
Font.GetType().InvokeMember("ColorIndex", BindingFlags.SetProperty, null, Font, new object[] { Color });
Font.GetType().InvokeMember("FontStyle", BindingFlags.SetProperty, null, Font, new object[] { FontStyle });
Font.GetType().InvokeMember("Size", BindingFlags.SetProperty, null, Font, new object[] { FontSize });
}
/// <summary>
/// УСТАНОВИТЬ ВЫСОТУ СТРОК
/// </summary>
/// <param name="range"></param>
/// <param name="Height"></param>
public void SetRowHeight(string range, double Height)
{
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
if (Height > 400)
{
Height = 409;//Максимальное значение
}
object[] args = new object[] { Height };
Range.GetType().InvokeMember("RowHeight", BindingFlags.SetProperty, null, Range, args);
}
public object GetCellValue(int Row, int Col)
{
if (Row > 0 & Col > 0)
{
object cell = WorkSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, WorkSheet, new object[] { Row, Col });
return(cell.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, cell, null));
}
else
{
return(string.Empty);
}
}
public int GetRow()
{
try
{
Range = WorkSheet.GetType().InvokeMember("UsedRange", BindingFlags.GetProperty, null, WorkSheet, null);
Rows = Range.GetType().InvokeMember("Rows", BindingFlags.GetProperty, null, Range, null);
return((int)Rows.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, Rows, null));
}
catch (Exception e)
{
return(0);
}
}
/// <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 int GetCurrRegCols(int FirstRow, int FirstCol)
{
if (FirstRow > 0 & FirstCol > 0)
{
object cell = WorkSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, WorkSheet, new object[] { FirstRow, FirstCol });
object curreg = cell.GetType().InvokeMember("CurrentRegion", BindingFlags.GetProperty, null, cell, null);
object cols = curreg.GetType().InvokeMember("Columns", BindingFlags.GetProperty, null, curreg, null);
return((int)cols.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, cols, null));
}
else
{
return(0);
}
}
/// <summary>
/// Получить объект - диапазон ячеек с указанным адресом на текущем листе
/// </summary>
/// <param name="pos">Строка - адрес диапазона (по умолчанию "A1")</param>
/// <returns>Объект - диапазон ячеек</returns>
private object getRange(string pos = "A1")
{
object objRes = null;
try {
objRes = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[1] {
pos
});
} catch (Exception e) {
Logging.Logg().Exception(e, string.Format("MSExcelIO::getRange (позиция={0}) - ...", pos), Logging.INDEX_MESSAGE.NOT_SET);
}
return(objRes);
}
//ГРУППИРОВКА СТОЛБЦОВ
public void SetColumnsGroup(string range, bool Value)
{
//Selection.Columns.Group
//Selection.Columns.Ungroup
Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, WorkSheet, new object[] { range });
object Columns = Range.GetType().InvokeMember("Columns", BindingFlags.GetProperty, null, Range, null);
if (Value)
{
Columns.GetType().InvokeMember("Group", BindingFlags.GetProperty, null, Columns, null);
}
else
{
Columns.GetType().InvokeMember("Ungroup", BindingFlags.GetProperty, null, Columns, null);
}
}