private void SaveExcelSheet(string path)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Initialize Application
IApplication application = excelEngine.Excel;
//Set the default application version as Excel 2016
application.DefaultVersion = ExcelVersion.Excel2016;
//Create a new workbook
IWorkbook workbook = application.Workbooks.Create(1);
//Access first worksheet from the workbook instance
IWorksheet worksheet = workbook.Worksheets[0];
//Exporting DataTable to worksheet
DataGridView dataTable = (dataGridView1);
worksheet.ImportDataGridView(dataTable, 1, 1, true, false);
worksheet.UsedRange.AutofitColumns();
//Save the workbook to disk in xlsx format
workbook.SaveAs(path + @"\Saved_Table_" + repositoryName + ".xlsx");
}
}
public static bool ExportExcel(ref DataGridView source, string name = "baocao")
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.FileName = string.Format("report {0}.xlsx", name);
saveFileDialog.InitialDirectory = @"C:\";
saveFileDialog.RestoreDirectory = true;
saveFileDialog.Title = "Save report";
saveFileDialog.CheckPathExists = true;
saveFileDialog.Filter = "xlsx files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
try
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
//Create a workbook with single worksheet
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Import from DataGridView to worksheet
worksheet.ImportDataGridView(source, 1, 1, isImportHeader: true, isImportStyle: true);
worksheet.UsedRange.AutofitColumns();
workbook.SaveAs(saveFileDialog.FileName);
return(true);
}
}
catch (Exception e) { MessageBox.Show($"Đã có lỗi xảy ra khi cố gắng lưu file, xin hãy kiểm tra lại trùng lặp!\nChi tiết lỗi: {e.Message}"); }
}
return(false);
}
public static void DataGridViewToExcel(DataGridView dgv, string fileName)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
//Create a workbook with single worksheet
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Import from DataGridView to worksheet
worksheet.ImportDataGridView(dgv, 1, 1, isImportHeader: true, isImportStyle: true);
worksheet.UsedRange.AutofitColumns();
workbook.SaveAs(fileName);
//Process.Start(fileName);
}
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
//Create a workbook with single worksheet
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Import from DataGridView to worksheet
worksheet.ImportDataGridView(dataGridView1, 1, 1, isImportHeader: true, isImportStyle: true);
worksheet.UsedRange.AutofitColumns();
workbook.SaveAs("Output.xlsx");
System.Diagnostics.Process.Start("Output.xlsx");
}
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
#region Workbook Intialization
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
string fileName = "";
if (this.rdbXls.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "ImportGrid.xls";
}
//Set the Workbook version as Excel 2007-2016
else if (this.rdbXlsx.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "ImportGrid.xlsx";
}
//Create a workbook with single worksheet
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
// Import from DataGridView to worksheet.
worksheet.ImportDataGridView(dataGridView, 1, 1, checkBox1.Checked, checkBox2.Checked);
worksheet.UsedRange.AutofitColumns();
#region Workbook Save
string outputPath = "Output.xlsx";
outputPath = GetFullOutputPath(fileName);
workbook.SaveAs(outputPath);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created document.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
try
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(outputPath)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(outputPath);
#endif
}
catch (Win32Exception ex)
{
MessageBox.Show("Ms Excel is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
#endregion
}
