void ReferenceAfterCopy2() { using (var wb = ReoGridControl.CreateMemoryWorkbook()) { AssertEquals(wb.Worksheets.Count, 1); wb.Worksheets[0]["A1"] = 10; wb.Worksheets[0]["B1"] = "=Sheet1!A1"; wb.CopyWorksheet(0, 1); AssertSame(wb.Worksheets.Count, 2); AssertSame(wb.Worksheets[0].Name, "Sheet1"); AssertSame(wb.Worksheets[1].Name, "Sheet2"); AssertSame(wb.Worksheets[0]["A1"], 10); AssertSame(wb.Worksheets[1]["A1"], 10); AssertSame(wb.Worksheets[0]["B1"], 10); AssertSame(wb.Worksheets[1]["B1"], 10); wb.Worksheets[0]["A1"] = 20; wb.Recalculate(); AssertSame(wb.Worksheets[0]["B1"], 20); AssertSame(wb.Worksheets[1]["B1"], 20); wb.Worksheets[1]["A1"] = 30; wb.Recalculate(); AssertSame(wb.Worksheets[0]["B1"], 20); AssertSame(wb.Worksheets[1]["B1"], 20); } }
private bool CreateTransactionReport(string destination, DateTime start, DateTime end) { var result = true; var start_row = 5; using (var workbook = ReoGridControl.CreateMemoryWorkbook()) using (var context = new HotelContext()) { if (string.IsNullOrEmpty(destination)) { return(false); } var worksheet = workbook.Worksheets[0]; var transactionList = new List <Transaction>(); try { transactionList = context.Transactions .Where(t => t.TransactionAt >= start.Date) .Where(t => t.TransactionAt < end.Date) .ToList(); } catch { result = false; } try { worksheet.Cells[start_row, 0].Data = "Date"; worksheet.Cells[start_row, 1].Data = "Description"; worksheet.Cells[start_row, 2].Data = "In"; worksheet.Cells[start_row, 3].Data = "Out"; for (int i = 1; i <= transactionList.Count; i++) { var rowNum = start_row + i; var transaction = transactionList[i - 1]; if (rowNum >= worksheet.RowCount - 1) { worksheet.RowCount = rowNum + 2; } worksheet.Cells[rowNum, 0].Data = transaction.TransactionAt; worksheet.Cells[rowNum, 1].Data = transaction.Description; worksheet.Cells[rowNum, 2].Data = transaction.AmmountIn; worksheet.Cells[rowNum, 3].Data = transaction.AmmountOut; worksheet.Cells[rowNum, 0].DataFormat = CellDataFormatFlag.DateTime; } workbook.Save(destination); } catch { result = false; } } return(result); }
public void Create(string fileName) { this.fileName = fileName; if (workbook == null) { workbook = ReoGridControl.CreateMemoryWorkbook(); } }
private void ProcessDB() { using (var workbook = ReoGridControl.CreateMemoryWorkbook()) { try { var workshit = workbook.CreateWorksheet("Final"); var items = TmpItems; var grp_material = (from a in items group a by new { a.AF, a.AH, a.AL, } into b select new MaterialClass() { MatlNo = b.Key.AF, Unit = b.Key.AH, SupplierCod = b.Key.AL, ListItems = b.ToList(), }).ToList(); var grp_item = (from a in items group a by new { a.A, a.AF } into b select new POClass { Material = b.Key.AF, Week = b.Key.A, POs = b.ToList() }).ToList(); Invoke(new UpdateStatus(UpdateStat), $"Creating Header ({grp_material.Count})...", 0); GenerateHeader(workshit, grp_material); GenerateOutput(workshit, grp_item, grp_material); workbook.AddWorksheet(workshit); workbook.RemoveWorksheet(0); Invoke(new UpdateTables(UpdateWorkbook), workbook, true); } catch (Exception ex) { Invoke(new UpdateTables(UpdateWorkbook), null, false); MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } GC.Collect(); }
private async void btn_Input_BtnClick(object sender, EventArgs e) { using (OpenFileDialog dialog = new OpenFileDialog()) { dialog.Multiselect = false; dialog.Title = "请选择文件"; dialog.Filter = "Excel表格(*.xlsx)|*.xlsx"; if (dialog.ShowDialog() == DialogResult.OK) { string file = dialog.FileName; try { var workbook = ReoGridControl.CreateMemoryWorkbook(); workbook.Load(file); var sheet = workbook.Worksheets[0]; List <Card> cards = new List <Card>(); for (int i = 1; i < sheet.RowCount; i++) { if (string.IsNullOrEmpty(sheet[i, 0].GetString()) == false && int.TryParse(sheet[i, 4].GetString(), out var points)) { var card = new Card() { CardId = sheet[i, 0].GetString(), PhoneNumber = sheet[i, 1].GetString(), UserName = sheet[i, 2].GetString(), CardType = CardType.积分卡, Points = points, Money = 0, IsActive = sheet[i, 6].GetString() == "正常" ? true : false }; cards.Add(card); } } using (var db = new MemberCardContext()) { db.Cards.AddRange(cards); await db.SaveChangesAsync(); } FrmDialog.ShowDialog(this, "导入成功"); } catch (Exception ex) { FrmDialog.ShowDialog(this, ex.Message); } } } }
private void btn_Output_BtnClick(object sender, EventArgs e) { using (SaveFileDialog sfd = new SaveFileDialog()) { sfd.Filter = "Excel表格(*.xlsx)|*.xlsx"; sfd.FilterIndex = 1; sfd.RestoreDirectory = true; sfd.FileName = "会员卡数据" + DateTime.Now.ToString("yyyyMMddHHmmss"); if (sfd.ShowDialog() == DialogResult.OK) { var workbook = ReoGridControl.CreateMemoryWorkbook(); var sheet = workbook.Worksheets[0]; sheet.SetRows(_cards.Count + 2); sheet.SetCols(7); sheet[0, 0] = "会员卡号"; sheet[0, 1] = "手机号码"; sheet[0, 2] = "会员姓名"; sheet[0, 3] = "卡类型"; sheet[0, 4] = "当前积分"; sheet[0, 5] = "余额"; sheet[0, 6] = "会员卡状态"; for (int i = 0; i < _cards.Count; i++) { sheet[i + 1, 0] = _cards[i].CardId; sheet[i + 1, 1] = _cards[i].PhoneNumber; sheet[i + 1, 2] = _cards[i].UserName; sheet[i + 1, 3] = _cards[i].CardType; sheet[i + 1, 4] = _cards[i].Points; sheet[i + 1, 5] = _cards[i].Money; sheet[i + 1, 6] = _cards[i].IsActive == true ? "正常" : "锁定"; } try { workbook.Save(sfd.FileName); FrmDialog.ShowDialog(this, "导出成功"); } catch (Exception ex) { FrmDialog.ShowDialog(this, ex.Message); } } } }
void LastBordersInputOutput() { var mb = ReoGridControl.CreateMemoryWorkbook(); var sheet = mb.Worksheets[0]; sheet.Resize(10, 10); sheet.SetRangeBorders("A1:J10", BorderPositions.Right | BorderPositions.Bottom, RangeBorderStyle.BlackDotted); AssertSame(sheet.UsedRange, new RangePosition("A1:J10")); sheet.SaveRGF(GetRGFFileName("FrozenCellsInputOutput")); sheet.Reset(); sheet.LoadRGF(GetRGFFileName("FrozenCellsInputOutput")); AssertSame(sheet.UsedRange, new RangePosition("A1:J10")); var borders = sheet.GetRangeBorders("J10"); AssertSame(borders.Right, RangeBorderStyle.BlackDotted); AssertSame(borders.Bottom, RangeBorderStyle.BlackDotted); }
void FrozenWorkbookRGF() { // https://reogrid.net/forum/viewtopic.php?id=225 var wb = ReoGridControl.CreateMemoryWorkbook(); var sheet = wb.Worksheets[0]; sheet["A1"] = "Hello"; sheet.FreezeToCell("C3"); sheet["D4"] = 12345; sheet.SaveRGF("RGF_MB_Frozen.rgf"); sheet.Reset(); sheet.LoadRGF("RGF_MB_Frozen.rgf"); AssertEquals(sheet["A1"], "Hello"); AssertSame(sheet["D4"], 12345); AssertEquals(sheet.FreezePos, new CellPosition("C3"), "sheet.FreezePos = C3"); }
private bool CreateBookingReport(string destination, DateTime start, DateTime end) { var result = true; var start_row = 5; using (var workbook = ReoGridControl.CreateMemoryWorkbook()) using (var context = new HotelContext()) { if (string.IsNullOrEmpty(destination)) { return(false); } var worksheet = workbook.Worksheets[0]; var transactionList = new List <Booking>(); try { transactionList = context.Bookings .Include(t => t.Type) .Include(t => t.Room) .Include(t => t.Guest) .Where(t => t.BookingAt >= start.Date) .Where(t => t.BookingAt < end.Date) .Where(t => t.CheckoutAt.HasValue) .ToList(); } catch { result = false; } try { worksheet.Cells[start_row, 0].Data = "Booking Date"; worksheet.Cells[start_row, 1].Data = "Arrival Date"; worksheet.Cells[start_row, 2].Data = "Departure Date"; worksheet.Cells[start_row, 3].Data = "Checkin Date"; worksheet.Cells[start_row, 4].Data = "Checkout Date"; worksheet.Cells[start_row, 5].Data = "Booking Type"; worksheet.Cells[start_row, 6].Data = "Room Number"; worksheet.Cells[start_row, 7].Data = "Guest Id"; worksheet.Cells[start_row, 8].Data = "Guest Name"; worksheet.Cells[start_row, 9].Data = "Total Adult"; worksheet.Cells[start_row, 10].Data = "Total Child"; worksheet.Cells[start_row, 11].Data = "Total Price"; worksheet.Cells[start_row, 12].Data = "Total Pay"; for (int i = 1; i <= transactionList.Count; i++) { var bookingData = transactionList[i - 1]; var rowNum = start_row + i; if (rowNum >= worksheet.RowCount - 1) { worksheet.RowCount = rowNum + 2; } worksheet.Cells[rowNum, 0].Data = bookingData.BookingAt; worksheet.Cells[rowNum, 1].Data = bookingData.ArrivalDate; worksheet.Cells[rowNum, 2].Data = bookingData.DepartureDate; worksheet.Cells[rowNum, 3].Data = bookingData.CheckinAt; worksheet.Cells[rowNum, 4].Data = bookingData.CheckoutAt; worksheet.Cells[rowNum, 5].Data = bookingData.Type.TypeName; worksheet.Cells[rowNum, 6].Data = bookingData.Room.RoomNumber; worksheet.Cells[rowNum, 7].Data = bookingData.Guest.IdNumber; worksheet.Cells[rowNum, 8].Data = bookingData.Guest.Fullname; worksheet.Cells[rowNum, 9].Data = bookingData.CountAdult; worksheet.Cells[rowNum, 10].Data = bookingData.CountChild; worksheet.Cells[rowNum, 11].Data = GetPrice(bookingData.Id); worksheet.Cells[rowNum, 12].Data = GetPay(bookingData.Id); worksheet.Cells[rowNum, 0].DataFormat = CellDataFormatFlag.DateTime; worksheet.Cells[rowNum, 1].DataFormat = CellDataFormatFlag.DateTime; worksheet.Cells[rowNum, 2].DataFormat = CellDataFormatFlag.DateTime; worksheet.Cells[rowNum, 3].DataFormat = CellDataFormatFlag.DateTime; worksheet.Cells[rowNum, 4].DataFormat = CellDataFormatFlag.DateTime; worksheet.Cells[rowNum, 6].DataFormat = CellDataFormatFlag.Text; worksheet.Cells[rowNum, 7].DataFormat = CellDataFormatFlag.Text; } workbook.Save(destination); } catch { result = false; } } return(result); }
public object Export(params object[] datas) { var param = datas[0] as ExpandoObject; var dict = ((IDictionary <string, Object>)param); using (var grid = ReoGridControl.CreateMemoryWorkbook()) { try { var items = dict["items"] as List <object>; var worksheet = grid.Worksheets[0]; if (items is null) { return(false); } foreach (var item in items) { ApplyRow(worksheet, item); } if (dict.ContainsKey("options")) { var options = dict["options"] as List <object>; if (options != null) { foreach (var option in options) { ApplyOptions(worksheet, option); } } } var oThread = new Thread(() => { var basePath = AppDomain.CurrentDomain.BaseDirectory; var dialog = new SaveFileDialog(); dialog.Filter = "Excel | *.xlsx"; if (dialog.ShowDialog() == DialogResult.OK) { //save grid.Save(dialog.FileName); } }); oThread.SetApartmentState(ApartmentState.STA); oThread.Start(); var isSafe = oThread.Join(new TimeSpan(2, 0, 0)); if (isSafe) { oThread.Abort(); } return(true); } catch (KeyNotFoundException ex) { return(false); } } }