private void ExportCommon(OrderedDictionary columns, string sql, ExcelPackage xl, string sheetName) { int colIndex; var ws = xl.Workbook.Worksheets.Add(sheetName); using (IDbConnection conn = _sys.NewDBConnection()) { conn.Open(); using (IDataReader dr = _sys.ExecuteReader(conn, sql)) { if (!string.IsNullOrEmpty(_sys.LastError)) { throw new Exception(_sys.LastError); } string[] keys = new string[columns.Count]; columns.Keys.CopyTo(keys, 0); int rowIndex = 2; while (dr.Read()) { for (colIndex = 1; colIndex <= columns.Count; colIndex++) { ws.Cells[rowIndex, colIndex].Value = dr[keys[colIndex - 1]]; } rowIndex++; } dr.Close(); } conn.Close(); } ws.Cells["A1:H1"].AutoFilter = true; ws.Column(5).Style.Numberformat.Format = "MM/dd/yyyy"; //TODO: put this in the config file ws.Column(8).Style.Numberformat.Format = "MM/dd/yyyy"; //TODO: put this in the config file for (colIndex = 1; colIndex <= columns.Count; colIndex++) { ws.Cells[1, colIndex].Value = columns[colIndex - 1]; ws.Column(colIndex).AutoFit(); ws.Column(colIndex).Style.WrapText = true; } }
private bool ExportForOPT(DateTime startDate, DateTime endDate, ExcelPackage xl, string lang) { OrderedDictionary columns = new OrderedDictionary() { { "cityname", "Destination" }, { "productsubtypename", "Type" }, { "operatorname", "Supplier" }, { "title", "Product Name" }, { "code", "Product Code" }, { "catdesc", "Category" }, { "cat", "Category Code" }, { "validity", "Validity" }, { "net", "Agent Net" }, { "retail", "Retail Price" }, { "chd limit", "Child Age" }, { "chd grate", "Child Net" }, { "childretail", "Child Retail" }, { "jr limit", "Junior Age" }, { "jr grate", "Junior Net" }, { "juniorretail", "Junior Retail" }, { "duration", "Duration" }, }; endDate = endDate.AddDays(1).AddSeconds(-1); using (IDbConnection conn = _sys.NewDBConnection()) { conn.Open(); using (IDbCommand cmd = _sys.NewDBCommand(conn)) { cmd.CommandText = "usp_ProductList"; cmd.CommandType = CommandType.StoredProcedure; AddParameter(cmd, DbType.DateTime, "@startDate", startDate); AddParameter(cmd, DbType.DateTime, "@endDate", endDate); AddParameter(cmd, DbType.String, "@lang", lang); return(ExportCommon(columns, cmd, xl, lang)); } } }
private void Export(DateTime startDate, DateTime endDate, ExcelPackage xl) { Dictionary <string, string> agencies = new Dictionary <string, string> { { "8572040812", "LPG" }, { "7026802096", "Expedia" }, { "2625068382", "Musement" }, { "8556648235", "GYG" }, { "7027495698", "Viator" }, { "HOTELBEDS", "HotelBeds" }, { "REZDY", "Rezdy" }, { "R-VENETIAN", "Venetian" }, }; string agencyList = string.Join(",", agencies.Select(x => $"'{x.Key}'")); Dictionary <string, ExcelWorksheet> sheets = new Dictionary <string, ExcelWorksheet>(); foreach (var item in agencies) { sheets.Add(item.Key, xl.Workbook.Worksheets.Add(item.Value)); } Dictionary <string, string> columns = new Dictionary <string, string>() { { "res no", "Trip #" }, { "item", "Item" }, { "res agt", "Res Agent" }, { "tripremarks", "Trip Remarks" }, { "itemremarks", "Item Remarks" }, { "reference", "Reference" }, { "doc nbr", "Doc Nbr" }, { "last name", "Last Name" }, { "first name", "First Name" }, { "code", "Code" }, { "description", "Description" }, { "category", "Category" }, { "catdesc", "Category Description" }, { "start date", "Service Date" }, { "res date", "Booking Date" }, { "cancelled", "Cancelled" }, { "net", "Amount" }, { "cost", "Cost" }, }; string[] keys = new string[columns.Count]; columns.Keys.CopyTo(keys, 0); //Create another dictionary with the same keys holding the index of the current row in each sheet int colCounter = 1; Dictionary <string, int> rowCounters = new Dictionary <string, int>(); foreach (var sheet in sheets) { colCounter = 1; foreach (var key in columns) { sheet.Value.Cells[1, colCounter].Value = key.Value; sheet.Value.Cells[1, colCounter].Style.Font.Bold = true; colCounter++; } rowCounters.Add(sheet.Key, 2); //row 1 is the column header } string dateField = $"[{radioGroupDate.EditValue}]"; endDate = endDate.AddDays(1).AddSeconds(-1); string sql = $@"SELECT reshdr.[Res No], resitm.Item, resitm.[Res Agt], reshdr.InternalRemarks as TripRemarks, resitm.InternalRemarks as ItemRemarks, reshdr.Reference, psgrlist.[Doc Nbr], psgrlist.[Last Name], psgrlist.[First Name], Code, DESCRIP AS description, resroom.CAT AS Category, resroom.catdesc, [Strt Date] AS [Start Date], resitm.[Res Date], CASE WHEN resitm.Inactive = 1 THEN 'Yes' ELSE 'No' END AS Cancelled, resitm.agency, resitm.Net, resnight.Cost FROM resitm INNER JOIN reshdr ON resitm.[res no] = reshdr.[res no] INNER JOIN psgrres ON reshdr.[res no] = psgrres.[res no] and psgrres.[client level] = 1 INNER JOIN psgrlist ON psgrres.[CLIENT NO] = psgrlist.[CLIENT NO] INNER JOIN resroom ON resroom.[res no] = resitm.[res no] and resroom.item = resitm.item and resroom.room = 0 INNER JOIN resnight ON resnight.[res no] = resitm.[res no] and resnight.item = resitm.item and resnight.room = 0 and resnight.night = 0 WHERE AGENCY in ({agencyList}) AND resitm.{dateField} >= '{startDate}' AND resitm.{dateField} <= '{endDate}' ORDER BY {dateField}"; sql = sql.Replace(Environment.NewLine, string.Empty); using (IDbConnection conn = _sys.NewDBConnection()) { conn.Open(); using (IDataReader dr = _sys.ExecuteReader(conn, sql)) { if (!string.IsNullOrEmpty(_sys.LastError)) { throw new Exception(_sys.LastError); } while (dr.Read()) { string agency = dr["agency"].ToString(); var ws = sheets[agency]; rowCounters[agency] = AddRow(dr, ws, keys, rowCounters[agency]); } dr.Close(); } conn.Close(); } foreach (var sheet in sheets) { var ws = sheet.Value; ws.Cells[sheet.Value.Dimension.Address].AutoFilter = true; ws.Column(13).Style.Numberformat.Format = "MM/dd/yyyy"; //TODO: put this in the config file ws.Column(14).Style.Numberformat.Format = "MM/dd/yyyy"; //TODO: put this in the config file for (colCounter = 1; colCounter < keys.Count(); colCounter++) { sheet.Value.Column(colCounter).AutoFit(); sheet.Value.Column(colCounter).Style.WrapText = true; } } }