//
// GET: /RowColumnManipulation/
public ActionResult RowColumnManipulation(string Saveoption, string button)
{
if (Saveoption == null)
{
return(View());
}
else if (button == "Input Template")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"monthly_sales.xlsx"));
return(excelEngine.SaveAsActionResult(workbook, "Template.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft 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;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook;
if (Saveoption == "Xlsx")
{
application.DefaultVersion = ExcelVersion.Excel2016;
workbook = application.Workbooks.Open(ResolveApplicationDataPath("monthly_sales.xlsx"));
}
else
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath("monthly_sales.xls"));
}
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#region Grouping and ungrouping
// Grouping by Rows
sheet.Range["C5:F7"].Group(ExcelGroupBy.ByRows);
// Grouping by Columns
sheet.Range["C10:F10"].Group(ExcelGroupBy.ByColumns);
#endregion
#region Hiding unhiding
// Hiding fifth and sixth Column
sheet.ShowColumn(5, false);
sheet.ShowColumn(6, false);
//Showing the 28th row
sheet.ShowRow(28, true);
#endregion
#region Insert and delete
//Deleting Row
sheet.DeleteRow(25);
//Inserting Column
sheet.InsertColumn(7, 1, ExcelInsertOptions.FormatAsBefore);
sheet.Range["G4"].Text = "Loss/Gain";
//Deleting Column
sheet.DeleteColumn(8);
#endregion
#region ColumnWidth and RowHeight
// Changing the Column Width
sheet.Range["D5"].ColumnWidth = 15;
// Changing the Row Height
sheet.Range["D29"].RowHeight = 25;
#endregion
try
{
// Save the file
if (Saveoption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "RowColumnManipulation.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "RowColumnManipulation.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
#region Workbook Initialization
//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;
//Get file path of input file
string inputPath = GetFullTemplatePath("monthly_sales.xls");
//Open an existing spreadsheet which will be used as a template for creating new worksheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
//IWorkbook workbook = application.Workbooks.Open(inputPath);
IWorkbook workbook = application.Workbooks.Open(inputPath);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Row and Column Manipulations
#region Grouping and ungrouping
// Grouping by Rows
worksheet.Range["C5:F7"].Group(ExcelGroupBy.ByRows);
// Grouping by Columns
worksheet.Range["C10:F10"].Group(ExcelGroupBy.ByColumns);
#endregion
#region Hiding unhiding
// Hiding fifth and sixth Column
worksheet.ShowColumn(5, false);
worksheet.ShowColumn(6, false);
//Showing the 28th row
worksheet.ShowRow(28, true);
#endregion
#region Insert and delete
//Deleting Row
worksheet.DeleteRow(25);
//Inserting Column
worksheet.InsertColumn(7, 1, ExcelInsertOptions.FormatAsBefore);
worksheet.Range["G4"].Text = "Loss/Gain";
//Deleting Column
worksheet.DeleteColumn(8);
#endregion
#region ColumnWidth and RowHeight
// Changing the Column Width
worksheet.Range["D5"].ColumnWidth = 15;
// Changing the Row Height
worksheet.Range["D29"].RowHeight = 25;
#endregion
#endregion
#region Workbook Save
//Set the default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
workbook.Version = ExcelVersion.Excel97to2003;
fileName = "Sample.xls";
}
//Set the default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
workbook.Version = ExcelVersion.Excel2007;
fileName = "Sample.xlsx";
}
//Set the default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
workbook.Version = ExcelVersion.Excel2010;
fileName = "Sample.xlsx";
}
//Set the default version as Excel 2010
else if (this.rdbExcel2013.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
fileName = "Sample.xlsx";
}
//Saving the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//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(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
