public void WriteRange(object[][] data, string startCell)
{
int startRowIndex = worksheet.GetRowIndex(startCell);
int startCellIndex = worksheet.GetRowIndex(startCell);
List <Row> newRows = new List <Row>();
for (int i = 0; i < data.Length; i++)
{
int rowIndex = startRowIndex + 1;
Row row = worksheet.GetRow(rowIndex);
if (row == null)
{
row = new Row()
{
RowIndex = (uint)rowIndex
};
newRows.Add(row);
}
for (int j = 0; j < data[i].Length; j++)
{
int cellIndex = startCellIndex + j;
string cellAddress = SpreadsheetExtender.ToCellAddress(rowIndex, cellIndex);
Cell resultCell = row.GetCell(cellAddress);
if (resultCell == null)
{
resultCell = row.CreateCell(cellAddress);
}
resultCell.SetCellText(data[i][j]);
int styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, data[i][j]);
resultCell.SetCellStyle(styleIndex);
}
sheetData.Append(newRows.ToArray());
}
}
public void AppendRange(object[][] data)
{
if (data == null || data.Length <= 0)
{
throw new ArgumentNullException("Please confirm whether input datas is empty!");
}
int rowIndex = worksheet.GetRowCount();
Row[] rows = new Row[data.Length];
for (int i = 0; i < data.Length; i++)
{
rowIndex += 1;
rows[i] = new Row {
RowIndex = (uint)rowIndex
};
for (int j = 0; j < data[i].Length; j++)
{
string cellAddress = SpreadsheetExtender.ToCellAddress(rowIndex, (j + 1));
Cell cell = rows[i].CreateCell(cellAddress);
cell.SetCellText(data[i][j]);
int styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, data[i][j]);
cell.SetCellStyle(styleIndex);
}
}
sheetData.Append(rows);
}
public void WriteRow(object[] data, int row, int column = 1)
{
object[][] table = new object[1][];
table[0] = data;
string cellAddress = SpreadsheetExtender.ToCellAddress(row, column);
WriteRange(table, cellAddress);
}
public void WriteColumn(object[] data, int column, int row = 1)
{
object[][] table = new object[data.Length][];
for (int i = 0; i < table.Length; i++)
{
table[i] = new object[] { data[i] };
}
string cellAddress = SpreadsheetExtender.ToCellAddress(row, column);
}
public object[] ReadColumn(int columnIndex, bool ignoreHeader = true)
{
string startCell = string.Empty;
string endCell = string.Empty;
object[] resultArray;
if (!ignoreHeader)
{
startCell = SpreadsheetExtender.ToCellAddress(header, columnIndex);
endCell = SpreadsheetExtender.ToCellAddress(worksheet.GetRowCount(), columnIndex);
}
var table = ReadRange(startCell, endCell);
resultArray = new object[table.Length];
for (int i = 0; i < table.Length; i++)
{
resultArray[i] = table[i][0];
}
return(resultArray);
}
private string GetCellAddressByMapping(int rowIndex, MappingAttribute attribute, Dictionary <string, Cell> cellMapping)
{
int cellIndex = 1;
string cellAddress = string.Empty;
switch (attribute.MappingOption)
{
case MappingOption.FieldName:
cellIndex = cellMapping[attribute.MappingKey].GetCellIndex();
cellAddress = SpreadsheetExtender.ToCellAddress(rowIndex, cellIndex);
break;
case MappingOption.ColumnName:
cellAddress = attribute.MappingKey + rowIndex; break;
case MappingOption.ColumnIndex:
cellAddress = SpreadsheetExtender.ToCellAddress(rowIndex, int.Parse(attribute.MappingKey));
break;
}
return(cellAddress);
}
private void SetStyleIndexByCellFormatOption(Cell cell, object value, CellFormatOption option, MappingAttribute attribute)
{
int styleIndex = 0;
switch (cellFormatOption)
{
case CellFormatOption.KeepStyle:
break;
case CellFormatOption.OverWrite:
styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, value);
cell.SetCellStyle(styleIndex);
break;
case CellFormatOption.ReferenceStyle:
if (string.IsNullOrEmpty(attribute.ReferenceStyle))
{
styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, value);
}
else
{
Cell refCell = SpreadsheetExtender.GetCell(worksheet, attribute.ReferenceStyle);
if (refCell == null)
{
throw new ArgumentException("Please confirm the cell to reference is not null!");
}
if (refCell.StyleIndex == null || (refCell.StyleIndex != null && !refCell.StyleIndex.HasValue))
{
styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, value);
}
else
{
styleIndex = int.Parse(refCell.StyleIndex.ToString());
}
cell.SetCellStyle(styleIndex);
} break;
}
}
private void SetStyleIndexByCellFormatOption(Cell cell, object value, CellFormatOption option)
{
int styleIndex = 0;
switch (option)
{
case CellFormatOption.KeepStyle:
if (cell.StyleIndex != null && cell.StyleIndex.HasValue)
{
return;
}
if ((cell.StyleIndex == null || (cell.StyleIndex != null && !cell.StyleIndex.HasValue)))
{
styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, value);
}
cell.SetCellStyle(styleIndex); break;
case CellFormatOption.OverWrite:
case CellFormatOption.ReferenceStyle:
styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, value);
cell.SetCellStyle(styleIndex); break;
}
}
public void WriteRange(object[][] data, int row, int column)
{
string cellAddress = SpreadsheetExtender.ToCellAddress(row, column);
WriteRange(data, cellAddress);
}
