public static Row FindRow(SheetData sheetData, uint rowIndex)
        {
            Row  newChild = null;
            uint index    = rowIndex;

            if (sheetData.Elements <Row>().Where(r => r.RowIndex.Value == rowIndex).Count <Row>() != 0)
            {
                return(sheetData.Elements <Row>().Where(r => r.RowIndex.Value == rowIndex).First());
            }
            newChild          = new Row();
            newChild.RowIndex = index;
            int num = 0;

            foreach (Row row2 in sheetData.Elements <Row>())
            {
                if (row2.RowIndex.Value > rowIndex)
                {
                    sheetData.InsertAt <Row>(newChild, num);
                    return(newChild);
                }
                num++;
            }
            sheetData.AppendChild(newChild);
            return(newChild);
        }
Beispiel #2
0
    public static int InsertRow(WorksheetPart worksheetPart)
    {
        SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
        Row       lastRow   = sheetData.Elements <Row>().LastOrDefault();

        if (lastRow != null)
        {
            sheetData.InsertAfter(new Row()
            {
                RowIndex = (lastRow.RowIndex + 1)
            }, lastRow);
            return((int)UInt32Value.ToUInt32(lastRow.RowIndex));
        }
        else
        {
            sheetData.InsertAt(new Row()
            {
                RowIndex = 0
            }, 0);
            return(0);
        }
    }
Beispiel #3
0
        /// <summary>
        /// Add row moi vao sheetdata
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="workbookPart"></param>
        /// <returns></returns>
        public Row RowAddNew(SheetData sheetData)
        {
            Row lastRow = sheetData.Elements <Row>().LastOrDefault();
            Row newRow;

            if (lastRow != null)
            {
                newRow = new Row()
                {
                    RowIndex = (lastRow.RowIndex + 1)
                };
                sheetData.InsertAfter(newRow, lastRow);
            }
            else
            {
                newRow = new Row()
                {
                    RowIndex = 0
                };
                sheetData.InsertAt(newRow, 0);
            }
            return(newRow);
        }
Beispiel #4
0
        /// <summary>
        /// json rows to openXml excel object
        /// see https://blog.johnwu.cc/article/asp-net-core-export-to-excel.html
        /// </summary>
        /// <param name="rows">json array</param>
        /// <param name="docx"></param>
        /// <param name="srcRowNo">excel start row, base 1</param>
        /// <returns>error msg if any</returns>
        public static string DocxByRows(JArray rows, SpreadsheetDocument docx, int srcRowNo)
        {
            #region 1.check docx
            if (docx == null)
            {
                return("_Excel.cs RowsToDocx() failed, docx is null.");
            }
            #endregion

            //2.get col name list from source rows[0]
            var rowCount = (rows == null) ? 0 : rows.Count;
            var cols     = new List <string>();
            if (rowCount > 0)
            {
                foreach (var item in (JObject)rows[0])
                {
                    cols.Add(item.Key);
                }
            }

            #region prepare excel variables
            SheetData sheetData = null;
            var       colCount  = cols.Count;
            var       sheet     = docx.WorkbookPart.Workbook.Descendants <Sheet>().FirstOrDefault();
            var       wsPart    = (WorksheetPart)docx.WorkbookPart.GetPartById(sheet.Id);
            sheetData = wsPart.Worksheet.GetFirstChild <SheetData>();
            #endregion

            //3.loop of write excel rows, use template
            for (var rowNo = 0; rowNo < rowCount; rowNo++)
            {
                //add row and fill data, TODO: copy row style
                var row    = (JObject)rows[rowNo];
                var newRow = new Row();
                for (var colNo = 0; colNo < colCount; colNo++)
                {
                    newRow.Append(new Cell()
                    {
                        CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString()),
                        DataType  = CellValues.String,
                    });
                }

                //insert row into sheet
                sheetData.InsertAt(newRow, rowNo + srcRowNo);
            }

            //case of ok
            return("");

            #region remark: no template file

            /*
             * else
             * {
             *  //no template file
             #region prepre excel-sheetData
             *  var bookPart = docx.AddWorkbookPart();
             *  bookPart.Workbook = new Workbook();
             *
             *  var sheetPart = bookPart.AddNewPart<WorksheetPart>();
             *  sheetPart.Worksheet = new Worksheet(new SheetData());
             *
             *  var sheets = bookPart.Workbook.AppendChild(new Sheets());
             *  sheets.Append(new Sheet()
             *  {
             *      Id = bookPart.GetIdOfPart(sheetPart),
             *      SheetId = 1,
             *      Name = "Sheet1",
             *  });
             *  sheetData = sheetPart.Worksheet.GetFirstChild<SheetData>();
             #endregion
             *
             *  //add header row
             *  //srcRowNo = 1;   //base 1
             *  var newRow = new Row();
             *  for (var i = 0; i < colCount; i++)
             *  {
             *      newRow.Append(new Cell()
             *      {
             *          CellValue = new CellValue(cols[i]),
             *          DataType = CellValues.String,
             *      });
             *  }
             *  sheetData.AppendChild(newRow);
             *
             *  //write excel row, no template
             *  for (var rowNo = 0; rowNo < rowCount; rowNo++)
             *  {
             *      //var excelRow = NewRow(row, colCount, cols);
             *      var row = (JObject)rows[rowNo];
             *      newRow = new Row();
             *      for (var colNo = 0; colNo < colCount; colNo++)
             *      {
             *          newRow.Append(new Cell()
             *          {
             *              CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString()),
             *              DataType = CellValues.String,
             *          });
             *      }
             *      sheetData.AppendChild(newRow);
             *  }
             * }
             */
            #endregion
        }
Beispiel #5
0
        /*
         * public static void DocxByCrud(ReadModel crud, JObject findJson, SpreadsheetDocument docx, string sheetName, List<string> headers = null, List<string> cols = null, string dbStr = "")
         * {
         *  DocxByRows(new CrudRead(dbStr).GetAllRows(crud, findJson), docx, sheetName, headers, cols);
         * }
         *
         * public static void DocxBySql(string sql, SpreadsheetDocument docx, string sheetName, List<string> headers = null, List<string> cols = null, string dbStr = "")
         * {
         *  var rows = _Db.GetJsons(sql, null, dbStr);
         *  DocxByRows(rows, docx, sheetName, headers, cols);
         * }
         */

        /// <summary>
        /// json rows to openXml object
        /// see https://blog.johnwu.cc/article/asp-net-core-export-to-excel.html
        /// </summary>
        /// <param name="rows">json array</param>
        /// <param name="docx"></param>
        /// <param name="srcRowNo">資料開始列數, base 1, 如果為0表示沒有使用template file, 則此參數自動設為1</param>
        /// <param name="sheetName">default 'Sheet1', excel save sheet name</param>
        /// //<param name="template">excel template, 如果空白, 則欄位名稱會使用row field id</param>
        /// //<param name="headers">excel欄位header, 如果空白, 則使用欄位id</param>
        /// //<param name="cols">要輸出的excel欄位, 如果空白, 則全部輸出</param>
        //public static void DocxByRows(JArray rows, SpreadsheetDocument docx, string template, string sheetName, List<string> headers = null, List<string> cols = null)
        public static void DocxByRows(JArray rows, SpreadsheetDocument docx, int srcRowNo = 1)
        {
            //check
            if (docx == null)
            {
                return;
            }

            //set cols
            var rowCount = (rows == null) ? 0 : rows.Count;
            var cols     = new List <string>();

            if (rowCount > 0)
            {
                foreach (var item in (JObject)rows[0])
                {
                    cols.Add(item.Key);
                }
            }

            //if (headers == null)
            //    headers = cols;

            SheetData sheetData   = null;
            var       hasTemplate = (srcRowNo > 0);
            var       colCount    = cols.Count;

            //Row srcRow = null;
            //Row nowRow = null;
            //Row newRow = null;
            if (hasTemplate)
            {
                //使用範本
                #region prepre excel-sheetData
                var sheet  = docx.WorkbookPart.Workbook.Descendants <Sheet>().FirstOrDefault();
                var wsPart = (WorksheetPart)docx.WorkbookPart.GetPartById(sheet.Id);
                //var ws = wsPart.Worksheet;
                sheetData = wsPart.Worksheet.GetFirstChild <SheetData>();
                #endregion

                //var count = sheetData.Elements<Row>().Count();
                //TODO: copy row style
                //srcRow = sheetData.Elements<Row>().ElementAt(srcRowNo);

                //寫入excel row, 使用範本
                for (var rowNo = 0; rowNo < rowCount; rowNo++)
                {
                    //第一筆時不必新增一列
                    var row = (JObject)rows[rowNo];

                    /*
                     * if (rowNo == 0)
                     * {
                     *  nowRow = sheetData.Elements<Row>().ElementAt(srcRowNo);
                     *  Cell cell;
                     *  for (var colNo = 0; colNo < colCount; colNo++)
                     *  {
                     *      cell = nowRow.Elements<Cell>().ElementAt(colNo);
                     *      cell.CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString());
                     *      cell.DataType = CellValues.String;
                     *  }
                     * }
                     * else
                     * {
                     */
                    //新增一列 & 填入欄位
                    //TODO: copy row style
                    var newRow = new Row();
                    for (var colNo = 0; colNo < colCount; colNo++)
                    {
                        newRow.Append(new Cell()
                        {
                            CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString()),
                            DataType  = CellValues.String,
                        });
                    }
                    sheetData.InsertAt(newRow, rowNo + srcRowNo);
                    //}
                }
            }
            else
            {
                //沒有範本
                #region prepre excel-sheetData
                var bookPart = docx.AddWorkbookPart();
                bookPart.Workbook = new Workbook();

                var sheetPart = bookPart.AddNewPart <WorksheetPart>();
                sheetPart.Worksheet = new Worksheet(new SheetData());

                var sheets = bookPart.Workbook.AppendChild(new Sheets());
                sheets.Append(new Sheet()
                {
                    Id      = bookPart.GetIdOfPart(sheetPart),
                    SheetId = 1,
                    Name    = "Sheet1",
                });
                sheetData = sheetPart.Worksheet.GetFirstChild <SheetData>();
                #endregion

                //add header row
                //srcRowNo = 1;   //base 1
                var newRow = new Row();
                for (var i = 0; i < colCount; i++)
                {
                    newRow.Append(new Cell()
                    {
                        CellValue = new CellValue(cols[i]),
                        DataType  = CellValues.String,
                    });
                }
                sheetData.AppendChild(newRow);

                //寫入excel row, 沒有範本
                for (var rowNo = 0; rowNo < rowCount; rowNo++)
                {
                    //var excelRow = NewRow(row, colCount, cols);
                    var row = (JObject)rows[rowNo];
                    newRow = new Row();
                    for (var colNo = 0; colNo < colCount; colNo++)
                    {
                        newRow.Append(new Cell()
                        {
                            CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString()),
                            DataType  = CellValues.String,
                        });
                    }
                    sheetData.AppendChild(newRow);
                }
            }
        }