private void ExportToExcell()
{
ExcellSctructure excelStructure = new ExcellSctructure();
if (dataGridView1.Rows.Count > 0)
{
excelStructure.SetElements();
copyAlltoClipboard();
for (int x = 1; x < dataGridView1.Columns.Count + 1; x++)
{
excelStructure.XlWorkSheet.Cells[1, x] = dataGridView1.Columns[x - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
excelStructure.XlWorkSheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
var saveFileDialoge = new SaveFileDialog();
saveFileDialoge.FileName = String.Concat("Result", "Excell import for");
saveFileDialoge.DefaultExt = ".xlsx";
if (saveFileDialoge.ShowDialog() == DialogResult.OK)
{
excelStructure.XlWorkSheet.SaveAs(saveFileDialoge.FileName, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
excelStructure.XlWorkBook.Close(true, excelStructure.MisValue, excelStructure.MisValue);
excelStructure.XlExcel.Quit();
releaseObject(excelStructure.XlWorkSheet);
releaseObject(excelStructure.XlWorkBook);
releaseObject(excelStructure.XlExcel);
}
}
private void ImportFromExcell()
{
Microsoft.Office.Interop.Excel.Range excellRange;
ExcellSctructure excelStructure = new ExcellSctructure();
OpenFileDialog excellImport = new OpenFileDialog();
DataTable dTable = new DataTable("dataTableExcell");
DataSet dsSource = new DataSet("dataSetExcell");
dTable.Reset();
try
{
Employees.Clear();
excelStructure.SetElements();
excellImport.Title = "Select file excell";
excellImport.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
excellImport.FilterIndex = 1;
excellImport.RestoreDirectory = true;
if (excellImport.ShowDialog() == DialogResult.OK)
{
excelStructure.XlWorkBook = excelStructure.XlExcel.Workbooks.Open(excellImport.FileName, excelStructure.MisValue, excelStructure.MisValue,
excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue,
excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue);
excelStructure.XlWorkSheet = excelStructure.XlWorkBook.Sheets.get_Item(1);
excellRange = excelStructure.XlWorkSheet.UsedRange;
var employeeProperties = typeof(Employee).GetProperties();
var fiedCountEmployee = employeeProperties.Length;
var columnCountExcell = excellRange.Columns.Count;
List <string> columnList = new List <string>();
for (int columnNum = 1; columnNum <= excellRange.Columns.Count; columnNum++)
{
dTable.Columns.Add(new DataColumn((excellRange.Cells[1, columnNum] as Microsoft.Office.Interop.Excel.Range).Value2.ToString()));
}
dTable.AcceptChanges();
for (int i = 0; i < dTable.Columns.Count; i++)
{
columnList.Add(dTable.Columns[i].ColumnName);
}
//Check format
try
{
bool wrongFormat = false;
wrongFormat = fiedCountEmployee != columnCountExcell;
foreach (var item in columnList)
{
if (employeeProperties.FirstOrDefault(zx => zx.Name == item) == null)
{
wrongFormat = false;
break;
}
}
if (wrongFormat)
{
throw new Exception("We have the wrong format of a file");
}
;
}
catch
{
throw;
}
for (int rowNumber = 2; rowNumber <= excellRange.Rows.Count; rowNumber++)
{
DataRow dRow = dTable.NewRow();
for (int columnNumber = 1; columnNumber <= excellRange.Columns.Count; columnNumber++)
{
if ((excellRange.Cells[rowNumber, columnNumber] as Microsoft.Office.Interop.Excel.Range).Value2 != null)
{
dRow[columnNumber - 1] = (excellRange.Cells[rowNumber, columnNumber] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
}
}
dTable.Rows.Add(dRow);
dTable.AcceptChanges();
}
excelStructure.XlWorkBook.Close(true, excelStructure.MisValue, excelStructure.MisValue);
excelStructure.XlExcel.Quit();
for (int i = 0; i < dTable.Rows.Count; i++)
{
var tableRow = dTable.Rows[i];
FixedTimeEmployee fixT = new FixedTimeEmployee();
for (int j = 0; j < tableRow.ItemArray.Length; j++)
{
var currentValue = tableRow.ItemArray[j];
var columnNAme = columnList[j];
if (employeeProperties.FirstOrDefault(zx => zx.Name == columnNAme).GetValue(fixT) is decimal)
{
employeeProperties.FirstOrDefault(zx => zx.Name == columnNAme).SetValue(fixT, Decimal.Parse(currentValue.ToString()));
}
else
{
employeeProperties.FirstOrDefault(zx => zx.Name == columnNAme).SetValue(fixT, currentValue);
}
}
Employees.Add(fixT);
}
}
else
{
releaseObject(excellImport);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
releaseObject(excelStructure.XlWorkSheet);
releaseObject(excelStructure.XlWorkBook);
releaseObject(excelStructure.XlExcel);
releaseObject(excellImport);
}
}