internal void OnImportClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
// Initializing Workbook
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = null;
#if COMMONSB
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.ExportData.xlsx");
#else
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.ExportData.xlsx");
#endif
IWorkbook workbook = application.Workbooks.Open(fileStream);
workbook.Version = ExcelVersion.Excel2013;
IWorksheet sheet = workbook.Worksheets[0];
Dictionary <string, string> mappingProperties = new Dictionary <string, string>();
mappingProperties.Add("Brand", "BrandName");
mappingProperties.Add("Vehicle Type", "VehicleType.VehicleName");
mappingProperties.Add("Model", "VehicleType.Model.ModelName");
List <Brand> clrObjects = sheet.ExportData <Brand>(4, 1, 141, 3, mappingProperties);
dataGrid.ItemsSource = clrObjects;
this.btnGenerate.IsEnabled = true;
workbook.Close();
excelEngine.Dispose();
}
internal void OnImportClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
// Initializing Workbook
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = null;
#if COMMONSB
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.ExportSales.xlsx");
#else
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.ExportSales.xlsx");
#endif
IWorkbook workbook = application.Workbooks.Open(fileStream);
workbook.Version = ExcelVersion.Excel2013;
IWorksheet sheet = workbook.Worksheets[0];
List <CustomerObject> clrObjects = sheet.ExportData <CustomerObject>(1, 1, 41, 4);
dataGrid.ItemsSource = clrObjects;
this.btnGenerate.IsEnabled = true;
workbook.Close();
excelEngine.Dispose();
}
void ButtonImportClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
string resourcePath = "";
resourcePath = "SampleBrowser.Samples.XlsIO.Template.ExportData.xlsx";
Assembly assembly = Assembly.GetExecutingAssembly();
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = application.Workbooks.Open(fileStream);
IWorksheet sheet = workbook.Worksheets[0];
workbook.Version = ExcelVersion.Excel2013;
Dictionary <string, string> mappingProperties = new Dictionary <string, string>();
mappingProperties.Add("Brand", "BrandName");
mappingProperties.Add("Vehicle Type", "VehicleType.VehicleName");
mappingProperties.Add("Model", "VehicleType.Model.ModelName");
List <Brand> CLRObjects = sheet.ExportData <Brand>(4, 1, 141, 3, mappingProperties);
sfGrid.ItemsSource = CLRObjects;
btnExport.Enabled = true;
}
private void Jsonise_Excel()
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
//The workbook is opened.
using (FileStream fileStream = new FileStream(Server.MapPath("~/Uploaded/PTR.xlsx"), FileMode.Open))
{
IWorkbook workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//Export worksheet data into CLR Objects
IList <Transaction> transactions = worksheet.ExportData <Transaction>(1, 1, worksheet.UsedRange.LastRow, workbook.Worksheets[0].UsedRange.LastColumn);
//open file stream
using (StreamWriter file = File.CreateText(Server.MapPath("~/Uploaded/PTR.json")))
{
JsonSerializer serializer = new JsonSerializer();
//serialize object directly into file stream
serializer.Serialize(file, transactions);
}
}
}
}
public List <PhoneNumbers> GetNumbers(string colXlName, string mappedColName,
int firstRow, int firstCol, int lastRow, int lastCol)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream inputStream = assembly.GetManifestResourceStream("eServiceApp.Files.Number.xlsx");
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet worksheet = workbook.Worksheets[0];
Dictionary <string, string> mappingProperties = new Dictionary <string, string>();
mappingProperties.Add(colXlName, mappedColName);
List <PhoneNumbers> listOfNumbers = new List <PhoneNumbers>();
listOfNumbers = worksheet.ExportData <PhoneNumbers>(firstRow, firstCol, lastRow, lastCol, mappingProperties);
return(listOfNumbers);
}
static void Main(string[] args)
{
try
{
//Instantiate the spreadsheet creation engine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
//The workbook is opened.
FileStream fileStream = new FileStream("../../Data/Config Keys_Names V2.xlsx", FileMode.Open);
IWorkbook workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//Export worksheet data into CLR Objects
IList <ConfigKeyData> customers = worksheet.ExportData <ConfigKeyData>(1, 1, worksheet.UsedRange.LastRow, workbook.Worksheets[0].UsedRange.LastColumn);
//open file stream
using (StreamWriter file = File.CreateText("../../Output/data.json"))
{
JsonSerializer serializer = new JsonSerializer();
//serialize object directly into file stream
serializer.Serialize(file, customers);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
private async void btnImportData_Click(object sender, RoutedEventArgs 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.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
// For Export Data
Assembly assembly = typeof(TemplateMarker).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.ExportData.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
Dictionary <string, string> mappingProperties = new Dictionary <string, string>();
mappingProperties.Add("Brand", "BrandName");
mappingProperties.Add("Vehicle Type", "VehicleType.VehicleName");
mappingProperties.Add("Model", "VehicleType.Model.ModelName");
List <Brand> CLRObjects = worksheet.ExportData <Brand>(4, 1, 141, 3, mappingProperties);
this.grdViewExport.ItemsSource = CLRObjects;
btnExportData.IsEnabled = true;
#endregion
}
public IEnumerable <DeviceForApproval> GetDevices()
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream inputStream = assembly.GetManifestResourceStream("eServiceApp.Files.Equipment2.xls");
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet worksheet = workbook.Worksheets[0];
Dictionary <string, string> mappingProperties = new Dictionary <string, string>();
mappingProperties.Add("LicenceNo", "LicenseNumber");
mappingProperties.Add("Manufucturer", "Manufucturer");
mappingProperties.Add("Model", "Model");
mappingProperties.Add("DeviceType", "DeviceType");
List <DeviceForApproval> deviceForApprovals = new List <DeviceForApproval>();
deviceForApprovals = worksheet.ExportData <DeviceForApproval>(1, 1, 419, 4, mappingProperties);
return(deviceForApprovals);
}
private void button1_Click(object sender, EventArgs e)
{
//Imports Data from the Template spreadsheet into the Grid.
#region Workbook Initialize
//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 path of the Input file
string inputPath = GetTemplatePath("ExportData.xlsx");
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook = application.Workbooks.Open(inputPath);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Export CLR Object from Workbook
//Read data from spreadsheet.
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCells;
Dictionary <string, string> mappingProperties = new Dictionary <string, string>();
mappingProperties.Add("Brand", "BrandName");
mappingProperties.Add("Vehicle Type", "VehicleType.VehicleName");
mappingProperties.Add("Model", "VehicleType.Model.ModelName");
List <Brand> CLRObjects = worksheet.ExportData <Brand>(4, 1, 141, 3, mappingProperties);
this.dataGridView1.DataSource = CLRObjects;
btnExport.Enabled = true;
#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
}
public string dp(String caminhoNomeSalvar)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
FileStream fileStream = new FileStream($@"{caminhoNomeSalvar}", FileMode.Open);
IWorkbook workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[2];
IList <modelopessoa> pessoa = worksheet.ExportData <modelopessoa>(1, 1, worksheet.UsedRange.LastRow, workbook.Worksheets[2].UsedRange.LastColumn);
using (StreamWriter file = File.CreateText(@"F:\pessoa.json"))
{
JsonSerializer serializer = new JsonSerializer();
serializer.Serialize(file, pessoa);
}
}
return(@"F:\pessoa.json");
}
public IList <dadosmodels> dt(string caminhoNomeSalvar)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
var conteudo = Convert.FromBase64String(caminhoNomeSalvar);
MemoryStream stream = new MemoryStream(conteudo);
IApplication application = excelEngine.Excel;
//FileStream fileStream = new FileStream($@"{caminhoNomeSalvar}", FileMode.Open);
IWorkbook workbook = application.Workbooks.Open(stream, ExcelOpenType.Automatic);
stream.Dispose();
IWorksheet worksheet = workbook.Worksheets[0];
IList <dadosmodels> automovels = worksheet.ExportData <dadosmodels>(1, 1, worksheet.UsedRange.LastRow, workbook.Worksheets[0].UsedRange.LastColumn);
//using (StreamWriter file = File.CreateText(@"C:\Users\SNIR_DEV00\Documents\internacional-seguros\ISWebApp.GraphQL\Excel\automovel.json"))
//{
// JsonSerializer serializer = new JsonSerializer();
// serializer.Serialize(file, automovels);
//}
return(automovels);
}
}
/// <summary>
/// Gets the data from Excel for mail merge
/// </summary>
/// <returns></returns>
private static MailMergeDataTable GetData()
{
//Creates new excel engine
ExcelEngine excelEngine = new ExcelEngine();
//Creates new excel application
IApplication application = excelEngine.Excel;
//Opens the excel to extract data for mail merge
Stream excelStream = File.OpenRead(Path.GetFullPath(@"../../../StockDetails.xlsx"));
IWorkbook workbook = application.Workbooks.Open(excelStream);
excelStream.Dispose();
//Exports data from worksheet to .NET objects
IWorksheet sheet = workbook.Worksheets[0];
List <StockDetail> stockDetails = sheet.ExportData <StockDetail>(1, 1, 31, 5);
workbook.Close();
excelEngine.Dispose();
return(new MailMergeDataTable("StockDetails", stockDetails));
}
void ButtonImportClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
string resourcePath = "";
resourcePath = "SampleBrowser.Samples.XlsIO.Template.ExportSales.xlsx";
Assembly assembly = Assembly.GetExecutingAssembly();
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = application.Workbooks.Open(fileStream);
IWorksheet sheet = workbook.Worksheets[0];
workbook.Version = ExcelVersion.Excel2013;
List <CustomerObject> businessObjects = sheet.ExportData <CustomerObject>(1, 1, 41, 4);
sfGrid.ItemsSource = businessObjects;
btnExport.Enabled = true;
}
//For Session
//public HttpSessionStateBase Session { get; }
public ActionResult CLRObjects(string saveOption, string button)
{
string basePath = _hostingEnvironment.WebRootPath;
ViewBag.exportButtonState = "disabled=\"disabled\"";
///SaveOption Null
if (saveOption == null || button == null)
{
return(View());
}
//Start CLR Object Functions
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;
FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportSales.xlsx", FileMode.Open, FileAccess.Read);
// Opening the Existing Worksheet from a Workbook.
IWorkbook workbook = application.Workbooks.Open(inputStream);
try
{
string ContentType = null;
string fileName = null;
if (saveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "ExportSales.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ExportSales.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
}
else if (button == "Import From Excel")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportSales.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet sheet = workbook.Worksheets[0];
//Export Bussiness Objects
List <Sales> CLRObjects = sheet.ExportData <Sales>(1, 1, 41, 4);
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
int temp = 1;
foreach (Sales sale in CLRObjects)
{
sale.ID = temp;
temp++;
}
//Set the grid value to the Session
_sales = CLRObjects;
ViewBag.DataSource = _sales;
ViewBag.exportButtonState = "";
return(View());
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (saveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook;
workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Import Bussiness Object to worksheet
sheet.ImportData(_sales, 5, 1, false);
sheet.Range["E4"].Text = "";
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = Color.FromArgb(0, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.White;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = Color.FromArgb(0, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:E1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A2:E2"].Merge();
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A3:A4"].Merge();
sheet["B3:B4"].Merge();
sheet["E3:E4"].Merge();
sheet["C3:D3"].Merge();
sheet["C3"].Text = "Sales";
sheet["A3:E4"].CellStyle = tableHeader;
sheet["A3"].Text = "S.ID";
sheet["B3"].Text = "Sales Person";
sheet["C4"].Text = "January - June";
sheet["D4"].Text = "July - December";
sheet["E3"].Text = "Change(%)";
sheet.UsedRange.AutofitColumns();
sheet.Columns[0].ColumnWidth = 10;
sheet.Columns[1].ColumnWidth = 24;
sheet.Columns[2].ColumnWidth = 21;
sheet.Columns[3].ColumnWidth = 21;
sheet.Columns[4].ColumnWidth = 16;
#endregion
try
{
string ContentType = null;
string fileName = null;
if (saveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "ExportSales.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ExportSales.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
public ActionResult BusinessObjects(string Saveoption, string button)
{
ViewBag.exportButtonState = "disabled=\"disabled\"";
//Check FileName
string fileName = "ExportSales.xlsx";
///SaveOption Null
if (Saveoption == null || button == null)
{
_sales = new List <Sales>();
return(View());
}
//Start Business Object Functions
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(fileName));
return(excelEngine.SaveAsActionResult(workbook, fileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else if (button == "Import From Excel")
{
//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(fileName));
IWorksheet sheet = workbook.Worksheets[0];
//Export Bussiness Objects
List <Sales> businessObjects = sheet.ExportData <Sales>(1, 1, 41, 4);
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
int temp = 1;
foreach (Sales sale in businessObjects)
{
sale.ID = temp;
temp++;
}
//Set the grid value to the Session
_sales = businessObjects;
ViewBag.DataSource = _sales;
ViewBag.exportButtonState = "";
button = null;
return(View());
//return new CustomResult(HttpContext.ApplicationInstance.Response);
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (Saveoption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook;
workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Import Bussiness Object to worksheet
sheet.ImportData(_sales, 5, 1, false);
sheet.Range["E4"].Text = "";
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = Color.FromArgb(0, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.White;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = Color.FromArgb(0, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:E1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A2:E2"].Merge();
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A3:A4"].Merge();
sheet["B3:B4"].Merge();
sheet["E3:E4"].Merge();
sheet["C3:D3"].Merge();
sheet["C3"].Text = "Sales";
sheet["A3:E4"].CellStyle = tableHeader;
sheet["A3"].Text = "S.ID";
sheet["B3"].Text = "Sales Person";
sheet["C4"].Text = "January - June";
sheet["D4"].Text = "July - December";
sheet["E3"].Text = "Change(%)";
sheet.UsedRange.AutofitColumns();
sheet.Columns[0].ColumnWidth = 10;
sheet.Columns[1].ColumnWidth = 24;
sheet.Columns[2].ColumnWidth = 21;
sheet.Columns[3].ColumnWidth = 21;
sheet.Columns[4].ColumnWidth = 16;
#endregion
try
{
//Saving the workbook to disk. This spreadsheet is the result of opening and modifying
//an existing spreadsheet and then saving the result to a new workbook.
if (Saveoption == "Xlsx")
{
return(excelEngine.SaveAsActionResult(workbook, "BusinessObjects.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "BusinessObjects.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
//For Session
//public HttpSessionStateBase Session { get; }
public ActionResult CollectionObjects(string saveOption, string button)
{
string fileName = "ExportData.xlsx";
ViewBag.exportButtonState = "disabled=\"disabled\"";
///SaveOption Null
if (saveOption == null || button == null)
{
_sales = new List <Brand>();
return(View());
}
//Start Business Object Functions
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(fileName));
return(excelEngine.SaveAsActionResult(workbook, fileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else if (button == "Import From Excel")
{
//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(fileName));
IWorksheet sheet = workbook.Worksheets[0];
//Export Bussiness Objects
Dictionary <string, string> mappingProperties = new Dictionary <string, string>();
mappingProperties.Add("Brand", "BrandName");
mappingProperties.Add("Vehicle Type", "VehicleType.VehicleName");
mappingProperties.Add("Model", "VehicleType.Model.ModelName");
List <Brand> businessObjects = sheet.ExportData <Brand>(4, 1, 141, 3, mappingProperties);
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
int temp = 1;
foreach (Brand brand in businessObjects)
{
brand.ID = temp;
temp++;
}
//Set the grid value to the Session
_sales = businessObjects;
ViewBag.DataSource = _sales;
ViewBag.exportButtonState = "";
return(View());
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (saveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook;
workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Import Bussiness Object to worksheet
sheet.ImportData(_sales, 4, 1, true);
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 16;
pageHeader.Font.Bold = true;
pageHeader.Color = Color.FromArgb(0, 146, 208, 80);
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Bold = true;
tableHeader.Font.FontName = "Calibri";
tableHeader.Color = Color.FromArgb(0, 146, 208, 80);
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:C2"].Merge();
sheet["A1"].Text = "Automobile Brands in the US";
sheet["A1"].CellStyle = pageHeader;
sheet["A4:C4"].CellStyle = tableHeader;
sheet["A1:C1"].CellStyle.Font.Bold = true;
sheet.UsedRange.AutofitColumns();
#endregion
try
{
//Saving the workbook to disk. This spreadsheet is the result of opening and modifying
//an existing spreadsheet and then saving the result to a new workbook.
if (saveOption == "Xlsx")
{
return(excelEngine.SaveAsActionResult(workbook, "CollectionObjects.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "CollectionObjects.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
//For Session
//public HttpSessionStateBase Session { get; }
public ActionResult CollectionObjects(string saveOption, string button)
{
string basePath = _hostingEnvironment.WebRootPath;
ViewBag.exportButtonState = "disabled=\"disabled\"";
///SaveOption Null
if (saveOption == null || button == null)
{
_sales = new List <Brand>();
return(View());
}
//Start Business Object Functions
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;
FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportData.xlsx", FileMode.Open, FileAccess.Read);
// Opening the Existing Worksheet from a Workbook.
IWorkbook workbook = application.Workbooks.Open(inputStream);
try
{
string ContentType = null;
string fileName = null;
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/msexcel";
fileName = "ExportData.xlsx";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
}
else if (button == "Import From Excel")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportData.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet sheet = workbook.Worksheets[0];
//Export Bussiness Objects
Dictionary <string, string> mappingProperties = new Dictionary <string, string>();
mappingProperties.Add("Brand", "BrandName");
mappingProperties.Add("Vehicle Type", "VehicleType.VehicleName");
mappingProperties.Add("Model", "VehicleType.Model.ModelName");
List <Brand> businessObjects = sheet.ExportData <Brand>(4, 1, 141, 3, mappingProperties);
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
int temp = 1;
foreach (Brand brand in businessObjects)
{
brand.ID = temp;
temp++;
}
//Set the grid value to the Session
_sales = businessObjects;
ViewBag.DataSource = _sales;
ViewBag.exportButtonState = "";
return(View());
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (saveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook;
workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Import Bussiness Object to worksheet
sheet.ImportData(_sales, 4, 1, true);
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 16;
pageHeader.Font.Bold = true;
pageHeader.Color = Color.FromArgb(0, 146, 208, 80);
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Bold = true;
tableHeader.Font.FontName = "Calibri";
tableHeader.Color = Color.FromArgb(0, 146, 208, 80);
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:C2"].Merge();
sheet["A1"].Text = "Automobile Brands in the US";
sheet["A1"].CellStyle = pageHeader;
sheet["A4:C4"].CellStyle = tableHeader;
sheet["A1:C1"].CellStyle.Font.Bold = true;
sheet.UsedRange.AutofitColumns();
#endregion
try
{
string ContentType = null;
string fileName = null;
if (saveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "ExportData.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ExportData.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
