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()); }