private void btnBulkUpload_Click(object sender, EventArgs e)
{
var result = openExcelFile.ShowDialog(this);
if (result == DialogResult.OK)
{
if (!string.IsNullOrEmpty(openExcelFile.FileName))
{
try
{
using (var excelWorkbook = new Library.ExcelDataTable(openExcelFile.FileName))
{
var sheet = excelWorkbook.Workbook.Worksheets[TypeData.Label];
if (sheet != null)
{
var importer = new ExcelImport(sheet, TypeData);
var importResult = importer.ShowDialog(this);
if (importResult == DialogResult.OK)
{
LoadDataRows();
}
}
else
{
MessageBox.Show(this, "Could not find sheet with name: " + TypeData.Label, "Validation Error - Data import Failure", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception err)
{
MessageBox.Show(this, "Error Loading workbook , please ensure it is not open in Excel.\n Error Info:" + err.Message, "Workbook error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
private void btnExportAsNew_Click(object sender, EventArgs e)
{
var result = saveExcelFile.ShowDialog(this);
if (result == DialogResult.OK)
{
if (!string.IsNullOrEmpty(saveExcelFile.FileName))
{
try
{
using (var excelWorkbook = new Library.ExcelDataTable())
{
var sheet = excelWorkbook.Workbook.Worksheets[TypeData.Label];
if (sheet == null)
{
excelWorkbook.Workbook.Worksheets.Add(TypeData.Label);
sheet = excelWorkbook.Workbook.Worksheets[TypeData.Label];
var data = TypeData.GetWorksheetData();
int rowindex = 1;
foreach (var row in data)
{
int columnindex = 1;
foreach (var value in row)
{
sheet.SetValue(rowindex, columnindex, value);
columnindex++;
}
rowindex++;
}
excelWorkbook.SaveAs(saveExcelFile.FileName);
}
}
}
catch (Exception err)
{
MessageBox.Show(this, "Error Loading workbook , please ensure it is not open in Excel.\n Error Info:" + err.Message, "Workbook error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
private void btnUploadToGSheets_Click(object sender, EventArgs e)
{
var result = openExcelFile.ShowDialog(this);
if (result == DialogResult.OK)
{
if (!string.IsNullOrEmpty(openExcelFile.FileName))
{
try
{
using (var excelWorkbook = new Library.ExcelDataTable(openExcelFile.FileName))
{
var sheet = excelWorkbook.Workbook.Worksheets[TypeData.Label];
if (sheet != null)
{
var confirmresult = MessageBox.Show(this, "Found an existing sheet named " + TypeData.Label + ", all data will be cleared, \ndo you wish to proceed ?", "confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (confirmresult == DialogResult.Yes)
{
excelWorkbook.ClearSheetData(sheet);
excelWorkbook.SavePackage();
}
var data = TypeData.GetWorksheetData();
int rowindex = 1;
foreach (var row in data)
{
int columnindex = 1;
foreach (var value in row)
{
sheet.SetValue(rowindex, columnindex, value);
columnindex++;
}
rowindex++;
}
excelWorkbook.SavePackage();
}
else
{
excelWorkbook.Workbook.Worksheets.Add(TypeData.Label);
sheet = excelWorkbook.Workbook.Worksheets[TypeData.Label];
var data = TypeData.GetWorksheetData();
int rowindex = 1;
foreach (var row in data)
{
int columnindex = 1;
foreach (var value in row)
{
sheet.SetValue(rowindex, columnindex, value);
columnindex++;
}
rowindex++;
}
excelWorkbook.SavePackage();
}
}
}
catch (Exception err)
{
MessageBox.Show(this, "Error Loading workbook , please ensure it is not open in Excel.\n Error Info:" + err.Message, "Workbook error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}