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