public IList <Row> GetSheetByRow()
{
List <Row> rows = new List <Row>();
object[,] value = (object[, ])app.ActiveSheet.Range["A1", ExcelConvert.ToName(app.ActiveSheet.UsedRange.Columns.Count) + app.ActiveSheet.UsedRange.Rows.Count].Value;
for (int row = 1; row <= value.GetLongLength(0); row++)
{
Row rowInfo = new Row()
{
Index = row
};
for (int col = 1; col <= value.GetLongLength(1); col++)
{
Cell cell = new Cell()
{
Value = value[row, col] == null ? "" : value[row, col].ToString(), ColumnName = ExcelConvert.ToName(col - 1), RowIndex = row
};
rowInfo.Cells.Add(cell);
}
rows.Add(rowInfo);
}
return(rows);
}
public IList <Cell> GetRangeByName(string rangeName)
{
List <Cell> cells = new List <Cell>();
dynamic rng = app.ActiveSheet.Range(rangeName);
if (rng == null)
{
return(cells);
}
if (rng.Cells.Count == 1)
{
Cell cell = new Cell()
{
Value = rng.Value(XlRangeValueDataType.xlRangeValueDefault), ColumnName = ExcelConvert.ToName((int)rng.Cells.Column - 1), RowIndex = rng.Cells.Row
};
cells.Add(cell);
return(cells);
}
object[,] exceldata = (object[, ])rng.Value(XlRangeValueDataType.xlRangeValueDefault);
for (int i = 1; i <= exceldata.GetLongLength(0); i++)
{
for (int j = 1; j <= exceldata.GetLongLength(1); j++)
{
Cell cell = new Cell()
{
Value = exceldata[i, j] == null ? "" : exceldata[i, j].ToString(), ColumnName = ExcelConvert.ToName(j - 1), RowIndex = i
};
cells.Add(cell);
}
}
return(cells);
}
public void SetCellValue(string sheetName, int rowIndex, string columnName, string value)
{
dynamic xlsWorkSheet = wkb.Worksheets[sheetName];
xlsWorkSheet.Cells[rowIndex, ExcelConvert.ToIndex(columnName) + 1] = value;
}
public void SetCellValue(int rowIndex, string columnName, string value)
{
app.ActiveSheet.Cells[rowIndex, ExcelConvert.ToIndex(columnName) + 1] = value;
}
public Column GetColumn(string columnName)
{
Column column = new Column();
dynamic rng = app.ActiveSheet.Range(columnName + 1 + ":" + columnName + UsedRowCount);
if (rng == null)
{
return(column);
}
if (rng.Cells.Count == 1)
{
Cell cell = new Cell()
{
Value = rng.Value(XlRangeValueDataType.xlRangeValueDefault).ToString(), ColumnName = ExcelConvert.ToName((int)rng.Cells.Column - 1), RowIndex = rng.Cells.Row
};
column.Cells.Add(cell);
return(column);
}
object[,] exceldata = (object[, ])rng.Value(XlRangeValueDataType.xlRangeValueDefault);
for (int i = 1; i <= exceldata.GetLongLength(0); i++)
{
for (int j = 1; j <= exceldata.GetLongLength(1); j++)
{
Cell cell = new Cell()
{
Value = exceldata[i, j] == null ? "" : exceldata[i, j].ToString(), ColumnName = ExcelConvert.ToName(j - 1), RowIndex = i
};
column.Cells.Add(cell);
}
}
return(column);
}