public async Task <int> DeleteEmploye(int id) { IWorksheet worksheet = await Excel.get(); int length = worksheet.Rows.Count(); worksheet.DeleteRow(id); Excel._workbook.Close(true); return(id); }
// // GET: /RowColumnManipulation/ public ActionResult RowColumnManipulation(string Saveoption, string button) { if (Saveoption == null) { return(View()); } else if (button == "Input Template") { //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"monthly_sales.xlsx")); return(excelEngine.SaveAsActionResult(workbook, "Template.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } else { //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //The new workbook will have 3 worksheets IWorkbook workbook; if (Saveoption == "Xlsx") { application.DefaultVersion = ExcelVersion.Excel2016; workbook = application.Workbooks.Open(ResolveApplicationDataPath("monthly_sales.xlsx")); } else { workbook = application.Workbooks.Open(ResolveApplicationDataPath("monthly_sales.xls")); } //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; #region Grouping and ungrouping // Grouping by Rows sheet.Range["C5:F7"].Group(ExcelGroupBy.ByRows); // Grouping by Columns sheet.Range["C10:F10"].Group(ExcelGroupBy.ByColumns); #endregion #region Hiding unhiding // Hiding fifth and sixth Column sheet.ShowColumn(5, false); sheet.ShowColumn(6, false); //Showing the 28th row sheet.ShowRow(28, true); #endregion #region Insert and delete //Deleting Row sheet.DeleteRow(25); //Inserting Column sheet.InsertColumn(7, 1, ExcelInsertOptions.FormatAsBefore); sheet.Range["G4"].Text = "Loss/Gain"; //Deleting Column sheet.DeleteColumn(8); #endregion #region ColumnWidth and RowHeight // Changing the Column Width sheet.Range["D5"].ColumnWidth = 15; // Changing the Row Height sheet.Range["D29"].RowHeight = 25; #endregion try { // Save the file if (Saveoption == "Xls") { return(excelEngine.SaveAsActionResult(workbook, "RowColumnManipulation.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } else { return(excelEngine.SaveAsActionResult(workbook, "RowColumnManipulation.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } } catch (Exception) { } //Close the workbook. workbook.Close(); excelEngine.Dispose(); } return(View()); }
/// <summary> /// Create the Excel document with form controls /// </summary> /// <returns>Return the created excel document as stream</returns> public MemoryStream FormControlsXlsIO(string version) { onlinePayments = new string[] { "Credit Card", "Net Banking" }; //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; //Check if user opts for XLSX if (version == "XLSX") { application.DefaultVersion = ExcelVersion.Excel2016; color1 = Color.FromArgb(255, 255, 230); } //Check if user opts for XLS else { color1 = Color.FromArgb(255, 255, 204); } //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //Workbook created with two worksheets IWorkbook workbook = application.Workbooks.Create(2); //The first worksheet object in the worksheets collection is accessed. //(0 based index) IWorksheet sheet2 = workbook.Worksheets[1]; //Assigning the array content to cells //by passing row and column position for (int i = 0; i < onlinePayments.Length; i++) { sheet2.SetValue(i + 1, 1, onlinePayments[i]); } //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; FileStream imageStream = new FileStream(ResolveApplicationImagePath("contact_sales.gif"), FileMode.Open, FileAccess.Read); sheet.Pictures.AddPicture(2, 3, imageStream); sheet[4, 3].Text = "Phone"; sheet[4, 3].CellStyle.Font.Bold = true; sheet[5, 3].Text = "Toll Free"; sheet[5, 5].Text = "1-888-9DOTNET"; sheet[6, 5].Text = "1-888-936-8638"; sheet[7, 5].Text = "1-919-481-1974"; sheet[8, 3].Text = "Fax"; sheet[8, 5].Text = "1-919-573-0306"; sheet[9, 3].Text = "Email"; sheet[10, 3].Text = "Sales"; //Creating the hyperlink in the 10th column and //5th row of the sheet IHyperLink link = sheet.HyperLinks.Add(sheet[10, 5]); link.Type = ExcelHyperLinkType.Url; link.Address = "mailto:[email protected]"; sheet[12, 3].Text = "Please fill out all required fields."; sheet[14, 5].Text = "First Name*"; sheet[14, 5].CellStyle.Font.Bold = true; sheet[14, 8].Text = "Last Name*"; sheet[14, 8].CellStyle.Font.Bold = true; //Create textbox for respective field //textbox to get First Name ITextBoxShape textBoxShape = sheet.TextBoxes.AddTextBox(15, 5, 23, 190); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; //textbox to get Last Name textBoxShape = sheet.TextBoxes.AddTextBox(15, 8, 23, 195); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; sheet[17, 3].Text = "Company*"; textBoxShape = sheet.TextBoxes.AddTextBox(17, 5, 23, 385); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; sheet[19, 3].Text = "Phone*"; textBoxShape = sheet.TextBoxes.AddTextBox(19, 5, 23, 385); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; sheet[21, 3].Text = "Email*"; textBoxShape = sheet.TextBoxes.AddTextBox(21, 5, 23, 385); textBoxShape.Fill.FillType = ExcelFillType.SolidColor; textBoxShape.Fill.ForeColor = color1; sheet[23, 3].Text = "Website"; textBoxShape = sheet.TextBoxes.AddTextBox(23, 5, 23, 385); ICheckBoxShape chkBoxProducts = sheet.CheckBoxes.AddCheckBox(25, 5, 20, 75); chkBoxProducts.Text = ""; sheet[25, 3].Text = "Multiple products?"; sheet[27, 3, 28, 3].Merge(); sheet[27, 3].Text = "Product(s)*"; sheet[27, 3].MergeArea.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; //Create a checkbox for each product ICheckBoxShape chkBoxProduct; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 5, 20, 75); chkBoxProduct.Text = "Studio"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 6, 20, 75); chkBoxProduct.Text = "Calculate"; chkBoxProduct.IsSizeWithCell = true; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 7, 20, 75); chkBoxProduct.Text = "Chart"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 8, 20, 75); chkBoxProduct.Text = "Diagram"; chkBoxProduct.IsSizeWithCell = true; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 9, 20, 75); chkBoxProduct.Text = "Edit"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 10, 20, 75); chkBoxProduct.Text = "XlsIO"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 5, 20, 75); chkBoxProduct.Text = "Grid"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 6, 20, 75); chkBoxProduct.Text = "Grouping"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 7, 20, 75); chkBoxProduct.Text = "HTMLUI"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 8, 20, 75); chkBoxProduct.Text = "PDF"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 9, 20, 75); chkBoxProduct.Text = "Tools"; chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 10, 20, 75); chkBoxProduct.Text = "DocIO"; chkBoxProducts.CheckState = ExcelCheckState.Mixed; //generate the link to linked cell property and formula GenerateFormula(excelEngine); sheet[30, 3].Text = "Selected Products Count"; //counts the selected product sheet[30, 5].Formula = "Sum(AA2:AA13)"; //align the cell content sheet[30, 5].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft; //create the textbox for additional information sheet[35, 3].Text = "Additional Information"; textBoxShape = sheet.TextBoxes.AddTextBox(32, 5, 150, 385); if (!(version == "XLS")) { sheet[43, 3].Text = "Online Payment"; //Create combobox IComboBoxShape comboBox1 = sheet.ComboBoxes.AddComboBox(43, 5, 20, 100); //Assign range to display in dropdown list comboBox1.ListFillRange = sheet2["A1:A2"]; //select 1st item from the list comboBox1.SelectedIndex = 1; sheet[46, 3].Text = "Card Type"; IOptionButtonShape optionButton1 = sheet.OptionButtons.AddOptionButton(46, 5); optionButton1.Text = "American Express"; optionButton1.CheckState = ExcelCheckState.Checked; optionButton1 = sheet.OptionButtons.AddOptionButton(46, 7); optionButton1.Text = "Master Card"; optionButton1 = sheet.OptionButtons.AddOptionButton(46, 9); optionButton1.Text = "Visa"; } //column alignment sheet.Columns[0].AutofitColumns(); sheet.Columns[3].ColumnWidth = 12; sheet.Columns[4].ColumnWidth = 10; sheet.Columns[5].ColumnWidth = 10; sheet.IsGridLinesVisible = false; sheet.DeleteRow(40); sheet.DeleteRow(41); sheet.DeleteRow(42); sheet.DeleteRow(45); //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); } } }
public int convertExceltoEMF(string path) { double sWidth = Command.thisCommand.getSheetWidth(); setSheetParameters(sWidth); string sheetSize = Command.thisCommand.getSheetSize(); if (sheetSize == "24 x 36") { formatHeight = 22.25; centerLine = 12.0; imagePerSheet = 4; } else if (sheetSize == "30 x 42") { formatHeight = 28.25; centerLine = 15.0; imagePerSheet = 5; } else if (sheetSize == "36 x 48") { formatHeight = 34.25; centerLine = 18.0; imagePerSheet = 6; } else { } double cellB; double cellC; double cellD; int resolution = 150; int pWidth; var savePath = Path.GetDirectoryName(path) + @"\Master Schedule (Images)\"; if (Directory.Exists(savePath)) { removeExistingImages(savePath); } else { Directory.CreateDirectory(savePath); } var saveName = "Master Schedule"; Command.thisCommand.dialog.ConvertingWord(); //Load the document. ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = excelEngine.Excel.Workbooks.OpenReadOnly(path); IWorksheet sheet = workbook.Worksheets[1]; //read widths of the cells we care about, B, C, and D cellB = sheet.GetColumnWidthInPixels(2); cellC = sheet.GetColumnWidthInPixels(3); cellD = sheet.GetColumnWidthInPixels(4); double cellWidth = cellB + cellC + cellD; float cellInches = Convert.ToSingle(cellWidth) / 72.0f; //store document width in pixels @ 150 DPI pWidth = Convert.ToInt32((cellInches * (float)resolution)); double multiplier = 994.0 / pWidth; sheet.SetColumnWidthInPixels(2, Convert.ToInt32(cellB * multiplier)); sheet.SetColumnWidthInPixels(3, Convert.ToInt32(cellC * multiplier)); sheet.SetColumnWidthInPixels(4, Convert.ToInt32(cellD * multiplier)); cellB = sheet.GetColumnWidthInPixels(2); cellC = sheet.GetColumnWidthInPixels(3); cellD = sheet.GetColumnWidthInPixels(4); cellWidth = cellB + cellC + cellD; cellInches = Convert.ToSingle(cellWidth) / 72.0f; //store document width in pixels @ 150 DPI pWidth = Convert.ToInt32((cellInches * (float)resolution)); //Read the last used row in the doc int lastRow = sheet.UsedRange.LastRow; //Resize fonts in every cell for (int x = 1; x <= lastRow; x++) { string searchRow = "A" + x.ToString(); double fntSize = sheet.Range[searchRow].CellStyle.Font.Size; var fgColor = sheet.Range[searchRow].CellStyle.PatternColorIndex; var bgColor = sheet.Range[searchRow].CellStyle.ColorIndex; //ExcelKnownColors checkColor = (ExcelKnownColors)65; fixCellSize(sheet, x); /*if (fntSize != 18) * { * if (bgColor == checkColor) * { * string searchRowB = "B" + x.ToString(); * double fontSize = sheet.Range[searchRowB].CellStyle.Font.Size; * if (fontSize != 10) * { * fixCellSize(sheet, x); * } * } * }*/ } string fullRange = "B1:D" + lastRow.ToString(); string lastRange = "D1:D" + lastRow.ToString(); // do some setup on the sheet sheet.IsGridLinesVisible = false; sheet.Range[lastRange].WrapText = true; //sheet.Range[lastRange].AutofitRows(); //sheet.Range[fullRange].AutofitRows(); //sheet.Range[lastRange].AutofitRows(); //delete hidden cells for (int rows = 1; rows <= lastRow; rows++) { bool row = sheet.IsRowVisible(rows); if (row == false) { sheet.DeleteRow(rows); rows--; } } lastRow = sheet.UsedRange.LastRow; //setup to figure out pages to publish double runningHeight = 0.0; ArrayList page = new ArrayList(); ArrayList startCell = new ArrayList(); ArrayList endCell = new ArrayList(); int sPage = 1; int sCell = 1; //seperate rows into pages for (int rows = 1; rows <= lastRow; rows++) { string content = sheet.Range["B1"].Text; double tempRow1 = sheet.GetRowHeight(1); double tempRow = sheet.GetRowHeightInPixels(rows) / 72.0; runningHeight = runningHeight + tempRow; if (runningHeight > formatHeight) { page.Add(sPage); startCell.Add(sCell); endCell.Add(rows - 1); sPage++; sCell = rows; runningHeight = tempRow; } if (rows == lastRow) { page.Add(sPage); startCell.Add(sCell); endCell.Add(rows); } } Command.thisCommand.dialog.setPageCount(page.Count); Command.thisCommand.dialog.SetupProgress(page.Count, "Task: Converting Excel Document to Images"); //save document by row ranges as images for (int i = 0; i <= page.Count - 1; i++) { int pageValue = i + 1; runningHeight = 0.0; int startingCell = int.Parse(startCell[i].ToString()); int endingCell = int.Parse(endCell[i].ToString()); for (int x = startingCell; x < endingCell; x++) { double tempRow = sheet.GetRowHeightInPixels(x) / 72.0; runningHeight = runningHeight + tempRow; } float pageHeight = Convert.ToSingle(runningHeight); pageHeight = pageHeight * 150; Image image = sheet.ConvertToImage(startingCell, 2, endingCell, 4, ImageType.Metafile, null); Bitmap bitmap = null; bitmap = new Bitmap(pWidth, Convert.ToInt32(pageHeight)); bitmap.SetResolution((float)resolution, (float)resolution); using (Graphics g = Graphics.FromImage(bitmap)) { g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.High; g.CompositingQuality = System.Drawing.Drawing2D.CompositingQuality.HighQuality; g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias; g.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias; g.DrawImage(image, 0, 0, pWidth, Convert.ToInt32(pageHeight)); g.Dispose(); } bitmap.Save(savePath + saveName + pageValue.ToString("D2") + ".png", ImageFormat.Png); bitmap.Dispose(); //setup progress for export //int percentage = (int)Math.Round(((double)i / (double)page.Count) * 100.0); //if (OnProgressUpdate != null) //{ // OnProgressUpdate(Convert.ToInt32(percentage)); //} //i++; Command.thisCommand.dialog.IncrementProgress(); bitmap = null; } //close document without saving workbook.Close(); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); return(page.Count); }
/// <summary> /// Export the List of Type /// </summary> /// <param name="isPrintPreview">if set to <c>true</c> [is print preview].</param> /// <param name="fileName">Name of the file.</param> /// <returns></returns> private bool OutReport <T>(List <IGrouping <string, T> > groupData, Dictionary <string, string> replaceValues, string groupBox, string viewName, bool isPrintPreview, string fileName) { string file = string.Empty; bool result = false; // Get template stream MemoryStream stream = GetTemplateStream(viewName); // Check if data is null if (stream == null) { return(false); } // Create excel engine ExcelEngine engine = new ExcelEngine(); IWorkbook workBook = engine.Excel.Workbooks.Open(stream); // Get sheets IWorksheet workSheet = workBook.Worksheets[0]; IWorksheet tmpSheet = workBook.Worksheets.Create(TMP_SHEET); // Copy template of group to temporary sheet IRange range = workSheet.Range[groupBox]; int rowCount = range.Rows.Count(); IRange tmpRange = tmpSheet.Range[groupBox]; range.CopyTo(tmpRange, ExcelCopyRangeOptions.All); // Replace value if (replaceValues != null && replaceValues.Count > 0) { // Find and replace values foreach (KeyValuePair <string, string> replacer in replaceValues) { Replace(workSheet, replacer.Key, replacer.Value); } } // Loop data for (int i = groupData.Count - 1; i >= 0; i--) { IGrouping <string, T> group = groupData[i]; List <T> listMember = group.ToList(); // Create template maker ITemplateMarkersProcessor markProcess = workSheet.CreateTemplateMarkersProcessor(); // Fill data into templates if (listMember.Count > 0) { markProcess.AddVariable(viewName, listMember); markProcess.ApplyMarkers(); } else { markProcess.ApplyMarkers(UnknownVariableAction.Skip); } // Insert template rows if (i > 0) { workSheet.InsertRow(range.Row, rowCount); tmpRange.CopyTo(workSheet.Range[groupBox], ExcelCopyRangeOptions.All); } } // Find row IRange[] rowSet = workSheet.FindAll(TMP_ROW, ExcelFindType.Text); // Delete row for (int i = rowSet.Count() - 1; i >= 0; i--) { range = rowSet[i]; // Delete if (range != null) { workSheet.DeleteRow(range.Row); } } // Get file name if (isPrintPreview) { file = Path.GetTempFileName() + Constants.FILE_EXT_XLS; } else { file = fileName; } // Remove temporary sheet workBook.Worksheets.Remove(tmpSheet); // Output file if (!FileCommon.IsFileOpenOrReadOnly(file)) { workBook.SaveAs(file); result = true; } // Close workBook.Close(); engine.Dispose(); // Print preview if (result && isPrintPreview) { PrintExcel(file); File.Delete(file); } return(result); }
/// <summary> /// Outs the simple report. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dataSource">The data source.</param> /// <param name="replaceValues">The replace values.</param> /// <param name="viewName">Name of the view.</param> /// <param name="isPrintPreview">if set to <c>true</c> [is print preview].</param> /// <param name="fileName">Name of the file.</param> /// <returns></returns> private bool OutSimpleReport <T>(List <T> dataSource, Dictionary <string, string> replaceValues, string viewName, bool isPrintPreview, ref string fileName) { string file = string.Empty; bool result = false; // Get template stream MemoryStream stream = GetTemplateStream(viewName); // Check if data is null if (stream == null) { return(false); } // Create excel engine ExcelEngine engine = new ExcelEngine(); IWorkbook workBook = engine.Excel.Workbooks.Open(stream); IWorksheet workSheet = workBook.Worksheets[0]; ITemplateMarkersProcessor markProcessor = workSheet.CreateTemplateMarkersProcessor(); // Replace value if (replaceValues != null && replaceValues.Count > 0) { // Find and replace values foreach (KeyValuePair <string, string> replacer in replaceValues) { Replace(workSheet, replacer.Key, replacer.Value); } } // Fill variables markProcessor.AddVariable(viewName, dataSource); // End template markProcessor.ApplyMarkers(UnknownVariableAction.ReplaceBlank); // Delete temporary row IRange range = workSheet.FindFirst(TMP_ROW, ExcelFindType.Text); // Delete if (range != null) { workSheet.DeleteRow(range.Row); } file = Path.GetTempFileName() + Constants.FILE_EXT_XLS; fileName = file; // Output file if (!FileCommon.IsFileOpenOrReadOnly(file)) { workBook.SaveAs(file); result = true; } // Close workBook.Close(); engine.Dispose(); // Print preview if (result && isPrintPreview) { PrintExcel(file); File.Delete(file); } return(result); }
private void btnCreate_Click(object sender, System.EventArgs e) { #region Workbook Initialization //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; //Get file path of input file string inputPath = GetFullTemplatePath("monthly_sales.xls"); //Open an existing spreadsheet which will be used as a template for creating new worksheet. //After opening, the workbook object represents the complete in-memory object model of the template spreadsheet. //IWorkbook workbook = application.Workbooks.Open(inputPath); IWorkbook workbook = application.Workbooks.Open(inputPath); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region Row and Column Manipulations #region Grouping and ungrouping // Grouping by Rows worksheet.Range["C5:F7"].Group(ExcelGroupBy.ByRows); // Grouping by Columns worksheet.Range["C10:F10"].Group(ExcelGroupBy.ByColumns); #endregion #region Hiding unhiding // Hiding fifth and sixth Column worksheet.ShowColumn(5, false); worksheet.ShowColumn(6, false); //Showing the 28th row worksheet.ShowRow(28, true); #endregion #region Insert and delete //Deleting Row worksheet.DeleteRow(25); //Inserting Column worksheet.InsertColumn(7, 1, ExcelInsertOptions.FormatAsBefore); worksheet.Range["G4"].Text = "Loss/Gain"; //Deleting Column worksheet.DeleteColumn(8); #endregion #region ColumnWidth and RowHeight // Changing the Column Width worksheet.Range["D5"].ColumnWidth = 15; // Changing the Row Height worksheet.Range["D29"].RowHeight = 25; #endregion #endregion #region Workbook Save //Set the default version as Excel 97to2003 if (this.rdbExcel97.Checked) { workbook.Version = ExcelVersion.Excel97to2003; fileName = "Sample.xls"; } //Set the default version as Excel 2007 else if (this.rdbExcel2007.Checked) { workbook.Version = ExcelVersion.Excel2007; fileName = "Sample.xlsx"; } //Set the default version as Excel 2010 else if (this.rdbExcel2010.Checked) { workbook.Version = ExcelVersion.Excel2010; fileName = "Sample.xlsx"; } //Set the default version as Excel 2010 else if (this.rdbExcel2013.Checked) { workbook.Version = ExcelVersion.Excel2013; fileName = "Sample.xlsx"; } //Saving the workbook to disk. workbook.SaveAs(fileName); #endregion #region Workbook Close and Dispose //Close the workbook. workbook.Close(); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); #endregion #region View the Workbook //Message box confirmation to view the created spreadsheet. if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] #if NETCORE System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName) { UseShellExecute = true }; process.Start(); #else Process.Start(fileName); #endif //Exit this.Close(); } else { // Exit this.Close(); } #endregion }
private int ProcessWorkSheetList( IProgressControl pc, IWorksheet sheet, T query, params string[] localParameterValues) { pc.SetStatus("Инициализация запроса..."); List <DbResult> results = query.SelectSimple(conn, pc, localParameterValues); pc.SetStatus("Идет обработка данных..."); int firstColNum = 0; int firstRowNum = 0; foreach (var cell in sheet.UsedCells) { string value = cell.Value; if (string.IsNullOrEmpty(value)) { continue; } if (value.Equals(string.Format("#{0}", query.Name)) || value.IndexOf(string.Format("#{0}(", query.Name)) >= 0) { firstRowNum = cell.LastRow; break; } else if (cell.Value.StartsWith("#")) { // Установка параметров в отчете ReplaceWithParameterValues(cell); } } string xlFieldValue; Dictionary <int, string> fieldPositions = new Dictionary <int, string>(); int j = 0; while (j < sheet.Rows[firstRowNum - 1].Columns.Length && sheet.Rows[firstRowNum - 1].Columns[j].Value != string.Empty) { fieldPositions[j] = sheet.Rows[firstRowNum - 1].Columns[j].Value; j++; } firstColNum = j; sheet.DeleteRow(firstRowNum); if (results.Count != 0) { sheet.InsertRow(firstRowNum, results.Count); } Func <DbResult, int, int, object> GetValue = (dbResult, xlRowNum, xlColNum) => { fieldPositions.TryGetValue(xlColNum, out xlFieldValue); // В ячейке формула if (xlFieldValue.StartsWith("=")) { return(EvaluateFormula(xlFieldValue, xlRowNum)); } if (xlFieldValue.StartsWith("#") && !xlFieldValue.Equals(string.Format("#{0}", query.Name))) { // В ячейке находится скалярная функция (запрос) var funcQuery = FindQuery(xlFieldValue); if (funcQuery != null) { return(funcQuery.ExecuteScalarSQL(conn, query.FieldNames, dbResult.Fields, ExtractParameterValues(xlFieldValue))); } } string searchFormat = xlColNum == 0 ? "#{0}" : "#:{0}:"; int index = query.FieldNames.FindIndex(fn => xlFieldValue.Equals(string.Format(searchFormat, fn))); if (index < 0) { return(xlFieldValue); } if (dbResult.Fields[index].Equals("$id")) { return(xlRowNum - firstRowNum + 2); } return(dbResult.Fields[index]); }; pc.SetProgress(0); int i = firstRowNum - 1; int progress = 0; foreach (DbResult result in results) { for (j = 0; j < firstColNum; j++) { sheet.Rows[i].Columns[j].Value2 = GetValue(result, i, j); sheet.Rows[i].Columns[j].BorderAround(ExcelLineStyle.Thin); } pc.SetProgress(progress++); i++; } for (j = 0; j < firstColNum; j++) { if (sheet.Rows[i].Columns[j].Value.StartsWith("=SUM", StringComparison.InvariantCultureIgnoreCase)) { string addressLocal = sheet.Rows[i].Columns[j].AddressLocal; string column = GetColumn(addressLocal); sheet.Rows[i].Columns[j].Formula = string.Format("=SUM({0}{1}:{2}{3})", column, firstRowNum, column, i); } } // Количество новых ячеек return(results.Count * query.FieldNames.Count); }
public static bool WriteCalculationsToExporterTemplate(ObservableCollection <Calculation3d> calcs, string path, ExporterTemplate template, bool asPdf = false ) { try { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; bool targetIsFolder = false; application.DefaultVersion = ExcelVersion.Excel2013; string ext = Path.GetExtension(path); targetIsFolder = string.IsNullOrEmpty(ext); string root = Path.GetDirectoryName(path); string filename = string.Format(@"{0}_{1}{2}", Regex.Replace(Strings.MyCalculation, "[^a-zA-Z0-9_]+", "_", RegexOptions.Compiled), Regex.Replace("1", "[^a-zA-Z0-9_]+", "_", RegexOptions.Compiled), string.IsNullOrEmpty(ext) ? asPdf ? ".pdf" : ".xlsx" : ext ); filename = getDuplicatedFileName(targetIsFolder ? path : root, filename); //Create a workbook IWorkbook workbook = application.Workbooks.Open(template.TemplatePath, ExcelOpenType.Automatic); IWorksheet worksheet = workbook.Worksheets[0]; string currencySymbol = CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol; foreach (ExporterSettings setting in template.Settings) { worksheet = workbook.Worksheets[setting.WorkSheetName]; switch (setting.Attribute.Property) { #region List case ExporterProperty.CalculationList: int insertRow = Convert.ToInt32(setting.Coordinates.Row); IStyle rowStyle = worksheet.Rows.ElementAt(insertRow).CellStyle; if (calcs.Count != 1) { worksheet.InsertRow(insertRow, calcs.Count - 1, ExcelInsertOptions.FormatAsAfter); } //Go to list for (int i = 0; i < calcs.Count; i++) { foreach (Printer3d printer in calcs[i].Printers) { foreach (Material3d material in calcs[i].Materials) { int Row = Convert.ToInt32(insertRow) + i; string Col = setting.Coordinates.Column; // Pos worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = i + 1; calcs[i].Material = material; calcs[i].Printer = printer; // Description StringBuilder sb = new StringBuilder(); sb.Append(calcs[i].Name); /* * sb.AppendLine(string.Format("Volume: {0} | Time: {1}", * Convert.ToDouble(calcs[i].Volume), * Convert.ToDouble(calcs[i].CalculatedPrintTime)) * ); */ Col = GetNextColumn(Col, 1); worksheet.Range[string.Format("{0}{1}", Col, Row)].Text = sb.ToString(); // Quantity Col = GetNextColumn(Col, 1); worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].Quantity); worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = "0"; // Single Col = GetNextColumn(Col, 1); worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = (Convert.ToDouble(calcs[i].TotalCosts) / (Convert.ToDouble(calcs[i].Quantity))); worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00"; // Total Col = GetNextColumn(Col, 1); worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].TotalCosts); worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00"; if (i < calcs.Count - 1) { } } } } // remove the empty rows if (calcs.Count != 1) { worksheet.DeleteRow(insertRow + calcs.Count, calcs.Count - 1); } break; #endregion } } if (asPdf) { //Open the Excel document to Convert ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook); //Initialize PDF document PdfDocument pdfDocument = new PdfDocument(); //Convert Excel document into PDF document pdfDocument = converter.Convert(); //Save the PDF file string savePath = targetIsFolder ? Path.Combine(path, filename) : Path.Combine(root, filename); pdfDocument.Save(savePath); } else { string savePath = targetIsFolder ? Path.Combine(path, filename) : Path.Combine(root, filename); workbook.SaveAs(savePath); } workbook.Close(); return(true); } } catch (Exception exc) { logger.Error(string.Format(Strings.EventExceptionOccurredFormated, exc.TargetSite, exc.Message)); return(false); } }
public static bool WriteCalculationsToTemplate(ObservableCollection <Calculation3d> calcs, string template, string path, ExcelWriteTemplateSettings Settings, bool asPdf = false ) { try { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; //Create a workbook IWorkbook workbook = application.Workbooks.Open(template, ExcelOpenType.Automatic); IWorksheet worksheet = workbook.Worksheets[0]; //Accessing first table in the sheet IListObject table = worksheet.ListObjects[0]; string currencySymbol = CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol; int insertRow = Convert.ToInt32(Settings.StartRow); IStyle rowStyle = worksheet.Rows.ElementAt(insertRow).CellStyle; worksheet.InsertRow(insertRow, calcs.Count - 1, ExcelInsertOptions.FormatAsAfter); //Go to list for (int i = 0; i < calcs.Count; i++) { int Row = Convert.ToInt32(Settings.StartRow) + i; //worksheet.Rows.ElementAt(Row).CellStyle = rowStyle; string Col = Settings.StartColumn; // Pos worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = i + 1; // Description StringBuilder sb = new StringBuilder(); sb.Append(calcs[i].Name); /* * sb.AppendLine(string.Format("Volume: {0} | Time: {1}", * Convert.ToDouble(calcs[i].Volume), * Convert.ToDouble(calcs[i].CalculatedPrintTime)) * ); */ Col = GetNextColumn(Col, 1); worksheet.Range[string.Format("{0}{1}", Col, Row)].Text = sb.ToString(); // Quantity Col = GetNextColumn(Col, 1); worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].Quantity); worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = "0"; // Single Col = GetNextColumn(Col, 1); worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = (Convert.ToDouble(calcs[i].TotalCosts) / (Convert.ToDouble(calcs[i].Quantity))); worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00"; // Total Col = GetNextColumn(Col, 1); worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].TotalCosts); worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00"; if (i < calcs.Count - 1) { var loc = table.Location; //worksheet.InsertRow(Row +1, 1, ExcelInsertOptions.FormatAsBefore); } } // remove the empty rows worksheet.DeleteRow(insertRow + calcs.Count - 1, calcs.Count - 1); if (asPdf) { //Open the Excel document to Convert ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook); //Initialize PDF document PdfDocument pdfDocument = new PdfDocument(); //Convert Excel document into PDF document pdfDocument = converter.Convert(); //Save the PDF file pdfDocument.Save(path); } else { workbook.SaveAs(path); } return(true); } } catch (Exception exc) { logger.Error(string.Format(Strings.EventExceptionOccurredFormated, exc.TargetSite, exc.Message)); return(false); } }