static void Main(string[] args) { string connection = @"Data Source=LAPTOP-N1RJNKED\SQLEXPRESS;Initial Catalog=RedactionDummy;Integrated Security=True"; //string query = "SELECT TOP 100 [EmpData].first_name, [EmpData].last_name, [EmpData].company_name, [EmpData].city, [EmpData].phone1, [EmpData].email FROM EmpData"; string query = "SELECT * FROM Table_1"; string excelFilePath = "Chart.xlsx"; string sheetName = "Tabelle1"; string columnIndex = "B"; uint rowIndex = 2; //var result = ExcelEditorServices.InsertPlainData(connection, query, excelFilePath, sheetName, columnIndex, rowIndex, false); //Console.WriteLine("Result: {0}", result.ToString()); //var result2 = ExcelEditorServices.GetTextBoxes(excelFilePath, sheetName); //var result2 = ExcelEditorServices.GetTextBoxes(excelFilePath, sheetName); using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFilePath, true)) { var ee = new ExcelEditor(document, sheetName); ee.DateTimeTest(); } Console.WriteLine("Program executed..."); Console.ReadKey(); }
/* * rowIndex and columnName act as the starting point for filling in data with Column headings. * In an excel prefilled with column names, rowIndex and columnName should still point to the first Column heading. * */ public static bool InsertPlainData(string connection, string query, string excelFilePath, string sheetName, string columnName, uint rowIndex, bool inlcudeHeadings = true) { bool success = true; try { using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(excelFilePath, true)) { var excelEditor = new ExcelEditor(spreadSheet, sheetName); DataTable dt = excelEditor.GetDataTable(query, connection); excelEditor.ExportDataTable(spreadSheet, columnName, rowIndex, dt, inlcudeHeadings); } } catch (Exception exception) { Debug.WriteLine(exception); success = false; } return(success); }
//No sheet name results in replacing formulas from all sheets public static bool ReplaceFormulasWithValues(string excelFile, string sheetName = "") { bool success = true; try { List <string> sheets; if (string.IsNullOrEmpty(sheetName)) { sheets = GetWorksheetNames(excelFile); } else { sheets = new List <string>() { sheetName } }; using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(excelFile, true)) { var excelEditor = new ExcelEditor(spreadSheet, sheetName); foreach (string sheet in sheets) { //prepare instance for each sheet if (true) { excelEditor.ReplaceFormulaWithValue(sheet); } } } } catch (Exception e) { Debug.WriteLine(e.Message); success = false; } return(success); }
public static bool InsertDataTable(DataTable dt, Stream excelFile, string sheetName, string columnName, uint rowIndex, bool inlcudeHeadings = true) { bool success = true; try { using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(excelFile, true)) { var excelEditor = new ExcelEditor(spreadSheet, sheetName); if (excelEditor.SheetPrepared) { excelEditor.ExportDataTable(spreadSheet, columnName, rowIndex, dt, inlcudeHeadings); } } } catch (Exception e) { Debug.WriteLine(e.Message); success = false; } return(success); }