/// <summary>
/// instance method, write data to existing worksheet, (WriteDataToExcelWorksheet node)
/// </summary>
/// <param name="startRow"></param>
/// <param name="startColumn"></param>
/// <param name="data"></param>
/// <param name="writeAsString"></param>
/// <returns></returns>
internal WorkSheet WriteData(int startRow, int startColumn, object[][] data, bool writeAsString = false)
{
startRow = Math.Max(0, startRow);
startColumn = Math.Max(0, startColumn);
int numRows, numColumns;
object[,] rangeData = ConvertToDimensionalArray(data, out numRows, out numColumns);
if (rangeData == null)
{
return(this);
}
var c1 = (Range)ws.Cells[startRow + 1, startColumn + 1];
var c2 = (Range)ws.Cells[startRow + numRows, startColumn + numColumns];
var range = ws.Range[c1, c2];
if (writeAsString)
{
range.NumberFormat = "@";
}
range.Value = rangeData;
wb.Save();
return(this);
}
/// <summary>
/// instance method, write data to existing worksheet, (WriteDataToExcelWorksheet node)
/// </summary>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="data"></param>
/// <returns></returns>
internal WorkSheet WriteData(int startRow, int startColumn, object[][] data)
{
startRow = Math.Max(0, startRow);
startColumn = Math.Max(0, startColumn);
int numRows, numColumns;
object[,] rangeData = ConvertToDimensionalArray(data, out numRows, out numColumns);
var c1 = (Range)ws.Cells[startRow + 1, startColumn + 1];
var c2 = (Range)ws.Cells[startRow + numRows, startColumn + numColumns];
var range = ws.Range[c1, c2];
range.Value = rangeData;
wb.Save();
return(this);
}
/// <summary>
/// create new worksheet from given workbook and name (AddExcelWorksheetToWorkbook node)
/// </summary>
/// <param name="wbook"></param>
/// <param name="sheetName"></param>
internal WorkSheet(WorkBook wbook, string sheetName)
{
wb = wbook;
WorkSheet wSheet = wbook.WorkSheets.FirstOrDefault(n => n.ws.Name == sheetName);
if (wSheet != null)
{
// Overwrite sheet
DSOffice.ExcelInterop.App.DisplayAlerts = false;
wSheet.ws.Delete();
DSOffice.ExcelInterop.App.DisplayAlerts = true;
}
ws = (Worksheet)wb.Add();
ws.Name = sheetName;
wb.Save();
}
/// <summary>
/// create new worksheet from given workbook and name (AddExcelWorksheetToWorkbook node)
/// </summary>
/// <param name="wbook"></param>
/// <param name="sheetName"></param>
internal WorkSheet(WorkBook wbook, string sheetName)
{
wb = wbook;
// Look for an existing worksheet
WorkSheet wSheet = wbook.WorkSheets.FirstOrDefault(n => n.ws.Name == sheetName);
// If you find one, then use it.
if (wSheet != null)
{
ws = wSheet.ws;
}
// If you don't find one, create one.
else
{
ws = (Worksheet)wb.Add();
ws.Name = sheetName;
wb.Save();
}
}
/// <summary>
/// create new worksheet from given workbook and name (AddExcelWorksheetToWorkbook node)
/// </summary>
/// <param name="wbook"></param>
/// <param name="sheetName"></param>
/// <param name="overWrite"></param>
internal WorkSheet(WorkBook wbook, string sheetName, bool overWrite = false)
{
wb = wbook;
// Look for an existing worksheet
WorkSheet[] worksheets = wbook.WorkSheets;
WorkSheet wSheet = worksheets.FirstOrDefault(n => n.ws.Name == sheetName);
if (wSheet == null)
{
// If you don't find one, create one.
ws = (Worksheet) wb.Add();
ws.Name = sheetName;
wb.Save();
return;
}
// If you find one, then use it.
if (overWrite)
{
// if there is only one worksheet, we need to add one more
// before we can delete the first one
ws = (Worksheet) wb.Add();
wSheet.ws.Delete();
ws.Name = sheetName;
wb.Save();
}
else
ws = wSheet.ws;
}
/// <summary>
/// create new worksheet from given workbook and name (AddExcelWorksheetToWorkbook node)
/// </summary>
/// <param name="wbook"></param>
/// <param name="sheetName"></param>
internal WorkSheet(WorkBook wbook, string sheetName)
{
wb = wbook;
ws = (Worksheet)wb.Add();
ws.Name = sheetName;
wb.Save();
}
/// <summary>
/// create new worksheet from given workbook and name (AddExcelWorksheetToWorkbook node)
/// </summary>
/// <param name="wbook"></param>
/// <param name="sheetName"></param>
internal WorkSheet (WorkBook wbook, string sheetName)
{
wb = wbook;
WorkSheet wSheet = wbook.WorkSheets.FirstOrDefault(n => n.ws.Name == sheetName);
if (wSheet != null)
{
// Overwrite sheet
DSOffice.ExcelInterop.App.DisplayAlerts = false;
wSheet.ws.Delete();
DSOffice.ExcelInterop.App.DisplayAlerts = true;
}
ws = (Worksheet)wb.Add();
ws.Name = sheetName;
wb.Save();
}
/// <summary>
/// create new worksheet from given workbook and name (AddExcelWorksheetToWorkbook node)
/// </summary>
/// <param name="wbook"></param>
/// <param name="sheetName"></param>
internal WorkSheet (WorkBook wbook, string sheetName)
{
wb = wbook;
// Look for an existing worksheet
WorkSheet wSheet = wbook.WorkSheets.FirstOrDefault(n => n.ws.Name == sheetName);
// If you find one, then use it.
if (wSheet != null)
{
ws = wSheet.ws;
}
// If you don't find one, create one.
else
{
ws = (Worksheet)wb.Add();
ws.Name = sheetName;
wb.Save();
}
}