private void btnStage3_Click(object sender, EventArgs e)
{
if (tbStage2.Text == "")
{
return;
}
string fn2 = tbStage2.Text;
if (!(File.Exists(fn2)))
{
return;
}
string fn3 = fn2.Replace("stage2", "stage3");
ExcelFile xls = new XlsFile(true); //Create a new file.
xls.Open(fn2); //Import the csv text.
DoStage3(tbOutputs.Text, ref xls, fn3.Replace(".xlsx", ".txt"));
if (File.Exists(fn3))
{
try
{
File.Delete(fn2);
}
catch
{
MessageBox.Show("Can't delete " + fn3 + " - is it open?", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
xls.Save(fn3);
MessageBox.Show("Done.", "MP3 App", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public ExcelFile CreateReport(String path, String NamLamViec, String ThangLamViec, String TrangThai)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThangNam = "";
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptKeToan_BangCanDoiSoDu");
LoadData(fr, NamLamViec, ThangLamViec, TrangThai);
NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String ThangNam = "Tháng "+ThangLamViec+" năm " + NamLamViec;
String NamTruoc = "Số năm " + (Convert.ToInt16(NamLamViec) - 1) + " chuyển sang";
String NamNay = "Số trong năm " + NamLamViec;
String NamSau = "Số trong năm " + (Convert.ToInt16(NamLamViec) + 1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("ThangNam", ThangNam);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("NamTruoc", NamTruoc);
fr.SetValue("NamNay", NamNay);
fr.SetValue("NamSau", NamSau);
fr.Run(Result);
return Result;
}
/// <summary>
/// Draws the used devices section to the file.
/// </summary>
/// <param name="file">The file that is drawn to.</param>
/// <param name="dlr">The report being drawn.</param>
/// <param name="row">The x coordinate to start drawing the section in cells.</param>
/// <param name="col">The y coordinate to start drawing the section in cells.</param>
/// <returns>A Tuple contining the width and height respectively of the drawn section.</returns>
protected Tuple <int, int> DrawUsedDevices(XlsFile file, DataLogReport dlr, int row, int col)
{
var l = dlr.localization;
// Draw the header
file.SetCellValue(row, col, l.serialNumber, sectionHeaderFormat);
file.SetCellValue(row, col + 1, l.name, sectionHeaderFormat);
file.SetCellValue(row, col + 2, l.certificationDate, sectionHeaderFormat);
file.SetCellValue(row, col + 3, l.deviceModel, sectionHeaderFormat);
// Draw the content
var offset = 1;
foreach (var device in dlr.devices)
{
var rowoff = row + offset;
file.SetCellValue(rowoff, col, device.serialNumber, sectionContentFormat);
file.SetCellValue(rowoff, col + 1, device.name, sectionContentFormat);
file.SetCellValue(rowoff, col + 2, GetDeviceCalibrationTime(device), sectionContentFormat);
file.SetCellValue(rowoff, col + 3, l.GetDeviceModelString(device.serialNumber.deviceModel), sectionContentFormat);
offset++;
}
return(new Tuple <int, int>(4, offset));
}
/// <summary>
/// Initializes the formats for the given file.
/// </summary>
/// <param name="file">File.</param>
protected void InitializeToFile(XlsFile file)
{
titleFormat = CreateTitleTextFormat(file);
sectionHeaderFormat = CreateSectionHeaderFormat(file);
sectionContentFormat = CreateSectionContentFormat(file);
sessionBreakFormat = CreateSessionBreakFormat(file);
}
public override ReportRunOutcome Run(IDbConnectionFactory connectionFactory, XlsFile excelFile, StringBuilder stringOutput)
{
using (var dataSet = GetDataSet(connectionFactory))
{
var orgUnit = dataSet.Tables["OrgUnit"];
var user = dataSet.Tables["User"];
var activeUser = new DataView(user, ActiveUserCondition, SortUsersBy, DataViewRowState.CurrentRows);
var inactiveUser = new DataView(user, "NOT " + ActiveUserCondition, SortUsersBy, DataViewRowState.CurrentRows);
var haveActivity = (user.Select(HaveActivityCondition).Length > 0);
using (var flexCelReport = new FlexCelReport(true))
{
SetProperties(flexCelReport);
SetCreditReportProperties(flexCelReport);
flexCelReport.SetValue("ShowGrandTotal", orgUnit.Rows.Count > 1);
flexCelReport.AddTable("OrgUnit", orgUnit);
flexCelReport.AddTable("ActiveUser", activeUser);
flexCelReport.AddTable("InactiveUser", inactiveUser);
flexCelReport.Run(excelFile);
}
return(haveActivity ? ReportRunOutcome.FileResult : ReportRunOutcome.NoResults);
}
}
/// <summary>
/// Draws the reports device measurement statistics to the file.
/// </summary>
/// <returns>A Tuple containing the width and height respectively of the drawn section.</returns>
/// <param name="file">The file that is drawn to.</param>
/// <param name="dlr">The report being drawn.</param>
/// <param name="row">The x coordinate to start drawing the section in cells.</param>
/// <param name="col">The y coordinate to start drawing the section in cells.</param>
protected Tuple <int, int> DrawDeviceAverages(XlsFile file, DataLogReport dlr, int row, int col)
{
var l = dlr.localization;
// Draw header
file.SetCellValue(row, col, l.serialNumber, sectionHeaderFormat);
file.SetCellValue(row, col + 1, l.minimum, sectionHeaderFormat);
file.SetCellValue(row, col + 2, l.maximum, sectionHeaderFormat);
file.SetCellValue(row, col + 3, l.average, sectionHeaderFormat);
// Draw the content
var offset = 1;
foreach (var pair in dlr.dataLogResults)
{
var ro = row + offset++;
var sensorType = pair.Key.type;
var u = ion.preferences.units.DefaultUnitFor(sensorType);
var min = pair.Value.minimum.ConvertTo(u);
var max = pair.Value.maximum.ConvertTo(u);
var avg = pair.Value.average.ConvertTo(u);
file.SetCellValue(ro, col, pair.Key.device.serialNumber, sectionContentFormat);
file.SetCellValue(ro, col + 1, SensorUtils.ToFormattedString(min, true), sectionContentFormat);
file.SetCellValue(ro, col + 2, SensorUtils.ToFormattedString(max, true), sectionContentFormat);
file.SetCellValue(ro, col + 3, SensorUtils.ToFormattedString(avg, true), sectionContentFormat);
}
return(new Tuple <int, int>(4, offset));
}
/// <summary>
/// hàm khởi tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NamLamViec"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String iNamLamViec, String iQuy, String iID_MaLoaiDoanhNghiep)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThang = ReportModels.Ngay_Thang_Nam_HienTai();
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptTCDN_BaoCaoTongHop");
LoadData(fr, iNamLamViec,iQuy,iID_MaLoaiDoanhNghiep);
String Thang = "";
if (iQuy == "1")
Thang = "3";
else if (iQuy == "2")
{
Thang = "6";
}
else if (iQuy == "3")
{
Thang = "9";
}
else
{
Thang = "cả năm";
}
fr.SetValue("Thang", Thang);
fr.SetValue("Ngay", NgayThang);
fr.SetValue("Nam", iNamLamViec);
fr.SetValue("NamTruoc", Convert.ToInt16(iNamLamViec)-1);
fr.Run(Result);
return Result;
}
/// <summary>
/// Abre el archivo; Si no se a definido pFilePath, entonces se crea y abre un nuevo archivo
/// </summary>
/// <param name="pFilePath">Directorio, nombre y extensión del archivo</param>
public void InitializeExcel(string pFilePath = null)
{
try
{
if (string.IsNullOrWhiteSpace(pFilePath))
{
XlsFile = new XlsFile(); // Inicializar el atributo de la clase con una nueva instancia
XlsFile.NewFile(1); // Crear y abre un nuevo archivo con una sola hoja de trabajo
XlsFile.SheetName = DateTime.Now.ToString("yyyy"); // Cambiarle el nombre a la hoja de trabajo predeterminada
}
else
{
XlsFile vXlsFile = new XlsFile(pFilePath); // Crear una nueva instancia y abre el archivo que se recibe por parámetro
if (vXlsFile == null)
{
throw new NullReferenceException(string.Format("Cant open the excel file in the path: {0}.", pFilePath));
}
else
{
vXlsFile.AllowOverwritingFiles = true; // Permitir sobreescribir el archivo
XlsFile = vXlsFile; // Inicializar el atributo de la clase con la nueva instancia
}
}
}
catch (Exception vE)
{
throw vE;
}
}
public ExcelFile CreateReport(String path, String iID_MaTaiKhoan)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThangNam = "";
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptKeToanTongHop_InTaiKhoanChiTiet");
LoadData(fr, iID_MaTaiKhoan);
NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("Ngay", DateTime.Now.Day);
fr.SetValue("Thang", DateTime.Now.Month);
fr.SetValue("Nam", DateTime.Now.Year);
if (iID_MaTaiKhoan == Guid.Empty.ToString())
{
fr.SetValue("TaiKhoan", "");
}
else
{
fr.SetValue("TaiKhoan", iID_MaTaiKhoan);
}
fr.Run(Result);
return Result;
}
/// <summary>
/// Class that convert json to xls
/// </summary>
/// <param name="name">Name of file.</param>
public ExcelIO(string name, string content)
{
Xls = new XlsFile(1, TExcelFileFormat.v2010, true);
//for linux
//var path = @$"../share/{name}.xlsx";
var path = @$ "..\{name}.xlsx";
private void pictureBox2_Click(object sender, EventArgs e)
{
XlsFile excel = new XlsFile(true);
excel.NewFile();
excel.SetCellValue(1, 3, " Bayi Satış"); // başlık ekle
int ek = 3;
for (int i = 1; i <= dataGridView1.ColumnCount; i++) // başlıklar
{
excel.SetCellValue(3, i, dataGridView1.Columns[i - 1].Name);
}
for (int i = 1; i <= dataGridView1.RowCount - 1; i++) // satırlar
{
for (int k = 1; k <= dataGridView1.ColumnCount; k++)
{
excel.SetCellValue(i + ek, k, dataGridView1[k - 1, i - 1].Value.ToString());
}
}
saveFileDialog1.Filter = "*.xls|*.xls";
saveFileDialog1.ShowDialog();
string yol2 = saveFileDialog1.FileName;
try
{
excel.Save("" + yol2 + "");
}
catch
{
MessageBox.Show("Aktarma Başarısız.."); return;
}
MessageBox.Show("Excele Aktarma İşlemi Bitti.");
}
public static string TextoCelula(XlsFile excel, string celula)
{
var linha = Convert.ToInt16(SoNumeros(celula));
var coluna = LetrasParaNumeros(SoLetras(celula));
return(excel.GetStringFromCell(linha, coluna));
}
/// <summary>
///
/// </summary>
/// <param name="xls"></param>
/// <param name="sheet">Start from 1</param>
/// <returns></returns>
public static SheetData GetSheetData(XlsFile xls, int sheet)
{
xls.ActiveSheet = sheet;
SheetData sheetData = new SheetData();
int colCount = xls.GetColCount(sheet);
int rowCount = xls.GetRowCount(sheet);
int row = 1;
for (int col = 1; col <= colCount; col++)
{
string val = Convert.ToString(xls.GetCellValue(row, col));
if (string.IsNullOrEmpty(val))
{
break;
}
sheetData.Columns.Add(val.Trim());
}
colCount = sheetData.Columns.Count();
row++;
while (row <= rowCount)
{
object[] recordRow = new object[colCount];
for (int col = 1; col <= colCount; col++)
{
recordRow[col - 1] = xls.GetCellValue(row, col);
}
sheetData.Records.Add(recordRow);
row++;
}
return(sheetData);
}
private void AddXlsFile(string FileName)
{
XlsFile xls = new XlsFile();
try
{
xls.Open(FileName);
}
catch (FlexCelXlsAdapterException ex)
{
if (ex.ErrorCode == XlsErr.ErrInvalidPassword)
{
return;
}
throw;
}
for (int sheet = 1; sheet <= xls.SheetCount; sheet++)
{
xls.ActiveSheet = sheet;
for (int r = 1; r <= xls.RowCount; r++)
{
for (int cindex = 1; cindex <= xls.ColCountInRow(r); cindex++)
{
int XF = -1;
object cell = xls.GetCellValueIndexed(r, cindex, ref XF);
AddWord(Convert.ToString(cell), FileName); //we could use TFlxNumberFormat.FormatValue() here, but we don't care about formatted values for searching.
}
}
}
}
/// <summary>
/// Tạo file PDF xuất dữ liệu
/// </summary>
/// <param name="path"></param>
/// <param name="MaND"></param>
/// <param name="sLNS"></param>
/// <param name="iThang_Quy"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="iID_MaPhongBan"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String MaND, String sLNS, String iThang_Quy, String iID_MaDonVi, String iID_MaPhongBan)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptQuyetToan_PhongBan");
LoadData(fr, MaND, sLNS, iThang_Quy, iID_MaDonVi, iID_MaPhongBan);
String Nam = ReportModels.LayNamLamViec(MaND);
if (iThang_Quy == "-1")
{
iThang_Quy = "Tất cả các Quý ";
}
if (iThang_Quy == "5")
{
iThang_Quy = "Bổ Sung ";
}
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.SetValue("Nam", Nam);
fr.SetValue("Quy", iThang_Quy);
fr.Run(Result);
return Result;
}
/// <summary>
/// Khởi tạo báo cáo
/// </summary>
/// <param name="path"> đường dẫn</param>
/// <param name="NamLamViec">Năm làm việc</param>
/// <param name="iID_MaDonVi">Mã đơn vị</param>
/// <param name="DotPhanBo">Mã đợt phân bổ</param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String iID_MaDonVi, String DotPhanBo, String iID_MaTrangThaiDuyet)
{
String MaND = User.Identity.Name;
XlsFile Result = new XlsFile(true);
Result.Open(path);
String TenDV = "";
DataTable dtDonVi = PhanBo_ReportModels.DanhSachDonVi2(MaND,iID_MaTrangThaiDuyet, DotPhanBo);
for (int i = 0; i < dtDonVi.Rows.Count;i++ )
{
if (iID_MaDonVi == dtDonVi.Rows[i]["iID_MaDonVi"].ToString())
{
TenDV = dtDonVi.Rows[i]["TenHT"].ToString();
break;
}
}
String NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String Dot = ReportModels.Get_STTDotPhanBo(MaND, iID_MaTrangThaiDuyet, DotPhanBo).ToString();
//set các thông số
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptThongBaoBoSungNganSach");
LoadData(fr, MaND, iID_MaTrangThaiDuyet, iID_MaDonVi, DotPhanBo);
fr.SetValue("Nam", ReportModels.LayNamLamViec(MaND));
fr.SetValue("TenDv", TenDV);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("Dot", Dot);
fr.SetValue("Tien", CommonFunction.TienRaChu(Tong));
fr.Run(Result);
return Result;
}
/// <summary>
/// Tạo báo cáo
/// </summary>
/// <param name="path">Đường dẫn tới file báo cáo</param>
/// <param name="iTuNgay">Từ ngày</param>
/// <param name="iDenNgay">Đến ngày</param>
/// <param name="iTuThang">Từ tháng</param>
/// <param name="iDenThang">Đến tháng</param>
/// <param name="iNam">Năm</param>
/// <param name="iDVTinh">Đơn vị tính</param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String iTuNgay, String iDenNgay, String iTuThang, String iDenThang, String iNam, String iDVTinh, String iSoTo, String iReport, String UserID, String iID_MaTrangThaiDuyet)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
//Thêm chữ ký vào báo cáo
fr = ReportModels.LayThongTinChuKy(fr, "rptKT_TongHop_RutDuToan");
LoadData(fr, iTuNgay, iDenNgay, iTuThang, iDenThang, iNam, iDVTinh,iSoTo,iReport,UserID,iID_MaTrangThaiDuyet);
fr.SetValue("cap1", ReportModels.CauHinhTenDonViSuDung(1).ToUpper());
fr.SetValue("cap2", ReportModels.CauHinhTenDonViSuDung(2).ToUpper());
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
DataTable dt = sLNS(GetLNS(iTuNgay, iDenNgay, iTuThang, iDenThang, iNam,UserID,iID_MaTrangThaiDuyet),iSoTo,iReport);
if (dt.Rows.Count > 0)
for (int i = 0; i < dt.Rows.Count; i++)
fr.SetValue("LNS" + (i + 1), dt.Rows[i]["sMoTa"]);
if (iDVTinh.Equals("rD"))
fr.SetValue("DVT", "Đồng");
else if (iDVTinh.Equals("rND"))
fr.SetValue("DVT", "Nghìn đồng");
else if (iDVTinh.Equals("rTrD"))
fr.SetValue("DVT", "Triệu đồng");
fr.SetValue("NgayThangTK", "Từ ngày " + iTuNgay + "/ " + iTuThang + " Đến ngày " + iDenNgay + " /" + iDenThang + "/" + iNam);
if (iReport.Equals("A4Ngang"))
fr.SetValue("SoTo", "Tờ số: "+iSoTo);
fr.Run(Result);
return Result;
}
private void writeHyperLinks_Click(object sender, System.EventArgs e)
{
if (Xls == null)
{
MessageBox.Show("You need to open a file first.");
return;
}
ExcelFile XlsOut = new XlsFile(true);
XlsOut.NewFile(1);
for (int i = 1; i <= Xls.HyperLinkCount; i++)
{
TXlsCellRange Range = Xls.GetHyperLinkCellRange(i);
THyperLink HLink = Xls.GetHyperLink(i);
int XF = -1;
object Value = Xls.GetCellValue(Range.Top, Range.Left, ref XF);
XlsOut.SetCellValue(i, 1, Value, XlsOut.AddFormat(Xls.GetFormat(XF)));
XlsOut.AddHyperLink(new TXlsCellRange(i, 1, i, 1), HLink);
}
if (saveFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
XlsOut.Save(saveFileDialog1.FileName);
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Process.Start(saveFileDialog1.FileName);
}
}
public static ToaDo getPositionTableFlexExcel(XlsFile Result, string TextFil, int Sheet = 1)
{
Result.ActiveSheet = Sheet;//we'll read sheet1. We could loop over the existing sheets by using xls.SheetCount and xls.ActiveSheet
for (int row = 1; row <= Result.RowCount; row++)
{
for (int colIndex = 1; colIndex <= Result.GetColCount(Sheet, false); colIndex++) //Don't use xls.ColCount as it is slow: See Performance.Pdf
{
try
{
var valueCell = Result.GetCellValue(row, colIndex);
if (valueCell != null)
{
var dataString = valueCell.ToString();
if (!string.IsNullOrEmpty(dataString))
{
if (dataString.ToUpper().IndexOf(TextFil.ToUpper()) >= 0)//bat key ton tai table vidu: <#ChiTiet.TENDICHVU>
{
return(new ToaDo()
{
X = row, Y = colIndex
});
}
}
}
}
catch (Exception)
{
}
}
}
return(new ToaDo()
{
X = 0, Y = 0
});
}
public ExcelFile CreateReport(String path, String NamLamViec, String TuThang, String DenThang, String TrangThai)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThangNam = "";
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptKeToan_SoCaiTaiKhoan");
LoadData(fr, NamLamViec, TuThang, DenThang, TrangThai);
NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String ThangNam = "Từ tháng " + TuThang + " đến tháng " + DenThang + " Năm " + NamLamViec;
if (TuThang == DenThang)
ThangNam = "Tháng " + TuThang + " Năm " + NamLamViec;
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("ThangNam", ThangNam);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("Ngay", DateTime.Now.Day);
fr.SetValue("Thang", DateTime.Now.Month);
fr.SetValue("Nam", DateTime.Now.Year);
fr.Run(Result);
return Result;
}
/// <summary>
/// Tạo file PDF xuất dữ liệu của quyết toán từng đơn vị
/// </summary>
/// <param name="path"></param>
/// <param name="MaND">Mã người dùng</param>
/// <param name="sLNS">Loại ngân sách</param>
/// <param name="iThang_Quy">Quý</param>
/// <param name="iID_MaDonVi">Mã đơn vị</param>
/// <param name="iID_MaNamNganSach">Năm ngân sách</param>
/// <param name="MaPhongBan">Mã phòng ban</param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String MaND, String sLNS, String iThang_Quy, String iID_MaDonVi, String iID_MaNamNganSach, String MaPhongBan)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptQuyetToan_DonVi_LNS");
LoadData(fr, MaND, sLNS, iThang_Quy, iID_MaDonVi, iID_MaNamNganSach, MaPhongBan);
String Nam = ReportModels.LayNamLamViec(MaND);
String NamNganSach = "";
if (iID_MaNamNganSach == "1")
NamNganSach = "QUYẾT TOÁN NĂM TRƯỚC";
else if (iID_MaNamNganSach == "2")
NamNganSach = "QUYẾT TOÁN NĂM NAY";
else
{
NamNganSach = "TỔNG HỢP";
}
String sTenDonVi = DonViModels.Get_TenDonVi(iID_MaDonVi);
fr.SetValue("BoQuocPhong", ReportModels.CauHinhTenDonViSuDung(1));
fr.SetValue("QuanKhu", ReportModels.CauHinhTenDonViSuDung(2));
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.SetValue("Nam", Nam);
fr.SetValue("Quy", iThang_Quy);
fr.SetValue("NamNganSach", NamNganSach);
fr.SetValue("sTenDonVi", sTenDonVi);
fr.Run(Result);
return Result;
}
partial void RandomizeClick(UIKit.UIBarButtonItem sender)
{
if (XlsUrl == null)
{
ShowHowToUse();
return;
}
XlsFile xls = new XlsFile(XlsPath, true);
//We'll go through all the numeric cells and make them random numbers
Random rnd = new Random();
for (int row = 1; row <= xls.RowCount; row++)
{
for (int colIndex = 1; colIndex < xls.ColCountInRow(row); colIndex++)
{
int XF = -1;
object val = xls.GetCellValueIndexed(row, colIndex, ref XF);
if (val is double)
{
xls.SetCellValue(row, xls.ColFromIndex(row, colIndex), rnd.Next());
}
}
}
//We can't save to the original file, we don't have permissions.
XlsPath = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.InternetCache),
"tmpFlexCel" + Path.GetExtension(XlsUrl.Path));
xls.Save(XlsPath);
Refresh();
}
public ExcelFile CreateReport(String path, String iID_MaChungTu, String iID_MaDonVi, String iThang, String iNam, String iID_MaThongTri, String sSoCT, String iLoai)
{
Thang = iThang + "/" + iNam;
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptThuNopThongTri");
LoadData(fr, iID_MaChungTu, iID_MaDonVi, sSoCT, iLoai);
////lay ten thong tri
if (!String.IsNullOrEmpty(iID_MaThongTri))
{
DataTable dtLoaiThongTri;
String SQL = "SELECT sLoaiThongTri,sTenLoaiNS FROM KT_LoaiThongTri WHERE iID_MaThongTri=@iID_MaThongTri";
SqlCommand cmd = new SqlCommand(SQL);
cmd.Parameters.AddWithValue("@iID_MaThongTri", iID_MaThongTri);
dtLoaiThongTri = Connection.GetDataTable(cmd);
cmd.Dispose();
if (dtLoaiThongTri != null && dtLoaiThongTri.Rows.Count > 0)
{
LoaiTT = Convert.ToString(dtLoaiThongTri.Rows[0]["sLoaiThongTri"]);
LoaiNS = Convert.ToString(dtLoaiThongTri.Rows[0]["sTenLoaiNS"]);
dtLoaiThongTri.Dispose();
}
}
else
{
LoaiTT = "Thu nộp Ngân sách";
LoaiNS = "Ngân sách...";
}
String NoiDung = "", sNgayLap = "", Nam = "", dNgayChungTu="";
DataTable dt = ThuNop_ChungTuModels.GetChungTu(iID_MaChungTu);
if (dt.Rows.Count > 0)
{
NoiDung = dt.Rows[0]["sNoiDung"].ToString();
dNgayChungTu = dt.Rows[0]["dNgayChungTu"].ToString();
Nam = dt.Rows[0]["iNamLamViec"].ToString();
dt.Dispose();
}
if (!String.IsNullOrEmpty(dNgayChungTu))
{
sNgayLap = " năm " + Nam;
}
fr.SetValue("NgayThangNam", "Ngày "+dNgayChungTu.Substring(0, 2)+ " tháng " + dNgayChungTu.Substring(3, 2) + " năm " + dNgayChungTu.Substring(6, 4));
fr.SetValue("NoiDung", NoiDung);
fr.SetValue("Loai", LoaiTT);
fr.SetValue("DonVi", DonViModels.Get_TenDonVi(iID_MaDonVi));
fr.SetValue("dNgayLap", sNgayLap);
fr.SetValue("LNS", LoaiNS);
fr.SetValue("sL", "");
fr.SetValue("sK", "");
fr.SetValue("Nam", Nam);
fr.SetValue("BoQuocPhong", ReportModels.CauHinhTenDonViSuDung(1).ToUpper());
fr.SetValue("QuanKhu", ReportModels.CauHinhTenDonViSuDung(2).ToUpper());
fr.Run(Result);
return Result;
}
/// <summary>
/// tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="Province"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, string Province)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
//tính tổng tiền
if (Province == null) Province = "0";
DataTable dt = LayDanhSach(Province);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
//set các thông số
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_DanhSachNghiHuu");
LoadData(fr, Province);
if (Province != "0" & Province != "")
{
string sProvinceName = Province_Get_Ten(Province);
fr.SetValue("Province", "ĐANG SINH SỐNG TẠI " + sProvinceName.ToUpper());
}
else
fr.SetValue("Province", "");
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("QuanKhu", QuanKhu);
fr.Run(Result);
return Result;
}
partial void CreateFile(NSObject sender)
{
var xls = new XlsFile(1, true);
xls.SetCellValue(1, 1, "Hello OSX Unified!");
xls.SetCellValue(2, 1, new TFormula("=\"Make sure to \" & \"look at the Windows examples\""));
xls.SetCellValue(3, 1, "for information on how to use FlexCel");
xls.SetCellValue(5, 1, "Concepts are similar, so it doesn't make sense to repeat them all here.");
xls.AutofitCol(1, false, 1.2);
NSSavePanel SaveDialog = new NSSavePanel();
{
SaveDialog.Title = "Save file as...";
SaveDialog.AllowedFileTypes = new string[] { "xlsx", "xls" };
SaveDialog.BeginSheet(Window,
(x) =>
{
if (SaveDialog.Url != null)
{
xls.Save(SaveDialog.Url.Path);
}
});
}
}
public ExcelFile CreateReport(String path, String NamLamViec, String ThangLamViec, String iID_MaDonVi, String TrangThai)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThangNam = "";
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptKeToan_QuyetToanNam_DonVi");
LoadData(fr, NamLamViec, ThangLamViec, iID_MaDonVi, TrangThai);
String sTenDonVi = Convert.ToString(CommonFunction.LayTruong("NS_DonVi", "iID_MaDonVi", iID_MaDonVi, "sTen"));
NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String ThangNam ="Tháng "+ ThangLamViec+"/năm " + NamLamViec;
String NamTruoc = "Số năm " + (Convert.ToInt16(NamLamViec) - 1) + " chuyển sang";
String NamNay = "Số trong năm " + NamLamViec;
String NamSau = "Số trong năm " + (Convert.ToInt16(NamLamViec) + 1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("ThangNam", ThangNam);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("NamTruoc", NamTruoc);
fr.SetValue("NamNay", NamNay);
fr.SetValue("NamSau", NamSau);
fr.SetValue("DonVi", sTenDonVi);
fr.SetValue("Ngay", DateTime.Now.Day);
fr.SetValue("Thang", DateTime.Now.Month);
fr.SetValue("Nam", DateTime.Now.Year);
fr.Run(Result);
return Result;
}
protected void BtnReadCellA1_Click(object sender, EventArgs e)
{
ExcelFile Xls = new XlsFile();
if (FileBox.PostedFile == null || FileBox.PostedFile.InputStream == null || FileBox.PostedFile.InputStream.Length == 0)
{
LabelA1.Text = "No file selected";
return;
}
FileBox.PostedFile.InputStream.Position = 0;
try
{
Xls.Open(FileBox.PostedFile.InputStream);
object v = Xls.GetCellValue(1, 1);
if (v == null)
{
LabelA1.Text = "Cell A1 is empty";
}
else
{
LabelA1.Text = "Cell A1 has the value: " + Convert.ToString(v);
}
}
catch (Exception ex)
{
LabelA1.Text = ex.Message;
}
}
public ExcelFile CreateReport(String path, String iID_MaChungTu)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
DataTable dt = dt_rptKTTGTongHopCTGS(iID_MaChungTu);
String SoGhiSo = "";
String Ngay = "";
String Thang = "";
String Nam = "";
if (dt.Rows.Count > 0)
{
SoGhiSo = dt.Rows[0]["sSoChungTuGhiSo"].ToString();
Ngay = dt.Rows[0]["iNgayCT"].ToString();
Thang =dt.Rows[0]["iThangCT"].ToString();
Nam = dt.Rows[0]["iNamLamViec"].ToString();
}
String NgayThang = Ngay + "/" + Thang + "/" + Nam;
String Count = dt.Rows.Count.ToString();
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptKTTienMatTongHopCTGS");
LoadData(fr,iID_MaChungTu);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("SoGhiSo", SoGhiSo);
fr.SetValue("NgayThang", NgayThang);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("Count", Count);
fr.Run(Result);
return Result;
}
public ExcelFile CreateReport(String path,String iID_MaChungTu)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String CucTaiChinh = ReportModels.CauHinhTenDonViSuDung(2);
FlexCelReport fr = new FlexCelReport();
String ngay = ReportModels.Ngay_Thang_Nam_HienTai();
DateTime dt = new System.DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
LoadData(fr,iID_MaChungTu);
fr = ReportModels.LayThongTinChuKy(fr, "rptKTKB_ThongTriTongHop");
//fr.SetValue("Nam", iNamLamViec);
fr.SetValue("So", iID_MaChungTu);
//fr.SetValue("Thang", iThang);
fr.SetValue("Ngay", String.Format("{0:dd}", dt));
fr.SetValue("Thangs", String.Format("{0:MM}", dt));
fr.SetValue("Nams", DateTime.Now.Year);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("CucTaiChinh", CucTaiChinh);
fr.SetValue("ngay", ngay);
fr.SetValue("ThangCT", CommonFunction.LayTruong("KTKB_ChungTuChiTiet", "iID_MaChungTu", iID_MaChungTu, "iThangCT"));
fr.SetValue("NgayCT", CommonFunction.LayTruong("KTKB_ChungTuChiTiet", "iID_MaChungTu", iID_MaChungTu, "iNgayCT"));
fr.Run(Result);
return Result;
}
public static DataTable LerTitulosAba(XlsFile excel)
{
DataTable tabela = new DataTable();
int qtdeColunas = excel.ColCountOnlyData;
for (int cont = 1; cont <= qtdeColunas; cont++)
{
Object coluna = excel.GetCellValue(1, cont);
if (coluna != null)
{
try
{
tabela.Columns.Add(coluna.ToString());
}
catch (DuplicateNameException)
{
string nomeArquivo = excel.ActiveFileName.Split('\\')[excel.ActiveFileName.Split('\\').Length - 1],
aba = excel.ActiveSheetByName;
throw new Exception("O arquivo \"" + nomeArquivo + "\"" + ", na aba \"" + aba + "\", está com o nome de coluna \"" + coluna.ToString() + "\" duplicada. \n\n Por favor renomeie a coluna e salve o arquivo antes de prosseguir.");
}
}
}
return tabela;
}
public int contarAbas(string arquivo)
{
XlsFile tempExcel = new XlsFile();
tempExcel.Open(arquivo);
return tempExcel.SheetCount;
}
protected void Page_Load(object sender, EventArgs e)
{
XlsFile xls = new XlsFile();
string DefaultFile = Server.MapPath("~/default.xls");
if (IsPostBack)
{
if (Uploader.HasFile)
{
xls.Open(Uploader.FileContent);
}
else
{
xls.Open(DefaultFile);
}
}
else
{
xls.Open(DefaultFile);
}
Viewer.HtmlExport.ImageNaming = TImageNaming.Guid;
Viewer.HtmlExport.Workbook = xls;
Viewer.RelativeImagePath = "images";
Viewer.HtmlExport.FixIE6TransparentPngSupport = true; //This is only needed if you are using IE and there are transparent png files.
Viewer.ImageExportMode = TImageExportMode.TemporaryFiles;
}
private static void FillColRange(TKeyList Items, XlsFile xls, TXlsCellRange Range, int[] Keys, TSortOrder[] SortOrder, IComparer Comparer)
{
for (int c = Range.Left; c <= Range.Right; c++)
{
object[] Values;
if (Keys == null)
{
int Len = Range.RowCount;
if (Len > 8)
{
Len = 8; //avoid taking up too much memory.
}
Values = new object[Len];
for (int r = 0; r < Values.Length; r++)
{
Values[r] = xls.GetCellValue(Range.Top + r, c);
}
}
else
{
Values = new object[Keys.Length];
for (int r = 0; r < Keys.Length; r++)
{
Values[r] = xls.GetCellValue(Keys[r], c);
}
}
Items.Add(new TKeyItem(c, Values, SortOrder, Comparer));
}
}
private static void FillRowRange(TKeyList Items, XlsFile xls, TXlsCellRange Range, int[] Keys, TSortOrder[] SortOrder, IComparer Comparer)
{
for (int r = Range.Top; r <= Range.Bottom; r++)
{
object[] Values;
if (Keys == null)
{
int Len = Range.ColCount;
if (Len > 8)
{
Len = 8; //avoid taking up too much memory.
}
Values = new object[Len];
for (int c = 0; c < Values.Length; c++)
{
Values[c] = xls.GetCellValue(r, Range.Left + c);
}
}
else
{
Values = new object[Keys.Length];
for (int c = 0; c < Keys.Length; c++)
{
Values[c] = xls.GetCellValue(r, Keys[c]);
}
}
Items.Add(new TKeyItem(r, Values, SortOrder, Comparer));
}
}
private void LoadFile()
{
try
{
//Khai bao bien
FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport();
string path = "";
//Xac dinh ten file mau theo loai bao cao
path = GetXlsFileDesign();
//Add du lieu tu ds, bien vao Flexcel report
if (p_rpType < 30)
{
flcReport.AddTable(p_ds); // Gan du lieu vao ds
}
SetValueFlexcel(ref flcReport); // Thiet lap gt cho cac bien object
#region Luu file xls len memory stream
//FlexCelPreview1.CenteredPreview = true;
//_templateStream.Flush();
try
{
_templateStream = new FileStream(path, FileMode.Open);
}
catch
{
}
_xlsMemoryStream = new MemoryStream();
flcReport.Run(_templateStream, _xlsMemoryStream);//Chet o day???????????????????????
#endregion
#region View file tren mem len FlexCelPreview1
xls = new FlexCel.XlsAdapter.XlsFile();
_xlsMemoryStream.Position = 0;
xls.Open(_xlsMemoryStream);
//
SetValuePageSize(ref flexCelImgExport1);
flexCelImgExport1.Workbook = xls;
FlexCelPreview1.InvalidatePreview();
#endregion
//Hien thi so trag, zoom cua page
txtPage.Text = String.Format("{0} / {1}", FlexCelPreview1.StartPage, FlexCelPreview1.TotalPages);
//UpdateZoom();
//Gan gia tri stream cho bien toan cuc cua form
c_XlsStream = _xlsMemoryStream;
_templateStream.Dispose();
}
catch (Exception ex)
{
//Neu loi ko Kiet xuat dc
MessageBox.MessageBoxBA msgDialog = new Taxi.MessageBox.MessageBoxBA();
msgDialog.Show(this, "Xảy ra lỗi trong quá trình lấy dữ liệu và tạo báo cáo!", "Thông báo", Taxi.MessageBox.MessageBoxButtonsBA.OK, Taxi.MessageBox.MessageBoxIconBA.Error);
//this.Close();
}
}
public ExcelFile CreateReport(String path, String iNamLamViec, String iID_MaChungTu)
{
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String CucTaiChinh = ReportModels.CauHinhTenDonViSuDung(2);
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
DataTable dt=KTLB_ThongTriRutDT(iNamLamViec,iID_MaChungTu);
String NgayCT="";
String ThangCT="";
String NgayThang="";
if(dt.Rows.Count >0)
{
NgayCT = dt.Rows[0]["iNgayCT"].ToString();
ThangCT = dt.Rows[0]["iThangCT"].ToString();
NgayThang = "ngày " + NgayCT + " tháng " + ThangCT;
}
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptKTKB_ThongTriRutDT");
LoadData(fr,iNamLamViec, iID_MaChungTu);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("Nam",iNamLamViec);
fr.SetValue("NgayThang", NgayThang);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("CucTaiChinh", CucTaiChinh);
fr.Run(Result);
return Result;
}
public ExcelFile CreateReport(String path, String iID_MaTrangThaiDuyet, String Thang_Quy, String LoaiThang_Quy)
{
String MaND = User.Identity.Name;
DataTable dt = NguoiDungCauHinhModels.LayCauHinh(MaND);
String DK = "", iNamLamViec = DateTime.Now.Year.ToString(), iID_MaNamNganSach = "1", iID_MaNguonNganSach = "1";
if (dt.Rows.Count > 0)
{
iNamLamViec = Convert.ToString(dt.Rows[0]["iNamLamViec"]);
iID_MaNamNganSach = Convert.ToString(dt.Rows[0]["iID_MaNamNganSach"]);
iID_MaNguonNganSach = Convert.ToString(dt.Rows[0]["iID_MaNguonNganSach"]);
}
XlsFile Result = new XlsFile(true);
Result.Open(path);
String Thang = "tháng";
if (LoaiThang_Quy == "1")
{
Thang = "quý";
}
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptCapPhat_TongHopChiTieuCapNganSach_81");
String NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
LoadData(fr, iID_MaTrangThaiDuyet, Thang_Quy, LoaiThang_Quy,MaND);
fr.SetValue("Nam", iNamLamViec);
fr.SetValue("Thang", Thang);
fr.SetValue("Thang_Quy", Thang_Quy);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.Run(Result);
fr.Dispose();
return Result;
}
private void AutoOpenRun()
{
string DataPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
DataPath = Path.Combine(DataPath, "..");
DataPath = Path.Combine(DataPath, "..");
Setup(edName.Text, edUrl.Text, DataPath);
XlsFile Xls = new XlsFile();
Xls.Open(Path.Combine(DataPath, "Getting Started Reports.template.xls"));
reportStart.Run(Xls);
string FilePath = Path.GetTempPath(); //GetTempFileName does not allow us to specify the "xlt" extension.
string FileName = Path.Combine(FilePath, Guid.NewGuid().ToString() + ".xlt"); //xlt is the extension for excel templates.
try
{
using (FileStream OutStream = new FileStream(FileName, FileMode.Create, FileAccess.Write))
{
FileInfo Fi = new FileInfo(FileName);
Fi.Attributes = FileAttributes.Temporary;
Xls.Save(OutStream);
}
Process.Start(FileName);
}
finally
{
File.Delete(FileName); //As it is an xlt file, we can delete it.
}
}
protected void BtnPdf_Click(object sender, EventArgs e)
{
ExcelFile Xls = new XlsFile();
CreateFile(Xls);
FlexCelPdfExport Pdf = new FlexCelPdfExport(Xls);
using (MemoryStream ms = new MemoryStream())
{
Pdf.BeginExport(ms);
try
{
Pdf.ExportAllVisibleSheets(true, "Getting Started");
}
finally
{
Pdf.EndExport();
}
ms.Position = 0;
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=Test.pdf");
Response.AddHeader("Content-Length", ms.Length.ToString());
Response.ContentType = "application/pdf"; //octet-stream";
Response.BinaryWrite(ms.ToArray());
Response.End();
}
}
/// <summary>
/// tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NgoaiTe"></param>
/// <param name="Quy"></param>
/// <param name="Nam"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String sDeAn, String NgoaiTe, String Quy, String iNamLamViec, String iCapTongHop)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
DataTable dtDVT = QLDA_ReportModel.getdtTien();
String DVT = " triệu đồng";
for (int i = 1; i < dtDVT.Rows.Count; i++)
{
if (NgoaiTe == dtDVT.Rows[i]["iID_MaNgoaiTe"].ToString())
{
DVT = dtDVT.Rows[i]["sTen"].ToString();
}
}
dtDVT.Dispose();
//set các thông số
FlexCelReport fr = new FlexCelReport();
//Toan lục luong
fr = ReportModels.LayThongTinChuKy(fr, "rptKHV_02DT_DTQuy");
LoadData(fr, sDeAn, NgoaiTe, Quy, iNamLamViec);
fr.SetValue("Nam", iNamLamViec);
fr.SetValue("Quy", Quy);
fr.SetValue("Ngay", "Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm " + DateTime.Now.Year.ToString());
fr.SetValue("DonVi", DVT);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("QuanKhu", QuanKhu);
fr.Run(Result);
return Result;
}
private void compareWithExcel_Click(object sender, System.EventArgs e)
{
if (openFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
compareWithExcel.Enabled = false;
validateRecalc.Enabled = false;
try
{
XlsFile xls1 = new XlsFile();
XlsFile xls2 = new XlsFile();
xls1.Open(openFileDialog1.FileName);
xls2.Open(openFileDialog1.FileName);
report.Text = "Compare with Excel: " + openFileDialog1.FileName;
// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// ////////Code here is only needed if you have linked files. In this example we don't know, so we will use it /////////
TWorkspace Work = new TWorkspace(); //Create a workspace
Work.Add(Path.GetFileName(openFileDialog1.FileName), xls1); //Add the original file to it
Work.LoadLinkedFile += new LoadLinkedFileEventHandler(Work_LoadLinkedFile); //Set up an event to load the linked files.
// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CompareXls(xls1, xls2, null);
}
finally
{
compareWithExcel.Enabled = true;
validateRecalc.Enabled = true;
}
}
public ExcelFile CreateReport(String path, String iNamLamViec, String iID_MaChungTu, String sSoChungTuChiTiet, String iID_MaNamNganSach, String iID_MaNguonNganSach)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String CucTaiChinh = ReportModels.CauHinhTenDonViSuDung(2);
String ngay = ReportModels.Ngay_Thang_Nam_HienTai();
DateTime dt = new System.DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
LoadData(fr, iNamLamViec, iID_MaChungTu, sSoChungTuChiTiet, iID_MaNamNganSach, iID_MaNguonNganSach);
//Lấy thông tin đơn vị nhận
DataTable dtDVN = dtDanhSachDonVi_Nhan(iNamLamViec, "", iID_MaChungTu);
String DonVi = "";
if (dtDVN.Rows.Count > 0)
{
DonVi=dtDVN.Rows[0]["sTenDonVi_Nhan"].ToString();
}
fr = ReportModels.LayThongTinChuKy(fr, "rptThongTriLoaiNS");
fr.SetValue("Nam", iNamLamViec);
fr.SetValue("So", sSoChungTuChiTiet);
fr.SetValue("Thang", String.Format("{0:MM}", dt));
fr.SetValue("Ngay", ngay);
fr.SetValue("Ngay", String.Format("{0:dd}", dt));
fr.SetValue("Thangs", String.Format("{0:MM}", dt));
fr.SetValue("Nams", DateTime.Now.Year);
fr.SetValue("ThangCT", CommonFunction.LayTruong("KTKB_ChungTuChiTiet", "sSoChungTuChiTiet", sSoChungTuChiTiet, "iThangCT"));
fr.SetValue("DonVi", DonVi);
//fr.SetValue("LoaiThangQuy", TenLoaiThangQuy);
fr.SetValue("CucTaiChinh", CucTaiChinh);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("Tien", CommonFunction.TienRaChu(tong));
fr.Run(Result);
return Result;
}
private void button5_Click(object sender, EventArgs e)
{
XlsFile excel = new XlsFile(true);
excel.NewFile();
excel.SetCellValue(1, 2, " Ürünler");
int ek = 3;
for (int i = 1; i <= dataGridView1.ColumnCount; i++)
{
excel.SetCellValue(3, i, dataGridView1.Columns[i - 1].Name);
}
for (int i = 1; i <= dataGridView1.RowCount - 1; i++)
{
for (int k = 1; k <= dataGridView1.ColumnCount; k++)
{
excel.SetCellValue(i + ek, k, dataGridView1[k - 1, i - 1].Value.ToString());
}
}
saveFileDialog1.Filter = "*.xlsx|*.xlsx";
saveFileDialog1.ShowDialog();
string yol = saveFileDialog1.FileName;
try
{
excel.Save("" + yol + "");
}
catch
{
MessageBox.Show("Aktarma Başarısız.."); return;
}
MessageBox.Show("Excele Aktarma İşlemi Bitti.");
}
private void AutoOpenRun()
{
string DataPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
DataPath = Path.Combine(DataPath, "..");
DataPath = Path.Combine(DataPath, "..");
Setup(edName.Text, edUrl.Text, DataPath);
XlsFile Xls = new XlsFile();
Xls.Open(Path.Combine(DataPath, "Getting Started Reports.template.xlsx"));
reportStart.Run(Xls);
string FilePath = Path.GetTempPath(); //GetTempFileName does not allow us to specify the "xltx" extension.
string FileName = Path.Combine(FilePath, Guid.NewGuid().ToString() + ".xltx"); //xltx is the extension for excel templates.
try
{
using (FileStream OutStream = new FileStream(FileName, FileMode.Create, FileAccess.ReadWrite))
{
Xls.IsXltTemplate = true; //Make it an xltx template.
Xls.Save(OutStream);
}
Process.Start(FileName);
}
finally
{
//For .Net 4 and newer you can use Task.Run here. See https://download.tmssoftware.com/flexcel/doc/net/tips/automatically-open-generated-excel-files.html
new Thread(delegate()
{
Thread.Sleep(30000); //wait for 30 secs to give Excel time to start.
File.Delete(FileName); //As it is an xltx file, we can delete it even when it is open on Excel.
});
}
}
private bool Refresh()
{
RemoveOldPdf();
try
{
XlsFile xls = new XlsFile(XlsPath);
PdfPath = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.InternetCache),
Path.ChangeExtension(Path.GetFileName(XlsUrl.Path), ".pdf"));
using (FlexCelPdfExport pdf = new FlexCelPdfExport(xls, true))
{
using (FileStream fs = new FileStream(PdfPath, FileMode.Create))
{
pdf.Export(fs);
}
}
Viewer.LoadRequest(new NSUrlRequest(NSUrl.FromFilename(PdfPath)));
}
catch (Exception ex)
{
Viewer.LoadHtmlString("<html>Error opening " + System.Security.SecurityElement.Escape(Path.GetFileName(XlsUrl.Path))
+ "<br><br>" + System.Security.SecurityElement.Escape(ex.Message) + "</html>", null);
return(false);
}
return(true);
}
/// <summary>
/// Читает данные с текущего листа XLS-файла.
/// </summary>
/// <param name="xlsFile">XSL-файл.</param>
/// <returns>Таблица с прочитанными данными.</returns>
private static DataTable ReadDataTable(XlsFile xlsFile)
{
// установить имя таблицы
DataTable table = new DataTable(xlsFile.GetSheetName(xlsFile.ActiveSheet));
// первая строка - названия столбцов
// вторая строка - типы столбцов
if (xlsFile.RowCount < 2)
throw new CoreInvalidOperationException(Resources.ResourceManager.GetString("InvalidFileFormatException"));
// прочитать имена столбцов для таблицы
for (int col = 0; col < xlsFile.ColCount; col++)
{
if (xlsFile.GetCellValue(1, col + 1) == null)
break;
// прочитать тип столбца
Type columnType = Type.GetType(xlsFile.GetCellValue(2, col + 1).ToString(), false);
if (columnType == null)
throw new CoreInvalidOperationException(Resources.ResourceManager.GetString("InvalidFileFormatException"));
table.Columns.Add(xlsFile.GetCellValue(1, col + 1).ToString(), columnType);
}
// прочитать данные
int rowFrom = 2;
/* XlsFile тупо обрабатывает
* вложенные картинки. Поместить картинку в определённую клетку можно,
* а вот взять её из клетки нельзя. Взять картинку можно только из массива
* рисунков Excel, начинающегося почему то с 2 (хотя в доке написано ч 1!).
* Будем надеяться, что они там в правильном порядке лежат.
*/
int imageCount = 2;
for (int row = rowFrom; row < xlsFile.RowCount; ++row)
{
// Т.к. XlsFile очень часто врёт по поводу количества строк в файле,
// то мы подстраховываемся таким образом. Считаем, что данные кончились,
// если колонка 'A' в строке пустая
if (xlsFile.GetCellValue(row + 1, 1) == null)
break;
object[] values = new object[table.Columns.Count];
for(int col = 0; col < table.Columns.Count; ++col)
{
if(table.Columns[col].DataType == typeof( byte[] ))
{
TXlsImgType imageType = TXlsImgType.Unknown;
values[col] = ConvertValue( xlsFile.GetImage( imageCount++, ref imageType ),
table.Columns[col].DataType );
}
else
{
values[col] = ConvertValue( xlsFile.GetCellValue( row + 1, col + 1 ),
table.Columns[col].DataType );
}
}
table.Rows.Add(values);
}
return table;
}
private string SerializaProposta(XlsFile excel)
{
var propostas = new List <Proposta>();
string gerente = String.Empty,
promotora = String.Empty,
agente = String.Empty,
empregador = String.Empty;
int QtdeLinhas = excel.RowCount;
for (int linha = 7; linha <= QtdeLinhas; linha++)
{
string CelulaA = Utils.TextoCelula(excel, "A", linha);
string CelulaC = Utils.TextoCelula(excel, "C", linha);
if (CelulaA.StartsWith("Gerência :"))
{
gerente = CelulaC;
}
else if (CelulaA.StartsWith("Corresp :"))
{
promotora = CelulaC;
}
else if (CelulaA.StartsWith("Agente :"))
{
agente = CelulaC;
}
else if (CelulaA.StartsWith("Conveniada :"))
{
empregador = CelulaC;
}
else if ((CelulaA.Replace("*", "") == Utils.SoNumeros(CelulaA)) && (CelulaA != String.Empty))
{
var proposta = new Proposta();
proposta.Gerente = gerente;
proposta.Promotora = promotora;
proposta.Agente = agente;
proposta.Empregador = empregador;
proposta.NumeroProposta = CelulaA;
proposta.Cliente = CelulaC;
proposta.Cpf = Utils.TextoCelula(excel, "D", linha);
if (Utils.TextoCelula(excel, "R", linha) != "")
{
proposta.DataBase = DateTime.ParseExact(Utils.TextoCelula(excel, "R", linha), "dd/MM/yyyy", CultureInfo.InvariantCulture);
}
proposta.QtdeParcelas = Convert.ToInt32(Utils.TextoCelula(excel, "J", linha));
proposta.Situacao = Utils.TextoCelula(excel, "L", linha);
proposta.Regra = Utils.TextoCelula(excel, "P", linha);
proposta.ValorOperacao = Convert.ToDecimal(Utils.TextoCelula(excel, "E", linha));
proposta.ValorLiquido = Convert.ToDecimal(Utils.TextoCelula(excel, "G", linha));
proposta.Iof = Convert.ToDecimal(Utils.TextoCelula(excel, "I", linha));
proposta.ValorParcela = Convert.ToDecimal(Utils.TextoCelula(excel, "K", linha));
propostas.Add(proposta);
}
}
return(Utils.ToXML <List <Proposta> >(propostas));
}
private void button1_Click(object sender, System.EventArgs e)
{
ExcelFile Xls = new XlsFile(true);
AddData(Xls);
NormalOpen(Xls);
}
private void SaveToFile(XlsFile xls, string fileName)
{
string outputFile = fileName.Replace(".", "_new.");
xls.Save(outputFile);
File.Copy(outputFile, fileName, true);
File.Delete(outputFile);
}
protected override void Dispose(bool disposing)
{
base.Dispose(disposing);
if (disposing)
{
_xls = null;
}
}
public ExcelFile CreateReport(String path, String iNamLamViec, String iID_MaChungTu, String sSoChungTuChiTiet, String iID_MaNamNganSach, String iID_MaNguonNganSach,String UserID,String iID_MaThongTri)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String CucTaiChinh = ReportModels.CauHinhTenDonViSuDung(2);
String ngay = ReportModels.Ngay_Thang_Nam_HienTai();
LoadData(fr, iNamLamViec, iID_MaChungTu, sSoChungTuChiTiet, iID_MaNamNganSach, iID_MaNguonNganSach);
//Lấy thông tin đơn vị nhận
DataTable dtDVN = dtDanhSachDonVi_Nhan(iNamLamViec,"",iID_MaChungTu);
String DVN = "";
if (dtDVN.Rows.Count > 0)
{
DVN = dtDVN.Rows[0]["sTenDonVi_Nhan"].ToString();
}
fr = ReportModels.LayThongTinChuKy(fr, "rptThongTriChuyenTien");
fr.SetValue("Nams", iNamLamViec);
fr.SetValue("So", sSoChungTuChiTiet);
fr.SetValue("Ngay", ngay);
fr.SetValue("ThangCT", DanhMucModels.ThangLamViec(UserID));
fr.SetValue("DonVi", DVN);
String sCT = "";
sCT = TachLaySoChungTu_DonViNhan(sSoChungTuChiTiet, 0);
fr.SetValue("RDT", "RDT-" + sCT);
fr.SetValue("CucTaiChinh", CucTaiChinh);
fr.SetValue("BoQuocPhong", BoQuocPhong);
DataTable dtTong = ThongTriChuyenTien(iNamLamViec, iID_MaChungTu, sSoChungTuChiTiet, iID_MaNamNganSach, iID_MaNguonNganSach);
long tong = 0;
if (dtTong.Rows.Count > 0)
{
for (int i = 0; i < dtTong.Rows.Count; i++)
{
tong += String.IsNullOrEmpty(dtTong.Rows[i]["rDTRut"].ToString()) ? 0 : long.Parse(dtTong.Rows[i]["rDTRut"].ToString());
}
}
//lay ten thong tri
DataTable dtLoaiThongTri;
String SQL = "SELECT * FROM KT_LoaiThongTri WHERE iID_MaThongTri=@iID_MaThongTri";
SqlCommand cmd = new SqlCommand(SQL);
cmd.Parameters.AddWithValue("@iID_MaThongTri", iID_MaThongTri);
dtLoaiThongTri = Connection.GetDataTable(cmd);
cmd.Dispose();
LoaiTT = Convert.ToString(dtLoaiThongTri.Rows[0]["sLoaiKhoan"]);
LoaiNS = Convert.ToString(dtLoaiThongTri.Rows[0]["sTenLoaiNS"]);
fr.SetValue("LoaiTT", LoaiTT.ToUpper());
fr.SetValue("LoaiNS", LoaiNS.ToUpper());
fr.SetValue("Tien", CommonFunction.TienRaChu(tong));
fr.Run(Result);
return Result;
}
/// <summary>
/// tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NamLamViec"></param>
/// <param name="ThangLamViec"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="DoTuoi_Tu"> tuoi tu</param>
/// <param name="DoTuoi_Den">tuoi den</param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String DoiTuong, string LoaiIn, string Cap,string Nam, int DoTuoi_Tu, int DoTuoi_Den)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
//tính tổng tiền
if (Nam == null) Nam = "0";
DataTable dt = PhanTichDuToanNS_52(DoiTuong, LoaiIn,Cap,Nam, DoTuoi_Tu, DoTuoi_Den);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
//set các thông số
FlexCelReport fr = new FlexCelReport();
if (LoaiIn.Equals("00000000-0000-0000-0000-000000000000"))
{
//Toan lục luong
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_DoTuoi");
}
else if (LoaiIn.Equals("00000000-0000-0000-0000-000000000001"))
{
//Theo don vi
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_DoTuoiTheoDonVi");
}
else
{
//Loai don vi: khoi du an va khoi doanh nghiep
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_DoTuoi_TheoLoaiDonVi");
}
LoadData(fr, DoiTuong, LoaiIn, Cap, Nam, DoTuoi_Tu, DoTuoi_Den);
if (Nam != "0" & Nam != "")
fr.SetValue("Nam", "NĂM " + Nam.ToUpper());
else
fr.SetValue("Nam", "");
if (Cap != "0" & Cap != "")
fr.SetValue("Cap", " - CÁN BỘ CẤP " + Cap.ToUpper());
else
fr.SetValue("Cap","");
string sDoiTuong = "";
if (DoiTuong == "0" || DoiTuong == "")
{
sDoiTuong = "TOÀN QUÂN";
}
else
{
sDoiTuong = "Đối tượng " + L_DanhMucNgachLuong_Get_Ten(DoiTuong);
}
fr.SetValue("DoiTuong", sDoiTuong.ToUpper());
fr.SetValue("LoaiIn", LoaiIn);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("DoTuoi", "ĐỘ TUỔI TỪ " + DoTuoi_Tu.ToString() + " ĐẾN " + DoTuoi_Den.ToString());
fr.Run(Result);
return Result;
}
/// <summary>
///
/// </summary>
internal override void Export()
{
XlsFile xls = new XlsFile();
xls.Open(_xlsPath);
string title = string.Format(
"��Դ���ɱ��Ա� {0} ~ {1}",
B, E);
SetCellValue(xls, ReportConfig.Title, title);
SetCellValue(xls, ReportConfig.AVGOT, ReportHelper.GetAvgOT(B, E));
double[] values = ReportHelper.GetFirstValues (B,E);
SetCellValue(xls, ReportConfig.AVGGT1, values[0]);
SetCellValue(xls, ReportConfig.AVGBT1, values[1]);
SetCellValue(xls, ReportConfig.AVGI1, values[2]);
SetCellValue(xls, ReportConfig.DT, DateTime.Now.ToString("yyyy-MM-dd"));
//
//
DataTable tbl = ReportHelper .GetFirstRecuriteDataTable ( B,E );
for (int i = 0; i < tbl.Rows.Count; i++)
{
int r = ReportConfig.StationStartRow + i;
// fill empty cell
//
for (int c = 1; c <= ReportConfig.TotalColumns; c++)
{
SetCellValue(xls, r, c, null, true);
}
DataRow row = tbl.Rows[i];
string name = row["stationname"].ToString();
double usedRecurit = Math.Round(
Convert.ToDouble(row["usedr"]),
ReportHelper.DotNumber);
//xls.SetCellValue(r, ReportConfig.StationNameCol, name);
SetCellValue(xls, r, ReportConfig.StationNameCol, name, true);
//xls.SetCellValue(r, ReportConfig.RecuritFluxCol, usedRecurit);
SetCellValue(xls, r, ReportConfig.RecuritFluxCol, usedRecurit, true);
}
string output = Xdgk.Common.Path.GetTempFileName("xls");
xls.Save(output);
Open(output);
}
public ExcelFile CreateReport(String path, String iNamLamViec,String KhoGiay)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptCongSan_PhuLuc3");
LoadData(fr, iNamLamViec);
fr.Run(Result);
return Result;
}
public ExcelFile CreateReport(String path, String MaBL)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
using (FlexCelReport fr = new FlexCelReport())
{
LoadData(fr, MaBL);
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.Run(Result);
return Result;
}
}
/// <summary>
/// Tạo báo cáo
/// </summary>
/// <param name="path">Đường dẫn đến file mẫu Excel</param>
/// <param name="Nam">Năm lương</param>
/// <param name="Thang">Tháng lương</param>
/// <param name="TrangThai">Trạng thái duyệt</param>
/// <param name="MaDV">Mã đơn vị</param>
/// <returns></returns>
public ExcelFile CreateReport(String Nam, String Thang, String TrangThai, String MaDV, String LoaiBieu)
{
XlsFile Result = new XlsFile(true);
DataTable data = new DataTable();
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptLuong_DanhSachChiTra");
switch (LoaiBieu)
{
case "rTheoDV":
data = Ghep_Data(Get_DV(Nam, Thang, TrangThai), DanhSachChiTra(Nam, Thang, TrangThai, MaDV), "Donvi");
data = Ghep_Data(GetLuong_Donvi(Nam, Thang, TrangThai, MaDV), data, "Tong");
data.Columns.Remove("iID_MaDonVi");
data.Columns.Remove("TenCB");
data.Columns.Remove("sSoSoLuong_CanBo");
data.Columns.Remove("TT_HT");
Result = TaoTieuDe(Result);
FillData(Result, data, "TT_DV", "sSoTaiKhoan_CanBo", "LCB");
break;
case "rLietKe":
data = Ghep_Data(Get_DV(Nam, Thang, TrangThai), DanhSachChiTra(Nam, Thang, TrangThai, MaDV), "Donvi");
data.Columns.Remove("iID_MaDonVi");
data.Columns.Remove("TenCB");
data.Columns.Remove("sSoSoLuong_CanBo");
data.Columns.Remove("TT_HT");
Result = TaoTieuDe(Result);
FillData(Result, data, "TT_DV", "sSoTaiKhoan_CanBo", "LCB");
break;
case "rCoHoTen":
data = Ghep_Data(Get_DV(Nam, Thang, TrangThai), DanhSachChiTra(Nam, Thang, TrangThai, MaDV), "Donvi");
data = Ghep_Data(GetLuong_Donvi(Nam, Thang, TrangThai, MaDV), data, "Tong");
data.Columns.Remove("iID_MaDonVi");
data.Columns.Remove("TT_DV");
data.Columns.Remove("sSoSoLuong_CanBo");
data.Columns.Remove("TT_HT");
Result = TaoTieuDe_HoTen(Result);
FillData(Result, data, "TenCB", "sSoTaiKhoan_CanBo", "LCB");
break;
}
data.Dispose();
tong=TongLuong(DanhSachChiTra(Nam, Thang, TrangThai, MaDV),tong);
//fr.SetValue("cap1", ReportModels.CauHinhTenDonViSuDung(1).ToUpper());
//fr.SetValue("cap2", ReportModels.CauHinhTenDonViSuDung(2).ToUpper());
fr.SetValue("Thang", Thang);
fr.SetValue("Nam", Nam);
int count=DanhSachChiTra(Nam,Thang,TrangThai,MaDV).Rows.Count>0? DanhSachChiTra(Nam,Thang,TrangThai,MaDV).Rows.Count:0;
fr.SetValue("SoNguoi", count);
fr.SetValue("SoTien", String.Format("{0:0,0}",tong));
fr.SetValue("TienRaChu", CommonFunction.TienRaChu(tong));
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.Run(Result);
return Result;
}
/// <summary>
/// tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NamLamViec"></param>
/// <param name="ThangLamViec"></param>
/// <param name="iID_MaDonVi"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String ChucVu, string LoaiIn, string Cap,string Nam)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
//tính tổng tiền
if (Nam == null) Nam = "0";
DataTable dt = PhanTichDuToanNS_52(ChucVu, LoaiIn,Cap,Nam);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
//set các thông số
FlexCelReport fr = new FlexCelReport();
if (LoaiIn.Equals("00000000-0000-0000-0000-000000000000"))
{
//Toan lục luong
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_ChucVu");
}
else if (LoaiIn.Equals("00000000-0000-0000-0000-000000000001"))
{
//Theo don vi
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_ChucVuTheoDonVi");
}
else
{
//Loai don vi: khoi du an va khoi doanh nghiep
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_ChucVu_TheoLoaiDonVi");
}
LoadData(fr, ChucVu, LoaiIn,Cap,Nam);
if (Nam != "0" & Nam != "")
fr.SetValue("Nam", "NĂM " + Nam.ToUpper());
else
fr.SetValue("Nam", "");
//if (Cap != "0" & Cap != "")
// fr.SetValue("Cap", " - CÁN BỘ CẤP " + Cap.ToUpper());
//else
// fr.SetValue("Cap","");
string sChucVu = "";
if (ChucVu == "0" || ChucVu == "")
{
sChucVu = "(TOÀN QUÂN)";
}
else
{
sChucVu = "(Chức vụ " + L_DanhMucChucVu_Get_Ten(ChucVu) + ")";
}
fr.SetValue("ChucVu", sChucVu.ToUpper());
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("QuanKhu", QuanKhu);
fr.Run(Result);
return Result;
}
/// <summary>
/// Tạo file PDF xuất dữ liệu của cấp phát thông tri
/// </summary>
/// <param name="path">Đường dẫn tới file excel</param>
/// <param name="MaND">Mã người dùng</param>
/// <param name="iNamCapPhat">Đợt cấp phát</param>
/// <param name="sLNS">Loại ngân sách</param>
/// <param name="iID_MaDonVi">Mã đơn vị</param>
/// <param name="LoaiTongHop">Loại báo cáo tổng hợp hay chi tiết</param>
/// <param name="LoaiCapPhat">Loại cấp phát</param>
/// <param name="LoaiThongTri">Loại thông tri</param>
/// <returns></returns>
/// VungNV: 2015/11/12
public ExcelFile CreateReport(String path, String MaND, String sLNS, String iNamCapPhat, String iID_MaDonVi,
String LoaiTongHop, String LoaiCapPhat, String LoaiThongTri)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptCapPhat_ThongTri");
//Lấy dữ liệu chi tiết
LoadData(fr, MaND, sLNS, iNamCapPhat, iID_MaDonVi, LoaiTongHop);
//Lấy tháng của đợt cấp phát
DateTime dNamCapPhat = Convert.ToDateTime(iNamCapPhat);
String Thang = dNamCapPhat.Month.ToString();
String Nam = ReportModels.LayNamLamViec(MaND);
//Lấy tên đơn vị
String sTenDonVi = DonViModels.Get_TenDonVi(iID_MaDonVi, MaND);
//Lấy tên loại cấp phát
if (String.IsNullOrWhiteSpace(LoaiCapPhat))
{
DataTable dtLoaiThongTri = CommonFunction.Lay_dtDanhMuc("LoaiCapPhat");
foreach (DataRow row in dtLoaiThongTri.Rows)
{
string sMaLoai = row["iID_MaDanhMuc"].ToString();
if (sMaLoai == LoaiThongTri)
{
LoaiCapPhat = row["sTen"].ToString();
break;
}
}
}
if (!String.IsNullOrEmpty(LoaiCapPhat))
{
LoaiCapPhat.Trim();
}
fr.SetValue("BoQuocPhong", ReportModels.CauHinhTenDonViSuDung(1, MaND));
fr.SetValue("QuanKhu", ReportModels.CauHinhTenDonViSuDung(2, MaND));
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.SetValue("Nam", Nam);
fr.SetValue("NgayLap", "tháng " + Thang + " năm " + Nam);
fr.SetValue("sTenDonVi", sTenDonVi);
fr.SetValue("LoaiCapPhat", LoaiCapPhat);
fr.Run(Result);
return Result;
}
public ExcelFile CreateReport(String path, String TuThang, String DenThang, String NamLuong, String TrangThai)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptLuong_TongHopThuThueDauVao");
LoadData(fr, TuThang, DenThang,NamLuong,TrangThai);
fr.SetValue("tuThang",TuThang);
fr.SetValue("denThang", DenThang);
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.Run(Result);
return Result;
}
/// <summary>
/// hàm khởi tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NamLamViec"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String NamLamViec)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThang = ReportModels.Ngay_Thang_Nam_HienTai();
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptTCDN_BaoCaoTaiChinhNam");
LoadData(fr, NamLamViec);
fr.SetValue("Ngay", NgayThang);
fr.SetValue("Nam", NamLamViec);
fr.Run(Result);
return Result;
}
