private void btnExtractData_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 the input file path string inputPath = GetFullTemplatePath("FindAndExtract.xls"); //Open an existing spreadsheet which will be used as a template for getting data from input file. //After opening, the workbook object represents the complete in-memory object model of the template spreadsheet. IWorkbook workbook = application.Workbooks.Open(inputPath, ExcelOpenType.Automatic); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region Find and Extract Data IRange result; #region Find and Extract Numbers if (lstFormat.SelectedItem.ToString() == "Number with format 0.00") { result = worksheet.FindFirst(1000000.00075, ExcelFindType.Number); //Gets the cell display text txtDisplay.Text = result.DisplayText.ToString(); //Gets the cell value txtValue.Text = result.Value2.ToString(); } if (lstFormat.SelectedItem.ToString() == "Number with format $#,##0.00") { result = worksheet.FindFirst(3.2, ExcelFindType.Number); //Gets the cell display text txtDisplay.Text = result.DisplayText.ToString(); //Gets the cell value txtValue.Text = result.Value2.ToString(); } #endregion #region Find and Extract DateTime if (lstFormat.SelectedItem.ToString() == "DateTime with format m/d/yy h:mm") { result = worksheet.FindFirst(DateTime.Parse("12/1/2007 1:23:00 AM", CultureInfo.InvariantCulture)); //Gets the cell display text txtDisplay.Text = result.DisplayText.ToString(); //Gets the cell value txtValue.Text = result.Value2.ToString(); } if (lstFormat.SelectedItem.ToString() == "Time with format h:mm:ss AM/PM") { result = worksheet.FindFirst(DateTime.Parse("1/1/2007 2:23:00 AM", CultureInfo.InvariantCulture)); //Gets the cell display text txtDisplay.Text = result.DisplayText.ToString(); //Gets the cell value txtValue.Text = result.DateTime.ToString(); } if (lstFormat.SelectedItem.ToString() == "Date with format d-mmm-yy") { result = worksheet.FindFirst(DateTime.Parse("12/4/2007 1:23:00 AM", CultureInfo.InvariantCulture)); //Gets the cell display text txtDisplay.Text = result.DisplayText.ToString(); //Gets the cell value txtValue.Text = result.Value2.ToString(); } if (lstFormat.SelectedItem.ToString() == "Date with format Saturday, December 01, 2007") { result = worksheet.FindFirst(DateTime.Parse("8/1/2007 3:23:00 AM", CultureInfo.InvariantCulture)); //Gets the cell display text txtDisplay.Text = result.DisplayText.ToString(); //Gets the cell value txtValue.Text = result.Value2.ToString(); } #endregion #region Find and Extract Text if (lstFormat.SelectedItem.ToString() == "Text") { result = worksheet.FindFirst("Simple text", ExcelFindType.Text); //Gets the cell display text txtDisplay.Text = result.DisplayText.ToString(); //Gets the cell value txtValue.Text = result.Value2.ToString(); } if (lstFormat.SelectedItem.ToString() == "Text With Styles and Patterns") { result = worksheet.FindFirst("Text with Styles and patterns", ExcelFindType.Text); //Gets the cell display text txtDisplay.Text = result.DisplayText.ToString(); //Gets the cell value txtValue.Text = result.Value2.ToString(); } if (lstFormat.SelectedItem.ToString() == "Number with Text Format") { result = worksheet.FindFirst("$8.200", ExcelFindType.Text); //Gets the cell display text txtDisplay.Text = result.DisplayText.ToString(); //Gets the cell value txtValue.Text = result.Value2.ToString(); } #endregion #endregion #region Workbook Close and Dispose //close the workbook workbook.Close(); excelEngine.Dispose(); #endregion }
public ActionResult FindAndExtract(string selectoption) { if (selectoption == null) { FillData(); 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; //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 = application.Workbooks.Open(ResolveApplicationDataPath("FindAndExtract.xls")); //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; IRange result; try { switch (selectoption) { #region Find and Extract Numbers case "Number with format 0.00": { result = sheet.FindFirst(1000000.00075, ExcelFindType.Number); //Gets the cell display text ViewData.Add("displayText", result.DisplayText.ToString()); //Gets the cell value ViewData.Add("valueText", result.Value2.ToString()); break; } case "Number with format $#,##0.00": { result = sheet.FindFirst(3.2, ExcelFindType.Number); //Gets the cell display text ViewData.Add("displayText", result.DisplayText.ToString()); //Gets the cell value ViewData.Add("valueText", result.Value2.ToString()); break; } #endregion #region Find and Extract DateTime case "DateTime with format m/d/yy h:mm": { result = sheet.FindFirst(DateTime.Parse("12/1/2007 1:23:00 AM", CultureInfo.InvariantCulture)); //Gets the cell display text ViewData.Add("displayText", result.DisplayText.ToString()); //Gets the cell value ViewData.Add("valueText", result.Value2.ToString()); break; } case "Time with format h:mm:ss AM/PM": { result = sheet.FindFirst(DateTime.Parse("1/1/2007 2:23:00 AM", CultureInfo.InvariantCulture)); //Gets the cell display text ViewData.Add("displayText", result.DisplayText.ToString()); //Gets the cell value ViewData.Add("valueText", result.DateTime.ToString()); break; } case "Date with format d-mmm-yy": { result = sheet.FindFirst(DateTime.Parse("12/4/2007 1:23:00 AM", CultureInfo.InvariantCulture)); //Gets the cell display text ViewData.Add("displayText", result.DisplayText.ToString()); //Gets the cell value ViewData.Add("valueText", result.Value2.ToString()); break; } case "Date with format Saturday, December 01, 2007": { result = sheet.FindFirst(DateTime.Parse("8/1/2007 3:23:00 AM", CultureInfo.InvariantCulture)); //Gets the cell display text ViewData.Add("displayText", result.DisplayText.ToString()); //Gets the cell value ViewData.Add("valueText", result.Value2.ToString()); break; } #endregion #region Find and Extract Text case "Text": { result = sheet.FindFirst("Simple text", ExcelFindType.Text); //Gets the cell display text ViewData.Add("displayText", result.DisplayText.ToString()); //Gets the cell value ViewData.Add("valueText", result.Value2.ToString()); break; } case "Text With Styles and Patterns": { result = sheet.FindFirst("Text with Styles and patterns", ExcelFindType.Text); //Gets the cell display text ViewData.Add("displayText", result.DisplayText.ToString()); //Gets the cell value ViewData.Add("valueText", result.Value2.ToString()); break; } case "Number with Text Format": { result = sheet.FindFirst("$8.200", ExcelFindType.Text); //Gets the cell display text ViewData.Add("displayText", result.DisplayText.ToString()); //Gets the cell value ViewData.Add("valueText", result.Value2.ToString()); break; } #endregion default: break; } workbook.Close(); excelEngine.Dispose(); } catch (Exception) { } FillData(); return(View()); }
/// <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 string Export(string saveOption, List <UserEventLogSearchResult> eventLogList, UserEventLogSearchCondition model) { string pathExport = string.Empty; // Khỏi tạo bảng excel ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(HttpContext.Current.Server.MapPath("/Template/LichSuTruyCapSuDung.xlsx")); IWorksheet sheet = workbook.Worksheets[0]; //Thay đổi title trong báo cáo // Utiliti.FillTitleExport(db, sheet); //DateTime dateNow = DateTime.Now; //IRange rangeDay = sheet.FindFirst("<Day>", ExcelFindType.Text, ExcelFindOptions.MatchCase); //rangeDay.Text = rangeDay.Text.Replace("<Day>", dateNow.Day.ToString()); //IRange rangeMonth = sheet.FindFirst("<Month>", ExcelFindType.Text, ExcelFindOptions.MatchCase); //rangeMonth.Text = rangeMonth.Text.Replace("<Month>", dateNow.Month.ToString()); //IRange rangeYear = sheet.FindFirst("<Year>", ExcelFindType.Text, ExcelFindOptions.MatchCase); //rangeYear.Text = rangeYear.Text.Replace("<Year>", dateNow.Year.ToString()); IRange rangeDateFrom = sheet.FindFirst("<DateFrom>", ExcelFindType.Text, ExcelFindOptions.MatchCase); rangeDateFrom.Text = rangeDateFrom.Text.Replace("<DateFrom>", (model.LogDateFrom.HasValue ? model.LogDateFrom.Value.ToString("dd/MM/yyy") : "--/--/----")); IRange rangeDateTo = sheet.FindFirst("<DateTo>", ExcelFindType.Text, ExcelFindOptions.MatchCase); rangeDateTo.Text = rangeDateTo.Text.Replace("<DateTo>", (model.LogDateTo.HasValue ? model.LogDateTo.Value.ToString("dd/MM/yyy") : "--/--/----")); int total = eventLogList.Count; int index = 1; var listExport = (from a in eventLogList select new { Index = index++, a.LogTypeName, // a.UserType, a.Description, CreateDate = a.CreateDate.HasValue ? a.CreateDate.Value.ToString("dd-MM-yyyy HH:mm:ss") : string.Empty, a.UserName }).ToList(); IRange iRangeData = sheet.FindFirst("<Data>", ExcelFindType.Text, ExcelFindOptions.MatchCase); sheet.ImportData(listExport, iRangeData.Row, iRangeData.Column, false); if (saveOption.Equals("PDF")) { sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders.Color = ExcelKnownColors.Black; pathExport = "/Template/Export/" + DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "LichSuTruyCapSuDung.pdf"; //convert the sheet to pdf ExcelToPdfConverter converter = new ExcelToPdfConverter(sheet); PdfDocument pdfDocument = new PdfDocument(); pdfDocument = converter.Convert(); pdfDocument.Save(HttpContext.Current.Server.MapPath(pathExport)); pdfDocument.Close(); converter.Dispose(); workbook.Close(); excelEngine.Dispose(); } else { pathExport = "/Template/Export/" + DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "LichSuTruyCapSuDung.xlsx"; workbook.SaveAs(HttpContext.Current.Server.MapPath(pathExport)); } return(pathExport); }