private void SaveDownload(string fileName, ClientList list)
{
var workbook = new HSSFWorkbook();
var worksheet = workbook.CreateSheet("Report");
ExcelWorker.CellStyles = new List <ICellStyle>();
ExcelWorker.CreateRow(worksheet, 0, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Mã", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tên", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "SĐT", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Loại", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Điểm", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Doanh số", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
});
for (var i = 0; i < list.Data.Count; i++)
{
var client = list.Data[i];
client.OrderHistory = ClientInfo.GetClientOrders(UserID, Employee.ID, Employee.BussinessID, client.ID);
ExcelWorker.CreateRow(worksheet, i + 1, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, client.Code),
ExcelWorker.CreateCell(workbook, client.Name),
ExcelWorker.CreateCell(workbook, client.Phone),
ExcelWorker.CreateCell(workbook, client.TypeName),
ExcelWorker.CreateCell(workbook, client.Point.GetCurrencyString()),
ExcelWorker.CreateCell(workbook, client.OrderHistory.Sum(o => o.Paid).GetCurrencyString())
});
}
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(fs);
}
}
private void SaveRevenue(string fileName, Report report)
{
var workbook = new HSSFWorkbook();
var worksheet = workbook.CreateSheet("Report");
ExcelWorker.CellStyles = new List <ICellStyle>();
ExcelWorker.CreateRow(worksheet, 0, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Kho"),
ExcelWorker.CreateCell(workbook, report.Filter.WarehouseID.HasValue ? report.PaidOrders.FirstOrDefault().WarehouseName : "Toàn bộ")
});
ExcelWorker.CreateRow(worksheet, 1, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Ngày", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số hóa đơn", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tổng hóa đơn", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số hóa đơn trả", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tổng hóa đơn trả", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số phiếu thu", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tổng thu", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số phiếu chi", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tổng chi", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
});
var current = report.Filter.From.Value;
var index = 2;
while (current < report.Filter.To)
{
var start = new DateTime(current.Year, current.Month, current.Day, 0, 0, 0);
var end = start.AddDays(1);
ExcelWorker.CreateRow(worksheet, index, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, current.ToString("dd/MM/yyyy")),
ExcelWorker.CreateCell(workbook, report.PaidOrders.Count(i => i.Status != OrderStatus.Refunded && i.SubmitDate >= start && i.SubmitDate <= end).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.PaidOrders.Where(i => i.Status != OrderStatus.Refunded && i.SubmitDate >= start && i.SubmitDate <= end).Sum(i => i.Total - i.Discount).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.PaidOrders.Count(i => i.Status == OrderStatus.Refunded && i.SubmitDate >= start && i.SubmitDate <= end).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.PaidOrders.Where(i => i.Status == OrderStatus.Refunded && i.SubmitDate >= start && i.SubmitDate <= end).Sum(i => i.Total - i.Discount).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.Incomes.Count(i => i.SubmitDate >= start && i.SubmitDate <= end).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.Incomes.Where(i => i.SubmitDate >= start && i.SubmitDate <= end).Sum(i => i.Amount).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.Outcomes.Count(i => i.SubmitDate >= start && i.SubmitDate <= end).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.Outcomes.Where(i => i.SubmitDate >= start && i.SubmitDate <= end).Sum(i => i.Amount).GetCurrencyString())
});
index++;
current = current.AddDays(1);
}
ExcelWorker.CreateRow(worksheet, index, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Tổng cộng"),
ExcelWorker.CreateCell(workbook, report.PaidOrders.Count(i => i.Status != OrderStatus.Refunded).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.PaidOrders.Where(i => i.Status != OrderStatus.Refunded).Sum(i => i.Total - i.Discount).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.PaidOrders.Count(i => i.Status == OrderStatus.Refunded).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.PaidOrders.Where(i => i.Status == OrderStatus.Refunded).Sum(i => i.Total - i.Discount).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.Incomes.Count.GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.Incomes.Sum(i => i.Amount).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.Outcomes.Count.GetCurrencyString()),
ExcelWorker.CreateCell(workbook, report.Outcomes.Sum(i => i.Amount).GetCurrencyString())
});
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(fs);
}
}
private void SaveDownloadSalary(string fileName, SalaryReport list)
{
var workbook = new HSSFWorkbook();
var worksheet = workbook.CreateSheet("Report");
ExcelWorker.CellStyles = new List <ICellStyle>();
var cells = new List <ExcelCell>()
{
ExcelWorker.CreateCell(workbook, "Tên", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
};
var totals = new Dictionary <DateTime, decimal>();
foreach (var month in list.Months)
{
cells.Add(ExcelWorker.CreateCell(workbook, month.ToString("MM/yyyy"), HSSFColor.RoyalBlue.Index, HSSFColor.White.Index));
}
ExcelWorker.CreateRow(worksheet, 0, cells.ToArray());
for (var i = 0; i < list.Records.Count; i++)
{
var record = list.Records[i];
var dictionary = (IDictionary <string, object>)record;
cells = new List <ExcelCell>()
{
ExcelWorker.CreateCell(workbook, dictionary["Name"])
};
foreach (var month in list.Months)
{
var value = 0m;
var key = month.ToString("_MMyyyy");
if (dictionary.ContainsKey(key))
{
value = (decimal)dictionary[key];
}
cells.Add(ExcelWorker.CreateCell(workbook, value.GetCurrencyString()));
if (!totals.ContainsKey(month))
{
totals.Add(month, value);
}
else
{
totals[month] = totals[month] + value;
}
}
ExcelWorker.CreateRow(worksheet, i + 1, cells.ToArray());
}
var index = list.Records.Count + 1;
var totalCells = new List <ExcelCell>()
{
ExcelWorker.CreateCell(workbook, "Tổng cộng")
};
totalCells.AddRange(totals.Select(i => ExcelWorker.CreateCell(workbook, i.Value.GetCurrencyString())));
ExcelWorker.CreateRow(worksheet, index, totalCells.ToArray());
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(fs);
}
}
private void SaveCheck(string fileName, List <ProductInfo> products)
{
var workbook = new HSSFWorkbook();
var worksheet = workbook.CreateSheet("Report");
ExcelWorker.CellStyles = new List <ICellStyle>();
ExcelWorker.CreateRow(worksheet, 0, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Nhóm", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Mã", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tên", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Đơn vị", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Giá bán lẻ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Kho", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tồn đầu kỳ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Nhập", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Xuất", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tồn cuối kỳ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số lượng bán", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số lượng trả", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
});
for (var i = 0; i < products.Count; i++)
{
var product = products[i];
ExcelWorker.CreateRow(worksheet, i + 1, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, product.TagName),
ExcelWorker.CreateCell(workbook, product.Code),
ExcelWorker.CreateCell(workbook, product.Name),
ExcelWorker.CreateCell(workbook, product.Unit),
ExcelWorker.CreateCell(workbook, product.PriceString),
ExcelWorker.CreateCell(workbook, product.WarehouseName),
ExcelWorker.CreateCell(workbook, product.StartQuantityString),
ExcelWorker.CreateCell(workbook, product.ImportQuantityString),
ExcelWorker.CreateCell(workbook, product.ExportQuantityString),
ExcelWorker.CreateCell(workbook, product.EndQuantityString),
ExcelWorker.CreateCell(workbook, product.SaleQuantityString),
ExcelWorker.CreateCell(workbook, product.ReturnedString)
});
}
ExcelWorker.CreateRow(worksheet, products.Count + 1, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, "Tổng cộng"),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.StartQuantity).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.ImportQuantity).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.ExportQuantity).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.EndQuantity).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.SaleQuantity).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.Returned).GetCurrencyString())
});
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(fs);
}
}
private void SaveProductWarehouse(HSSFWorkbook workbook, IEnumerable <ProductSaleReport> products, WarehouseInfo warehouse = null)
{
var name = warehouse != null ? warehouse.Name : "Toàn bộ";
var worksheet = workbook.CreateSheet(name);
ExcelWorker.CellStyles = new List <ICellStyle>();
ExcelWorker.CreateRow(worksheet, 0, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Kho"),
ExcelWorker.CreateCell(workbook, name)
});
ExcelWorker.CreateRow(worksheet, 0, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Mã", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tên", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tỷ lệ (số lượng)", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tỷ lệ (tổng tiền)", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số lượng bán", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tổng tiền bán", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số lượng nhập", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tổng tiền nhập", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
});
var index = 1;
foreach (var product in products)
{
ExcelWorker.CreateRow(worksheet, index, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, product.Code),
ExcelWorker.CreateCell(workbook, product.Name),
ExcelWorker.CreateCell(workbook, Math.Round(product.QuantityPercentage, 2).ToString()),
ExcelWorker.CreateCell(workbook, Math.Round(product.RevenuePercentage, 2).ToString()),
ExcelWorker.CreateCell(workbook, product.OrderQuantityString),
ExcelWorker.CreateCell(workbook, product.OrderTotalString),
ExcelWorker.CreateCell(workbook, product.ImportQuantityString),
ExcelWorker.CreateCell(workbook, product.ImportTotalString)
});
index++;
}
ExcelWorker.CreateRow(worksheet, index, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, "Tổng cộng"),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.OrderQuantity).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.OrderTotal).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.ImportQuantity).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.ImportTotal).GetCurrencyString())
});
}
private void SaveDownload(string fileName, List <ProductInfo> products)
{
var workbook = new HSSFWorkbook();
var worksheet = workbook.CreateSheet("Report");
ExcelWorker.CellStyles = new List <ICellStyle>();
ExcelWorker.CreateRow(worksheet, 0, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Mã", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tên", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Kho", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số lượng", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số lượng trả", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Xuất xứ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Thời gian bảo hành (chính hãng)", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Thời gian bảo hành (đơn vị)", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Đơn vị", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Giá bán lẻ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Điểm tích lũy", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Mô tả", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
});
for (var i = 0; i < products.Count; i++)
{
var product = products[i];
ExcelWorker.CreateRow(worksheet, i + 1, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, product.Code),
ExcelWorker.CreateCell(workbook, product.Name),
ExcelWorker.CreateCell(workbook, product.WarehouseName),
ExcelWorker.CreateCell(workbook, product.Quantity.ToString()),
ExcelWorker.CreateCell(workbook, product.Returned.ToString()),
ExcelWorker.CreateCell(workbook, product.MadeIn),
ExcelWorker.CreateCell(workbook, product.OriginalWarranty),
ExcelWorker.CreateCell(workbook, product.BussinessWarranty),
ExcelWorker.CreateCell(workbook, product.Unit),
ExcelWorker.CreateCell(workbook, product.Price.ToString()),
ExcelWorker.CreateCell(workbook, product.Point.ToString()),
ExcelWorker.CreateCell(workbook, product.Description)
});
}
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(fs);
}
}
private void SaveDownload(string fileName, List <IncomeInfo> products)
{
var workbook = new HSSFWorkbook();
var worksheet = workbook.CreateSheet("Report");
ExcelWorker.CellStyles = new List <ICellStyle>();
ExcelWorker.CreateRow(worksheet, 0, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Mã", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Kho", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Nhân viên", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Ngày tạo", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số tiền", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Phương thức", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Lý do", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Ghi chú", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
});
for (var i = 0; i < products.Count; i++)
{
var product = products[i];
ExcelWorker.CreateRow(worksheet, i + 1, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, product.Code),
ExcelWorker.CreateCell(workbook, product.WarehouseName),
ExcelWorker.CreateCell(workbook, product.EmployeeName),
ExcelWorker.CreateCell(workbook, product.SubmitDate.ToString(Constants.DateTimeString)),
ExcelWorker.CreateCell(workbook, product.Amount.GetCurrencyString()),
ExcelWorker.CreateCell(workbook, product.Type),
ExcelWorker.CreateCell(workbook, product.Reason),
ExcelWorker.CreateCell(workbook, product.Note)
});
}
ExcelWorker.CreateRow(worksheet, products.Count + 1, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, "Tổng tiền"),
ExcelWorker.CreateCell(workbook, products.Sum(i => i.Amount).GetCurrencyString())
});
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(fs);
}
}
private void SaveSale(string fileName, Report report)
{
var workbook = new HSSFWorkbook();
var worksheet = workbook.CreateSheet("Report");
ExcelWorker.CellStyles = new List <ICellStyle>();
var employees = report.PaidOrders.GroupBy(i => i.EmployeeID).Select(i => new { ID = i.Key, Name = i.FirstOrDefault().EmployeeName }).ToArray();
var header = new List <ExcelCell>()
{
ExcelWorker.CreateCell(workbook, "Tháng", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
};
foreach (var employee in employees)
{
header.Add(ExcelWorker.CreateCell(workbook, employee.Name, HSSFColor.RoyalBlue.Index, HSSFColor.White.Index));
}
ExcelWorker.CreateRow(worksheet, 0, header.ToArray());
var current = report.Filter.From.Value;
var index = 1;
while (current < report.Filter.To)
{
var start = new DateTime(current.Year, current.Month, 1, 0, 0, 0);
var end = start.AddMonths(1);
var cells = new List <ExcelCell>()
{
ExcelWorker.CreateCell(workbook, current.ToString("MM/yyyy"))
};
foreach (var employee in employees)
{
cells.Add(ExcelWorker.CreateCell(workbook, report.PaidOrders.Where(i => i.EmployeeID == employee.ID && i.Status != OrderStatus.Refunded && i.SubmitDate >= start && i.SubmitDate <= end).Sum(i => i.Total - i.Discount).GetCurrencyString()));
}
ExcelWorker.CreateRow(worksheet, index, cells.ToArray());
index++;
current = current.AddMonths(1);
}
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(fs);
}
}
private void SaveDownload(string fileName, List <EmployeeInfo> list)
{
var workbook = new HSSFWorkbook();
var worksheet = workbook.CreateSheet("Report");
ExcelWorker.CellStyles = new List <ICellStyle>();
ExcelWorker.CreateRow(worksheet, 0, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Tên", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Chức vụ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Ngày sinh", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Ngày vào làm", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Lương căn bản", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tài khoản NH", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tên NH", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Chi nhánh NH", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Trạng thái", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
});
for (var i = 0; i < list.Count; i++)
{
var employee = list[i];
ExcelWorker.CreateRow(worksheet, i + 1, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, employee.Name),
ExcelWorker.CreateCell(workbook, employee.Position),
ExcelWorker.CreateCell(workbook, employee.DOB.HasValue ? employee.DOB.Value.ToString(Constants.DateString) : ""),
ExcelWorker.CreateCell(workbook, employee.StartDate.HasValue ? employee.StartDate.Value.ToString(Constants.DateString) : ""),
ExcelWorker.CreateCell(workbook, employee.BaseSalaryString),
ExcelWorker.CreateCell(workbook, employee.BankNumber),
ExcelWorker.CreateCell(workbook, employee.BankName),
ExcelWorker.CreateCell(workbook, employee.BankBranch),
ExcelWorker.CreateCell(workbook, employee.WorkStatus)
});
}
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(fs);
}
}
private void SaveAll(string fileName, List <ProductInfo> products)
{
var workbook = new HSSFWorkbook();
var worksheet = workbook.CreateSheet("Report");
ExcelWorker.CellStyles = new List <ICellStyle>();
ExcelWorker.CreateRow(worksheet, 0, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Nhóm", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Mã", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tên", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Đơn vị", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Giá bán lẻ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Xuất xứ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Dòng sản phẩn", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Loại máy", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Giới tính", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Loại kính", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Chất liệu dây", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Chất liệu vỏ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Kiểu vỏ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Màu mặt số", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Bề dày vỏ", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Kích thước dây", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Đường kính (mm)", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Độ chịu nước", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Chức năng", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Phong cách", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Bảo hành chính hãng", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Bảo hành SKtime", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Nhà cung cấp", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Mô tả", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
});
for (var i = 0; i < products.Count; i++)
{
var product = products[i];
ExcelWorker.CreateRow(worksheet, i + 1, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, product.TagName),
ExcelWorker.CreateCell(workbook, product.Code),
ExcelWorker.CreateCell(workbook, product.Name),
ExcelWorker.CreateCell(workbook, product.Unit),
ExcelWorker.CreateCell(workbook, product.PriceString),
ExcelWorker.CreateCell(workbook, product.MadeIn),
ExcelWorker.CreateCell(workbook, product.Type),
ExcelWorker.CreateCell(workbook, product.Engine),
ExcelWorker.CreateCell(workbook, product.Gender),
ExcelWorker.CreateCell(workbook, product.MirrorType),
ExcelWorker.CreateCell(workbook, product.TrapMaterial),
ExcelWorker.CreateCell(workbook, product.CaseMaterial),
ExcelWorker.CreateCell(workbook, product.CaseType),
ExcelWorker.CreateCell(workbook, product.FrontColor),
ExcelWorker.CreateCell(workbook, product.CaseWidth),
ExcelWorker.CreateCell(workbook, product.TrapSize),
ExcelWorker.CreateCell(workbook, product.Diameter),
ExcelWorker.CreateCell(workbook, product.WaterResistance),
ExcelWorker.CreateCell(workbook, product.Functions),
ExcelWorker.CreateCell(workbook, product.Style),
ExcelWorker.CreateCell(workbook, product.OriginalWarranty),
ExcelWorker.CreateCell(workbook, product.BussinessWarranty),
ExcelWorker.CreateCell(workbook, product.SupplierName),
ExcelWorker.CreateCell(workbook, product.Description)
});
}
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(fs);
}
}
private void SaveFind(string fileName, FindList list)
{
var products = list.Data;
var warehouse = list.Warehouses.FirstOrDefault(i => i.ID == list.Filter.WarehouseID);
var workbook = new HSSFWorkbook();
var worksheet = workbook.CreateSheet("Report");
var index = 0;
ExcelWorker.CreateRow(worksheet, index++, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Kho"),
ExcelWorker.CreateCell(workbook, warehouse.Name),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, "Tổng tồn"),
ExcelWorker.CreateCell(workbook, list.Data.Where(i => i.Exist).Sum(i => i.Max).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, "Tổng tìm"),
ExcelWorker.CreateCell(workbook, list.Data.Sum(i => i.Quantity).GetCurrencyString())
});
ExcelWorker.CreateRow(worksheet, index++, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Đủ hàng"),
ExcelWorker.CreateCell(workbook, list.Data.Count(i => i.Exist && i.Quantity == i.Max).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, "Thiếu hàng"),
ExcelWorker.CreateCell(workbook, list.Data.Count(i => i.Exist && i.Quantity < i.Max).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, "Dư hàng"),
ExcelWorker.CreateCell(workbook, list.Data.Count(i => i.Exist && i.Quantity > i.Max).GetCurrencyString()),
ExcelWorker.CreateCell(workbook, ""),
ExcelWorker.CreateCell(workbook, "Không tồn tại"),
ExcelWorker.CreateCell(workbook, list.Data.Count(i => !i.Exist).GetCurrencyString())
});
index++;
ExcelWorker.CellStyles = new List <ICellStyle>();
ExcelWorker.CreateRow(worksheet, index++, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, "Thông tin", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Nhóm", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Mã", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tên", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Kho", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Đơn vị", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Tồn kho", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index),
ExcelWorker.CreateCell(workbook, "Số lượng tìm", HSSFColor.RoyalBlue.Index, HSSFColor.White.Index)
});
for (var i = 0; i < products.Count; i++)
{
var product = products[i];
var message = "Đủ hàng";
if (!product.Exist)
{
message = "Không tồn tại";
}
else if (product.Quantity > product.Max)
{
message = String.Format("Dư hàng({0})", product.Max - product.Quantity);
}
else if (product.Quantity < product.Max)
{
message = String.Format("Thiếu hàng({0})", product.Quantity - product.Max);
}
ExcelWorker.CreateRow(worksheet, index++, new ExcelCell[] {
ExcelWorker.CreateCell(workbook, message),
ExcelWorker.CreateCell(workbook, product.TagName),
ExcelWorker.CreateCell(workbook, product.Code),
ExcelWorker.CreateCell(workbook, product.Name),
ExcelWorker.CreateCell(workbook, product.WarehouseName),
ExcelWorker.CreateCell(workbook, product.Unit),
ExcelWorker.CreateCell(workbook, product.Max.GetCurrencyString()),
ExcelWorker.CreateCell(workbook, product.Quantity.GetCurrencyString())
});
}
using (var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(fs);
}
}