Esempio n. 1
0
        public void AddListToSheet <T>(IEnumerable <T> listOfEntities,
                                       string titleOfTable,
                                       string worksheetName,
                                       IEnumerable <string> headersToDelete = null)
        {
            if (listOfEntities is null)
            {
                return;
            }

            var headers = CreateHeaders(listOfEntities.First());

            try
            {
                AddTitle(titleOfTable);

                var emptyRow = ws.LastRowUsed()
                               .RowBelow();
                AddHeadersToWorksheet(emptyRow, headers);

                var table = CreateTable(listOfEntities, emptyRow);
                DeleteTableColumn(table, headersToDelete);
                CreateTableTotalSum(table);
            }
            catch (Exception ex)
            {
                throw;
            }

            ws.LastRowUsed().RowBelow().RowBelow();
        }
Esempio n. 2
0
        public void TestColumnOldModel()
        {
            for (int row_idx = 2; row_idx < sheet.LastRowUsed().RowNumber(); row_idx++)
            {
                var cur_row = sheet.Row(row_idx);
                var cell    = cur_row.Cell((int)BikeStoreSheetCols.OldModel);

                // Format is not required for this column.

                // test if the value is string.
                Assert.IsTrue(cell.TryGetValue <string>(out var cell_actual_val));

                var cell_expected_val =
                    cur_row.Cell((int)BikeStoreSheetCols.OrderDate).GetValue <DateTime>().Year >
                    cur_row.Cell((int)BikeStoreSheetCols.ModelYear).GetValue <int>()
                        ? "YES"
                        : "NO";

                Assert.That(cell_expected_val, Is.EqualTo(cell_actual_val).IgnoreCase, $"Cell {cell.Address} value should be {cell_expected_val} but it is {cell_actual_val}");

                // test whether this cell is actually a formula.
                Assert.IsTrue(cell.HasFormula, $"Cell {cell.Address} should be formula");

                // test whether the formula contains IF.
                StringAssert.Contains("IF", cell.FormulaR1C1,
                                      $"Cell {cell.Address} formula should include conditional");

                // test whether the formula referencing correct rows and columns.
                StringAssert.Contains("-4", cell.FormulaR1C1,
                                      $"Cell {cell.Address} formula should reference column order_date ");
                StringAssert.Contains("-12", cell.FormulaR1C1,
                                      $"Cell {cell.Address} formula should reference column model_year");
                Assert.IsTrue(Regex.Matches(cell.FormulaA1, cell.Address.RowNumber.ToString()).Count() == 2, $"Cell {cell.Address} is not referencing correct rows.  ");
            }
        }
        public void ExportToExcel(List <DataGridView> dgvs, string sheet_prefix)
        {
            if (dgvs == null)
            {
                return;
            }

            foreach (DataGridView dgv in dgvs)
            {
                DataTable dt = new DataTable();
                foreach (DataGridViewColumn col in dgv.Columns)
                {
                    dt.Columns.Add(col.HeaderText);
                }

                foreach (DataGridViewRow row in dgv.Rows)
                {
                    DataRow new_row = dt.NewRow();
                    foreach (DataGridViewCell cell in row.Cells)
                    {
                        if (dgv.Columns[cell.ColumnIndex].Visible)
                        {
                            new_row[cell.ColumnIndex] = cell.Value == null || cell.Value.ToString() == "NaN" ? "" : cell.Value;
                        }
                    }
                    lock (dt) dt.Rows.Add(new_row);
                }

                foreach (DataGridViewColumn col in dgv.Columns)
                {
                    if (!col.Visible)
                    {
                        dt.Columns.Remove(col.HeaderText);
                    }
                }

                IXLWorksheet worksheet = null;
                lock (workbook)
                {
                    worksheet = workbook.Worksheets.Add(dt, sheet_prefix.Substring(0, Math.Min(sheet_prefix.Length, 30 - dgv.Name.Length)) + "_" + dgv.Name);
                }

                foreach (var col in worksheet.Columns())
                {
                    try
                    {
                        col.Cells(2, worksheet.LastRowUsed().RowNumber()).DataType = Double.TryParse(worksheet.Row(2).Cell(col.ColumnNumber()).Value.ToString(), out double is_number) ?
                                                                                     XLCellValues.Number :
                                                                                     XLCellValues.Text;
                    }
                    catch
                    {
                        col.Cells(2, worksheet.LastRowUsed().RowNumber()).DataType = XLCellValues.Text;
                    }
                }
            }
        }
Esempio n. 4
0
 public void TestColumnListPrice()
 {
     for (var row_idx = 2; row_idx < sheet.LastRowUsed().RowNumber(); row_idx++)
     {
         var cur_row = sheet.Row(row_idx);
         var cell    = cur_row.Cell((int)BikeStoreSheetCols.ListPrice);
         Assert.IsTrue(cell.Style.NumberFormat.Format.Contains("$"), $"cell format {cell.Address} should be $");
         Assert.IsTrue(cell.Style.NumberFormat.Format.Contains("0.00"), $"cell format {cell.Address} should be 2 digit accurate");
     }
 }
Esempio n. 5
0
        private List <int> GetBoldIndex(IXLWorksheet ws1)
        {
            List <int> indexList = new List <int>();

            for (int i = 2; i < ws1.LastRowUsed().RowNumber(); i++)
            {
                if (ws1.Cell(i, 1).Style.Font.Bold == true && ws1.Cell(i, 1).Value.ToString() != "")
                {
                    indexList.Add(i - 1);
                }
            }
            indexList.Add(ws1.LastRowUsed().RowNumber());
            return(indexList);
        }
Esempio n. 6
0
        /*
         *
         *
         * functional objects(i.e. buttons, etc that do stuff)
         *
         *
         */

        //receiveing button
        private void button1_Click(object sender, EventArgs e)
        {
            //make sure the workbook file is selected
            if (currentWb == null)
            {
                MessageBox.Show("You must select a file!");
                return;
            }

            //make sure the ticket isn't left blank. This check isn't perfect, but a better one is in the works ;)
            if (textBox1.Text == "")
            {
                MessageBox.Show("You can't receive nothing!");
                return;
            }

            //check if the ticket has already been received or not
            if (atIndex(currentWsh, textBox1.Text) > 0)
            {
                MessageBox.Show("This ticket has already been received!");
            }
            else
            {
                //create a new row, add the ticket, and update the received date for said ticket.
                int rowNumber = currentWsh.LastRowUsed().RowNumber() + 1;
                currentWsh.Cell(rowNumber, 1).Value = textBox1.Text;
                currentWsh.Cell(rowNumber, 2).Value = DateTime.Now;
                myDt.Rows.Add(textBox1.Text, DateTime.Now);
                currentWb.Save();

                xlDataGridRefresh(dataGridView1, currentWb, myDt);

                MessageBox.Show("Ticket received successfully!");
            }
        }
Esempio n. 7
0
        // GET: api/Excel
        public void Get()
        {
            XLWorkbook   Workbook  = new XLWorkbook(@"c:\temp\Sample.xlsx");
            IXLWorksheet Worksheet = Workbook.Worksheets.First();

            Debug.WriteLine(Worksheet);
            int       NumberOfLastRow = Worksheet.LastRowUsed().RowNumber();
            IXLCell   CellForNewData  = Worksheet.Cell(8, 1);
            DataTable datatable       = new DataTable();

            datatable.Columns.Add("Name");
            datatable.Columns.Add("Marks");
            datatable.Columns.Add("Rank");
            var i = 10;

            while (i > 0)
            {
                DataRow dr = datatable.NewRow();
                dr["Name"]  = "shakthi";
                dr["Marks"] = "shiva";
                dr["Rank"]  = "sharan";

                datatable.Rows.Add(dr);
                --i;
            }
            CellForNewData.InsertTable(datatable.AsEnumerable());
            Worksheet.Row(8).Delete();
            Workbook.Save();
        }
Esempio n. 8
0
 public bool AddEmployee(Employee emp)
 {
     try
     {
         string filePath = Path.Combine(_hostingEnvironment.WebRootPath, "Database", "Employees.xlsx");
         using (XLWorkbook workBook = new XLWorkbook(filePath))
         {
             //Read the first Sheet from Excel file.
             IXLWorksheet workSheet = workBook.Worksheet(1);
             var          lastrow   = workSheet.LastRowUsed().RowNumber();
             var          newemp    = workSheet.Row(lastrow + 1);
             newemp.Cell(1).SetValue(emp.Id.ToString());
             newemp.Cell(2).SetValue(emp.FirstName);
             newemp.Cell(3).SetValue(emp.LastName);
             newemp.Cell(4).SetValue(emp.Team.ToString());
             newemp.Cell(5).SetValue(emp.Email);
         }
         return(true);
     }
     catch (Exception e)
     {
         Console.WriteLine(e);
         return(false);
     }
 }
Esempio n. 9
0
    private IXLRow GetRow(IReadOnlyDayWorkLog logs, IXLWorksheet worksheet)
    {
        if (logs.GetStartTime() == null)
        {
            return(worksheet.LastRowUsed().RowBelow());
        }

        var LastRowUsed = worksheet.LastRowUsed();

        if (GetDayWorkLog(LastRowUsed).GetStartTime()?.Date == logs.GetStartTime()?.Date)
        {
            return(LastRowUsed);
        }

        return(LastRowUsed.RowBelow());
    }
Esempio n. 10
0
        /// <summary>
        /// Parse xlsx sheet data into a list of dynamic objects
        /// it will list for given columnNames ( if not null ) or for all columns ( if null )
        /// if columnNamesIgnoreCase result object will contains lowercase properties
        /// </summary>
        public static List <dynamic> ParseXlsxData(this IXLWorksheet ws, HashSet <string> _columnNames = null, bool columnNamesIgnoreCase = true)
        {
            HashSet <string> columnNames = null;

            if (columnNamesIgnoreCase && _columnNames != null)
            {
                columnNames = _columnNames.Select(w => w.ToLower()).ToHashSet();
            }
            else
            {
                columnNames = _columnNames;
            }

            var res = new List <dynamic>();

            var columnDict = new Dictionary <string, int>();

            var row = ws.FirstRow();

            var lastCol = row.LastCellUsed().Address.ColumnNumber;

            for (int ci = 1; ci <= lastCol; ++ci)
            {
                var cname = (string)row.Cell(ci).Value;
                if (string.IsNullOrEmpty((string)cname))
                {
                    continue;
                }

                if (columnNamesIgnoreCase)
                {
                    cname = cname.ToLower();
                }

                if (columnNames == null || columnNames.Contains(cname))
                {
                    columnDict.Add(cname, ci);
                }
            }

            var lastRow = ws.LastRowUsed().RowNumber();

            for (int ri = 2; ri <= lastRow; ++ri)
            {
                row = ws.Row(ri);

                IDictionary <string, object> eo = new ExpandoObject();

                foreach (var c in columnDict)
                {
                    var cell = row.Cell(c.Value);

                    eo.Add(c.Key, cell.Value);
                }

                res.Add(eo);
            }

            return(res);
        }
Esempio n. 11
0
        public void ExportToExcel(DataTable resultTable, string fileLocation, string sheetName, string color = "", int numberOfLastRow = 0, int startingCellIndex = 1)
        {
            XLWorkbook   Workbook  = new XLWorkbook(fileLocation);
            IXLWorksheet Worksheet = Workbook.Worksheet(sheetName);

            //Gets the last used row
            if (numberOfLastRow == 0)
            {
                numberOfLastRow = Worksheet.LastRowUsed().RowNumber();
            }


            //Defines the starting cell for appeding  (Row , Column)
            IXLCell CellForNewData = Worksheet.Cell(numberOfLastRow + 1, startingCellIndex);

            if (!color.Equals(""))
            {
                for (int i = 0; i < resultTable.Rows.Count; i++)
                {
                    IXLRow RowForNewData = Worksheet.Row(numberOfLastRow + 1 + i);
                    RowForNewData.Style.Font.FontColor = XLColor.Red;
                }
            }
            //InsertData -  the data from the DataTable without the Column names ; InsertTable - inserts the data with the Column names
            CellForNewData.InsertData(resultTable);

            Workbook.SaveAs(fileLocation);
        }
Esempio n. 12
0
        public bool AddHistory(History history)
        {
            try
            {
                string filePath = Path.Combine(_hostingEnvironment.WebRootPath, "Database", "History.xlsx");
                using (XLWorkbook workBook = new XLWorkbook(filePath))
                {
                    //Read the first Sheet from Excel file.
                    IXLWorksheet workSheet  = workBook.Worksheet(1);
                    var          lastrow    = workSheet.LastRowUsed().RowNumber();
                    var          newHistory = workSheet.Row(lastrow + 1);

                    newHistory.Cell(1).SetValue(history.Id);
                    newHistory.Cell(2).SetValue(history.EmployeeIds.ToCSVString());
                    newHistory.Cell(3).SetValue(history.DaysOfWeek.ToString());
                    newHistory.Cell(4).SetValue(history.LastModified.ToString());
                }

                return(true);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return(false);
            }
        }
Esempio n. 13
0
        public int GetTableStart(IXLWorksheet worksheet, int tablePosition)
        {
            var lastRowUsed          = worksheet.LastRowUsed();
            int currentTablePosition = 1;

            if (lastRowUsed == null)
            {
                throw new Exception("No tables in the worksheet");
            }
            if (tablePosition == 1)
            {
                return(1);
            }
            //If found space between tables add it to the end of tables rows;
            for (int i = 1; i <= lastRowUsed.RowNumber(); i++)
            {
                if (worksheet.Cell(i, 1).IsEmpty() && !worksheet.Cell(i + 1, 1).IsEmpty())
                {
                    currentTablePosition++;
                    if (currentTablePosition == tablePosition)
                    {
                        return(i + 1);
                    }
                }
            }
            return(lastRowUsed.RowNumber() + 2);
        }
Esempio n. 14
0
        public void EditRowColor(string fileLocation, string sheetName, int startIndex, string date)
        {
            XLWorkbook   Workbook        = new XLWorkbook(fileLocation);
            IXLWorksheet Worksheet       = Workbook.Worksheet(sheetName);
            int          NumberOfLastRow = Worksheet.LastRowUsed().RowNumber();

            for (int i = startIndex; i < NumberOfLastRow; i++)
            {
                var cellValue = (Worksheet.Cell(i, 4).Value).ToString();
                if (cellValue.Equals(""))
                {
                    break;
                }
                var    validity  = DateTime.ParseExact(cellValue, "dd.MM.yyyy", null);
                var    todayDate = DateTime.ParseExact(date, "dd.MM.yyyy", null);
                IXLRow excelRow  = Worksheet.Row(i);
                if (DateTime.Compare(validity, todayDate) < 0)
                {
                    excelRow.Style.Fill.BackgroundColor = XLColor.Red;
                }
                else if (DateTime.Compare(validity, todayDate) == 0)
                {
                    excelRow.Style.Fill.BackgroundColor = XLColor.Yellow;
                }
            }
            Workbook.SaveAs(fileLocation);
        }
Esempio n. 15
0
        public ExcelReader(string filename)
        {
            workbook  = new XLWorkbook(File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite));
            worksheet = workbook.Worksheets.First();

            totalRows    = worksheet.LastRowUsed().RowNumber();
            totalColumns = worksheet.LastColumnUsed().ColumnNumber();
        }
Esempio n. 16
0
        private static void AddOrdersWorksheet(IXLWorksheet sheet, IList <Order> orders)
        {
            var cell = 1;

            sheet.Row(1).Cell(cell++).Value = "Compte";
            sheet.Row(1).Cell(cell++).Value = "Nom";
            sheet.Row(1).Cell(cell++).Value = "Prénom";
            sheet.Row(1).Cell(cell++).Value = "Email";
            sheet.Row(1).Cell(cell++).Value = "N° commande";
            sheet.Row(1).Cell(cell++).Value = "Date commande";
            sheet.Row(1).Cell(cell++).Value = "Date envoi";
            sheet.Row(1).Cell(cell++).Value = "Prix";
            sheet.Row(1).Cell(cell++).Value = "Localisation";
            sheet.Row(1).Cell(cell++).Value = "Type Doc";
            sheet.Row(1).Cell(cell++).Value = "Périodique";
            sheet.Row(1).Cell(cell++).Value = "Année";
            sheet.Row(1).Cell(cell++).Value = "Vol";
            sheet.Row(1).Cell(cell++).Value = "pp.";

            var index = 2;

            sheet.ColumnWidth = 11;

            foreach (var order in orders)
            {
                sheet.Row(index).Cell(1).Value = order.InvoiceAccount;
                sheet.Row(index).Cell(2).Value = order.Name;
                sheet.Row(index).Cell(3).Value = order.FirstName;
                sheet.Row(index).Cell(4).Value = order.EMail;

                sheet.Row(index).Cell(5).Value = order.IlLinkId;

                sheet.Row(index).Cell(6).Style.DateFormat.Format = "dd.MM.yyyy";
                sheet.Row(index).Cell(6).Value = order.OrderDate;

                sheet.Row(index).Cell(7).Style.DateFormat.Format = "dd.MM.yyyy";
                sheet.Row(index).Cell(7).Value = order.SendDate;

                sheet.Row(index).Cell(8).Style.NumberFormat.Format = "#,##0.00";
                sheet.Row(index).Cell(8).Value = order.Price;

                sheet.Row(index).Cell(9).Value  = order.Localisation;
                sheet.Row(index).Cell(10).Value = order.DocumentType;
                sheet.Row(index).Cell(11).Value = order.Title;
                sheet.Row(index).Cell(12).Value = order.Year;
                sheet.Row(index).Cell(13).Value = order.Volume;
                sheet.Row(index).Cell(14).Value = order.Pages;

                index++;
            }

            var tablename = $"Table_{sheet.Name}";
            var table     = sheet.Range(sheet.FirstColumn().FirstCell(), sheet.LastRowUsed().LastCellUsed()).CreateTable(tablename);

            table.ShowAutoFilter = true;
            table.Theme          = XLTableTheme.TableStyleMedium2;
        }
Esempio n. 17
0
        private IEnumerable <ValuesBunch> ReadWorkSheet(IXLWorksheet worksheet)
        {
            var items = new List <ValuesBunch>();

            var firstRow = worksheet.FirstRowUsed();
            var lastRow  = worksheet.LastRowUsed();

            IXLRow      currentRow = firstRow;
            ValuesBunch valueItems = null;

            while (currentRow.RowNumber() <= lastRow.RowNumber())
            {
                var currentRowFirstCell = currentRow.FirstCellUsed();
                var correntRowLastCell  = currentRow.LastCellUsed();

                if (DateTime.TryParse(currentRowFirstCell?.CachedValue.ToString(), out var initialDate) && initialDate < DateTime.Now)
                {
                    InitializeFinalDate(currentRowFirstCell, correntRowLastCell, initialDate, out var finalDate);

                    if (valueItems != null && valueItems.Count > 0)
                    {
                        items.Add(valueItems);
                    }

                    if (finalDate.HasValue)
                    {
                        valueItems = new ValuesBunch(initialDate, finalDate.Value);
                    }
                    else
                    {
                        valueItems = new ValuesBunch(initialDate);
                    }
                }
                else if (valueItems != null && currentRow != null && currentRow.CellsUsed().Count() > 0)
                {
                    var currentTypeCell = currentRow.FirstCellUsed();
                    var rowDelta        = ParseValueItems(valueItems, currentTypeCell);

                    if (valueItems != null && valueItems.Count > 0)
                    {
                        items.Add(valueItems);
                        valueItems = null;
                    }

                    currentRow = currentRow.RowBelow(rowDelta - currentRow.RowNumber());
                }

                currentRow = currentRow.RowBelow();
            }

            if (valueItems != null && valueItems.Count > 0)
            {
                items.Add(valueItems);
            }

            return(items);
        }
Esempio n. 18
0
        private void AddTableCells(IXLWorksheet worksheet, IEnumerable <Containers> containers)
        {
            int rowIndex = worksheet.LastRowUsed().RowNumber() + 1;

            foreach (var container in containers)
            {
                AddTableCell(worksheet, container, rowIndex);
                rowIndex++;
            }
        }
        /// <summary>
        /// Excelのデータをセルごとに読み込んで2次元配列に代入する
        /// </summary>
        /// <param name="sheetNumber">シート番号(1から)</param>
        /// <param name="workBook">XLWorkbook変数</param>
        /// <returns>使用しているExcelのセルのデータをstringの2次元配列で返す</returns>
        public string[,] ExtractionExcelData(int sheetNumber, XLWorkbook workBook)
        {
            IXLWorksheet workSheet = workBook.Worksheet(sheetNumber);

            (int column, int row)xlCellAddress;
            xlCellAddress.column     = workSheet.LastColumnUsed().ColumnNumber();
            xlCellAddress.row        = workSheet.LastRowUsed().RowNumber();
            string[,] xlStrDataArray = new string[xlCellAddress.column, xlCellAddress.row];
            return(xlStrDataArray);
        }
Esempio n. 20
0
        public static double getTotalHeight(IXLWorksheet ws, int startRow)
        {
            var totalHeight = 0.0;
            foreach (var row in ws.Rows(startRow, ws.LastRowUsed().RowNumber()))
            {
                totalHeight += row.Height;
            }

            return totalHeight;

        }
Esempio n. 21
0
        public void TestYearsTotals()
        {
            var totals_sheet = workbook.Worksheets.Worksheet(2);

            var expected_vals = new Dictionary <int, double>();

            for (int row_idx = 2; row_idx < sheet.LastRowUsed().RowNumber(); row_idx++)
            {
                var cur_row    = sheet.Row(row_idx);
                var order_date = cur_row.Cell((int)BikeStoreSheetCols.OrderDate).GetDateTime();
                var amount     = cur_row.Cell((int)BikeStoreSheetCols.LineTotalAfterDiscount).GetDouble();

                if (!expected_vals.ContainsKey(order_date.Year))
                {
                    expected_vals.Add(order_date.Year, 0);
                }
                expected_vals[order_date.Year] += amount;
            }

            var first_row = totals_sheet.Row(1);
            var total_row = totals_sheet.Row(2);

            for (int col = 2; ; col++)
            {
                if (!first_row.Cell(col).TryGetValue <int>(out var header_cell_val))
                {
                    break;
                }

                var total_cell = total_row.Cell(col);
                Assert.IsTrue(total_cell.HasFormula, $"Cell {total_cell.Address} should be formula");
                Assert.IsTrue(total_cell.TryGetValue <double>(out var total_actual_value), $"Cell {total_cell.Address} value is not a number");

                // test whether the formula contains IF.
                StringAssert.Contains("SUMIF", total_cell.FormulaR1C1,
                                      $"Cell {total_cell.Address} formula should include SUMIF");

                Assert.That(total_actual_value, Is.EqualTo(expected_vals[header_cell_val]).Within(0.01),
                            $"Cell {total_cell.Address} value should be {expected_vals[header_cell_val]} but it is {total_actual_value}");
            }
        }
Esempio n. 22
0
        //Read Excel data to generic list - overloaded version 2.
        public static IList <T> GetDataToList <T>(string filePath, string sheetName, Func <IList <string>, T> addRecord)
        {
            List <T> resultList = new List <T>();

            using (XLWorkbook wB = new XLWorkbook(filePath))
            {
                IXLWorksheet wS = wB.Worksheet(1);
                //DataTable dt = new DataTable();
                //bool firstRow = true;
                //Used for sheet row data to be added through delegation.

                var firstRow = true;
                int lastRow  = wS.LastRowUsed().RowNumber();
                var rows     = wS.Rows(1, lastRow);
                foreach (IXLRow row in rows)
                {
                    if (firstRow)
                    {
                        ////foreach (IXLCell cell in row.Cells())
                        ////{
                        ////    dt.Columns.Add(cell.Value.ToString());
                        ////}
                        firstRow = false;
                        continue;
                    }

                    if (row.IsEmpty())
                    {
                        continue;
                    }
                    var rowData = new List <string>();
                    //else
                    //{
                    //dt.Rows.Add();
                    //int i = 0;
                    //rowData.Clear();
                    foreach (IXLCell cell in row.Cells(wS.FirstCellUsed().Address.ColumnNumber, wS.LastCellUsed().Address.ColumnNumber))
                    {
                        rowData.Add(cell.Value.ToString());
                    }
                    //foreach (IXLCell cell in row.Cells())
                    //{
                    //    rowData.Add(cell.Value.ToString());
                    //}
                    resultList.Add(addRecord(rowData));
                }


                // }
                return(resultList);
            }
        }
Esempio n. 23
0
        public void ExportToExcel(List <DataTable> datatables, string sheet_prefix)
        {
            if (datatables == null)
            {
                return;
            }

            foreach (DataTable dt in datatables)
            {
                if (dt.Rows.Count == 0)
                {
                    continue;
                }

                IXLWorksheet worksheet = null;
                lock (workbook)
                {
                    worksheet = workbook.Worksheets.Add(sheet_name(sheet_prefix, dt.TableName));
                }

                // speedup by doing this in parallel
                worksheet.Cell(1, 1).InsertTable(dt);

                foreach (var col in worksheet.Columns())
                {
                    try
                    {
                        col.Cells(2, worksheet.LastRowUsed().RowNumber()).DataType =
                            Double.TryParse(worksheet.Row(2).Cell(col.ColumnNumber()).Value.ToString(), out double is_number) ?
                            XLCellValues.Number :
                            XLCellValues.Text;
                    }
                    catch
                    {
                        col.Cells(2, worksheet.LastRowUsed().RowNumber()).DataType = XLCellValues.Text;
                    }
                }
            }
        }
Esempio n. 24
0
        public List <RowRuEng> GetRowsRuEng(int workSheetNumber)
        {
            IXLWorksheet worksheet = _workbook.Worksheet(workSheetNumber);

            var list = new List <RowRuEng>();

            if (worksheet.LastRowUsed() == null)
            {
                return(list);
            }

            for (int i = 1; i <= worksheet.LastRowUsed().RowNumber(); i++)
            {
                var ruStr     = worksheet.Row(i).Cell(1).GetValue <string>();
                var enStr     = worksheet.Row(i).Cell(2).GetValue <string>();
                var testValue = new RowRuEng(ruStr, enStr, i);

                list.Add(testValue);
            }

            return(list);
        }
Esempio n. 25
0
        private void AdicionarEstilo(IXLWorksheet worksheet, DataTable tabelaDados)
        {
            int ultimaColunaUsada = worksheet.LastColumnUsed().ColumnNumber();
            int ultimaLinhaUsada  = worksheet.LastRowUsed().RowNumber();

            AdicionarEstiloCabecalho(worksheet, ultimaColunaUsada);
            AdicionarEstiloCorpo(worksheet, tabelaDados, ultimaColunaUsada, ultimaLinhaUsada);

            worksheet.ShowGridLines = false;

            worksheet.ColumnsUsed().AdjustToContents();
            worksheet.RowsUsed().AdjustToContents();
        }
Esempio n. 26
0
        public virtual SheetParseResult <T> ParseSheet <T>(IXLWorksheet sheet, Func <T, List <string> > validateT = null) where T : class, new()
        {
            if (sheet == null)
            {
                throw new ArgumentNullException(nameof(sheet));
            }

            var result    = new SheetParseResult <T>();
            var headerRow = sheet.FirstRowUsed();
            var lastRow   = sheet.LastRowUsed();
            var mappings  = GetDefaultPropertyMapParsers <T>(headerRow);

            //foreach over the rows and parse to T
            foreach (var _row in sheet.Rows(firstRow: headerRow.RowBelow().RowNumber(), lastRow: lastRow.RowNumber()))
            {
                result.TotalRecordCount++;
                var row = _row;                              //modified closure
                var runningValidation = new List <string>(); //use to give feedback on parse and validation
                var t = new T();
                foreach (var m in mappings)
                {
                    object val;
                    var    cell     = row.Cell(m.ExcelColumnIndex);
                    var    didParse = m.TryGetProperty(propertyInfo: m.ObjectPropertyInfo, input: cell.GetString(), outVal: out val);
                    if (didParse)
                    {
                        m.ObjectPropertyInfo.SetValue(t, val);
                    }
                    else
                    {
                        runningValidation.Add($"{m.ObjectPropertyInfo.Name} did not parse.");
                    }

                    this.FillCellBackground(cell: ref cell, isValid: didParse);
                }

                if (runningValidation.Count == 0 && validateT != null)
                {
                    runningValidation.AddRange(validateT(t));
                }

                if (runningValidation.Count == 0)
                {
                    result.ValidList.Add(t);
                }

                this.FillRowBackgroundWithValidationMessage(row: ref row, isValid: runningValidation.Count == 0, validationMessages: runningValidation);
            }

            return(result);
        }
Esempio n. 27
0
        public void WriteXlsx(string filepath, List <ResourceItem> resourceList)
        {
            foreach (var resource in resourceList)
            {
                var currentRow = _sheet.LastRowUsed().RowBelow();

                WriteKeyValues(currentRow, resource);
                WriteDefaultValue(currentRow, resource);
                WriteLocaleValues(currentRow, resource);
            }

            _sheet.FirstRow().CellsUsed().Style.Fill.BackgroundColor = XLColor.Yellow;
            _workBook.SaveAs(filepath);
        }
Esempio n. 28
0
        /*
         *
         *
         * FUNCTIONAL METHODS
         *
         *
         */



        //method to return the row in an excel file that a certain item occurs at in the index
        public int atIndex(IXLWorksheet sheet, string con)
        {
            int item;

            for (item = 1; item <= sheet.LastRowUsed().RowNumber(); item++)
            {
                if (sheet.Cell(item, 1).Value.ToString() == con)
                {
                    return(item);
                }
            }

            return(0);
        }
        /// <summary>
        /// Excelのデータをセルごとに読み込んでジャグ配列に代入する
        /// </summary>
        /// <param name="sheetNumber">シート番号(1から)</param>
        /// <param name="workBook">XLWorkbook変数</param>
        /// <returns>使用しているExcelのセルのデータをstringのジャグ配列で返す</returns>
        public string[][] ExtractionExcelDataJagged(int sheetNumber, XLWorkbook workBook)
        {
            IXLWorksheet workSheet = workBook.Worksheet(sheetNumber);

            (int column, int row)xlCellAddress;
            xlCellAddress.column = workSheet.LastColumnUsed().ColumnNumber();
            xlCellAddress.row    = workSheet.LastRowUsed().RowNumber();

            // ジャグ配列にExcelのセルのデータを入れる
            string[][] xlStrDataJaggedArray = new string[xlCellAddress.row][];
            xlStrDataJaggedArray = Enumerable.Range(0, xlCellAddress.column)
                                   .Select(row => (new string[xlCellAddress.column]).Select(str => workSheet.Cell(1, 1).Value.ToString()).ToArray()).ToArray();

            string[][] xlStrDataArray2 = new string[xlCellAddress.row][];
            return(xlStrDataJaggedArray);
        }
Esempio n. 30
0
 private void LoadEmpExcel(string path)
 {
     using (XLWorkbook empWb = new XLWorkbook(path))
     {
         //empRecords.Clear();
         empWs = empWb.Worksheet(1);
         for (int i = 2; i < empWs.LastRowUsed().RowNumber() + 1; i++)
         //for (int i = 2; i < empWs.RowCount(); i++)
         {
             if (LoadEmpLine(i, path) != null)
             {
                 empRecords.Add(LoadEmpLine(i, path));
             }
         }
     }
 }
Esempio n. 31
0
        private void LoadTrackerExcel(string path)
        {
            using (XLWorkbook trackerWb = new XLWorkbook(path))
            {
                // trackerRecords.Clear();
                trackerWs = trackerWb.Worksheet(1);
                //for (int i = 2; i < trackerWs.RowCount(); i++)
                for (int i = 2; i < trackerWs.LastRowUsed().RowNumber() + 1; i++)

                {
                    if (LoadTrackerLine(i, path) != null)
                    {
                        trackerRecords.Add(LoadTrackerLine(i, path));
                    }
                }
            }
        }
        private static bool SplitKD(IXLWorksheet worksheet, string title, int column)
        {
            // row to scan for KD
            const int row = 6;

            try
            {
                while (!worksheet.Cell(row, column).IsEmpty())
                {
                    // scan for first KD derivative in TRIM LEVEL row
                    if (worksheet.Cell(row, column).GetString().EndsWith("KD"))
                    {
                        // remove existing column grouping - commented out as grouping no longer applied as you can't un-group on a protected worksheet
                        // worksheet.Columns(8, worksheet.LastColumnUsed().ColumnNumber()).Ungroup();
                        // add KD SPEC GROUP heading
                        worksheet.Cell(1, column).Value = title.ToUpper() + " KD";
                        // insert and size new divider column before KD derivatives
                        worksheet.Column(column).InsertColumnsBefore(1);
                        worksheet.Column(column).Width = 8;
                        // group non-KD derivatives - commented out as you can't un-group on a protected worksheet
                        // worksheet.Columns(8, column - 1).Group();
                        // merge non-KD SPEC GROUP heading
                        worksheet.Range(1, 8, 1, column - 1).Merge();
                        // group KD derivatives - commented out as you can't un-group on a protected worksheet
                        // worksheet.Columns(column + 1, worksheet.LastColumnUsed().ColumnNumber()).Group();
                        // merge KD SPEC GROUP heading
                        worksheet.Range(1, column + 1, 1, worksheet.LastColumnUsed().ColumnNumber()).Merge();
                        // add vertical KD title
                        worksheet.Cell(1, column).Value = title.ToUpper() + " KD";
                        // merge and format vertical divider heading
                        worksheet.Range(1, column, 9, column).Merge();
                        worksheet.Range(1, column, worksheet.LastRowUsed().RowNumber(), column).Style
                            .Font.SetBold(true)
                            .Font.SetFontColor(XLColor.White)
                            .Fill.SetBackgroundColor(XLColor.Black)
                            .Alignment.SetVertical(XLAlignmentVerticalValues.Bottom)
                            .Alignment.SetTextRotation(90);
                        // do for first KD derivative then break out
                        return true;
                    }
                    column = column + 1;
                }
                return false;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw;
            }

        }
Esempio n. 33
0
        private void AddPerformanceFormatting(IXLWorksheet performanceSheet)
        {
            int lastRowUsed = performanceSheet.LastRowUsed().RowNumber();
            //freeze panels
            performanceSheet.SheetView.Freeze(1, 2);
            //performance global styles
            performanceSheet.Range(1, 1, performanceSheet.LastCellUsed().Address.RowNumber, performanceSheet.LastCellUsed().Address.ColumnNumber)
                .Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
            //column specific formatting

            //date columns
            performanceSheet.Range(string.Format("A2:A{0}", lastRowUsed)).Style.DateFormat.Format = "dd/MM/yyyy";

            //percentage formatting (0.00%)
            performanceSheet.Ranges(string.Format("E2:E{0},H2:I{1},Y2:Y{2},AC2:AC{3},AE2:AF{4},AH2:AI{5},AK2:AL{6},AN2:AO{7}",
                lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed
            )).Style.NumberFormat.NumberFormatId = 10;

            //no decimal points
            performanceSheet.Range(string.Format("R2:R{0}", lastRowUsed)).Style.NumberFormat.Format = "0";

            //decimal format (0.00)
            performanceSheet.Ranges(string.Format("J2:J{0},L2:N{1}",
                lastRowUsed, lastRowUsed
            )).Style.NumberFormat.Format = "0.00";

            //three decimal points (0.000)
            performanceSheet.Range(string.Format("U2:U{0}",
                lastRowUsed
            )).Style.NumberFormat.Format = "0.000";

            //money with two decimals ($ 0.00)
            performanceSheet.Ranges(string.Format("T2:T{0},W2:W{1},Z2:Z{2}",
                lastRowUsed, lastRowUsed, lastRowUsed
            )).Style.NumberFormat.Format = "$ 0.00";

            //money with three decimals ($ 0.000)
            performanceSheet.Range(string.Format("V2:V{0}",
                lastRowUsed, lastRowUsed, lastRowUsed
            )).Style.NumberFormat.Format = "$ 0.000";

            // adjust to content
            performanceSheet.Columns().AdjustToContents();
        }
        private int GetColumnNumber(String sheetName, String colName)
        {
            worksheet = workbook.Worksheet(sheetName);
            IXLRow r =  worksheet.Row(1);
            while (r != worksheet.LastRowUsed())
            {

                foreach(IXLCell c in r.CellsUsed())
                {
                    if (c.Value.Equals(colName))
                        return c.Address.ColumnNumber; // Starts with 0
                }

                r = r.RowBelow();
            }
            return -1;
        }
 public int GetRowCount(string sheetName)
 {
     worksheet = workbook.Worksheet(sheetName);
     return worksheet.LastRowUsed().RowNumber();
 }
 private static IXLRow FindRowById(string id, IXLWorksheet worksheet)
 {
     var lastRowUsed = worksheet.LastRowUsed().RowNumber();
     for (int rowIndex = 0; rowIndex <= lastRowUsed; rowIndex++) {
         if (worksheet.Row(rowIndex).Cell(1).GetValue<string>().ToLower() == id.ToLower()) {
             return worksheet.Row(rowIndex);
         }
     }
     return null;
 }