Example #1
0
        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;
        }
Example #2
0
        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();
        }
Example #3
0
        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);
            }
        }
Example #4
0
        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();
        }
Example #5
0
 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);
 }
Example #6
0
        /// <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();
        }
Example #7
0
        /// <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();
            }
        }
Example #8
0
        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;
        }
Example #9
0
 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;
 }
Example #10
0
 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;
 }
Example #11
0
        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;

        }
Example #12
0
 // 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;
 }
Example #13
0
        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;
        }
Example #14
0
 public DunaGridRowSelector(IRow row)
     : base()
 {
     this.Row = row;
     InitializeComponent();
     this.HoverAreaPadding = new Padding(0, 3, 0, 3);
     DoubleBuffered = true;
 }
Example #15
0
 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));
 }
Example #16
0
 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);
        }
Example #18
0
 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>()
     };
 }
Example #20
0
 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);
 }
Example #21
0
        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;
        }
Example #23
0
        /// <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;
            }
        }
Example #24
0
        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();
        }
Example #25
0
        /// <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);
		}
Example #28
0
 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);
 }
Example #29
0
        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;
        }
Example #30
0
        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")
 {
 }
Example #32
0
        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);
        }
Example #33
0
        /// <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);
        }
Example #34
0
        /// <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);
            }
        }
Example #35
0
        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)));
 }
Example #38
0
        /// <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);
            }
        }
Example #39
0
        /// <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));
            }
        }
Example #41
0
        /// <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)));
 }
Example #44
0
        public override void RemoveRow(int index)
        {
            IRow remove = sheet.GetRow(index);

            sheet.RemoveRow(remove);
        }
Example #45
0
        /// <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;
 }
Example #49
0
        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));
 }
Example #51
0
        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) }));
        }
Example #53
0
 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);
            });
        }
Example #55
0
        /// <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);
 }
Example #57
0
        /// <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);
            }
        }
Example #58
0
        /// <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))));
 }