public void DisplayText()
{
IWorksheet worksheet = this.workbook.Worksheets[2];
IMigrantRange migrantRange = worksheet.MigrantRange;
worksheet.UsedRangeIncludesFormatting = false;
int rowCount = worksheet.UsedRange.LastRow;
int colCount = worksheet.UsedRange.LastColumn;
List <string> rowsList = new List <string>();
List <string> rowsContentList = new List <string>();
for (int i = 8; i <= rowCount; i++)
{
//string rowValue = migrantRange.DisplayText.ToString();
//rowsList.Append<string>(rowValue);
string value = "";
for (int j = 1; j <= colCount; j++)
{
migrantRange.ResetRowColumn(i, j);
value = value + " - " + migrantRange.DisplayText;
}
Console.WriteLine(value);
}
//IRange[] cells = worksheet["A8:C40"].Cells;
//give value of each cell in range
/*foreach (IRange cell in cells)
* {
* string cellValue = cell.DisplayText.ToString();
*
* //Console.WriteLine(cellValue);
* }*/
//give value of each row in range
//IRange[] rows = worksheet["A8:"].Rows;
/*foreach (IRange row in rows)
* {
* row.ToArray();
* display value of the first cell of the row
* string rowValue = row.DisplayText.ToString();
* Console.WriteLine(rowValue);
*
*
* }*/
//string displayText = worksheet.Range["C8:C10"].DisplayText;
//getrange of cells and display a part of them
//IRange range = worksheet.Range[1, 8, 16, 534];
//string displayText = range[1, 9, 4, 20].Text;
//string displayText = worksheet.GetValueRowCol(8, 3).ToString();
worksheet.UsedRangeIncludesFormatting = false;
Console.WriteLine(worksheet.UsedRange.AddressLocal);
Close();
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
if (!(int.TryParse(numRowCount.Text, out rowCount) && int.TryParse(numColCount.Text, out colCount)))
{
MessageBox.Show("Enter Numerical Value");
return;
}
if (rowCount <= 0)
{
MessageBox.Show("Invalid row count");
return;
}
if (colCount <= 0)
{
MessageBox.Show("Invalid column count");
return;
}
if (rdbExcel97.Checked)
{
if (colCount > 256)
{
MessageBox.Show("Column count must be less than or equal to 256 for Excel 2003 format.");
return;
}
if (rowCount > 65536)
{
MessageBox.Show("Row count must be less than or equal to 65,536 for Excel 2003 format.");
return;
}
}
if (rdbExcel2007.Checked || rdbExcel2010.Checked || rdbExcel2013.Checked)
{
if (rowCount > 100001)
{
MessageBox.Show("Row count must be less than or equal to 100,000.");
return;
}
if (colCount > 151)
{
MessageBox.Show("Column count must be less than or equal to 151.");
return;
}
}
#region Starttime
//Start Time
#endregion
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "PerformanceChecking.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "PerformanceChecking.xlsx";
}
//Set the Default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "PerformanceChecking.xlsx";
}
//Set the Default version as Excel 2013
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "PerformanceChecking.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
startTime = DateTime.Now;
workbook.DetectDateTimeInValue = false;
#endregion
if (chbColumnStyle.Checked)
{
//Body Style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));
bodyStyle.Color = Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
bodyStyle.EndUpdate();
worksheet.SetDefaultColumnStyle(1, colCount, bodyStyle);
}
if (this.chkImportOnSave.Checked)
{
DataTable dataTable = new DataTable();
for (int column = 1; column <= colCount; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rowCount; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= colCount; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
startTime = DateTime.Now;
worksheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
#region Apply Style
//Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));
headerStyle.Color = Color.FromArgb(255, 174, 33);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
#endregion
IMigrantRange migrantRange = worksheet.MigrantRange;
for (int column = 1; column <= colCount; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.Text = "Column: " + column.ToString();
migrantRange.CellStyle = headerStyle;
}
#region Insert Data
//Writing Data using normal interface
for (int row = 2; row <= rowCount; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= colCount; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.Number = row * column;
}
}
}
#endregion
#region Workook Save
workbook.SaveAs(fileName);
#endregion
#region Workbook Save and Dispose
//Close the workbook
workbook.Close();
//Dispose the Excel Engine
excelEngine.Dispose();
#endregion
#region Set EndTime and get LogDetails
//End Time
endTime = DateTime.Now - startTime;
LogDetails(endTime);
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
}
#endregion
}
public ActionResult ExportExcel(string page, string row, string fromDatePara, string toDatePara, string title, string aetType, string total, string currency, string payer, string receiptter, string status, string isPlan, string orderBy)
{
int pageInt = int.Parse(page);
int length = int.Parse(row);
//Get data View
//var listData = _context.FundAccEntrys.Where(x => x.IsPlan == false && x.IsDeleted == false).Select(x => new { x.Title, x.AetType, x.Total, x.Currency, x.Payer, x.Receiptter, x.Status }).OrderUsingSortExpression(orderBy).AsNoTracking().ToList();
var fromDate = !string.IsNullOrEmpty(fromDatePara) ? DateTime.ParseExact(fromDatePara, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var toDate = !string.IsNullOrEmpty(toDatePara) ? DateTime.ParseExact(toDatePara, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var listData = (from a in _context.FundAccEntrys
join b in _context.FundCatReptExpss.Where(x => x.IsDeleted == false)
on a.CatCode equals b.CatCode
where (!a.IsDeleted &&
((fromDate == null) || (a.DeadLine.HasValue && a.DeadLine.Value.Date >= fromDate)) &&
((toDate == null) || (a.DeadLine.HasValue && a.DeadLine.Value.Date <= toDate))) &&
(string.IsNullOrEmpty(aetType) || (a.AetType.Equals(aetType))) &&
(string.IsNullOrEmpty(status) || (a.Status.Equals(status))) &&
(string.IsNullOrEmpty(isPlan) || (a.IsPlan.Equals(Convert.ToBoolean(isPlan))))
select new
{
CatName = b.CatName,
Id = a.Id,
AetCode = a.AetCode,
Title = a.Title,
AetType = a.AetType,
AetRelativeType = a.AetRelativeType,
AetDescription = a.AetDescription,
Total = a.Total,
Payer = a.Payer,
Currency = a.Currency,
Status = a.Status,
Receiptter = a.Receiptter
}).OrderUsingSortExpression(orderBy).AsNoTracking().ToList();
var listExport = new List <FundAccEntryExportModel>();
var no = 1;
foreach (var item in listData)
{
var itemExport = new FundAccEntryExportModel();
itemExport.No = no;
itemExport.CatName = item.CatName;
itemExport.Title = item.Title;
itemExport.AetType = item.AetType;
itemExport.Total = item.Total;
itemExport.Currency = item.Currency;
itemExport.Payer = item.Payer;
itemExport.Receiptter = item.Receiptter;
itemExport.Status = item.Status;
if (item.Status == "CREATED")
{
itemExport.Status = "Khởi tạo";
}
else if (item.Status == "PENDING")
{
itemExport.Status = "Đang chờ";
}
else if (item.Status == "APPROVED")
{
itemExport.Status = "Đã duyệt";
}
else if (item.Status == "REFUSE")
{
itemExport.Status = "Từ chối";
}
else
{
itemExport.Status = "Hủy bỏ";
}
if (item.AetType == "Receipt")
{
itemExport.AetType = "Thu";
}
else if (item.AetType == "Expense")
{
itemExport.AetType = "Chi";
}
//itemExport.LeaderIdea = item.LeaderIdea == "undefined" ? "Không ý kiến" : item.LeaderIdea;
//itemExport.Percent = item.Percent.ToString();
//if (item.Percent == 100)
//{
// itemExport.Percent = "100%";
//}
//else if (item.Percent == 0)
//{
// itemExport.Percent = "0%";
//}
//else
//{
// itemExport.Percent = String.Format("{0:0.00}", item.Percent) + "%";
//}
no = no + 1;
listExport.Add(itemExport);
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Create(1);
workbook.Version = ExcelVersion.Excel2016;
IWorksheet sheetRequest = workbook.Worksheets.Create("PhieuThuChi");
workbook.Worksheets[0].Remove();
IMigrantRange migrantRange = workbook.Worksheets[0].MigrantRange;
sheetRequest.Range["A1"].ColumnWidth = 24;
sheetRequest.Range["B1"].ColumnWidth = 24;
sheetRequest.Range["C1"].ColumnWidth = 24;
sheetRequest.Range["D1"].ColumnWidth = 24;
sheetRequest.Range["E1"].ColumnWidth = 24;
sheetRequest.Range["F1"].ColumnWidth = 24;
sheetRequest.Range["G1"].ColumnWidth = 24;
sheetRequest.Range["H1"].ColumnWidth = 24;
sheetRequest.Range["I1"].ColumnWidth = 24;
//sheetRequest.Range["I1"].ColumnWidth = 24;
//sheetRequest.Range["J1"].ColumnWidth = 24;
sheetRequest.Range["A1:I1"].Merge(true);
sheetRequest.Range["A1"].Text = "Phiếu Thu Chi";
sheetRequest.Range["A1"].CellStyle.Font.FontName = "Calibri";
sheetRequest.Range["A1"].CellStyle.Font.Bold = true;
sheetRequest.Range["A1"].CellStyle.Font.Size = 24;
sheetRequest.Range["A1"].CellStyle.Font.RGBColor = Color.FromArgb(0, 0, 176, 240);
sheetRequest.Range["A1"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheetRequest.ImportData(listExport, 2, 1, true);
sheetRequest["A2"].Text = "TT";
sheetRequest["B2"].Text = "Tên danh mục";
sheetRequest["C2"].Text = "Tiêu Đề";
sheetRequest["D2"].Text = "Loại";
sheetRequest["E2"].Text = "Số Tiền";
sheetRequest["F2"].Text = "Loại Tiền";
sheetRequest["G2"].Text = "Người Trả";
sheetRequest["H2"].Text = "Người Nhận";
sheetRequest["I2"].Text = "Trạng Thái";
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
tableHeader.Font.Color = ExcelKnownColors.White;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = Color.FromArgb(0, 0, 122, 192);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.None;
sheetRequest["A2:I2"].CellStyle = tableHeader;
sheetRequest.Range["A2:I2"].RowHeight = 20;
sheetRequest.UsedRange.AutofitColumns();
string ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var fileName = "ExportPhieuThuChi" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
workbook.Close();
excelEngine.Dispose();
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
public ActionResult ExportExcel(string page, string row, string title, string aetType, string total, string currency, string payer, string receiptter, string status, string orderBy)
{
int pageInt = int.Parse(page);
int length = int.Parse(row);
//Get data View
var listData = _context.FundAccEntrys.Where(x => x.IsPlan == true && x.IsDeleted == true).Select(x => new { x.Title, x.AetType, x.Total, x.Currency, x.Payer, x.Receiptter, x.Status }).OrderUsingSortExpression(orderBy).AsNoTracking().ToList();
var listExport = new List <FundPlanAccEntryExportModel>();
var no = 1;
foreach (var item in listData)
{
var itemExport = new FundPlanAccEntryExportModel();
itemExport.Title = item.Title;
itemExport.AetType = item.AetType;
itemExport.Total = item.Total;
itemExport.Currency = item.Currency;
itemExport.Payer = item.Payer;
itemExport.Receiptter = item.Receiptter;
itemExport.Status = item.Status;
//itemExport.LeaderIdea = item.LeaderIdea == "undefined" ? "Không ý kiến" : item.LeaderIdea;
//itemExport.Percent = item.Percent.ToString();
//if (item.Percent == 100)
//{
// itemExport.Percent = "100%";
//}
//else if (item.Percent == 0)
//{
// itemExport.Percent = "0%";
//}
//else
//{
// itemExport.Percent = String.Format("{0:0.00}", item.Percent) + "%";
//}
no = no + 1;
listExport.Add(itemExport);
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2010;
IWorkbook workbook = application.Workbooks.Create(1);
workbook.Version = ExcelVersion.Excel97to2003;
IWorksheet sheetRequest = workbook.Worksheets.Create("KeHoachThuChi");
workbook.Worksheets[0].Remove();
IMigrantRange migrantRange = workbook.Worksheets[0].MigrantRange;
sheetRequest.Range["A1"].ColumnWidth = 24;
sheetRequest.Range["B1"].ColumnWidth = 24;
sheetRequest.Range["C1"].ColumnWidth = 24;
sheetRequest.Range["D1"].ColumnWidth = 24;
sheetRequest.Range["E1"].ColumnWidth = 24;
sheetRequest.Range["F1"].ColumnWidth = 24;
sheetRequest.Range["G1"].ColumnWidth = 24;
sheetRequest.Range["H1"].ColumnWidth = 24;
//sheetRequest.Range["I1"].ColumnWidth = 24;
//sheetRequest.Range["J1"].ColumnWidth = 24;
sheetRequest.Range["A1:H1"].Merge(true);
sheetRequest.Range["A1"].Text = "Kế Hoạch Thu Chi";
sheetRequest.Range["A1"].CellStyle.Font.FontName = "Calibri";
sheetRequest.Range["A1"].CellStyle.Font.Bold = true;
sheetRequest.Range["A1"].CellStyle.Font.Size = 24;
sheetRequest.Range["A1"].CellStyle.Font.RGBColor = Color.FromArgb(0, 0, 176, 240);
sheetRequest.Range["A1"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheetRequest.ImportData(listExport, 2, 1, true);
sheetRequest["A2"].Text = "TT";
sheetRequest["B2"].Text = "Tiêu Đề";
sheetRequest["C2"].Text = "Loại";
sheetRequest["D2"].Text = "Số Tiền";
sheetRequest["E2"].Text = "Loại Tiền";
sheetRequest["F2"].Text = "Người Trả";
sheetRequest["G2"].Text = "Người Nhận";
sheetRequest["H2"].Text = "Trạng Thái";
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
tableHeader.Font.Color = ExcelKnownColors.White;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = Color.FromArgb(0, 0, 122, 192);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.None;
sheetRequest["A2:H2"].CellStyle = tableHeader;
sheetRequest.Range["A2:H2"].RowHeight = 20;
sheetRequest.UsedRange.AutofitColumns();
string ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var fileName = "ExportPhieuThuChi" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
workbook.Close();
excelEngine.Dispose();
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
private void Button1_Click(object sender, EventArgs e)
{
int rowCount = Convert.ToInt32(textBox1.Value);
int colCount = Convert.ToInt32(textBox2.Value);
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
if (chkImport.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
DataTable dataTable = new DataTable();
for (int column = 1; column <= colCount; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rowCount; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= colCount; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
sheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
IMigrantRange migrantRange = sheet.MigrantRange;
for (int column = 1; column <= colCount; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.SetValue("Column: " + column.ToString());
}
//Writing Data using normal interface
for (int row = 2; row <= rowCount; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= colCount; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.SetValue(row * column);
}
}
}
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (stream != null)
{
SaveAndroid androidSave = new SaveAndroid();
androidSave.Save("CreateSheet.xlsx", "application/msexcel", stream, m_context);
}
}
public ActionResult ExportFlow(int id)
{
//var logger = LogManager.GetLogger(typeof(Program));
//logger.Info("1 - Bat dau lay list export");
var userId = ESEIM.AppContext.UserId;
var data = _context.DispatchesHeaders.FirstOrDefault(x => x.Id == id);
//tracking User
var tracking = _context.DispatchTrackingProcesss.FirstOrDefault(x => x.DispatchCode == data.DispatchCode);
//groupUser
var listDispatchesMember = _context.DispatchesMemberActivitys.Where(x => x.ProcessCode == tracking.ProcessCode);
var listMember = listDispatchesMember.Where(x => x.ProcessCode == tracking.ProcessCode).Select(x => new IncommingDispatchesMember
{
Id = x.Id,
Assigner = x.Assigner,
AssignerName = _context.Users.FirstOrDefault(y => y.Id == x.Assigner).GivenName ?? null,
AssignerGroupUserName = _context.AdGroupUsers.Join(_context.AdUserInGroups.Where(y => y.UserId == x.Assigner), post => post.GroupUserCode, meta => meta.GroupUserCode, (post, meta) => post.Title).AsNoTracking().FirstOrDefault(),
GroupUserName = _context.AdGroupUsers.Join(_context.AdUserInGroups.Where(y => y.UserId == x.UserId), post => post.GroupUserCode, meta => meta.GroupUserCode, (post, meta) => post.Title).AsNoTracking().FirstOrDefault(),
Name = x.User.GivenName,
UserId = x.UserId,
CreatedTime = x.CreatedTime,
Role = x.Role,
//IsShowComment = x.Assigner.Equals(userId) ? true : false,
//IsShowDelete = false,
//IsShowAction = x.UserId.Equals(userId) ? true : false,
Status = (x.UserId.Equals(userId) && x.Role == 2 && x.AssigneeConfirm == EnumHelper <DocumentStatusEnum> .GetDisplayValue(DocumentStatusEnum.Review)) ? null : x.AssigneeConfirm,
Comment = x.Comment
}).OrderBy(x => x.Id).AsNoTracking();
var listExport = listMember.ToList().Select(a => new ExportFlowModel
{
AssignerName = a.AssignerName + " - " + a.AssignerGroupUserName,
Name = a.Name + " - " + a.GroupUserName,
Comment = a.Comment,
CreatedTime = a.CreatedTime.Value.ToString("dd/MM/yyyy"),
Role = a.Role == 1 ? "Xử lý chính" : a.Role == 2 ? "Phối hợp" : "Xem để biết",
Status = a.Status == "DONE" ? "Đã hoàn tất"
: a.Status == "SEND" ? "Đã chuyển xử lý chính"
: a.Status == "ADD_SEND" ? "Đã chuyển"
: a.Status == "SEND_COORDINATED" ? "Đã chuyển phối hợp"
: a.Status == "UPDATED" ? "Đã cập nhật"
: a.Status == "COORDINATED" ? "Đã phối hợp"
: a.Status == "PROCESSING" ? "Đang xử lý"
: a.Status == "NOCOORDINATED" ? "Chưa phối hợp"
: a.Status == null ? "Chưa xem"
: a.Status == "" ? "Chưa chuyển"
: "Đã xem"
}).ToList();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2010;
IWorkbook workbook = application.Workbooks.Create(1);
workbook.Version = ExcelVersion.Excel97to2003;
IWorksheet sheetRequest = workbook.Worksheets.Create("Sheet2");
workbook.Worksheets[0].Remove();
IMigrantRange migrantRange = workbook.Worksheets[0].MigrantRange;
sheetRequest.Range["A1"].ColumnWidth = 24;
sheetRequest.Range["B1"].ColumnWidth = 24;
sheetRequest.Range["C1"].ColumnWidth = 24;
sheetRequest.Range["D1"].ColumnWidth = 24;
sheetRequest.Range["E1"].ColumnWidth = 24;
sheetRequest.Range["F1"].ColumnWidth = 24;
sheetRequest.Range["A1:F1"].Merge(true);
sheetRequest.Range["A1"].Text = "Các hoạt động xử lý công văn";
sheetRequest.Range["A1"].CellStyle.Font.FontName = "Calibri";
sheetRequest.Range["A1"].CellStyle.Font.Bold = true;
sheetRequest.Range["A1"].CellStyle.Font.Size = 24;
sheetRequest.Range["A1"].CellStyle.Font.RGBColor = Color.FromArgb(0, 0, 176, 240);
sheetRequest.Range["A1"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheetRequest.ImportData(listExport, 2, 1, true);
sheetRequest["A2"].Text = "Người chuyển";
sheetRequest["B2"].Text = "Người nhận";
sheetRequest["C2"].Text = "Ý kiến";
sheetRequest["D2"].Text = "Thời gian";
sheetRequest["E2"].Text = "Vai trò";
sheetRequest["F2"].Text = "Trạng thái";
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
tableHeader.Font.Color = ExcelKnownColors.White;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = Color.FromArgb(0, 0, 122, 192);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.None;
sheetRequest["A2:F2"].CellStyle = tableHeader;
sheetRequest.Range["A2:F2"].RowHeight = 20;
sheetRequest.UsedRange.AutofitColumns();
string ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var fileName = "Luồng xử lý công văn (" + data.DocumentSymbol + " ngày " + data.PromulgateDate.Value.ToString("dd.MM.yyyy") + ") - " + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
workbook.Close();
excelEngine.Dispose();
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
#region Workbook initialization
int rowCount = Convert.ToInt32(cbRows.Text);
int colCount = Convert.ToInt32(cbColumns.Text);
MessageDialog dialog = null;
if (rdbExcel2003.IsChecked.Value && colCount > 256)
{
dialog = new MessageDialog("Maximum number of columns allowed for Excel 2003 format is 256. Please select Excel 2007 to 2013 format if you need more than 256 columns.");
await dialog.ShowAsync();
}
else if (rdbExcel2003.IsChecked.Value && rowCount > 65536)
{
dialog = new MessageDialog("Maximum number of rows allowed for Excel 2003 format is 65,536. Please select Excel 2007 to 2013 format if you need more than 65,536 rows.");
await dialog.ShowAsync();
}
else
{
System.Diagnostics.Stopwatch watcher = new System.Diagnostics.Stopwatch();
watcher.Start();
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Default Version as Excel 97to2003
if (this.rdbExcel2003.IsChecked == true)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
////Set the Default Version as Excel 2007
//else if (this.rdbExcel2007.IsChecked == true)
//{
// application.DefaultVersion = ExcelVersion.Excel2007;
//}
//else if (this.rdbExcel2010.IsChecked == true)
//{
// application.DefaultVersion = ExcelVersion.Excel2010;
//}
else if (this.rdbExcel2013.IsChecked == true)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region FillData
IMigrantRange migrantRange = worksheet.MigrantRange;
for (int column = 1; column <= colCount; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.Text = "Column: " + column.ToString();
//#if WINDOWS_PHONE_APP
worksheet.SetColumnWidth(column, 9);
//#endif
}
//Writing Data using normal interface
for (int row = 2; row <= rowCount; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= colCount; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.Number = row * column;
}
}
watcher.Stop();
LogDetails(watcher.Elapsed);
#endregion
#region Save the Workbook
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "Performance";
if (workbook.Version == ExcelVersion.Excel97to2003)
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xls"
});
}
else
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
}
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
if (workbook.Version == ExcelVersion.Excel97to2003)
{
storageFile = await local.CreateFileAsync("Performance.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("Performance.xlsx", CreationCollisionOption.ReplaceExisting);
}
}
//Saving the workbook to disk.
if (storageFile != null)
{
//Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
else
{
workbook.Close();
excelEngine.Dispose();
}
#endregion
}
}
public ActionResult Performance(string SaveOption, string Import, string rowCount, string colCount)
{
if (SaveOption == null)
{
return(View());
}
int rows = Convert.ToInt32(rowCount);
int columns = Convert.ToInt32(colCount);
if (SaveOption == "Xls")
{
if (columns > 256)
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Column count must be less than or equal to 256 for Excel 2003 format.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>");
return(View());
}
if (rows > 65536)
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Row count must be less than or equal to 65,536 for Excel 2003 format.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>");
return(View());
}
}
if (SaveOption == "Xlsx")
{
if (rows > 100001)
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Row count must be less than or equal to 100,000.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>");
return(View());
}
if (columns > 151)
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Column count must be less than or equal to 151.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>");
return(View());
}
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
if (SaveOption == "Xlsx")
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
else
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
if (Import == "importonsave")
{
workbook.Version = ExcelVersion.Excel2013;
DataTable dataTable = new DataTable();
for (int column = 1; column <= columns; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rows; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= columns; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
sheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
IMigrantRange migrantRange = sheet.MigrantRange;
for (int column = 1; column <= columns; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.SetValue("Column: " + column.ToString());
}
//Writing Data using normal interface
for (int row = 2; row <= rows; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= columns; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.SetValue(row * column);
}
}
}
try
{
if (SaveOption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "Performance.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "Performance.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
public EtlProcessedFile LoadExcelFileVectorPrecio()
{
var dateIni = DateTime.Now;
var idLoadFile = dateIni.TimeOfDay.ToString().PadRight(20).Replace(".", "").Replace(":", "");
//EtlProcessedFile processedFile = new EtlProcessedFile { DateIni = dateIni, FileName = MessageCenter.URL_FILE_VP };
// "vectorPrecios_20200301Data.xlsx" vectorPrecios_20200324Data.xlsx vectorPrecios_20200327Data.xlsx vectorPrecios_20200331Data.xlsx
EtlProcessedFile processedFile = new EtlProcessedFile {
DateIni = dateIni, FileName = "vectorPrecios_20200331Data.xlsx"
};
try
{
var stopwatch = new Stopwatch();
stopwatch.Start();
List <VectorPrecio> vectores = new List <VectorPrecio>();
using (ExcelEngine excelEngine = new ExcelEngine())
{
FileStream inputStream = new FileStream(processedFile.FileName, FileMode.Open);
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputStream);
IWorksheet sheet = workbook.Worksheets[0];
IMigrantRange mRange = sheet.MigrantRange;
int rowCount = sheet.UsedRange.LastRow;
int colCount = sheet.UsedRange.LastColumn;
for (int r = 2; r <= rowCount; r++)
{
var vectorPrecio = new VectorPrecio();
for (int c = 1; c <= colCount; c++)
{
mRange.ResetRowColumn(r, c);
var value = mRange.Value?.ToString().TrimEnd().TrimStart();
DateTime parsedDate = new DateTime();
switch (c)
{
case 1: vectorPrecio.Fecha = DateTime.TryParse(value, out parsedDate) == true ? parsedDate : new DateTime(); break;
case 2: vectorPrecio.Operadora = value; break;
case 3: vectorPrecio.Fondo = value; break;
case 4: vectorPrecio.Serie = value; break;
case 6: vectorPrecio.Precio = String.IsNullOrEmpty(value) == true ? -1 : Convert.ToDecimal(value.Replace(",", "").Replace("'", "").Replace("´", ""), null); break;
}//SWITCH
}
var fondoserie = vectorPrecio.Fondo + vectorPrecio.Serie;
var fondo = vectorPrecio.Fondo;
var serie = vectorPrecio.Serie;
var operadora = vectorPrecio.Operadora;
var fondokey = vectorPrecio.Fecha.ToString("MMyy") + fondoserie;
//var fondoInverison = _dataContext.FondosInversionMontosMinimos.FirstOrDefault(fi => fi.FondoKey == fondokey);
vectorPrecio.FondoSerie = fondoserie;
vectorPrecio.FondoKey = fondokey;
vectorPrecio.FondoInversion = new FondosInversionMontosMinimos {
FondoKey = fondokey, Fondo = fondo, Serie = serie, FondoSerie = fondoserie, Operadora = operadora
};
vectorPrecio.IdLoadFile = idLoadFile;
/*** CUATRO_DIAS ***/
var r4 = CalculateRendimiento(vectorPrecio.Fecha, vectorPrecio.Precio, fondoserie, MessageCenter.CUATRO_DIAS);
vectorPrecio.RendiDirCuatroDias = r4.Directo;
vectorPrecio.RendiAnuCuatroDias = r4.Anualizado;
/*** SIETE_DIAS ***/
var r7 = CalculateRendimiento(vectorPrecio.Fecha, vectorPrecio.Precio, fondoserie, MessageCenter.SIETE_DIAS);
vectorPrecio.RendiDirSieteDias = r7.Directo;
vectorPrecio.RendiAnuSieteDias = r7.Anualizado;
/*** UN_MES ***/
var r30 = CalculateRendimiento(vectorPrecio.Fecha, vectorPrecio.Precio, fondoserie, MessageCenter.UN_MES);
vectorPrecio.RendiDirUnMes = r30.Directo;
vectorPrecio.RendiAnuUnMes = r30.Anualizado;
/*** TRES_MESES ***/
var r90 = CalculateRendimiento(vectorPrecio.Fecha, vectorPrecio.Precio, fondoserie, MessageCenter.TRES_MESES);
vectorPrecio.RendiDirTresMeses = r90.Directo;
vectorPrecio.RendiAnuTresMeses = r90.Anualizado;
/*** SEIS_MESES ***/
var r180 = CalculateRendimiento(vectorPrecio.Fecha, vectorPrecio.Precio, fondoserie, MessageCenter.SEIS_MESES);
vectorPrecio.RendiDirSeisMeses = r180.Directo;
vectorPrecio.RendiAnuSeisMeses = r180.Anualizado;
/*** NUEVE_MESES ***/
var r270 = CalculateRendimiento(vectorPrecio.Fecha, vectorPrecio.Precio, fondoserie, MessageCenter.NUEVE_MESES);
vectorPrecio.RendiDirNueveMeses = r270.Directo;
vectorPrecio.RendiAnuNueveMeses = r270.Anualizado;
/*** DOCE_MESES ***/
var r360 = CalculateRendimiento(vectorPrecio.Fecha, vectorPrecio.Precio, fondoserie, MessageCenter.DOCE_MESES);
vectorPrecio.RendiDirDoceMeses = r360.Directo;
vectorPrecio.RendiAnuDoceMeses = r360.Anualizado;
/*** DIECIOCHO_MESES ***/
var r540 = CalculateRendimiento(vectorPrecio.Fecha, vectorPrecio.Precio, fondoserie, MessageCenter.DIECIOCHO_MESES);
vectorPrecio.RendiDirDiesiOchoMeses = r540.Directo;
vectorPrecio.RendiAnuDiesiOchoMeses = r540.Anualizado;
/*** VEINTICUATRO_MESES ***/
var r720 = CalculateRendimiento(vectorPrecio.Fecha, vectorPrecio.Precio, fondoserie, MessageCenter.VEINTICUATRO_MESES);
vectorPrecio.RendiDirVeintiCuatroMeses = r720.Directo;
vectorPrecio.RendiAnuVeintiCuatroMeses = r720.Anualizado;
/*** TREINTAYSEIS_MESES ***/
var r1080 = CalculateRendimiento(vectorPrecio.Fecha, vectorPrecio.Precio, fondoserie, MessageCenter.TREINTAYSEIS_MESES);
vectorPrecio.RendiDirTreintaySeisMeses = r1080.Directo;
vectorPrecio.RendiAnuTreintaySeisMeses = r1080.Anualizado;
vectores.Add(vectorPrecio);
}
workbook.Close(); //Close the instance of IWorkbook
excelEngine.Dispose(); //Dispose the instance of ExcelEngine
}//USING
_dataContext.BulkInsert(vectores);
stopwatch.Stop();
processedFile.DateEnd = DateTime.Now;
processedFile.ElapsedTime = (stopwatch.ElapsedMilliseconds) / 1000;
processedFile.LoadedRecords = vectores.Count;
processedFile.TypeLoad = "Vector de Precios";
processedFile.IdLoadFile = idLoadFile;
_dataContext.EtlProcessedFiles.Add(processedFile);
_dataContext.SaveChanges();
} catch (Exception e)
{ Console.WriteLine($"Errors LoadExcelFileVectorPrecio: {e.ToString()} "); }
return(processedFile);
}//Vectorprecio
public EtlProcessedFile LoadExcelFileMontosMinimos()
{
var dateIni = DateTime.Now;
var idLoadFile = dateIni.TimeOfDay.ToString().PadRight(20).Replace(".", "").Replace(":", "");
EtlProcessedFile processedFile = new EtlProcessedFile {
DateIni = dateIni, FileName = MessageCenter.URL_FILE_MM
};
try
{
var stopwatch = new Stopwatch();
stopwatch.Start();
List <FondosInversionMontosMinimos> fondos = new List <FondosInversionMontosMinimos>();
using (ExcelEngine excelEngine = new ExcelEngine())
{
FileStream inputStream = new FileStream(processedFile.FileName, FileMode.Open);
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputStream);
IWorksheet sheet = workbook.Worksheets["montos minimos"];
IMigrantRange mRange = sheet.MigrantRange;
int rowCount = sheet.UsedRange.LastRow;
int colCount = sheet.UsedRange.LastColumn;
for (int r = 2; r <= rowCount; r++)
{
var fondoInversion = new FondosInversionMontosMinimos();
for (int c = 1; c <= colCount; c++)
{
mRange.ResetRowColumn(r, c);
var value = mRange.Value?.ToString().TrimEnd().TrimStart();
DateTime parsedDate = new DateTime();
switch (c)
{
case 1: fondoInversion.Fecha = DateTime.TryParse(value, out parsedDate) == true ? parsedDate : new DateTime(); break;
case 2: fondoInversion.Operadora = value; break;
case 3: fondoInversion.Fondo = value; break;
case 4: fondoInversion.Serie = value; break;
case 6: fondoInversion.MontoMinimo = String.IsNullOrEmpty(value) == true ? -1 : Convert.ToDecimal(value.Replace(",", "").Replace("'", "").Replace("´", ""), null); break;
case 7: fondoInversion.MontoMinimoTipo = value.ToUpper() == "SERIE A" ? (char)'S' : StringToChar(value.ToCharArray()); break;
case 8: fondoInversion.Divisa = value; break;
case 9: fondoInversion.Activo = StringToChar(value.ToUpper().ToCharArray()); break;
case 11: fondoInversion.PersonaFisica = TransformStringToBoolean(value.ToUpper()); break;
case 12: fondoInversion.PersonaMoral = TransformStringToBoolean(value.ToUpper()); break;
case 13: fondoInversion.PersonaMoralNoContribuyente = TransformStringToBoolean(value.ToUpper()); break;
case 14: fondoInversion.SociedadesDeInversion = TransformStringToBoolean(value.ToUpper()); break;
}//SWITCH
}
fondoInversion.FondoSerie = fondoInversion.Fondo + fondoInversion.Serie;
fondoInversion.FondoKey = fondoInversion.Fecha.ToString("MMyy") + fondoInversion.FondoSerie;
fondoInversion.IdLoadFile = idLoadFile;
fondos.Add(fondoInversion);
}
workbook.Close(); //Close the instance of IWorkbook
excelEngine.Dispose(); //Dispose the instance of ExcelEngine
}//USING
_dataContext.BulkInsert(fondos);
stopwatch.Stop();
processedFile.DateEnd = DateTime.Now;
processedFile.ElapsedTime = (stopwatch.ElapsedMilliseconds) / 1000;
processedFile.LoadedRecords = fondos.Count;
processedFile.TypeLoad = "Fondos de Inversión MM";
processedFile.IdLoadFile = idLoadFile;
_dataContext.EtlProcessedFiles.Add(processedFile);
_dataContext.SaveChanges();
} catch (Exception e)
{ Console.WriteLine($"Finish with errors {e.ToString()} "); }
return(processedFile);
}//Montominimo
private async void Excel()
{
var balanceView = BalanceView.GetInstance();
var confirmacion = await dialogService.ShowMessageConfirmacion("Mensaje", "¿Desea exportar el balance a una planilla de cálculo?");
if (confirmacion)
{
try {
Cargas();
if (ListaBalance.Count == 0)
{
await dialogService.ShowMessage("Error", "Se deben agregar elementos al balance");
balanceView.excelUnTapped();
return;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
cont = 0;
//Seleccionar versión de Excel 2013
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013;
//Crear workbook con una hoja de trabajo
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
//Acceder a la primera hoja de trabajo desde la instancia de workbook
IWorksheet worksheet = workbook.Worksheets[0];
IMigrantRange migrantRange = worksheet.MigrantRange;
foreach (var elemento in ListaBalance)
{
// Writing Data.
//cont aumenta en 7 la posición de las filas en cada producto, las columnas dependen de los días elegidos
migrantRange["A1"].Text = "Fecha";
migrantRange["A1"].CellStyle.Font.Bold = true;
migrantRange["B1"].Text = "Origen";
migrantRange["B1"].CellStyle.Font.Bold = true;
migrantRange["C1"].Text = "Categoría";
migrantRange["C1"].CellStyle.Font.Bold = true;
migrantRange["D1"].Text = "Monto";
migrantRange["D1"].CellStyle.Font.Bold = true;
//Nueva celda
migrantRange.ResetRowColumn(cont + 2, 1);
migrantRange.Text = string.Format("{0}/{1}/{2}", elemento.Dia, elemento.Mes, elemento.Anio);
//migrantRange.CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
//Nueva celda
migrantRange.ResetRowColumn(cont + 2, 2);
migrantRange.Text = elemento.Origen;
//Nueva celda
migrantRange.ResetRowColumn(cont + 2, 3);
migrantRange.Text = elemento.GastoIngreso;
//Nueva celda
migrantRange.ResetRowColumn(cont + 2, 4);
migrantRange.Number = double.Parse(elemento.Cantidad);
if (double.Parse(elemento.Cantidad) > 0)
{
worksheet[string.Format("D{0}", cont + 2)].CellStyle.Font.Color = ExcelKnownColors.Green;
}
else if (double.Parse(elemento.Cantidad) < 0)
{
worksheet[string.Format("D{0}", cont + 2)].CellStyle.Font.Color = ExcelKnownColors.Red;
}
cont = cont + 1;
}
;
IRange range = worksheet.Range[string.Format("A{0}:C{0}", cont + 2)];
range.Merge();
range.Text = string.Format("Balance: ");
range.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
worksheet[string.Format("D{0}", cont + 2)].Number = double.Parse(BalanceTotal);
worksheet[string.Format("D{0}", cont + 2)].CellStyle.Font.Bold = true;
if (double.Parse(BalanceTotal) > 0)
{
worksheet[string.Format("D{0}", cont + 2)].CellStyle.ColorIndex = ExcelKnownColors.Green;
}
else if (double.Parse(BalanceTotal) < 0)
{
worksheet[string.Format("D{0}", cont + 2)].CellStyle.ColorIndex = ExcelKnownColors.Red;
}
worksheet.Range[string.Format("A1:D{0}", cont + 2)].BorderInside();
worksheet.Range[string.Format("A1:D{0}", cont + 2)].BorderAround();
worksheet.UsedRange.AutofitColumns();
//Save the workbook to stream in xlsx format.
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
//Save the stream as a file in the device and invoke it for viewing
DependencyService.Get <ISave>().SaveAndView(string.Format("Balance Mensual de {0}-{1}", SelectedItemMes, SelectedItemAño) + ".xlsx", "application/msexcel", stream);
await dialogService.ShowMessage("Mensaje", string.Format("El balance se guardó como archivo de nombre '{0}' en la carpeta Balances", string.Format("Balance Mensual de {0}-{1}", SelectedItemMes, SelectedItemAño) + ".xlsx"));
}
}
catch
{
await dialogService.ShowMessage("Error", "No se pudo exportar a hoja de cálculo. Intenta habilitando los permisos en ajustes.");
}
}
balanceView.excelUnTapped();
}
public ActionResult Performance(string SaveOption, string Import, string rowCount, string colCount)
{
if (SaveOption == null)
{
return(View());
}
int rows = Convert.ToInt32(rowCount);
int columns = Convert.ToInt32(colCount);
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Creating new workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
#region Generate Excel
if (Import == "importonsave")
{
DataTable dataTable = new DataTable();
for (int column = 1; column <= columns; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rows; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= columns; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
sheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
IMigrantRange migrantRange = workbook.Worksheets[0].MigrantRange;
for (int column = 1; column <= columns; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.Text = "Column: " + column.ToString();
}
//Writing Data using normal interface
for (int row = 2; row <= rows; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= columns; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.Number = row * column;
}
}
}
#endregion
string ContentType = null;
string fileName = null;
if (SaveOption == "ExcelXls")
{
ContentType = "Application/vnd.ms-excel";
fileName = "Sample.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "Sample.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
private async void Xls()
{
if (ProductoPrincipal.Calculo == false)
{
await dialogService.ShowMessage("Error", "Se debe realizar, en primera medida, el cálculo");
return;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
cont = 0;
//Seleccionar versión de Excel 2013
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013;
//Crear workbook con una hoja de trabajo
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
foreach (var productoA in ProductoPrincipal.Productos)
{
CantSemanas = productoA.Semanas.Count.ToString();
//Acceder a la primera hoja de trabajo desde la instancia de workbook
IWorksheet worksheet = workbook.Worksheets[0];
IMigrantRange migrantRange = worksheet.MigrantRange;
// Writing Data.
//cont aumenta en 7 la posición de las filas en cada producto, las columnas dependen de los días elegidos
migrantRange["A" + (cont + 1).ToString()].Text = productoA.Nombres;
migrantRange["A" + (2 + cont).ToString()].Text = "Requerimiento Bruto";
migrantRange["A" + (3 + cont).ToString()].Text = "Inventario Inicial";
migrantRange["A" + (4 + cont).ToString()].Text = "Requerimiento Neto";
migrantRange["A" + (5 + cont).ToString()].Text = "Liberación Orden";
migrantRange["A" + (6 + cont).ToString()].Text = "Inventario Final";
//Estilos de las celdas
migrantRange["A" + (cont + 1).ToString()].CellStyle.Font.Bold = true;
migrantRange["A" + (cont + 1).ToString()].CellStyle.ColorIndex = ExcelKnownColors.Sea_green;
migrantRange["A" + (cont + 1).ToString()].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
migrantRange["A" + (2 + cont).ToString()].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
migrantRange["A" + (2 + cont).ToString()].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
migrantRange["A" + (3 + cont).ToString()].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
migrantRange["A" + (3 + cont).ToString()].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
migrantRange["A" + (4 + cont).ToString()].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
migrantRange["A" + (4 + cont).ToString()].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
migrantRange["A" + (5 + cont).ToString()].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
migrantRange["A" + (5 + cont).ToString()].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
migrantRange["A" + (6 + cont).ToString()].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
migrantRange["A" + (6 + cont).ToString()].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
//Recorrido de columnas desde la columna 2 en adelante
for (int column = 2; column < int.Parse(CantSemanas) + 2; column++)
{
//Nueva celda
migrantRange.ResetRowColumn(cont + 1, column);
migrantRange.Text = "Día" + (column - 1).ToString() + " " + DateSelected.Date.AddDays(column - 2).ToString().Remove(10);
//Estilo de la celda migrantRange.ResetRowColumn(cont+1, column)
migrantRange.CellStyle.ColorIndex = ExcelKnownColors.Pale_blue;
migrantRange.CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
if (!string.IsNullOrEmpty(productoA.Semanas[column - 2].LibOrden))
{
migrantRange.CellStyle.ColorIndex = ExcelKnownColors.Bright_green;
}
//Nueva celda
migrantRange.ResetRowColumn(cont + 2, column);
migrantRange.Text = productoA.Semanas[column - 2].ReqBruto;
//Nueva celda
migrantRange.ResetRowColumn(cont + 3, column);
migrantRange.Text = productoA.Semanas[column - 2].InventarioInicial;
//Nueva celda
migrantRange.ResetRowColumn(cont + 4, column);
migrantRange.Text = productoA.Semanas[column - 2].ReqNeto;
//Nueva celda
migrantRange.ResetRowColumn(cont + 5, column);
migrantRange.Text = productoA.Semanas[column - 2].LibOrden;
//Estilo de la celda migrantRange.ResetRowColumn(cont+5, column)
if (!string.IsNullOrEmpty(productoA.Semanas[column - 2].LibOrden))
{
migrantRange.CellStyle.ColorIndex = ExcelKnownColors.Bright_green;
migrantRange.CellStyle.Font.Bold = true;
}
//Nueva celda
migrantRange.ResetRowColumn(cont + 6, column);
migrantRange.Text = productoA.Semanas[column - 2].InvFinal;
}
cont = cont + 7;
worksheet.UsedRange.AutofitColumns();
}
//Save the workbook to stream in xlsx format.
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
//Save the stream as a file in the device and invoke it for viewing
await DependencyService.Get <ISave>().SaveAndView(ProductoPrincipal.Nombre + ".xlsx", "application/msexcel", stream);
await dialogService.ShowMessage("Mensaje", "El Producto se guardó como hoja de Excel en la carpeta MRP");
}
}
private void BtnGenerate_Clicked(object sender, EventArgs e)
{
int rows = Convert.ToInt32(this.rowCount.Text);
int columns = Convert.ToInt32(this.colCount.Text);
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
workbook = application.Workbooks.Create(1);
workbook.Version = ExcelVersion.Excel2013;
IWorksheet sheet = workbook.Worksheets[0];
if (this.Import.IsChecked.Value)
{
DataTable dataTable = new DataTable();
for (int column = 1; column <= columns; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rows; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= columns; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
sheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
IMigrantRange migrantRange = sheet.MigrantRange;
for (int column = 1; column <= Convert.ToInt32(this.colCount.Text); column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.SetValue("Column: " + column.ToString());
}
//Writing Data using normal interface
for (int row = 2; row <= rows; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= columns; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.SetValue(row * column);
}
}
}
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Sample.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Sample.xlsx", "application/msexcel", stream);
}
}
public ActionResult ExportExcel(string page, string row, string customerName, string areaExport, string staff, string fromDate, string toDate, string orderBy)
{
int pageInt = int.Parse(page);
int length = int.Parse(row);
//Get data View
var listData = FuncJTable(customerName, areaExport, staff, fromDate, toDate).OrderUsingSortExpression(orderBy).AsNoTracking().ToList();
var listExport = new List <ProporCurrentExportModel>();
var no = 1;
foreach (var item in listData)
{
var itemExport = new ProporCurrentExportModel();
itemExport.No = no;
itemExport.CustomerName = item.CustomerName;
itemExport.Area = item.Area;
itemExport.Staff = item.Staff;
itemExport.CreatedTime = item.CreatedTime != null?item.CreatedTime.Value.ToString("dd/MM/yyyy HH:mm") : "";
itemExport.Proportion = item.Proportion.ToString();
itemExport.TotalCanImp = item.TotalCanImp.ToString();
//if (item.Percent == 100)
//{
// itemExport.Percent = "100%";
//}
//else if (item.Percent == 0)
//{
// itemExport.Percent = "0%";
//}
//else
//{
// itemExport.Percent = String.Format("{0:0.00}", item.Percent) + "%";
//}
no = no + 1;
listExport.Add(itemExport);
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2010;
IWorkbook workbook = application.Workbooks.Create(1);
workbook.Version = ExcelVersion.Excel97to2003;
IWorksheet sheetRequest = workbook.Worksheets.Create("TyTrongButSon");
workbook.Worksheets[0].Remove();
IMigrantRange migrantRange = workbook.Worksheets[0].MigrantRange;
sheetRequest.Range["A1"].ColumnWidth = 24;
sheetRequest.Range["B1"].ColumnWidth = 24;
sheetRequest.Range["C1"].ColumnWidth = 24;
sheetRequest.Range["D1"].ColumnWidth = 24;
sheetRequest.Range["E1"].ColumnWidth = 24;
sheetRequest.Range["F1"].ColumnWidth = 24;
sheetRequest.Range["G1"].ColumnWidth = 24;
//sheetRequest.Range["H1"].ColumnWidth = 24;
//sheetRequest.Range["I1"].ColumnWidth = 24;
//sheetRequest.Range["J1"].ColumnWidth = 24;
sheetRequest.Range["A1:G1"].Merge(true);
sheetRequest.Range["A1"].Text = "Tỷ trọng";
sheetRequest.Range["A1"].CellStyle.Font.FontName = "Calibri";
sheetRequest.Range["A1"].CellStyle.Font.Bold = true;
sheetRequest.Range["A1"].CellStyle.Font.Size = 24;
sheetRequest.Range["A1"].CellStyle.Font.RGBColor = Color.FromArgb(0, 0, 176, 240);
sheetRequest.Range["A1"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheetRequest.ImportData(listExport, 2, 1, true);
sheetRequest["A2"].Text = "TT";
sheetRequest["B2"].Text = "NPP/ĐL/CH";
sheetRequest["C2"].Text = "KHU VỰC";
sheetRequest["D2"].Text = "Tỷ trọng (%)";
sheetRequest["E2"].Text = "TRỮ LƯỢNG TRỐNG (T)";
sheetRequest["F2"].Text = "NHÂN VIÊN";
sheetRequest["G2"].Text = "THỜI GIAN NHẬP DỮ LIỆU";
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
tableHeader.Font.Color = ExcelKnownColors.White;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = Color.FromArgb(0, 0, 122, 192);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.None;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.None;
sheetRequest["A2:G2"].CellStyle = tableHeader;
sheetRequest.Range["A2:G2"].RowHeight = 20;
sheetRequest.UsedRange.AutofitColumns();
string ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var fileName = "ExportTyTrongButSon" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
workbook.Close();
excelEngine.Dispose();
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
