/// <summary> /// Generate the excel file /// </summary> /// <param name="meta"></param> override public bool Print(MetaFile meta) { npage = 0; nrecord = 0; nmeta = meta; mmfirst = System.DateTime.Now; bool aresult = base.Print(meta); int PageLimit = ToPage - 1; int FirstPage = FromPage - 1; Type objClassType; objClassType = Type.GetTypeFromProgID("Excel.Application"); object excel = Activator.CreateInstance(objClassType); object[] param1 = new object[1]; object wbs = excel.GetType().InvokeMember("Workbooks", System.Reflection.BindingFlags.GetProperty, null, excel, null); object wb = wbs.GetType().InvokeMember("Add", System.Reflection.BindingFlags.InvokeMethod, null, wbs, null); int shcount = 1; object shs = wb.GetType().InvokeMember("Sheets", System.Reflection.BindingFlags.GetProperty, null, wb, null); param1[0] = 1; object sh = shs.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, shs, param1); object cells = sh.GetType().InvokeMember("Cells", System.Reflection.BindingFlags.GetProperty, null, sh, null); object shfont = cells.GetType().InvokeMember("Font", System.Reflection.BindingFlags.GetProperty, null, cells, null); string FontName = System.Convert.ToString(shfont.GetType().InvokeMember("Name", System.Reflection.BindingFlags.GetProperty, null, shfont, null)); int FontSize = System.Convert.ToInt32(shfont.GetType().InvokeMember("Size", System.Reflection.BindingFlags.GetProperty, null, shfont, null)); int FCurrentPage = FirstPage; meta.RequestPage(int.MaxValue - 1); if (meta.Pages.CurrentCount <= FirstPage) { return(false); } if (ToPage > (meta.Pages.CurrentCount - 1)) { PageLimit = meta.Pages.CurrentCount - 1; } SetPageSize(meta.Pages[0].PageDetail); SetOrientation(meta.Orientation); int dpix = GraphicUtils.DefaultDPI; int dpiy = dpix; SortedList columns = new SortedList(); SortedList rows = new SortedList(); MetaPage apage; int i, index; // First pass to determine columns for (i = FirstPage; i <= PageLimit; i++) { apage = meta.Pages[i]; foreach (MetaObject obj1 in apage.Objects) { if ((obj1.MetaType == MetaObjectType.Text) || (obj1.MetaType == MetaObjectType.Image)) { string leftstring = ((double)obj1.Left / Precision).ToString("0000000000"); index = columns.IndexOfKey(leftstring); if (index < 0) { columns.Add(leftstring, null); } } } } int rowinit = 0; // Second pass determine rows for (i = FirstPage; i <= PageLimit; i++) { npage = i; if (!OneSheet) { rowinit = 0; int shcountactual = System.Convert.ToInt32(shs.GetType().InvokeMember("Count", System.Reflection.BindingFlags.GetProperty, null, shs, null)); if (shcountactual < shcount) { param1[0] = shcountactual; object lastsh = shs.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, shs, param1); object[] param4 = new object[4]; param4[0] = DBNull.Value; param4[1] = lastsh; param4[2] = 1; param4[3] = DBNull.Value; sh = shs.GetType().InvokeMember("Add", System.Reflection.BindingFlags.InvokeMethod, null, shs, param4); } else { param1[0] = shcount; sh = shs.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, shs, param1); } } else { rowinit = rowinit + rows.Count; } shcount++; rows.Clear(); apage = meta.Pages[i]; // Calculate rows nrecord = 0; foreach (MetaObject obj2 in apage.Objects) { if ((obj2.MetaType == MetaObjectType.Text) || (obj2.MetaType == MetaObjectType.Image)) { string topstring = ((double)obj2.Top / Precision).ToString("0000000000"); index = rows.IndexOfKey(topstring); if (index < 0) { rows.Add(topstring, null); } } } // Finally, draw objects foreach (MetaObject obj in apage.Objects) { if ((obj.MetaType == MetaObjectType.Text) || (obj.MetaType == MetaObjectType.Image)) { PrintObject(sh, apage, obj, dpix, dpiy, rows, columns, FontName, FontSize, rowinit, Precision); } nrecord++; CheckProgress(false); } } EndDocument(meta); CheckProgress(true); if (FileName.Length > 0) { object[] paramssav = null; if (!CheckVersion2010Up(excel)) { paramssav = new object[1]; } else { paramssav = new object[2]; // Excel 97 format ExcelFileFormat nformat = ExcelFormat; if (nformat == ExcelFileFormat.Auto) { nformat = FileFormatFromFilename(FileName); } paramssav[1] = (int)nformat; } paramssav[0] = FileName; // If xlsx extension, force WorkBookNormal wb.GetType().InvokeMember("SaveAs", System.Reflection.BindingFlags.InvokeMethod, null, wb, paramssav); } if (!Visible) { object[] paramclose = new object[1]; paramclose[0] = false; wb.GetType().InvokeMember("Close", System.Reflection.BindingFlags.InvokeMethod, null, wb, paramclose); excel.GetType().InvokeMember("Quit", System.Reflection.BindingFlags.InvokeMethod, null, excel, null); excel = null; wb = null; shs = null; sh = null; } else { param1[0] = Visible; excel.GetType().InvokeMember("Visible", System.Reflection.BindingFlags.SetProperty, null, excel, param1); } return(aresult); }
public MemoryStream CreateExcelFile(MatchSettingsViewModel options, List <DyntaxaMatchItem> items, ExcelFileFormat fileFormat) { var excelFile = new MatchExcelFile(); var dataTable = GetResultTable(items, options); return(excelFile.CreateExcelFile(dataTable, fileFormat, true)); }
/// <summary> /// Creates a memory stream of all factors for a taxon in a excel file /// </summary> /// <param name="user"></param> /// <param name="taxon"></param> /// <param name="dyntaxaFactors"></param> /// <param name="fileFormat"></param> /// <param name="showNonPublicData"></param> /// <returns></returns> public MemoryStream CreateSpeciesFactExcelFile(IUserContext user, ITaxon taxon, DyntaxaAllFactorData dyntaxaFactors, ExcelFileFormat fileFormat, bool showNonPublicData) { var excelFile = new SpeciesFactDataList(); return(excelFile.CreateSpeciesFactExcelFile(fileFormat, user, dyntaxaFactors, taxon, showNonPublicData)); }
/// <summary> /// Creates an excel file. /// Writes the content of a DataTable into a worksheet of an excelfile and save the file. /// </summary> /// <param name="dataTables"> /// The DataTables object that should be exported to an excel file. /// </param> /// <param name="fileFormat"> /// File format. /// </param> /// <param name="autosizeColumnWidth"> /// If true, the columns will be autosized. /// </param> /// <returns> /// The <see cref="MemoryStream"/>. /// </returns> public MemoryStream CreateExcelFile(List <System.Data.DataTable> dataTables, ExcelFileFormat fileFormat, bool autosizeColumnWidth = false) { MemoryStream memoryStream; ExcelWorksheet worksheet = null; memoryStream = new MemoryStream(); try { using (ExcelPackage package = new ExcelPackage(memoryStream)) { for (int tableIndex = 0; tableIndex < dataTables.Count; tableIndex++) { var dataTable = dataTables[tableIndex]; if (package.Workbook.Worksheets.Count <= tableIndex) { worksheet = package.Workbook.Worksheets.Add(dataTable.TableName); } for (Int32 colIndex = 0; colIndex < dataTable.Columns.Count; colIndex++) { worksheet.Cells[1, colIndex + 1].Value = dataTable.Columns[colIndex].Caption; } // Formating straight columns if (IsColumnHeaderBackgroundUsed) { using (var range = worksheet.Cells[1, 1, 1, dataTable.Columns.Count]) { range.Style.Font.Bold = true; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(ExcelHelper.ColorTable[15]); } } for (Int32 rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++) { for (Int32 colIndex = 0; colIndex < dataTable.Columns.Count; colIndex++) { worksheet.Cells[rowIndex + 2, colIndex + 1].Value = dataTable.Rows[rowIndex][colIndex]; } } if (autosizeColumnWidth) { worksheet.Cells.AutoFitColumns(0); } } package.Save(); } memoryStream.Position = 0; return(memoryStream); } catch (Exception) { if (memoryStream != null) { memoryStream.Dispose(); } throw; } }