public bool ValidateConnection(XlsFileConnection conn, string fileNamePath, WaitDialogForm wait) { if (conn.Open() == null) { if (wait != null) wait.Close(); if (conn.ConnectionError == XlsFileErrorCode.NOT_EXCEL_FORMATED) { HelpMsgBox.ShowNotificationMessage("Tập tin \"" + fileNamePath + "\" không phải định dạng excel!"); } else if (conn.ConnectionError == XlsFileErrorCode.PASSWORD_PROTECTED) { HelpMsgBox.ShowNotificationMessage("Tập tin \"" + new FileInfo(fileNamePath).Name + "\" đã được bảo vệ bằng mật khẩu!"); } else { HelpMsgBox.ShowNotificationMessage("Truy cập vào tập tin \"" + fileNamePath + "\" không thành công!"); } return false; } return true; }
private TNSSaveResult ReadContent() { var result = TNSSaveResult.VALID_EXCEL; try { var f = new XlsFileConnection(_filePath, _excelVersion); f.Open(); var ds = f.LoadDataSet(_sheeName, null, false); f.Close(); if (ds == null || ds.Tables.Count == 0) result = TNSSaveResult.INVALID_EXCEL; else { var dt = ds.Tables[0]; dt.DefaultView.RowFilter = string.Format("{0} is not null and {0} <> ''", TNS_SALE_REV_SCR_CT.KENH_PHAT_NAME); var dtKenhPhat = dt.DefaultView.ToTable(true, TNS_SALE_REV_SCR_CT.KENH_PHAT_NAME); var channelNames = (from DataRow rKp in dtKenhPhat.Rows select rKp[0].ToString()).ToList(); _notExistsChannel = TNSHelper.GetNotExistsChannel(channelNames); if (_notExistsChannel != "") { return TNSSaveResult.NOT_EXIST_CHANNEL; } #region danh mục var allRowsInFile = dt.AsEnumerable(); FWDBService db = HelpDB.getDBService(); var dbTrans = FWTransaction.BeginTrans(db); FWTransaction fwTrans = FWTransaction.I(db, dbTrans); try { foreach (var dmObj in _dmObjs) { Application.DoEvents(); var colName = dmObj.ColNameInFile; if (dt.Columns.Contains(dmObj.ColIDInFile)) { dt.Columns.Remove(dmObj.ColIDInFile); } dt.Columns.Add(dmObj.ColIDInFile, typeof (Int64)); var dsDmFromDb = AppUtil.GetDataset(fwTrans, dmObj.TableName); var dtDmFromDb = dsDmFromDb.Tables[0]; var namesFromDb = dtDmFromDb.AsEnumerable().Select(r => r["NAME"].ToString()); var namesFromFile = allRowsInFile.Select(r => r[colName].ToString()).Where( a => a != "" && !a.StartsWith("..") && a != "-").Distinct( StringComparer.CurrentCultureIgnoreCase); var namesNew = namesFromFile.Except(namesFromDb, StringComparer.CurrentCultureIgnoreCase).ToList(); var count = namesNew.Count; if (count > 0) { var dtForSave = dtDmFromDb.Clone(); dtForSave.Columns["VISIBLE_BIT"].DefaultValue = "Y"; if (dmObj.TableName == DMDaiTruyenHinh.TABLE_MAP) { dtForSave.Columns["IS_DID"].DefaultValue = "N"; } var id = fwTrans.GetNewID(AppConst.G_FW_DM_ID); var maxId = id + count; AppUtil.SetValueGenerator(fwTrans, AppConst.G_FW_DM_ID, maxId); for (int i = 0; i < count; i++) { dtForSave.Rows.Add(id, namesNew[i]); id++; } var dsDmForSave = new DataSet(); dsDmForSave.Tables.Add(dtForSave); if (!db.UpdateDataSet(dsDmForSave, dbTrans)) { FWTransaction.Rollback(dbTrans); return TNSSaveResult.INVALID_EXCEL; } dtDmFromDb.Merge(dtForSave, false, MissingSchemaAction.Ignore); } dmObj.DataSet = dsDmFromDb; UpdateDatasetForComboxPivotField(dmObj); var colname = dmObj.ColNameInFile; var rowsInDb = dtDmFromDb.AsEnumerable(); foreach (var name in namesFromFile.ToList()) { var nameLower = name.ToLower(); Application.DoEvents(); var rid = rowsInDb.FirstOrDefault(r => r["NAME"].ToString().ToLower() == nameLower); if (rid != null&&rid["ID"].ToString()!="") { var rowsInFile = allRowsInFile.Where(r => r[colname].ToString().ToLower() == nameLower); rowsInFile.ToList().ForEach(r => r.SetField(dmObj.ColIDInFile, rid["ID"])); } } } FWTransaction.Commit(dbTrans); } catch (Exception) { FWTransaction.Rollback(dbTrans); return TNSSaveResult.VALID_EXCEL; } //TODO: begin refactor here, check why it's slow #endregion dt.Columns.Add(TNS_SALE_REV_SCR_CT.TIME_START); dt.Columns.Add(TNS_SALE_REV_SCR_CT.TIME_END); dt.Columns.Add(TNS_SALE_REV_SCR_CT.MINS_START, typeof(decimal)); dt.Columns.Add(TNS_SALE_REV_SCR_CT.MINS_END, typeof(decimal)); object preChannel = DBNull.Value; var preTimeband = ""; TNSTimeLine preTimes = null; object preAdvertiser = DBNull.Value; foreach (DataRow row in dt.Rows) { Application.DoEvents(); var channel = row[TNS_SALE_REV_SCR_CT.KENH_PHAT]; if (channel is DBNull) { var timeband = row[TNS_SALE_REV_SCR_CT.TIME_BAND].ToString(); if (timeband == "") { row[TNS_SALE_REV_SCR_CT.TIME_BAND] = preTimeband; var advertiser = row[TNS_SALE_REV_SCR_CT.ADVERTISER]; if (advertiser is DBNull) { row[TNS_SALE_REV_SCR_CT.ADVERTISER] = preAdvertiser; } else { preAdvertiser = advertiser; } } else { preTimeband = timeband; preTimes = TNSTimeLine.Parse(preTimeband); if (preTimes == null || preTimes.End.TotalMinute - preTimes.Start.TotalMinute != 60) { return TNSSaveResult.INVALID_TIMEBAND; } preAdvertiser = ""; } if (preTimes != null) { row[TNS_SALE_REV_SCR_CT.TIME_START] = preTimes.Start.TimeText; row[TNS_SALE_REV_SCR_CT.MINS_START] = preTimes.Start.TotalMinute; row[TNS_SALE_REV_SCR_CT.TIME_END] = preTimes.End.TimeText; row[TNS_SALE_REV_SCR_CT.MINS_END] = preTimes.End.TotalMinute; } row[TNS_SALE_REV_SCR_CT.KENH_PHAT] = preChannel; } else { preChannel = channel; preTimeband = ""; preTimes = null; } } dt.DefaultView.RowFilter = string.Format("{0} is not null and {0} <> ''", TNS_SALE_REV_SCR_CT.TIME_BAND); DataSet dsSource = DATNSSaleRevScr.I.LoadDetailDataSet(-1); DataTable dtSource = dsSource.Tables[0]; _savedDatatables = new List<DataTable>(); foreach (DateTime? date in _listDates) { Application.DoEvents(); if (date == null) continue; var subfix = TNSHelper.GetSubFix(date); var newDt = dt.DefaultView.ToTable(TNS_SALE_REV_SCR_CT.TABLE_NAME, false, TNS_SALE_REV_SCR_CT.KENH_PHAT, TNS_SALE_REV_SCR_CT.ADVERTISER, TNS_SALE_REV_SCR_CT.BRAND, TNS_SALE_REV_SCR_CT.SECTOR, TNS_SALE_REV_SCR_CT.GROUP, TNS_SALE_REV_SCR_CT.AGENCY, TNS_SALE_REV_SCR_CT.TIME_START, TNS_SALE_REV_SCR_CT.TIME_END, TNS_SALE_REV_SCR_CT.MINS_START, TNS_SALE_REV_SCR_CT.MINS_END, TNS_SALE_REV_SCR_CT.TIME_BAND, TNS_SALE_REV_SCR_CT.ALTERNATIVE_COST + subfix, TNS_SALE_REV_SCR_CT.GRP + subfix, TNS_SALE_REV_SCR_CT.DISCOUNT + subfix, TNS_SALE_REV_SCR_CT.CPP + subfix, TNS_SALE_REV_SCR_CT.GRP_PERCENT + subfix); TNSHelper.RenameColumn(newDt, subfix, TNS_SALE_REV_SCR_CT.ALTERNATIVE_COST, TNS_SALE_REV_SCR_CT.GRP, TNS_SALE_REV_SCR_CT.DISCOUNT, TNS_SALE_REV_SCR_CT.CPP, TNS_SALE_REV_SCR_CT.GRP_PERCENT); TNSHelper.AddDefaultColumn(newDt, TNS_SALE_REV_SCR_CT.NGAY_PHAT, typeof(DateTime), date.Value); _savedDatatables.Add(newDt); dtSource.Merge(newDt, true, MissingSchemaAction.Ignore); } _detailRowCount = dtSource.Rows.Count; pivotGridControl1.DataSource = dtSource; return result; } } catch (ArgumentException) { result = TNSSaveResult.INVALID_DATA_FORMAT; } catch (Exception) { result = TNSSaveResult.INVALID_EXCEL; } return result; }
private void btnImportCode_Click(object sender, EventArgs e) { WaitDialogForm wait = null; try { OpenFileDialog oP = new OpenFileDialog(); oP.InitialDirectory = "My Documents://"; oP.Filter = HelpExcel.FILTER_FILE_OPEN; oP.Title = HelpApplication.getTitleForm("Chọn bảng giá kế toán"); if (oP.ShowDialog() == DialogResult.Cancel) return; System.Windows.Forms.Application.DoEvents(); string filenamepath = oP.FileName; wait = new WaitDialogForm("Đang xử lý..", "Import code chương trình từ excel!", new Size(250, 50)); string tempFilePath = AppUtil.GetTempFile(Path.GetExtension(filenamepath)); File.Copy(filenamepath, tempFilePath, true); FileInfo inf = new FileInfo(tempFilePath); if (inf.IsReadOnly) { inf.IsReadOnly = false; } wait.SetCaption("Đang kết nối tập tin excel..."); XlsFileConnection conn = new XlsFileConnection(tempFilePath); if (ValidateConnection(conn, filenamepath, wait) == false) { if (wait != null) wait.Close(); return; } DataSet ds = new DataSet(); ds = conn.LoadDataSet(CHUONG_TRINH.TABLE_NAME, null); if (ds == null || ds.Tables.Count == 0) { wait.Close(); HelpMsgBox.ShowNotificationMessage("Tập tin excel có cấu trúc không đúng mẫu, vui lòng kiểm tra lại"); return; } if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)//Loại bỏ dòng rỗng { ds.Tables[0].Rows.RemoveAt(0); } conn.Close(); DataTable dt = ds.Tables[0]; dt.Columns.Add(CHUONG_TRINH.CT_ID, typeof(Int64)); dt.Columns.Add("CODE_CT_OLD"); DataRow r = null; string sql = "select ct.*,ct.code_ct code_ct_old from "+ CHUONG_TRINH.TABLE_NAME + " ct where ct." + CHUONG_TRINH.MA_CT + "='{0}'"; DataSet dsSource = HelpDB.getDBService().LoadDataSet(string.Format(sql, "-1"),CHUONG_TRINH.TABLE_NAME); DataTable dtSource = dsSource.Tables[0]; DataSet dsTemp = null; string error = ""; for (int i = 0; i < dt.Rows.Count; i++) { r = dt.Rows[i]; dsTemp = HelpDB.getDBService().LoadDataSet(string.Format(sql, r[CHUONG_TRINH.MA_CT])); if (dsTemp != null && dsTemp.Tables.Count > 0 && dsTemp.Tables[0].Rows.Count > 0) { dsTemp.Tables[0].Rows[0][CHUONG_TRINH.CODE_CT] = r[CHUONG_TRINH.CODE_CT]; dtSource.ImportRow(dsTemp.Tables[0].Rows[0]); } else { error += ""; } } gridControlImport.DataSource = dtSource; ; } catch (Exception ex) { if (wait != null) wait.Close(); HelpMsgBox.ShowNotificationMessage("Tập tin excel có cấu trúc không đúng mẫu, vui lòng kiểm tra lại."); PLException.AddException(ex); return; } finally { if (wait != null) wait.Close(); } }
private void CreateExcel() { FWDBService db = HelpDB.getDBService(); DbCommand cmd = db.GetStoredProcCommand("SP_LPS_KE_TOAN"); db.AddInParameter(cmd, "@I_TU_NGAY", DbType.Date, startDate); db.AddInParameter(cmd, "@I_DEN_NGAY", DbType.Date, endDate); db.AddInParameter(cmd, "@I_KENH_PHAT", DbType.Int64, KenhPhat._getSelectedID()); DataSet ds = db.LoadDataSet(cmd); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { HelpMsgBox.ShowNotificationMessage("Không có chương trình phát sóng trênh kênh " + KenhPhat.Text + " trong khoảng thời gian này!"); return; } string filenamepath = BangGiaKeToan.Text; DataTable dtGia = null; if (filenamepath != "") { string tempFilePath = AppUtil.GetTempFile(Path.GetExtension(filenamepath)); File.Copy(filenamepath, tempFilePath, true); FileInfo inf = new FileInfo(tempFilePath); if (inf.IsReadOnly) { inf.IsReadOnly = false; } XlsFileConnection conn = new XlsFileConnection(tempFilePath); if (ValidateConnection(conn, filenamepath, null) == false) { return; } DataSet dsBG = new DataSet(); dsBG = conn.LoadDataSet(AppConst.BANG_GIA_KE_TOAN_NAME, null); if (dsBG == null || dsBG.Tables.Count == 0) { HelpMsgBox.ShowNotificationMessage("Tập tin excel có cấu trúc không đúng mẫu, vui lòng kiểm tra lại"); return; } if (dsBG.Tables.Count > 0 && dsBG.Tables[0].Rows.Count > 0)//Loại bỏ dòng rỗng { dsBG.Tables[0].Rows.RemoveAt(0); } conn.Close(); System.Windows.Forms.Application.DoEvents(); dtGia = dsBG.Tables[0]; } DataTable dt = ds.Tables[0]; DataRow[] rows = null; if (dtGia != null) { decimal giatien = -1; foreach (DataRow r in dt.Rows) { if (r["CODE_CT"].ToString().Length > 0) { giatien = -1; if (r["RUN"] is DBNull) { rows = dtGia.Select("CODE_CT='" + r["CODE_CT"] + "'"); } else { rows = dtGia.Select("CODE_CT='" + r["CODE_CT"] + "' and RUN='" + r["RUN"] + "'"); } if (rows.Length > 0&&rows[0]["GIA_TIEN"].ToString() != "") { giatien = HelpNumber.ParseDecimal(rows[0]["GIA_TIEN"]); } if (giatien > -1) { r["GIA_TIEN"] = giatien.ToString(); } } } } FWDBService dbc = HelpDB.getDBService(); DbCommand cmdc = dbc.GetStoredProcCommand("SP_LPS_KE_TOAN_COLS"); DataSet dsCols = dbc.LoadDataSet(cmdc); DataTable dtCols = dsCols.Tables[0]; dtCols.Columns.Add("GIA_TIEN"); DataRow rCols = dtCols.Rows[0]; rCols["GIA_TIEN"] = "Giá tiền"; DataRow rAlign = dtCols.Rows[1]; rAlign["GIA_TIEN"] = "C"; Excel.ApplicationClass excelApp = new Excel.ApplicationClass(); Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing); Excel.Worksheet excelSheet = null; List<Excel.Worksheet> lSheet = new List<Excel.Worksheet>(); int startR = 1; int startC = 1; int curR = 0; int curC = 0; Excel.Range startCell = null; Excel.Range endCell = null; Excel.Range range = null; DataTable dtdays = dt.DefaultView.ToTable(true, "NGAY_PHAT"); DateTime date = new DateTime(); //DataRow[] rows = null; Color bdc = Color.FromArgb(178, 178, 178); int i = 1; string align = ""; foreach (DataRow rd in dtdays.Rows) { date = (DateTime)rd["NGAY_PHAT"]; excelSheet = (Excel.Worksheet)excelWorkbook.Sheets.Add( excelWorkbook.Sheets.get_Item(1), Type.Missing, 1, Excel.XlSheetType.xlWorksheet); excelSheet.Name = AppCtrl.GetThuTrongTuanTitle(date.DayOfWeek) + ", " + date.ToString("dd-MM"); try { excelSheet.Cells.Font.Name = "Times New Roman"; } catch { } curR = startR; curC = startC; startCell = (Excel.Range)excelSheet.Cells[curR, curC]; endCell = (Excel.Range)excelSheet.Cells[curR, curC + dtCols.Columns.Count - 2]; range = excelSheet.get_Range(startCell, endCell); range.Merge(Type.Missing); range.set_Value(Type.Missing, "CHƯƠNG TRÌNH PHÁT SÓNG TRÊN KÊNH " + KenhPhat.Text.ToUpper() + " (KÊNH GIẢI TRÍ TỔNG HỢP)"); range.Font.Bold = true; startCell = (Excel.Range)excelSheet.Cells[++curR, curC]; endCell = (Excel.Range)excelSheet.Cells[curR, curC + dtCols.Columns.Count - 2]; range = excelSheet.get_Range(startCell, endCell); range.Merge(Type.Missing); range.set_Value(Type.Missing, AppCtrl.GetThuTrongTuanTitle(date.DayOfWeek) + ": " + date.ToString(FrameworkParams.option.dateFormat)); range.Font.Bold = true; startCell = (Excel.Range)excelSheet.Cells[++curR, curC]; endCell = (Excel.Range)excelSheet.Cells[curR, curC + dtCols.Columns.Count - 2]; range = excelSheet.get_Range(startCell, endCell); range.Merge(Type.Missing); range.set_Value(Type.Missing, "Trực biên tập:"); range.Font.Bold = true; curR++; ; foreach (DataColumn c in dtCols.Columns) { if (c.ColumnName == "NGAY_PHAT") continue; range = ((Excel.Range)excelSheet.Cells[curR, curC]); range.set_Value(Type.Missing, rCols[c.ColumnName]); range.Font.Bold = true; range.HorizontalAlignment = hacenter; BorderCell(range); curC++; } rows = dt.Select(string.Format("NGAY_PHAT='{0}'", rd["NGAY_PHAT"])); curR++; decimal tongtien = 0; decimal tien = 0; foreach (DataRow r in rows) { curC = startC; foreach (DataColumn c in dtCols.Columns) { if (c.ColumnName == "NGAY_PHAT") continue; range = ((Excel.Range)excelSheet.Cells[curR, curC]); range.set_Value(Type.Missing, r[c.ColumnName]); align = rAlign[c.ColumnName].ToString().Trim(); if (align == "" || align == "L") range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; else if (align == "C") range.HorizontalAlignment = hacenter; else if (align == "R") range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; curC++; } curR++; tien = HelpNumber.ParseDecimal(r["GIA_TIEN"]); if (tien > 0) tongtien += tien; } int col = dtCols.Columns.Count; range = ((Excel.Range)excelSheet.Cells[curR, startC + col-2]); range.set_Value(Type.Missing,tongtien); range.Font.Bold = true; startCell = ((Excel.Range)excelSheet.Cells[curR, startC]); endCell = ((Excel.Range)excelSheet.Cells[curR, startC + col - 3]); range = excelSheet.get_Range(startCell, endCell); range.Merge(Type.Missing); range.set_Value(Type.Missing, "Tổng tiền"); range.Font.Bold = true; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; startCell = (Excel.Range)excelSheet.Cells[startR + 3, startC]; endCell = (Excel.Range)excelSheet.Cells[curR, startC + dtCols.Columns.Count - 2]; range = excelSheet.get_Range(startCell, endCell); range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; //range.Font.Size = 6; range.Columns.AutoFit(); lSheet.Add(excelSheet); } excelSheet.Application.ActiveWindow.DisplayGridlines = false; string name = AppUtil.GetTempFile(); string file = name + ".htm"; // TemplFiles.Add(file); crFile = name + extension; // TemplFiles.Add(crFile); excelWorkbook.SaveAs(crFile, xlFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelWorkbook.SaveAs(file, Excel.XlFileFormat.xlHtml, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelWorkbook.Close(true, Type.Missing, Type.Missing); excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelSheet = null; excelWorkbook = null; excelApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); excelWrapper1.OpenFile(file); }
public DataSet GetImportDataSet() { WaitDialogForm wait = null; try { OpenFileDialog oP = new OpenFileDialog(); oP.InitialDirectory = "My Documents://"; oP.Filter = HelpExcel.FILTER_FILE_OPEN; oP.Title = HelpApplication.getTitleForm("Import dữ liệu từ tập tin excel"); if (oP.ShowDialog() == DialogResult.Cancel) return null; System.Windows.Forms.Application.DoEvents(); string filenamepath = oP.FileName; wait = new WaitDialogForm("Đang xử lý..", "Import dữ liệu từ excel!", new Size(250, 50)); string tempFilePath = AppUtil.GetTempFile(Path.GetExtension(filenamepath)); File.Copy(filenamepath, tempFilePath, true); FileInfo inf = new FileInfo(tempFilePath); if (inf.IsReadOnly) { inf.IsReadOnly = false; } wait.SetCaption("Đang kết nối tập tin excel..."); XlsFileConnection conn = new XlsFileConnection(tempFilePath); if (ValidateConnection(conn, filenamepath, wait) == false) { if (wait != null) wait.Close(); return null; } DataSet ds = new DataSet(); ds = conn.LoadDataSet(BANG_GIA_BUDGET_CT.TABLE_NAME, null); if (ds == null) { wait.Close(); HelpMsgBox.ShowNotificationMessage("Tập tin excel có cấu trúc không đúng mẫu, vui lòng kiểm tra lại"); return null; } if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)//Loại bỏ dòng rỗng { ds.Tables[0].Rows.RemoveAt(0); } conn.Close(); System.Windows.Forms.Application.DoEvents(); return ds; } catch (Exception ex) { if (wait != null) wait.Close(); HelpMsgBox.ShowNotificationMessage("Tập tin excel có cấu trúc không đúng mẫu, vui lòng kiểm tra lại."); PLException.AddException(ex); return null; } finally { if (wait != null) wait.Close(); } }