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);
}
}
}
