/// <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; }