Beispiel #1
0
        /// <summary>
        /// Sortieren der Daten
        /// Überschriften auf BOLD setzen
        /// Autofilter Aktivieren
        /// Spaltenbreite an Inhalt anpassen
        /// </summary>
        /// <param name="worksheet"></param>
        private void SortAndFormatXlsSheet(IXLWorksheet worksheet)
        {
            ////Sortieren der Daten
            var lastCellUsed        = worksheet.LastCellUsed();
            var lastCellUsedAddress = $"A2:{lastCellUsed.Address}";
            var DataRange           = worksheet.Range(lastCellUsedAddress);

            DataRange.Sort("F, C, D, E");

            //Autofilter und Spaltenbreite an Inhalt anpassen
            worksheet.RangeUsed().SetAutoFilter();

            lastCellUsed = worksheet.LastCellUsed();
            //Spaltenbreite an Inhalt anpassen
            worksheet.Columns().AdjustToContents(1, lastCellUsed.Address.RowNumber);
            worksheet.Row(1).Style.Font.SetBold();

            //Spalten als Zahl formartieren
            worksheet.Columns("F,J").AdjustToContents(1, lastCellUsed.Address.RowNumber).Style.NumberFormat.NumberFormatId = 2;

            worksheet.Range($"I2:I{lastCellUsed.Address.RowNumber}").SetDataType(XLDataType.Number);

            //Formatieren
            //Druckbereich
            //Druckeigenschaften
        }
Beispiel #2
0
        public object  ExcelToJson(string excel_path, string isHeader, string importStartRow)
        {
            List <Dictionary <string, object> > resultJson = new List <Dictionary <string, object> >();

            List <string> header = new List <string>();

            XLWorkbook   workbook  = new XLWorkbook(excel_path);
            IXLWorksheet worksheet = workbook.Worksheets.Worksheet(1);

            var firstCell = worksheet.FirstCellUsed();
            var lastCell  = worksheet.LastCellUsed();
            var firstRow  = worksheet.FirstRow();

            int hearderColumnsCount = lastCell.Address.ColumnNumber;

            if (isHeader == "checked")
            {
                for (int col = 1; col <= hearderColumnsCount; col++)
                {
                    header.Add(worksheet.Cell(1, col).Value.ToString());
                }
            }
            else
            {
                Dictionary <string, object> jo = new Dictionary <string, object>();

                for (int col = 1; col <= hearderColumnsCount; col++)
                {
                    string id = "col_" + col.ToString();
                    header.Add(id);
                    jo.Add(id, id);
                }
                resultJson.Add(jo);
            }
            int row = 1;

            foreach (IXLRow dr in worksheet.Rows())
            {
                if (row != 1)
                {
                    if (int.Parse(importStartRow) > row)
                    {
                        row += 1;
                        continue;
                    }
                }

                Dictionary <string, object> jo = new Dictionary <string, object>();
                jo.Add("sort_order", row);
                var i = 1;
                foreach (var head in header)
                {
                    jo.Add(head, dr.Cell(i).Value.ToString());
                    i += 1;
                }
                resultJson.Add(jo);
                row += 1;
            }
            return(resultJson);
        }
Beispiel #3
0
        public IXLRange CopyTo(IXLRange range)
        {
            var tempRng = _sheet.Range(_sheet.Cell(1, 1), _sheet.LastCellUsed()); //_sheet.Cell(_prevrow, _prevclmn));

            range.InsertRowsBelow(tempRng.RowCount() - range.RowCount(), true);
            range.InsertColumnsAfter(tempRng.ColumnCount() - range.ColumnCount(), true);
            tempRng.CopyTo(range.FirstCell());

            var tgtSheet    = range.Worksheet;
            var tgtStartRow = range.RangeAddress.FirstAddress.RowNumber;

            using (var srcRows = _sheet.Rows(tempRng.RangeAddress.FirstAddress.RowNumber, tempRng.RangeAddress.LastAddress.RowNumber))
                foreach (var row in srcRows)
                {
                    var xlRow = tgtSheet.Row(row.RowNumber() + tgtStartRow - 1);
                    xlRow.OutlineLevel = row.OutlineLevel;
                    if (row.IsHidden)
                    {
                        xlRow.Collapse();
                    }
                    else
                    {
                        xlRow.Expand();
                    }
                }
            return(range);
        }
Beispiel #4
0
        private void button2_Click(object sender, EventArgs e)
        {
            IXLWorkbook  wb = new XLWorkbook();                  //Create workbook used closedXML
            IXLWorksheet ws = wb.Worksheets.Add("Sample Sheet"); //In workbook create worksheet and give name

            //To delete worksheet ---> wb.Worksheet("Sample Sheet").Delete();


            //Give headers using textbox inputs

            ws.Cell(1, 1).Value = box_0101.Text;
            ws.Cell(1, 2).Value = box_0102.Text;
            ws.Cell(1, 3).Value = box_0103.Text;
            ws.Cell(1, 4).Value = box_0104.Text;
            ws.Cell(1, 5).Value = box_0105.Text;
            ws.Cell(1, 6).Value = box_0106.Text;
            ws.Cell(1, 7).Value = box_0107.Text;
            ws.Cell(1, 8).Value = box_0108.Text;

            //select range in cells
            //IXLRange rng = ws.Range("A1:H1");
            var firstCell = ws.FirstCellUsed();
            var lastCell  = ws.LastCellUsed();
            var range     = ws.Range(firstCell.Address, lastCell.Address);

            range.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;


            ws.Row(1).Sort();

            wb.SaveAs(@"Y:\Liverpool projects\Windows form app\Ruth\test.xlsx");
        }
Beispiel #5
0
        /// <summary>
        ///     Infoシートに情報を書き込む
        /// </summary>
        /// <param name="info">Infoシート</param>
        private void setInfo(IXLWorksheet info)
        {
            setInfoHeader(info);

            // 画像情報
            info.Cell(3, 2).SetValue(FileName);
            info.Cell(4, 2).SetValue(string.Format("{0} x {1}", Image.Width, Image.Height));
            info.Cell(5, 2).SetValue(PaletteName);

            // ビーズ情報
            int i = 0;

            Palette.Where(x => x.Count > 0).ForEach(bead =>
            {
                int row = 8 + i++;
                info.Cell(row, 1).SetValue(bead.No)
                .Style.Fill.SetBackgroundColor(bead.Color)
                .Font.SetFontColor(bead.FontColor);
                info.Cell(row, 2).SetValue(bead.Name);
                info.Cell(row, 3).Value = bead.Count;
                bead.Total += bead.Count;
                bead.Count  = 0;
            });

            info.Range(info.Cell(7, 1), info.LastCellUsed())
            .Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
            .Alignment.SetVertical(XLAlignmentVerticalValues.Center)
            .Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
            .Border.SetInsideBorder(XLBorderStyleValues.Dashed);
        }
Beispiel #6
0
        // ワークシートをCSVとして保存
        public static void SaveAsCsv(IXLWorksheet worksheet, string filename)
        {
            var lines        = new List <string>();
            var lastCellUsed = worksheet.LastCellUsed();

            if (lastCellUsed != null)
            {
                var rows = worksheet.Rows(1, lastCellUsed.Address.RowNumber);
                foreach (var row in rows)
                {
                    var values = new List <string>();
                    var cells  = row.Cells(1, lastCellUsed.Address.ColumnNumber);

                    foreach (var cell in cells)
                    {
                        if (cell.HasFormula) // エラーになるので対応
                        {
                            values.Add(cell.ValueCached.ToString());
                        }
                        else
                        {
                            values.Add(cell.Value.ToString());
                        }
                    }

                    string line = string.Join(";", values);
                    lines.Add(line);
                }
            }

            File.WriteAllLines(filename, lines);
        }
        private DataTable TransferExcelToDataTable(IXLWorksheet xlWorksheet)
        {
            var datatable = new DataTable();
            var range     = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed());

            int col = range.ColumnCount();
            int row = range.RowCount();

            // add columns hedars
            datatable.Clear();

            for (int i = 1; i <= col; i++)
            {
                IXLCell column = xlWorksheet.Cell(1, i);
                datatable.Columns.Add(column.Value.ToString());
            }

            // add rows data
            int firstHeadRow = 0;

            foreach (var item in range.Rows())
            {
                if (firstHeadRow != 0)
                {
                    var array = new object[col];
                    for (int y = 1; y <= col; y++)
                    {
                        array[y - 1] = item.Cell(y).Value;
                    }
                    datatable.Rows.Add(array);
                }
                firstHeadRow++;
            }
            return(datatable);
        }
        private int GetRowCount(IXLWorksheet worksheet)
        {
            var mainFirstTableCell = worksheet.FirstCellUsed();
            var mainLastTableCell  = worksheet.LastCellUsed();

            return(mainFirstTableCell == null || mainLastTableCell == null
                ? 1
                : worksheet.Range(mainFirstTableCell.Address, mainLastTableCell.Address).RowCount() + 1);
        }
Beispiel #9
0
        public static void ReadFromXml(string filePath)
        {
            XLWorkbook   wb = new XLWorkbook(filePath);
            IXLWorksheet ws = wb.Worksheets.First();

            IXLTable table = ws.Range(ws.FirstCellUsed(), ws.LastCellUsed()).AsTable();

            /*string s = "";
             * table.DataRange.CellsUsed().Count();
             * table.DataRange.Rows().ForEach(row =>
             * {
             *  s += "\n";
             *  row.Cells().ToList().ForEach(cell =>
             *  {
             *      s += cell.GetString()+"\t|||\t";
             *  });
             * });
             * if (true)
             * {
             *
             * }*/

            TimeTable orar      = new TimeTable();
            IXLCell   grupaCell = null;

            #region grupa

            //cauta coloana care contine grupa
            IXLRangeColumn grupaColoana = table.DataRange.FindColumn(column =>
            {
                grupaCell = column.Cells()?.FirstOrDefault(cell => cell.GetString().Equals("Grupa"));
                return(grupaCell != null);
                //return column.Cells().Any(cell => cell.GetString().Equals("Grupa"));
            });
            int id = 1;
            //insereaza grupele care apar in excel in orar
            grupaColoana.CellsUsed().Where(cell => !cell.GetString().Equals("Grupa")).ForEach(cell =>
            {
                orar.Groups.Add(new GroupDto(id++, cell.GetString()));
            });

            #endregion

            table.Range(grupaCell.Address.RowNumber + 1, grupaCell.Address.ColumnNumber + 2,
                        table.LastCellUsed().Address.RowNumber, table.LastCellUsed().Address.ColumnNumber)
            .CellsUsed().Where(cell => !table.Cell(cell.Address.RowNumber, 3).IsEmpty()).ForEach(cell =>
            {
                string[] split = cell.GetString().Split(",".ToCharArray());
                //Programare prog = new Programare();

                string materie    = split[0];
                string tipMaterie = split[1];
                string sala       = split[2];
                string profesor   = split[3];
            });
        }
        public void SetPrevCellToLastUsed()
        {
            var lastUsed = _sheet.LastCellUsed();
            var clmn     = _clmn < lastUsed.Address.ColumnNumber
                ? lastUsed.Address.ColumnNumber + 1
                : _clmn;

            ChangeAddress(lastUsed.Address.RowNumber, clmn);
            NewRow();
        }
Beispiel #11
0
        private IXLTable CreateTable <T>(IEnumerable <T> listOfEntities, IXLRow emptyRow)
        {
            var r     = emptyRow.RowBelow().RangeAddress;
            var first = r.FirstAddress;

            ws.Cell(first).InsertData(listOfEntities);
            var range = ws.Range(emptyRow.FirstCell(), ws.LastCellUsed());

            return(range.CreateTable());
        }
Beispiel #12
0
        /// <summary>
        /// Calcula la siguiente posición libre que habrá
        /// en la tabla excel
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="posicionInicial"></param>
        /// <returns></returns>
        private PosicionTabla SiguientePosicion(IXLWorksheet worksheet, PosicionTabla posicionInicial)
        {
            var celda = worksheet.LastCellUsed();

            if (celda == null)
            {
                return(posicionInicial);
            }

            return(new PosicionTabla(posicionInicial.Columna, celda.Address.RowNumber + 1));
        }
        public void LastCellFromMerge()
        {
            var          wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Sheet");

            ws.Range("B2:D4").Merge();

            string first = ws.FirstCellUsed(true).Address.ToStringRelative();
            string last  = ws.LastCellUsed(true).Address.ToStringRelative();

            Assert.AreEqual("B2", first);
            Assert.AreEqual("D4", last);
        }
Beispiel #14
0
        public static void Delete(IXLWorksheet ws)
        {
            var firstDataCell = ws.Cell("B4");
            var lastDataCell  = ws.LastCellUsed();
            var rng           = ws.Range(firstDataCell.Address, lastDataCell.Address);

            using (var range = rng.Rows(r => !string.IsNullOrWhiteSpace(r.Cell(3).GetString()) && !r.Cell(3).GetBoolean()))
            {
                foreach (var item in range)
                {
                    item.Delete();
                }
            }
        }
        private static void CreateTable <TData>(IXLWorksheet worksheet, IList <ExcelColumnInfo <TData> > excelColumnInfos)
        {
            var firstCell = worksheet.FirstCellUsed();
            var lastCell  = worksheet.LastCellUsed();
            var range     = worksheet.Range(firstCell.Address, lastCell.Address);

            foreach (var excelColumnInfo in excelColumnInfos.Where(x => x.DataFormat != null))
            {
                var index = excelColumnInfos.IndexOf(excelColumnInfo) + 1;
                range.Column(index).Style.NumberFormat.Format = excelColumnInfo.DataFormat;
            }

            range.CreateTable();

            worksheet.Columns().AdjustToContents();
        }
        public static void AreWorksheetsContentEquals(IXLWorksheet expected, IXLWorksheet actual)
        {
            if (expected == actual)
            {
                return;
            }

            Assert.AreEqual(expected.CellsUsed(XLCellsUsedOptions.All).Count(), actual.CellsUsed(XLCellsUsedOptions.All).Count(), "Cells used count failed");

            IXLCell expectedFirstCellUsed = expected.FirstCellUsed(XLCellsUsedOptions.All);
            IXLCell actualFirstCellUsed   = actual.FirstCellUsed(XLCellsUsedOptions.All);

            Assert.AreEqual(expectedFirstCellUsed.Address, actualFirstCellUsed.Address, "First cell used failed");
            IXLCell expectedLastCellUsed = expected.LastCellUsed(XLCellsUsedOptions.All);
            IXLCell actualLastCellUsed   = actual.LastCellUsed(XLCellsUsedOptions.All);

            Assert.AreEqual(expectedLastCellUsed.Address, actualLastCellUsed.Address, "Last cell used failed");

            IXLRange range = expected.Range(expectedFirstCellUsed, expectedLastCellUsed);

            foreach (IXLCell expectedCell in range.Cells())
            {
                IXLCell actualCell = actual.Cell(expectedCell.Address);
                if (expectedCell.HasFormula)
                {
                    Assert.AreEqual(expectedCell.FormulaA1, actualCell.FormulaA1, $"Cell {expectedCell.Address} FormulaA1 failed.");
                    //// For some reason sometimes the formulas "FormulaR1C1" are different although the formulas "FormulaA1" are match
                    //Assert.AreEqual(expectedCell.FormulaR1C1, actualCell.FormulaR1C1, $"Cell {expectedCell.Address} FormulaR1C1 failed.");
                    Assert.AreEqual(expectedCell.FormulaReference, actualCell.FormulaReference, $"Cell {expectedCell.Address} FormulaReference failed.");
                }
                else
                {
                    Assert.AreEqual(expectedCell.Value, actualCell.Value, $"Cell {expectedCell.Address} Value failed.");
                }
                Assert.AreEqual(expectedCell.DataType, actualCell.DataType, $"Cell {expectedCell.Address} DataType failed.");
                Assert.AreEqual(expectedCell.Active, actualCell.Active, $"Cell {expectedCell.Address} Active failed.");
                AreColumnsEquals(expectedCell.WorksheetColumn(), actualCell.WorksheetColumn(), $"Column {actualCell.WorksheetColumn().RangeAddress} {{0}} failed.");
                AreRowEquals(expectedCell.WorksheetRow(), actualCell.WorksheetRow(), $"Row {actualCell.WorksheetRow().RangeAddress} {{0}} failed.");
                AreCellsStyleEquals(expectedCell.Style, actualCell.Style, $"Cell {expectedCell.Address} Style {{0}} failed.");
                AreCellsCommentEquals(expectedCell.Comment, actualCell.Comment, $"Cell {expectedCell.Address} Comment {{0}} failed.");
            }

            AreMergedRangesEquals(expected.MergedRanges, actual.MergedRanges);
            AreNamedRangesEquals(expected.NamedRanges, actual.NamedRanges);
            ArePageSetupEquals(expected.PageSetup, actual.PageSetup, "PageSetup {0} failed.");
        }
Beispiel #17
0
        /// <summary>
        ///     Resultシートに情報を書き込む
        /// </summary>
        /// <param name="result">Resultシート</param>
        public void setResult(IXLWorksheet result)
        {
            // タイトル
            result.Cell(1, 1).SetValue(Text)
            .Style.Font.SetFontSize(20.0)
            .Font.SetBold();

            // パレット情報
            result.Cell(3, 1).SetValue("パレット")
            .Style.Font.SetBold()
            .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right)
            .Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
            .Border.SetInsideBorder(XLBorderStyleValues.Dashed)
            .Fill.SetBackgroundColor(XLColor.LightGray);
            result.Cell(3, 2).SetValue(PaletteName);

            // ビーズ情報
            result.Cell(5, 1).SetValue("No");
            result.Cell(5, 2).SetValue("名前");
            result.Cell(5, 3).SetValue("個数");
            result.Range(5, 1, 5, 3).Style.Font.SetBold()
            .Fill.SetBackgroundColor(XLColor.LightGray);

            int i = 0;

            Palette.Where((x) => x.Total > 0).ForEach((bead) =>
            {
                int row = 6 + i++;
                result.Cell(row, 1).SetValue(bead.No)
                .Style.Fill.SetBackgroundColor(bead.Color)
                .Font.SetFontColor(bead.FontColor);
                result.Cell(row, 2).SetValue(bead.Name);
                result.Cell(row, 3).Value = bead.Total;
            });

            result.Range(result.Cell(5, 1), result.LastCellUsed())
            .Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
            .Alignment.SetVertical(XLAlignmentVerticalValues.Center)
            .Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
            .Border.SetInsideBorder(XLBorderStyleValues.Dashed);

            // 行列幅
            result.Columns(1, 1).Width = 16.43;
            result.Columns(2, 2).Width = 23.57;
            result.Rows(1, 1).Height   = 26.25;
        }
        private bool LoadFromWorksheet(IXLWorksheet worksheet, out IXLRange range)
        {
            var numberRow         = _fileMerge[_indexFile].HeaderLength + (_fileMerge[_indexFile].HeaderLength == 0 ? 1 : 0);
            var finalCell         = worksheet.LastCellUsed().Address;
            var cellAdressInitial = string.Empty;

            switch (_headerAction)
            {
            case HeaderAction.ConsiderFirstFile:
                if (_copiedOnlyFirstHeader)
                {
                    cellAdressInitial = $"A{numberRow + 1}";
                }
                else
                {
                    if (_indexFile == 0 && _indexSheet == 0)
                    {
                        cellAdressInitial      = $"A{numberRow}";
                        _copiedOnlyFirstHeader = true;
                    }
                }
                break;

            case HeaderAction.IgnoreAll:
                cellAdressInitial = $"A{numberRow + 1}";
                break;

            case HeaderAction.None:
            default:
                cellAdressInitial = "A1";
                break;
            }

            range = worksheet.Range(worksheet.Cell(cellAdressInitial).Address, finalCell);

            return(worksheet.RangeUsed().RowCount() >= numberRow);
        }
Beispiel #19
0
        /// <summary>
        /// To the data table.
        /// </summary>
        /// <param name="xlWorksheet">The xl worksheet.</param>
        /// <returns>DataTable.</returns>
        public static DataTable ToDataTable(this IXLWorksheet xlWorksheet)
        {
            var datatable  = new DataTable();
            var errorFound = false;

            try
            {
                var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed()).RangeUsed();

                var columnCount = range.ColumnCount();
                var rowCount    = range.RowCount();
                datatable.Clear();

                //
                // Create our columns bases on the first row which contains the itemName and category names
                //
                try
                {
                    if (rowCount < 1)
                    {
                        return(new DataTable());
                    }
                    var goodColumnCount = 0;
                    for (var i = 1; i <= columnCount; i++)
                    {
                        if (xlWorksheet.Cell(1, i).Value.ToString().GetClsString().IsNullOrEmpty())
                        {
                            break;
                        }
                        goodColumnCount++;
                        datatable.Columns.Add(new DataColumn
                        {
                            DataType   = Type.GetType("System.String"),
                            ColumnName = xlWorksheet.Cell(1, i).Value.ToString().GetClsString(),
                            Caption    = xlWorksheet.Cell(1, i).Value.ToString()
                        }
                                              );
                    }
                    columnCount = goodColumnCount;
                }
                catch (Exception ex)
                {
                    NLogger.Warn("Unable to create the data table columns. {0}", ex.Message);
                    return(new DataTable());
                }

                //
                // Copy cell data into our datatable.
                //
                if (rowCount < 2)
                {
                    return(datatable);
                }
                for (var i = 2; i <= rowCount; i++)
                {
                    if (range.Row(i).Cell(1).Value.ToString().IsNullOrEmpty())
                    {
                        continue;
                    }
                    var array = new object[columnCount];
                    for (var y = 1; y <= columnCount; y++)
                    {
                        try
                        {
                            if (range.Row(i).Cell(y).HasFormula)
                            {
                                NLogger.Warn("Unable to process cells which contain a forumlas (to remove formulas: copy -> paste special values). Check on Sheet {0} row {1} column {2}", xlWorksheet.Name, i, y);
                                errorFound = true;
                                continue;
                            }
                            array[y - 1] = range.Row(i).Cell(y).Value.ToString();
                            if (y == 1)
                            {
                                //if (array[y-1].ToString().IsEqualTo("Spurlock - SP03 Bed Ash Silo Chute", true)) System.Diagnostics.Debugger.Break();
                                NLogger.Trace(array[y - 1]);
                            }
                        }
                        catch (Exception ex)
                        {
                            errorFound = true;
                            NLogger.Warn("Error on Sheet {0} row {1} column {2}", xlWorksheet.Name, i, y);
                            NLogger.Trace(ex.Message);
                        }
                    }
                    if (ArrayHasValue(array))
                    {
                        datatable.Rows.Add(array);
                    }
                }
                if (errorFound)
                {
                    throw new Exception("There are value errors in the spreadsheet.");
                }
            }
            catch (Exception ex)
            {
                NLogger.Warn(ex.Message);
                datatable = new DataTable();
            }
            return(datatable);
        }
Beispiel #20
0
        ///----------------------------------------------------
        /// <summary>
        ///     稼働表作成 : closedXML版 2018/02/22</summary>
        ///----------------------------------------------------
        public void worksOutputXML()
        {
            DateTime stDate;
            DateTime edDate;

            //IXLWorksheet tmpSheet = null;

            try
            {
                using (var book = new XLWorkbook(Properties.Settings.Default.xlsKadouPath, XLEventTracking.Disabled))
                {
                    // 稼働予定開始年月日
                    stDate = DateTime.Parse(DateTime.Today.Year.ToString() + "/" + DateTime.Today.Month.ToString() + "/01");

                    // 稼働予定終了年月日
                    edDate = stDate.AddMonths(6).AddDays(-1);

                    int ew = 0;

                    while (ew < sCnt)
                    {
                        // 言語別シートを作成
                        for (int i = 1; i <= gengo.GetLength(0); i++)
                        {
                            int sNum = i + (10 * ew);

                            // シートを追加する 2018/02/22
                            if (ew == cEAST)
                            {
                                book.Worksheet("東").CopyTo(book, sheetName[ew] + "・" + gengo[i - 1, 1], sNum);
                            }
                            else if (ew == cWEST)
                            {
                                book.Worksheet("西").CopyTo(book, sheetName[ew] + "・" + gengo[i - 1, 1], sNum);
                            }

                            // カレントシート
                            IXLWorksheet tmpSheet = book.Worksheet(sNum);

                            int xCol = 0;   // 日列初期値

                            // 稼働予定期間のカレンダーをセット
                            for (int mon = 0; mon < 6; mon++)
                            {
                                // 該当月
                                DateTime wDt = stDate.AddMonths(mon);
                                xCol = 31 * mon + ew + 9;
                                //tmpSheet.Cell(1, xCol).Value = wDt.ToShortDateString(); // 9,40,71,102,・・・ 2018/02/23
                                tmpSheet.Cell(1, xCol).SetValue(wDt.Year + "年" + wDt.Month + "月"); // 9,40,71,102,・・・ 2018/02/28

                                // 年月と開始列の配列にセット
                                sheetYYMM[mon, 0] = wDt.Year.ToString() + wDt.Month.ToString().PadLeft(2, '0');
                                sheetYYMM[mon, 1] = xCol.ToString();

                                DateTime dDay;

                                // 該当月の暦
                                int dy = 0;
                                while (dy < 31)
                                {
                                    if (DateTime.TryParse(wDt.Year.ToString() + "/" + wDt.Month.ToString() + "/" + (dy + 1).ToString(), out dDay))
                                    {
                                        // 2018/02/23
                                        tmpSheet.Cell(2, xCol + dy).SetValue((dy + 1).ToString());    // 日
                                        tmpSheet.Cell(3, xCol + dy).SetValue(dDay.ToString("ddd"));   // 曜日
                                    }
                                    else
                                    {
                                        // 2018/02/23
                                        tmpSheet.Cell(2, xCol + dy).SetValue(string.Empty);
                                        tmpSheet.Cell(3, xCol + dy).SetValue(string.Empty);
                                    }

                                    dy++;
                                }
                            }

                            // 組合員予定申告データを取得
                            string cardNum = string.Empty;
                            string gCode   = gengo[i - 1, 0];
                            int    sRow    = sheetStRow;

                            jfgDataClassDataContext db = new jfgDataClassDataContext();

                            // 東・LINQ
                            var linqEast = db.会員情報.Where(a => (a.言語1 == int.Parse(gCode) || a.言語2 == int.Parse(gCode) ||
                                                               a.言語3 == int.Parse(gCode) || a.言語4 == int.Parse(gCode) ||
                                                               a.言語5 == int.Parse(gCode)) && a.東西 == 1)
                                           .OrderBy(a => a.会員稼働予定.フリガナ).ThenBy(a => a.会員稼働予定.カード番号).ThenBy(a => a.会員稼働予定.年).ThenBy(a => a.会員稼働予定.月)
                                           .Select(a => new
                            {
                                cardno = a.カード番号,
                                氏名     = a.氏名,
                                携帯電話番号 = a.携帯電話番号,
                                JFG加入年 = a.JFG加入年,
                                a.会員稼働予定
                            });

                            // 西・LINQ
                            var linqWest = db.会員情報.Where(a => (a.言語1 == int.Parse(gCode) || a.言語2 == int.Parse(gCode) ||
                                                               a.言語3 == int.Parse(gCode) || a.言語4 == int.Parse(gCode) ||
                                                               a.言語5 == int.Parse(gCode)) && a.東西 == 2)
                                           .OrderBy(a => a.地域コード).ThenBy(a => a.会員稼働予定.フリガナ).ThenBy(a => a.カード番号).ThenBy(a => a.会員稼働予定.年).ThenBy(a => a.会員稼働予定.月)
                                           .Select(a => new
                            {
                                地域コード  = a.地域コード,
                                地域名    = a.地域名,
                                cardno = a.カード番号,
                                氏名     = a.氏名,
                                携帯電話番号 = a.携帯電話番号,
                                JFG加入年 = a.JFG加入年,
                                a.会員稼働予定
                            });

                            if (ew == cEAST)    // 東
                            {
                                // 組合員予定申告データクラスのインスタンス生成
                                clsWorksTbl w = new clsWorksTbl();
                                w.cardNumBox = string.Empty;
                                w.sRow       = sheetStRow;
                                w.ew         = ew;

                                foreach (var t in linqEast)
                                {
                                    w.cardNo = t.cardno;
                                    w.氏名     = t.氏名;
                                    w.携帯電話番号 = t.携帯電話番号;
                                    w.JFG加入年 = (short)t.JFG加入年;
                                    w.会員稼働予定 = t.会員稼働予定;

                                    // エクセル稼働表作成 2018/02/26
                                    if (!xlsCellsSetXML(w, tmpSheet))
                                    {
                                        continue;
                                    }
                                }
                            }
                            else if (ew == cWEST)   // 西
                            {
                                // 組合員予定申告データクラスのインスタンス生成
                                clsWorksTbl w = new clsWorksTbl();
                                w.cardNumBox = string.Empty;
                                w.sRow       = sheetStRow;
                                w.ew         = ew;

                                foreach (var t in linqWest)
                                {
                                    w.地域コード  = (int)t.地域コード;
                                    w.地域名    = t.地域名;
                                    w.cardNo = t.cardno;
                                    w.氏名     = t.氏名;
                                    w.携帯電話番号 = t.携帯電話番号;
                                    w.JFG加入年 = (short)t.JFG加入年;
                                    w.会員稼働予定 = t.会員稼働予定;

                                    // エクセル稼働表作成 2018/02/26
                                    if (!xlsCellsSetXML(w, tmpSheet))
                                    {
                                        continue;
                                    }
                                }
                            }

                            // カレンダーにない日の列削除
                            bool colDelStatus = true;

                            // 2018/02/26
                            while (colDelStatus)
                            {
                                for (int cl = (ew + 9); cl <= tmpSheet.RangeUsed().RangeAddress.LastAddress.ColumnNumber; cl++)
                                {
                                    if (!Utility.NumericCheck(Utility.nulltoString(tmpSheet.Cell(2, cl).Value).Trim()))
                                    {
                                        tmpSheet.Column(cl).Delete();  // 2018/02/26
                                        colDelStatus = true;
                                        break;
                                    }
                                    else
                                    {
                                        colDelStatus = false;
                                    }
                                }
                            }

                            // 年月を表すセルを結合する 2018/02/28
                            int stCell = 0;
                            int edCell = 0;

                            tmpSheet.Range(tmpSheet.Cell(1, ew + 9).Address,
                                           tmpSheet.Cell(1, tmpSheet.LastCellUsed().Address.ColumnNumber).Address).Style
                            .Border.BottomBorder = XLBorderStyleValues.Thin;
                            for (int cl = (ew + 9); cl <= tmpSheet.LastCellUsed().Address.ColumnNumber; cl++)
                            {
                                if (Utility.nulltoString(tmpSheet.Cell(2, cl).Value).Trim() == "1")
                                {
                                    if (stCell == 0)
                                    {
                                        stCell = cl;
                                    }
                                    else
                                    {
                                        // セル結合
                                        tmpSheet.Range(tmpSheet.Cell(1, stCell).Address, tmpSheet.Cell(1, edCell).Address).Merge(false);

                                        // IsMerge()パフォ劣化回避のためのStyle変更
                                        for (int cc = stCell; cc <= edCell; cc++)
                                        {
                                            tmpSheet.Cell(1, cc).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                        }

                                        stCell = cl;
                                    }
                                }
                                else
                                {
                                    edCell = cl;
                                }
                            }

                            if (stCell != 0)
                            {
                                // セル結合
                                tmpSheet.Range(tmpSheet.Cell(1, stCell).Address, tmpSheet.Cell(1, edCell).Address).Merge(false);

                                // IsMerge()パフォ劣化回避のためのStyle変更
                                for (int cc = stCell; cc <= edCell; cc++)
                                {
                                    tmpSheet.Cell(1, cc).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                }
                            }

                            // 表の外枠罫線を引く 2018/02/26
                            var range = tmpSheet.Range(tmpSheet.Cell("A1").Address, tmpSheet.LastCellUsed().Address);
                            range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;

                            // 年月セル下部に罫線を引く 2018/02/28
                            tmpSheet.Range(tmpSheet.Cell(1, ew + 9).Address,
                                           tmpSheet.Cell(1, tmpSheet.LastCellUsed().Address.ColumnNumber).Address).Style
                            .Border.BottomBorder = XLBorderStyleValues.Thin;

                            tmpSheet.Range(tmpSheet.Cell(2, ew + 9).Address,
                                           tmpSheet.Cell(2, tmpSheet.LastCellUsed().Address.ColumnNumber).Address).Style
                            .Border.BottomBorder = XLBorderStyleValues.Dotted;

                            // 明細最上部に罫線を引く 2018/02/27
                            tmpSheet.Range(tmpSheet.Cell("A4").Address,
                                           tmpSheet.Cell(4, tmpSheet.LastCellUsed().Address.ColumnNumber).Address).Style
                            .Border.TopBorder = XLBorderStyleValues.Thin;

                            // 表の外枠左罫線を引く 2018/02/27
                            tmpSheet.Range(tmpSheet.Cell("A1").Address, tmpSheet.LastCellUsed().Address).Style
                            .Border.LeftBorder = XLBorderStyleValues.Thin;

                            // 見出しの背景色 2018/02/28
                            tmpSheet.Range(tmpSheet.Cell("A1").Address, tmpSheet.Cell(3, tmpSheet.LastCellUsed().Address.ColumnNumber).Address)
                            .Style.Fill.BackgroundColor = XLColor.WhiteSmoke;

                            // 日曜日の背景色
                            range = tmpSheet.Range(tmpSheet.Cell(3, ew + 9).Address, tmpSheet.Cell(3, tmpSheet.LastCellUsed().Address.ColumnNumber).Address);
                            range.AddConditionalFormat()
                            .WhenEquals("日")
                            .Fill.SetBackgroundColor(XLColor.MistyRose);

                            var range2 = tmpSheet.Range(tmpSheet.Cell(2, ew + 9).Address, tmpSheet.Cell(2, tmpSheet.LastCellUsed().Address.ColumnNumber).Address);

                            if (ew == cEAST)
                            {
                                // 日曜日の日付の背景色
                                range2.AddConditionalFormat()
                                .WhenIsTrue("=I3=" + @"""日""")
                                .Fill.BackgroundColor = XLColor.MistyRose;

                                // ウィンドウ枠の固定
                                tmpSheet.SheetView.Freeze(3, 2);

                                // 見出し
                                tmpSheet.Cell("A2").SetValue("氏名").Style.Font.SetBold(true).Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                                tmpSheet.Cell("B2").SetValue("フリガナ").Style.Font.SetBold(true).Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                                tmpSheet.Cell("C2").SetValue("入会年度").Style.Font.SetBold(true);
                                tmpSheet.Cell("D2").SetValue("携帯電話").Style.Font.SetBold(true);
                                tmpSheet.Cell("E2").SetValue("稼働日数").Style.Font.SetBold(true);
                                tmpSheet.Cell("F2").SetValue("自己申告").Style.Font.SetBold(true);
                                tmpSheet.Cell("F3").SetValue("日数").Style.Font.SetBold(true);
                                tmpSheet.Cell("G2").SetValue("備考").Style.Font.SetBold(true);
                                tmpSheet.Cell("H2").SetValue("更新日").Style.Font.SetBold(true);

                                // 見出しはBold 2018/02/28
                                tmpSheet.Range(tmpSheet.Cell("I1").Address, tmpSheet.Cell(3, tmpSheet.LastCellUsed().Address.ColumnNumber).Address)
                                .Style.Font.SetBold(true);
                            }
                            else if (ew == cWEST)
                            {
                                // 日曜日の日付の背景色
                                range2.AddConditionalFormat()
                                .WhenIsTrue("=J3=" + @"""日""")
                                .Fill.BackgroundColor = XLColor.MistyRose;

                                // ウィンドウ枠の固定
                                tmpSheet.SheetView.Freeze(3, 3);

                                // 見出し
                                tmpSheet.Cell("A2").SetValue("地域").Style.Font.SetBold(true).Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                                tmpSheet.Cell("B2").SetValue("氏名").Style.Font.SetBold(true).Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                                tmpSheet.Cell("C2").SetValue("フリガナ").Style.Font.SetBold(true).Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                                tmpSheet.Cell("D2").SetValue("入会年度").Style.Font.SetBold(true);
                                tmpSheet.Cell("E2").SetValue("携帯電話").Style.Font.SetBold(true);
                                tmpSheet.Cell("F2").SetValue("稼働日数").Style.Font.SetBold(true);
                                tmpSheet.Cell("G2").SetValue("自己申告").Style.Font.SetBold(true);
                                tmpSheet.Cell("G3").SetValue("日数").Style.Font.SetBold(true);
                                tmpSheet.Cell("H2").SetValue("備考").Style.Font.SetBold(true);
                                tmpSheet.Cell("I2").SetValue("更新日").Style.Font.SetBold(true);

                                // 見出しはBold 2018/02/28
                                tmpSheet.Range(tmpSheet.Cell("J1").Address, tmpSheet.Cell(3, tmpSheet.LastCellUsed().Address.ColumnNumber).Address)
                                .Style.Font.SetBold(true);
                            }
                        }

                        ew++;
                    }

                    // テンプレートシートは削除する 2018/02/26
                    book.Worksheet("東").Delete();
                    book.Worksheet("西").Delete();

                    // カレントシート 2018/02/26
                    //tmpSheet = book.Worksheet(1);

                    //保存処理 2018/02/26
                    book.SaveAs(Properties.Settings.Default.xlsWorksPath);
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            finally
            {
            }
        }
Beispiel #21
0
        private DataTable ConvertWorksheetToDataTable(IXLWorksheet worksheet)
        {
            var dataTable = new DataTable();

            List <int> formattedColumnNumbers = new List <int>();

            IXLRange range = worksheet.Range(worksheet.FirstCellUsed(), worksheet.LastCellUsed());

            int columnCount = range.ColumnCount();

            dataTable.Clear();

            for (int i = 1; i <= columnCount; i++)
            {
                IXLCell cell = worksheet.Cell(1, i);

                Type dataTableColumnType = GetDataType(range, cell.Address.ColumnNumber);

                dataTable.Columns.Add(cell.Value.ToString(), dataTableColumnType);
            }

            int firstHeadRow = 0;

            foreach (var row in range.Rows())
            {
                if (firstHeadRow != 0)
                {
                    var array = new object[columnCount];

                    for (int y = 1; y <= columnCount; y++)
                    {
                        if (!row.Cell(y).HasFormula)
                        {
                            if (row.Cell(y).Value == null || string.IsNullOrEmpty(row.Cell(y).Value.ToString()))
                            {
                                array[y - 1] = null;
                            }
                            else if (formattedColumnNumbers.Contains(row.Cell(y).Address.ColumnNumber))
                            {
                                array[y - 1] = row.Cell(y).GetFormattedString();
                            }
                            else if (row.Cell(y).DataType == XLDataType.DateTime)
                            {
                                DateTime dateTime = row.Cell(y).GetDateTime();

                                array[y - 1] = dateTime.TimeOfDay.Ticks == 0
                                    ? dateTime.ToString("MM/dd/yyyy")
                                    : dateTime.ToString();
                            }
                            else
                            {
                                row.Cell(y).SetDataType(XLDataType.Text);

                                array[y - 1] = row.Cell(y).Value;
                            }
                        }
                    }

                    dataTable.Rows.Add(array);
                }

                firstHeadRow++;
            }

            return(dataTable);
        }
Beispiel #22
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();
        }
        //TODO:
        //Throw Exception
        public DataTable InsertTblRegisteredStudents(String FilePath)
        {
            DataTable dt = null;

            using (XLWorkbook workBook = new XLWorkbook(FilePath))
            {
                //Read the first Sheet from Excel file.
                IXLWorksheet workSheet = workBook.Worksheet(1);
                //Create a new DataTable.
                dt = new DataTable();
                //Loop through the Worksheet rows.
                bool firstRow = true;
                foreach (IXLRow row in workSheet.Rows())
                {
                    if (!row.Cell(1).IsEmpty())
                    {
                        //Use the first row to add columns to DataTable.
                        if (firstRow)
                        {
                            foreach (IXLCell cell in row.Cells())
                            {
                                dt.Columns.Add(cell.Value.ToString());
                            }
                            firstRow = false;
                        }

                        else
                        {
                            //Add rows to DataTable.
                            dt.Rows.Add();
                            int i = 0;
                            foreach (IXLCell cell in row.Cells(workSheet.FirstCellUsed().Address.ColumnNumber, workSheet.LastCellUsed().Address.ColumnNumber))
                            {
                                dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                                i++;
                            }
                        }
                    }
                }
            }
            return(dt);
        }
Beispiel #24
0
        private void prepDashboardSheets(IXLWorksheet sheet)
        {
            /*.
             * can use htis for both types of sheets.
             *  Regular: List.Count -1 (end of list: should be Month) List.Count -2 (last value: Should be provider name)
             *  Diabetes: List.Count -1 (end of list: Month) List.Count -3 (blank space between provider name an dmonth on this one)
             */

            _worksheet = sheet;
            //use this one to keep track of provider name and row number. then send it to metrics to dashboard and do accordignly
            Dictionary<String, IXLRangeRow> providerRows = new Dictionary<String, IXLRangeRow>();

            if (_worksheet != null)
            {
                var firstCell = _worksheet.FirstCellUsed();
                var lastCell = _worksheet.LastCellUsed();
                _worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);

                if (_worksheet_range != null)
                {
                    int nRows = _worksheet_range.RowCount();
                    int nCols = _worksheet_range.ColumnCount();
                    for (int i = 1; i < nRows + 1; i++)
                    {
                        var row = _worksheet_range.Row(i);
                        var newRow = _worksheet_range.Row(i + 1);
                        string value = row.Cell(1).Value as string;

                        if (value != null)
                        {
                            foreach (string provider in providers)
                            {
                                if (value.Contains(provider))
                                {
                                    if (_worksheet == _dashboard.Worksheet(2))//add a new row for the depression sheets
                                    {
                                        newRow = _worksheet_range.Row(i + 3);
                                        newRow.InsertRowsBelow(1);
                                        var blankRow = _worksheet_range.Row(i + 4);
                                        blankRow.Style.NumberFormat.NumberFormatId = 0;
                                        providerRows.Add(value, blankRow);

                                    }
                                    else //add a new row for every other sheet in the dashboard: Asthma, Diabetes, Cardiovascular, Preventive
                                    {
                                        newRow = _worksheet_range.Row(i + 2);//this gets us int he right area and then insert the row above
                                        newRow.InsertRowsBelow(1); //try to insert rows after we have metrics and tehn insert metrics into cells then insert row
                                        var blankRow = _worksheet_range.Row(i + 3);
                                        blankRow.Style.NumberFormat.NumberFormatId = 0;
                                        providerRows.Add(value, blankRow);
                                    }
                                    break; //break out of the foreach provider loop, we already found one, we wont find another match, time to go to the next row instead
                                }
                            }
                        }
                    }
                   MetricsToDashboard(providerRows, _worksheet);//figure out what we need to send to this method , worksheet, provider / row dict etc.
                }
            }
        }
        internal static void SetAsTable(this IXLWorksheet sheet, int startRow, int startCol)
        {
            var range = sheet.Range(sheet.Cell(startRow, startCol), sheet.LastCellUsed());

            range.CreateTable("EarningsTable");
        }
        public ViewResult saveToDatabase(HttpPostedFileBase excelFile)
        {
            try
            {
                //Save the uploaded Excel file.
                string path = Server.MapPath("~/Uploads/");
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }

                excelFile.SaveAs(path + Path.GetFileName(excelFile.FileName));


                using (XLWorkbook workBook = new XLWorkbook(path + Path.GetFileName(excelFile.FileName)))
                {
                    String columnsNamesQuery = String.Format("CREATE TABLE {0} (", excelFile.FileName.Split('.')[0]);
                    String insertQuery       = String.Format("INSERT INTO {0} (", excelFile.FileName.Split('.')[0]);

                    IXLWorksheet workSheet = workBook.Worksheet(1);

                    bool firstRow = true;
                    foreach (IXLRow row in workSheet.Rows())
                    {
                        if (firstRow)
                        {
                            foreach (IXLCell cell in row.Cells())
                            {
                                columnsNamesQuery += "[" + cell.Value.ToString() + "] VARCHAR(MAX),";
                                insertQuery       += "[" + cell.Value.ToString() + "],";
                            }

                            insertQuery        = insertQuery.Remove(insertQuery.Length - 1, 1);
                            insertQuery       += ") VALUES (";
                            columnsNamesQuery  = columnsNamesQuery.Remove(columnsNamesQuery.Length - 1, 1);
                            columnsNamesQuery += ");";

                            //Creating data table
                            Command.Connection = Connection;
                            Connection.Open();
                            Command.CommandText = columnsNamesQuery;
                            Command.ExecuteNonQuery();
                            Connection.Close();

                            firstRow = false;
                        }
                        else
                        {
                            String rowsQuery = insertQuery;
                            foreach (IXLCell cell in row.Cells(workSheet.FirstCellUsed().Address.ColumnNumber, workSheet.LastCellUsed().Address.ColumnNumber))
                            {
                                if (cell.IsEmpty())
                                {
                                    rowsQuery += "NULL,";
                                }
                                else
                                {
                                    rowsQuery += "'" + cell.Value.ToString() + "' ,";
                                }
                            }
                            rowsQuery  = rowsQuery.Remove(rowsQuery.Length - 1, 1);
                            rowsQuery += ");";

                            //Insert data into database
                            Command.Connection = Connection;
                            Connection.Open();
                            Command.CommandText = rowsQuery;
                            Command.ExecuteNonQuery();
                            Connection.Close();
                        }
                    }
                }
                return(View("Success"));
            }
            catch (Exception e)
            {
                ViewBag.error = "ERROR: " + e.Message + "<br />";
                return(View("Index"));
            }
        }
Beispiel #27
0
        //カレントSheetのデータ範囲最終行を取得
        private int getEndRow()
        {
            var last = _currentWs.LastCellUsed();

            return(last.WorksheetRow().RowNumber());
        }
Beispiel #28
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);
            }
        }
 private IXLRange GetRootRange(IXLWorksheet ws, IList <IXLNamedRange> worksheetPanels)
 {
     return(ws.Range(ws.FirstCell(), GetRootRangeLastCell(ws.LastCellUsed(), worksheetPanels ?? new List <IXLNamedRange>())));
 }
        public IXLRange CopyTo(IXLRange range)
        {
            var firstCell = _sheet.Cell(1, 1);
            var lastCell  = _sheet.LastCellUsed(XLCellsUsedOptions.All) ?? firstCell;
            var tempRng   = _sheet.Range(firstCell, lastCell);

            var rowDiff = tempRng.RowCount() - range.RowCount();

            if (rowDiff > 0)
            {
                range.LastRow().RowAbove().InsertRowsBelow(rowDiff, true);
            }
            else if (rowDiff < 0)
            {
                range.Worksheet.Range(
                    range.LastRow().RowNumber() + rowDiff + 1,
                    range.FirstColumn().ColumnNumber(),
                    range.LastRow().RowNumber(),
                    range.LastColumn().ColumnNumber())
                .Delete(XLShiftDeletedCells.ShiftCellsUp);
            }

            range.Worksheet.ConditionalFormats.Remove(c => c.Range.Intersects(range));

            var columnDiff = tempRng.ColumnCount() - range.ColumnCount();

            if (columnDiff > 0)
            {
                range.InsertColumnsAfter(columnDiff, true);
            }
            else if (columnDiff < 0)
            {
                range.Worksheet.Range(
                    range.FirstRow().RowNumber(),
                    range.LastColumn().ColumnNumber() + columnDiff + 1,
                    range.LastRow().RowNumber(),
                    range.LastColumn().ColumnNumber())
                .Delete(XLShiftDeletedCells.ShiftCellsLeft);
            }

            tempRng.CopyTo(range.FirstCell());

            var tgtSheet    = range.Worksheet;
            var tgtStartRow = range.RangeAddress.FirstAddress.RowNumber;
            var srcRows     = _sheet.Rows(tempRng.RangeAddress.FirstAddress.RowNumber, tempRng.RangeAddress.LastAddress.RowNumber);

            foreach (var row in srcRows)
            {
                var xlRow = tgtSheet.Row(row.RowNumber() + tgtStartRow - 1);
                xlRow.OutlineLevel = row.OutlineLevel;
                if (row.IsHidden)
                {
                    xlRow.Collapse();
                }
                else
                {
                    xlRow.Expand();
                }
            }
            return(range);
        }
Beispiel #31
0
        public static Dictionary <string, string> ReadExcel(string path, int fCell, int fRow)
        {
            try
            {
                using (XLWorkbook workBook = new XLWorkbook(path))
                {
                    IXLWorksheet workSheet = workBook.Worksheet(1);
                    var          range     = workSheet.Range(workSheet.FirstCellUsed(), workSheet.LastCellUsed());
                    var          dict      = new Dictionary <string, string>();
                    var          i         = 0;

                    foreach (var item in range.Rows())
                    {
                        if (i >= fRow)
                        {
                            dict.TryAdd(item.Cell(fCell).Value.ToString(), item.Cell(fCell + 2).Value.ToString());
                        }
                        i++;
                    }

                    return(dict);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Beispiel #32
-1
        private List<String> ReturnMetricsRow(IXLWorksheet sheet)
        {
            List<String> metricNames = new List<string>();

            _worksheet = sheet;
            if (_worksheet != null)
            {
                var firstCell = _worksheet.FirstCellUsed();
                var lastCell = _worksheet.LastCellUsed();
                _worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);

                if (_worksheet_range != null)
                {
                    int nRows = _worksheet_range.RowCount();
                    int nCols = _worksheet_range.ColumnCount();
                    for (int i = 1; i < nRows + 1; i++)
                    {
                        var row = _worksheet_range.Row(i);
                        var newRow = _worksheet_range.Row(i + 1);
                        string value = row.Cell(1).Value as string;

                        if (value == "Month")
                        {
                            var metricRow = _worksheet_range.Row(i);
                            for (int x = 1; x <= metricRow.CellCount(); x++)
                            {
                                metricNames.Add(metricRow.Cell(x).Value.ToString());
                            }
                            break;
                        }
                    }

                }
            }
            return metricNames;
        }