private Dictionary<string, object> ReadRow(IRow row, KeyValuePair<int, string>[] titles) { var data = new Dictionary<string, object>(); foreach (var title in titles) { var cell = row.GetCell(title.Key); if (cell == null) { data.Add(title.Value, string.Empty); continue; } switch (cell.CellType) { case CellType.Numeric: data.Add(title.Value, cell.NumericCellValue); break; case CellType.String: data.Add(title.Value, cell.StringCellValue); break; case CellType.Blank: data.Add(title.Value, string.Empty); break; default: throw new Exception(string.Format("Invalid excel cell type: {0} ({1}.{2}.{3})", cell.CellType, cell.Row.Sheet.SheetName, cell.RowIndex, cell.ColumnIndex)); } } return data; }
public QAException(IRow exception) { if (exception == null) throw new ArgumentNullException("exception", "Cannot pass null row to QAException"); this._objectID = exception.OID; int idx; idx = exception.Fields.FindField("ATTACHMENT"); this._attachment = exception.get_Value(idx).ToString(); idx = exception.Fields.FindField("DATA_EXCEPTION_POINT_ID"); this._exceptionID = Convert.ToInt32(exception.get_Value(idx)); idx = exception.Fields.FindField("DATA_EXCEPTION_STATUS"); this._status = exception.get_Value(idx).ToString(); idx = exception.Fields.FindField("EXCEPTION_DESCRIPTION"); this._description = exception.get_Value(idx).ToString(); idx = exception.Fields.FindField("LATITUDE"); this._latitude = Convert.ToDouble(exception.get_Value(idx)); idx = exception.Fields.FindField("LONGITUDE"); this._longitude = Convert.ToDouble(exception.get_Value(idx)); idx = exception.Fields.FindField("OPERATIONAL_DATASET_NAME"); this._operationDSName = exception.get_Value(idx).ToString(); idx = exception.Fields.FindField("QA_TEST_NAME"); this._testName = exception.get_Value(idx).ToString(); }
private static void Process(IRow row, HSSFFormulaEvaluator eval) { IEnumerator it = row.GetEnumerator(); while (it.MoveNext()) { ICell cell = (ICell)it.Current; if (cell.CellType != NPOI.SS.UserModel.CellType.FORMULA) { continue; } FormulaRecordAggregate record = (FormulaRecordAggregate)((HSSFCell)cell).CellValueRecord; FormulaRecord r = record.FormulaRecord; Ptg[] ptgs = r.ParsedExpression; String cellRef = new CellReference(row.RowNum, cell.ColumnIndex, false, false).FormatAsString(); #if !HIDE_UNREACHABLE_CODE if (false && cellRef.Equals("BP24")) { Console.Write(cellRef); Console.WriteLine(" - has " + ptgs.Length + " ptgs:"); for (int i = 0; i < ptgs.Length; i++) { String c = ptgs[i].GetType().ToString(); Console.WriteLine("\t" + c.Substring(c.LastIndexOf('.') + 1)); } Console.WriteLine("-> " + cell.CellFormula); } #endif NPOI.SS.UserModel.CellValue evalResult = eval.Evaluate(cell); Assert.IsNotNull(evalResult); } }
private void buildWorkbook(IWorkbook wb) { ISheet sh = wb.CreateSheet(); IRow row1 = sh.CreateRow(0); IRow row2 = sh.CreateRow(1); row3 = sh.CreateRow(2); row1.CreateCell(0, CellType.Numeric); row1.CreateCell(1, CellType.Numeric); row2.CreateCell(0, CellType.Numeric); row2.CreateCell(1, CellType.Numeric); row3.CreateCell(0); row3.CreateCell(1); CellReference a1 = new CellReference("A1"); CellReference a2 = new CellReference("A2"); CellReference b1 = new CellReference("B1"); CellReference b2 = new CellReference("B2"); sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(35); sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(0); sh.GetRow(b1.Row).GetCell(b1.Col).CellFormula = (/*setter*/"A1/A2"); sh.GetRow(b2.Row).GetCell(b2.Col).CellFormula = (/*setter*/"NA()"); Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); }
public static object GetValueByColumn(this ITable table, string colName, IRow row) { var colId = table.Columns.Where(c => c.Name == colName).Select((c, i) => (int?)i).FirstOrDefault(); if (colId == null) throw new ArgumentOutOfRangeException(String.Format("Column '{0}' not found.", colName)); return GetValue(table, colId.Value, row); }
/// <summary/> private static void WriteRow(IRow row, JsonTextWriter writer) { // Row // => { c1:v1, c2:v2, ...} // Header writer.WriteStartObject(); // Fields var columns = row.Schema; for(int i=0; i<columns.Count; i++) { // Note: We simply delegate to Json.Net for all data conversions // For data conversions beyond what Json.Net supports, do an explicit projection: // ie: SELECT datetime.ToString(...) AS datetime, ... object value = row.Get<object>(i); // Note: We don't bloat the JSON with sparse (null) properties if(value != null) { writer.WritePropertyName(columns[i].Name, escape:true); writer.WriteValue(value); } } // Footer writer.WriteEndObject(); }
/// <summary>Apply is called at least once per instance</summary> /// <param name="input">A SQLIP row</param> /// <param name="output">A SQLIP updatable row.</param> /// <returns>IEnumerable of IRow, one IRow per SQLIP row.</returns> /// <remarks>Because applier constructor arguments cannot depend on /// column references, the name of the column to parse is given as a string. Then /// the actual column value is obtained by calling IRow.Get. The rest of the code /// is the same as XmlDomExtractor.</remarks> public override IEnumerable<IRow> Apply(IRow input, IUpdatableRow output) { // Make sure that all requested columns are of type string IColumn column = output.Schema.FirstOrDefault(col => col.Type != typeof(string)); if (column != null) { throw new ArgumentException(string.Format("Column '{0}' must be of type 'string', not '{1}'", column.Name, column.Type.Name)); } XmlDocument xmlDocument = new XmlDocument(); xmlDocument.LoadXml(input.Get<string>(this.xmlColumnName)); foreach (XmlNode xmlNode in xmlDocument.DocumentElement.SelectNodes(this.rowPath)) { // IUpdatableRow implements a builder pattern to save memory allocations, // so call output.Set in a loop foreach(IColumn col in output.Schema) { var explicitColumnMapping = this.columnPaths.FirstOrDefault(columnPath => columnPath.Value == col.Name); XmlNode xml = xmlNode.SelectSingleNode(explicitColumnMapping.Key ?? col.Name); output.Set(explicitColumnMapping.Value ?? col.Name, xml == null ? null : xml.InnerXml); } // then call output.AsReadOnly to build an immutable IRow. yield return output.AsReadOnly(); } }
internal static ICell CreateCell(IWorkbook workbook, IRow row, int column, decimal? content, bool isCentered) { ICellStyle style = workbook.CreateCellStyle(); ICell cell = row.CreateCell(column); if (content == null) { cell.SetCellValue(""); } else { cell.SetCellValue(Convert.ToDouble(content.Value)); } if (isCentered) { style.Alignment = HorizontalAlignment.Center; } style.BorderBottom = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; cell.CellStyle = style; return cell; }
public bool IsGood(IRow row, ITable table) { var keystring = table.GetKeystring(row); var inf = _lookup[keystring]; System.Threading.Thread.Sleep(inf.Duration); return inf.Result; }
private DataCell GetCell(IRow row, int index) { DataCell retCell = new DataCell(); ICell cell = row.Cells[index]; switch (cell.CellType) { case CellType.String : retCell.Type = DataCellType.String; retCell.Value = cell.StringCellValue; break; case CellType.Numeric : retCell.Type = DataCellType.Numeric; retCell.Value = cell.NumericCellValue; break; case CellType.Formula : retCell.Type = DataCellType.Formula; retCell.Value = cell.CellFormula; break; case CellType.Boolean : retCell.Type = DataCellType.Boolean; retCell.Value = cell.BooleanCellValue; break; case CellType.Blank : retCell.Type = DataCellType.Blank; retCell.Value = cell.StringCellValue; break; } retCell.ColumnIndex = cell.ColumnIndex; return retCell; }
public static double GetCellNumic(IRow row, int y) { double _r = double.MinValue; ICell cell = row.Cells[y - 1]; if (cell != null) switch (cell.CellType) { case CellType.String: { _r = double.Parse(cell.StringCellValue.Trim()); } break; case CellType.Numeric: { _r = cell.NumericCellValue; } break; default: { _r = 0; } break; } return _r; }
// Output // // Outputs the names of the rowset columns in a column separated row and optionally adds their types in a second row. // public override void Output(IRow row, IUnstructuredWriter output) { if (_first_row_written) { return; } using (StreamWriter streamWriter = new StreamWriter(output.BaseStream, this._encoding)) { streamWriter.NewLine = this._row_delim; ISchema schema = row.Schema; for (int i = 0; i < schema.Count(); i++) { var col = schema[i]; if (i > 0) { streamWriter.Write(this._col_delim); } var val = _quoting ? AddQuotes(col.Name) : col.Name; streamWriter.Write(val); } streamWriter.WriteLine(); if (_with_types) { for (int i = 0; i < schema.Count(); i++) { var col = schema[i]; if (i > 0) { streamWriter.Write(this._col_delim); } var val = _quoting ? AddQuotes(col.Type.FullName) : col.Type.FullName; streamWriter.Write(val); } streamWriter.WriteLine(); } } _first_row_written = true; }
public static object GetDefaultValueForField(IRow row, IField fld) { object defaultValue = DBNull.Value; if (!fld.IsNullable) defaultValue = string.Empty; if (row != null && fld != null) { IClass cls = row.Table; if (HasSubtype(cls)) { ISubtypes subTypes = (ISubtypes)cls; string subTypeFieldName = GetSubTypeFieldName(cls); if (string.IsNullOrEmpty(subTypeFieldName) == false) { object o = GetFieldValue(row, subTypeFieldName); int subTypeCode = ToInteger(o, -1); if (subTypeCode != -1) defaultValue = subTypes.get_DefaultValue(subTypeCode, fld.Name); } } if (defaultValue == DBNull.Value && fld.Type == esriFieldType.esriFieldTypeDate) defaultValue = fld.DefaultValue; } return defaultValue; }
public DunaGridRowSelector(IRow row) : base() { this.Row = row; InitializeComponent(); this.HoverAreaPadding = new Padding(0, 3, 0, 3); DoubleBuffered = true; }
public void CompletedRowsReturnsCorrectly(IRow row) { foreach (var position in row.Positions) _grid = _grid.Fill(position, Mark.Cross); var completedRows = _grid.CompletedRows(); Assert.That(completedRows, Is.Not.Null); Assert.That(completedRows, Contains.Item(row)); }
private static int InsertCell(IRow row, int cellIndex, string value, ICellStyle cellStyle) { var cell = row.CreateCell(cellIndex); cell.SetCellValue(value); cell.CellStyle = cellStyle; cellIndex++; return cellIndex; }
internal static string TransformSpecialCase(IRow theRow, short redLightDiviser, short numberOfLightsOn) { var lightsOn = new String('\0', numberOfLightsOn); lightsOn = String.Join("", lightsOn.Select((light, index) => CheckIfIsTimeToUseRed(index, redLightDiviser) ? BerlinClockConstants.RedLight : BerlinClockConstants.YellowLight)); return AppendLightsOffAndLineBreak(theRow.NumberOfLights, lightsOn, theRow.HasLineBreak); }
public override void Output(IRow input, IUnstructuredWriter output) { var obj = input.Get<object>(0); byte[] imageArray = (byte[])obj; using (MemoryStream ms = new MemoryStream(imageArray)) { var image = Image.FromStream(ms); image.Save(output.BaseStream, ImageFormat.Jpeg); } }
public SpotifyNotification CreateSingleFrom(IRow row) { return new SpotifyNotification() { SongTitle = row.Cells[2].StringCellValue, Artist = row.Cells[3].StringCellValue, Row = row, Genres = new List<String>() }; }
public void CreateCells(int cellsCount, ref IRow row, IList<string> cellsValues = null) { //var isEmptyCells = cellsValues == null || !cellsValues.Any() || cellsValues.Count != cellsCount; //if (isEmptyCells) // for (var j = 0; j < cellsCount; j++) // row.CreateCell(j).SetCellValue(string.Empty); //else for (var j = 0; j < cellsCount; j++) row.CreateCell(j).SetCellValue("cell " + j); }
protected ICell WriteCell(IRow row, int column, Action<ICell> setValue, ICellStyle style = null) { var cell = row.CreateCell(column); setValue(cell); if (style != null) { cell.CellStyle = style; } return cell; }
public Dictionary<string, object> GetAttributes(IRow row) { var atts = new Dictionary<string, object>(); for (int i = 0; i < row.Fields.FieldCount; i++) { esriFieldType esriType = row.Fields.get_Field(i).Type; switch (esriType) { case esriFieldType.esriFieldTypeBlob: break; case esriFieldType.esriFieldTypeDate: DateTime date = (DateTime)row.get_Value(i); atts.Add(row.Fields.get_Field(i).AliasName, date.Date.ToShortDateString()); break; case esriFieldType.esriFieldTypeDouble: atts.Add(row.Fields.get_Field(i).AliasName, row.get_Value(i)); break; case esriFieldType.esriFieldTypeGUID: break; case esriFieldType.esriFieldTypeGeometry: break; case esriFieldType.esriFieldTypeGlobalID: break; case esriFieldType.esriFieldTypeInteger: atts.Add(row.Fields.get_Field(i).AliasName, row.get_Value(i)); break; case esriFieldType.esriFieldTypeOID: break; case esriFieldType.esriFieldTypeRaster: break; case esriFieldType.esriFieldTypeSingle: atts.Add(row.Fields.get_Field(i).AliasName, row.get_Value(i)); break; case esriFieldType.esriFieldTypeSmallInteger: atts.Add(row.Fields.get_Field(i).AliasName, row.get_Value(i)); break; case esriFieldType.esriFieldTypeString: atts.Add(row.Fields.get_Field(i).AliasName, row.get_Value(i)); break; case esriFieldType.esriFieldTypeXML: break; default: break; } } return atts; }
/// <summary> /// vyhodnoti podminku a vrati vysledek /// </summary> public bool getResult(IRow radek) { //zrusi odkazy na sloupce a pripravi si pro vyhodnoceni realnou hodnotu z radku object left = this.tryParseColumn(this.left_value, radek); object right = this.tryParseColumn(this.right_value, radek); //pokud se nerovnaji datove typy tak si hodnoty nemohou byt rovny if (!this.IsComparable(left, right)) { return false; } else { switch (compare_operator) { case Operators.equal: if (left.Equals(right)) return true; break; case Operators.not_equal: if (!left.Equals(right)) return true; break; case Operators.greater_than: if (left.GetType() == typeof(double)) { if ((double)left > (double)right) return true; } break; case Operators.lower_than: if (left.GetType() == typeof(double)) { if ((double)left < (double)right) return true; } break; case Operators.like: //TODO: implementovat operator LIKE break; case Operators.regexp: //TODO: implementovat operator REGEXP break; } return false; } }
public override IRow Process(IRow input, IUpdatableRow output) { var img = input.Get<byte[]>("image_data"); // load image only once into memory per row using (StreamImage inImage = new StreamImage(img)) { output.SetColumnIfExists("equipment_make", inImage.getStreamImageProperty(ImageProperties.equipment_make)); output.SetColumnIfExists("equipment_model", inImage.getStreamImageProperty(ImageProperties.equipment_model)); output.SetColumnIfExists("description", inImage.getStreamImageProperty(ImageProperties.description)); output.SetColumnIfExists("copyright", inImage.getStreamImageProperty(ImageProperties.copyright)); output.SetColumnIfExists("thumbnail", inImage.scaleStreamImageTo(150, 150)); } return output.AsReadOnly(); }
/// <summary/> public override void Output(IRow row, IUnstructuredWriter output) { // First Row if(this.writer == null) { // Json.Net (writer) this.writer = new JsonTextWriter(new StreamWriter(output.BaseStream)); // Header (array) this.writer.WriteStartArray(); } // Row(s) WriteRow(row, this.writer); }
private BetType CreateBetTypeFromExcelRow(IRow row) { if (row == null) { throw new ArgumentNullException(); } BetType betType = new BetType(); betType.Id = Convert.ToInt32(row.Cells[0].NumericCellValue); betType.Name = betType.Id.ToString() + " - " + row.Cells[1].StringCellValue; betType.Description = row.Cells[2].StringCellValue; betType.Note = row.Cells[3].StringCellValue; betType.Category = row.Cells[4].StringCellValue; return betType; }
public void SetCellValue(IRow row, int index, CellType cellType, dynamic value) { if (value == null) value = string.Empty; if (value is string) { value = Convert.ChangeType(value, TypeCode.String); } else { value = Convert.ChangeType(value, TypeCode.Double); } ICell cell = row.CreateCell(index); cell.SetCellType(cellType); cell.SetCellValue(value); }
Row CreateRow(IRow row, int columns) { var cells = new List<Cell>(); ICell[] iCells = row.Cells.ToArray(); int skipped = 0; for (int i = 0; i < columns; i++) { Cell cell; if (i - skipped >= iCells.Length || i != iCells[i - skipped].ColumnIndex) { cell = new Cell(row.RowNum, i, null); skipped++; } else cell = CreateCell(iCells[i - skipped]); cells.Add(cell); } return new Row(cells); }
internal static ICell CreateCell(IWorkbook workbook, IRow row, int column, string content, bool isCentered) { ICellStyle style = workbook.CreateCellStyle(); ICell cell = row.CreateCell(column); cell.SetCellValue(content); if (isCentered) { style.Alignment = HorizontalAlignment.Center; } style.BorderBottom = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; cell.CellStyle = style; return cell; }
public QATest(IRow testRow, ITable paramTable, bool canWrite) { if (testRow == null) throw new ArgumentNullException("testRow", "No row from the QA test table passed to constructor for QATest"); if (paramTable == null) throw new ArgumentNullException("paramTable", "No QA parameter table passed to constructor for QATest"); int index; index = testRow.Fields.FindField(TEST_NAME_FIELD); if (index < 0) throw new System.MissingFieldException(QATestCollection.TABLE_NAME, TEST_NAME_FIELD); this._name = testRow.get_Value(index).ToString(); index = testRow.Fields.FindField(COM_CLSID_FIELD); if (index < 0) throw new System.MissingFieldException(QATestCollection.TABLE_NAME, COM_CLSID_FIELD); this._comClsid = testRow.get_Value(index).ToString(); // Constrained to be YES or NO index = testRow.Fields.FindField(MANDITORY_FIELD); if (index < 0) throw new System.MissingFieldException(QATestCollection.TABLE_NAME, MANDITORY_FIELD); string isManditoryStr = testRow.get_Value(index).ToString(); this._isManditory = isManditoryStr.ToUpper().Equals("YES"); this._isActive = this._isManditory; // if manditory, make active // Try to instantiate a copy of the COM object try { System.Guid theGuid = new Guid(this._comClsid); this._test = this.CreateTestFromGuid(theGuid, true); // Load the parameter collection this._parameters = new QAParameterCollection(paramTable, this.Test); } catch (Exception ex) { util.Logger.Write("Could not instantiate a IDataQualityTest with CLSID '" + this._comClsid + "'" + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace.ToString(), util.Logger.LogLevel.Warn); } this._canWrite = canWrite; }
// Methods public DeletedRowInaccessibleException(IRow row) : base(row, "Operacja niedozwolona na skasowanym obiekcie danych") { }
public static DataTable GetDataFromExcel(Stream ins, out ISheet fSheet, int headRowIndex = 5) { IWorkbook workbook = InitWorkBook(ins); fSheet = null; DataTable dt = new DataTable(); if (workbook.NumberOfSheets > 0) { fSheet = workbook.GetSheetAt(0); if (fSheet.LastRowNum < headRowIndex) { throw new ArgumentException("Excel模版错误,标题行索引大于总行数"); } //读取标题行 IRow row = null; ICell cell = null; row = fSheet.GetRow(headRowIndex); object objColumnName = null; for (int i = 0, length = row.LastCellNum; i < length; i++) { cell = row.GetCell(i); if (cell == null) { continue; } objColumnName = GetCellVale(cell); if (objColumnName != null) { try { dt.Columns.Add(objColumnName.ToString().Trim()); } catch (Exception e) { throw new Exception("上传文件格式与下载模板格式不符"); } } else { dt.Columns.Add(""); } } //读取数据行 object[] entityValues = null; int columnCount = dt.Columns.Count; for (int i = headRowIndex + 1, length = fSheet.LastRowNum; i < length; i++) { row = fSheet.GetRow(i); if (row == null) { continue; } entityValues = new object[columnCount]; //用于判断是否为空行 bool isHasData = false; int dataColumnLength = row.LastCellNum < columnCount ? row.LastCellNum : columnCount; for (int j = 0; j < dataColumnLength; j++) { cell = row.GetCell(j); if (cell == null) { continue; } entityValues[j] = GetCellVale(cell); if (!isHasData && j < columnCount && entityValues[j] != null) { isHasData = true; } } if (isHasData) { dt.Rows.Add(entityValues); } } } return(dt); }
/// <summary> /// Excel导入 /// </summary> /// <param name="filePath"></param> /// <returns></returns> public List <T> ImportFromExcel(string filePath) { string absoluteFilePath = GlobalContext.HostingEnvironment.ContentRootPath + filePath.Replace(Path.AltDirectorySeparatorChar, Path.DirectorySeparatorChar); List <T> list = new List <T>(); HSSFWorkbook hssfWorkbook = null; XSSFWorkbook xssWorkbook = null; ISheet sheet = null; using (FileStream file = new FileStream(absoluteFilePath, FileMode.Open, FileAccess.Read)) { switch (Path.GetExtension(filePath)) { case ".xls": hssfWorkbook = new HSSFWorkbook(file); sheet = hssfWorkbook.GetSheetAt(0); break; case ".xlsx": xssWorkbook = new XSSFWorkbook(file); sheet = xssWorkbook.GetSheetAt(0); break; default: throw new Exception("不支持的文件格式"); } } IRow columnRow = sheet.GetRow(1); // 第二行为字段名 Dictionary <int, PropertyInfo> mapPropertyInfoDict = new Dictionary <int, PropertyInfo>(); for (int j = 0; j < columnRow.LastCellNum; j++) { ICell cell = columnRow.GetCell(j); PropertyInfo propertyInfo = MapPropertyInfo(cell.ParseToString()); if (propertyInfo != null) { mapPropertyInfoDict.Add(j, propertyInfo); } } for (int i = (sheet.FirstRowNum + 2); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); T entity = new T(); for (int j = row.FirstCellNum; j < columnRow.LastCellNum; j++) { if (mapPropertyInfoDict.ContainsKey(j)) { if (row.GetCell(j) != null) { PropertyInfo propertyInfo = mapPropertyInfoDict[j]; switch (propertyInfo.PropertyType.ToString()) { case "System.DateTime": case "System.Nullable`1[System.DateTime]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDateTime()); break; case "System.Boolean": case "System.Nullable`1[System.Boolean]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToBool()); break; case "System.Byte": case "System.Nullable`1[System.Byte]": mapPropertyInfoDict[j].SetValue(entity, Byte.Parse(row.GetCell(j).ParseToString())); break; case "System.Int16": case "System.Nullable`1[System.Int16]": mapPropertyInfoDict[j].SetValue(entity, Int16.Parse(row.GetCell(j).ParseToString())); break; case "System.Int32": case "System.Nullable`1[System.Int32]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToInt()); break; case "System.Int64": case "System.Nullable`1[System.Int64]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToLong()); break; case "System.Double": case "System.Nullable`1[System.Double]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble()); break; case "System.Decimal": case "System.Nullable`1[System.Decimal]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDecimal()); break; default: case "System.String": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString()); break; } } } } list.Add(entity); } hssfWorkbook?.Close(); xssWorkbook?.Close(); return(list); }
/// <summary> /// 将excel导入到datatable /// </summary> /// <param name="filePath">excel路径</param> /// <param name="isColumnName">第一行是否是列名</param> /// <returns>返回datatable</returns> public static DataTable ExcelToDataTable(string filePath, bool isColumnName) { DataTable dataTable = null; FileStream fs = null; DataColumn column = null; DataRow dataRow = null; IWorkbook workbook = null; ISheet sheet = null; IRow row = null; ICell cell = null; int startRow = 0; try { using (fs = File.OpenRead(filePath)) { // 2007版本 if (filePath.IndexOf(".xlsx") > 0) { workbook = new XSSFWorkbook(fs); } // 2003版本 else if (filePath.IndexOf(".xls") > 0) { workbook = new HSSFWorkbook(fs); } if (workbook != null) { sheet = workbook.GetSheetAt(0);//读取第一个sheet,当然也可以循环读取每个sheet dataTable = new DataTable(); if (sheet != null) { int rowCount = sheet.LastRowNum;//总行数 if (rowCount > 0) { IRow firstRow = sheet.GetRow(0); //第一行 int cellCount = firstRow.LastCellNum; //列数 //构建datatable的列 if (isColumnName) { startRow = 1;//如果第一行是列名,则从第二行开始读取 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { cell = firstRow.GetCell(i); if (cell != null) { if (cell.StringCellValue != null) { column = new DataColumn(cell.StringCellValue); dataTable.Columns.Add(column); } } } } else { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { column = new DataColumn("column" + (i + 1)); dataTable.Columns.Add(column); } } //填充行 for (int i = startRow; i <= rowCount; ++i) { row = sheet.GetRow(i); if (row == null) { continue; } dataRow = dataTable.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { cell = row.GetCell(j); if (cell == null) { dataRow[j] = ""; } else { //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,) switch (cell.CellType) { case CellType.Blank: dataRow[j] = ""; break; case CellType.Numeric: short format = cell.CellStyle.DataFormat; //对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理 if (format == 14 || format == 31 || format == 57 || format == 58) { dataRow[j] = cell.DateCellValue; } else { dataRow[j] = cell.NumericCellValue; } break; case CellType.String: dataRow[j] = cell.StringCellValue; break; } } } dataTable.Rows.Add(dataRow); } } } } } return(dataTable); } catch (Exception) { if (fs != null) { fs.Close(); } return(null); } }
public void Save(ISheet sheet) { int realRowIndex = 0; for (int rowIndex = 0; rowIndex < _rows.Count; rowIndex++) { VRow row = _rows[rowIndex] as VRow; if (row.realRowIndex != -1 || row.targetRowIndex != -1) { IRow sheetRow = null; if (row.realRowIndex != -1 && row.changed && row.targetRowIndex == -1) { sheetRow = sheet.GetRow(realRowIndex); if (sheetRow != null) { int lastRowIndex = sheet.LastRowNum; sheet.ShiftRows(realRowIndex + 1, sheet.LastRowNum, -1, true, false); } continue; } if (realRowIndex <= sheet.LastRowNum && row.realRowIndex != -1) { sheetRow = sheet.GetRow(realRowIndex); } else { if (realRowIndex <= sheet.LastRowNum) { sheet.ShiftRows(realRowIndex, sheet.LastRowNum, 1, true, false); } sheetRow = sheet.CreateRow(realRowIndex); //sheetRow = sheet.GetRow(realRowIndex); } int lastCellIndex = sheetRow.LastCellNum; for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) { IExcelCell cell = row.GetCell(columnIndex); ICell sheetCell = sheetRow.GetCell(columnIndex); if (cell != null && cell.value != null) { if (sheetCell == null) { sheetCell = sheetRow.CreateCell(columnIndex); } if (sheetCell != null) { sheetCell.SetCellValue(cell.value); } } else { if (sheetCell != null) { sheetRow.RemoveCell(sheetCell); } } } realRowIndex++; } } }
protected override ValueGetter <Single> GetScoreGetter(IRow row) { return(row.GetGetter <Single>(_bindings.ScoreIndex)); }
private static CountAggregator GetVecAggregator <T>(IRow row, ColumnType colType, int colSrc) { return(new CountAggregator <T>(colType, row.GetGetter <VBuffer <T> >(colSrc))); }
/// <summary> /// DataTable 导出到 Excel 的 MemoryStream /// </summary> /// <param name="dtSource">源 DataTable</param> /// <param name="strHeaderText">表头文本 空值未不要表头标题</param> /// <returns></returns> public static MemoryStream ExportExcel(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); #region 文件属性 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "517best.com"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "517best.com"; si.ApplicationName = "517best.com"; si.LastAuthor = "517best.com"; si.Comments = ""; si.Title = ""; si.Subject = ""; si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; int intTop = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表、填充表头、填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } intTop = 0; #region 表头及样式 { if (strHeaderText.Length > 0) { IRow headerRow = sheet.CreateRow(intTop); intTop += 1; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(intTop); intTop += 1; ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = intTop; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); } }
/// <summary> /// DataTable 导出到 Excel 的 MemoryStream /// </summary> /// <param name="workbook">源 workbook</param> /// <param name="dtSource">源 DataTable</param> /// <param name="strHeaderText">表头文本 空值未不要表头标题(多个表对应多个表头以英文逗号(,)分开,个数应与表相同)</param> /// <returns></returns> public static void ExportFromDSExcel(HSSFWorkbook workbook, DataTable dtSource, string strHeaderText) { ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); ISheet sheet = workbook.CreateSheet(strHeaderText); int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; int intTop = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表、填充表头、填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } intTop = 0; #region 表头及样式 { if (strHeaderText.Length > 0) { IRow headerRow = sheet.CreateRow(intTop); intTop += 1; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(intTop); intTop += 1; ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 // sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); // 设置设置列宽 太长会报错 修改2014 年9月22日 int dd = (arrColWidth[column.Ordinal] + 1) * 256; if (dd > 200 * 256) { dd = 100 * 256; } sheet.SetColumnWidth(column.Ordinal, dd); } } #endregion rowIndex = intTop; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 if (drValue.Length > 0) { DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 } else { newCell.SetCellValue(drValue); } break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } }
public IActionResult PostUploadCSV() { using var transaction = _context.Database.BeginTransaction(); try { var file = Request.Form.Files[0]; string folderName = "Upload"; string newPath = Path.Combine(Guid.NewGuid().ToString() + '_' + folderName); if (!Directory.Exists(newPath)) { Directory.CreateDirectory(newPath); } if (file.Length > 0) { string fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"'); string fullPath = Path.Combine(newPath, fileName); string sFileExtension = Path.GetExtension(file.FileName); ISheet sheet; var list = new List <ScrewCompressorTrainModel>(); using (var stream = new FileStream(fullPath, FileMode.Create)) { file.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { NPOI.XSSF.UserModel.XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; transaction.CreateSavepoint("amin"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { var obj = new ScrewCompressorTrainModel(); IRow row = sheet.GetRow(i); var PS1 = row.GetCell(0).ToString(); var PD1 = row.GetCell(1).ToString(); var PS2 = row.GetCell(2).ToString(); var PD2 = row.GetCell(3).ToString(); var TS1 = row.GetCell(4).ToString(); var TD1 = row.GetCell(5).ToString(); var TS2 = row.GetCell(6).ToString(); var TD2 = row.GetCell(7).ToString(); obj.TenantId = 1; obj.PS1 = Convert.ToDecimal(PS1); obj.PD1 = Convert.ToDecimal(PD1); obj.PS2 = Convert.ToDecimal(PS2); obj.PD2 = Convert.ToDecimal(PD2); obj.TS1 = Convert.ToDecimal(TS1); obj.TD1 = Convert.ToDecimal(TD1); obj.TS2 = Convert.ToDecimal(TS2); obj.TD2 = Convert.ToDecimal(TD2); list.Add(obj); _context.ScrewCompressureTrainData.Add(obj); _context.SaveChangesAsync(); } transaction.Commit(); _context.Database.CloseConnection(); } } return(Ok("Done")); } catch (Exception exe) { transaction.RollbackToSavepoint("amin"); _context.Database.CloseConnection(); return(BadRequest(exe.Message)); } }
/// <summary> /// 根据Excel格式读取Excel /// </summary> /// <param name="stream">文件流</param> /// <param name="type">Excel格式枚举类型,xls/xlsx</param> /// <param name="sheetName">表名,默认取第一张</param> /// <returns>DataTable</returns> public static DataTable ImportExcel(Stream stream, ExcelExtType type, string sheetName) { DataTable dt = new DataTable(); IWorkbook workbook; try { //xls使用HSSFWorkbook类实现,xlsx使用XSSFWorkbook类实现 switch (type) { case ExcelExtType.xlsx: workbook = new XSSFWorkbook(stream); break; default: workbook = new HSSFWorkbook(stream); break; } ISheet sheet = null; //获取工作表 默认取第一张 if (string.IsNullOrWhiteSpace(sheetName)) { sheet = workbook.GetSheetAt(0); } else { sheet = workbook.GetSheet(sheetName); } if (sheet == null) { return(null); } IEnumerator rows = sheet.GetRowEnumerator(); #region 获取表头 IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); if (cell != null) { dt.Columns.Add(cell.ToString()); } else { dt.Columns.Add(""); } } #endregion #region 获取内容 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { //判断单元格是否为日期格式 if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j))) { if (row.GetCell(j).DateCellValue.Year >= 1970) { dataRow[j] = row.GetCell(j).DateCellValue.ToString(); } else { dataRow[j] = row.GetCell(j).ToString(); } } else { dataRow[j] = row.GetCell(j).ToString(); } } } dt.Rows.Add(dataRow); } #endregion } catch (Exception ex) { dt = null; } finally { //if (stream != null) //{ // stream.Close(); // stream.Dispose(); //} } return(dt); }
/// <summary> /// Excel添加行数据 /// </summary> /// <param name="sheet"></param> /// <param name="model"></param> private void AddRow(ISheet sheet, AkServerReportModel model, List <int> ltStyle) { IRow dataRow = sheet.CreateRow(rowIndex); int column = 0; dataRow.Height = 18 * 20; ICell newCel0 = dataRow.CreateCell(column++); ICell newCel1 = dataRow.CreateCell(column++); ICell newCel2 = dataRow.CreateCell(column++); ICell newCel3 = dataRow.CreateCell(column++); ICell newCel4 = dataRow.CreateCell(column++); ICell newCel5 = dataRow.CreateCell(column++); ICell newCel6 = dataRow.CreateCell(column++); ICell newCel7 = dataRow.CreateCell(column++); ICell newCel8 = dataRow.CreateCell(column++); ICell newCel9 = dataRow.CreateCell(column++); ICell newCel10 = dataRow.CreateCell(column++); ICell newCel11 = dataRow.CreateCell(column++); ICell newCel12 = dataRow.CreateCell(column++); ICell newCel13 = dataRow.CreateCell(column++); ICell newCel14 = dataRow.CreateCell(column++); ICell newCel15 = dataRow.CreateCell(column++); ICell newCel16 = dataRow.CreateCell(column++); ICell newCel17 = dataRow.CreateCell(column++); ICell newCel18 = dataRow.CreateCell(column++); ICell newCel19 = dataRow.CreateCell(column++); ICell newCel20 = dataRow.CreateCell(column++); ICell newCel21 = dataRow.CreateCell(column++); newCel0.SetCellValue(model.TimeInterval); newCel1.SetCellValue(model.HourTime); newCel2.SetCellValue(model.SalesTurnover); newCel3.SetCellValue(model.OrderNum); newCel4.SetCellValue(model.AvgProduct); newCel5.SetCellValue(model.Item1); newCel6.SetCellValue(model.Item2); newCel7.SetCellValue(model.Item3); newCel8.SetCellValue(model.Item4); newCel9.SetCellValue(model.Item5); newCel10.SetCellValue(model.Item6); newCel11.SetCellValue(model.Item11); newCel12.SetCellValue(model.Item12); newCel13.SetCellValue(model.Item13); newCel14.SetCellValue(model.Item14); newCel15.SetCellValue(model.Item15); newCel16.SetCellValue(model.Item16); newCel17.SetCellValue(model.Item21); newCel18.SetCellValue(model.Item22); newCel19.SetCellValue(model.Item23); newCel20.SetCellValue(model.Item31); newCel21.SetCellValue(model.Item32); if (ltStyle != null && ltStyle.Contains(rowIndex)) { newCel0.CellStyle = this.StyleForegroundThin; newCel1.CellStyle = this.StyleForegroundThin; newCel2.CellStyle = this.StyleForegroundThin; newCel3.CellStyle = this.StyleForegroundThin; newCel4.CellStyle = this.StyleForegroundThin; newCel5.CellStyle = this.StyleForegroundThin; newCel6.CellStyle = this.StyleForegroundThin; newCel7.CellStyle = this.StyleForegroundThin; newCel8.CellStyle = this.StyleForegroundThin; newCel9.CellStyle = this.StyleForegroundThin; newCel10.CellStyle = this.StyleForegroundThin; newCel11.CellStyle = this.StyleForegroundThin; newCel12.CellStyle = this.StyleForegroundThin; newCel13.CellStyle = this.StyleForegroundThin; newCel14.CellStyle = this.StyleForegroundThin; newCel15.CellStyle = this.StyleForegroundThin; newCel16.CellStyle = this.StyleForegroundThin; newCel17.CellStyle = this.StyleForegroundThin; newCel18.CellStyle = this.StyleForegroundThin; newCel19.CellStyle = this.StyleForegroundThin; newCel20.CellStyle = this.StyleForegroundThin; newCel21.CellStyle = this.StyleForegroundThin; } else { newCel0.CellStyle = this.StyleThin; newCel1.CellStyle = this.StyleThin; newCel2.CellStyle = this.StyleThin; newCel3.CellStyle = this.StyleThin; newCel4.CellStyle = this.StyleThin; newCel5.CellStyle = this.StyleThin; newCel6.CellStyle = this.StyleThin; newCel7.CellStyle = this.StyleThin; newCel8.CellStyle = this.StyleThin; newCel9.CellStyle = this.StyleThin; newCel10.CellStyle = this.StyleThin; newCel11.CellStyle = this.StyleThin; newCel12.CellStyle = this.StyleThin; newCel13.CellStyle = this.StyleThin; newCel14.CellStyle = this.StyleThin; newCel15.CellStyle = this.StyleThin; newCel16.CellStyle = this.StyleThin; newCel17.CellStyle = this.StyleThin; newCel18.CellStyle = this.StyleThin; newCel19.CellStyle = this.StyleThin; newCel20.CellStyle = this.StyleThin; newCel21.CellStyle = this.StyleThin; } rowIndex++; }
public string Serialize(IRow row) { return(JsonConvert.SerializeObject(row.ToFriendlyExpandoObject(_fields))); }
public override void RemoveRow(int index) { IRow remove = sheet.GetRow(index); sheet.RemoveRow(remove); }
/// <summary> /// 将 Excel 中的数据导入到 DataTable 中 /// </summary> /// <param name="sheetName">工作表名称</param> /// <param name="isFirstRowColumn">第一行是否是标题列</param> /// <returns>返回的 DataTable</returns> public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) { ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.ToLower().IndexOf(".xlsx") > 0) { workbook = new XSSFWorkbook(fs); } else if (fileName.ToLower().IndexOf(".xls") > 0) { workbook = new HSSFWorkbook(fs); } if (workbook == null) { throw new Exception("无法根据文件的扩展名区别 Excel 的版本,创建 Excel 工作簿失败"); } if (sheetName != "") { sheet = workbook.GetSheet(sheetName); } else { sheet = workbook.GetSheetAt(0); } if (sheet == null) { if (sheetName == "") { throw new Exception( string.Format( "文件 [{0}] 中没有任何工作表", fileName)); } else { throw new Exception( string.Format( "文件 [{0}] 中没有找到名为 [{1}] 的工作表", fileName, sheetName)); } } IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; // 一行最后一个 Cell 的编号,就是总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; i++) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } // 最后一行的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; i++) { IRow row = sheet.GetRow(i); if (row == null) // 没有数据的行默认是 null { continue; } DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = GetValueType(row.GetCell(j)).ToString(); } } data.Rows.Add(dataRow); } return(data); } catch (Exception error) { throw error; } }
private static Delegate GetGetterAsDelegateCore <TValue>(IRow row, int col) { return(row.GetGetter <TValue>(col)); }
protected override RowCursorState InitializeState(IRow input) { return(new RowCursorState(_truncationLevel)); }
public RowImpl(IRow row, int col) { _row = row; _col = col; }
public static object CopyNative(IValueManager manager, Schema.IDataType dataType, object tempValue) { // This code is duplicated in the descendent CopyNative methods for performance if (tempValue == null) { return(tempValue); } Schema.IScalarType scalarType = dataType as Schema.IScalarType; if (scalarType != null) { if (tempValue is StreamID) { return(manager.StreamManager.Reference((StreamID)tempValue)); } ICloneable cloneable = tempValue as ICloneable; if (cloneable != null) { return(cloneable.Clone()); } if (scalarType.IsCompound) { return(CopyNative(manager, scalarType.CompoundRowType, tempValue)); } return(tempValue); } Schema.IRowType rowType = dataType as Schema.IRowType; if (rowType != null) { NativeRow nativeRow = (NativeRow)tempValue; NativeRow newRow = new NativeRow(rowType.Columns.Count); for (int index = 0; index < rowType.Columns.Count; index++) { #if USEDATATYPESINNATIVEROW newRow.DataTypes[index] = nativeRow.DataTypes[index]; newRow.Values[index] = CopyNative(manager, nativeRow.DataTypes[index], nativeRow.Values[index]); #else newRow.Values[index] = CopyNative(AManager, rowType.Columns[index].DataType, nativeRow.Values[index]); #endif } return(newRow); } Schema.IListType listType = dataType as Schema.IListType; if (listType != null) { NativeList nativeList = (NativeList)tempValue; NativeList newList = new NativeList(); for (int index = 0; index < nativeList.DataTypes.Count; index++) { newList.DataTypes.Add(nativeList.DataTypes[index]); newList.Values.Add(CopyNative(manager, nativeList.DataTypes[index], nativeList.Values[index])); } return(newList); } Schema.ITableType tableType = dataType as Schema.ITableType; if (tableType != null) { NativeTable nativeTable = (NativeTable)tempValue; NativeTable newTable = new NativeTable(manager, nativeTable.TableVar); using (Scan scan = new Scan(manager, nativeTable, nativeTable.ClusteredIndex, ScanDirection.Forward, null, null)) { scan.Open(); while (scan.Next()) { using (IRow row = scan.GetRow()) { newTable.Insert(manager, row); } } } return(newTable); } Schema.ICursorType cursorType = dataType as Schema.ICursorType; if (cursorType != null) { return(tempValue); } var runtimeType = manager.GetRuntimeType(tempValue); if (runtimeType != null) { return(CopyNative(manager, runtimeType, tempValue)); } throw new RuntimeException(RuntimeException.Codes.InvalidValueType, dataType == null ? "<null>" : dataType.GetType().Name); }
public static GetterFactory Create(IRow row, int col) { return(new RowImpl(row, col)); }
public void SetSheetOrderHSSF() { IWorkbook wb = new HSSFWorkbook(); ISheet s1 = wb.CreateSheet("first sheet"); ISheet s2 = wb.CreateSheet("other sheet"); IName name1 = wb.CreateName(); name1.NameName = (/*setter*/ "name1"); name1.RefersToFormula = (/*setter*/ "'first sheet'!D1"); IName name2 = wb.CreateName(); name2.NameName = (/*setter*/ "name2"); name2.RefersToFormula = (/*setter*/ "'other sheet'!C1"); IRow s1r1 = s1.CreateRow(2); ICell c1 = s1r1.CreateCell(3); c1.SetCellValue(30); ICell c2 = s1r1.CreateCell(2); c2.CellFormula = (/*setter*/ "SUM('other sheet'!C1,'first sheet'!C1)"); IRow s2r1 = s2.CreateRow(0); ICell c3 = s2r1.CreateCell(1); c3.CellFormula = (/*setter*/ "'first sheet'!D3"); ICell c4 = s2r1.CreateCell(2); c4.CellFormula = (/*setter*/ "'other sheet'!D3"); // conditional formatting ISheetConditionalFormatting sheetCF = s1.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule( ComparisonOperator.Between, "'first sheet'!D1", "'other sheet'!D1"); IConditionalFormattingRule[] cfRules = { rule1 }; CellRangeAddress[] regions = { new CellRangeAddress(2, 4, 0, 0), // A3:A5 }; sheetCF.AddConditionalFormatting(regions, cfRules); wb.SetSheetOrder("other sheet", 0); // names Assert.AreEqual("'first sheet'!D1", wb.GetName("name1").RefersToFormula); Assert.AreEqual("'other sheet'!C1", wb.GetName("name2").RefersToFormula); // cells Assert.AreEqual("SUM('other sheet'!C1,'first sheet'!C1)", c2.CellFormula); Assert.AreEqual("'first sheet'!D3", c3.CellFormula); Assert.AreEqual("'other sheet'!D3", c4.CellFormula); // conditional formatting IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0); Assert.AreEqual("'first sheet'!D1", cf.GetRule(0).Formula1); Assert.AreEqual("'other sheet'!D1", cf.GetRule(0).Formula2); }
/// <summary> /// Given the item type, typeDst, a row, and column index, return a ValueGetter{VBuffer{TDst}} for the /// vector-valued column with a conversion to a vector of typeDst, if needed. /// </summary> public static ValueGetter <VBuffer <TDst> > GetVecGetterAs <TDst>(PrimitiveType typeDst, IRow row, int col) { Contracts.CheckValue(typeDst, nameof(typeDst)); Contracts.CheckParam(typeDst.RawType == typeof(TDst), nameof(typeDst)); Contracts.CheckValue(row, nameof(row)); Contracts.CheckParam(0 <= col && col < row.Schema.ColumnCount, nameof(col)); Contracts.CheckParam(row.IsColumnActive(col), nameof(col), "column was not active"); var typeSrc = row.Schema.GetColumnType(col); Contracts.Check(typeSrc.IsVector, "Source column type must be vector"); Func <VectorType, PrimitiveType, GetterFactory, ValueGetter <VBuffer <TDst> > > del = GetVecGetterAsCore <int, TDst>; var methodInfo = del.GetMethodInfo().GetGenericMethodDefinition().MakeGenericMethod(typeSrc.ItemType.RawType, typeof(TDst)); return((ValueGetter <VBuffer <TDst> >)methodInfo.Invoke(null, new object[] { typeSrc, typeDst, GetterFactory.Create(row, col) })); }
public override IRow Operate(IRow row) { row[Context.Field] = _transform(row); Increment(); return(row); }
private static ValueGetter <StringBuilder> GetGetterAsStringBuilderCore <TSrc>(ColumnType typeSrc, IRow row, int col) { Contracts.Assert(typeof(TSrc) == typeSrc.RawType); var getter = row.GetGetter <TSrc>(col); var conv = Conversions.Instance.GetStringConversion <TSrc>(typeSrc); var src = default(TSrc); return ((ref StringBuilder dst) => { getter(ref src); conv(in src, ref dst); }); }
/// <summary> /// 从excel中所有sheet中读取数据 /// </summary> /// <param name="ins">输入流</param> /// <param name="headRowIndex">标题行索引 默认为第6行</param> /// <param name="listSheet">所有有数据的Sheet</param> /// <returns>DataTable</returns> public static DataSet GetDataSetFromExcel(Stream ins, List <string> sheetNames, int headRowIndex, out List <ISheet> listSheet) { IWorkbook workbook = InitWorkBook(ins); DataSet ds = new DataSet(); List <ISheet> sheets = new List <ISheet>(); if (workbook.NumberOfSheets > 0) { //读取标题行 IRow row = null; ICell cell = null; ISheet fSheet = null; DataTable dt = null; foreach (string sheetName in sheetNames) { fSheet = workbook.GetSheet(sheetName); if (fSheet == null || fSheet.LastRowNum < headRowIndex) { continue; } dt = new DataTable(); dt.TableName = sheetName; row = fSheet.GetRow(headRowIndex); object objColumnName = null; for (int i = 0, length = row.LastCellNum; i < length; i++) { cell = row.GetCell(i); if (cell == null) { continue; } objColumnName = GetCellVale(cell); if (objColumnName != null) { dt.Columns.Add(objColumnName.ToString().Trim()); } else { dt.Columns.Add(""); } } //读取数据行 object[] entityValues = null; int columnCount = dt.Columns.Count; for (int i = headRowIndex + 1, length = fSheet.LastRowNum + 1; i < length; i++) { row = fSheet.GetRow(i); if (row == null) { continue; } entityValues = new object[columnCount]; //用于判断是否为空行 bool isHasData = false; int dataColumnLength = row.LastCellNum < columnCount ? row.LastCellNum : columnCount; for (int j = 0; j < dataColumnLength; j++) { cell = row.GetCell(j); if (cell == null) { continue; } entityValues[j] = GetCellVale(cell); if (!isHasData && j < columnCount && entityValues[j] != null) { isHasData = true; } } if (isHasData) { dt.Rows.Add(entityValues); } } ds.Tables.Add(dt); sheets.Add(fSheet); } } listSheet = sheets; return(ds); }
public override IRow Operate(IRow row) { row[Context.Field] = GetCallerIdentity(); return(row); }
/// <summary> /// List导出到Excel的MemoryStream /// </summary> /// <param name="list">数据源</param> /// <param name="sHeaderText">表头文本</param> /// <param name="columns">需要导出的属性</param> private MemoryStream CreateExportMemoryStream(List <T> list, string sHeaderText, string[] columns) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); Type type = typeof(T); PropertyInfo[] properties = ReflectionHelper.GetProperties(type, columns); ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); #region 取得每列的列宽(最大宽度) int[] arrColWidth = new int[properties.Length]; for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++) { //GBK对应的code page是CP936 arrColWidth[columnIndex] = properties[columnIndex].Name.Length; } #endregion for (int rowIndex = 0; rowIndex < list.Count; rowIndex++) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(sHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1)); } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++) { // 类属性如果有Description就用Description当做列名 DescriptionAttribute customAttribute = (DescriptionAttribute)Attribute.GetCustomAttribute(properties[columnIndex], typeof(DescriptionAttribute)); string description = properties[columnIndex].Name; if (customAttribute != null) { description = customAttribute.Description; } headerRow.CreateCell(columnIndex).SetCellValue(description); headerRow.GetCell(columnIndex).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256); } } #endregion } #endregion #region 填充内容 ICellStyle contentStyle = workbook.CreateCellStyle(); contentStyle.Alignment = HorizontalAlignment.Left; IRow dataRow = sheet.CreateRow(rowIndex + 2); // 前面2行已被占用 for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++) { ICell newCell = dataRow.CreateCell(columnIndex); newCell.CellStyle = contentStyle; string drValue = properties[columnIndex].GetValue(list[rowIndex], null).ParseToString(); switch (properties[columnIndex].PropertyType.ToString()) { case "System.String": newCell.SetCellValue(drValue); break; case "System.DateTime": case "System.Nullable`1[System.DateTime]": newCell.SetCellValue(drValue.ParseToDateTime()); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": case "System.Nullable`1[System.Boolean]": newCell.SetCellValue(drValue.ParseToBool()); break; case "System.Byte": case "System.Nullable`1[System.Byte]": case "System.Int16": case "System.Nullable`1[System.Int16]": case "System.Int32": case "System.Nullable`1[System.Int32]": newCell.SetCellValue(drValue.ParseToInt()); break; case "System.Int64": case "System.Nullable`1[System.Int64]": newCell.SetCellValue(drValue.ParseToString()); break; case "System.Double": case "System.Nullable`1[System.Double]": newCell.SetCellValue(drValue.ParseToDouble()); break; case "System.Decimal": case "System.Nullable`1[System.Decimal]": newCell.SetCellValue(drValue.ParseToDouble()); break; case "System.DBNull": newCell.SetCellValue(string.Empty); break; default: newCell.SetCellValue(string.Empty); break; } } #endregion } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); workbook.Close(); ms.Flush(); ms.Position = 0; return(ms); } }
/// <summary> /// 将 DataTable 数据导入到 Excel 中 /// </summary> /// <param name="data">要导入的数据</param> /// <param name="sheetName">Excel 文件工作表的名称</param> /// <param name="isColumnWritten">是否导入 DatatTable 的列名</param> /// <returns>导入的数据行数(包括列名)</returns> public int DataTableToExcel( DataTable data, string sheetName, bool isColumnWritten) { int count = 0; ISheet sheet = null; fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); if (fileName.ToLower().IndexOf(".xlsx") > 0) { workbook = new XSSFWorkbook(); } else if (fileName.ToLower().IndexOf(".xls") > 0) { workbook = new HSSFWorkbook(); } try { if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } else { throw new Exception("无法根据文件的扩展名区别 Excel 的版本,创建 Excel 工作簿失败"); } // 写入 DataTable 的 if (isColumnWritten) { IRow row = sheet.CreateRow(0); for (int j = 0; j < data.Columns.Count; j++) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } for (int i = 0; i < data.Rows.Count; i++) { IRow row = sheet.CreateRow(count); for (int j = 0; j < data.Columns.Count; j++) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } count++; } workbook.Write(fs); // 写入到 Excel return(count); } catch (Exception error) { throw error; } }
private static ValueGetter <TDst> GetGetterAsCore <TSrc, TDst>(ColumnType typeSrc, ColumnType typeDst, IRow row, int col) { Contracts.Assert(typeof(TSrc) == typeSrc.RawType); Contracts.Assert(typeof(TDst) == typeDst.RawType); var getter = row.GetGetter <TSrc>(col); bool identity; var conv = Conversions.Instance.GetStandardConversion <TSrc, TDst>(typeSrc, typeDst, out identity); if (identity) { Contracts.Assert(typeof(TSrc) == typeof(TDst)); return((ValueGetter <TDst>)(Delegate) getter); } var src = default(TSrc); return ((ref TDst dst) => { getter(ref src); conv(in src, ref dst); }); }
/// <summary> /// Returns a row that is a deep in-memory copy of an input row. Note that inactive /// columns are allowed in this row, and their activity or inactivity will be reflected /// in the output row. Note that the deep copy includes a copy of the metadata as well. /// </summary> /// <param name="row">The input row</param> /// <returns>A deep in-memory copy of the input row</returns> public static IRow CloneRow(IRow row) { Contracts.CheckValue(row, nameof(row)); return(RowColumnUtils.GetRow(null, Utils.BuildArray(row.Schema.ColumnCount, c => RowColumnUtils.GetColumn(row, c)))); }