public virtual void FillData(int x, int y, string field, object value) { //if (!field.StartsWith("title_")) // cell.SetStyle(GetDataStyle()); switch ((value ?? string.Empty).GetType().Name.ToLower()) { case "int32": case "int64": case "decimal": sheet.Cells[y, x].PutValue(PFDataHelper.ObjectToType <double>(value, 0)); break; //case "System.String[]": // var s = String.Join(",", (string[])value); // sheet.Cells[y, x].PutValue(s); // break; default: if (value is string[]) { var s = String.Join(",", value as string[]); sheet.Cells[y, x].PutValue(s); } else { sheet.Cells[y, x].PutValue(PFDataHelper.ObjectToString(value)); } break; } }
public object handle(String consumerTag, PFMqMessage message) { var lastCMonth = ProjDataHelper.GetLastCMonthByDate(DateTime.Now); var p = JsonConvert.DeserializeObject <PagingParameters>(message.ToString()); var backupDatabase = PFDataHelper.ObjectToString(p["backupDatabase"]) ?? ""; var service = new DbReportService(lastCMonth); var list = service.GetCompareCntList(backupDatabase, false); return(list); }
private static bool IsDataRowMatchUpdate(DataRow row, SqlUpdateCollection update) { foreach (var i in update) { if (PFDataHelper.ObjectToString(i.Value.Value) == PFDataHelper.ObjectToString(row[i.Key])) { } else { return(false); } } return(true); }
public virtual void FillData(int x, int y, string field, object value)//x列y行 { FixXY(x, y); switch ((value ?? string.Empty).GetType().Name.ToLower()) { case "int32": case "int64": case "decimal": BuildCell(PFDataHelper.ObjectToType <double>(value, 0).ToString()); break; default: BuildCell(PFDataHelper.ObjectToString(value) ?? ""); break; } //if (y > curRow) { //} //if (table.Rows.Count < y+1) { // for (int i = table.Rows.Count - 1; i < y; i++) { // if (i < 0) { continue; } // builder.MoveToCell(0,i,table.Rows[i].Count-1,0); // builder.EndRow(); // } //} //sheet.MoveToCell(y,x); ////if (!field.StartsWith("title_")) //// cell.SetStyle(GetDataStyle()); //switch ((value ?? string.Empty).GetType().Name.ToLower()) //{ // case "int32": // case "int64": // case "decimal": // sheet.Cells[y, x].PutValue(PFDataHelper.ObjectToType<double>(value, 0)); // break; // default: // sheet.Cells[y, x].PutValue(PFDataHelper.ObjectToString(value)); // break; //} }
public static List <Dictionary <string, object> > ExcelToDictList(Workbook workbook) { var list = new List <Dictionary <string, object> >(); var cols = new List <string>(); //if (Request.Files.Count < 1) //{ // return Json(JsonData.SetFault("文件为空")); //} //Workbook wb = new Workbook(Request.Files[0].InputStream); //if (wb == null) //{ // return Json(JsonData.SetFault("excel打开失败")); //} var sheet = workbook.Worksheets[0]; int rowCnt = sheet.Cells.Rows.Count; int colCnt = sheet.Cells.Columns.Count; for (int j = 0; j < colCnt; j++) { cols.Add(PFDataHelper.ObjectToString(sheet.Cells[0, j].Value)); } var telephoneIdx = cols.IndexOf("telephone"); if (telephoneIdx < 0) { return(null); } for (int i = 1; i < rowCnt; i++) { var item = new Dictionary <string, object>(); var telephone = PFDataHelper.ObjectToString(sheet.Cells[i, telephoneIdx].Value); if (PFDataHelper.StringIsNullOrWhiteSpace(telephone))//只要有一行为空,就返回 { return(list); } for (int j = 0; j < colCnt; j++) { item[cols[j]] = sheet.Cells[i, j].Value; } list.Add(item); } return(list); }
public static List <T> ExcelToList <T>(Worksheet sheet, Dictionary <string, string> columnTextNameDict = null, string keyColumnName = null) where T : new() { //创建一个属性的列表 //List<PropertyInfo> prlist = new List<PropertyInfo>(); Dictionary <string, PropertyInfo> prDict = new Dictionary <string, PropertyInfo>(); //获取TResult的类型实例 反射的入口 Type t = typeof(T); //获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表 Array.ForEach <PropertyInfo>(t.GetProperties(), p => { if (columnTextNameDict == null || columnTextNameDict.Values.Contains(p.Name)) { prDict.Add(p.Name, p); } }); //创建返回的集合 List <T> oblist = new List <T>(); //var list = new List<Dictionary<string, object>>(); var cols = new List <string>(); //if (Request.Files.Count < 1) //{ // return Json(JsonData.SetFault("文件为空")); //} //Workbook wb = new Workbook(Request.Files[0].InputStream); //if (wb == null) //{ // return Json(JsonData.SetFault("excel打开失败")); //} //var sheet = workbook.Worksheets[0]; int rowCnt = sheet.Cells.Rows.Count; int colCnt = sheet.Cells.Columns.Count; for (int j = 0; j < colCnt; j++) { cols.Add(PFDataHelper.ObjectToString(sheet.Cells[0, j].Value)); } int keyIdx = -1; //var keyColumnText = columnTextNameDict == null ? keyColumnName : columnTextNameDict.First(a => a.Value == keyColumnName).Key; if (keyColumnName != null) { keyIdx = cols.IndexOf(columnTextNameDict == null ? keyColumnName : columnTextNameDict.First(a => a.Value == keyColumnName).Key); if (keyIdx < 0) { return(null); } } for (int i = 1; i < rowCnt; i++) { //var item = new Dictionary<string, object>(); if (keyColumnName != null) { var keyValue = PFDataHelper.ObjectToString(sheet.Cells[i, keyIdx].Value); if (PFDataHelper.StringIsNullOrWhiteSpace(keyValue))//只要有一行为空,就返回 { return(oblist); } } var item = new T(); for (int j = 0; j < colCnt; j++) { //item[cols[j]] = sheet.Cells[i, j].Value; //var sheetHeadRowText = ObjectToString(sheet.Cells[0, j].Value); var columnText = cols[j]; var columnName = columnTextNameDict == null ? columnText : columnTextNameDict[columnText]; var p = prDict[columnName]; var pt = PFDataHelper.GetPropertyType(p); var cellValue = sheet.Cells[i, j].Value; if (pt.IsEnum) { p.SetValue(item, Enum.Parse(pt, cellValue.ToString()), null); } //else if (pt.IsSubclassOf(typeof(PFCustomStringType))) //{ // p.SetValue(ob, row[p.Name].ToString(), null); //} else if (cellValue != null) { //if (pt == typeof(decimal) && dt.Columns[p.Name].DataType == typeof(int)) //{ // //由于某些数据库版本不统一的问题会导致,inv表的pv是decimal的,但tc_inv表的pv是int的,于是建model时tc_inv都采用decimal就行了 //} p.SetValue(item, ConvertObjectByType(sheet.Cells[i, j].Value, cellValue.GetType(), p.PropertyType), null); } } oblist.Add(item); } return(oblist); }
public Exporter Export(IExport export) { int i = 0; if (_title == null) { _title = new List <List <StoreColumn> >(); _title.Add(new List <StoreColumn>()); //PFDataHelper.EachListHeader(_data, (i, field, type) => _title[0].Add(new StoreColumn() { title = field, field = field, rowspan = 1, colspan = 1 })); PFDataHelper.EachListHeader(_data, (a, field, type) => _title[0].Add(new StoreColumn() { title = field, data = field //, rowspan = 1, colspan = 1 })); } Dictionary <int, int> currentHeadRow = new Dictionary <int, int>(); Dictionary <string, List <int> > fieldIndex = new Dictionary <string, List <int> >(); int titleRowCount = 0; if (!string.IsNullOrWhiteSpace(_sheetTitle)) { titleRowCount++; } Func <int, int> GetCurrentHeadRow = cell => currentHeadRow.ContainsKey(cell) ? currentHeadRow[cell] : titleRowCount; var currentRow = 0; var currentCell = 0; export.Init(_data, _printPageScheme); //标题--wxj20181011 var temp = new StoreColumn { Children = _columns }; int columnCount = temp.GetAllLeafCount(a => a.visible); var firstData = temp.FirstLeaf(a => true).data; if (!string.IsNullOrWhiteSpace(_sheetTitle)) { export.FillData(0, 0, firstData, _sheetTitle); export.SetTitleStyle(0, 0, columnCount - 1, 0); currentRow++; } //Stopwatch sw = new Stopwatch(); //sw.Start(); //int resultCount = 0; #region 3秒(后来发现只有调试时特别慢,原因未明) ////生成多行题头 for (i = 0; i < _title.Count; i++) { currentCell = 0; for (var j = 0; j < _title[i].Count; j++) { var item = _title[i][j]; if (item.visible == false) { continue; } //隐藏列不导出--wxj20181009 while (currentRow < GetCurrentHeadRow(currentCell)) { currentCell++; } //export.FillData(currentCell, currentRow, "title_" + item.data, item.title); export.FillData(currentCell, currentRow, "title_" + item.data, item.title ?? item.data);//e:\svn\businesssys2018\yjquery.web\areas\bonus\views\reportquery05\treegrid.cshtml里的title是null if (item.rowspan + item.colspan > 2) { export.MergeCell(currentCell, currentRow, currentCell + item.colspan - 1, currentRow + item.rowspan - 1); } if (!string.IsNullOrEmpty(item.data)) { if (!fieldIndex.ContainsKey(item.data)) { fieldIndex[item.data] = new List <int>(); } fieldIndex[item.data].Add(currentCell); } for (var k = 0; k < item.colspan; k++) { currentHeadRow[currentCell] = GetCurrentHeadRow(currentCell++) + item.rowspan; } //resultCount++; } currentRow++; } #endregion #region 一样是3秒 ////生成多行题头 //foreach (var ii in _title) //{ // currentCell = 0; // foreach (var j in ii) // { // //var item = _title[i][j]; // var item = j; // if (item.visible == false) { continue; }//隐藏列不导出--wxj20181009 // //if (item.hidden) continue; // while (currentRow < GetCurrentHeadRow(currentCell)) // currentCell++; // //export.FillData(currentCell, currentRow, "title_" + item.data, item.title); // export.FillData(currentCell, currentRow, "title_" + item.data, item.title ?? item.data);//e:\svn\businesssys2018\yjquery.web\areas\bonus\views\reportquery05\treegrid.cshtml里的title是null // if (item.rowspan + item.colspan > 2) // export.MergeCell(currentCell, currentRow, currentCell + item.colspan - 1, currentRow + item.rowspan - 1); // if (!string.IsNullOrEmpty(item.data)) // { // if (!fieldIndex.ContainsKey(item.data)) // fieldIndex[item.data] = new List<int>(); // fieldIndex[item.data].Add(currentCell); // } // for (var k = 0; k < item.colspan; k++) // currentHeadRow[currentCell] = GetCurrentHeadRow(currentCell++) + item.rowspan; // } // currentRow++; //} #endregion //sw.Stop(); //var aa = string.Format("插入{0}条记录共花费{1}毫秒,{2}分钟", resultCount, sw.ElapsedMilliseconds, sw.ElapsedMilliseconds / 1000 / 60); //设置题头样式 //export.SetHeadStyle(0, 0, currentCell - 1, currentRow - 1); export.SetHeadStyle(0, titleRowCount, currentHeadRow.Count - 1, currentRow - 1);//上面那样,当后面的列不是多表头时,背景色只填到最后一个多表头为止 ////设置数据样式 var dataCount = 0; if (_data is PagingResult) { var data = _data as PagingResult; var list = data.data as ArrayList; if (list != null) { for (var rowIndex = 0; rowIndex < list.Count; rowIndex++) { dataCount++; } } else { var list1 = data.data as List <TreeListItem>; if (list1 != null) { (new TreeListItem { Children = list1 }).EachChild(a => dataCount++); } } } else { PFDataHelper.EachListRow(_data, (a, r) => dataCount++);//原版 } ////export.SetRowsStyle(0, currentRow, currentCell - 1, currentRow + dataCount - 1); //此句内报错,要优化--benjamin todo if (!PFDataHelper.IsDebug) { export.SetRowsStyle(0, currentRow, currentHeadRow.Count - 1, currentRow + dataCount - 1);//上面那样,当后面的列不是多表头时,边框不见了 } //填充数据 if (_data is PagingResult) { var data = _data as PagingResult; if (data.data is List <TreeListItem> ) { export.SetFont(0, currentRow, 0, currentRow + dataCount - 1, "宋体");//默认的Arial字体中,树型的┝等符号对不齐--benjamin20190711 var tree = new TreeListItem(); tree.Children = data.data as List <TreeListItem>; int rowIndex = 0; //int colIndex = 0; var matrix = new TreeMatrix(tree.Children); tree.EachChild((a, deep) => { //colIndex = 0; PFDataHelper.EachObjectProperty(a.Data, (b, name, value) => { if (fieldIndex.ContainsKey(name)) { foreach (int cellIndex in fieldIndex[name]) { if (_fieldFormatter.ContainsKey(name)) { value = _fieldFormatter[name].Format(value); } //if (colIndex == 0) if (cellIndex == 0) { var line = ""; for (var j = 0; j < deep - 2; j++) { //line += string.Format("<div class='{0} {1}'></div>", "tree-tr-linearea ", GetClassByTreeMatrixNetLine(matrix.GetNetLine(j, rowIdx))); line += matrix.GetNetLineString(j, rowIndex); } value = line + PFDataHelper.ObjectToString(value); //var line = GetClassByTreeMatrixNetLine(matrix.GetNetLine(cellIndex, currentRow)) } export.FillData(cellIndex, currentRow, name, value); } //colIndex++; } }); rowIndex++; currentRow++; }); } else { var list = data.data as ArrayList; for (var rowIndex = 0; rowIndex < list.Count; rowIndex++) { var rowData = list[rowIndex] as Dictionary <string, object>; for (i = 0; i < rowData.Count; i++) { var name = rowData.ElementAt(i).Key; var value = rowData.ElementAt(i).Value; if (fieldIndex.ContainsKey(name)) { foreach (int cellIndex in fieldIndex[name]) { if (_fieldFormatter.ContainsKey(name)) { value = _fieldFormatter[name].Format(value); } export.FillData(cellIndex, currentRow, name, value); } } } currentRow++; } } } else { //原版 PFDataHelper.EachListRow(_data, (rowIndex, rowData) => { PFDataHelper.EachObjectProperty(rowData, (a, name, value) => { if (fieldIndex.ContainsKey(name)) { foreach (int cellIndex in fieldIndex[name]) { if (_fieldFormatter.ContainsKey(name)) { value = _fieldFormatter[name].Format(value); } export.FillData(cellIndex, currentRow, name, value); } } }); currentRow++; }); } //汇总行 bool hasSummary = false; i = 0; int firstSummary = 0;//第一个有汇总的格的位置 string firstSummaryField = ""; new StoreColumn { Children = _columns }.EachLeaf(a => { //设置列宽--wxjtodo20190417 if (!PFDataHelper.StringIsNullOrWhiteSpace(a.width)) { //export.SetColumnWidth(i, PFDataHelper.WebWidthToExcel(a.width).Value); export.SetColumnWidth(i, a.width); } //if (a.excelWidth.HasValue) //{ // export.SetColumnWidth(i, double.Parse(a.width.Replace("px", ""))); //} //var column = _title[_title.Count - 1][i]; if (a.visible) { var column = a; if (!hasSummary) { firstSummary = i; firstSummaryField = column.data; } if (column.summary != null) { hasSummary = true; export.FillData(i, currentRow, column.data, column.summary); } i++; } }); if (hasSummary) { export.FillData(firstSummary - 1, currentRow, firstSummaryField, "合计:"); export.SetRowsStyle(0, currentRow, columnCount - 1, currentRow);//上面那样,当后面的列不是多表头时,边框不见了 currentRow++; } //Foot--wxj20181011 if (!string.IsNullOrWhiteSpace(_sheetFoot)) { export.FillData(0, currentRow, firstData, _sheetFoot); export.SetFootStyle(0, currentRow, columnCount - 1, currentRow); //titleRowCount++; currentRow++; } _exporter = export; //_fileStream = export.SaveAsStream(); _suffix = export.suffix; if (string.IsNullOrEmpty(_fileName)) { _fileName = DateTime.Now.ToString("yyyyMMddHHmmss"); } return(this); }