private void btnControl_btnEventExcel_Click(object sender, EventArgs e)
{
DevExpress.Utils.WaitDialogForm waitDialogForm = new DevExpress.Utils.WaitDialogForm("Đang xuất excel ...", "Vui lòng chờ giây lát !");
try
{
ExcelManager excelManager = new ExcelManager(true);
// Print band header
BandedGridView view = gvGrid;
view.ExpandAllGroups();
object[] data = new object[view.VisibleColumns.Count];
excelManager.BandedGridHeader2Excel(view, false, 9, 1, "headerRangeName");
excelManager.SetTitleRows();
excelManager.SelectRange()
.SetFontFamily("Times New Roman");
waitDialogForm.SetCaption(String.Format("{0} - {1}%", "Đang xuất excel ...", 50));
excelManager.GridData2Excel(gvGrid, 10, 1, false, false, "", false, false);
excelManager.SelectRange(excelManager.WorkingRange.Row + excelManager.WorkingRange.Rows.Count, excelManager.WorkingRange.Column,
excelManager.WorkingRange.Row + excelManager.WorkingRange.Rows.Count, excelManager.WorkingRange.Column + excelManager.WorkingRange.Columns.Count - 1);
// Save working range
//excelManager.MoveRange(2, 0);
//int maxCol = 12;
//int xtraCol = 2;
int sr = excelManager.WorkingRange.Row + 1;
int sc = excelManager.WorkingRange.Column;
int er = excelManager.WorkingRange.Row + 1;
int ec = excelManager.WorkingRange.Column + excelManager.WorkingRange.Columns.Count - 1;
//excelManager.SelectRange(8, 1, 8, maxCol).SetRowHeight("", 45);
////Dòng Title
excelManager
.SelectRange(1, 1, 1, 1)
.SetFontStyle(true, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
.SetRangeValue(clsParameter.pParentHospital);
excelManager
.SelectRange(2, 1, 2, 1)
.SetFontStyle(false, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
.SetRangeValue(clsParameter.pHospital);
excelManager
.SelectRange(4, 1, 4, ec)
.Merge()
.SetFontStyle(true, false, false)
.SetFontSize(16)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue("DANH MỤC ĐỀ NGHỊ TRÚNG THẦU (CHO TỪNG GÓI THẦU)");
excelManager
.SelectRange(5, 1, 5, ec)
.Merge()
.SetFontStyle(true, false, false)
.SetFontSize(16)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue(string.Format("Gói thầu : {0}", lueGoiThau.Text));
excelManager
.SelectRange(6, 1, 6, ec)
.Merge()
.SetFontStyle(true, true, false)
.SetFontSize(16)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue(clsParameter.pQuyetDinh);
excelManager
.SelectRange(er, 3, er, 3)
.SetFontStyle(true, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
.SetRangeValue(string.Format("Tổng giá trị theo giá kế hoạch: {0:#,#0}", _tongGiaTriTheoGiaKH));
excelManager
.SelectRange(er, 13, er, 18)
.Merge()
.SetFontStyle(true, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
.SetRangeValue(string.Format("Tổng giá trị theo giá chào thầu: {0:#,#0}", _tongGiaTriTheoGiaChaoThau));
//excelManager
// .SelectRange(4, 1, 4, 1)
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
// .SetRangeValue("MST: 1801318149");
//excelManager
// .SelectRange(5, 1, 5, 1)
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
// .SetRangeValue("SĐT: 07103.737.261 - DĐ: 0932.993.081");
//excelManager
// .SelectRange(6, 1, 6, 1)
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
// .SetRangeValue("Số TK: 1800201218555 tại NH Agribank Cần Thơ");
//excelManager
// .SelectRange(8, 1, 8, ec)
// .Merge()
// .SetFontStyle(true, false, false)
// .SetFontSize(16)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
// .SetRangeValue(Title);
//if (RangeTime != string.Empty)
// excelManager
// .SelectRange(9, 1, 9, ec)
// .Merge()
// .SetFontStyle(true, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
// .SetRangeValue(RangeTime);
excelManager.SelectRange(12, 2, er, ec).AutoFitColumn();
excelManager.SelectRange(12, 2, er, ec).SetNumberFormat("#,#0");
excelManager
.SelectRange(er + 2, ec - 3, er + 2, ec)
.Merge()
.SetFontStyle(false, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue(string.Format("Ngày {0} tháng {1} năm {2}", DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year));
excelManager
.SelectRange(er + 3, ec - 3, er + 3, ec)
.Merge()
.SetFontStyle(false, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue("Thư ký");
excelManager
.SelectRange(er + 6, ec - 3, er + 6, ec)
.Merge()
.SetFontStyle(false, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue(clsParameter.pThuKy);
excelManager
.SelectRange(er + 3, 3, er + 3, 4)
.Merge()
.SetFontStyle(false, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue("Giám đốc");
excelManager
.SelectRange(er + 6, 3, er + 6, 4)
.Merge()
.SetFontStyle(false, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue(clsParameter.pGiamDoc);
}
catch (Exception)
{
XtraMessageBox.Show("Lỗi trong quá trình xuất Excel.\nVui lòng kiểm tra lại biểu mẫu hoặc tài liệu đang mở.", "Thông báo lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//Marshal.ReleaseComObject(excelSheet);
//Marshal.ReleaseComObject(excelBook);
//Marshal.ReleaseComObject(books);
//Marshal.ReleaseComObject(excel);
waitDialogForm.Close();
}
}
private void btnControl_btnEventExcel_Click(object sender, EventArgs e)
{
DevExpress.Utils.WaitDialogForm waitDialogForm = new DevExpress.Utils.WaitDialogForm("Đang xuất excel ...", "Vui lòng chờ giây lát !");
try
{
ExcelManager excelManager = new ExcelManager(true);
// Print band header
BandedGridView view = gvGrid;
view.ExpandAllGroups();
object[] data = new object[view.VisibleColumns.Count];
excelManager.BandedGridHeader2Excel(view, false, 6, 1, "headerRangeName");
excelManager.SetTitleRows();
excelManager.SelectRange()
.SetFontFamily("Times New Roman");
waitDialogForm.SetCaption(String.Format("{0} - {1}%", "Đang xuất excel ...", 50));
excelManager.GridData2Excel(gvGrid, 8, 1, false, false, "", false, false);
//excelManager.SelectRange(excelManager.WorkingRange.Row + excelManager.WorkingRange.Rows.Count, excelManager.WorkingRange.Column,
// excelManager.WorkingRange.Row + excelManager.WorkingRange.Rows.Count, excelManager.WorkingRange.Column + excelManager.WorkingRange.Columns.Count - 1);
// Save working range
//excelManager.MoveRange(2, 0);
//int maxCol = 12;
//int xtraCol = 2;
int sr = excelManager.WorkingRange.Row + 1;
int sc = excelManager.WorkingRange.Column;
int er = excelManager.WorkingRange.Row + 1;
int ec = excelManager.WorkingRange.Column + excelManager.WorkingRange.Columns.Count - 1;
//excelManager.SelectRange(8, 1, 8, maxCol).SetRowHeight("", 45);
////Dòng Title
excelManager
.SelectRange(1, 1, 1, 1)
.SetFontStyle(true, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
.SetRangeValue(clsParameter.pParentHospital);
excelManager
.SelectRange(2, 1, 2, 1)
.SetFontStyle(false, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
.SetRangeValue(clsParameter.pHospital);
excelManager
.SelectRange(4, 1, 4, ec)
.Merge()
.SetFontStyle(true, false, false)
.SetFontSize(16)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue("DANH SÁCH HỘI VIÊN");
excelManager.SelectRange(11, 2, er, ec).AutoFitColumn();
//excelManager.SelectRange(11, 2, er, ec).SetNumberFormat("#,#0");
// excelManager
// .SelectRange(er + 2, ec - 3, er + 2, ec)
// .Merge()
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
// .SetRangeValue(string.Format("Ngày {0} tháng {1} năm {2}", DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year));
// excelManager
// .SelectRange(er + 3, ec - 3, er + 3, ec)
// .Merge()
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
// .SetRangeValue("Thư ký");
// excelManager
// .SelectRange(er + 6, ec - 3, er + 6, ec)
// .Merge()
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
// .SetRangeValue(clsParameter.pThuKy);
// excelManager
// .SelectRange(er + 3, 3, er + 3, 4)
// .Merge()
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
// .SetRangeValue("Giám đốc");
// excelManager
//.SelectRange(er + 6, 3, er + 6, 4)
//.Merge()
//.SetFontStyle(false, false, false)
//.SetFontSize(12)
//.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
//.SetRangeValue(clsParameter.pGiamDoc);
}
catch (Exception)
{
XtraMessageBox.Show("Lỗi trong quá trình xuất Excel.\nVui lòng kiểm tra lại biểu mẫu hoặc tài liệu đang mở.", "Thông báo lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//Marshal.ReleaseComObject(excelSheet);
//Marshal.ReleaseComObject(excelBook);
//Marshal.ReleaseComObject(books);
//Marshal.ReleaseComObject(excel);
waitDialogForm.Close();
}
}
public static void LunaticBandedGridToExcel(String Title, string RangeTime, BandedGridView table, string formatNumber)
{
DevExpress.Utils.WaitDialogForm waitDialogForm = new DevExpress.Utils.WaitDialogForm("Đang xuất excel ...", "Vui lòng chờ giây lát !");
try
{
ExcelManager excelManager = new ExcelManager(true);
// Print band header
BandedGridView view = table;
view.ExpandAllGroups();
object[] data = new object[view.VisibleColumns.Count];
excelManager.BandedGridHeader2Excel(view, false, 11, 1, "headerRangeName");
excelManager.SetTitleRows();
excelManager.SelectRange()
.SetFontFamily("Times New Roman");
waitDialogForm.SetCaption(String.Format("{0} - {1}%", "Đang xuất excel ...", 50));
excelManager.GridData2Excel(table, 12, 1, false, false, "", false, false);
excelManager.SelectRange(excelManager.WorkingRange.Row + excelManager.WorkingRange.Rows.Count, excelManager.WorkingRange.Column,
excelManager.WorkingRange.Row + excelManager.WorkingRange.Rows.Count, excelManager.WorkingRange.Column + excelManager.WorkingRange.Columns.Count - 1);
// Save working range
excelManager.MoveRange(2, 0);
//int maxCol = 12;
//int xtraCol = 2;
int sr = excelManager.WorkingRange.Row + 1;
int sc = excelManager.WorkingRange.Column;
int er = excelManager.WorkingRange.Row + 1;
int ec = excelManager.WorkingRange.Column + excelManager.WorkingRange.Columns.Count - 1;
//excelManager.SelectRange(8, 1, 8, maxCol).SetRowHeight("", 45);
////Dòng Title
//excelManager
// .SelectRange(1, 1, 1, 1)
// .SetFontStyle(true, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
// .SetRangeValue("Công ty cổ phần xuất nhập khẩu KIM QUYỀN");
//excelManager
// .SelectRange(2, 1, 2, 1)
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
// .SetRangeValue("Địa chỉ : P08 đường số 8, Khu đô thị mới - Hưng Phú, ");
//excelManager
// .SelectRange(3, 1, 3, 1)
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
// .SetRangeValue("P. Hưng Thạnh, Q. Cái Răng, Tp. Cần Thơ");
//excelManager
// .SelectRange(4, 1, 4, 1)
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
// .SetRangeValue("MST: 1801318149");
//excelManager
// .SelectRange(5, 1, 5, 1)
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
// .SetRangeValue("SĐT: 07103.737.261 - DĐ: 0932.993.081");
//excelManager
// .SelectRange(6, 1, 6, 1)
// .SetFontStyle(false, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
// .SetRangeValue("Số TK: 1800201218555 tại NH Agribank Cần Thơ");
//excelManager
// .SelectRange(8, 1, 8, ec)
// .Merge()
// .SetFontStyle(true, false, false)
// .SetFontSize(16)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
// .SetRangeValue(Title);
//if (RangeTime != string.Empty)
// excelManager
// .SelectRange(9, 1, 9, ec)
// .Merge()
// .SetFontStyle(true, false, false)
// .SetFontSize(12)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
// .SetRangeValue(RangeTime);
excelManager.SelectRange(12, 2, er, ec).AutoFitColumn();
excelManager.SelectRange(12, 2, er, ec).SetNumberFormat("#,#0");
excelManager
.SelectRange(er - 2, ec - 3, er - 2, ec - 3)
.Merge()
.SetFontStyle(false, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
.SetRangeValue(string.Format("Ngày {0} tháng {1} năm {2}", DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year));
}
catch (Exception)
{
XtraMessageBox.Show("Lỗi trong quá trình xuất Excel.\nVui lòng kiểm tra lại biểu mẫu hoặc tài liệu đang mở.", "Thông báo lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//Marshal.ReleaseComObject(excelSheet);
//Marshal.ReleaseComObject(excelBook);
//Marshal.ReleaseComObject(books);
//Marshal.ReleaseComObject(excel);
waitDialogForm.Close();
}
}
void ExportExcel(String Title, string RangeTime, BandedGridView table, string formatNumber)
{
DevExpress.Utils.WaitDialogForm waitDialogForm = new DevExpress.Utils.WaitDialogForm("Đang xuất excel ...", "Vui lòng chờ giây lát !");
try
{
ExcelManager excelManager = new ExcelManager(true);
// Print band header
BandedGridView view = table;
view.ExpandAllGroups();
object[] data = new object[view.VisibleColumns.Count];
excelManager.BandedGridHeader2Excel(view, false, 8, 1, "headerRangeName");
excelManager.SetTitleRows();
excelManager.SelectRange()
.SetFontFamily("Times New Roman");
waitDialogForm.SetCaption(String.Format("{0} - {1}%", "Đang xuất excel ...", 50));
excelManager.GridData2Excel(table, 9, 1, false, false, "", false, false);
excelManager.SelectRange(excelManager.WorkingRange.Row + excelManager.WorkingRange.Rows.Count, excelManager.WorkingRange.Column,
excelManager.WorkingRange.Row + excelManager.WorkingRange.Rows.Count, excelManager.WorkingRange.Column + excelManager.WorkingRange.Columns.Count - 1);
// Save working range
excelManager.MoveRange(2, 0);
//int maxCol = 12;
//int xtraCol = 2;
int sr = excelManager.WorkingRange.Row + 1;
int sc = excelManager.WorkingRange.Column;
int er = excelManager.WorkingRange.Row + 1;
int ec = excelManager.WorkingRange.Column + excelManager.WorkingRange.Columns.Count - 1;
//excelManager.SelectRange(8, 1, 8, maxCol).SetRowHeight("", 45);
////Dòng Title
excelManager
.SelectRange(1, 1, 1, 4)
.Merge()
.SetFontStyle(true, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue(clsParameter.pParentHospital);
excelManager
.SelectRange(2, 1, 2, 4)
.Merge()
.SetFontStyle(false, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue(clsParameter.pHospital);
excelManager
.SelectRange(1, 11, 1, ec)
.Merge()
.SetFontStyle(true, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue("CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM");
excelManager
.SelectRange(2, 11, 2, ec)
.Merge()
.SetFontStyle(true, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue("Độc lập - Tự do - Hạnh phúc");
excelManager
.SelectRange(5, 1, 5, ec)
.Merge()
.SetFontStyle(true, false, false)
.SetFontSize(16)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue(Title);
//excelManager
// .SelectRange(6, 1, 6, ec)
// .Merge()
// .SetFontStyle(true, false, false)
// .SetFontSize(14)
// .SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
// .SetRangeValue("Gói thầu: " + lueGoiThau.Text);
if (RangeTime != string.Empty)
{
excelManager
.SelectRange(9, 1, 9, ec)
.Merge()
.SetFontStyle(true, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter)
.SetRangeValue(RangeTime);
}
excelManager.SelectRange(10, 2, er, ec).AutoFitColumn();
excelManager.SelectRange(10, 2, er, ec).SetNumberFormat("#,#0");
excelManager
.SelectRange(er - 2, ec - 3, er - 2, ec - 3)
.Merge()
.SetFontStyle(false, false, false)
.SetFontSize(12)
.SetHorizontalAlignment(Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft)
.SetRangeValue(string.Format("Ngày {0} tháng {1} năm {2}", DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year));
}
catch (Exception)
{
XtraMessageBox.Show("Lỗi trong quá trình xuất Excel.\nVui lòng kiểm tra lại biểu mẫu hoặc tài liệu đang mở.", "Thông báo lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//Marshal.ReleaseComObject(excelSheet);
//Marshal.ReleaseComObject(excelBook);
//Marshal.ReleaseComObject(books);
//Marshal.ReleaseComObject(excel);
waitDialogForm.Close();
}
}
