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);
}
