public void NegativeColumnNumberIsInvalid()
{
var ws = new XLWorkbook().AddWorksheet("Sheet1") as XLWorksheet;
var column = new XLColumn(ws, -1);
Assert.IsFalse(column.RangeAddress.IsValid);
}
// load sheet into grid
private void LoadSheet(C1FlexGrid flex, XLSheet sheet, bool fixedCells)
{
// account for fixed cells
int frows = flex.Rows.Fixed;
int fcols = flex.Cols.Fixed;
// copy dimensions
flex.Rows.Count = sheet.Rows.Count + frows;
flex.Cols.Count = sheet.Columns.Count + fcols;
// initialize fixed cells
if (fixedCells && frows > 0 && fcols > 0)
{
flex.Styles.Fixed.TextAlign = TextAlignEnum.CenterCenter;
for (int r = 1; r < flex.Rows.Count; r++)
{
flex[r, 0] = r;
}
for (int c = 1; c < flex.Cols.Count; c++)
{
// string hdr = string.Format("{0}", (char)('A' + c - 1));
// flex[0, c] = hdr;
flex[0, c] = c;
}
}
// set default properties
flex.Font = sheet.Book.DefaultFont;
flex.Rows.DefaultSize = C1XLBook.TwipsToPixels(sheet.DefaultRowHeight);
flex.Cols.DefaultSize = C1XLBook.TwipsToPixels(sheet.DefaultColumnWidth);
// prepare to convert styles
_styles = new Hashtable();
// set row/column properties
for (int r = 0; r < sheet.Rows.Count; r++)
{
// size/visibility
Row fr = flex.Rows[r + frows];
XLRow xr = sheet.Rows[r];
if (xr.Height >= 0)
{
fr.Height = C1XLBook.TwipsToPixels(xr.Height);
}
fr.Visible = xr.Visible;
// style
CellStyle cs = StyleFromExcel(flex, xr.Style);
if (cs != null)
{
//cs.DefinedElements &= ~StyleElementFlags.TextAlign; // << need to fix the grid
fr.Style = cs;
}
}
for (int c = 0; c < sheet.Columns.Count; c++)
{
// size/visibility
Column fc = flex.Cols[c + fcols];
XLColumn xc = sheet.Columns[c];
if (xc.Width >= 0)
{
fc.Width = C1XLBook.TwipsToPixels(xc.Width);
}
fc.Visible = xc.Visible;
// style
CellStyle cs = StyleFromExcel(flex, xc.Style);
if (cs != null)
{
//cs.DefinedElements &= ~StyleElementFlags.TextAlign; // << need to fix the grid
fc.Style = cs;
}
}
// load cells
for (int r = 0; r < sheet.Rows.Count; r++)
{
for (int c = 0; c < sheet.Columns.Count; c++)
{
// get cell
XLCell cell = sheet.GetCell(r, c);
if (cell == null)
{
continue;
}
// apply content
flex[r + frows, c + fcols] = cell.Value;
// apply style
CellStyle cs = StyleFromExcel(flex, cell.Style);
if (cs != null)
{
flex.SetCellStyle(r + frows, c + fcols, cs);
}
}
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
string yearmonth = drpYear.SelectedValue;
string building = Func.ParseString(Session["__BUILDINGID__"]);
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT *";
sql += " FROM BD_BudgetSchedule ";
sql += " WHERE BuildingId = '" + building + "' ";
sql += " and YearMonth = '" + yearmonth + "' ";
sql += drpBudgetExport.SelectedValue.Equals("") ? "" : " and id ='" + drpBudgetExport.SelectedValue + "'";
sql += " and DelFlag = 0 Order by id";
using (SqlDatabase db = new SqlDatabase())
{
C1XLBook xlbBook = new C1XLBook();
string fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\NganSach.xlsx");
if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])));
}
string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
string strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\NganSach" + strDT + ".xlsx";
string strFilePathExport = @"../../Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + @"/NganSach" + strDT + ".xlsx";
string fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);
//string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
File.Copy(fileName, fileNameDes);
xlbBook.Load(fileNameDes);
string sheet = "NganSach";
XLSheet xlsSheet = xlbBook.Sheets[sheet];
string IDs = "";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
xlsSheet[0, 2].Value = xlsSheet[0, 2].Value.ToString().Replace("{%TOA_NHA%}", DbHelper.GetScalar("Select Name From Mst_Building Where BuildingId = '" + building + "'"));
xlsSheet[0, 2].Value = xlsSheet[0, 2].Value.ToString().Replace("{%NAM_THANG%}", "NĂM " + yearmonth);
int j = 7;
DataTable dtReport = ds.Tables[0];
foreach (DataRow rowType in dtReport.Rows)
{
string Budget = rowType["Budget"].ToString();
string id = rowType["id"].ToString();
IDs += ",'" + id + "'";
xlsSheet[2, j].Value = id;
xlsSheet[3, j].Value = Budget;
j++;
}
for (int i = j; i < j * 12; i++)
{
XLColumn col = new XLColumn();
col = xlsSheet.Columns[j];
xlsSheet.Columns.Remove(col);
}
}
}
if (String.IsNullOrEmpty(IDs))
{
mvMessage.AddError("Hiện tại chưa có Kỳ ngân sách nào được tạo");
return;
}
string buildingId = Func.ParseString(Session["__BUILDINGID__"]);
string sessionId = Session.SessionID;
DbHelper.ExecuteNonQuery("Delete From BD_BudgetScheduleDetailReport where SessionId = '" + sessionId + "'");
string[] idList = IDs.Substring(1).Split(',');
for (int m = 0; m < idList.Length; m++)
{
string sqlTmp = "Select * from BD_BudgetScheduleDetail where BuggetScheduleId in (" + idList[m] + ") and delFlag = 0 Order by Id";
DataTable dtTable = new DataTable();
dtTable.Columns.Add("SessionId", Type.GetType("System.String"));
dtTable.Columns.Add("BuggetScheduleId", Type.GetType("System.Int32"));
dtTable.Columns.Add("PaymentType", Type.GetType("System.String"));
dtTable.Columns.Add("PaymentId", Type.GetType("System.Int32"));
dtTable.Columns.Add("ParentId", Type.GetType("System.Int32"));
dtTable.Columns.Add("InVND", Type.GetType("System.Double"));
dtTable.Columns.Add("InUSD", Type.GetType("System.Decimal"));
dtTable.Columns.Add("OutVND", Type.GetType("System.Double"));
dtTable.Columns.Add("OutUSD", Type.GetType("System.Decimal"));
dtTable.Columns.Add("ItemLevel", Type.GetType("System.String"));
DataTable dt = DbHelper.GetDataTable(sqlTmp);
foreach (DataRow dr in dt.Rows)
{
if (dr["ParentId"].ToString() == "0")
{
int j = 1;
int BuggetScheduleId = Func.ParseInt(dr["BuggetScheduleId"]);
string PaymentType = dr["PaymentType"].ToString();
int PaymentId = Func.ParseInt(dr["PaymentId"]);
int ParentId = Func.ParseInt(dr["ParentId"]);
double InVND = Func.ParseDouble(dr["InVND"]);
decimal InUSD = Func.ParseInt(dr["InUSD"]);
double OutVND = Func.ParseDouble(dr["OutVND"]);
decimal OutUSD = Func.ParseInt(dr["OutUSD"]);
string itemLevel = Func.ParseString(dr["itemLevel"]);
dtTable.Rows.Add(sessionId, BuggetScheduleId, PaymentType, PaymentId, ParentId, InVND, InUSD, OutVND, OutUSD, itemLevel);
GetChildItems(Func.ParseString(PaymentId), dt, dtTable, j);
}
}
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "BD_BudgetScheduleDetailReport";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
copy.ColumnMappings.Add(0, "SessionId");
copy.ColumnMappings.Add(1, "BuggetScheduleId");
copy.ColumnMappings.Add(2, "PaymentType");
copy.ColumnMappings.Add(3, "PaymentId");
copy.ColumnMappings.Add(4, "ParentId");
copy.ColumnMappings.Add(5, "InVND");
copy.ColumnMappings.Add(6, "InUSD");
copy.ColumnMappings.Add(7, "OutVND");
copy.ColumnMappings.Add(8, "OutUSD");
copy.ColumnMappings.Add(9, "ItemLevel");
copy.WriteToServer(dtTable);
}
}
ds = new DataSet();
sql = "Select * from BD_BudgetScheduleDetailReport where SessionId = '" + sessionId + "' Order by BuggetScheduleId,Id";
int k = 5;
int colData = 6;
string bsId = "";
string[] alpha = "A. B. C. D. E. F. G. H. I. J. K. L. M. N. O. P. Q. R. S. T. U. V. W. X. Y. Z.".Split(' ');
string[] alphaLevel2 = "I. II. III. IV. V. VI. VII. VIII. IX. X. XI. XII. XII. XIV.".Split(' ');
string[] alphaLevel3 = "1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20.".Split(' ');
string[] alphaLevel4 = "a. b. c. d. e. f. g. h. i. j. k. l. m. n. o. p. q. r. s. t. u. v. w.".Split(' ');
int level1 = -1;
int level2 = -1;
int level3 = -1;
int level4 = -1;
xlsSheet.Columns[1].Width = 300;
xlsSheet.Columns[2].Width = 300;
xlsSheet.Columns[3].Width = 300;
xlsSheet.Columns[4].Width = 300;
xlsSheet.Columns[5].Width = 300;
int lastrow = 0;
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
double inSumVND = 0;
double outSumVND = 0;
DataTable dtReport = ds.Tables[0];
foreach (DataRow rowType in dtReport.Rows)
{
XLStyle xlstStyleAll = new XLStyle(xlbBook);
//xlstStyleAll.AlignHorz = XLAlignHorzEnum.Left;
xlstStyleAll.WordWrap = false;
xlstStyleAll.Font = new Font("", 8, FontStyle.Regular);
xlstStyleAll.SetBorderColor(Color.Black);
xlstStyleAll.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleAll.BorderTop = XLLineStyleEnum.Thin;
xlstStyleAll.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleAll.BorderRight = XLLineStyleEnum.Thin;
XLStyle xlstStyleLeft = new XLStyle(xlbBook);
//xlstStyleLeft.AlignHorz = XLAlignHorzEnum.Left;
xlstStyleLeft.WordWrap = false;
xlstStyleLeft.Font = new Font("", 8, FontStyle.Regular);
xlstStyleLeft.SetBorderColor(Color.Black);
xlstStyleLeft.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleLeft.BorderTop = XLLineStyleEnum.Thin;
xlstStyleLeft.BorderLeft = XLLineStyleEnum.Thin;
XLStyle xlstStyleRight = new XLStyle(xlbBook);
//xlstStyleRight.AlignHorz = XLAlignHorzEnum.Left;
xlstStyleRight.WordWrap = false;
xlstStyleRight.Font = new Font("", 8, FontStyle.Regular);
xlstStyleRight.SetBorderColor(Color.Black);
xlstStyleRight.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleRight.BorderTop = XLLineStyleEnum.Thin;
xlstStyleRight.BorderRight = XLLineStyleEnum.Thin;
XLStyle xlstStyleMiddle = new XLStyle(xlbBook);
//xlstStyleMiddle.AlignHorz = XLAlignHorzEnum.Left;
xlstStyleMiddle.WordWrap = false;
xlstStyleMiddle.Font = new Font("", 8, FontStyle.Regular);
xlstStyleMiddle.SetBorderColor(Color.Black);
xlstStyleMiddle.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleMiddle.BorderTop = XLLineStyleEnum.Thin;
xlsSheet[k, 2].Style = xlstStyleLeft;
xlsSheet[k, 3].Style = xlstStyleMiddle;
xlsSheet[k, 4].Style = xlstStyleMiddle;
xlsSheet[k, 5].Style = xlstStyleMiddle;
xlsSheet[k, 6].Style = xlstStyleRight;
string PaymentType = rowType["PaymentType"].ToString();
string InVND = rowType["InVND"].ToString();
string InUSD = rowType["InUSD"].ToString();
string OutVND = rowType["OutVND"].ToString();
string OutUSD = rowType["OutUSD"].ToString();
string PaymentId = rowType["PaymentId"].ToString();
int colNo = Func.ParseInt(rowType["colNo"].ToString());
string id = rowType["BuggetScheduleId"].ToString();
string ParentId = rowType["ParentId"].ToString();
string itemLevel = rowType["ItemLevel"].ToString();
if (itemLevel.Equals("0"))
{
xlstStyleAll.BackColor = Color.Orange;
xlstStyleLeft.BackColor = Color.Orange;
xlstStyleRight.BackColor = Color.Orange;
xlstStyleMiddle.BackColor = Color.Orange;
}
else
{
xlstStyleAll.BackColor = Color.White;
xlstStyleLeft.BackColor = Color.White;
xlstStyleRight.BackColor = Color.White;
xlstStyleMiddle.BackColor = Color.White;
}
if (itemLevel.Equals("0") || itemLevel.Equals("1") || itemLevel.Equals("2"))
{
xlstStyleAll.Font = new Font("", 8, FontStyle.Bold);
xlstStyleLeft.Font = new Font("", 8, FontStyle.Bold);
xlstStyleRight.Font = new Font("", 8, FontStyle.Bold);
xlstStyleMiddle.Font = new Font("", 8, FontStyle.Bold);
}
xlsSheet[k, colData].Style = xlstStyleAll;
//j += 2;
if (!bsId.Equals(id))
{
if (k > 5)
{
lastrow = k;
xlsSheet[k, 2].Value = alpha[level1+1];
xlsSheet[k, 3].Value = "CÂN ĐỐI THU - CHI (Phần Lãi)";
//xlsSheet[k, colData + 1].Value = Func.ParseDouble(InUSD);
xlsSheet[k, colData].Value = Func.ParseDouble(inSumVND - outSumVND);
}
k = 5;
colData++;
bsId = id;
level1 = -1;
}
int col = Func.ParseInt(itemLevel) + 3;
if (itemLevel.Equals("0"))
{
level1++;
xlsSheet[k, col - 1].Value = alpha[level1];
level2 = -1;
}
else if (itemLevel.Equals("1"))
{
level2++;
xlsSheet[k, col - 1].Value = alphaLevel2[level2];
level3 = -1;
}
else if (itemLevel.Equals("2"))
{
level3++;
xlsSheet[k, col - 1].Value = alphaLevel3[level3];
level4 = -1;
}
else if (itemLevel.Equals("3"))
{
level4++;
xlsSheet[k, col - 1].Value = alphaLevel4[level4];
}
xlsSheet[k, col].Value = PaymentType;
//xlsSheet[k, colData + 1].Value = Func.ParseDouble(InUSD);
xlsSheet[k, colData].Value = Func.ParseDouble(InVND);
////xlsSheet[k, colData + 3].Value = Func.ParseDouble(OutUSD);
////xlsSheet[k, colData + 4].Value = Func.ParseDouble(OutVND);
xlsSheet[k, 0].Value = PaymentId;
if (PaymentId.Equals("9"))
{
inSumVND = Func.ParseDouble(InVND);
}
else if (PaymentId.Equals("10"))
{
outSumVND = Func.ParseDouble(InVND);
}
//XLStyle xlstStyleAll = new XLStyle(xlbBook);
//xlstStyleAll.AlignHorz = XLAlignHorzEnum.Left;
//xlstStyleAll.WordWrap = false;
//xlstStyleAll.Font = new Font("", 8, FontStyle.Regular);
//xlstStyleAll.SetBorderColor(Color.Black);
//xlstStyleAll.BorderBottom = XLLineStyleEnum.Thin;
//xlstStyleAll.BorderTop = XLLineStyleEnum.Thin;
//xlstStyleAll.BorderLeft = XLLineStyleEnum.Thin;
//xlstStyleAll.BorderRight = XLLineStyleEnum.Thin;
//XLStyle xlstStyleLeft = new XLStyle(xlbBook);
//xlstStyleLeft.AlignHorz = XLAlignHorzEnum.Left;
//xlstStyleLeft.WordWrap = false;
//xlstStyleLeft.Font = new Font("", 8, FontStyle.Regular);
//xlstStyleLeft.SetBorderColor(Color.Black);
//xlstStyleLeft.BorderBottom = XLLineStyleEnum.Thin;
//xlstStyleLeft.BorderTop = XLLineStyleEnum.Thin;
//xlstStyleLeft.BorderLeft = XLLineStyleEnum.Thin;
//XLStyle xlstStyleRight = new XLStyle(xlbBook);
//xlstStyleRight.AlignHorz = XLAlignHorzEnum.Left;
//xlstStyleRight.WordWrap = false;
//xlstStyleRight.Font = new Font("", 8, FontStyle.Regular);
//xlstStyleRight.SetBorderColor(Color.Black);
//xlstStyleRight.BorderBottom = XLLineStyleEnum.Thin;
//xlstStyleRight.BorderTop = XLLineStyleEnum.Thin;
//xlstStyleRight.BorderRight = XLLineStyleEnum.Thin;
//XLStyle xlstStyleMiddle = new XLStyle(xlbBook);
//xlstStyleMiddle.AlignHorz = XLAlignHorzEnum.Left;
//xlstStyleMiddle.WordWrap = false;
//xlstStyleMiddle.Font = new Font("", 8, FontStyle.Regular);
//xlstStyleMiddle.SetBorderColor(Color.Black);
//xlstStyleMiddle.BorderBottom = XLLineStyleEnum.Thin;
//xlstStyleMiddle.BorderTop = XLLineStyleEnum.Thin;
//xlsSheet[k, 2].Style = xlstStyleLeft;
//xlsSheet[k, 3].Style = xlstStyleMiddle;
//xlsSheet[k, 4].Style = xlstStyleMiddle;
//xlsSheet[k, 5].Style = xlstStyleMiddle;
//xlsSheet[k, 6].Style = xlstStyleRight;
//if (itemLevel.Equals("0"))
//{
// xlstStyleAll.BackColor = Color.Orange;
// xlstStyleLeft.BackColor = Color.Orange;
// xlstStyleRight.BackColor = Color.Orange;
// xlstStyleMiddle.BackColor = Color.Orange;
//}
//else
//{
// xlstStyleAll.BackColor = Color.White;
// xlstStyleLeft.BackColor = Color.White;
// xlstStyleRight.BackColor = Color.White;
// xlstStyleMiddle.BackColor = Color.White;
//}
//if (itemLevel.Equals("0") || itemLevel.Equals("1") || itemLevel.Equals("2"))
//{
// xlstStyleAll.Font = new Font("", 8, FontStyle.Bold);
// xlstStyleLeft.Font = new Font("", 8, FontStyle.Bold);
// xlstStyleRight.Font = new Font("", 8, FontStyle.Bold);
// xlstStyleMiddle.Font = new Font("", 8, FontStyle.Bold);
//}
xlsSheet[k, colData].Style = xlstStyleAll;
k++;
if (k == lastrow)
{
XLStyle xlstStyleLast = new XLStyle(xlbBook);
xlstStyleLast.WordWrap = false;
xlstStyleLast.Font = new Font("", 8, FontStyle.Regular);
xlstStyleLast.SetBorderColor(Color.Black);
xlstStyleLast.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleLast.BorderTop = XLLineStyleEnum.Thin;
xlstStyleLast.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleLast.BorderRight = XLLineStyleEnum.Thin;
xlstStyleLast.Font = new Font("", 8, FontStyle.Bold);
xlstStyleLast.BackColor = Color.Orange;
xlsSheet[k, colData].Value = Func.ParseDouble(inSumVND - outSumVND);
xlsSheet[k, colData].Style = xlstStyleLast;
}
}
}
}
//ds = new DataSet();
//sql = string.Empty;
//sql = " SELECT *";
//sql += " FROM BD_PaymentReportMonth ";
//sql += " WHERE BuildingId = '" + building + "' ";
//sql += " and YearMonth = '" + yearmonth + "' order by id";
//using (db = new SqlDatabase())
//{
// using (SqlCommand cm = db.CreateCommand(sql))
// {
// SqlDataAdapter da = new SqlDataAdapter(cm);
// da.Fill(ds);
// if (ds != null)
// {
// xlbBook = new C1XLBook();
// fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\BaoCaoThuChiThang.xlsx");
// if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
// {
// Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])));
// }
// strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
// strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\BaoCaoThuChiThang" + strDT + ".xlsx";
// strFilePathExport = "Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + "/BaoCaoThuChiThang" + strDT + ".xlsx";
// fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);
// //string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
// File.Copy(fileName, fileNameDes);
// xlbBook.Load(fileNameDes);
// sheet = "BaoCao";
// xlsSheet = xlbBook.Sheets[sheet];
// int i = 5;
// xlsSheet[0, 0].Value = xlsSheet[0, 0].Value.ToString().Replace("{%TOA_NHA%}", DbHelper.GetScalar("Select Name From Mst_Building Where BuildingId = '" + building + "'"));
// xlsSheet[0, 0].Value = xlsSheet[0, 0].Value.ToString().Replace("{%NAM_THANG%}", "THÁNG" + yearmonth.Substring(4, 2) + "/" + yearmonth.Substring(0, 4));
// DataTable dtReport = ds.Tables[0];
// foreach (DataRow rowType in dtReport.Rows)
// {
// int colNo = Func.ParseInt(rowType["colNo"]);
// string PaymentType = rowType["PaymentType"].ToString();
// string InVND = rowType["InVND"].ToString();
// string InUSD = rowType["InUSD"].ToString();
// string OutVND = rowType["OutVND"].ToString();
// string OutUSD = rowType["OutUSD"].ToString();
// bool bold = rowType["bold"].ToString().Equals("1") ? true : false;
// XLCellRange mrCell = new XLCellRange(i, i, 0, 3);
// xlsSheet.MergedCells.Add(mrCell);
// xlsSheet[i, 0].Value = "." + " ".PadLeft(colNo * 3, ' ') + PaymentType;
// xlsSheet[i, 4].Value = Func.ParseDouble(InUSD);
// xlsSheet[i, 5].Value = Func.ParseDouble(InVND);
// xlsSheet[i, 6].Value = Func.ParseDouble(OutUSD);
// xlsSheet[i, 7].Value = Func.ParseDouble(OutVND);
// XLStyle xlstStyle = new XLStyle(xlbBook);
// xlstStyle.AlignHorz = XLAlignHorzEnum.Left;
// xlstStyle.WordWrap = false;
// xlstStyle.Font = new Font("", 8, FontStyle.Regular);
// xlstStyle.SetBorderColor(Color.Black);
// xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
// xlstStyle.BorderTop = XLLineStyleEnum.Thin;
// xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
// xlstStyle.BorderRight = XLLineStyleEnum.Thin;
// xlsSheet[i, 0].Style = xlstStyle;
// xlsSheet[i, 1].Style = xlstStyle;
// xlsSheet[i, 2].Style = xlstStyle;
// xlsSheet[i, 3].Style = xlstStyle;
// xlstStyle = new XLStyle(xlbBook);
// xlstStyle.WordWrap = false;
// xlstStyle.Font = new Font("", 8, FontStyle.Regular);
// xlstStyle.SetBorderColor(Color.Black);
// xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
// xlstStyle.BorderTop = XLLineStyleEnum.Thin;
// xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
// xlstStyle.BorderRight = XLLineStyleEnum.Thin;
// xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
// xlsSheet[i, 4].Style = xlstStyle;
// xlsSheet[i, 5].Style = xlstStyle;
// xlsSheet[i, 6].Style = xlstStyle;
// xlsSheet[i, 7].Style = xlstStyle;
// i++;
// }
// xlbBook.Save(fileNameDes);
// ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('../" + strFilePathExport + "'," + PopupWidth + "," + PopupHeight + ",'EditReport', true);", true);
// }
// }
//}
xlbBook.Save(fileNameDes);
ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('" + strFilePathExport + "'," + PopupWidth + "," + PopupHeight + ",'EditReport', true);", true);
}
}
//===========================================================================================
#region ** Save a C1FlexGrid into an XLSheet
private void SaveSheet(C1FlexGrid flex, XLSheet sheet, bool fixedCells)
{
// account for fixed cells
int frows = flex.Rows.Fixed;
int fcols = flex.Cols.Fixed;
if (fixedCells)
{
frows = fcols = 0;
}
// copy dimensions
int lastRow = flex.Rows.Count - frows - 1;
int lastCol = flex.Cols.Count - fcols - 1;
if (lastRow < 0 || lastCol < 0)
{
return;
}
XLCell cell = sheet[lastRow, lastCol];
// set default properties
sheet.Book.DefaultFont = flex.Font;
sheet.DefaultRowHeight = C1XLBook.PixelsToTwips(flex.Rows.DefaultSize);
sheet.DefaultColumnWidth = C1XLBook.PixelsToTwips(flex.Cols.DefaultSize);
// prepare to convert styles
_styles = new Hashtable();
// set row/column properties
for (int r = frows; r < flex.Rows.Count; r++)
{
// size/visibility
Row fr = flex.Rows[r];
XLRow xr = sheet.Rows[r - frows];
if (fr.Height >= 0)
{
xr.Height = C1XLBook.PixelsToTwips(fr.Height);
}
xr.Visible = fr.Visible;
// style
XLStyle xs = StyleFromFlex(fr.Style);
if (xs != null)
{
xr.Style = xs;
}
}
for (int c = fcols; c < flex.Cols.Count; c++)
{
// size/visibility
Column fc = flex.Cols[c];
XLColumn xc = sheet.Columns[c - fcols];
if (fc.Width >= 0)
{
xc.Width = C1XLBook.PixelsToTwips(fc.Width);
}
xc.Visible = fc.Visible;
// style
XLStyle xs = StyleFromFlex(fc.Style);
if (xs != null)
{
xc.Style = xs;
}
}
// load cells
for (int r = frows; r < flex.Rows.Count; r++)
{
for (int c = fcols; c < flex.Cols.Count; c++)
{
// get cell
cell = sheet[r - frows, c - fcols];
// apply content
cell.Value = flex[r, c];
// apply style
XLStyle xs = StyleFromFlex(flex.GetCellStyle(r, c));
if (xs != null)
{
cell.Style = xs;
}
}
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
string yearmonth = drpYear.SelectedValue;
string building = Func.ParseString(Session["__BUILDINGID__"]);
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT *";
sql += " FROM BD_BudgetSchedule ";
sql += " WHERE BuildingId = '" + building + "' ";
sql += " and YearMonth = '" + yearmonth + "' ";
sql += " and id ='" + drpBudgetExport.SelectedValue + "' and DelFlag = 0 Order by id";
using (SqlDatabase db = new SqlDatabase())
{
C1XLBook xlbBook = new C1XLBook();
string fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\NganSach.xlsx");
if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])));
}
string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
string strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\NganSach" + strDT + ".xlsx";
string strFilePathExport = @"../../Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + @"/NganSach" + strDT + ".xlsx";
string fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);
//string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
File.Copy(fileName, fileNameDes);
xlbBook.Load(fileNameDes);
string sheet = "NganSach";
XLSheet xlsSheet = xlbBook.Sheets[sheet];
string IDs = "";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
xlsSheet[0, 1].Value = xlsSheet[0, 1].Value.ToString().Replace("{%TOA_NHA%}", DbHelper.GetScalar("Select Name From Mst_Building Where BuildingId = '" + building + "'"));
xlsSheet[0, 1].Value = xlsSheet[0, 1].Value.ToString().Replace("{%NAM_THANG%}", "NĂM " + yearmonth);
int j = 2;
DataTable dtReport = ds.Tables[0];
foreach (DataRow rowType in dtReport.Rows)
{
string Budget = rowType["Budget"].ToString();
string id = rowType["id"].ToString();
IDs += ",'" + id + "'";
xlsSheet[2, j].Value = id;
xlsSheet[3, j].Value = Budget;
j += 2;
}
for (int i = j; i < j * 12; i++)
{
XLColumn col = new XLColumn();
col = xlsSheet.Columns[j];
xlsSheet.Columns.Remove(col);
}
}
}
if (String.IsNullOrEmpty(IDs))
{
mvMessage.AddError("Hiện tại chưa có Kỳ ngân sách nào được tạo");
return;
}
DataTable dtTable = new DataTable();
dtTable.Columns.Add("SessionId", Type.GetType("System.String"));
dtTable.Columns.Add("BuggetScheduleId", Type.GetType("System.Int32"));
dtTable.Columns.Add("PaymentId", Type.GetType("System.Int32"));
dtTable.Columns.Add("ParentId", Type.GetType("System.String"));
string buildingId = Func.ParseString(Session["__BUILDINGID__"]);
string sessionId = Session.SessionID;
string sqlTmp = "Select * from BD_BudgetScheduleDetail where BuggetScheduleId in (" + IDs.Substring(1) + ") and delFlag = 0 Order by ParentId";
DbHelper.ExecuteNonQuery("Delete From BD_BudgetScheduleDetailReport where SessionId = '" + sessionId + "'");
DataTable dt = DbHelper.GetDataTable(sqlTmp);
foreach (DataRow dr in dt.Rows)
{
if (dr["ParentId"].ToString() == "0")
{
int j = 1;
int BuggetScheduleId = Func.ParseInt(dr["BuggetScheduleId"]);
int PaymentId = Func.ParseInt(dr["PaymentId"]);
string ParentId = dr["ParentId"].ToString();
dtTable.Rows.Add(sessionId, BuggetScheduleId, PaymentId, ParentId);
GetChildItems(dr["PaymentId"].ToString(), dt, dtTable, j);
}
}
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "BD_BudgetScheduleDetailReport";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
copy.ColumnMappings.Add(0, "SessionId");
copy.ColumnMappings.Add(1, "BuggetScheduleId");
copy.ColumnMappings.Add(2, "PaymentId");
copy.ColumnMappings.Add(3, "ParentId");
copy.WriteToServer(dtTable);
}
ds = new DataSet();
sql = "Select * from BD_BudgetScheduleDetailReport where SessionId = '" + sessionId + "' Order by Id";
int k = 5;
int colData = -1;
string bsId = "";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
DataTable dtReport = ds.Tables[0];
foreach (DataRow rowType in dtReport.Rows)
{
string PaymentType = rowType["PaymentType"].ToString();
string InVND = rowType["InVND"].ToString();
string InUSD = rowType["InUSD"].ToString();
string OutVND = rowType["OutVND"].ToString();
string OutUSD = rowType["OutUSD"].ToString();
string PaymentId = rowType["PaymentId"].ToString();
int colNo = Func.ParseInt(rowType["colNo"].ToString());
string id = rowType["BuggetScheduleId"].ToString();
string ParentId = rowType["ParentId"].ToString();
//string PaymentId = rowType["PaymentId"].ToString();
//xlsSheet[2, j].Value = id;
//xlsSheet[3, j].Value = Budget;
//j += 2;
if (!bsId.Equals(id))
{
k = 5;
colData += 2;
bsId = id;
}
xlsSheet[k, 1].Value = PaymentType;
xlsSheet[k, colData + 1].Value = Func.ParseDouble(InUSD);
xlsSheet[k, colData + 2].Value = Func.ParseDouble(InVND);
//xlsSheet[k, colData + 3].Value = Func.ParseDouble(OutUSD);
//xlsSheet[k, colData + 4].Value = Func.ParseDouble(OutVND);
xlsSheet[k, 0].Value = PaymentId;
XLStyle xlstStyle = new XLStyle(xlbBook);
xlstStyle.AlignHorz = XLAlignHorzEnum.Left;
xlstStyle.WordWrap = false;
xlstStyle.Font = new Font("", 8, FontStyle.Regular);
xlstStyle.SetBorderColor(Color.Black);
xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
xlstStyle.BorderTop = XLLineStyleEnum.Thin;
xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
xlstStyle.BorderRight = XLLineStyleEnum.Thin;
if ("0".Equals(ParentId))
{
xlstStyle.BackColor = Color.Orange;
}
xlsSheet[k, 1].Style = xlstStyle;
xlstStyle = new XLStyle(xlbBook);
if (String.IsNullOrEmpty(ParentId))
{
xlstStyle.BackColor = Color.Orange;
}
xlstStyle.WordWrap = false;
xlstStyle.Font = new Font("", 8, FontStyle.Regular);
xlstStyle.SetBorderColor(Color.Black);
xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
xlstStyle.BorderTop = XLLineStyleEnum.Thin;
xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
xlstStyle.BorderRight = XLLineStyleEnum.Thin;
xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
xlsSheet[k, colData + 1].Style = xlstStyle;
xlsSheet[k, colData + 2].Style = xlstStyle;
//xlsSheet[k, colData+3].Style = xlstStyle;
//xlsSheet[k, colData+4].Style = xlstStyle;
k++;
}
}
}
//ds = new DataSet();
//sql = string.Empty;
//sql = " SELECT *";
//sql += " FROM BD_PaymentReportMonth ";
//sql += " WHERE BuildingId = '" + building + "' ";
//sql += " and YearMonth = '" + yearmonth + "' order by id";
//using (db = new SqlDatabase())
//{
// using (SqlCommand cm = db.CreateCommand(sql))
// {
// SqlDataAdapter da = new SqlDataAdapter(cm);
// da.Fill(ds);
// if (ds != null)
// {
// xlbBook = new C1XLBook();
// fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\BaoCaoThuChiThang.xlsx");
// if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
// {
// Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])));
// }
// strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
// strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\BaoCaoThuChiThang" + strDT + ".xlsx";
// strFilePathExport = "Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + "/BaoCaoThuChiThang" + strDT + ".xlsx";
// fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);
// //string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
// File.Copy(fileName, fileNameDes);
// xlbBook.Load(fileNameDes);
// sheet = "BaoCao";
// xlsSheet = xlbBook.Sheets[sheet];
// int i = 5;
// xlsSheet[0, 0].Value = xlsSheet[0, 0].Value.ToString().Replace("{%TOA_NHA%}", DbHelper.GetScalar("Select Name From Mst_Building Where BuildingId = '" + building + "'"));
// xlsSheet[0, 0].Value = xlsSheet[0, 0].Value.ToString().Replace("{%NAM_THANG%}", "THÁNG" + yearmonth.Substring(4, 2) + "/" + yearmonth.Substring(0, 4));
// DataTable dtReport = ds.Tables[0];
// foreach (DataRow rowType in dtReport.Rows)
// {
// int colNo = Func.ParseInt(rowType["colNo"]);
// string PaymentType = rowType["PaymentType"].ToString();
// string InVND = rowType["InVND"].ToString();
// string InUSD = rowType["InUSD"].ToString();
// string OutVND = rowType["OutVND"].ToString();
// string OutUSD = rowType["OutUSD"].ToString();
// bool bold = rowType["bold"].ToString().Equals("1") ? true : false;
// XLCellRange mrCell = new XLCellRange(i, i, 0, 3);
// xlsSheet.MergedCells.Add(mrCell);
// xlsSheet[i, 0].Value = "." + " ".PadLeft(colNo * 3, ' ') + PaymentType;
// xlsSheet[i, 4].Value = Func.ParseDouble(InUSD);
// xlsSheet[i, 5].Value = Func.ParseDouble(InVND);
// xlsSheet[i, 6].Value = Func.ParseDouble(OutUSD);
// xlsSheet[i, 7].Value = Func.ParseDouble(OutVND);
// XLStyle xlstStyle = new XLStyle(xlbBook);
// xlstStyle.AlignHorz = XLAlignHorzEnum.Left;
// xlstStyle.WordWrap = false;
// xlstStyle.Font = new Font("", 8, FontStyle.Regular);
// xlstStyle.SetBorderColor(Color.Black);
// xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
// xlstStyle.BorderTop = XLLineStyleEnum.Thin;
// xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
// xlstStyle.BorderRight = XLLineStyleEnum.Thin;
// xlsSheet[i, 0].Style = xlstStyle;
// xlsSheet[i, 1].Style = xlstStyle;
// xlsSheet[i, 2].Style = xlstStyle;
// xlsSheet[i, 3].Style = xlstStyle;
// xlstStyle = new XLStyle(xlbBook);
// xlstStyle.WordWrap = false;
// xlstStyle.Font = new Font("", 8, FontStyle.Regular);
// xlstStyle.SetBorderColor(Color.Black);
// xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
// xlstStyle.BorderTop = XLLineStyleEnum.Thin;
// xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
// xlstStyle.BorderRight = XLLineStyleEnum.Thin;
// xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
// xlsSheet[i, 4].Style = xlstStyle;
// xlsSheet[i, 5].Style = xlstStyle;
// xlsSheet[i, 6].Style = xlstStyle;
// xlsSheet[i, 7].Style = xlstStyle;
// i++;
// }
// xlbBook.Save(fileNameDes);
// ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('../" + strFilePathExport + "'," + PopupWidth + "," + PopupHeight + ",'EditReport', true);", true);
// }
// }
//}
xlbBook.Save(fileNameDes);
ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('" + strFilePathExport + "'," + PopupWidth + "," + PopupHeight + ",'EditReport', true);", true);
}
}
