/// <summary> /// Replaces the data work sheet. /// </summary> /// <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 ReplaceDataWorkSheet(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); } } 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); }
/// <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); }