public DaxStudioWindow(Microsoft.Office.Interop.Excel.Application app) { InitializeComponent(); Application = app; // Insert code required on object creation below this point. daxEditorUserControl1.AllowDrop = true; daxEditorUserControl1.Drop += UcDaxEditorDrop; _xlHelper = new ExcelHelper(_app, cboOutputTo); _workbook = Application.ActiveWorkbook; this.btnRunGrid.Click += OnRunGrdClick; }
static void TestExcelRead(string file) { try { using (ExcelHelper excelHelper = new ExcelHelper(file)) { DataTable dt = excelHelper.ExcelToDataTable("MySheet", true); PrintData(dt); } } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); } }
static void TestExcelWrite(string file) { try { using (ExcelHelper excelHelper = new ExcelHelper(file)) { DataTable data = GenerateData(); int count = excelHelper.DataTableToExcel(data, "MySheet", true); if (count > 0) Console.WriteLine("Number of imported data is {0} ", count); } } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); } }
public void GenerateSpreadsheet(OperationTemplates template, string outputLocation, Results data) { var templatePath = GetTemplate(template); using (ExcelHelper helper = new ExcelHelper(templatePath, outputLocation)) { helper.Direction = ExcelHelper.DirectionType.TOP_TO_DOWN; helper.CurrentSheetName = "Sheet1"; helper.CurrentPosition = new CellRef("A1"); helper.InsertRange("header"); CellRangeTemplate sample1; IEnumerable<List<object>> sample = null; switch (template) { case OperationTemplates.FIND_DLL_BY_PROJECT_TEMPLATE: sample1 = helper.CreateCellRangeTemplate("sample1", new List<string> { "dll", "branch", "version" }); sample = getSample(data, 3); break; case OperationTemplates.FIND_DLL_BY_NAME_TEMPLATE: sample1 = helper.CreateCellRangeTemplate("sample1", new List<string> { "project", "projectBranch", "dllBranch", "version"}); sample = getSample(data, 4); break; case OperationTemplates.FIND_DLL_BY_SOLUTION_TEMPLATE: sample1 = helper.CreateCellRangeTemplate("sample1", new List<string> { "project", "dllName", "dllBranch", "version" }); sample = getSample(data, 4); break; case OperationTemplates.FIND_SERVICE_REFERENCES_BY_NAME_TEMPLATE: sample1 = helper.CreateCellRangeTemplate("sample1", new List<string> { "solution", "branch", "url" }); sample = getSample(data, 3); break; case OperationTemplates.FIND_SERVICE_REFERENCES_BY_SOLUTION_TEMPLATE: sample1 = helper.CreateCellRangeTemplate("sample1", new List<string> { "serviceName", "url" }); sample = getSample(data, 2); break; default: return; } helper.InsertRange(sample1, sample); helper.DeleteSheet("Sheet3"); } }
public static bool ImportDkFromExcel(string filename,NZYData nzydata) { //ObservableCollection<NZYDKData> dk = new ObservableCollection<NZYDKData>(); ExcelHelper eh = new ExcelHelper(filename); DataTable dt; try { dt = eh.ExcelToDataTable("sheet1", true); } catch(Exception ex) { throw ex; } foreach(DataRow row in dt.Rows) { if (Methods.IsNumber(row[0].ToString())){ NZYDKData data = new NZYDKData(row[1].ToString()); data.PaddyArea =double.Parse(row[8].ToString()); data.PaddyLevel = double.Parse(row[9].ToString()); data.DryArea = double.Parse(row[10].ToString()); data.DryLevel = double.Parse(row[11].ToString()); nzydata.Dk.Add(data); } } return true; }
protected void Button2_Click(object sender, EventArgs e) { ExcelHelper.GridViewToExcel(Grid_Detail, "设备保养完成情况统计表"); }
public ActionResult GetFinanceReportList() { var data = ExcelHelper.ExcelImport(Server.MapPath("~/Areas/LR_ReportModule/Views/ReportTemplate/ReportData/FinanceReport.xlsx")); return(Success(data)); }
public static void LoadNpcConfig() { Debug.Log("开始读取npc表,需要较长时间,请耐心等候...."); npcData = ExcelHelper.ExcelToDataTable(EditorConfig.ExcelDataDir + "/120_npc.xlsm", "NPC"); Debug.Log("npc表读取完毕!"); }
private void btnLoadColumns_Click(object sender, EventArgs e) { var eh = new ExcelHelper((a) => { MessageBox.Show(a, "Erro"); }); cbSheets.DataSource = eh.GetSheetNames(txtCsvFile.Text); }
protected void btExport_Click(object sender, EventArgs e) { if (TabStrip1.ActiveTabIndex == 0) { if (GridGoods.Rows.Count < 1) { Alert.Show("没有数据,无法导出!"); return; } string strSql2 = @"(SELECT SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.SL),'XSD',ABS(B.SL),'XSG',ABS(B.SL),'DSH',ABS(B.SL))) FROM SYS_DEPT A,DAT_GOODSJXC B,DOC_GOODS C WHERE A.CODE = B.DEPTID AND A.TYPE = '3' AND B.RQSJ >= TO_DATE('{0}','yyyy-MM-dd') AND B.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1 AND B.KCADD < 0 AND B.GDSEQ = C.GDSEQ"; string strWhere2 = ")"; // if (!PubFunc.StrIsEmpty(ddlISGZ.SelectedValue)) strWhere2 += " AND C.ISGZ = '" + ddlISGZ.SelectedValue + "'"; if (strWhere2.Trim().Length > 0) { strSql2 = string.Format(strSql2, dpkDATE1.Text, dpkDATE2.Text) + strWhere2; } string strSql3 = @"(SELECT SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.HSJE),'XSG',ABS(B.HSJE),'XSD',ABS(B.HSJE),'DSH',ABS(B.HSJE))) FROM SYS_DEPT A,DAT_GOODSJXC B,DOC_GOODS C WHERE A.CODE = B.DEPTID AND A.TYPE = '3' AND B.RQSJ >= TO_DATE('{0}','yyyy-MM-dd') AND B.RQSJ< TO_DATE('{1}','yyyy-MM-dd') + 1 AND B.KCADD < 0 AND B.GDSEQ = C.GDSEQ"; string strWhere3 = ")"; // if (!PubFunc.StrIsEmpty(ddlISGZ.SelectedValue)) strWhere3 += " AND C.ISGZ = '" + ddlISGZ.SelectedValue + "'"; if (strWhere3.Trim().Length > 0) { strSql3 = string.Format(strSql3, dpkDATE1.Text, dpkDATE2.Text) + strWhere3; } string strSql = @"SELECT ' '||GDSEQ 商品编码,GDNAME 商品名称,SL 使用数量,ROUND(SLZB*100,4) ||'%' 数量占比,ROUND(TBSL*100,4) ||'%' 同比数量,ROUND(HBSL*100,4) ||'%' 环比数量,SYJE 使用金额,ROUND(JEZB*100,4) ||'%' 金额占比,ROUND(TBJE*100,4) ||'%' 同比金额,ROUND(HBJE*100,4) ||'%' 环比金额 from(SELECT C.GDSEQ, C.GDNAME,NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.SL),'XSD',ABS(B.SL),'XSG',ABS(B.SL),'DSH',ABS(B.SL))),0)SL, ROUND(NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.SL),'XSD',ABS(B.SL),'XSG',ABS(B.SL),'DSH',ABS(B.SL))),0)/{2},4) SLZB, F_GETBL('DPSLTB','{0}','{1}','{4}',C.GDSEQ,'','','') TBSL, F_GETBL('DPSLHB','{0}','{1}','{4}',C.GDSEQ,'','','') HBSL, NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.HSJE),'XSG',ABS(B.HSJE),'XSD',ABS(B.HSJE),'DSH',ABS(B.HSJE))),0) SYJE, ROUND(NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.HSJE),'XSD',ABS(B.HSJE),'XSG',ABS(B.HSJE),'DSH',ABS(B.HSJE))),0)/{3},4) JEZB, F_GETBL('DPJETB','{0}','{1}','{4}',C.GDSEQ,'','','') TBJE, F_GETBL('DPJEHB','{0}','{1}','{4}',C.GDSEQ,'','','') HBJE FROM SYS_DEPT A,DAT_GOODSJXC B,DOC_GOODS C WHERE A.CODE = B.DEPTID AND A.TYPE = '3' AND B.RQSJ >= TO_DATE('{0}','yyyy-MM-dd') AND B.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1 AND B.KCADD < 0 AND B.GDSEQ = C.GDSEQ"; string strWhere = ""; if (lstDEPTID.SelectedValue.Length > 0) { strWhere += " AND B.DEPTID = '" + lstDEPTID.SelectedValue + "'"; } if (strWhere.Trim().Length > 0) { strSql = strSql + strWhere; } strSql += " GROUP BY C.GDSEQ, C.GDNAME) where sl<>0"; string sortField = GridGoods.SortField; string sortDirection = GridGoods.SortDirection; string tempsql = string.Format(strSql, dpkDATE1.Text, dpkDATE2.Text, strSql2, strSql3, lstDEPTID.SelectedValue.ToString()) + String.Format(" ORDER BY 使用金额 DESC"); DataTable dt = DbHelperOra.QueryForTable(tempsql); ExcelHelper.ExportByWeb(dt, "单品消耗排行导出", "单品消耗排行导出_" + DateTime.Now.ToString("yyyyMMddHH") + ".xls"); } if (TabStrip1.ActiveTabIndex == 1) { if (GridList.Rows.Count < 1) { Alert.Show("没有数据,无法导出!"); return; } string strSql = @" SELECT ' '||B.GDSEQ 商品编码,B.GDNAME 商品名称,B.GDSPEC 规格,F_GETDEPTNAME(A.DEPTID) 部门,f_getunitname(B.UNIT) 单位, B.HSJJ 单价,nvl(A.SL,0)使用数量,NVL(A.HSJE,0)使用金额, f_getproducername(B.PRODUCER) 生产商,B.PIZNO 批准文号, ROUND((NVL(A.SL,0)/(SELECT SUM(DECODE(AA.BILLTYPE,'XST',-ABS(AA.SL),'XSG',ABS(AA.SL),'XSD',ABS(AA.SL),'DSH',ABS(AA.SL))) FROM DAT_GOODSJXC AA WHERE AA.RQSJ>=TO_DATE('{0}','yyyy-MM-dd') AND AA.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1 AND AA.DEPTID = A.DEPTID AND AA.KCADD='-1')),4) 数量占比, ROUND((nvl(A.HSJE,0)/(SELECT SUM(DECODE(AA.BILLTYPE,'XST',-ABS(AA.HSJE),'XSD',ABS(AA.HSJE),'XSG',ABS(AA.HSJE),'DSH',ABS(AA.HSJE))) FROM DAT_GOODSJXC AA WHERE AA.RQSJ >= TO_DATE('{0}','yyyy-MM-dd') AND AA.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1 AND AA.DEPTID = A.DEPTID AND AA.KCADD='-1')),4) 金额占比, F_GETBL('KSSLHB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') 数量环比,F_GETBL('KSSLTB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') 数量同比, F_GETBL('KSJEHB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') 金额环比,F_GETBL('KSJETB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') 金额同比 FROM (SELECT A.DEPTID,A.GDSEQ,SUM(DECODE(A.BILLTYPE,'XST',-ABS(A.SL),'XSG',ABS(A.SL),'XSD',ABS(A.SL),'DSH',ABS(A.SL))) SL ,SUM(DECODE(A.BILLTYPE,'XST',-ABS(A.HSJE),'XSD',ABS(A.HSJE),'XSG',ABS(A.HSJE),'DSH',ABS(A.HSJE))) HSJE FROM DAT_GOODSJXC A WHERE A.RQSJ>= TO_DATE('{0}','yyyy-MM-dd') AND A.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1 AND A.DEPTID IN(SELECT CODE FROM SYS_DEPT WHERE TYPE='3') AND A.KCADD<0 GROUP BY A.DEPTID,A.GDSEQ) A,DOC_GOODS B WHERE A.GDSEQ = B.GDSEQ"; string strWhere = ""; string strWhere2 = ""; if (ddlDEPTID.SelectedValue.Length > 1) { strWhere2 += " AND A.DEPTID = '" + ddlDEPTID.SelectedValue + "'"; } if (lisGDSEQ.Text.Trim().Length > 0) { strWhere += String.Format(" AND (B.GDSEQ LIKE '%{0}%' OR B.GDNAME LIKE '%{0}%' OR B.ZJM LIKE '%{0}%')", lisGDSEQ.Text.Trim()); } // if (lstISGZ.SelectedValue.Length > 0) strWhere += " AND B.ISGZ = '" + lstISGZ.SelectedValue + "'"; if (strWhere.Trim().Length > 0) { strSql = strSql + strWhere; } strSql += " AND SL<>0"; string tempsql = string.Format(strSql, lisDATE1.Text, lisDATE2.Text) + String.Format(" ORDER BY 使用金额 DESC"); DataTable dtData = DbHelperOra.QueryForTable(tempsql); //DataTable dtData = PubFunc.DbGetPage(GridList.PageIndex, GridList.PageSize, string.Format(strSql, dpkDATE1.Text, dpkDATE2.Text, strWhere2) + String.Format(" ORDER BY 使用金额 DESC" ), ref total); ExcelHelper.ExportByWeb(dtData, "商品使用排行导出", "商品使用排行导出_" + DateTime.Now.ToString("yyyyMMddHH") + ".xls"); } }
public void PreparePrintableDocument() { FormatDocument(); ExcelHelper.SetupPageBreaksView(); }
public void Init( string _path) { // Debug.Log("init state machine"); ExcelHelper helper = new ExcelHelper( _path); mainTable = helper.ReadXLS(Global.MAIN_SHEET_NAME); factory.Init(this); path = _path; }
public ActionResult UploadExcelForClient(string name, string type = "Xlsx") { var fileName = Guid.NewGuid().ToString("N"); var filePath = Server.MapPath(Settings.Current["TemporaryFolderForImportExport"]); ExcelType excelType; ExcelType.TryParse(type, true, out excelType); try { if (Request.Files.Count != 1 || Request.Files[0] == null) { return new ContentResult { ContentType = "text/html", Content = DynamicEntityJSONDataSource.GetNotSuccess("Неверное количество файлов в запросе.") } } ; var ds = ExcelHelper.FromExcel(Request.Files[0].InputStream, fileName, filePath, excelType); List <dynamic> records; var result = ImportExportHelper.ImportForClient(name, ds, out records); var report = result.GetReport(new ImportResultTextFormatter()); var res = new { success = !(result.HaveMappingErrors || result.HaveParsingErrors), message = report, records = records.Select(c => (c as DynamicEntity).Dictionary).ToArray() }; return(new ContentResult { ContentType = "text/html", Content = ServiceStack.Text.JsonSerializer.SerializeToString(res) }); } catch (Exception ex) { Logger.Log.Error(string.Format("Ошибка загрузки шаблона {0}", name), ex); return(new ContentResult { ContentType = "text/html", Content = DynamicEntityJSONDataSource.GetNotSuccess(ex.Message) }); } finally { try { var fullName = ExcelHelper.GetFullFilePath(fileName, filePath, excelType); if (System.IO.File.Exists(fullName)) { System.IO.File.Delete(fullName); } } catch (Exception ex) { Logger.Log.Error("Ошибка удаления временного файла", ex); } } }
/// <summary> /// 用户列表(导出Excel) /// </summary> /// <returns></returns> public void GetExportList() { try { //取出数据源 DataTable exportTable = userService.GetExportList(); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "测试用户导出"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.FileName = "用户导出.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 excelconfig.ColumnEntity = new List <ColumnModel>(); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_account", ExcelColumn = "账户" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_realname", ExcelColumn = "姓名" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_gender", ExcelColumn = "性别" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_birthday", ExcelColumn = "生日" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_mobile", ExcelColumn = "手机", Background = Color.Red }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_telephone", ExcelColumn = "电话", Background = Color.Red }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_wechat", ExcelColumn = "微信" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_organize", ExcelColumn = "公司" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_department", ExcelColumn = "部门" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_description", ExcelColumn = "说明" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_createdate", ExcelColumn = "创建日期" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_createusername", ExcelColumn = "创建人" }); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); } catch (Exception ex) { if (ex is ExceptionEx) { throw; } else { throw ExceptionEx.ThrowBusinessException(ex); } } }
/// <summary> /// CSV value of the cell /// </summary> public string CSVValue(bool useFormat, string separator) { return(ExcelHelper.ToCsv(useFormat ? DisplayValue : RawDisplayValue, separator)); }
private void InitTitle(object path) { try { eh = new ExcelHelper(path.ToString()); this.cmbKeyToPair.Dispatcher.Invoke(new Action(() => { cmbKeyToPair.ItemsSource = eh.GetRowWithColumnIndex(eh.FirstRowNum); cmbKeyToPair.SelectedIndex = 0; })); } catch (Exception ex) { eh = null; this.cmbKeyToPair.Dispatcher.Invoke(new Action(() => { cmbKeyToPair.ItemsSource = null; cmbKeyToPair.SelectedIndex = -1; ShowError(ex.Message, ex); })); ShowError("获取Excel标题失败", ex); } finally { GC.Collect(); } }
/// <summary> /// 导出EXCEL /// </summary> /// <param name="queryJson"></param> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; try { TrainingOrderEntity para = new TrainingOrderEntity(); if (!string.IsNullOrWhiteSpace(queryJson)) { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); //类型 if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty()) { var condition = queryParam["condition"].ToString().ToLower(); switch (condition) { case "trainingorderno": para.TrainingOrderNo = queryParam["keyword"].ToString(); break; case "membername": para.MemberName = queryParam["keyword"].ToString(); break; case "membermobile": para.MemberMobile = queryParam["keyword"].ToString(); break; case "schoolname": para.SchoolName = queryParam["keyword"].ToString(); break; case "trainingcarnumber": para.TrainingCarNumber = queryParam["keyword"].ToString(); break; } } if (!queryParam["status"].IsEmpty()) { para.Status = int.Parse(queryParam["status"].ToString()); } } para.sidx = "CreateTime"; para.sord = "desc"; var list = TrainingOrderBLL.Instance.GetList(para); if (list != null) { int subcount = 0; list.ForEach((o) => { if (o.ServiceDate != null) { o.ServiceTime = Converter.ParseDateTime(o.ServiceDate).ToString("yyyy-MM-dd") + " " + o.ServiceTime; } if (o.Status != null) { o.StatusName = ((RCHL.Model.Enums.TrainingStatus)o.Status).ToString(); } if (o.CashType != null) { o.CashTypeName = ((RCHL.Model.Enums.CashType)o.CashType).ToString(); } var detail = TrainingOrderDetailBLL.Instance.GetList(new TrainingOrderDetailEntity() { TrainingOrderId = o.TrainingOrderId }); int subCount = 0; detail.ForEach((d) => { subCount = subCount + 1; }); o.SubrictCount = subCount; }); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "实训预约订单"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "实训预约订单.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "TrainingOrderNo", ExcelColumn = "订单号", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "MemberName", ExcelColumn = "学员用户名", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "MemberMobile", ExcelColumn = "联系方式", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ServiceTime", ExcelColumn = "预约时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SubrictCount", ExcelColumn = "合计预约数", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SchoolName", ExcelColumn = "预约驾校", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Price", ExcelColumn = "费用", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CashTypeName", ExcelColumn = "支付方式", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "TrainingTypeName", ExcelColumn = "实训类型", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "TrainingCarName", ExcelColumn = "实训车名称", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "TrainingCarNumber", ExcelColumn = "实训车车牌", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "StatusName", ExcelColumn = "状态", Width = 20 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <TrainingOrderEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
public ActionResult Export(string queryJson) { Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 100000000; pagination.p_kid = "rownum idx"; pagination.p_fields = @"firetypename,carinvest, equipinvest, practicalinvest"; pagination.p_tablename = "HRS_FireEstimate t"; pagination.conditionJson = string.Format(" 1=1 "); pagination.sidx = "createdate"; //排序字段 pagination.sord = "desc"; //排序方式 Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (!user.IsSystem) { pagination.conditionJson += string.Format(" and CREATEUSERORGCODE ='{0}'", user.OrganizeCode); } var watch = CommonHelper.TimerStart(); var data = FireEstimatebll.GetPageList(pagination, queryJson); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "各类器材投资估算指标"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 16; excelconfig.FileName = "各类器材投资估算指标.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "idx", ExcelColumn = "序号", Alignment = "center" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "firetypename", ExcelColumn = "名称", Alignment = "center" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "carinvest", ExcelColumn = "车辆投资(万元)", Alignment = "center" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equipinvest", ExcelColumn = "装备和器材投资(万元)", Alignment = "center" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "practicalinvest", ExcelColumn = "实际投入(万元)", Alignment = "center" }); //调用导出方法 ExcelHelper.ExcelDownload(data, excelconfig); return(Success("导出成功。")); }
private void radButton3_Click(object sender, EventArgs e) { ExcelHelper.ImprotDataToExcel(this.dgv_01, "灭菌器批次"); }
//xem trước các lỗi bắt bước 1 private void ProcessPreviewExcelFile(string excelPath, string extension) { bool IsError = false; ExcelHelper excelHelper = new ExcelHelper(); DataSet dataset = new DataSet(); try { excelHelper.OpenExcelFile(excelPath, extension); } catch { ErrorMessage(true, "Lỗi không tìm thấy file import"); return; } try { dataset = excelHelper.GetWorksheets(excelHelper.WorkSheetNames[0]); } catch { lblMessError.Text = Resource.ErrorD002; DeleteFileTemp(excelPath); return; } if (dataset.Tables[0].Rows.Count > 2000) { ErrorMessage(true, "Để đảm bảo đường truyền. Vui lòng chỉ import tối đa 2000 record trong một lần!"); return; } if (dataset.Tables.Count > 0) { //kiem tra mau import int count = 0; int countError = 0; if (dataset.Tables[0].Rows.Count > 0 && !ValidateColumnName(dataset.Tables[0].Rows[0])) { lblMessError.Text = Resource.ErrorD001; return; } dataset.Tables[0].Columns.Add(new DataColumn("STT", typeof(int))); dataset.Tables[0].Columns.Add(new DataColumn("ErrorContent", typeof(string))); dataset.Tables[0].Columns.Add(new DataColumn("IsDongLoi", typeof(bool))); //Check data import string stringError; bool boolError; List<Catologie> listCatologie = new List<Catologie>(); string itemInRow; string positionRecordError = string.Empty; try { foreach (DataRow row in dataset.Tables[0].Rows) { itemInRow = string.Empty; stringError = string.Empty; boolError = false; row["STT"] = ++count; Catologie itemSP = new Catologie(); itemInRow = row["Massv"].ToString().Trim().ToUpper(); if (itemInRow.Equals("")) { stringError += ", Mã sinh viên " + Resource.ErrorD003; IsError = true; } else itemSP.Massv = itemInRow; itemInRow = row["hovaten"].ToString().Trim(); if (itemInRow == "") { stringError += ", Tên sinh viên " + Resource.ErrorD003; IsError = true; } else itemSP.CatologyName = itemInRow; itemInRow = row["lop"].ToString().Trim().ToUpper(); if (itemInRow == "") { stringError += ", Lớp " + Resource.ErrorD003; IsError = true; } else itemSP.Lop = itemInRow; itemInRow = row["khoa"].ToString().Trim().ToUpper(); if (itemInRow == "") { stringError += ", Khoa " + Resource.ErrorD003; IsError = true; } else itemSP.Khoa = itemInRow; //Check exist data try { //Check row exist in data import if (listCatologie.Contains(itemSP)) { stringError += ", record " + Resource.ErrorD006; IsError = true; } else listCatologie.Add(itemSP); //Check row exist in database if (CheckItemCatologie(itemSP)) { stringError += ", Trùng lắp thông tin CSDL"; IsError = true; } } catch (Exception) { stringError = Resource.ErrorD007; IsError = true; } //Check error; true: error - false: unError boolError = stringError == string.Empty ? false : true; if (boolError) { countError++; positionRecordError = positionRecordError + ", " + count; } row["IsDongLoi"] = IsError;// boolError; row["ErrorContent"] = stringError; } } catch { } DeleteFileTemp(excelPath); //Save xml to preview and next step 2, then, save data into database lblMessageError.Text = "Lỗi " + countError.ToString() + " record! <b>Vị trí</b>: " + positionRecordError; if (TempXMLHelper.SaveTemporayDataAsXml(Server.MapPath(ConfigurationManager.AppSettings["VALUE_PATH_XML_INFOTEACHING"] + "/" + SiteUtils.GetCurrentUserId() + ".xml"), dataset)) { if (!IsError) PopulateControls(Step.Step2); else PopulateControls(Step.Error); } else PopulateControls(Step.Step1); } }
void BGW_DoWork(object sender, DoWorkEventArgs e) { #region 取得需要的資料 string fileName = (string)((object[])e.Argument)[0]; // 取得學生資料 Dictionary<string, ValueObj.StudentVO> studentListDic = DAO.FDQuery.GetStudentInfo(_StudentIdList); // 取得學生學習歷程 List<JHSchool.Data.JHSemesterHistoryRecord> semesterHistoryList = JHSchool.Data.JHSemesterHistory.SelectByStudentIDs(_StudentIdList); Dictionary<string, JHSchool.Data.JHSemesterHistoryRecord> semesterHistoryListDic = new Dictionary<string, JHSchool.Data.JHSemesterHistoryRecord>(); foreach (var record in semesterHistoryList) { if (!semesterHistoryListDic.ContainsKey(record.RefStudentID)) semesterHistoryListDic.Add(record.RefStudentID, record); } _BGW.ReportProgress(10); // 取得領域成績 Dictionary<string, ValueObj.DomainsVO> domainScoreListDic = DAO.FDQuery.GetDomainScore(_StudentIdList); // 取得社團 Dictionary<string, ValueObj.ClubsVO> clubListDic = DAO.FDQuery.GetClubRecordByStudentIdList(_StudentIdList); _BGW.ReportProgress(20); // 取得服務學習 Dictionary<string, ValueObj.ServicesVO> serviceListDic = DAO.FDQuery.GetLearningServiceByStudentIdList(_StudentIdList); // 取得懲戒紀錄 List<Data.DemeritRecord> demeritList = K12.Data.Demerit.SelectByStudentIDs(_StudentIdList); Dictionary<string, ValueObj.DemeritsVO> demeritListDic = new Dictionary<string,ValueObj.DemeritsVO>(); // 把資料依照學生跟學年度學期分好 foreach (Data.DemeritRecord rec in demeritList) { if (!demeritListDic.ContainsKey(rec.RefStudentID)) demeritListDic.Add(rec.RefStudentID, new ValueObj.DemeritsVO()); demeritListDic[rec.RefStudentID].AddDemerit(rec); } _BGW.ReportProgress(30); // 取得獎歷紀錄 List<Data.MeritRecord> meritList = K12.Data.Merit.SelectByStudentIDs(_StudentIdList); Dictionary<string, ValueObj.MeritsVO> meritListDic = new Dictionary<string,ValueObj.MeritsVO>(); // 把資料依照學生跟學年度學期分好 foreach (Data.MeritRecord rec in meritList) { if (!meritListDic.ContainsKey(rec.RefStudentID)) meritListDic.Add(rec.RefStudentID, new ValueObj.MeritsVO()); meritListDic[rec.RefStudentID].AddMerit(rec); } #endregion _BGW.ReportProgress(50); #region 計算分數 foreach (ValueObj.StudentVO studentObj in studentListDic.Values) { // 由學習歷程來取得年級跟學年度學期的關係 Dictionary<int, ValueObj.SchoolYearSemester> gradeMap = Utility.ProcessSemesterHistory(semesterHistoryListDic[studentObj.StudentId]); decimal totalPoints = 0; // 計算扶助弱勢 totalPoints += Cal_VulnerableGroups(studentObj, _TagMap); // 計算均衡學習 totalPoints += Cal_BalanceLearning(studentObj, gradeMap, (domainScoreListDic.ContainsKey(studentObj.StudentId) ? domainScoreListDic[studentObj.StudentId] : null)); // 計算德行表現 totalPoints += Cal_VirtuousConduct(studentObj, gradeMap, (clubListDic.ContainsKey(studentObj.StudentId) ? clubListDic[studentObj.StudentId] : null), (serviceListDic.ContainsKey(studentObj.StudentId) ? serviceListDic[studentObj.StudentId] : null)); // 計算無記過紀錄 totalPoints += Cal_Demerit(studentObj, gradeMap, (demeritListDic.ContainsKey(studentObj.StudentId) ? demeritListDic[studentObj.StudentId] : null)); // 計算獎勵紀錄 totalPoints += Cal_Merit(studentObj, gradeMap, (meritListDic.ContainsKey(studentObj.StudentId) ? meritListDic[studentObj.StudentId] : null)); // 回存學生的總分數 studentObj.TotalPoints = totalPoints; } #endregion _BGW.ReportProgress(70); #region 輸出到Excel // 輸出到Excel ExcelHelper excelHelper = new ExcelHelper(fileName); List<ValueObj.StudentVO> StudentList = studentListDic.Values.ToList(); // 排序 StudentList.Sort(SortStudent); // 輸出正常資料 foreach (ValueObj.StudentVO studentObj in StudentList) { excelHelper.OutRowData(studentObj); excelHelper.OutDetailData(studentObj); } // 輸出有問題的學生 excelHelper.OutEmptyRowData(); foreach (var rec in _WarningStudentDic) { excelHelper.OutWarningData(rec.Key, rec.Value); } #endregion _BGW.ReportProgress(90); // 儲存結果 e.Result = new object[] { excelHelper }; }
public ActionResult UploadExcel(string name, string type = "Xlsx") { var fileName = Guid.NewGuid().ToString("N"); var filePath = Server.MapPath(Settings.Current["TemporaryFolderForImportExport"]); ExcelType excelType; ExcelType.TryParse(type, true, out excelType); try { if (Request.Files.Count != 1 || Request.Files[0] == null) { return new ContentResult { ContentType = "text/html", Content = DynamicEntityJSONDataSource.GetNotSuccess("Неверное количество файлов в запросе.") } } ; var ds = ExcelHelper.FromExcel(Request.Files[0].InputStream, fileName, filePath, excelType); var result = ImportExportHelper.Import(name, ds); var report = result.GetReport(new ImportResultTextFormatter()); return(new ContentResult { ContentType = "text/html", Content = DynamicEntityJSONDataSource.GetSuccess(report.Replace("\r\n", "</br>")) }); } catch (OptimaJet.DynamicEntities.Exceptions.DynamicEntitiesConvertException ex) { string msg = String.Format("{0}: {1}.", LocalizationProvider.Provider.Get("Error of template loading"), LocalizationProvider.Provider.Get("Incorrect format of fields in file")); Logger.Log.Error(ex.Message, ex); return(new ContentResult { ContentType = "text/html", Content = DynamicEntityJSONDataSource.GetNotSuccess(msg) }); } catch (Exception ex) { Logger.Log.Error(LocalizationProvider.Provider.Get("Error of template loading") + " " + name, ex); return(new ContentResult { ContentType = "text/html", Content = DynamicEntityJSONDataSource.GetNotSuccess(ex.Message) }); } finally { try { var fullName = ExcelHelper.GetFullFilePath(fileName, filePath, excelType); if (System.IO.File.Exists(fullName)) { System.IO.File.Delete(fullName); } } catch (Exception ex) { Logger.Log.Error("Ошибка удаления временного файла", ex); } } }
public ActionResult ExportData(string queryJson) { try { Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 1000000000; pagination.p_kid = "Id"; pagination.p_fields = @"FileName,RelatedCompany,AccTime,case when AccidentGrade='1' then '一般事故' when AccidentGrade ='2' then '较大事故' when AccidentGrade='3' then '重大事故' when AccidentGrade='4' then '特别重大事故' end AccidentGrade,intDeaths,AccType, case when AccRange='1' then '本单位事故' when AccRange ='2' then '本集团事故' when AccRange='3' then '电力系统内容事故' end AccRange ,Remark,FilesId,AccidentCompany"; pagination.p_tablename = " bis_accidentCaseLaw"; pagination.conditionJson = "1=1"; pagination.sidx = "createdate"; //排序字段 pagination.sord = "desc"; //排序方式 Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (!user.IsSystem) { IEnumerable <DepartmentEntity> orgcodelist = new List <DepartmentEntity>(); //电厂获取省公司的机构ID if (user.RoleName.Contains("省级用户")) { pagination.conditionJson += " and ( createuserorgcode = '" + user.OrganizeCode + "' or createuserorgcode='00')"; } else { orgcodelist = departmentBLL.GetList().Where(t => user.NewDeptCode.Contains(t.DeptCode) && t.Nature == "省级"); pagination.conditionJson += " and ("; foreach (DepartmentEntity item in orgcodelist) { pagination.conditionJson += "createuserorgcode ='" + item.EnCode + "' or "; } pagination.conditionJson += " createuserorgcode = '" + user.OrganizeCode + "' or createuserorgcode='00')"; } } DataTable exportTable = accidentcaselawbll.GetPageDataTable(pagination, queryJson); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "事故案例信息"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 16; excelconfig.FileName = "事故案例信息导出.xls"; excelconfig.IsAllSizeColumn = true; List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); //需跟数据源列顺序保持一致 excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "filename", ExcelColumn = "事故名称", Width = 50 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "relatedcompany", ExcelColumn = "涉事单位", Width = 50 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "acctime", ExcelColumn = "事故时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "accidentgrade", ExcelColumn = "事故等级", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "intdeaths", ExcelColumn = "死亡人数", Width = 30 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "acctype", ExcelColumn = "事故类别", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "accrange", ExcelColumn = "数据范围", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "remark", ExcelColumn = "备注", Width = 15 }); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); } catch (Exception ex) { } return(Success("导出成功。")); }
/// <summary> /// 导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExport_Click(object sender, EventArgs e) { string saveFileName = "成员贡献率"; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xlsx"; saveDialog.Filter = "Excel文件|*.xlsx"; saveDialog.FileName = saveFileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) { return; //被点了取消 } dynamic xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } //ExcelHelper excel = new ExcelHelper(FileHelper.GetWorkdir() + FileHelper.GetUploadPath(UploadType.Report_Plan, "", "") + ConstHelper.Report_Plan, saveFileName); ExcelHelper excel = new ExcelHelper(saveFileName);//创建excel //excel.InsertColumns(1, 8);//创建8列 #region 项目计划 List <string> columns; columns = new List <string>() { "RowNo", "Source", "name", "desc", "startedate", "enddate", "workload", "zhanbi" }; Export(dt, excel, columns); #endregion #region 设置单元格格式 //先设置单元格居右 excel.SetCellsAlign(2, 1, dt.Rows.Count + 1, 8, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight); int rowno = 1; int first = 1; int last = 1; foreach (DataRow item in dt.Rows) { rowno++; if (item["type"].ToString() == "-1") { last = rowno - 2; if (last > 2) { excel.SetCellsBorder(first, 1, last, 8);//设置边框 } first = rowno; excel.SetCellsStyle(rowno, 1, rowno, 8, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, true, ColorIndex.灰色25); //设置 excel.SetCellsStyle(rowno + 1, 1, rowno + 1, 8, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, false, ColorIndex.灰色25); //设置 } } excel.SetCellsBorder(first, 1, dt.Rows.Count + 1, 8); //设置边框 excel.InsertRows(1, 1); excel.MergeCells(1, 1, 2, 8, project.Name); //项目名称 excel.DeleteRows(4, 1); excel.SetCellsStyle(1, 1, 1, 1, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, true, ColorIndex.无色); //项目名称粗体 #endregion excel.SaveAsFile();//文件保存 MessageBox.Show("操作成功!"); }
private List <m.KIIDData> ReadFundsData() { Log.Info("Inizio generazione documenti "); const string mainSheetname = "DATI KIID"; const string performanceSheetname = "PERFORMANCE"; const int MAX_EMPTY_ROWS = 3; List <m.KIIDData> result = new List <m.KIIDData>(); Thread.CurrentThread.CurrentCulture = new CultureInfo("en"); Log.Info("Inizio lettura dati input"); using (ExcelHelper excelHelper = new ExcelHelper(datafile)) { //Performance Log.Info("Inizio lettura dati input - Performance"); int row = 2; Dictionary <string, SortedDictionary <string, string> > isinPerformanceAnnoMap = new Dictionary <string, SortedDictionary <string, string> >(); string isin = excelHelper.GetValue(performanceSheetname, "B", row.ToString()); int emptyRows = 0; while (emptyRows <= MAX_EMPTY_ROWS) { string anno = "0", dato = "0"; if (string.IsNullOrEmpty(isin)) { emptyRows++; } else { emptyRows = 0; anno = excelHelper.GetValue(performanceSheetname, "C", row.ToString()); dato = excelHelper.GetValue(performanceSheetname, "D", row.ToString()); if (string.IsNullOrEmpty(dato)) { dato = "0"; } else { dato = (Convert.ToDouble(dato) / 100).ToString(); } SortedDictionary <string, string> isinPerformanceAnno; if (!isinPerformanceAnnoMap.TryGetValue(isin, out isinPerformanceAnno)) { isinPerformanceAnno = new SortedDictionary <string, string>(); } isinPerformanceAnno[anno] = dato; isinPerformanceAnnoMap[isin] = isinPerformanceAnno; } row++; isin = excelHelper.GetValue(performanceSheetname, "B", row.ToString()); } Log.Info("Termine lettura dati input - Performance"); Log.Info("Inizio lettura dati input - Dati fondi"); string suffix = ""; if (!language.Equals("it-IT")) { suffix += " - " + language.Split('-')[0].ToUpper(); } Log.Info("Dati fondi: suffisso " + suffix); Dictionary <string, string> fieldPosition = new Dictionary <String, string>(); //Header row string classeCol = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "CLASSE", 1)); string isinCol = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "ISIN", 1)); string classeDiRischioCol = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "CLASSE DI RISCHIO", 1)); string testo1Col = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "TESTO1" + suffix, 1)); string testo2Col = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "TESTO2" + suffix, 1)); string testo3Col = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "TESTO3" + suffix, 1)); string spesesottoscrizioneCol = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "SPESE SOTTOSCRIZIONE", 1)); string speserimborsoCol = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "SPESE DI RIMBORSO", 1)); string spesecorrentiCol = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "SPESE CORRENTI", 1)); string spesediconversioneCol = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "SPESE DI CONVERSIONE", 1)); string commissioniRendimentoCol = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "COMMISSIONI LEGATE AL RENDIMENTO" + suffix, 1)); //string informazionipraticheCol = excelHelper.GetCellColumn(excelHelper.GetCellByContent(mainSheetname, "INFORMAZIONI PRATICHE" + suffix, 1)); string datagenerazioneStr = datagenerazione.ToString("dd MMMM yyyy", cultureInfo); Log.Debug("testo1Col " + testo1Col); Log.Debug("testo2Col " + testo2Col); Log.Debug("testo3Col " + testo3Col); Log.Debug("commissioniRendimentoCol " + commissioniRendimentoCol); //Log.Debug("informazionipraticheCol " + informazionipraticheCol); //Dati Fondo row = 3; string classe = excelHelper.GetValue(mainSheetname, classeCol, row.ToString()); while (!string.IsNullOrEmpty(classe)) { string currentIsin = excelHelper.GetValue(mainSheetname, isinCol, row.ToString()); SortedDictionary <string, string> performances = new SortedDictionary <string, string>(); isinPerformanceAnnoMap.TryGetValue(currentIsin, out performances); m.KIIDData item = new m.KIIDData( classe, currentIsin, excelHelper.GetValue(mainSheetname, classeDiRischioCol, row.ToString()), excelHelper.GetValue(mainSheetname, testo1Col, row.ToString()), excelHelper.GetValue(mainSheetname, testo2Col, row.ToString()), excelHelper.GetValue(mainSheetname, testo3Col, row.ToString()), (Convert.ToDouble(excelHelper.GetValue(mainSheetname, spesesottoscrizioneCol, row.ToString())) * 100).ToString(), (Convert.ToDouble(excelHelper.GetValue(mainSheetname, speserimborsoCol, row.ToString())) * 100).ToString(), (Convert.ToDouble(excelHelper.GetValue(mainSheetname, spesecorrentiCol, row.ToString())) * 100).ToString(), (Convert.ToDouble(excelHelper.GetValue(mainSheetname, spesediconversioneCol, row.ToString())) * 100).ToString(), excelHelper.GetValue(mainSheetname, commissioniRendimentoCol, row.ToString()), "",//excelHelper.GetValue(mainSheetname, informazionipraticheCol, row.ToString()), datagenerazioneStr, performances ); result.Add(item); row++; classe = excelHelper.GetValue(mainSheetname, classeCol, row.ToString()); } Log.Info("Termine lettura dati input - Dati fondi"); } TotNumeroDocumenti = result.Count(); return(result); }
public virtual string UpdateWJFL(DateTime asOfDate, string sourceFilePath) { logger.DebugFormat("Updating WJFL for {0}", asOfDate.ToString("yyyy-MM-dd")); var result = string.Empty; if (!File.Exists(sourceFilePath)) { result = "风险贷款情况表的初表修订结果在这个路径下没找到:\r\n" + sourceFilePath; logger.Debug(result); return(result); } var dao = new SqlDbHelper(); var dateString = asOfDate.ToString("yyyyMMdd"); logger.DebugFormat("Getting existing import id for {0}", dateString); var import = Import.GetByDate(asOfDate); if (import == null || !import.Items.Exists(x => x.ItemType == XEnum.ImportItemType.Loan)) { result = string.Format("{0}的《贷款欠款查询》数据还没导入系统,请先导入这项数据。", asOfDate.ToString("yyyy年M月d日")); logger.Debug(result); return(result); } var importFolder = System.Environment.CurrentDirectory + "\\Import\\" + import.Id.ToString(); var targetFileName = "WJFL.xls"; //Original var originalFolder = importFolder + @"\Original\"; if (!Directory.Exists(originalFolder)) { Directory.CreateDirectory(originalFolder); } File.Copy(sourceFilePath, originalFolder + @"\" + targetFileName, true); //Processed var processedFolder = importFolder + @"\Processed\"; if (!Directory.Exists(processedFolder)) { Directory.CreateDirectory(processedFolder); } File.Copy(sourceFilePath, processedFolder + @"\" + targetFileName, true); var targetFilePath = processedFolder + @"\" + targetFileName; result = ExcelHelper.ProcessWJFL(targetFilePath); if (!string.IsNullOrEmpty(result)) { return(result); } logger.Debug("Updating in database"); var oleOpened = false; OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + targetFilePath + ";Extended Properties=Excel 8.0"); try { logger.Debug("Opening connection to " + targetFilePath); oconn.Open(); oleOpened = true; logger.Debug("Opened"); logger.Debug("Reading from No Accrual sheet"); OleDbCommand ocmd = new OleDbCommand("SELECT [行名], [客户名称], [贷款余额], [放款日期], [到期日期], [七级分类] FROM [非应计$]", oconn); OleDbDataReader reader = ocmd.ExecuteReader(); logger.Debug("Executed"); result = UpdateWJFLSheet(import.Id, reader); if (!string.IsNullOrEmpty(result)) { return(result); } logger.Debug("Reading from Overdue sheet"); ocmd = new OleDbCommand("SELECT [行名], [客户名称], [贷款余额], [放款日期], [到期日期], [七级分类] FROM [逾期$]", oconn); reader = ocmd.ExecuteReader(); logger.Debug("Executed"); result = UpdateWJFLSheet(import.Id, reader); if (!string.IsNullOrEmpty(result)) { return(result); } logger.Debug("Reading from ZQX sheet"); ocmd = new OleDbCommand("SELECT [行名], [客户名称], [贷款余额], [放款日期], [到期日期], [七级分类] FROM [只欠息$]", oconn); reader = ocmd.ExecuteReader(); logger.Debug("Executed"); result = UpdateWJFLSheet(import.Id, reader); if (!string.IsNullOrEmpty(result)) { return(result); } logger.Debug("Updating WJFLDate field for import #" + import.Id.ToString()); dao.ExecuteNonQuery("UPDATE Import SET WJFLDate = GETDATE() WHERE Id = " + import.Id.ToString()); logger.Debug("Updated"); } catch (Exception ex) { logger.Error("Outest catch", ex); return(ex.Message); } finally { if (oleOpened) { oconn.Close(); } } return(result); }
/// <summary> /// 导出EXCEL /// </summary> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; AuditOrderEntity para = new AuditOrderEntity(); try { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); if (!queryParam["StartTime"].IsEmpty() && !queryParam["EndTime"].IsEmpty()) { DateTime startTime = queryParam["StartTime"].ToDate(); DateTime endTime = queryParam["EndTime"].ToDate(); para.StartTime = startTime.ToString(); para.EndTime = endTime.ToString(); } //类型 if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty()) { var condition = queryParam["condition"].ToString().ToLower(); switch (condition) { case "carnum": para.CarNum = queryParam["keyword"].ToString(); break; case "carframenum": para.CarFrameNum = queryParam["keyword"].ToString(); break; case "mobile": para.Mobile = queryParam["keyword"].ToString(); break; case "organizationname": para.OrganizationName = queryParam["keyword"].ToString(); break; } } if (!queryParam["TimeSpace"].IsEmpty()) { para.ServiceTime = queryParam["TimeSpace"].ToString(); } para.NotStatus = (int)QX360.Model.Enums.PaySatus.已取消; var list = AuditOrderBLL.Instance.GetList(para); if (list != null) { //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "个人年审车辆预约明细报表"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "个人年审车辆预约明细报表.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CarNum", ExcelColumn = "车牌号码", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CarFrameNum", ExcelColumn = "车架号", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Mobile", ExcelColumn = "联系电话", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ServiceDate", ExcelColumn = "预约时间", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ServiceTime", ExcelColumn = "预约时段", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "OrganizationName", ExcelColumn = "年审机构", Width = 15 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <AuditOrderEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
protected bool CopyItem(int importId, string importFolder, string sourceFilePath, XEnum.ImportItemType itemType) { int itemTypeId = (int)itemType; if (sourceFilePath.Length == 0 || !File.Exists(sourceFilePath)) { return(false); } string targetFileName = this.targetFileNames[itemTypeId]; if (itemType == XEnum.ImportItemType.YWNei || itemType == XEnum.ImportItemType.YWWai || itemType == XEnum.ImportItemType.Loan) { var orgId = GetOrgId4YW(sourceFilePath); targetFileName = GetYWTargetFileName(itemType, orgId); } //Original var originalFolder = importFolder + @"\Original\"; if (!Directory.Exists(originalFolder)) { Directory.CreateDirectory(originalFolder); } File.Copy(sourceFilePath, originalFolder + @"\" + targetFileName, true); //Processed var processedFolder = importFolder + @"\Processed\"; if (!Directory.Exists(processedFolder)) { Directory.CreateDirectory(processedFolder); } File.Copy(sourceFilePath, processedFolder + @"\" + targetFileName, true); logger.Debug("Process copied item for " + itemType.ToString()); ExcelHelper.ProcessCopiedItem(processedFolder + @"\" + targetFileName, itemType); logger.Debug("Updating ImportItem table"); var dao = new SqlDbHelper(); var sql = new StringBuilder(); sql.AppendFormat("SELECT ISNULL(MAX(Id), 0) FROM ImportItem WHERE ImportId = {0} AND ItemType = {1}", importId, itemTypeId); var importItemId = (int)dao.ExecuteScalar(sql.ToString()); if (importItemId == 0) { sql.Clear(); sql.AppendLine(string.Format("INSERT INTO ImportItem (ImportId, ItemType, FilePath) VALUES ({0}, {1}, '{2}')", importId, itemTypeId, sourceFilePath)); sql.AppendLine("SELECT SCOPE_IDENTITY()"); importItemId = (int)((decimal)dao.ExecuteScalar(sql.ToString())); logger.Debug("New record created. ImportItemId = " + importItemId.ToString()); } else { sql.Clear(); sql.AppendFormat("UPDATE ImportItem SET FilePath = '{0}', ModifyDate = getdate() WHERE Id = {1}", sourceFilePath, importItemId); dao.ExecuteNonQuery(sql.ToString()); logger.Debug("Existing record updated. ImportItemId = " + importItemId.ToString()); } return(true); }
private void cbSheets_SelectedIndexChanged(object sender, EventArgs e) { var eh = new ExcelHelper((a) => { MessageBox.Show(a, "Erro"); }); cbColHeader.DataSource = eh.GetSheetColumnNames(txtCsvFile.Text, cbSheets.SelectedItem.ToString()); }
//导出 protected void Exp_Click(object sender, EventArgs e) { string str1 = " WHERE 1=1"; //个人权限 if (hkdb.StrIx("603091", Session["roleqx"].ToString())) { str1 = str1 + " AND UserID='" + this.Session["userid"] + "'"; } //部门权限 if (hkdb.StrIx("603092", Session["roleqx"].ToString())) { if (this.Session["bmglqx"].ToString() == "Y") { string cal = ""; SqlDataReader dr_bm = SqlHelper.ExecuteReader("EXEC sp_RECUR_BM '" + this.Session["deptid"].ToString() + "'"); while (dr_bm.Read()) { if (string.IsNullOrEmpty(cal)) { cal = dr_bm["bm_id"].ToString(); } else { cal = cal + "','" + dr_bm["bm_id"].ToString(); } } dr_bm.Close(); str1 = str1 + " AND DeptID IN('" + cal + "')"; } else { str1 = str1 + " AND DeptID='" + this.Session["deptid"] + "'"; } } if (!string.IsNullOrEmpty(txtSdate.Text)) { str1 = str1 + " AND NowTime>='" + hkdb.GetStr(txtSdate.Text) + "'"; } if (!string.IsNullOrEmpty(txtEdate.Text)) { str1 = str1 + " AND NowTime<='" + hkdb.GetStr(txtEdate.Text) + "'"; } if (!string.IsNullOrEmpty(UserName.Text)) { str1 = str1 + " AND UserName LIKE '%" + hkdb.GetStr(UserName.Text) + "%'"; } if (!string.IsNullOrEmpty(IPAddress.Text)) { str1 = str1 + " AND IPAddress='" + hkdb.GetStr(IPAddress.Text) + "'"; } if (!string.IsNullOrEmpty(LogContent.Text)) { str1 = str1 + " AND LogContent LIKE '%" + hkdb.GetStr(LogContent.Text) + "%'"; } if (!string.IsNullOrEmpty(ModuName.Text)) { if (ModuName.Text == "00000") { str1 = str1 + " AND MODUID='00000'"; } else { str1 = str1 + " AND (MODUID LIKE '%" + hkdb.GetStr(ModuName.Text) + "%' OR MODU_MC LIKE '%" + hkdb.GetStr(ModuName.Text) + "%')"; } } else { str1 = str1 + " AND MODUID<>'00000'"; } DataSet ds = SqlHelper.ExecuteDs("SELECT A.NOWTIME AS '操作日期',A.USERNAME AS '操作人',A.IPADDRESS AS 'IP地址',B.MODU_MC AS '模块名称',A.LOGCONTENT AS '日志内容' FROM HK_SYSTEMLOG A LEFT OUTER JOIN HK_MODU B ON(A.MODUID=B.MODU_ID) " + str1 + " ORDER BY NOWTIME DESC"); string fileName = "czrzcx.xls"; ExcelHelper.ExportDataSetToExcel(ds, fileName, "操作日志查询"); }
public static string Create(OrderRecord order, List <OrderDetailRecord> details) { string appPath = AppDomain.CurrentDomain.BaseDirectory + "Temp/"; string sheetName = "采购单"; string fileName = appPath + DateTime.Now.ToString("yyyyMMddHHmmss_") + "采购单.xlsx"; order.Contract = order.Contract.Replace("<p>", "").Replace("</p>", "\n").Replace("<br>", "\n"); ExcelHelper excel = new ExcelHelper(); try { excel.Create(); var sheet = excel.AddSheet(sheetName); sheet.Rows.RowHeight = 22; sheet.Columns.ColumnWidth = 5; excel.UniteCells(sheet, 1, 1, 1, 16); excel.UniteCells(sheet, 2, 1, 2, 15); excel.UniteCells(sheet, 3, 1, 3, 19); excel.UniteCells(sheet, 5, 1, 5, 15); excel.UniteCells(sheet, 6, 1, 6, 3); excel.UniteCells(sheet, 6, 4, 6, 15); excel.UniteCells(sheet, 7, 1, 7, 3); excel.UniteCells(sheet, 7, 4, 7, 7); excel.UniteCells(sheet, 8, 1, 8, 3); excel.UniteCells(sheet, 8, 4, 8, 7); excel.UniteCells(sheet, 9, 1, 9, 3); excel.UniteCells(sheet, 9, 4, 9, 7); excel.UniteCells(sheet, 9, 8, 9, 15); excel.UniteCells(sheet, 10, 1, 10, 3); excel.UniteCells(sheet, 10, 4, 10, 7); excel.UniteCells(sheet, 11, 1, 11, 3); excel.UniteCells(sheet, 11, 4, 11, 7); excel.UniteCells(sheet, 11, 8, 11, 10); excel.UniteCells(sheet, 11, 11, 11, 14); excel.SetCellProperty(sheet, 1, 1, 1, 16, 20, "宋体", true, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlCenter); excel.SetCellProperty(sheet, 2, 1, 2, 15, 12, "宋体", true, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlCenter); excel.SetCellProperty(sheet, 3, 1, 3, 19, 9, "宋体", true, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlCenter); excel.SetCellProperty(sheet, 5, 1, 5, 15, 18, "宋体", true, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlCenter); excel.SetCellProperty(sheet, 6, 1, 11, 3, 16, "宋体", true, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlRight); excel.SetCellProperty(sheet, 6, 4, 11, 7, 16, "宋体", true, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlLeft); excel.SetCellProperty(sheet, 9, 8, 9, 15, 16, "宋体", true, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlCenter); excel.SetCellProperty(sheet, 11, 8, 11, 10, 16, "宋体", true, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlRight); excel.SetCellProperty(sheet, 11, 11, 11, 14, 16, "宋体", true, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlLeft); excel.SetCellValue(sheet, 1, 1, "东莞市黄江红兴木业制品有限公司"); excel.SetCellValue(sheet, 2, 1, "Dongguan Huang Jiang Hongxing Wood Products Co., Ltd."); excel.SetCellValue(sheet, 3, 1, "广东省东莞市黄江星光福星路1号 TEL:0769-89392278 FAX:0769:87006238 E-MAIL:[email protected]"); excel.SetCellValue(sheet, 5, 1, "采购单"); excel.SetCellValue(sheet, 6, 1, "供 应 商:"); excel.SetCellValue(sheet, 6, 4, order.Supplier); excel.SetCellValue(sheet, 7, 1, "联 系 人:"); excel.SetCellValue(sheet, 7, 4, order.LinkPerson); excel.SetCellValue(sheet, 8, 1, "联系电话:"); excel.SetCellValue(sheet, 8, 4, order.Phone); excel.SetCellValue(sheet, 9, 1, "电 话:"); excel.SetCellValue(sheet, 9, 4, order.Tel); excel.SetCellValue(sheet, 9, 8, order.OrderNo); excel.SetCellValue(sheet, 10, 1, "传 真:"); excel.SetCellValue(sheet, 10, 4, order.Fax); excel.SetCellValue(sheet, 11, 1, "下单日期:"); excel.SetCellValue(sheet, 11, 4, order.OrderDate.ToString("yyyy-MM-dd")); excel.SetCellValue(sheet, 11, 8, "交货日期:"); excel.SetCellValue(sheet, 11, 11, order.DeliveryDate.ToString("yyyy-MM-dd")); int row = 13; int table_rows = details.Count; int table_cols = 15; excel.SetCellProperty_Table(sheet, row, 1, row + table_rows, table_cols); excel.SetCellProperty(sheet, row, 1, row, table_cols, 12, "宋体", true, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlCenter); excel.SetCellProperty(sheet, row + 1, 1, row + table_rows, table_cols, 12, "宋体", false, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlCenter); excel.SetCellProperty_WrapText(sheet, row, 1, row + table_rows, table_cols, true); excel.UniteCells(sheet, row, 2, row, 6); excel.UniteCells(sheet, row, 8, row, 9); excel.UniteCells(sheet, row, 10, row, 15); excel.SetCellValue(sheet, row, 1, "序号"); excel.SetCellValue(sheet, row, 2, "品名规格"); excel.SetCellValue(sheet, row, 7, "单位"); excel.SetCellValue(sheet, row, 8, "数量"); excel.SetCellValue(sheet, row, 10, "备注"); foreach (var det in details) { row++; excel.UniteCells(sheet, row, 2, row, 6); excel.UniteCells(sheet, row, 8, row, 9); excel.UniteCells(sheet, row, 10, row, 15); excel.SetCellValue(sheet, row, 1, det.SortIndex); excel.SetCellValue(sheet, row, 2, det.Name + det.Size); excel.SetCellValue(sheet, row, 7, det.Unit); excel.SetCellValue(sheet, row, 8, det.Total); excel.SetCellValue(sheet, row, 10, det.Remark); } row = row + 2; int contract_rows = 7; int contract_cols = 15; excel.UniteCells(sheet, row, 1, row + contract_rows, contract_cols); excel.SetCellProperty(sheet, row, 2, row + contract_rows, contract_cols, 12, "宋体", false, Microsoft.Office.Interop.Excel.Constants.xlTop, Microsoft.Office.Interop.Excel.Constants.xlLeft); excel.SetCellProperty_WrapText(sheet, row, 2, row + contract_rows, contract_cols, true); excel.SetCellValue(sheet, row, 1, order.Contract); row = row + contract_rows + 2; excel.UniteCells(sheet, row, 3, row, 4); excel.SetCellValue(sheet, row, 3, "确认签字:"); excel.SetCellProperty(sheet, row, 3, row, 4, 12, "宋体", false, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlRight); excel.UniteCells(sheet, row, 5, row, 6); excel.SetCellProperty_Border_Bottom(sheet, row, 5, row, 6); excel.SetCellProperty(sheet, row, 5, row, 6, 12, "宋体", false, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlLeft); excel.UniteCells(sheet, row, 12, row, 13); excel.SetCellValue(sheet, row, 12, "制表:"); excel.SetCellProperty(sheet, row, 12, row, 13, 12, "宋体", false, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlRight); excel.UniteCells(sheet, row, 14, row, 15); excel.SetCellProperty_Border_Bottom(sheet, row, 14, row, 15); excel.SetCellProperty(sheet, row, 14, row, 15, 12, "宋体", false, Microsoft.Office.Interop.Excel.Constants.xlCenter, Microsoft.Office.Interop.Excel.Constants.xlLeft); excel.SaveAs(fileName); } catch (Exception e) { throw e; } finally { excel.Close(); } return(fileName); }
public static void Main(string[] args) { //var conn = new SqlConnection("server=.;uid=liweihan;pwd=Admin888;database=AccountingApp"); //var entityList = conn.Select<TestEntity>("select * from Users").ToArray(); //entityList[0].Amount = 0; //entityList[0].PasswordHash = ""; ////var dataTable = entityList.ToDataTable(); //var result = entityList.ToExcelFile(ApplicationHelper.MapPath("test.xlsx")); //var result1 = ExcelHelper.ToEntityList<TestEntity>(ApplicationHelper.MapPath("test.xlsx")); //// 找不到文件 ////var aaa = ExcelHelper.ToEntityList<TestEntity>(""); //var entityList1 = conn.Select<TestEntity2>("select * from Bills"); //var result2 = entityList1.ToExcelFile(ConfigurationHelper.MapPath("test1.xls")); //entityList1 = ExcelHelper.ToEntityList<TestEntity2>(ConfigurationHelper.MapPath("test1.xls")); //var entityList2 = ExcelHelper.ToEntityList<Model>(FilePath).Where(_ => !string.IsNullOrWhiteSpace(_.HotelId)).ToArray(); //if (entityList2.Length > 0) //{ // var dir = Path.GetDirectoryName(FilePath); // foreach (var group in entityList2.GroupBy(e => new // { // HotelId = e.HotelId.Trim(), // HotelName = e.HotelName.Trim() // })) // { // var path = $"{dir}\\sub\\{group.Key.HotelName}-1月对账单.xlsx"; // group.ToArray().ToExcelFile(path); // } // Console.WriteLine("Success"); //} //var table = ExcelHelper.ToDataTable(FilePath); ////Console.WriteLine(table.Rows.Count); ////Console.WriteLine(table.ToExcelFile(FilePath.Replace("AllStores", "AllStores1"))); //using (var connection = new SqlConnection("server=.;uid=liweihan;pwd=Admin888;database=TestDb")) //{ // Console.WriteLine($"导入结果:{connection.BulkCopy(table, "testBulkCopy")}"); //} //var setting = ExcelHelper.SettingFor<TestEntity>(); //// ExcelSetting //setting.HasAuthor("WeihanLi") // .HasTitle("WeihanLi.Npoi test") // .HasDescription("") // .HasSubject(""); //setting.HasFilter(0, 1) // .HasFreezePane(0, 1, 2, 1); //setting.Property(_ => _.Amount) // .HasColumnTitle("Amount") // .HasColumnIndex(2); //setting.Property(_ => _.Username) // .HasColumnTitle("Username") // .HasColumnIndex(0); //setting.Property(_ => _.CreateTime) // .HasColumnTitle("CreateTime") // .HasColumnFormatter("yyyy-MM-dd HH:mm:ss"); //setting.Property(_ => _.PasswordHash) // .Ignored(); //var entities = ExcelHelper.ToEntityList<TestEntity>(ApplicationHelper.MapPath("test.xlsx")); //Console.WriteLine(entities.Count); ////entities = conn.Select<TestEntity>("select * from Users").ToList(); //entities.ToExcelFile(ApplicationHelper.MapPath("test_1.xlsx")); //Console.WriteLine("Success"); //Console.WriteLine($"WorkingSet size: {Process.GetCurrentProcess().WorkingSet64 / 1024} kb"); //// ExportExcelViaEpplusPerfTest(); //// ExportExcelViaEpplusPerfTest(1_000_000, 5); //// ExportExcelPerfTest(100_000, 10); //// ExportExcelPerfTest(1_000_000, 5); //ExportCsvPerfTest(100_000, 10); //// ExportCsvPerfTest(1_000_000, 5); //Console.WriteLine($"WorkingSet size: {Process.GetCurrentProcess().WorkingSet64 / 1024} kb"); //GC.Collect(2, GCCollectionMode.Forced); //Console.WriteLine($"WorkingSet size: {Process.GetCurrentProcess().WorkingSet64 / 1024} kb"); var testData = new List <TestEntity>(10); for (int i = 1; i <= 10; i++) { testData.Add(new TestEntity() { Amount = 1000, Username = "******", CreateTime = DateTime.UtcNow.AddDays(-3), PKID = i, PasswordHash = SecurityHelper.SHA1($"_x_{i}") }); } var excelFilePath = $@"{Environment.GetEnvironmentVariable("USERPROFILE")}\Desktop\temp\test\test123.fx.xlsx"; testData.ToExcelFile(excelFilePath); testData.ToCsvFile(excelFilePath.Replace(".xlsx", ".csv")); var list = ExcelHelper.ToEntityList <TestEntity>(excelFilePath); Console.WriteLine("complete"); Console.ReadLine(); }
protected void Button_Excel(object sender, EventArgs e) { ExcelHelper.GridViewToExcel(Gridview_OAInfo, "回款明细表"); }
protected void btnExport_Click(object sender, EventArgs e) { if (Session["IndividualData"] != null) { var individualSource = (IEnumerable <dynamic>)Session["IndividualData"]; // create default settings string workSheetName = "Export"; string title = "RockExport"; ExcelPackage excel = new ExcelPackage(); excel.Workbook.Properties.Title = title; // add author info Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); if (userLogin != null) { excel.Workbook.Properties.Author = userLogin.Person.FullName; } else { excel.Workbook.Properties.Author = "Rock"; } // add the page that created this excel.Workbook.Properties.SetCustomPropertyValue("Source", HttpContext.Current.Request.Url.OriginalString); ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add(workSheetName); var headerRows = 1; int rowCounter = headerRows; int columnCounter = 1; worksheet.Cells[rowCounter, columnCounter].Value = "NickName"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.GeneralFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "LastName"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.GeneralFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "IndividualGoal"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.CurrencyFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "TotalRaised"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.CurrencyFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "PercentageRaised"; worksheet.Column(columnCounter).Style.Numberformat.Format = "0%"; // print data if (individualSource.Any()) { foreach (var individual in individualSource) { rowCounter++; var columnIndex = 1; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], individual.NickName); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, individual.NickName); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], individual.LastName); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, individual.LastName); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], decimal.Parse(individual.IndividualFundraisingGoal)); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, decimal.Parse(individual.IndividualFundraisingGoal)); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], decimal.Parse(individual.ContributionTotal)); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, decimal.Parse(individual.ContributionTotal)); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], decimal.Parse(individual.Percentage) / 100); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, decimal.Parse(individual.Percentage)); } } else { rowCounter++; var columnIndex = 1; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], string.Empty); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, string.Empty); } var range = worksheet.Cells[headerRows, 1, rowCounter, columnCounter]; var table = worksheet.Tables.Add(range, title); table.ShowHeader = true; table.ShowFilter = true; table.TableStyle = OfficeOpenXml.Table.TableStyles.None; // Format header range using (ExcelRange r = worksheet.Cells[headerRows, 1, headerRows, columnCounter]) { r.Style.Font.Bold = true; r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; } // do AutoFitColumns on no more than the first 10000 rows (10000 can take 4-5 seconds, but could take several minutes if there are 100000+ rows ) int autoFitRows = Math.Min(rowCounter, 10000); var autoFitRange = worksheet.Cells[headerRows, 1, autoFitRows, columnCounter]; autoFitRange.AutoFitColumns(); // set some footer text worksheet.HeaderFooter.OddHeader.CenteredText = title; worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); var filename = string.Format("FinancialProgress_{0}.xlsx", Regex.Replace(individualSource.FirstOrDefault().GroupName, "[^A-Za-z0-9_\\- ]", string.Empty, RegexOptions.CultureInvariant)); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename); Response.Charset = string.Empty; Response.BinaryWrite(excel.ToByteArray()); Response.Flush(); Response.End(); int? groupId = this.PageParameter("GroupId").AsIntegerOrNull(); int? groupMemberId = this.PageParameter("GroupMemberId").AsIntegerOrNull(); Guid?groupGuid = GetAttributeValue("ShowSpecificGroup").AsGuidOrNull(); if (groupId.HasValue || groupMemberId.HasValue || groupGuid.HasValue) { ShowView(groupId, groupMemberId, groupGuid); } } }
private void FillData() { var excelHelper = new ExcelHelper(reportConfig.TempPath); var kpiSheetConfigs = reportConfig.GetAllKPISheetConfigs(); foreach (var sheetConfig in kpiSheetConfigs) { if (sheetConfig.KPISheetIndex == 0) { excelHelper.SetCellValue(sheetConfig.Name, "A2", string.Format("{0} China APPS KPIs Actual", reportConfig.ReportYear)); excelHelper.SetCellValue(sheetConfig.Name, "A3", string.Format("{0} YTD", CultureInfo.GetCultureInfo("en-us").DateTimeFormat.GetMonthName(reportConfig.ReportMonth))); var cells = new List<string>() { "B5", "I5", "P5", "W5", "AD5", "B17", "I17", "B28", "I28" }; cells.ForEach(c => { excelHelper.SetCellValue(sheetConfig.Name, c, string.Format("{0} {1} YTD", reportConfig.ReportYear, CultureInfo.GetCultureInfo("en-us").DateTimeFormat.GetAbbreviatedMonthName(reportConfig.ReportMonth))); }); cells = new List<string>() { "E5", "L5", "S5", "Z5", "AG5", "E17", "L17", "E28", "L28" }; cells.ForEach(c => { excelHelper.SetCellValue(sheetConfig.Name, c, string.Format("{0} Full Year", reportConfig.ReportYear)); }); } else { excelHelper.SetCellValue(sheetConfig.Name, "C3", string.Format("{0}-{1}", CultureInfo.GetCultureInfo("en-us").DateTimeFormat.GetMonthName(reportConfig.ReportMonth), reportConfig.ReportYear)); } if (sheetConfig.Independency) { var lastMonthKPIData = KPIReportData.LastMonthKPIDatas.Tables[sheetConfig.TableName]; var currentMonthKPIData = KPIReportData.CurrentMonthKPIDatas.Tables[sheetConfig.TableName]; var lastMonthFormulaData = (KPIReportData.LastMonthKPIDatas.Tables.Contains(string.Concat(sheetConfig.TableName, "Formulas"))) ? KPIReportData.LastMonthKPIDatas.Tables[string.Concat(sheetConfig.TableName, "Formulas")] : null; excelHelper.SetKPIData(sheetConfig, reportConfig.ReportMonth, lastMonthKPIData, currentMonthKPIData, lastMonthFormulaData); } else if (!sheetConfig.Independency && sheetConfig.DataColumns.Count > 0) { var lastMonthKPIData = KPIReportData.LastMonthKPIDatas.Tables[sheetConfig.TableName]; var lastMonthFormulaData = (KPIReportData.LastMonthKPIDatas.Tables.Contains(string.Concat(sheetConfig.TableName, "Formulas"))) ? KPIReportData.LastMonthKPIDatas.Tables[string.Concat(sheetConfig.TableName, "Formulas")] : null; excelHelper.SetLastMonthKPIRefData(sheetConfig, reportConfig.ReportMonth, lastMonthKPIData, lastMonthFormulaData); } } var rawSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(c => c.SetStartRow.HasValue); foreach (var sheetConfig in rawSheetConfigs) { var rawDataTable = KPIReportData.RawDatas.Tables[sheetConfig.TableName]; var rawFormulaTable = (KPIReportData.RawDatas.Tables.Contains(string.Concat(sheetConfig.TableName, "Formulas"))) ? KPIReportData.RawDatas.Tables[string.Concat(sheetConfig.TableName, "Formulas")] : null; var rawTitleTable = KPIReportData.RawTitleDatas.Tables[sheetConfig.TableName]; excelHelper.SetRawData(sheetConfig, rawDataTable, rawFormulaTable, rawTitleTable, reportConfig.ReportYear, reportConfig.ReportMonth); } excelHelper.SaveAs(reportConfig.OutputPath); }
//public List<UserInfoEntity> GetUserData(string code, string account, string name, string tel, int page, int pagesize, out int total) //{ // return service.GetUserData(code, account, name, tel, page, pagesize, out total); //} #endregion #region 处理数据 /// <summary> /// 导出用户列表 /// </summary> /// <returns></returns> public void GetExportList(string condition, string keyword, string code, string isOrg) { //取出数据源 DataTable exportTable = service.GetExportList(condition, keyword, code, isOrg); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); //excelconfig.Title = "测试用户导出"; //excelconfig.TitleFont = "微软雅黑"; //excelconfig.TitlePoint = 25; excelconfig.HeadHeight = 50; excelconfig.HeadPoint = 12; excelconfig.HeadFont = "宋体"; excelconfig.FileName = "用户导出.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "account", ExcelColumn = "账户" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname", ExcelColumn = "姓名" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "gender", ExcelColumn = "性别" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "birthday", ExcelColumn = "生日" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "mobile", ExcelColumn = "手机" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "telephone", ExcelColumn = "电话", Background = Color.Red }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "wechat", ExcelColumn = "微信" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "manager", ExcelColumn = "主管" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "organize", ExcelColumn = "公司" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "department", ExcelColumn = "部门" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "description", ExcelColumn = "说明" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "createdate", ExcelColumn = "创建日期" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "createusername", ExcelColumn = "创建人" }); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); //从泛型Lis导出 //TExcelHelper<DepartmentEntity>.ExcelDownload(department.GetList().ToList(), excelconfig); }
public void ShowDialogFromPath(string path, string sheetName) { ExcelHelper helper = new ExcelHelper( path); DataTable table = helper.ReadXLS(sheetName); ShowDialog(table,sheetName); }
static void Main(string[] args) { Console.Write("enter file path:"); string filePath = Console.ReadLine(); Console.WriteLine(); ExcelHelper eh = new ExcelHelper(filePath); Random rowRan = new Random(eh.FirstRowNum); Random columnRan = new Random(eh.FirstColumnNum); Console.Write("enter run times: "); do { int count = int.Parse(Console.ReadLine()); // 1 // DateTime start = DateTime.Now; for (int i = 0; i < count; i++) { //string value = eh.GetValue(rowRan.Next(eh.LastRowNum), columnRan.Next(eh.LastColumnNum)); } Console.WriteLine($"get value from workbook\t\t for {count} times : {(DateTime.Now - start).TotalSeconds}"); //start = DateTime.Now; //for (int i = eh.FirstRowNum; i < eh.LastRowNum; i++) //{ // for (int j = eh.FirstColumnNum; j < eh.LastColumnNum; j++) // { // eh.Update(rowRan.Next(), i, j); // } //} //Console.WriteLine($"update value :\t {(DateTime.Now - start).TotalSeconds}"); // 4 // start = DateTime.Now; var dic = eh.ToDictionary(); Console.WriteLine($"init value to dictionary :\t {(DateTime.Now - start).TotalSeconds}"); start = DateTime.Now; for (int i = 0; i < count; i++) { string dicValue = dic[rowRan.Next(eh.LastRowNum)][columnRan.Next(eh.LastColumnNum)]; } Console.WriteLine($"get value from dictionary\t for {count} times : {(DateTime.Now - start).TotalSeconds}"); start = DateTime.Now; eh.Update(dic); Console.WriteLine($"update value from dictionary :\t\t {(DateTime.Now - start).TotalSeconds}"); // 2 // start = DateTime.Now; var arr = eh.ToArray(); Console.WriteLine($"init value to array :\t\t {(DateTime.Now - start).TotalSeconds}"); start = DateTime.Now; for (int i = 0; i < count; i++) { string arrValue = arr[rowRan.Next(eh.LastRowNum) - eh.FirstRowNum][columnRan.Next(eh.LastColumnNum) - eh.FirstColumnNum]; } Console.WriteLine($"get value from array\t\t for {count} times : {(DateTime.Now - start).TotalSeconds}"); start = DateTime.Now; eh.Update(arr); Console.WriteLine($"update value from array :\t\t {(DateTime.Now - start).TotalSeconds}"); // 3 // start = DateTime.Now; var dt = eh.ToDataTable(); Console.WriteLine($"init value to datatable :\t {(DateTime.Now - start).TotalSeconds}"); start = DateTime.Now; for (int i = 0; i < count; i++) { string dtValue = dt.Rows[rowRan.Next(eh.LastRowNum)][columnRan.Next(eh.LastColumnNum)].ToString(); } Console.WriteLine($"get value from datatable\t for {count} times : {(DateTime.Now - start).TotalSeconds}"); start = DateTime.Now; eh.Update(dt); Console.WriteLine($"update value from datatable :\t\t {(DateTime.Now - start).TotalSeconds}"); // 5 // start = DateTime.Now; eh.Save("D:\\1.xlsx", true); Console.WriteLine($"save to disk :\t {(DateTime.Now - start).TotalSeconds}"); Console.WriteLine("\r\n"); Console.Write("enter run times: "); } while (true); }
//从本地文件中获取用户 async Task<bool> LoadUsers() { var msg = "获取本地文件并解析..."; loadingTextControl.Text = msg; LogHelper.WriteLog(msg); return await Task.Factory.StartNew(() => { try { var excelHelper = new ExcelHelper(System.AppDomain.CurrentDomain.BaseDirectory + "users.xlsx"); Users = excelHelper.ExcelToUsers("Sheet1", false); this.Dispatcher.Invoke(() => { dataGridControl.ItemsSource = Users; if (Users.Count-1 == CurrentIndex) { dataGridControl.IsEnabled = false; textControl.Text = "执行完成!"; return true; } else { dataGridControl.SelectedIndex = CurrentIndex; indexControl.Text = CurrentIndex + 1 + ""; } return false; }); } catch (Exception ex) { msg += "遇到错误"; LogHelper.WriteLog(msg, ex); } return false; }); }
public void Read() { #region Read Raw Data KPIReportData.RawDatas = new DataSet(); KPIReportData.RawTitleDatas = new DataSet(); Action<ExcelHelper, IEnumerable<RawSheetConfig>> ReadRawSheetDatas = ((e, sheetConfigs) => { foreach (var sheetConfig in sheetConfigs) { FinaChanLogger.Info("Load raw data of {0} worksheet start", sheetConfig.Name); DataTable rawDataTable = new DataTable(sheetConfig.TableName); //FinaChanCore.FillTableSchemaByColumns(sheetConfig.DataColumns, rawDataTable); rawDataTable.FillSchemaByColumns(sheetConfig.DataColumns); DataTable rawFormulaTable = null; var containFormulaColumns = sheetConfig.DataColumns.Where(c => c.ContainFormula); if (containFormulaColumns.Count() > 0) { rawFormulaTable = new DataTable(string.Concat(sheetConfig.TableName, "Formulas")); foreach (var column in containFormulaColumns) { rawFormulaTable.Columns.Add(column.Name); } } DataTable rawTitleDataTable = null; if (sheetConfig.TitleDataColumns != null && sheetConfig.TitleDataColumns.Count > 0) { rawTitleDataTable = new DataTable(sheetConfig.TableName); rawTitleDataTable.FillSchemaByColumns(sheetConfig.TitleDataColumns); //FinaChanCore.FillTableSchemaByColumns(sheetConfig.TitleDataColumns, rawTitleDataTable); } AsmHelper script = null; if (!string.IsNullOrEmpty(sheetConfig.FilterScript) && !string.IsNullOrEmpty(sheetConfig.FilterMethod)) { script = reportConfig.GetDynamicScript(sheetConfig.FilterScript); } e.GetRawSheetData(sheetConfig, rawDataTable, rawFormulaTable, rawTitleDataTable, script); KPIReportData.RawDatas.Tables.Add(rawDataTable); if (rawFormulaTable != null) { KPIReportData.RawDatas.Tables.Add(rawFormulaTable); } if (rawTitleDataTable != null) { KPIReportData.RawTitleDatas.Tables.Add(rawTitleDataTable); } FinaChanLogger.Info("Load raw data of {0} worksheet end", sheetConfig.Name); } }); #region Read Booking & CashCollections Raw Data var excelHelper = new ExcelHelper(reportConfig.BookingCashWorkbookFile); var bookingcashSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.BookingCashReport); ReadRawSheetDatas(excelHelper, bookingcashSheetConfigs); #endregion #region Read Project Contribution(MTD) Raw Data excelHelper = new ExcelHelper(reportConfig.ProjectContributionMTDWorkbookFile); var projectContributionMTDSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.ProjectContributionMTDReport); ReadRawSheetDatas(excelHelper, projectContributionMTDSheetConfigs); #endregion #region Read Project Contribution(TYD) Raw Data excelHelper = new ExcelHelper(reportConfig.ProjectContributioYTDWorkbookFile); var projectContributionYTDSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.ProjectContributionYTDReport); ReadRawSheetDatas(excelHelper, projectContributionYTDSheetConfigs); #endregion #region Read BD Raw Data excelHelper = new ExcelHelper(reportConfig.BDWorkbookFile); var bdSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.BDReport); ReadRawSheetDatas(excelHelper, bdSheetConfigs); #endregion #region Read Sector IDC (YTD)/SL IDC(YTD) Raw Data excelHelper = new ExcelHelper(reportConfig.IDCProjectWorkbookFile); var projectIDCSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.ProjectIDCReport); ReadRawSheetDatas(excelHelper, projectIDCSheetConfigs); #endregion #region Read Oracle IDC by employee (YTD) Raw Data excelHelper = new ExcelHelper(reportConfig.IDCEmployeeWorkbookFile); var employeeIDCSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.EmployeeIDCReport); ReadRawSheetDatas(excelHelper, employeeIDCSheetConfigs); #endregion #region Read Employee Activity Raw Data excelHelper = new ExcelHelper(reportConfig.EmployeeWorkbookFile); var employeeSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.EmployeeReport); ReadRawSheetDatas(excelHelper, employeeSheetConfigs); #endregion #endregion #region Read Last Months KPI Data KPIReportData.LastMonthKPIDatas = new DataSet(); if (!string.IsNullOrEmpty(reportConfig.LastMonthWorkbookFile)) { excelHelper = new ExcelHelper(reportConfig.LastMonthWorkbookFile); var sectorSheetConfigs = reportConfig.GetAllKPISheetConfigs() .Where(c => c.ReportCatalog == ReportCatalog.Sector); var slSheetConfigs = reportConfig.GetAllKPISheetConfigs() .Where(c => c.ReportCatalog == ReportCatalog.SL); foreach (var sheetConfig in sectorSheetConfigs) { FinaChanLogger.Info("Load lastmonth report data of {0} worksheet start", sheetConfig.Name); if (sheetConfig != null && sheetConfig.DataColumns.Count > 0) { DataTable sectorTable = new DataTable(sheetConfig.TableName); sectorTable.FillSchemaByColumns(sheetConfig.DataColumns); // FinaChanCore.FillTableSchemaByColumns(sheetConfig.DataColumns, sectorTable); DataTable sectorFormulaTable = null; var containFormulaColumns = sheetConfig.DataColumns.Where(c => c.ContainFormula); if (containFormulaColumns.Count() > 0) { sectorFormulaTable = new DataTable(string.Concat(sheetConfig.TableName, "Formulas")); foreach (var column in containFormulaColumns) { sectorFormulaTable.Columns.Add(column.Name, typeof(string)); } } excelHelper.GetLastSectorKPIData(sheetConfig, reportConfig.ReportYear, reportConfig.ReportMonth, sectorTable, sectorFormulaTable); KPIReportData.LastMonthKPIDatas.Tables.Add(sectorTable); if (sectorFormulaTable != null) KPIReportData.LastMonthKPIDatas.Tables.Add(sectorFormulaTable); } FinaChanLogger.Info("Load lastmonth report data of {0} worksheet end", sheetConfig.Name); } foreach (var sheetConfig in slSheetConfigs) { FinaChanLogger.Info("Load lastmonth report data of {0} worksheet start", sheetConfig.Name); if (sheetConfig != null && sheetConfig.DataColumns.Count > 0) { DataTable slTable = new DataTable(sheetConfig.TableName); slTable.FillSchemaByColumns(sheetConfig.DataColumns); //FinaChanCore.FillTableSchemaByColumns(sheetConfig.DataColumns, slTable); DataTable slFormulaTable = null; var containFormulaColumns = sheetConfig.DataColumns.Where(c => c.ContainFormula); if (containFormulaColumns.Count() > 0) { slFormulaTable = new DataTable(string.Concat(sheetConfig.TableName, "Formulas")); foreach (var column in containFormulaColumns) { slFormulaTable.Columns.Add(column.Name, typeof(string)); } } excelHelper.GetLastServiceLineKPIData(sheetConfig, reportConfig.ReportYear, reportConfig.ReportMonth, slTable, slFormulaTable); KPIReportData.LastMonthKPIDatas.Tables.Add(slTable); if (slFormulaTable != null) KPIReportData.LastMonthKPIDatas.Tables.Add(slFormulaTable); } FinaChanLogger.Info("Load lastmonth report data of {0} worksheet end", sheetConfig.Name); } } #endregion }
/// <summary> /// CSV value of the cell /// </summary> public string CSVValue(bool useFormat, string separator) { string result = ExcelHelper.ToCsv(useFormat ? DisplayValue : RawDisplayValue, separator); return(result); }
public ActionResult ExportData(string queryJson) { try { Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 100000; pagination.p_kid = "id"; pagination.p_fields = "checktitle,checktype,to_char(startdate,'yyyy-mm-dd') startdate,to_char(enddate,'yyyy-mm-dd') enddate,0 total,0 count1,0 count2"; pagination.p_tablename = @"jt_safetycheck t"; pagination.conditionJson = "1=1"; var user = OperatorProvider.Provider.Current(); if (!user.IsSystem) { pagination.conditionJson = string.Format("deptcode like '{0}%'", user.OrganizeCode); } var watch = CommonHelper.TimerStart(); var data = saftycheckdatabll.GetPageList(pagination, queryJson); DepartmentBLL deptBll = new DepartmentBLL(); string typeName = ""; foreach (DataRow dr in data.Rows) { string id = dr["id"].ToString(); string sql = string.Format("select count(1) from JT_CHECKITEMS where checkid='{0}'", id); int total = deptBll.GetDataTable(sql).Rows[0][0].ToInt(); dr["total"] = total; string sql1 = sql + string.Format(" and result='已完成'"); int count = deptBll.GetDataTable(sql1).Rows[0][0].ToInt(); dr["count1"] = count; count = 0; sql1 = string.Format("select realitydate,plandate from JT_CHECKITEMS where checkid='{0}' and result='未完成' and plandate is not null", id); DataTable dtCount = deptBll.GetDataTable(sql1); if (dtCount.Rows.Count > 0) { count = dtCount.AsEnumerable().Where(t => t.Field <DateTime>("plandate") < DateTime.Now.ToString("yyyy-MM-dd 00:00:00").ToDate()).Count(); dr["count2"] = count; } dr["count2"] = count; string type = dr["checktype"].ToString(); if (type == "1") { typeName = "日常安全检查"; } if (type == "2") { typeName = "专项安全检查"; } if (type == "3") { typeName = "节假日前后安全检查"; } if (type == "4") { typeName = "季节性安全检查"; } if (type == "5") { typeName = "综合安全检查"; } if (type == "6") { typeName = "其他安全检查"; } dr["checktype"] = typeName; } data.Columns.Remove("id"); List <ColumnEntity> list = new List <ColumnEntity>(); list.Add(new ColumnEntity { Column = "checktitle", ExcelColumn = "检查名称", }); list.Add(new ColumnEntity { Column = "checktype", ExcelColumn = "检查类型", }); list.Add(new ColumnEntity { Column = "startdate", ExcelColumn = "检查开始时间", }); list.Add(new ColumnEntity { Column = "enddate", ExcelColumn = "检查结束时间", }); list.Add(new ColumnEntity { Column = "total", ExcelColumn = "检查项数", }); list.Add(new ColumnEntity { Column = "count1", ExcelColumn = "整改项数", }); list.Add(new ColumnEntity { Column = "count2", ExcelColumn = "超期未整改项数", }); ExcelHelper.ExportByAspose(data, "安全检查记录", list); return(Success("操作成功")); } catch (Exception ex) { return(Error(ex.Message)); } }
public void ReadToTableTest() { ExcelHelper eh = new ExcelHelper(); DataTable dt = eh.ReadToTable(@"C:\Users\Carlisle-PC\Desktop\webuploader-0.1.5\assessInfo.xls", "AssessEntity"); var list = dt.ToList<AssessImportModels>(); }
private void btnExport_Click(object sender, EventArgs e) { ExcelHelper.ExportExcel(myGridViewBinding1); }
public HttpResponseMessage GenerateIncidentExcel() { var queryStrings = Request.GetQueryNameValuePairs().ToDictionary(x => x.Key, x => x.Value); string timeZoneDisplayString; TimeZoneInfo timeZoneRequest; DateTimeOffset startTime; DateTimeOffset endTime; string responseText; responseText = "Excel Output Requested -"; if (queryStrings.ContainsKey("timezone")) { try { timeZoneRequest = TimeZoneInfo.FindSystemTimeZoneById(queryStrings["timezone"]); } catch (TimeZoneNotFoundException) { Services.Log.Warn("Unable to retrieve the requested Time Zone. Reverting to UTC."); timeZoneRequest = TimeZoneInfo.Utc; } catch (InvalidTimeZoneException) { Services.Log.Warn("Unable to retrieve the requested Time Zone. Reverting to UTC."); timeZoneRequest = TimeZoneInfo.Utc; } } else { Services.Log.Warn("No Time Zone Requested. Reverting to UTC."); timeZoneRequest = TimeZoneInfo.Utc; } if (queryStrings.ContainsKey("starttime")) { if (!DateTimeOffset.TryParse(queryStrings["starttime"], out startTime)) { Services.Log.Warn("Unable to parse the requested Start Time [" + queryStrings["starttime"] + "]. Reverting to Min Value."); } } else { startTime = DateTimeOffset.MinValue; Services.Log.Warn("No Start Time Requested. Reverting to Min Value."); } if (queryStrings.ContainsKey("endtime")) { if (!DateTimeOffset.TryParse(queryStrings["endtime"], out endTime)) { endTime = DateTimeOffset.MaxValue; Services.Log.Warn("Unable to parse the requested End Time [" + queryStrings["endtime"] + "]. Reverting to Max Value."); } } else { endTime = DateTimeOffset.MaxValue; Services.Log.Warn("No End Time Requested. Reverting to Max Value."); } timeZoneDisplayString = "[" + timeZoneRequest.DisplayName.ToString() + "]"; responseText += " TimeZone " + timeZoneDisplayString; responseText += " StartTime [" + startTime.ToString() + "]"; responseText += " EndTime [" + endTime.ToString() + "]"; responseText += " [API]"; Services.Log.Info(responseText); List<Incident> dbIncidentCollection = new List<Incident>(); List<IncidentExcelData> fullIncidentCollection = new List<IncidentExcelData>(); stranddContext context = new stranddContext(); // List<Incident> objincident = context.Incidents.ToList(); //Loading List of Incidents from DB Context dbIncidentCollection = context.Incidents .Where(a => a.CreatedAt >= startTime) .Where(a => a.CreatedAt <= endTime) .ToList(); foreach (Incident dbIncident in dbIncidentCollection) { fullIncidentCollection.Add(new IncidentExcelData(dbIncident)); } string strData = string.Empty; strData = "IncidentGUID,TimeStamp,IncidentStatus,ArrivalTime,ConcertoCaseID,JobCode,ConfirmedAdminName,CustomerName,CustomerPhone,VehicleRegistration,VehicleDescription,StaffNotes,ServiceFee,CustomerComments,CustomerRating,PaymentStatus,PaymentAmount,PaymentPlatform"; DataTable table = ExcelHelper.ConvertListToDataTable(fullIncidentCollection); ExcelHelper objexcel = new ExcelHelper(); return objexcel.GetExcel(table, strData, "IncidentHistoryReport"); }
protected void btnExcel_Click(object sender, System.EventArgs e) { DataTable dt1 = this.treasuryStockBll.GetStuffInfo(this.txtCode.Text.Trim(), this.txtName.Text.Trim(), this.hfldPrjId.Value.Trim(), 0, 0, this.hfldIsWBSRelevance.Value, this.txtSpecification.Text.Trim(), this.txtBrand.Text.Trim(), this.txtModelNumber.Text.Trim()); string strSql = string.Format(@" with t1 as( SELECT t.TaskId, t.TaskName,t.ResourceId,t.ResourceCode scode,t.ResourceQuantity,w.numberIng--,sws.number--sum(sws.number) numberIng-- ,sws.wpcode FROM (SELECT PrjGuid,TaskId,ResourceId,TaskName,ResourceCode,sum(ResourceQuantity) ResourceQuantity FROM ( SELECT btr.PrjGuid,btr.TaskId,btr.ResourceId,bt.TaskName,rs.ResourceCode,btr.ResourceQuantity FROM Bud_TaskResource btr LEFT JOIN Res_Resource rs on btr.ResourceId=rs.ResourceId LEFT JOIN Bud_Task bt on btr.TaskId=bt.TaskId where btr.PrjGuid='{0}' UNION ALL SELECT bm.PrjId,bmt.TaskId,bmts.ResourceId,bt.TaskName,rs.ResourceCode,bmts.ResourceQuantity FROM Bud_ModifyTaskRes bmts LEFT JOIN Bud_ModifyTask bmt ON bmts.ModifyTaskId=bmt.ModifyTaskId LEFT JOIN Bud_Modify bm ON bmt.ModifyId=bm.ModifyId LEFT JOIN Bud_Task bt on bmt.TaskId=bt.TaskId LEFT JOIN Res_Resource rs on bmts.ResourceId=rs.ResourceId WHERE bm.flowstate !=-2 and bm.PrjId='{0}' ) s GROUP BY PrjGuid,TaskId,ResourceId,TaskName,ResourceCode )t LEFT JOIN ( SELECT sws.scode,sws.TaskId,sum(sws.number) numberIng from Sm_Wantplan_Stock sws --sw.flowstate,sw.swcode, LEFT JOIN Sm_Wantplan sw ON sws.wpcode=sw.swcode WHERE sw.flowstate !=-2 and sw.swcode !='' GROUP BY sws.scode,sws.TaskId ) w ON w.scode=t.ResourceCode AND w.TaskId=t.TaskId) SELECT t1.*,t2.*,rs.ResourceName from t1 LEFT JOIN ( select sws.scode,sum(sws.number) nums,sw.procode,(select sum(sos.number) outNums from Sm_out_Stock sos WHERE sos.wpcode in( select sws.wpcode from Sm_Wantplan_Stock sws LEFT JOIN Sm_Wantplan sw ON sws.wpcode=sw.swcode where sw.procode='{0}' ) and sos.scode=sws.scode) outNums from Sm_Wantplan_Stock sws LEFT JOIN Sm_Wantplan sw ON sws.wpcode=sw.swcode where sw.procode='{0}' GROUP BY sws.scode,sw.procode ) t2 on t1.scode=t2.scode LEFT JOIN Res_Resource rs on t1.ResourceId=rs.ResourceId " , this.hfldPrjId.Value); DataTable dt2 = publicDbOpClass.DataTableQuary(strSql); DataTable stuffInfo = new DataTable(); stuffInfo = dt1.Clone(); stuffInfo.Columns.Add("nums"); stuffInfo.Columns["nums"].DataType = Type.GetType("System.Decimal"); stuffInfo.Columns.Add("outNums"); stuffInfo.Columns["outNums"].DataType = Type.GetType("System.Decimal"); for (int ii = 0; ii < dt1.Rows.Count; ii++) { stuffInfo.Rows.Add(dt1.Rows[ii].ItemArray); } foreach (DataRow dr1 in stuffInfo.Rows) { foreach (DataRow dr2 in dt2.Rows) { if (dr1["ResourceCode"].ToString() == dr2["scode"].ToString()) { if (!string.IsNullOrEmpty(dr2["nums"].ToString())) { dr1["nums"] = Convert.ToDecimal(dr2["nums"].ToString()); } else { dr1["nums"] = 0; } if (!string.IsNullOrEmpty(dr2["outNums"].ToString())) { dr1["outNums"] = Convert.ToDecimal(dr2["outNums"].ToString()); } else { dr1["outNums"] = 0; } } } } foreach (DataRow dr1 in stuffInfo.Rows) { if (string.IsNullOrEmpty(dr1["nums"].ToString())) { dr1["nums"] = 0; } if (string.IsNullOrEmpty(dr1["outNums"].ToString())) { dr1["outNums"] = 0; } } if (stuffInfo.Rows.Count > 0) { DataRow dataRow = stuffInfo.NewRow(); dataRow["Num"] = "合计"; dataRow["BudQuantity"] = stuffInfo.Compute("sum(BudQuantity)", "1>0"); dataRow["BudTotal"] = stuffInfo.Compute("sum(BudTotal)", "1>0"); dataRow["PurchaseNumber"] = stuffInfo.Compute("sum(PurchaseNumber)", "1>0"); dataRow["PurchaseCost"] = stuffInfo.Compute("sum(PurchaseCost)", "1>0"); dataRow["StorageNumber"] = stuffInfo.Compute("sum(StorageNumber)", "1>0"); dataRow["StorageCost"] = stuffInfo.Compute("sum(StorageCost)", "1>0"); dataRow["RealityNumber"] = stuffInfo.Compute("sum(RealityNumber)", "1>0"); dataRow["RealityTotal"] = stuffInfo.Compute("sum(RealityTotal)", "1>0"); dataRow["ProfitLossNumber"] = stuffInfo.Compute("sum(ProfitLossNumber)", "1>0"); dataRow["ProfitLossCost"] = stuffInfo.Compute("sum(ProfitLossCost)", "1>0"); dataRow["BalanceNumber"] = stuffInfo.Compute("sum(BalanceNumber)", "1>0"); dataRow["BalanceCost"] = stuffInfo.Compute("sum(BalanceCost)", "1>0"); dataRow["nums"] = stuffInfo.Compute("sum(nums)", "1>0"); dataRow["outNums"] = stuffInfo.Compute("sum(outNums)", "1>0"); stuffInfo.Rows.Add(dataRow); } stuffInfo = this.GetTitleByTable(stuffInfo); System.Collections.Generic.List <ExcelHeader> list = new System.Collections.Generic.List <ExcelHeader>(); list.Add(ExcelHeader.Create("目标成本", 1, 3, 6, 0)); list.Add(ExcelHeader.Create("在途成本", 1, 5, 9, 0)); list.Add(ExcelHeader.Create("实际成本", 1, 3, 14, 0)); list.Add(ExcelHeader.Create("盈亏", 1, 2, 17, 0)); list.Add(ExcelHeader.Create("结存", 1, 2, 19, 0)); System.Collections.Generic.List <int> list2 = new System.Collections.Generic.List <int>(); foreach (DataColumn dataColumn in stuffInfo.Columns) { if (dataColumn.Ordinal >= 6) { list2.Add(dataColumn.Ordinal); } if (dataColumn.Ordinal < 6) { list.Add(ExcelHeader.Create(dataColumn.ColumnName, 2, 0, 0, 2)); } else { list.Add(ExcelHeader.Create(dataColumn.ColumnName, 2, 0, 0, 0)); } } ExcelHelper.ExportExcel(stuffInfo, "材料分析", "材料分析", "材料分析.xls", list, null, 3, base.Request.Browser.Browser); }