/// <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>
/// Read data from a Microsoft Excel spreadsheet. Data is read by row and
/// returned in a series of lists by row. Rows and columns are zero-indexed;
/// for example, the value in cell A1 will appear in the data list at [0,0].
/// This node requires Microsoft Excel to be installed.
/// </summary>
/// <param name="filePath">File path to the Microsoft Excel spreadsheet.</param>
/// <param name="sheetName">Name of the worksheet containing the data.</param>
/// <returns name="data">Rows of data from the Excel worksheet.</returns>
/// <search>office,excel,spreadsheet</search>
public static object[][] Read(string filePath, string sheetName)
{
WorkBook wb = WorkBook.ReadExcelFile(filePath);
WorkSheet ws = wb.GetWorksheetByName(sheetName);
return(ws.Data);
}
public static object[][] ReadFromFile(FileInfo file, string sheetName, bool readAsStrings = false, bool showExcel = true)
{
object[][] data;
if (!showExcel)
{
ExcelInterop.ShowOnStartup = false;
}
WorkBook wb = WorkBook.ReadExcelFile(file.FullName);
WorkSheet ws = wb.GetWorksheetByName(sheetName);
if (readAsStrings)
{
data = ws.GetData(true);
}
else
{
data = ws.Data;
}
if (!showExcel)
{
wb.CloseHidden();
ExcelInterop.ShowOnStartup = true;
}
return(data);
}
/// <summary>
/// Read data from a Microsoft Excel spreadsheet. Data is read by row and
/// returned in a series of lists by row. Rows and columns are zero-indexed;
/// for example, the value in cell A1 will appear in the data list at [0,0].
/// This node requires Microsoft Excel to be installed.
/// </summary>
/// <param name="file">File representing the Microsoft Excel spreadsheet.</param>
/// <param name="sheetName">Name of the worksheet containing the data.</param>
/// <returns name="data">Rows of data from the Excel worksheet.</returns>
/// <search>office,excel,spreadsheet</search>
public static object[][] ReadFromFile(FileInfo file, string sheetName)
{
WorkBook wb = WorkBook.ReadExcelFile(file.FullName);
WorkSheet ws = wb.GetWorksheetByName(sheetName);
return(ws.Data);
}
/// <summary>
/// Write data to a Microsoft Excel spreadsheet. Data is written by row
/// with sublists to be written in successive rows. Rows and columns are
/// zero-indexed; for example, the value in the data list at [0,0] will
/// be written to cell A1. This node requires Microsoft Excel to be
/// installed.
/// </summary>
/// <param name="filePath">File path to the Microsoft Excel spreadsheet.</param>
/// <param name="sheetName">Name of the workseet to write data to.</param>
/// <param name="startRow">Start row for writing data. Enter 0 for A, 1 for B, etc.</param>
/// <param name="startCol">
/// Start column for writing data. Enter 0 for col 1, 1 for column 2, ect.
/// </param>
/// <param name="data">Data to write to the spreadsheet.</param>
/// <returns name="data">Data written to the spreadsheet.</returns>
/// <search>office,excel,spreadsheet</search>
public static object[][] WriteToFile(string filePath, string sheetName, int startRow, int startCol, object[][] data)
{
WorkBook wb = new WorkBook(filePath);
WorkSheet ws = new WorkSheet(wb, sheetName);
ws = ws.WriteData(startRow, startCol, data);
return(ws.Data);
}
internal static object[][] WriteData(string filePath, string sheetName, int startRow, int startCol, object[][] data, bool overWrite = false, bool writeAsString = false)
{
WorkBook wb = new WorkBook(filePath);
WorkSheet ws = new WorkSheet(wb, sheetName, overWrite);
ws = ws.WriteData(startRow, startCol, data, writeAsString);
return(ws.Data);
}
public static WorkSheet WriteDataToExcelWorksheet(
WorkSheet worksheet, int startRow = 0, int startColumn = 0, object[][] data = null)
{
if (data == null)
{
return(worksheet);
}
else
{
return(worksheet.WriteData(startRow, startColumn, data));
}
}
/// <summary>
/// Read data from a Microsoft Excel spreadsheet. Data is read by row and
/// returned in a series of lists by row. Rows and columns are zero-indexed;
/// for example, the value in cell A1 will appear in the data list at [0,0].
/// This node requires Microsoft Excel to be installed.
/// </summary>
/// <param name="file">File representing the Microsoft Excel spreadsheet.</param>
/// <param name="sheetName">Name of the worksheet containing the data.</param>
/// <param name="readAsStrings">toggle to switch between reading Excel file as strings only or not</param>
/// <returns name="data">Rows of data from the Excel worksheet.</returns>
/// <search>office,excel,spreadsheet,ifequalreturnindex</search>
public static object[][] ReadFromFile(FileInfo file, string sheetName, bool readAsStrings = false)
{
WorkBook wb = WorkBook.ReadExcelFile(file.FullName);
WorkSheet ws = wb.GetWorksheetByName(sheetName);
if (readAsStrings)
{
return(ws.GetData(true));
}
return(ws.Data);
}
/// <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();
}
}
public static object[][] GetDataFromExcelWorksheet(WorkSheet worksheet)
{
return(worksheet.Data);
}
/// <summary>
/// Write data to a Microsoft Excel spreadsheet. Data is written by row
/// with sublists to be written in successive rows. Rows and columns are
/// zero-indexed; for example, the value in the data list at [0,0] will
/// be written to cell A1. Null values and empty lists are written to Excel
/// as empty cells. This node requires Microsoft Excel to be installed.
/// </summary>
/// <param name="filePath">File path to the Microsoft Excel spreadsheet.</param>
/// <param name="sheetName">Name of the workseet to write data to.</param>
/// <param name="startRow">Start row for writing data. Enter 0 for A, 1 for B, etc.</param>
/// <param name="startCol">
/// Start column for writing data. Enter 0 for col 1, 1 for column 2, ect.
/// </param>
/// <param name="data">Data to write to the spreadsheet.</param>
/// <param name="overWrite"></param>
/// <returns name="data">Data written to the spreadsheet.</returns>
/// <search>office,excel,spreadsheet</search>
public static object[][] WriteToFile(string filePath, string sheetName, int startRow, int startCol, object[][] data, bool overWrite = false)
{
WorkBook wb = new WorkBook(filePath);
WorkSheet ws = new WorkSheet(wb, sheetName, overWrite);
ws = ws.WriteData(startRow, startCol, data);
return ws.Data;
}
public static WorkSheet WriteDataToExcelWorksheet(
WorkSheet worksheet, int startRow = 0, int startColumn = 0, object[][] data = null)
{
if (data == null)
return worksheet;
else
return worksheet.WriteData(startRow, startColumn, data);
}
public static object[][] GetDataFromExcelWorksheet(WorkSheet worksheet)
{
return worksheet.Data;
}