protected void iBtnExcelExport_Click(object sender, ImageClickEventArgs e) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; try { for (int i = 1; i < 6; i++) { sheet.Range[1, i, 2, i].Merge(); sheet.Range[1, i, 2, i].Text = ugrdResultTotal.Columns[i - 1].Header.Caption; sheet.Range[2, i, 2, i].AddComment().Text = ugrdResultTotal.Columns[i - 1].Key; sheet.Range[1, i, 2, i].AutofitColumns(); sheet.Range[1, i, 2, i].CellStyle.ColorIndex = ExcelKnownColors.Blue_grey; sheet.Range[1, i, 2, i].CellStyle.Font.Color = ExcelKnownColors.White; sheet.Range[1, i, 2, i].CellStyle.Font.Bold = true; sheet.Range[1, i, 2, i].ColumnWidth = 8; sheet.Range[1, 1, 2, i].VerticalAlignment = ExcelVAlign.VAlignCenter; sheet.Range[1, 1, 2, i].HorizontalAlignment = ExcelHAlign.HAlignCenter; } for (int i = 16; i < 20; i++) { sheet.Range[1, i, 2, i].Merge(); sheet.Range[1, i, 2, i].Text = ugrdResultTotal.Columns[i + 3].Header.Caption; sheet.Range[2, i, 2, i].AddComment().Text = ugrdResultTotal.Columns[i + 3].Key; sheet.Range[1, i, 2, i].AutofitColumns(); sheet.Range[1, i, 2, i].CellStyle.ColorIndex = ExcelKnownColors.Blue_grey; sheet.Range[1, i, 2, i].CellStyle.Font.Color = ExcelKnownColors.White; sheet.Range[1, i, 2, i].CellStyle.Font.Bold = true; sheet.Range[1, i, 2, i].ColumnWidth = 0; sheet.Range[1, 1, 2, i].VerticalAlignment = ExcelVAlign.VAlignCenter; sheet.Range[1, 1, 2, i].HorizontalAlignment = ExcelHAlign.HAlignCenter; } for (int i = 6; i < 16; i++) { if (i == 6 || i == 8 || i == 10 || i == 12 || i == 14) { sheet.Range[1, i, 1, i + 1].Merge(); if (i == 6) { sheet.Range[1, i, 1, i + 1].Text = "조직"; } else if (i == 8) { sheet.Range[1, i, 1, i + 1].Text = "개인"; } else if (i == 10) { sheet.Range[1, i, 1, i + 1].Text = "목표1"; } else if (i == 12) { sheet.Range[1, i, 1, i + 1].Text = "목표2"; } else if (i == 14) { sheet.Range[1, i, 1, i + 1].Text = "목표3"; } sheet.Range[1, i, 1, i + 1].AutofitColumns(); sheet.Range[1, i, 1, i + 1].CellStyle.ColorIndex = ExcelKnownColors.Blue_grey; sheet.Range[1, i, 1, i + 1].CellStyle.Font.Color = ExcelKnownColors.White; sheet.Range[1, i, 1, i + 1].CellStyle.Font.Bold = true; sheet.Range[1, i, 1, i + 1].VerticalAlignment = ExcelVAlign.VAlignCenter; sheet.Range[1, i, 1, i + 1].HorizontalAlignment = ExcelHAlign.HAlignCenter; } sheet.Range[2, i, 2, i].Text = ugrdResultTotal.Columns[i - 1].Header.Caption; sheet.Range[2, i, 2, i].AddComment().Text = ugrdResultTotal.Columns[i - 1].Key; sheet.Range[2, i, 2, i].AutofitColumns(); sheet.Range[2, i, 2, i].CellStyle.ColorIndex = ExcelKnownColors.Blue_grey; sheet.Range[2, i, 2, i].CellStyle.Font.Color = ExcelKnownColors.White; sheet.Range[2, i, 2, i].CellStyle.Font.Bold = true; sheet.Range[2, i, 2, i].VerticalAlignment = ExcelVAlign.VAlignCenter; sheet.Range[2, i, 2, i].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range[2, i, 2, i].ColumnWidth = 9; } DataTable dataTable = new DataTable(); DataRow dataRow = null; dataTable.Columns.Add("DEPT_NAME", typeof(string)); dataTable.Columns.Add("POS_CLS_NAME", typeof(string)); dataTable.Columns.Add("POS_GRP_NAME", typeof(string)); dataTable.Columns.Add("EMP_CODE", typeof(string)); dataTable.Columns.Add("EMP_NAME", typeof(string)); dataTable.Columns.Add("ORGANIZATION_POINT", typeof(double)); dataTable.Columns.Add("ORGANIZATION_WEIGHT", typeof(int)); dataTable.Columns.Add("APPRAISAL_POINT", typeof(double)); dataTable.Columns.Add("APPRAISAL_WEIGHT", typeof(int)); dataTable.Columns.Add("OTHERS1_POINT", typeof(double)); dataTable.Columns.Add("OTHERS1_WEIGHT", typeof(int)); dataTable.Columns.Add("OTHERS2_POINT", typeof(double)); dataTable.Columns.Add("OTHERS2_WEIGHT", typeof(int)); dataTable.Columns.Add("OTHERS3_POINT", typeof(double)); dataTable.Columns.Add("OTHERS3_WEIGHT", typeof(int)); dataTable.Columns.Add("EMP_REF_ID", typeof(int)); dataTable.Columns.Add("EST_DEPT_REF_ID", typeof(string)); dataTable.Columns.Add("POS_CLS_ID", typeof(string)); dataTable.Columns.Add("POS_GRP_ID", typeof(string)); UltraGridRow row; for (int i = 0; i < ugrdResultTotal.Rows.Count; i++) { row = ugrdResultTotal.Rows[i]; dataRow = dataTable.NewRow(); dataRow["EMP_REF_ID"] = row.Cells.FromKey("EMP_REF_ID").Text; dataRow["EST_DEPT_REF_ID"] = row.Cells.FromKey("EST_DEPT_REF_ID").Text; dataRow["DEPT_NAME"] = row.Cells.FromKey("DEPT_NAME").Text; dataRow["POS_CLS_ID"] = row.Cells.FromKey("POS_CLS_ID").Text; dataRow["POS_CLS_NAME"] = row.Cells.FromKey("POS_CLS_NAME").Text; dataRow["POS_GRP_ID"] = row.Cells.FromKey("POS_GRP_ID").Text; dataRow["POS_GRP_NAME"] = row.Cells.FromKey("POS_GRP_NAME").Text; dataRow["EMP_CODE"] = row.Cells.FromKey("EMP_CODE").Text; dataRow["EMP_NAME"] = row.Cells.FromKey("EMP_NAME").Text; dataRow["ORGANIZATION_POINT"] = row.Cells.FromKey("ORGANIZATION_POINT").Value; dataRow["ORGANIZATION_WEIGHT"] = row.Cells.FromKey("ORGANIZATION_WEIGHT").Value; dataRow["APPRAISAL_POINT"] = row.Cells.FromKey("APPRAISAL_POINT").Value; dataRow["APPRAISAL_WEIGHT"] = row.Cells.FromKey("APPRAISAL_WEIGHT").Value; dataRow["OTHERS1_POINT"] = row.Cells.FromKey("OTHERS1_POINT").Value; dataRow["OTHERS1_WEIGHT"] = row.Cells.FromKey("OTHERS1_WEIGHT").Value; dataRow["OTHERS2_POINT"] = row.Cells.FromKey("OTHERS2_POINT").Value; dataRow["OTHERS2_WEIGHT"] = row.Cells.FromKey("OTHERS2_WEIGHT").Value; dataRow["OTHERS3_POINT"] = row.Cells.FromKey("OTHERS3_POINT").Value; dataRow["OTHERS3_WEIGHT"] = row.Cells.FromKey("OTHERS3_WEIGHT").Value; dataTable.Rows.Add(dataRow); } sheet.ImportDataTable(dataTable, false, 3, 1); sheet = SetGridtoExcelFormat(sheet); string _filename = "BSC_Person_Evaluation_" + ddlEstTermInfo.SelectedItem.Value + "_" + ddlEstTermMonth.SelectedItem.Value + ".xls"; workbook.SaveAs(_filename, ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); } catch (Exception ex) { ltrScript.Text = JSHelper.GetAlertScript("다운로드 중 오류가 발생하였습니다.", false); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); } }
public ActionResult ImportExportDataTable(string saveOption, string button, string importOption) { string basePath = _hostingEnvironment.WebRootPath; ViewBag.exportButtonState = "disabled=\"disabled\""; ///SaveOption Null if (saveOption == null || button == null) { ViewBag.DataSource = null; return(View()); } //Start Business Object Functions if (button == "Input Template") { Stream ms = new FileStream(basePath + @"/XlsIO/NorthwindDataTemplate.xls", FileMode.Open, FileAccess.Read); string ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; string fileName = "NorthwindDataTemplate.xls"; return(File(ms, ContentType, fileName)); } 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; Stream sampleFile = new FileStream(basePath + @"/XlsIO/NorthwindDataTemplate.xls", FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(sampleFile); IWorksheet sheet = workbook.Worksheets[0]; if (importOption == "Skip") { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventSkip; ViewBag.importOptionSkip = "value=" + importOption + " checked = \"checked\""; } else if (importOption == "Replace") { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventReplace; ViewBag.importOptionReplace = "value=" + importOption + " checked = \"checked\""; } else { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventStop; ViewBag.importOptionStop = "value=" + importOption + " checked = \"checked\""; } dataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Close the workbook. workbook.Close(); excelEngine.Dispose(); ViewBag.DataSource = dataTable; 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 data table to worksheet sheet.ImportDataTable(dataTable, true, 1, 1, true); sheet.UsedRange.AutofitColumns(); 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. string ContentType = null; string fileName = null; if (saveOption == "Xlsx") { workbook.Version = ExcelVersion.Excel2013; ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; fileName = "ExportDataTable.xlsx"; } else { ContentType = "Application/vnd.ms-excel"; fileName = "ExportDataTable.xls"; } 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 ImportExportDataTable(string saveOption, string button, string importOption) { string fileName = "NorthwindDataTemplate.xls"; ViewBag.exportButtonState = "disabled=\"disabled\""; ///SaveOption Null if (saveOption == null || button == null) { ViewBag.DataSource = null; 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]; if (importOption == "Skip") { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventSkip; ViewBag.importOptionSkip = "value=" + importOption + " checked = \"checked\""; } else if (importOption == "Replace") { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventReplace; ViewBag.importOptionReplace = "value=" + importOption + " checked = \"checked\""; } else { sheet.ExportDataTableEvent += Sheet_ExportDataTableEventStop; ViewBag.importOptionStop = "value=" + importOption + " checked = \"checked\""; } dataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Close the workbook. workbook.Close(); excelEngine.Dispose(); ViewBag.DataSource = dataTable; 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 data table to worksheet sheet.ImportDataTable(dataTable, true, 1, 1, true); sheet.UsedRange.AutofitColumns(); 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, "ExportDataTable.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } else { return(excelEngine.SaveAsActionResult(workbook, "ExportDataTable.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); } return(View()); }
public void GenerateXls(int id) { #region Initialize Workbook //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; IWorkbook workbook; string inputPath; if (this.excel2003RadioBtn.Checked) { //Indicates the Path of the Input File inputPath = GetFullTemplatePath("Invoice.xls"); workbook = application.Workbooks.Open(inputPath); } else {//Indicates the Path of the Input File inputPath = GetFullTemplatePath("Invoice.xlsx"); workbook = application.Workbooks.Open(inputPath, ExcelOpenType.Automatic); } IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region Insert data worksheet.Range["A5"].Text = "One Portals Way"; worksheet.Range["A6"].Text = "Twin Points WA 98156"; worksheet.Range["A7"].Text = "Phone: 1-206-555-1417 "; worksheet.Range["A8"].Text = "Fax: 1-206-555-5938"; worksheet.Range["D5"].Text = "INVOICE NO:"; worksheet.Range["D6"].Text = "DATE:"; worksheet.Range["D7"].Text = "CUSTOMER ID :"; worksheet.Range["E6"].Formula = "TODAY()"; worksheet.Range["E5"].Number = id; //Set values for Ship To. GetShipDetails(id); worksheet.Range["E7"].Text = shipName; worksheet.Range["E10"].Text = shipName; worksheet.Range["E11"].Text = address; worksheet.Range["E12"].Text = shipCity; worksheet.Range["E13"].Text = shipCountry; //Set values for Bill To. worksheet.Range["B10"].Text = shipName; worksheet.Range["B11"].Text = address; worksheet.Range["B12"].Text = shipCity; worksheet.Range["B13"].Text = shipCountry; //Calculates sub total worksheet.Range["E27"].Formula = "SUM(E20:E26)"; //Set the number format worksheet.Range["E20:E29"].NumberFormat = "$#,##0.00"; worksheet.Range["E28"].Value = freight.ToString(); //Calculates final total worksheet.Range["E29"].Formula = "E27+E28"; //Import the data tables. worksheet.ImportDataTable(GetOrder(id), false, 17, 1); worksheet.ImportDataTable(GetProductDetails(id), false, 20, 1); //Calculates price from discount and unit price. for (int i = 20; i <= 26; i++) { if (worksheet.Range["A" + i.ToString()].Value == "") { break; } else { worksheet.Range["E" + i.ToString()].Formula = "(B" + i.ToString() + "*C" + i.ToString() + ")- (D" + i.ToString() + "/100)"; } } #endregion #region Save the Workbook //Set the Workbook version as Excel 97to2003 if (this.excel2003RadioBtn.Checked) { workbook.Version = ExcelVersion.Excel97to2003; fileName = "InvoiceOutput.xls"; } //Set the Workbook version as Excel 2007 else if (this.excel2007RadioBtn.Checked) { workbook.Version = ExcelVersion.Excel2007; fileName = "InvoiceOutput.xlsx"; } //Set the Workbook version as Excel 2010 else if (this.excel2010RadioBtn.Checked) { workbook.Version = ExcelVersion.Excel2010; fileName = "InvoiceOutput.xlsx"; } //Set the Workbook version as Excel 2010 else if (this.excel2013RadioBtn.Checked) { workbook.Version = ExcelVersion.Excel2013; fileName = "InvoiceOutput.xlsx"; } //Save 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 }
private void btnCreate_Click(object sender, System.EventArgs e) { if (!(int.TryParse(numRowCount.Text, out rowCount) && int.TryParse(numColCount.Text, out colCount))) { MessageBox.Show("Enter Numerical Value"); return; } if (rowCount <= 0) { MessageBox.Show("Invalid row count"); return; } if (colCount <= 0) { MessageBox.Show("Invalid column count"); return; } if (rdbExcel97.Checked) { if (colCount > 256) { MessageBox.Show("Column count must be less than or equal to 256 for Excel 2003 format."); return; } if (rowCount > 65536) { MessageBox.Show("Row count must be less than or equal to 65,536 for Excel 2003 format."); return; } } if (rdbExcel2007.Checked || rdbExcel2010.Checked || rdbExcel2013.Checked) { if (rowCount > 100001) { MessageBox.Show("Row count must be less than or equal to 100,000."); return; } if (colCount > 151) { MessageBox.Show("Column count must be less than or equal to 151."); return; } } #region Starttime //Start Time #endregion #region Initialize Workbook //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; //Set the Default version as Excel 97to2003 if (this.rdbExcel97.Checked) { application.DefaultVersion = ExcelVersion.Excel97to2003; fileName = "PerformanceChecking.xls"; } //Set the Default version as Excel 2007 else if (this.rdbExcel2007.Checked) { application.DefaultVersion = ExcelVersion.Excel2007; fileName = "PerformanceChecking.xlsx"; } //Set the Default version as Excel 2010 else if (this.rdbExcel2010.Checked) { application.DefaultVersion = ExcelVersion.Excel2010; fileName = "PerformanceChecking.xlsx"; } //Set the Default version as Excel 2013 else if (this.rdbExcel2013.Checked) { application.DefaultVersion = ExcelVersion.Excel2013; fileName = "PerformanceChecking.xlsx"; } //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 3 worksheets IWorkbook workbook = application.Workbooks.Create(3); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; startTime = DateTime.Now; workbook.DetectDateTimeInValue = false; #endregion if (chbColumnStyle.Checked) { //Body Style IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247)); bodyStyle.Color = Color.FromArgb(239, 243, 247); bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; bodyStyle.EndUpdate(); worksheet.SetDefaultColumnStyle(1, colCount, bodyStyle); } if (this.chkImportOnSave.Checked) { DataTable dataTable = new DataTable(); for (int column = 1; column <= colCount; column++) { dataTable.Columns.Add("Column: " + column.ToString(), typeof(int)); } //Adding data into data table for (int row = 1; row < rowCount; row++) { dataTable.Rows.Add(); for (int column = 1; column <= colCount; column++) { dataTable.Rows[row - 1][column - 1] = row * column; } } startTime = DateTime.Now; worksheet.ImportDataTable(dataTable, 1, 1, true, true); } else { #region Apply Style //Header Style IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33)); headerStyle.Color = Color.FromArgb(255, 174, 33); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); #endregion IMigrantRange migrantRange = worksheet.MigrantRange; for (int column = 1; column <= colCount; column++) { migrantRange.ResetRowColumn(1, column); migrantRange.Text = "Column: " + column.ToString(); migrantRange.CellStyle = headerStyle; } #region Insert Data //Writing Data using normal interface for (int row = 2; row <= rowCount; row++) { //double columnSum = 0.0; for (int column = 1; column <= colCount; column++) { //Writing number migrantRange.ResetRowColumn(row, column); migrantRange.Number = row * column; } } } #endregion #region Workook Save workbook.SaveAs(fileName); #endregion #region Workbook Save and Dispose //Close the workbook workbook.Close(); //Dispose the Excel Engine excelEngine.Dispose(); #endregion #region Set EndTime and get LogDetails //End Time endTime = DateTime.Now - startTime; LogDetails(endTime); #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 } #endregion }
private void generarreporte(int selected) { dtConsulta = getventas(selected); if (dtConsulta.Rows.Count > 0) { int sucursales = radios(); if (sucursales == 1) { string strFechaInicial = Convert.ToDateTime(dtinicial.SelectedDate).ToString("dd-MMM-yyyy").ToUpper(); string strFechaFinal = Convert.ToDateTime(dtfinal.SelectedDate).ToString("dd-MMM-yyyy").ToUpper(); DateTime date = DateTime.Now; string datewithformat = date.ToString(); string dateday = date.ToString("dd MM yyyy"); string closure = date.ToString("dd MM yyyy HH mm"); using (ExcelEngine excelEngine = new ExcelEngine()) { excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //IStyle headerStyle = wo.Styles.Add("HeaderStyle"); IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); DataTable tabla = dtConsulta; int osos = tabla.Rows.Count; worksheet.Name = m_nombreexterno; worksheet.ImportDataTable(tabla, true, 2, 1, true); worksheet.AutoFilters.FilterRange = worksheet.Range["A2:AG2"]; worksheet.Range["A1"].Text = m_nombreempresa + " - VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal; // worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa"; worksheet.Rows[1].FreezePanes(); worksheet.Rows[2].FreezePanes(); #region IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); //workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113)); //headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113); headerStyle.Color = System.Drawing.Color.Gray; headerStyle.Font.Bold = true; headerStyle.Font.Color = ExcelKnownColors.White; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); worksheet.Rows[1].CellStyle = headerStyle; IStyle pStyle = workbook.Styles.Add("pStyle"); pStyle.BeginUpdate(); //workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227)); //pStyle.Color = colorss(m_select); pStyle.Color = System.Drawing.Color.DodgerBlue; pStyle.Font.Bold = true; pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; pStyle.EndUpdate(); #endregion #region //ancho y alineacion columnas worksheet.Rows[0].CellStyle = pStyle; worksheet.SetColumnWidth(1, 10); worksheet.SetColumnWidth(2, 10); worksheet.SetColumnWidth(3, 10); worksheet.SetColumnWidth(4, 10); worksheet.SetColumnWidth(5, 12);//Estado worksheet.SetColumnWidth(6, 7); worksheet.SetColumnWidth(7, 10); worksheet.SetColumnWidth(8, 10); worksheet.SetColumnWidth(9, 45); worksheet.SetColumnWidth(10, 10);//Linea worksheet.SetColumnWidth(11, 12); worksheet.SetColumnWidth(12, 7); worksheet.SetColumnWidth(13, 10); worksheet.SetColumnWidth(14, 5); worksheet.SetColumnWidth(15, 15); worksheet.SetColumnWidth(16, 5); worksheet.SetColumnWidth(17, 4); worksheet.SetColumnWidth(18, 4); worksheet.SetColumnWidth(19, 4); worksheet.SetColumnWidth(20, 4); worksheet.SetColumnWidth(21, 40); worksheet.SetColumnWidth(22, 20); worksheet.SetColumnWidth(23, 5); worksheet.SetColumnWidth(24, 20); worksheet.SetColumnWidth(26, 12); IStyle pStyles = workbook.Styles.Add("pStyles"); pStyles.BeginUpdate(); worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft; worksheet.Columns[8].HorizontalAlignment = ExcelHAlign.HAlignLeft; pStyles.EndUpdate(); #endregion // Create Table with data in the given range int soviet = osos; int rojos = soviet + 3; int rus = soviet + 4; string rusia = rus.ToString(); string cossacks = rojos.ToString(); string gulag = "A2:H" + cossacks + ""; //IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]); string registros = soviet.ToString(); //IRange range = worksheet.Range[gulag]; //table.ShowTotals = true; //table.Columns[0].TotalsRowLabel = "Total"; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< string chorchill = "O2,O" + cossacks + ""; string russevel = "O" + rusia + ""; string ftotal = "O" + rusia + ""; string qty = "N" + rusia + ""; string fpl = "AA" + rusia + ""; string totalr = "A" + rusia + ""; worksheet.Range[totalr].Text = registros + " Registros"; worksheet.Range[totalr].CellStyle = pStyle; string nrusia = "=SUBTOTAL(9,O2:O" + cossacks + ")"; worksheet.Range[russevel].Formula = nrusia; worksheet.Range[russevel].CellStyle = pStyle; worksheet.Range["AA2:" + fpl].NumberFormat = "#,##0.00"; worksheet.Range["O2:" + ftotal].NumberFormat = "#,##0.00"; string sumqty = "=SUBTOTAL(9,N2:N" + cossacks + ")"; worksheet.Range[qty].Formula = sumqty; worksheet.Range[qty].CellStyle = pStyle; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< //table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum; //hacer el subtotal pero conformula ** el otro marca error con total calculation //range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos}); string namer = dateday; string fileName = @"C:\BIG\LRG\Excel\REP. VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal + "( " + closure + " )" + ".xlsx"; // string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx"; workbook.SaveAs(fileName); //workbook.Close(); //excelEngine.Dispose(); string argument = @"/select, " + fileName; System.Diagnostics.Process.Start("explorer.exe", argument); } } else //base VPN { string strFechaInicial = Convert.ToDateTime(dtinicial.SelectedDate).ToString("dd-MMM-yyyy").ToUpper(); string strFechaFinal = Convert.ToDateTime(dtfinal.SelectedDate).ToString("dd-MMM-yyyy").ToUpper(); DateTime date = DateTime.Now; string datewithformat = date.ToString(); string dateday = date.ToString("dd MM yyyy"); string closure = date.ToString("dd MM yyyy HH mm"); using (ExcelEngine excelEngine = new ExcelEngine()) { excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //IStyle headerStyle = wo.Styles.Add("HeaderStyle"); IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); DataTable tabla = dtConsulta; int osos = tabla.Rows.Count; worksheet.Name = m_nombreexterno; worksheet.ImportDataTable(tabla, true, 2, 1, true); worksheet.AutoFilters.FilterRange = worksheet.Range["A2:AG2"]; worksheet.Range["A1"].Text = m_nombreempresa + " - VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal; // worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa"; worksheet.Rows[1].FreezePanes(); worksheet.Rows[2].FreezePanes(); #region IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); headerStyle.Color = System.Drawing.Color.Gray; headerStyle.Font.Bold = true; headerStyle.Font.Color = ExcelKnownColors.White; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); worksheet.Rows[1].CellStyle = headerStyle; IStyle pStyle = workbook.Styles.Add("pStyle"); pStyle.BeginUpdate(); pStyle.Font.Color = ExcelKnownColors.White; //workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227)); if (m_select == 2) { pStyle.Color = System.Drawing.Color.Orange; } else if (m_select == 3 || m_select == 4) { pStyle.Color = System.Drawing.Color.Red; } else { pStyle.Color = System.Drawing.Color.Gray; } pStyle.Font.Bold = true; pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; pStyle.EndUpdate(); #endregion #region //ancho y alineacion columnas worksheet.Rows[0].CellStyle = pStyle; worksheet.SetColumnWidth(1, 10); worksheet.SetColumnWidth(2, 10); worksheet.SetColumnWidth(3, 10); worksheet.SetColumnWidth(4, 10); worksheet.SetColumnWidth(5, 12);//Estado worksheet.SetColumnWidth(6, 7); worksheet.SetColumnWidth(7, 10); worksheet.SetColumnWidth(8, 10); worksheet.SetColumnWidth(9, 45); worksheet.SetColumnWidth(10, 10);//Linea worksheet.SetColumnWidth(11, 12); worksheet.SetColumnWidth(12, 7); worksheet.SetColumnWidth(13, 10); worksheet.SetColumnWidth(14, 5); worksheet.SetColumnWidth(15, 15); worksheet.SetColumnWidth(16, 5); worksheet.SetColumnWidth(17, 4); worksheet.SetColumnWidth(18, 40); worksheet.SetColumnWidth(19, 20); worksheet.SetColumnWidth(20, 4); worksheet.SetColumnWidth(21, 17); worksheet.SetColumnWidth(22, 7); worksheet.SetColumnWidth(23, 15); worksheet.SetColumnWidth(24, 15); worksheet.SetColumnWidth(25, 15); worksheet.SetColumnWidth(26, 7); IStyle pStyles = workbook.Styles.Add("pStyles"); pStyles.BeginUpdate(); worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft; worksheet.Columns[8].HorizontalAlignment = ExcelHAlign.HAlignLeft; worksheet.Columns[7].HorizontalAlignment = ExcelHAlign.HAlignLeft; worksheet.Columns[21].HorizontalAlignment = ExcelHAlign.HAlignRight; pStyles.EndUpdate(); #endregion // Create Table with data in the given range int soviet = osos; int rojos = soviet + 3; int rus = soviet + 4; string rusia = rus.ToString(); string cossacks = rojos.ToString(); string gulag = "A2:H" + cossacks + ""; //IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]); string registros = soviet.ToString(); //IRange range = worksheet.Range[gulag]; //table.ShowTotals = true; //table.Columns[0].TotalsRowLabel = "Total"; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< string chorchill = "O2,O" + cossacks + ""; string russevel = "O" + rusia + ""; string ftotal = "O" + rusia + ""; string ffecha = "B" + rusia + ""; string qty = "N" + rusia + ""; string totalr = "A" + rusia + ""; worksheet.Range[totalr].Text = registros + " Registros"; worksheet.Range[totalr].CellStyle = pStyle; string nrusia = "=SUBTOTAL(9,O2:O" + cossacks + ")"; worksheet.Range[russevel].Formula = nrusia; worksheet.Range[russevel].CellStyle = pStyle; worksheet.Range["O2:" + ftotal].NumberFormat = "#,##0.00"; string sumqty = "=SUBTOTAL(9,N2:N" + cossacks + ")"; worksheet.Range[qty].Formula = sumqty; worksheet.Range[qty].CellStyle = pStyle; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< //table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum; //hacer el subtotal pero conformula ** el otro marca error con total calculation //range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos}); string namer = dateday; string fileName = @"C:\BIG\LRG\Excel\REP. VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal + "( " + closure + " )" + ".xlsx"; // string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx"; workbook.SaveAs(fileName); //workbook.Close(); //excelEngine.Dispose(); string argument = @"/select, " + fileName; System.Diagnostics.Process.Start("explorer.exe", argument); } } } else { RadWindow radWindow = new RadWindow(); RadWindow.Alert(new DialogParameters() { Content = "No hay registros para mostrar.", Header = "BIG", DialogStartupLocation = WindowStartupLocation.CenterOwner // IconContent = ""; }); } }
//For Session //public HttpSessionStateBase Session { get; } public ActionResult DataTable(string saveOption, string button) { string basePath = _hostingEnvironment.WebRootPath; ViewBag.exportButtonState = "disabled=\"disabled\""; ///SaveOption Null if (saveOption == null || button == null) { return(View()); } //Start Data Table 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 Data Table DataTable customersTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); //Add Customer Unique ID DataColumn column = customersTable.Columns.Add("ID"); column.SetOrdinal(0); for (int i = 0; i < customersTable.Rows.Count; i++) { customersTable.Rows[i][0] = i + 1; } //Close the workbook. workbook.Close(); excelEngine.Dispose(); //Set the grid value to the Session _sales = customersTable; 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 DataTable to worksheet sheet.ImportDataTable(_sales, false, 5, 1); 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 = "ExportDataTable.xls"; } else { workbook.Version = ExcelVersion.Excel2013; ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; fileName = "ExportDataTable.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()); }
private void btnExport_Click(object sender, System.EventArgs e) { //Exports the DataTable to a spreadsheet. #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; //Set the Workbook version as Excel 97to2003 if (this.rdbExcel97.Checked) { application.DefaultVersion = ExcelVersion.Excel97to2003; fileName = "ExportToExcel.xls"; } //Set the Workbook version as Excel 2007 else if (this.rdbExcel2007.Checked) { application.DefaultVersion = ExcelVersion.Excel2007; fileName = "ExportToExcel.xlsx"; } //Set the Workbook version as Excel 2010 else if (this.rdbExcel2010.Checked) { application.DefaultVersion = ExcelVersion.Excel2010; fileName = "ExportToExcel.xlsx"; } //Set the Workbook version as Excel 2010 else if (this.rdbExcel2013.Checked) { application.DefaultVersion = ExcelVersion.Excel2013; fileName = "ExportToExcel.xlsx"; } //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 3 worksheets IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region Export DataTable to Excel //Export DataTable. if (this.dataGridView1.DataSource != null) { worksheet.ImportDataTable((DataTable)this.dataGridView1.DataSource, true, 3, 1, -1, -1); } else { MessageBox.Show("There is no datatable to export, Please import a datatable first", "Error"); //Close the workbook. workbook.Close(); return; } #endregion #region Formatting the Report //Formatting the Report #region Applying Body Stlye //Body Style IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(9, Color.FromArgb(239, 242, 247)); bodyStyle.Color = Color.FromArgb(239, 243, 247); bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; //Apply Style worksheet.UsedRange.CellStyleName = "BodyStyle"; bodyStyle.EndUpdate(); #endregion #region Applying Header Style //Header Style IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(8, Color.FromArgb(182, 189, 218)); headerStyle.Color = Color.FromArgb(182, 189, 218); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; //Apply Style worksheet.Range["A1:K3"].CellStyleName = "HeaderStyle"; headerStyle.EndUpdate(); #endregion //Remove grid lines in the worksheet. worksheet.IsGridLinesVisible = false; //Autofit Rows and Columns worksheet.UsedRange.AutofitRows(); worksheet.UsedRange.AutofitColumns(); //Adjust Row Height. worksheet.Rows[1].RowHeight = 25; //Freeze header row. worksheet.Range["A4"].FreezePanes(); worksheet.Range["C2"].Text = "Customer Details"; worksheet.Range["C2:D2"].Merge(); worksheet.Range["C2"].CellStyle.Font.Size = 14; worksheet.Range["C2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; #endregion #region Workbook Save and Close //Saving the workbook to disk. workbook.SaveAs(fileName); //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 }
public static void CreateSurveyExcelSyncfusion3(ExcelVersion version, bool withFilter, DataTable table, string dir) { ArrayList messages = new ArrayList(); string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("Syncfusion_{0}", version))); DateTime begin = DateTime.Now; ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = version; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; //Header IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247)); headerStyle.Color = Color.FromArgb(239, 243, 247); //headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; //headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Font.Bold = true; headerStyle.Font.Size = 12; headerStyle.Color = Color.FromArgb(192, 192, 192); headerStyle.Locked = true; headerStyle.Font.FontName = "Arial"; headerStyle.Font.Color = ExcelKnownColors.Black; headerStyle.Borders.LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; headerStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; headerStyle.EndUpdate(); worksheet.SetDefaultRowStyle(1, headerStyle); IRanges header = worksheet.CreateRangesCollection(); header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]); header.HorizontalAlignment = ExcelHAlign.HAlignCenter; header.RowHeight = 36.75; header.VerticalAlignment = ExcelVAlign.VAlignCenter; //Body IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); workbook.SetPaletteColor(10, Color.FromArgb(255, 255, 204)); bodyStyle.Color = Color.FromArgb(255, 255, 204); bodyStyle.Font.Size = 10; bodyStyle.Font.Color = ExcelKnownColors.Black; bodyStyle.Font.FontName = "Arial"; bodyStyle.Font.Bold = false; bodyStyle.Font.Color = ExcelKnownColors.Black; bodyStyle.Borders.LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; bodyStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; //bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; //bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; bodyStyle.EndUpdate(); worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle); IRanges data = worksheet.CreateRangesCollection(); data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]); data.RowHeight = 31.50; //IStyle bodyStyle = worksheet.CreateRangesCollection().CellStyle; //DateTime beginFillPattern = DateTime.Now; //data.CellStyle.FillPattern = ExcelPattern.Solid; //DateTime endFillPattern = DateTime.Now; //messages.Add(string.Format("\tFillPattern:\t{0}", (endFillPattern - beginFillPattern))); //worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle); IRanges rangesOne = worksheet.CreateRangesCollection(); rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]); worksheet.AutoFilters.FilterRange = rangesOne; worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true); IRanges dateTimeColl = worksheet.CreateRangesCollection(); dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]); dateTimeColl.NumberFormat = "MM/DD/YYYY h:mm am/pm"; for (int i = 1; i <= table.Columns.Count; i++) { worksheet.AutofitColumn(i); } worksheet.SetColumnWidth(5, 18.00); MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); workbook.Close(); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); byte[] xlsData = ms.ToArray(); SaveFile(xlsData, filePath); DateTime end = DateTime.Now; messages.Add(string.Format("Total time:\t{0}", (end - begin))); LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion3(...) - {0},{1}", Path.GetFileName(filePath), (end - begin))); StringBuilder sb = new StringBuilder(); sb.AppendLine(""); foreach (string message in messages) { sb.AppendLine(message); } LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion3 steps\n{0}", sb)); }
private void BtnGenerate_Clicked(object sender, EventArgs e) { int rows = Convert.ToInt32(this.rowCount.Text); int columns = Convert.ToInt32(this.colCount.Text); //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; IWorkbook workbook; workbook = application.Workbooks.Create(1); workbook.Version = ExcelVersion.Excel2013; IWorksheet sheet = workbook.Worksheets[0]; if (this.Import.IsChecked.Value) { DataTable dataTable = new DataTable(); for (int column = 1; column <= columns; column++) { dataTable.Columns.Add("Column: " + column.ToString(), typeof(int)); } //Adding data into data table for (int row = 1; row < rows; row++) { dataTable.Rows.Add(); for (int column = 1; column <= columns; column++) { dataTable.Rows[row - 1][column - 1] = row * column; } } sheet.ImportDataTable(dataTable, 1, 1, true, true); } else { IMigrantRange migrantRange = sheet.MigrantRange; for (int column = 1; column <= Convert.ToInt32(this.colCount.Text); column++) { migrantRange.ResetRowColumn(1, column); migrantRange.SetValue("Column: " + column.ToString()); } //Writing Data using normal interface for (int row = 2; row <= rows; row++) { //double columnSum = 0.0; for (int column = 1; column <= columns; column++) { //Writing number migrantRange.ResetRowColumn(row, column); migrantRange.SetValue(row * column); } } } MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); workbook.Close(); excelEngine.Dispose(); if (Device.RuntimePlatform == Device.UWP) { Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Sample.xlsx", "application/msexcel", stream); } else { Xamarin.Forms.DependencyService.Get <ISave>().Save("Sample.xlsx", "application/msexcel", stream); } }
public static void CreateSurveyExcelSyncfusion0(DataTable table, string dir) { DateTime begin = DateTime.Now; ArrayList messages = new ArrayList(); string filePath = Path.Combine(dir, FileName.Replace(NameKey, "Syncfusion")); ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2010; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; //GlobalStyles DateTime beginDateTimeFormat = DateTime.Now; IRanges dateTimeColl = worksheet.CreateRangesCollection(); dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]); //dateTimeColl.NumberFormat = "mm/dd/yyyy h:mm tt"; dateTimeColl.NumberFormat = "mm/dd/yyyy hh:m am/pm"; messages.Add(string.Format("DateTimeFormat:\t{0}", (DateTime.Now - beginDateTimeFormat))); //Header DateTime beginHeaderFormat = DateTime.Now; IRanges header = worksheet.CreateRangesCollection(); header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]); header.VerticalAlignment = ExcelVAlign.VAlignCenter; header.HorizontalAlignment = ExcelHAlign.HAlignCenter; header.CellStyle.Font.Bold = true; header.CellStyle.Font.Size = 12; header.CellStyle.Font.FontName = "Arial"; header.CellStyle.Font.RGBColor = Color.Black; header.CellStyle.Color = Color.FromArgb(192, 192, 192); header.CellStyle.Locked = true; header.RowHeight = 36.75; header.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin; header.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; header.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; messages.Add(string.Format("HeaderFormat:\t{0}\n", (DateTime.Now - beginHeaderFormat))); //Body DateTime beginBodyFormat = DateTime.Now; IRanges data = worksheet.CreateRangesCollection(); data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]); data.CellStyle.Font.Size = 10; data.RowHeight = 31.50; data.CellStyle.Color = Color.FromArgb(255, 255, 204); data.VerticalAlignment = ExcelVAlign.VAlignCenter; data.CellStyle.Font.FontName = "Arial"; data.CellStyle.Font.RGBColor = Color.Black; data.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin; data.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; data.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; messages.Add(string.Format("BodyFormat:\t{0}\n", (DateTime.Now - beginBodyFormat))); DateTime beginAutoFilters = DateTime.Now; IRanges rangesOne = worksheet.CreateRangesCollection(); rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]); worksheet.AutoFilters.FilterRange = rangesOne; messages.Add(string.Format("AutoFilters:\t{0}", (DateTime.Now - beginAutoFilters))); DateTime beginImport = DateTime.Now; worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true); messages.Add(string.Format("Import:\t{0}", (DateTime.Now - beginImport))); DateTime beginAutofitColumn = DateTime.Now; for (int i = 1; i <= table.Columns.Count; i++) { worksheet.AutofitColumn(i); } messages.Add(string.Format("AutofitColumn:\t{0}", (DateTime.Now - beginAutofitColumn))); MemoryStream ms = new MemoryStream(); DateTime beginSaveAsMemoryStream = DateTime.Now; workbook.SaveAs(ms); DateTime endSaveAsMemoryStream = DateTime.Now; messages.Add(string.Format("SaveAsMemoryStream:\t{0}", (endSaveAsMemoryStream - beginSaveAsMemoryStream))); //workbook.Close(); //excelEngine.ThrowNotSavedOnDestroy = false; //excelEngine.Dispose(); DateTime beginSaveFile = DateTime.Now; byte[] xlsData = ms.ToArray(); SaveFile(xlsData, filePath); DateTime endSaveFile = DateTime.Now; messages.Add(string.Format("SaveFile:\t{0}", (endSaveFile - beginSaveFile))); //LsiLogger.Trace("End of CreateSurveyExcelSyncfusion(...)"); DateTime end = DateTime.Now; messages.Add(string.Format("Total time:\t{0}", (end - begin))); LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion(...) - {0},{1}", Path.GetFileName(filePath), (end - begin))); StringBuilder sb = new StringBuilder(); sb.AppendLine(""); foreach (string message in messages) { sb.AppendLine(message); } LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion steps\n{0}", sb)); }
public static void CreateSurveyExcelSyncfusion2(ExcelVersion version, bool withFilter, DataTable table, string dir) { ArrayList messages = new ArrayList(); string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("Syncfusion_{0}", version))); DateTime begin = DateTime.Now; //LsiLogger.Trace("Begin of CreateSurveyExcelSyncfusion(...)"); ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = version; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; DateTime beginColumnNames = DateTime.Now; worksheet.Range["A1"].Text = "Uid"; worksheet.Range["B1"].Text = "Suid"; worksheet.Range["C1"].Text = "Survey"; worksheet.Range["D1"].Text = "Location"; worksheet.Range["E1"].Text = "Date / Time Name"; worksheet.Range["F1"].Text = "Prompt 1"; worksheet.Range["G1"].Text = "Prompt 2"; worksheet.Range["H1"].Text = "Prompt 3"; worksheet.Range["I1"].Text = "Prompt 4"; worksheet.Range["J1"].Text = "Prompt 5"; worksheet.Range["K1"].Text = "Duration (sec)"; worksheet.Range["L1"].Text = "Expired"; DateTime endColumnNames = DateTime.Now; messages.Add(string.Format("ColumnNames:\t{0}", (endColumnNames - beginColumnNames))); DateTime beginDateTimeFormat = DateTime.Now; IRanges dateTimeColl = worksheet.CreateRangesCollection(); dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]); //dateTimeColl.NumberFormat = "mm/dd/yyyy h:mm tt"; dateTimeColl.NumberFormat = "mm/dd/yyyy hh:mm"; DateTime endDateTimeFormat = DateTime.Now; messages.Add(string.Format("DateTimeFormat:\t{0}", (endDateTimeFormat - beginDateTimeFormat))); //Header DateTime beginHeaderFormat = DateTime.Now; IRanges header = worksheet.CreateRangesCollection(); header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]); header.HorizontalAlignment = ExcelHAlign.HAlignCenter; header.CellStyle.Font.Bold = true; header.CellStyle.Font.Size = 12; header.RowHeight = 36.75; header.CellStyle.Color = Color.FromArgb(192, 192, 192); header.CellStyle.Locked = true; header.VerticalAlignment = ExcelVAlign.VAlignCenter; header.CellStyle.Font.FontName = "Arial"; header.CellStyle.Font.RGBColor = Color.Black; header.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin; header.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; header.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; DateTime endHeaderFormat = DateTime.Now; messages.Add(string.Format("HeaderFormat:\t{0}\n", (endHeaderFormat - beginHeaderFormat))); //Body DateTime beginBodyFormat = DateTime.Now; for (int i = 0; i < table.Rows.Count + 1; i++) { IRanges data = worksheet.CreateRangesCollection(); data.Add(worksheet.Range[2 + i, 1, 2 + i, table.Columns.Count]); //IRanges data = worksheet.CreateRangesCollection(); //data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]); DateTime beginLineStyle = DateTime.Now; data.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin; DateTime endLineStyle = DateTime.Now; messages.Add(string.Format("\tLineStyle:\t{0}", (endLineStyle - beginLineStyle))); DateTime beginDiagonalDown = DateTime.Now; data.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; DateTime endDiagonalDown = DateTime.Now; messages.Add(string.Format("\tDiagonalDown:\t{0}", (endDiagonalDown - beginDiagonalDown))); DateTime beginDiagonalUp = DateTime.Now; data.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; DateTime endDiagonalUp = DateTime.Now; messages.Add(string.Format("\tDiagonalUp:\t{0}", (endDiagonalUp - beginDiagonalUp))); IFont font = data.CellStyle.Font; DateTime beginFontName = DateTime.Now; //data.CellStyle.Font.FontName = "Arial"; font.FontName = "Arial"; DateTime endFontName = DateTime.Now; messages.Add(string.Format("\tFontName:\t{0}", (endFontName - beginFontName))); DateTime beginFontSize = DateTime.Now; //data.CellStyle.Font.Size = 10; font.Size = 10; DateTime endFontSize = DateTime.Now; messages.Add(string.Format("\tFontSize:\t{0}", (endFontSize - beginFontSize))); DateTime beginFontRGBColor = DateTime.Now; //data.CellStyle.Font.RGBColor = Color.Black; font.Color = ExcelKnownColors.Black; DateTime endFontRGBColor = DateTime.Now; messages.Add(string.Format("\tFontRGBColor:\t{0}", (endFontRGBColor - beginFontRGBColor))); DateTime beginVerticalAlignment = DateTime.Now; data.VerticalAlignment = ExcelVAlign.VAlignCenter; DateTime endVerticalAlignment = DateTime.Now; messages.Add(string.Format("\tVerticalAlignment:\t{0}", (endVerticalAlignment - beginVerticalAlignment))); DateTime beginFillPattern = DateTime.Now; data.CellStyle.FillPattern = ExcelPattern.Solid; DateTime endFillPattern = DateTime.Now; messages.Add(string.Format("\tFillPattern:\t{0}", (endFillPattern - beginFillPattern))); DateTime beginRowHeight = DateTime.Now; //data.RowHeight = 31.50; worksheet.SetRowHeight(i + 2, 31.5); DateTime endRowHeight = DateTime.Now; messages.Add(string.Format("\tRowHeight:\t{0}", (endRowHeight - beginRowHeight))); DateTime beginColor = DateTime.Now; data.CellStyle.Color = Color.FromArgb(255, 255, 204); DateTime endColor = DateTime.Now; messages.Add(string.Format("\tColor:\t{0}", (endColor - beginColor))); } DateTime endBodyFormat = DateTime.Now; messages.Add("\t---------------------------"); messages.Add(string.Format("BodyFormat:\t{0}\n", (endBodyFormat - beginBodyFormat))); if (withFilter) { DateTime beginAutoFilters = DateTime.Now; IRanges rangesOne = worksheet.CreateRangesCollection(); rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]); worksheet.AutoFilters.FilterRange = rangesOne; DateTime endAutoFilters = DateTime.Now; messages.Add(string.Format("AutoFilters:\t{0}", (endAutoFilters - beginAutoFilters))); } DateTime beginImport = DateTime.Now; worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true); DateTime endImport = DateTime.Now; messages.Add(string.Format("Import:\t{0}", (endImport - beginImport))); DateTime beginAutofitColumn = DateTime.Now; for (int i = 1; i <= table.Columns.Count; i++) { worksheet.AutofitColumn(i); //10000-7sec. } DateTime endAutofitColumn = DateTime.Now; messages.Add(string.Format("AutofitColumn:\t{0}", (endAutofitColumn - beginAutofitColumn))); MemoryStream ms = new MemoryStream(); DateTime beginSaveAsMemoryStream = DateTime.Now; workbook.SaveAs(ms); DateTime endSaveAsMemoryStream = DateTime.Now; messages.Add(string.Format("SaveAsMemoryStream:\t{0}", (endSaveAsMemoryStream - beginSaveAsMemoryStream))); //workbook.Close(); //excelEngine.ThrowNotSavedOnDestroy = false; //excelEngine.Dispose(); DateTime beginSaveFile = DateTime.Now; byte[] xlsData = ms.ToArray(); SaveFile(xlsData, filePath); DateTime endSaveFile = DateTime.Now; messages.Add(string.Format("SaveFile:\t{0}", (endSaveFile - beginSaveFile))); //LsiLogger.Trace("End of CreateSurveyExcelSyncfusion(...)"); DateTime end = DateTime.Now; messages.Add(string.Format("Total time:\t{0}", (end - begin))); LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion2(...) - {0},{1}", Path.GetFileName(filePath), (end - begin))); StringBuilder sb = new StringBuilder(); sb.AppendLine(""); foreach (string message in messages) { sb.AppendLine(message); } LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion2 steps\n{0}", sb)); }
/// <summary> /// Convert the Excel document to JSON /// </summary> /// <returns>Return the JSON document as stream</returns> public MemoryStream ImportExportXlsIO(string button, string option, DataTable dataTable) { if (button == "Input Document") { //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 using (ExcelEngine excelEngine = new ExcelEngine()) { //Step 2 : Instantiate the excel application object IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; //Opening the encrypted Workbook FileStream inputStream = new FileStream(ResolveApplicationPath("northwind-data-template.xls"), FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelParseOptions.Default); //Save the document as a stream and retrun the stream using (MemoryStream stream = new MemoryStream()) { //Save the created Excel document to MemoryStream workbook.SaveAs(stream); return(stream); } } } 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 using (ExcelEngine excelEngine = new ExcelEngine()) { //Exports the DataTable to a spreadsheet. string fileName = string.Empty; #region Workbook Initialize //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; //Set the Workbook version as Excel 97to2003 if (option == "XLS") { application.DefaultVersion = ExcelVersion.Excel97to2003; fileName = "ExportToExcel.xls"; } //Set the Workbook version as Excel 2007 else { application.DefaultVersion = ExcelVersion.Excel2007; fileName = "ExportToExcel.xlsx"; } //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 3 worksheets IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region Export DataTable to Excel //Export DataTable. if (dataTable != null) { worksheet.ImportDataTable(dataTable, true, 3, 1, -1, -1); } #endregion #region Formatting the Report //Formatting the Report #region Applying Body Stlye //Body Style IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(9, Syncfusion.Drawing.Color.FromArgb(239, 242, 247)); bodyStyle.Color = Syncfusion.Drawing.Color.FromArgb(239, 243, 247); bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; //Apply Style worksheet.UsedRange.CellStyleName = "BodyStyle"; bodyStyle.EndUpdate(); #endregion #region Applying Header Style //Header Style IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); //Add custom colors to the palette. workbook.SetPaletteColor(8, Syncfusion.Drawing.Color.FromArgb(182, 189, 218)); headerStyle.Color = Syncfusion.Drawing.Color.FromArgb(182, 189, 218); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; //Apply Style worksheet.Range["A1:K3"].CellStyleName = "HeaderStyle"; headerStyle.EndUpdate(); #endregion //Remove grid lines in the worksheet. worksheet.IsGridLinesVisible = false; //Autofit Rows and Columns worksheet.UsedRange.AutofitRows(); worksheet.UsedRange.AutofitColumns(); //Adjust Row Height. worksheet.Rows[1].RowHeight = 25; //Freeze header row. worksheet.Range["A4"].FreezePanes(); worksheet.Range["C2"].Text = "Customer Details"; worksheet.Range["C2:D2"].Merge(); worksheet.Range["C2"].CellStyle.Font.Size = 14; worksheet.Range["C2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; #endregion #region Workbook Save and Close //Saving the workbook to disk. //Save the document as a stream and retrun the stream using (MemoryStream stream = new MemoryStream()) { //Save the created Excel document to MemoryStream workbook.SaveAs(stream); return(stream); } #endregion } } }
private void generarreporte() { DataTable tabla = dtblcatalogo; //Refresh(CashierBusyIndicator); //generar(); DateTime date = DateTime.Now; string datewithformat = date.ToString(); string dateday = date.ToString("dd MMMM yyyy HH mm ").ToUpper(); using (ExcelEngine excelEngine = new ExcelEngine()) { excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //IStyle headerStyle = wo.Styles.Add("HeaderStyle"); IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); int osos = tabla.Rows.Count; worksheet.Name = "Servicios"; worksheet.ImportDataTable(tabla, true, 2, 1); worksheet.AutoFilters.FilterRange = worksheet.Range["A2:K2"]; worksheet.Range["A1"].Text = "CATÁLOGO DE SERVICIOS AL " + dateday; // worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa"; worksheet.Rows[1].FreezePanes(); worksheet.Rows[2].FreezePanes(); IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113)); headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); worksheet.Rows[1].CellStyle = headerStyle; IStyle pStyle = workbook.Styles.Add("pStyle"); pStyle.BeginUpdate(); workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227)); pStyle.Color = System.Drawing.Color.FromArgb(89, 171, 227); pStyle.Font.Bold = true; pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; pStyle.EndUpdate(); worksheet.Rows[0].CellStyle = pStyle; worksheet.SetColumnWidth(1, 10); worksheet.SetColumnWidth(2, 30); worksheet.SetColumnWidth(3, 15); worksheet.SetColumnWidth(4, 20); worksheet.SetColumnWidth(5, 7); worksheet.SetColumnWidth(6, 7); worksheet.SetColumnWidth(7, 10); worksheet.SetColumnWidth(8, 15); worksheet.SetColumnWidth(11, 15); IStyle pStyles = workbook.Styles.Add("pStyles"); pStyles.BeginUpdate(); worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft; pStyles.EndUpdate(); // Create Table with data in the given range int soviet = osos; int rojos = soviet + 3; int rus = soviet + 4; string rusia = rus.ToString(); string cossacks = rojos.ToString(); string gulag = "A2:H" + cossacks + ""; //IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]); string registros = soviet.ToString(); //IRange range = worksheet.Range[gulag]; //table.ShowTotals = true; //table.Columns[0].TotalsRowLabel = "Total"; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< string chorchill = "H2,H" + cossacks + ""; string russevel = "H" + rusia + ""; string totalr = "A" + rusia + ""; worksheet.Range[totalr].Text = registros + " Registros"; worksheet.Range[totalr].CellStyle = pStyle; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< //table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum; //hacer el subtotal pero conformula ** el otro marca error con total calculation //range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos}); string namer = dateday; string fileName = @"C:\BIG\LRG\Excel\CATÁLOGO DE SERVICIOS AL " + dateday + ".xlsx"; // string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx"; workbook.SaveAs(fileName); string argument = @"/select, " + fileName; System.Diagnostics.Process.Start("explorer.exe", argument); } }
static void Main(string[] args) { intEMPRESAID = Convert.ToByte(GetSetting(appName, section, "EmpresaID", String.Empty)); intSUCURSALID = Convert.ToByte(GetSetting(appName, section, "SucursalID", String.Empty)); DateTime date = DateTime.Now; string datewithformat = date.ToString(); string dateday = date.ToString("dd MMMM yyyy HH mm "); using (ExcelEngine excelEngine = new ExcelEngine()) { excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //IStyle headerStyle = wo.Styles.Add("HeaderStyle"); IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); DataTable tabla = GetExistenciaDataTable(); int osos = tabla.Rows.Count; worksheet.ImportDataTable(tabla, true, 2, 1); worksheet.AutoFilters.FilterRange = worksheet.Range["A2:H2"]; string namesuc = GetName(intSUCURSALID); worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al " + dateday + namesuc; // worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa"; worksheet.Rows[1].FreezePanes(); worksheet.Rows[2].FreezePanes(); IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113)); headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); worksheet.Rows[1].CellStyle = headerStyle; IStyle pStyle = workbook.Styles.Add("pStyle"); pStyle.BeginUpdate(); workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227)); pStyle.Color = System.Drawing.Color.FromArgb(89, 171, 227); pStyle.Font.Bold = true; pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; pStyle.EndUpdate(); worksheet.Rows[0].CellStyle = pStyle; worksheet.SetColumnWidth(1, 13); worksheet.SetColumnWidth(2, 50); worksheet.SetColumnWidth(3, 17); worksheet.SetColumnWidth(4, 28); // Create Table with data in the given range int soviet = osos; int rojos = soviet + 3; int rus = soviet + 4; string rusia = rus.ToString(); string cossacks = rojos.ToString(); string gulag = "A2:H" + cossacks + ""; //IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]); string registros = soviet.ToString(); //IRange range = worksheet.Range[gulag]; //table.ShowTotals = true; //table.Columns[0].TotalsRowLabel = "Total"; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< string chorchill = "H2,H" + cossacks + ""; string russevel = "H" + rusia + ""; string totalr = "A" + rusia + ""; worksheet.Range[totalr].Text = registros + " Registros"; worksheet.Range[totalr].CellStyle = pStyle; string nrusia = "=SUM(H2:H" + cossacks + ")"; worksheet.Range[russevel].Formula = nrusia; worksheet.Range[russevel].CellStyle = pStyle; //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< //table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum; //hacer el subtotal pero conformula ** el otro marca error con total calculation //range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos}); string namer = dateday; string fileName = "LRG-Existencias al " + namer + namesuc + ".xlsx"; // string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx"; workbook.SaveAs(fileName); workbook.Close(); excelEngine.Dispose(); } }
public ActionResult Performance(string SaveOption, string Import, string rowCount, string colCount) { if (SaveOption == null) { return(View()); } int rows = Convert.ToInt32(rowCount); int columns = Convert.ToInt32(colCount); if (SaveOption == "Xls") { if (columns > 256) { Response.Write("<script LANGUAGE='JavaScript' >alert('Column count must be less than or equal to 256 for Excel 2003 format.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>"); return(View()); } if (rows > 65536) { Response.Write("<script LANGUAGE='JavaScript' >alert('Row count must be less than or equal to 65,536 for Excel 2003 format.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>"); return(View()); } } if (SaveOption == "Xlsx") { if (rows > 100001) { Response.Write("<script LANGUAGE='JavaScript' >alert('Row count must be less than or equal to 100,000.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>"); return(View()); } if (columns > 151) { Response.Write("<script LANGUAGE='JavaScript' >alert('Column count must be less than or equal to 151.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>"); return(View()); } } //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; IWorkbook workbook; if (SaveOption == "Xlsx") { application.DefaultVersion = ExcelVersion.Excel2016; } else { application.DefaultVersion = ExcelVersion.Excel97to2003; } workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; if (Import == "importonsave") { workbook.Version = ExcelVersion.Excel2013; DataTable dataTable = new DataTable(); for (int column = 1; column <= columns; column++) { dataTable.Columns.Add("Column: " + column.ToString(), typeof(int)); } //Adding data into data table for (int row = 1; row < rows; row++) { dataTable.Rows.Add(); for (int column = 1; column <= columns; column++) { dataTable.Rows[row - 1][column - 1] = row * column; } } sheet.ImportDataTable(dataTable, 1, 1, true, true); } else { IMigrantRange migrantRange = sheet.MigrantRange; for (int column = 1; column <= columns; column++) { migrantRange.ResetRowColumn(1, column); migrantRange.SetValue("Column: " + column.ToString()); } //Writing Data using normal interface for (int row = 2; row <= rows; row++) { //double columnSum = 0.0; for (int column = 1; column <= columns; column++) { //Writing number migrantRange.ResetRowColumn(row, column); migrantRange.SetValue(row * column); } } } try { if (SaveOption == "Xls") { return(excelEngine.SaveAsActionResult(workbook, "Performance.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } else { return(excelEngine.SaveAsActionResult(workbook, "Performance.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); return(View()); }
public ActionResult Performance(string SaveOption, string Import, string rowCount, string colCount) { if (SaveOption == null) { return(View()); } int rows = Convert.ToInt32(rowCount); int columns = Convert.ToInt32(colCount); //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; // Creating new workbook IWorkbook workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; #region Generate Excel if (Import == "importonsave") { DataTable dataTable = new DataTable(); for (int column = 1; column <= columns; column++) { dataTable.Columns.Add("Column: " + column.ToString(), typeof(int)); } //Adding data into data table for (int row = 1; row < rows; row++) { dataTable.Rows.Add(); for (int column = 1; column <= columns; column++) { dataTable.Rows[row - 1][column - 1] = row * column; } } sheet.ImportDataTable(dataTable, 1, 1, true, true); } else { IMigrantRange migrantRange = workbook.Worksheets[0].MigrantRange; for (int column = 1; column <= columns; column++) { migrantRange.ResetRowColumn(1, column); migrantRange.Text = "Column: " + column.ToString(); } //Writing Data using normal interface for (int row = 2; row <= rows; row++) { //double columnSum = 0.0; for (int column = 1; column <= columns; column++) { //Writing number migrantRange.ResetRowColumn(row, column); migrantRange.Number = row * column; } } } #endregion string ContentType = null; string fileName = null; if (SaveOption == "ExcelXls") { ContentType = "Application/vnd.ms-excel"; fileName = "Sample.xls"; } else { workbook.Version = ExcelVersion.Excel2013; ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; fileName = "Sample.xlsx"; } MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; return(File(ms, ContentType, fileName)); }
public ActionResult Invoice(string id, string SaveOption) { if (SaveOption == null) { InitializeData(); return(View()); } int invoiceId = Convert.ToInt32(id); AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true); sqlCeConnection = new SqlCeConnection(); if (sqlCeConnection.ServerVersion.StartsWith("3.5")) { sqlCeConnection.ConnectionString = "Data Source = " + ResolveApplicationDataPath("NorthwindIO_3.5.sdf"); } else { sqlCeConnection.ConnectionString = "Data Source = " + ResolveApplicationDataPath("NorthwindIO.sdf"); } //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; IWorkbook workbook; if (SaveOption == "Xlsx") { application.DefaultVersion = ExcelVersion.Excel2016; workbook = application.Workbooks.Open(ResolveApplicationDataPath("Invoice.xlsx")); } else { application.DefaultVersion = ExcelVersion.Excel97to2003; workbook = application.Workbooks.Open(ResolveApplicationDataPath("Invoice.xls")); } IWorksheet sheet = workbook.Worksheets[0]; sheet.Range["A5"].Text = "One Portals Way"; sheet.Range["A6"].Text = "Twin Points WA 98156"; sheet.Range["A7"].Text = "Phone: 1-206-555-1417 "; sheet.Range["A8"].Text = "Fax: 1-206-555-5938"; sheet.Range["D5"].Text = "INVOICE NO:"; sheet.Range["D6"].Text = "DATE:"; sheet.Range["D7"].Text = "CUSTOMER ID :"; sheet.Range["E6"].Formula = "TODAY()"; sheet.Range["E5"].Number = invoiceId; //Set values for Ship To. GetShipDetails(invoiceId); sheet.Range["E7"].Text = shipName; sheet.Range["E10"].Text = shipName; sheet.Range["E11"].Text = address; sheet.Range["E12"].Text = shipCity; sheet.Range["E13"].Text = shipCountry; //Set values for Bill To. sheet.Range["B10"].Text = shipName; sheet.Range["B11"].Text = address; sheet.Range["B12"].Text = shipCity; sheet.Range["B13"].Text = shipCountry; //Calculates sub total sheet.Range["E27"].Formula = "SUM(E20:E26)"; //Set the number format sheet.Range["E20:E29"].NumberFormat = "$#,##0.00"; sheet.Range["E28"].Value = freight.ToString(); //Calculates final total sheet.Range["E29"].Formula = "E27+E28"; //Import the data tables. sheet.ImportDataTable(GetOrder(invoiceId), false, 17, 1); sheet.ImportDataTable(GetProductDetails(invoiceId), false, 20, 1); //Calculates price from discount and unit price. for (int i = 20; i <= 26; i++) { if (sheet.Range["A" + i.ToString()].Value == "") { break; } else { sheet.Range["E" + i.ToString()].Formula = "(B" + i.ToString() + "*C" + i.ToString() + ")- (D" + i.ToString() + "/100)"; } } try { if (SaveOption == "Xls") { return(excelEngine.SaveAsActionResult(workbook, "Invoice.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } else { return(excelEngine.SaveAsActionResult(workbook, "Invoice.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); adapter.Dispose(); connection.Close(); return(View()); }
void OnExportButtonClicked(object sender, EventArgs e) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 1 worksheets IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; //Get DataGrid's datasource as DataTable System.Data.DataTable dataTable = (System.Data.DataTable)sfGrid.ItemsSource; //Import DataTable to Excel worksheet. sheet.ImportDataTable(dataTable, 5, 1, false); #region Define Styles IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle"); IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle"); pageHeader.Font.RGBColor = COLOR.Color.FromArgb(255, 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.Black; tableHeader.Font.Bold = true; tableHeader.Font.Size = 11; tableHeader.Font.FontName = "Calibri"; tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Color = COLOR.Color.FromArgb(255, 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:D1"].Merge(); sheet["A1"].Text = "Yearly Sales Report"; sheet["A1"].CellStyle = pageHeader; sheet["A1"].RowHeight = 20; sheet["A2:D2"].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["A2"].RowHeight = 20; sheet["A3:A4"].Merge(); sheet["D3:D4"].Merge(); sheet["B3:C3"].Merge(); sheet["B3"].Text = "Sales"; sheet["A3:D4"].CellStyle = tableHeader; sheet["A3"].Text = "Sales Person"; sheet["B4"].Text = "Jan - Jun"; sheet["C4"].Text = "Jul - Dec"; sheet["D3"].Text = "Change (%)"; sheet.Columns[0].ColumnWidth = 19; sheet.Columns[1].ColumnWidth = 10; sheet.Columns[2].ColumnWidth = 10; sheet.Columns[3].ColumnWidth = 11; #endregion workbook.Version = ExcelVersion.Excel2013; MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); workbook.Close(); excelEngine.Dispose(); if (stream != null) { SaveiOS iOSSave = new SaveiOS(); iOSSave.Save("DataTable.xlsx", "application/msexcel", stream); } }
private void Button1_Click(object sender, EventArgs e) { int rowCount = Convert.ToInt32(textBox1.Value); int colCount = Convert.ToInt32(textBox2.Value); //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; IWorkbook workbook; workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; if (chkImport.Checked) { workbook.Version = ExcelVersion.Excel2013; DataTable dataTable = new DataTable(); for (int column = 1; column <= colCount; column++) { dataTable.Columns.Add("Column: " + column.ToString(), typeof(int)); } //Adding data into data table for (int row = 1; row < rowCount; row++) { dataTable.Rows.Add(); for (int column = 1; column <= colCount; column++) { dataTable.Rows[row - 1][column - 1] = row * column; } } sheet.ImportDataTable(dataTable, 1, 1, true, true); } else { IMigrantRange migrantRange = sheet.MigrantRange; for (int column = 1; column <= colCount; column++) { migrantRange.ResetRowColumn(1, column); migrantRange.SetValue("Column: " + column.ToString()); } //Writing Data using normal interface for (int row = 2; row <= rowCount; row++) { //double columnSum = 0.0; for (int column = 1; column <= colCount; column++) { //Writing number migrantRange.ResetRowColumn(row, column); migrantRange.SetValue(row * column); } } } workbook.Version = ExcelVersion.Excel2013; MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); workbook.Close(); excelEngine.Dispose(); if (stream != null) { SaveAndroid androidSave = new SaveAndroid(); androidSave.Save("CreateSheet.xlsx", "application/msexcel", stream, m_context); } }
public void convertirXls(string rutaFile, long idInterno) { string xlsxFile = rutaFile.Split('.')[0] + ".xlsx"; _logger.LogInformation("Si existe el archivo xlsx {0} lo borra.", xlsxFile); if (File.Exists(xlsxFile)) { File.Delete(xlsxFile); } try { try { _logger.LogInformation("Intenta abrir el archivo xls {0}.", rutaFile); ExcelPackage package = new ExcelPackage(); try { DataTableCollection dts; using (var stream = File.Open(rutaFile, FileMode.Open, FileAccess.Read)) { _logger.LogInformation("Usa stream para el archivo xls {0}.", rutaFile); using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream)) { DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true } }); dts = result.Tables; _logger.LogInformation("Obtiene tablas el archivo xlsx {0}, {1} tablas.", xlsxFile, result.Tables.Count); } } _logger.LogInformation("Abrió el archivo xls {0}.", rutaFile); _logger.LogInformation("Intenta escribir el archivo xlsx {0}.", xlsxFile); using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; DataTable dataTable = dts[0]; worksheet.ImportDataTable(dataTable, true, 1, 1); worksheet.UsedRange.AutofitColumns(); using (FileStream file_stream = new FileStream(xlsxFile, FileMode.Create)) { _logger.LogInformation("Usa stream para el archivo xlsx {0}.", xlsxFile); workbook.SaveAs(file_stream); _logger.LogInformation("Guardó en stream el archivo xlsx {0}.", xlsxFile); } } _logger.LogInformation("Escribió el archivo xlsx {0}.", xlsxFile); } catch (Exception e) { RegistraError(e, "No se pudo abrir el XLSX"); return; } } catch (Exception e) { RegistraError(e, "No se pudo abrir el XLSX"); return; } // using (ExcelPackage excel = new ExcelPackage()) // { // excel.Workbook.Worksheets.Add("Worksheet1"); // excel.Workbook.Worksheets.Add("Worksheet2"); // excel.Workbook.Worksheets.Add("Worksheet3"); // FileInfo excelFile = new FileInfo(rutaFile.Split('.')[0] + "xlsx"); // excel.SaveAs(excelFile); // } } catch (Exception ex) { } procesarXlsx(xlsxFile, idInterno); }
internal void OnExportClicked(object sender, EventArgs e) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; //Initializing Workbook //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 1 worksheets IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; //Create new DataTable to store the data from DataGrid DataTable dataTable = new DataTable(); //Convert the Grid's data to DataTable ConvertGridDataToDataTable(dataTable, dataGrid.ItemsSource); //Import the DataTable to worksheet sheet.ImportDataTable(dataTable, 5, 1, false); // Define Styles IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle"); IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle"); pageHeader.Font.RGBColor = COLOR.Color.FromArgb(255, 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.Black; tableHeader.Font.Bold = true; tableHeader.Font.Size = 11; tableHeader.Font.FontName = "Calibri"; tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Color = COLOR.Color.FromArgb(255, 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; // Apply Styles // Apply style for header sheet["A1:D1"].Merge(); sheet["A1"].Text = "Yearly Sales Report"; sheet["A1"].CellStyle = pageHeader; sheet["A1"].RowHeight = 20; sheet["A2:D2"].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["A2"].RowHeight = 20; sheet["A3:A4"].Merge(); sheet["D3:D4"].Merge(); sheet["B3:C3"].Merge(); sheet["B3"].Text = "Sales"; sheet["A3:D4"].CellStyle = tableHeader; sheet["A3"].Text = "Sales Person"; sheet["B4"].Text = "Jan - Jun"; sheet["C4"].Text = "Jul - Dec"; sheet["D3"].Text = "Change (%)"; sheet.Columns[0].ColumnWidth = 19; sheet.Columns[1].ColumnWidth = 10; sheet.Columns[2].ColumnWidth = 10; sheet.Columns[3].ColumnWidth = 11; // Saving workbook and disposing objects workbook.Version = ExcelVersion.Excel2013; MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); workbook.Close(); excelEngine.Dispose(); if (Device.RuntimePlatform == Device.UWP) { Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("DataTable.xlsx", "application/msexcel", stream); } else { Xamarin.Forms.DependencyService.Get <ISave>().Save("DataTable.xlsx", "application/msexcel", stream); } }
public async Task ExportToExcel(string GridModel, string gridId, int id, string process, int?userId) { ExcelExport exp = new ExcelExport(); GridProperties obj = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel); //Clear if there are any filter columns //syncfusion bug in exporting while in filter mode obj.FilterSettings.FilteredColumns.Clear(); grid = gridId; count = 0; if (gridId == "Audits") { var audits = await _prepareService.GetAudits(); if (userId.HasValue) { audits = audits.Where(a => a.AuditorId == userId.Value || a.Inspection.InspectionSteps.Any(s => s.InspectorId == userId.Value)); } var auditManage = AuditMapper.ToAuditManageViewModel(audits); //var dataSource = auditManage.Audits.ToList(); //auditViewModel = dataSource; //var currentDate = DateTime.Today.ToShortDateString().Replace("/", "-"); //obj.ServerExcelQueryCellInfo = QueryCellInfo; //exp.Export(obj, dataSource, "Audits " + currentDate + ".xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron"); using (ExcelEngine excelEngine = new ExcelEngine()) { // Set the default application version as Excel 2016. excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //var group1 = auditManage.Audits.ToList(); var allAudits = auditManage.Audits.ToList(); int i = 0; //Create a workbook with a worksheet. IWorkbook workbook = excelEngine.Excel.Workbooks.Create(audits.Select(a => a.Survey).Distinct().Count()); foreach (var survey in audits.Select(a => a.Survey).Distinct()) { IWorksheet worksheet = workbook.Worksheets[i]; DataTable tbl = new DataTable(); //fill columns tbl.Columns.Add(LocalizedStrings.GetString("AuditDate"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Schedule"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Team"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Auditor"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Auditee"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("AuditStatus"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("StandardAuditee"), typeof(string)); foreach (var surveyItem in survey.SurveyItems) { tbl.Columns.Add(surveyItem.Query, typeof(int)); } tbl.Columns.Add("Score", typeof(int)); tbl.Columns.Add(LocalizedStrings.GetString("Comment"), typeof(string)); //fill rows foreach (var audit in auditManage.Audits.Where(a => a.SurveyId == survey.Id)) { DataRow dr = null; dr = tbl.NewRow(); dr[LocalizedStrings.GetString("AuditDate")] = audit.EndDate.Value.ToShortDateString(); dr[LocalizedStrings.GetString("Schedule")] = audit.EndDate.Value.ToShortTimeString().Replace(":", "H"); dr[LocalizedStrings.GetString("Team")] = string.Join(",", audit.AuditorTeams); dr[LocalizedStrings.GetString("Auditor")] = audit.AuditorName; dr[LocalizedStrings.GetString("Auditee")] = audit.AuditeeName; dr[LocalizedStrings.GetString("AuditStatus")] = audits.Select(a => a.Auditor).FirstOrDefault(u => u.UserId == audit.AuditorId).Tenured.HasValue ? audits.Select(a => a.Auditor).FirstOrDefault(u => u.UserId == audit.AuditorId).Tenured.Value ? "Titulaire" : "Intérimaire" : ""; dr[LocalizedStrings.GetString("StandardAuditee")] = audit.ProcessName; int scoreTemp = 0; foreach (var surveyItem in survey.SurveyItems) { dr[surveyItem.Query] = audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.HasValue ? audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.Value ? 1 : 0 : -1; if (audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.Value == true) { scoreTemp++; } } dr["Score"] = scoreTemp; tbl.Rows.Add(dr); } worksheet.ImportDataTable(tbl, true, 2, 1); worksheet.Name = survey.Name; worksheet.UsedRange.WrapText = true; worksheet.Columns[0].AutofitColumns(); worksheet.SetColumnWidth(1, 10); IListObject table = worksheet.ListObjects.Create("tbl_" + survey.Name.Replace(" ", "_"), worksheet.UsedRange); table.BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9; foreach (var surveyItem in survey.SurveyItems) { //Apply conditional formats for IsOK questionnaire items IConditionalFormats condition = worksheet.Columns[table.Columns.IndexOf(s => s.Name == surveyItem.Query)].ConditionalFormats; IConditionalFormat condition1 = condition.AddCondition(); condition1.FormatType = ExcelCFType.CellValue; condition1.Operator = ExcelComparisonOperator.Equal; condition1.FirstFormula = "1"; condition1.BackColor = ExcelKnownColors.Green; IConditionalFormat condition2 = condition.AddCondition(); condition2.FormatType = ExcelCFType.CellValue; condition2.Operator = ExcelComparisonOperator.Equal; condition2.FirstFormula = "0"; condition2.BackColor = ExcelKnownColors.Red; worksheet.Columns[table.Columns.IndexOf(s => s.Name == surveyItem.Query)].RowHeight = 80; worksheet.AutofitColumn(table.Columns.IndexOf(s => s.Name == surveyItem.Query)); } i++; } //worksheet.ImportData(group1, 2, 1, true); var path = Server.MapPath("~/App_Data/" + LocalizedStrings.GetString("Audits") + ".xlsx"); workbook.SaveAs(path, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2010); workbook.Close(); excelEngine.Dispose(); } } if (gridId == "AuditItems") { var audits = await _prepareService.GetAudits(id); var audit = AuditMapper.ToAuditViewModel(audits.FirstOrDefault()); var dataSource = audit.AuditItems.ToList(); auditItemViewModel = dataSource; var currentDate = DateTime.Today.ToShortDateString().Replace("/", "-"); obj.ServerExcelQueryCellInfo = QueryCellInfo; exp.Export(obj, dataSource, LocalizedStrings.GetString("Audit") + " " + process + " " + currentDate + ".xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron"); } }