Beispiel #1
0
        public void CreatePreserveCell(ExcelCell cell, IXlsxDocumentBuilder document)
        {
            string _PreservationSheetName_ = "PreservationSheet";

            var preservationSheet = document.GetPreservationSheet() as XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder;

            if (preservationSheet == null)
            {
                preservationSheet = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder(_PreservationSheetName_, false, true, true);
                document.AppendSheet(preservationSheet);
            }
            else
            {
                preservationSheet.AddAndActivateNewTable(_PreservationSheetName_);
            }

            preservationSheet.AppendColumnHeaderRowItem(cell.CellHeader);


            Dictionary <string, object> resolveRow = new Dictionary <string, object>();

            resolveRow.Add(cell.CellHeader, cell.CellValue);

            PopulateRows(resolveRow.Keys.ToList(), resolveRow, preservationSheet);
        }
        public void As_converts_cell_value_type_to_generic_argument_type()
        {
            var newCell = new ExcelCell("2");

            Assert.AreEqual(2, newCell.Cast <int>());
            Assert.AreEqual(typeof(int), newCell.Cast <int>().GetType());
        }
        private static ExcelCellMatrix ProcessListToCellMatrix(ProcessList processList)
        {
            var numRows = 1 + processList.Processes.Count();
            var c       = 0;

            ExcelCell[][] cells = new ExcelCell[numRows][];
            var           initR = 0;
            var           r     = initR;

            foreach (var process in processList.Processes)
            {
                cells[r] = new ExcelCell[c + 1];
                var macro = string.Format(@"=EXEC(""{0}"")", EscapeString(process.CmdLine.ToString()));
                if (r == initR)
                {
                    cells[r][c] = new ExcelCell(value: macro, name: "Auto_open");
                }
                else
                {
                    cells[r][c] = new ExcelCell(value: macro);
                }
                r++;
            }
            cells[r]    = new ExcelCell[c + 1];
            cells[r][c] = new ExcelCell(value: "=HALT()");
            return(new ExcelCellMatrix(cells));
        }
Beispiel #4
0
        //创建一个空的表结构
        private DataTable createDataTable()
        {
            ExcelWorksheet sheet = _ExcelObj.Worksheets.ActiveWorksheet;

            if (sheet.Rows.Count == 0)
            {
                return(null);
            }
            ExcelRow  headRow = sheet.Rows[0];
            DataTable newDt   = new DataTable();
            int       aCount  = headRow.AllocatedCells.Count;

            for (int i = 0; i < aCount; i++)
            {
                ExcelCell cell = headRow.AllocatedCells[i];
                if (cell.Value == null)
                {
                    continue;
                }
                DataColumn dc = new DataColumn(cell.Value.ToString().Trim());
                dc.Caption = cell.Value.ToString().Trim();
                _ColumnAndIndex.Add(i, dc);
                newDt.Columns.Add(dc);
            }
            return(newDt);
        }
Beispiel #5
0
        public byte[] BuildExcelFile(int numberOfWorksheet, int numberOfColumns, int numberOfRows)
        {
            for (var worksheetIndex = 0; worksheetIndex < numberOfWorksheet; worksheetIndex++)
            {
                var worksheetName = $"worksheet_{worksheetIndex}";

                var cells = new ExcelCell[numberOfColumns];
                for (var columnIndex = 0; columnIndex < numberOfColumns; columnIndex++)
                {
                    cells[columnIndex] = new ExcelCell($"cell_name_{columnIndex}", ExcelSheetStyleIndex.Bold);
                }
                _excelBuilder.AddRowToWorksheet(worksheetName, cells);

                for (var rowIndex = 0; rowIndex < numberOfRows; rowIndex++)
                {
                    cells = new ExcelCell[numberOfColumns];
                    for (var columnIndex = 0; columnIndex < numberOfColumns; columnIndex++)
                    {
                        cells[columnIndex] = new ExcelCell($"cell_value_{rowIndex}_{columnIndex}", ExcelSheetStyleIndex.Default);
                    }
                    _excelBuilder.AddRowToWorksheet(worksheetName, cells);
                }
            }

            return(_excelBuilder.FinishAndGetExcel());
        }
        //根据Caption 找到datatable中的列,并把和Excel 中对应列的Index 一起存储在hashTable中作为
        //获取行导入时对应的列。
        private bool setColumnAndIndex(DataTable dt, ExcelFile excelObj, Hashtable columnAndIndex)
        {
            ExcelWorksheet sheet = excelObj.Worksheets.ActiveWorksheet;

            if (sheet.Rows.Count == 0)
            {
                return(false);
            }
            ExcelRow headRow = sheet.Rows[0];
            int      aCount  = headRow.AllocatedCells.Count;

            for (int i = 0; i < aCount; i++)
            {
                ExcelCell cell = headRow.AllocatedCells[i];
                if (cell.Value == null)
                {
                    continue;
                }
                foreach (DataColumn dc in dt.Columns)
                {
                    if (string.Compare(dc.Caption, cell.Value.ToString(), true) != 0)
                    {
                        continue;
                    }
                    columnAndIndex.Add(i, dc);
                    break;
                }
            }
            return(columnAndIndex.Count > 0);
        }
        public DataTable GetSpreadsheet(string sheetName, ExcelCell startingCell, ExcelCell endingCell)
        {
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException("sheetName", "sheetName cannot be null or empty");
            }
            DataTable dtResult = new DataTable();

            using (var connection = new OleDbConnection(GetConnectionString())) {
                connection.Open();
                if (!sheetName.EndsWith("$"))
                {
                    sheetName += "$";
                }
                using (var command = new OleDbCommand("Select * From [" + sheetName + startingCell.Cell + ":" + endingCell.Cell + "]", connection)) {
                    using (var adapter = new OleDbDataAdapter(command)) {
                        using (DataSet ds = new DataSet()) {
                            adapter.Fill(ds);
                            if (ds.Tables.Count > 0)
                            {
                                dtResult = ds.Tables[0];
                            }
                            ds.Dispose();
                        }
                    }
                }
                connection.Close();
            }
            return(dtResult);
        }
Beispiel #8
0
        public static void writeFormattedText(string text, ExcelCell cell)
        {
            StringBuilder sb = new StringBuilder();

            foreach (System.Text.RegularExpressions.Match pMatch in parameterHeaderRegex.Matches(text))
            {
                foreach (System.Text.RegularExpressions.Match aMatch in attributeRegex.Matches(pMatch.Value))
                {
                    switch (aMatch.Groups["name"].Value)
                    {
                    case "text":
                        sb.Append(aMatch.Groups["value"].Value);
                        break;

                    //case "font":
                    //    cell.Style.Font.Name = aMatch.Groups["value"].Value;
                    //    break;
                    //case "index":
                    //    cell.Style.Font.ScriptPosition = (aMatch.Groups["value"].Value == "sup") ? ScriptPosition.Superscript : ScriptPosition.Subscript;
                    //    break;
                    case "style":
                        switch (aMatch.Groups["value"].Value)
                        {
                        case "underline": cell.Style.Font.UnderlineStyle = UnderlineStyle.Single; break;

                        case "bold": cell.Style.Font.Weight = 5; break;

                        case "italic": cell.Style.Font.Italic = true; break;
                        }
                        break;
                    }
                }
            }
            cell.Value = sb.ToString();
        }
Beispiel #9
0
    private ExcelCell ParseExcelCell(ICell c, PropertyInfo info, ExcelRow row)
    {
        string    content = c == null ? string.Empty : c.ToString();
        ExcelCell cell    = new ExcelCell(row, content, info);

        return(cell);
    }
Beispiel #10
0
 private void cleanTable(string worksheetName)
 {
     while (true)
     {
         int i = 0;
         for (; i < m_reportTable.WorkSheets[worksheetName].Count; ++i)
         {
             for (int x = 0; x < m_reportTable.WorkSheets[worksheetName][i].Count; ++x)
             {
                 ExcelCell cell = m_reportTable.WorkSheets[worksheetName][i][x];
                 if (cell.Tag != null)
                 {
                     m_reportTable.WorkSheets[worksheetName][i].RemoveAt(x);
                     if (m_reportTable.WorkSheets[worksheetName][i].Count == 0)
                     {
                         m_reportTable.WorkSheets[worksheetName].RemoveAt(i);
                         m_reportTable.WorkSheets[worksheetName].ForceUpdateRowNumbers();
                         break;
                     }
                 }
             }
         }
         if (i >= m_reportTable.WorkSheets[worksheetName].Count - 1)
         {
             break;
         }
     }
 }
Beispiel #11
0
        public static int QuickWrite(ExcelSheet excelSheet, string filePath, ExcelVersions version = ExcelVersions.Xls)
        {
            using (FileStream fs = File.Open(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                IWorkbook workbook = OpenWrite(version);
                ISheet    sheet    = workbook.CreateSheet(string.IsNullOrEmpty(excelSheet.Name) ? "sheet1" : excelSheet.Name);

                for (int i = 0; i < excelSheet.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(i);

                    ExcelRow excelRow = excelSheet.Rows[i];

                    for (int j = 0; j < excelRow.Cells.Count; j++)
                    {
                        ExcelCell excelCell = excelRow.GetCell(j);

                        ICell cell = CreateCell(row, j, excelCell);
                    }
                }

                workbook.Write(fs);
                fs.Close();
                return(DotNETCode.SUCCESS);
            }
        }
Beispiel #12
0
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile ef = ExcelFile.Load("NumberFormat.xlsx");

        var ws = ef.Worksheets[0];

        ws.Cells[0, 2].Value             = "ExcelCell.Value";
        ws.Columns[2].Style.NumberFormat = "@";

        ws.Cells[0, 3].Value             = "CellStyle.NumberFormat";
        ws.Columns[3].Style.NumberFormat = "@";

        ws.Cells[0, 4].Value             = "ExcelCell.GetFormattedValue()";
        ws.Columns[4].Style.NumberFormat = "@";

        for (int i = 1; i < ws.Rows.Count; i++)
        {
            ExcelCell sourceCell = ws.Cells[i, 0];

            ws.Cells[i, 2].Value = sourceCell.Value == null ? null : sourceCell.Value.ToString();
            ws.Cells[i, 3].Value = sourceCell.Style.NumberFormat;
            ws.Cells[i, 4].Value = sourceCell.GetFormattedValue();
        }

        // Auto-fit columns
        for (int i = 0; i < 5; i++)
        {
            ws.Columns[i].AutoFit();
        }

        ef.Save("Number Format.xlsx");
    }
Beispiel #13
0
        public void TranslatesAddress(string address, int expectedColumn, int expectedRow)
        {
            var cell = new ExcelCell(address, "");

            Assert.AreEqual(expectedColumn, cell.Column);
            Assert.AreEqual(expectedRow, cell.Row);
        }
Beispiel #14
0
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile      ef = new ExcelFile();
        ExcelWorksheet ws = ef.Worksheets.Add("Hyperlinks");

        ws.Cells["A1"].Value = "Hyperlink examples:";

        ExcelCell cell = ws.Cells["B3"];

        cell.Value = "GemBoxSoftware";
        cell.Style.Font.UnderlineStyle = UnderlineStyle.Single;
        cell.Style.Font.Color          = SpreadsheetColor.FromName(ColorName.Blue);
        cell.Hyperlink.Location        = "https://www.gemboxsoftware.com";
        cell.Hyperlink.IsExternal      = true;

        cell       = ws.Cells["B5"];
        cell.Value = "Jump";
        cell.Style.Font.UnderlineStyle = UnderlineStyle.Single;
        cell.Style.Font.Color          = SpreadsheetColor.FromName(ColorName.Blue);
        cell.Hyperlink.ToolTip         = "This is tool tip! This hyperlink jumps to E1.";
        cell.Hyperlink.Location        = ws.Name + "!E1";

        ws.Cells["E1"].Value = "Destination";

        cell         = ws.Cells["B8"];
        cell.Formula = "=HYPERLINK(\"https://www.gemboxsoftware.com/spreadsheet/examples/excel-cell-hyperlinks/207\", \"Example of HYPERLINK formula\")";
        cell.Style.Font.UnderlineStyle = UnderlineStyle.Single;
        cell.Style.Font.Color          = SpreadsheetColor.FromName(ColorName.Blue);

        ef.Save("Hyperlinks.xlsx");
    }
        /// <summary>
        ///     Adds the selected field to the bottom of the excel columns
        /// </summary>
        public void AddFieldToExcelColumns()
        {
            string field = this.SelectedField;

            if (this.SelectedField == "-TEXT-")
            {
                TextPromptView textWindow = new TextPromptView
                {
                    DataContext = new TextPromptViewModel("Add Field", "Field", "string")
                };
                textWindow.ShowDialog();
                if (textWindow.DialogResult == true)
                {
                    field = '"' + (textWindow.DataContext as TextPromptViewModel).Field1Value + '"';
                }
                else
                {
                    return;
                }
            }

            ExcelCell newCell = new ExcelCell(
                this.ExcelId,
                this.ExcelColumns.Count + 1,
                field, "",
                this.ExcelCustomer,
                ExcelService.ReturnColumLetter(this.ExcelColumns.Count + 1));

            this.ExcelColumns.Add(newCell);
        }
Beispiel #16
0
        public void TestWrite()
        {
            string file = Path.GetTempFileName() + ".xls";

            using (ExcelDocument doc = ExcelDocument.CreateDocument())
            {
                ExcelWorksheet worksheet = doc.CreateWorksheet("Test");

                ExcelCell cell = worksheet.GetCellAt(new ExcelCellName(new ColumnLetter(Letter.A), 1));
                cell.Text = "Hello World";

                ExcelFormatter formatter = worksheet.GetFormatterAt(
                    new ExcelCellName(new ColumnLetter(Letter.A), 1),
                    new ExcelCellName(new ColumnLetter(Letter.C), 3));
                formatter.Background  = Color.Red;
                formatter.Foreground  = Color.Yellow;
                formatter.BorderColor = Color.Blue;
                formatter.Font        = new Font("Courier New", 12, FontStyle.Underline | FontStyle.Bold);
                formatter.Merge();
                formatter.ColumnWidth = 100;
                formatter.RowHeight   = 100;

                Assert.AreEqual(Color.FromArgb(0, 255, 0, 0), formatter.Background);
                Assert.AreEqual(Color.FromArgb(0, 255, 255, 0), formatter.Foreground);
                //Assert.AreEqual(Color.FromArgb(0, 0, 0, 255), formatter.BorderColor); TODO

                doc.Save(file);
            }
        }
Beispiel #17
0
        private void setLoad(ExcelCell cell, int j, ref string day, ref string start_hour, ref string stop_hour, ref string curs_hours, ref string pregatire_hours, ref string recuperare_hours, ref string final_hour)
        {
            switch (j)
            {
            case 0:
                day = cell.Value.ToString();
                break;

            case 1:
                start_hour = cell.Value.ToString();
                break;

            case 2:
                stop_hour = cell.Value.ToString();
                break;

            case 3:
                curs_hours = cell.Value.ToString();
                break;

            case 4:
                pregatire_hours = cell.Value.ToString();
                break;

            case 5:
                recuperare_hours = cell.Value.ToString();
                break;

            case 6:
                final_hour = cell.Value.ToString();
                break;
            }
        }
        protected virtual void AddEntry(
            int entryIndex,
            decimal billableHours,
            decimal ratePerHour,
            int weekNumber,
            DateTime startDate,
            DateTime endDate,
            InvoiceConfiguration invoiceConfiguration,
            bool isSplitRate = false)
        {
            Excel.Application excelApplication = ExcelGenerator.GetExcelApplication();

            // Qty...
            ExcelCell qtyStartCell = Helpers.Excel.ToCell(invoiceConfiguration.QtyStartCell);

            excelApplication.Cells[qtyStartCell.Row + entryIndex, qtyStartCell.Column].Value = billableHours;

            // Description...
            ExcelCell descriptionStartCell = Helpers.Excel.ToCell(invoiceConfiguration.DescriptionStartCell);

            string description = string.Format("{0} | {1}", invoiceConfiguration.Description, this.GetTimesheetWeekString(weekNumber, startDate, endDate, isSplitRate));

            excelApplication.Cells[descriptionStartCell.Row + entryIndex, descriptionStartCell.Column].Value = description;

            // PO Number...
            int       PONumber    = invoiceConfiguration.PONumber;
            ExcelCell POStartCell = Helpers.Excel.ToCell(invoiceConfiguration.PONumberStartCell);

            excelApplication.Cells[POStartCell.Row + entryIndex, POStartCell.Column].Value = invoiceConfiguration.PONumber;

            // Rate...
            ExcelCell rateStartCell = Helpers.Excel.ToCell(invoiceConfiguration.RateStartCell);

            excelApplication.Cells[rateStartCell.Row + entryIndex, rateStartCell.Column].Value = ratePerHour;
        }
Beispiel #19
0
        private static ICell CreateCell(IRow row, int cellIndex, ExcelCell excelCell)
        {
            switch (excelCell.Type)
            {
            case ExcelCellTypes.Numberic:
            {
                ICell cell = row.CreateCell(cellIndex, CellType.Numeric);
                cell.SetCellValue((double)excelCell.Value);
                return(cell);
            }

            case ExcelCellTypes.String:
            {
                ICell cell = row.CreateCell(cellIndex, CellType.String);
                cell.SetCellValue(excelCell.Value.ToString());
                return(cell);
            }

            case ExcelCellTypes.Null:
            {
                ICell cell = row.CreateCell(cellIndex, CellType.Blank);
                return(cell);
            }

            default:
                throw new NotSupportedException("不支持的数据格式");
            }
        }
Beispiel #20
0
 public ExcelCellDiff(int columnIndex, int rowIndex, ExcelCell src, ExcelCell dst, ExcelCellStatus status)
 {
     ColumnIndex = columnIndex;
     RowIndex    = rowIndex;
     SrcCell     = src;
     DstCell     = dst;
     Status      = status;
 }
Beispiel #21
0
        /// <summary>
        /// Import the data from the Spreadsheet
        /// </summary>
        /// <returns>Returns a list of JObjects</returns>
        public virtual List <ExcelCell> Import(bool ignoreRowWithEmptyCells = false)
        {
            if (Workbook == null)
            {
                throw new MissingMemberException("Missing Workbook");
            }

            var sheet = Workbook.Worksheets.FirstOrDefault();

            if (sheet == null)
            {
                throw new MissingMemberException("No sheet found");
            }

            for (int i = sheet.Dimension.Start.Row; i <= sheet.Dimension.End.Row; i++)
            {
                for (int j = sheet.Dimension.Start.Column; j <= sheet.Dimension.End.Column; j++)
                {
                    ExcelCell cell = null;
                    if (ignoreRowWithEmptyCells)
                    {
                        if (sheet.Cells[i, j].Value != null && !string.IsNullOrEmpty(sheet.Cells[i, j].Value.ToString()))
                        {
                            continue;
                        }
                        else
                        {
                            cell = new ExcelCell {
                                CellName = sheet.Cells[i, j].Address, CellValue = sheet.Cells[i, j].Value
                            };
                        }
                    }
                    else
                    {
                        cell = new ExcelCell {
                            CellName = sheet.Cells[i, j].Address, CellValue = sheet.Cells[i, j].Value
                        };
                    }

                    try
                    {
                        Sheet.Add(cell);
                    }
                    catch (Exception ex)
                    {
                        ErrorList.AddError(new ValidationErrorItem
                        {
                            Location  = sheet.Cells[i, j].Address,
                            Row       = i,
                            Exception = ex
                        });
                    }
                }
                rowCount++;
            }

            return(Sheet);
        }
    bool DrawNameCell(ExcelCell cell)
    {
        _defaultBgColor     = GUI.backgroundColor;
        GUI.backgroundColor = cell.ruleColor;
        bool dirty = DrawCell(cell);

        GUI.backgroundColor = _defaultBgColor;
        return(dirty);
    }
Beispiel #23
0
 public void AddFields(ExcelCell cell)
 {
     foreach (Match m in FieldRegex.Matches(cell.Value.ToString()))
     {
         var field = new Field(m.Groups[0].Value, cell);
         field.UpdateParameters(Fields);
         Fields.Add(field);
     }
 }
Beispiel #24
0
 public ExcelCellDiff(int columnIndex, int rowIndex, ExcelCell src, ExcelCell dst, ExcelCellStatus status, ExcelCellMergeStatus mergeStatus = ExcelCellMergeStatus.None)
 {
     ColumnIndex = columnIndex;
     RowIndex = rowIndex;
     SrcCell = src;
     DstCell = dst;
     Status = status;
     MergeStatus = mergeStatus;
 }
Beispiel #25
0
 /// <summary>
 /// 执行数据导入的操作。
 /// </summary>
 /// <returns></returns>
 public virtual void Commit()
 {
     try {
         if (!_DataTableIsAutoCreate)
         {
             bool b = setColumnAndIndex(_DtData);
             if (!b)
             {
                 return;
             }
         }
         ExcelWorksheet sheet = _ExcelObj.Worksheets.ActiveWorksheet;
         //表示不存在任何记录。
         if (sheet.Rows.Count == 1)
         {
             return;
         }
         int rowCount = sheet.Rows.Count;
         int colCount = sheet.Rows[0].AllocatedCells.Count;
         if (colCount == 0)
         {
             return;
         }
         for (int i = 1; i < rowCount; i++)
         {
             bool     isEmptyRow = true;
             ExcelRow dataRow    = sheet.Rows[i];
             DataRow  newDr      = _DtData.NewRow();
             for (int j = 0; j < colCount; j++)
             {
                 if (!_ColumnAndIndex.ContainsKey(j))
                 {
                     continue;
                 }
                 DataColumn dc   = (DataColumn)_ColumnAndIndex[j];
                 ExcelCell  cell = dataRow.AllocatedCells[j];
                 object     val  = cell.Value;
                 if (val != null && !string.IsNullOrEmpty(val.ToString().TrimEnd()))
                 {
                     isEmptyRow = false;
                 }
                 OnProviderProgress(dc.ColumnName, ref val);
                 setRowValue(newDr, val, dc.ColumnName, dc.DataType);
             }
             //如果是空行的话,不加到返回的DataTable中
             if (!isEmptyRow)
             {
                 _DtData.Rows.Add(newDr);
             }
         }
     }
     catch (Exception ex) {
         throw ex;
     }
     return;
 }
Beispiel #26
0
 public Field(string template, ExcelCell cell)
 {
     Cell       = cell;
     Parameters = new List <FieldParameter>();
     Template   = template;
     foreach (Match m in CellValueRegex.Matches(Template))
     {
         Parameters.Add(new FieldParameter(m.Groups[0].Value));
     }
 }
        private ExcelRow CreateRowForItem(JobItemDocumentVM item)
        {
            var cells = new ExcelCell[item.Macros.Length + 3];

            var errors = new List <string>();

            if (item.Issues.Any())
            {
                errors.Add($"[File] - {string.Join(", ", item.Issues)}");
            }

            var cellColor = default(KnownColor?);

            switch (item.Status)
            {
            case Common.Services.JobItemStatus_e.Failed:
                cellColor = KnownColor.Red;
                break;

            case Common.Services.JobItemStatus_e.InProgress:
                cellColor = KnownColor.LightBlue;
                break;

            case Common.Services.JobItemStatus_e.Succeeded:
                cellColor = KnownColor.LightGreen;
                break;

            case Common.Services.JobItemStatus_e.Warning:
                cellColor = KnownColor.LightYellow;
                break;

            case Common.Services.JobItemStatus_e.AwaitingProcessing:
                cellColor = KnownColor.LightGray;
                break;
            }

            cells[0] = new ExcelCell(item.Status, null, cellColor);
            cells[1] = new ExcelCell(item.DisplayObject.Path, null, cellColor);

            for (int i = 0; i < item.Macros.Length; i++)
            {
                var macro = item.Macros[i];

                if (macro.Issues.Any())
                {
                    errors.Add($"[{macro.Name}] - {string.Join("; ", macro.Issues)}");
                }

                cells[i + 2] = new ExcelCell(macro.Status, null, cellColor);
            }

            cells[cells.Length - 1] = new ExcelCell(string.Join(Environment.NewLine, errors), null, cellColor);

            return(new ExcelRow(cells));
        }
        public void TemplateRepositoryTests_InsertExcelLayoutColumns_ShouldInsert()
        {
            #region Assemble

            DbHelpers.ClearDatabase();
            TemplateRepository templateRepository = new TemplateRepository(DbHelpers.GetContextFactory());
            ConnectionManager  connectionManager  = new ConnectionManager(@"(local)\SQLExpress", "Odin");
            connectionManager.SetUseTrustedConnection(true);
            LogServiceFactory  logServiceFactory      = new LogServiceFactory("Odin");
            OdinContextFactory OdinContextFactory     = new OdinContextFactory(connectionManager, logServiceFactory);
            ObservableCollection <ExcelCell> cellList = new ObservableCollection <ExcelCell>();
            ExcelCell excelCell = new ExcelCell();
            excelCell.ExcelId      = 4;
            excelCell.ColumnNumber = 7;
            excelCell.Field        = "TestField";
            excelCell.Option       = "TestOption";
            excelCell.Customer     = "TestCustomer";
            cellList.Add(excelCell);
            ExcelCell excelCell2 = new ExcelCell();
            excelCell2.ExcelId      = 4;
            excelCell2.ColumnNumber = 8;
            excelCell2.Field        = "TestField2";
            excelCell2.Option       = "TestOption2";
            excelCell2.Customer     = "TestCustomer2";
            cellList.Add(excelCell2);

            #endregion // Assemble

            #region Act

            templateRepository.InsertExcelLayoutColumns(cellList);

            #endregion // Act

            #region Assert

            using (OdinContext context = OdinContextFactory.CreateContext())
            {
                List <OdinExcelLayoutData> odinExcelLayoutData = (from o in context.OdinExcelLayoutData select o).ToList();

                Assert.AreEqual("4", Convert.ToString(odinExcelLayoutData[0].LayoutId));
                Assert.AreEqual("7", Convert.ToString(odinExcelLayoutData[0].ColumnNumber));
                Assert.AreEqual("TestField", odinExcelLayoutData[0].Field);
                Assert.AreEqual("TestOption", odinExcelLayoutData[0].Option);
                Assert.AreEqual("TestCustomer", odinExcelLayoutData[0].Customer);

                Assert.AreEqual("4", Convert.ToString(odinExcelLayoutData[1].LayoutId));
                Assert.AreEqual("8", Convert.ToString(odinExcelLayoutData[1].ColumnNumber));
                Assert.AreEqual("TestField2", odinExcelLayoutData[1].Field);
                Assert.AreEqual("TestOption2", odinExcelLayoutData[1].Option);
                Assert.AreEqual("TestCustomer2", odinExcelLayoutData[1].Customer);
            }

            #endregion // Assert
        }
Beispiel #29
0
    private ExcelRow ParseExcelRow(IRow r, int idx)
    {
        ExcelRow row = new ExcelRow(idx, this);

        for (int i = 0; i < m_nPropertyNums; i++)
        {
            ExcelCell c = ParseExcelCell(r.GetCell(i), _Properties[i], row);
            row.AppendCell(c);
        }
        return(row);
    }
    bool DrawCell(ExcelCell cell)
    {
        string s = EditorGUILayout.TextField(cell.stringValue);

        if (s != cell.stringValue)
        {
            cell.stringValue = s;
            return(true);
        }
        return(false);
    }
Beispiel #31
0
 public void As_converts_cell_value_type_to_generic_argument_type()
 {
     var newCell = new ExcelCell("2");
     Assert.AreEqual(2, newCell.Cast<int>());
     Assert.AreEqual(typeof(int), newCell.Cast<int>().GetType());
 }
Beispiel #32
0
 public void Cell_implicitly_converts_to_string()
 {
     var newCell = new ExcelCell("some value");
     Assert.IsTrue("some value" == newCell);
 }
Beispiel #33
0
 public void Constructor_sets_cell_value()
 {
     var newCell = new ExcelCell("hello");
     Assert.AreEqual("hello", newCell.Value);
 }
        private void ImportStyle(ExcelCell excelCell)
        {
            //      Not implemented
            //		    ShrinkToFit
            //		    WrapText
            CellStyle style = excelCell.Style;

            // Import font
            //      Not implemented
            //          ScriptPosition
            //          UnderlineStyle
            //          Strikeout
            ExcelFont font = style.Font;
            Label.FontFamily = new FontFamily(font.Name);
            Label.Foreground = new SolidColorBrush(font.Color.ConvertToNewColor());
            Label.FontStyle = font.Italic ? FontStyles.Italic : FontStyles.Normal;
            Label.FontWeight = FontWeight.FromOpenTypeWeight(font.Weight);
            Label.FontSize = font.Size / 20d;

            // Fill pattern
            ApplyFillPattern(style);

            // Alignment
            ApplyAlignment(style);

            // Indent
            Label.Padding = new Thickness(style.Indent, 0, style.Indent, 0);

            // Text Vertical
            Label.RenderTransform = new RotateTransform((style.IsTextVertical ? 90 : 0) + style.Rotation);

            // Locked
            if (style.Locked)
                IsReadOnly = true;

            // Number format
            NumberFormat = style.NumberFormat;

            // Borders
            // TODO Implement borders
            //ImportBorders(style.Borders);
        }
Beispiel #35
0
        private void DoRow(ref int rownum, ExcelRow row, ExcelSheet sheet, ExcelWorksheet exSheet, object[] fileargs = null, object[] sheetargs = null)
        {
            //Process Cell

            if (row.SQL != ExcelSQLType.PlainText)
            {
                using (DbCommand cmd = conn.RowConnection.CreateCommand())
                {

                    cmd.CommandTimeout = conn.CommandTimeout;
                    cmd.CommandType = row.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text;
                    cmd.CommandText = row.Text;

                    if (fileargs != null)
                        for (int i = 0; i < fileargs.Length; i++)
                        {
                            DbParameter param = cmd.CreateParameter();
                            param.DbType = DbType.String;
                            param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@F" + i : "F" + i;
                            param.Value = fileargs[i];
                            cmd.Parameters.Add(param);
                        }

                    if (sheetargs != null)
                        for (int i = 0; i < sheetargs.Length; i++)
                        {
                            DbParameter param = cmd.CreateParameter();
                            param.DbType = DbType.String;
                            param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@S" + i : "S" + i;
                            param.Value = sheetargs[i];
                            cmd.Parameters.Add(param);
                        }

                    using (DbDataReader reader = cmd.ExecuteReader())
                    {

                        if (row.ColumnHeader)
                        {
                            int curcell = 1;
                            using (var rw = exSheet.Cells[++rownum + ":" + rownum])
                            {
                                Console.WriteLine("Writing row {0} with Column header...", rw.Address);
                                var cols = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToArray();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    ExcelCell dc = new ExcelCell();
                                    dc.SQL = ExcelSQLType.PlainText;
                                    dc.Text = cols[i];

                                    //Console.WriteLine("Column '{0}' is have a type of '{1}'", cols[i], (reader.GetFieldType(i) ?? typeof(string)).FullName);

                                    DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, cols);
                                }
                                exSheet.View.FreezePanes(2, 1);
                            }
                        }

                        while (reader.Read())
                        {
                            int curcell = 1;
                            using (var rw = exSheet.Cells[++rownum + ":" + rownum])
                            {
                                //Console.WriteLine("Writing row {0} with given SQL...", rw.Address);
                                object[] args = new object[reader.FieldCount];
                                reader.GetValues(args);
                                for (int i = 0; i < reader.FieldCount; i++)
                                {

                                    if (row.Fetch != null)
                                    {
                                        using (DbCommand subcmd = conn.Connection.CreateCommand())
                                        {

                                            cmd.CommandTimeout = conn.CommandTimeout;
                                            subcmd.CommandType = row.Fetch.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text;
                                            subcmd.CommandText = row.Fetch.Text;

                                            if (fileargs != null)
                                                for (int j = 0; j < fileargs.Length; j++)
                                                {
                                                    DbParameter param = subcmd.CreateParameter();
                                                    param.DbType = DbType.String;
                                                    param.ParameterName = subcmd.Connection is System.Data.SqlClient.SqlConnection ? "@F" + j : "F" + j;
                                                    param.Value = fileargs[j];
                                                    subcmd.Parameters.Add(param);
                                                }

                                            if (sheetargs != null)
                                                for (int j = 0; j < sheetargs.Length; j++)
                                                {
                                                    DbParameter param = subcmd.CreateParameter();
                                                    param.DbType = DbType.String;
                                                    param.ParameterName = subcmd.Connection is System.Data.SqlClient.SqlConnection ? "@S" + j : "S" + j;
                                                    param.Value = sheetargs[j];
                                                    subcmd.Parameters.Add(param);
                                                }

                                            for (int j = 0; j < args.Length; j++)
                                            {
                                                DbParameter param = subcmd.CreateParameter();
                                                param.DbType = DbType.String;
                                                param.ParameterName = subcmd.Connection is System.Data.SqlClient.SqlConnection ? "@R" + j : "R" + j;
                                                param.Value = args[j];
                                                subcmd.Parameters.Add(param);
                                            }

                                            using (DbDataReader subreader = subcmd.ExecuteReader())
                                            {
                                                if (row.Fetch.Type == ExcelFetchType.Single)
                                                {
                                                    if (!subreader.Read()) continue;
                                                    for (int j = 0; j < subreader.FieldCount; j++)
                                                    {
                                                        ExcelCell dc = new ExcelCell();
                                                        dc.SQL = ExcelSQLType.PlainText;
                                                        dc.Text = subreader.GetValue(j).ToString();
                                                        dc.Out = DetermineOutType(subreader.GetFieldType(j));

                                                        DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, args);
                                                    }
                                                }
                                                else
                                                {
                                                    while (subreader.Read())
                                                    {
                                                        ExcelCell dc = new ExcelCell();
                                                        dc.SQL = ExcelSQLType.PlainText;
                                                        dc.Text = subreader.GetValue(0).ToString();
                                                        dc.Out = DetermineOutType(subreader.GetFieldType(0));

                                                        DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, args);
                                                    }
                                                }
                                            }
                                        }
                                    }
                                    else
                                    {
                                        ExcelCell dc = new ExcelCell();
                                        dc.SQL = ExcelSQLType.PlainText;
                                        dc.Text = reader.GetValue(i).ToString();
                                        dc.Out = DetermineOutType(reader.GetFieldType(i));

                                        DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, args);
                                    }

                                }
                            }

                        }
                    }

                }
            }
            else
            {
                int curcell = 1;
                using (var rw = exSheet.Cells[++rownum + ":" + rownum])
                {
                    //Console.WriteLine("Writing row {0}...", rw.Start.Row);
                    for (int i = 0; i < (row.Cells != null ? row.Cells.Count : 0); i++) DoCell(ref curcell, row.Cells[i], row, sheet, rw, fileargs, sheetargs);
                }
            }
        }
Beispiel #36
0
 public void to_string_returns_value_as_string()
 {
     var newCell = new ExcelCell("hello");
     Assert.AreEqual("hello", newCell.ToString());
 }
Beispiel #37
0
        private void DoCell(ref int cellnum, ExcelCell cell, ExcelRow row, ExcelSheet sheet, ExcelRange exRow, object[] fileargs = null, object[] sheetargs = null, object[] rowargs = null)
        {
            //Cell Value
            int cnt = cellnum;
            Action<string> cellfunc = val =>
            {
                using (var cl = exRow[exRow.Start.Row, cnt++])
                {
                    //Console.WriteLine("Writing cell {0}...", cl.Start.Address);

                    if (val != null)
                    {
                        double nval;
                        long lval;
                        DateTime dval;
                        bool isFomula = !string.IsNullOrEmpty(val) && val[0] == '=';
                        string rawval = isFomula ? val.Substring(1) : val;
                        switch (cell.Out)
                        {
                            case ExcelOutType.DateTime:
                                cl.Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";
                                if (isFomula) cl.Formula = rawval; else if (DateTime.TryParse(rawval, out dval)) cl.Value = dval; else cl.Value = rawval;
                                if (cl.Text.EndsWith("00:00:00"))
                                    cl.Style.Numberformat.Format = "yyyy-mm-dd";
                                break;
                            case ExcelOutType.Date:
                                cl.Style.Numberformat.Format = "yyyy-mm-dd";
                                if (isFomula) cl.Formula = rawval; else if (DateTime.TryParse(rawval, out dval)) cl.Value = dval; else cl.Value = rawval;
                                break;
                            case ExcelOutType.Integer:
                                cl.Style.Numberformat.Format = "#,##0";
                                if (isFomula) cl.Formula = rawval; else if (long.TryParse(rawval, out lval)) cl.Value = lval; else cl.Value = rawval;
                                break;
                            case ExcelOutType.Number:
                                cl.Style.Numberformat.Format = "#,##0.00";
                                if (isFomula) cl.Formula = rawval; else if (double.TryParse(rawval, out nval)) cl.Value = nval; else cl.Value = rawval;
                                break;
                            case ExcelOutType.Money:
                                cl.Style.Numberformat.Format = "₩ #,##0";
                                if (isFomula) cl.Formula = rawval; else if (long.TryParse(rawval, out lval)) cl.Value = lval; else cl.Value = rawval;
                                break;
                            case ExcelOutType.Normal:
                            default:
                                if (isFomula) cl.Formula = rawval; else cl.Value = rawval;
                                break;
                        }
                    }
                }

            };

            if (cell.SQL != ExcelSQLType.PlainText)
            {
                using (DbCommand cmd = conn.CellConnection.CreateCommand())
                {

                    cmd.CommandTimeout = conn.CommandTimeout;
                    cmd.CommandType = cell.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text;
                    cmd.CommandText = cell.Text;

                    if (fileargs != null)
                        for (int i = 0; i < fileargs.Length; i++)
                        {
                            DbParameter param = cmd.CreateParameter();
                            param.DbType = DbType.String;
                            param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@F" + i : "F" + i;
                            param.Value = fileargs[i];
                            cmd.Parameters.Add(param);
                        }

                    if (sheetargs != null)
                        for (int i = 0; i < sheetargs.Length; i++)
                        {
                            DbParameter param = cmd.CreateParameter();
                            param.DbType = DbType.String;
                            param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@S" + i : "S" + i;
                            param.Value = sheetargs[i];
                            cmd.Parameters.Add(param);
                        }

                    if (rowargs != null)
                        for (int i = 0; i < rowargs.Length; i++)
                        {
                            DbParameter param = cmd.CreateParameter();
                            param.DbType = DbType.String;
                            param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@R" + i : "R" + i;
                            param.Value = rowargs[i];
                            cmd.Parameters.Add(param);
                        }

                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            cellfunc.Invoke(reader.GetValue(0).ToString());
                        }
                    }

                }
            }
            else if(cell.Text != null)
            {
                cellfunc.Invoke(cell.Text);
            }

            cellnum = cnt;
        }
Beispiel #38
0
 /// <summary>
 /// 向当前工作区的表中插入一个单元格
 /// </summary>
 /// <param name="worksheetPart">当前的工作区对象</param>
 /// <param name="cell">设置要插入的单元格信息</param>
 /// <returns>返回插入成功的数量</returns>
 public static bool InsertCell(this WorksheetPart worksheetPart, ExcelCell cell)
 {
     return InsertCell(worksheetPart, new List<ExcelCell> { cell }) > 0;
 }
Beispiel #39
0
 public void ValueAs_returns_default_generic_value_when_value_is_DBNull()
 {
     var newCell = new ExcelCell(DBNull.Value);
     Assert.AreEqual(0, newCell.Cast<int>());
 }
 public DataTable GetSpreadsheet(string sheetName, ExcelCell startingCell, ExcelCell endingCell)
 {
     if (string.IsNullOrEmpty(sheetName)) throw new ArgumentNullException("sheetName", "sheetName cannot be null or empty");
     DataTable dtResult = new DataTable();
     using (var connection = new OleDbConnection(GetConnectionString())) {
         connection.Open();
         if (!sheetName.EndsWith("$")) sheetName += "$";
         using (var command = new OleDbCommand("Select * From [" + sheetName + startingCell.Cell + ":" + endingCell.Cell + "]", connection)) {
             using (var adapter = new OleDbDataAdapter(command)) {
                 using (DataSet ds = new DataSet()) {
                     adapter.Fill(ds);
                     if (ds.Tables.Count > 0) dtResult = ds.Tables[0];
                     ds.Dispose();
                 }
             }
         }
         connection.Close();
     }
     return dtResult;
 }
        public void ImportData(ExcelCell excelCell)
        {
            // Check if style to import is default
            if (!excelCell.IsStyleDefault) {
                this.isDefault = false;
            }
            else {
                // Cell Value
                if (excelCell.Value == null) {
                    SetValue(String.Empty);
                }
                else {
                    SetValue(excelCell.Value);
                }

                Formula = FormulaManager.GetExpression(excelCell.Formula);
                IsFormulaHidden = excelCell.Style.FormulaHidden;

                // Style
                ImportStyle(excelCell);
            }
        }