Пример #1
0
        /// <summary>
        /// import by stream
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="importDto"></param>
        /// <param name="fileName"></param>
        /// <param name="uiDtFormat"></param>
        /// <returns></returns>
        public async Task <ResultImportDto> ImportByStreamAsync(Stream stream, ExcelImportDto <T> importDto, string dirUpload, string fileName, string uiDtFormat)
        {
            stream.Position = 0;
            var docx   = _Excel.StreamToDocx(stream);
            var result = await ImportByDocxAsync(docx, importDto, dirUpload, fileName, uiDtFormat);

            //release docx
            docx = null;
            return(result);
        }
Пример #2
0
        /*
         * //excel license file path
         * //protected string asposeLicPath;
         * protected string saveExcelPath; //with path
         * protected string tplPath;
         * protected string sheetName;
         * protected string uploadFileName;
         * protected string sysFileName;
         */

        #region 抽象方法宣告, 在子代實作
        //檢查table row(excel轉dataTable)
        //abstract public T CheckTableRow(DataRow dr);

        //執行匯入
        //abstract public void RunImport();
        #endregion

        /*
         * /// <summary>
         * /// constructor, 如果傳入參數有誤則送出 Exception
         * /// </summary>
         * /// <param name="asposeLicPath">server path</param>
         * /// <param name="excelPath">excel file full path</param>
         * /// <param name="checkTableRow"></param>
         * /// <param name="sheetName">excel sheet name</param>
         * public ImportExcelService(string asposeLicPath, string excelPath, string tplPath, string sheetName = "Sheet1")
         * {
         *  try
         *  {
         *      //set instance variables
         *      this.asposeLicPath = asposeLicPath;
         *      this.excelPath = excelPath;
         *      this.tplPath = tplPath;
         *      this.sheetName = sheetName;
         *
         *      //建立excel connection
         *      var excelBook = Utils.OpenExcel(asposeLicPath, excelPath);
         *
         *      //set dataTable _dt
         *      var errorMsg = "";
         *      this.dt = Utils.ReadWorksheet(excelBook, sheetName, 1, 0, out errorMsg, true);
         *  }
         *  catch (Exception ex)
         *  {
         *      throw new Exception("ImportExcelService.cs constructor failed: " + ex.Message);
         *  }
         * }
         */

        /// <summary>
        ///
        /// </summary>
        /// <param name="asposeLicPath"></param>
        /// <param name="uploadFileName"></param>
        /// <param name="saveExcelPath"></param>
        /// <param name="tplPath"></param>
        /// <param name="sheetName"></param>
        /// <returns>system error, not excel row error</returns>

        /*
         * public string ImportByDocx_new(string asposeLicPath, string uploadFileName, string saveExcelPath, string tplPath, string sheetName = "Sheet1")
         * {
         *  //initial
         *  var error = Init(asposeLicPath, uploadFileName, saveExcelPath, tplPath, sheetName);
         *  if (!string.IsNullOrEmpty(error))
         *      return error;
         *
         *  //檢查excel rows
         *  CheckRows();
         *
         *  //匯入
         *  RunImport();
         *  return "";
         * }
         */

        public string ImportByStream(string frontDtFormat, Stream stream, ExcelImportDto <T> import)
        {
            stream.Position = 0;
            var docx = _Excel.StreamToDocx(stream);
            //var docx = SpreadsheetDocument.Open(stream, false);
            var error = ImportByDocx(frontDtFormat, docx, import);

            //release docx
            docx = null;
            return(error);
        }
Пример #3
0
        /*
         * //string filePath, string insertSql, int[] excelCols, int excelStartRow, bool[] isDates = null, int sheetNo = 0, Db db = null)
         * public static bool ImportByFile(HttpPostedFileBase file, string insertSql, int excelStartRow, int[] excelCols, bool[] isDates = null, int sheetNo = 0, Db db = null)
         * {
         *  //check
         *  if (file == null || file.ContentLength == 0)
         *      return false;
         *
         *  return _Excel.ImportByStream(file.InputStream, insertSql, excelStartRow, excelCols, isDates, sheetNo, db);
         * }
         */

        public static string ImportByFile <T>(IFormFile file,
                                              ExcelImportDto <T> import) where T : class, new()
        {
            //check
            if (file == null || file.Length == 0)
            {
                return("error");
            }

            //TODO pending
            //return new ExcelImportService<T>().ImportByStream(file.InputStream, import);
            return(null);
        }
Пример #4
0
        /// <summary>
        /// import by file
        /// </summary>
        /// <param name="file"></param>
        /// <param name="importType"></param>
        /// <param name="importDto"></param>
        /// <param name="uiDtFormat">skip if excel no datetime cell</param>
        public static async Task <ResultImportDto> ImportByFileAsync <T>(IFormFile file, string dirUpload,
                                                                         ExcelImportDto <T> importDto, string uiDtFormat = "") where T : class, new()
        {
            //check
            if (file == null || file.Length == 0)
            {
                return new ResultImportDto()
                       {
                           ErrorMsg = "Upload file is empty.",
                       }
            }
            ;

            return(await new ExcelImportService <T>().ImportByStreamAsync(file.OpenReadStream(), importDto, dirUpload, file.FileName, uiDtFormat));
        }
Пример #5
0
        /// <summary>
        /// import by excel docx
        /// excel row/cell (base 0)
        /// </summary>
        /// <param name="docx"></param>
        /// <param name="importDto"></param>
        /// <param name="fileName">imported excel file name</param>
        /// <param name="uiDtFormat"></param>
        /// <returns>error msg if any</returns>
        public async Task <ResultImportDto> ImportByDocxAsync(SpreadsheetDocument docx, ExcelImportDto <T> importDto, string dirUpload, string fileName, string uiDtFormat)
        {
            #region 1.set variables
            #region set docx, excelRows, ssTable
            //var errorMsg = "";
            var wbPart = docx.WorkbookPart;
            var wsPart = (WorksheetPart)wbPart.GetPartById(
                wbPart.Workbook.Descendants <Sheet>().ElementAt(importDto.SheetNo).Id);

            var excelRows = wsPart.Worksheet.Descendants <Row>();    //include empty rows
            var ssTable   = wbPart.GetPartsOfType <SharedStringTablePart>().First().SharedStringTable;
            #endregion

            #region set importDto.ExcelFids, excelFidLen
            int idx;
            var colMap    = new JObject();  //col x-way name(ex:A) -> col index
            var cells     = excelRows.ElementAt(importDto.FidRowNo - 1).Elements <Cell>();
            var excelFids = new List <string>();
            //if (importDto.ExcelFids == null || importDto.ExcelFids.Count == 0)
            //{
            //如果沒有傳入excel欄位名稱, 則使用第一行excel做為欄位名稱
            idx = 0;
            foreach (var cell in cells)
            {
                excelFids.Add(GetCellValue(ssTable, cell));
                colMap[CellXname(cell.CellReference)] = idx;
                idx++;
            }

            /*
             * }
             * else
             * {
             *  //有傳入excel欄位名稱
             *  //check
             *  var cellLen = cells.Count();
             *  if (cellLen != importDto.ExcelFids.Count)
             *  {
             *      errorMsg = "importDto.ExcelFids length should be " + cellLen;
             *      goto lab_error;
             *  }
             *
             *  //set colMap
             *  for (var i=0; i< cellLen; i++)
             *  {
             *      var colName = CellXname(cells.ElementAt(i).CellReference);
             *      colMap[colName] = i;
             *  }
             * }
             */

            //initial excelIsDates & set excelFidLen
            var excelIsDates = new List <bool>();        //是否為是日期欄位
            var excelFidLen  = excelFids.Count;
            for (var i = 0; i < excelFidLen; i++)
            {
                excelIsDates.Add(false);    //initial
            }
            #endregion

            #region set excelIsDates, modelFids, modelDateFids/Fno/Len, modelNotDateFids/Fno/Len
            int fno;
            var modelFids = new List <string>();         //全部欄位
            var model     = new T();
            foreach (var prop in model.GetType().GetProperties())
            {
                //如果對應的excel欄位不存在, 則不記錄此欄位(skip)
                //var type = prop.GetValue(model, null).GetType();
                var fid = prop.Name;
                fno = excelFids.FindIndex(a => a == fid);
                if (fno < 0)
                {
                    continue;
                }

                modelFids.Add(fid);
                if (prop.PropertyType == typeof(DateTime?))
                {
                    excelIsDates[fno] = true;
                }
            }

            //var modelDateFidLen = modelDateFids.Count;
            //var modelNotDateFidLen = modelNotDateFids.Count;
            #endregion

            #region set fileRows by excel file
            var fileRows    = new List <T>(); //excel rows with data(not empty row)
            var excelRowLen = excelRows.LongCount();
            for (var i = importDto.FidRowNo; i < excelRowLen; i++)
            {
                var excelRow = excelRows.ElementAt(i);
                var fileRow  = new T();

                /*
                 * //set datetime column
                 * //var rowHasCol = false;
                 * for(var j=0; j<modelDateFidLen; j++)
                 * {
                 *  //var cell = cells.ElementAt(modelDateFnos[j]);
                 *  var cell = excelRow.Descendants<Cell>().ElementAt(j);
                 *  if (cell.DataType != null)
                 *  {
                 *      //rowHasCol = true;
                 *      value = (cell.DataType == CellValues.SharedString) ? ssTable.ChildElements[int.Parse(cell.CellValue.Text)].InnerText :
                 *          cell.CellValue.Text;
                 *      _Model.SetValue(modelRow, modelDateFids[j], DateTime.FromOADate(double.Parse(value)).ToString(rb.uiDtFormat));
                 *  }
                 * }
                 */

                //write not date column
                //for (var j = 0; j < modelNotDateFidLen; j++)
                //var j = 0;
                foreach (Cell cell in excelRow)
                {
                    /*
                     * if (i == 2 && j == 1)
                     * {
                     *  var aa = "aa";
                     * }
                     */
                    //var cell = cells.ElementAt(modelNotDateFnos[j]);
                    //var cell = excelRow.Descendants<Cell>().ElementAt(modelNotDateFnos[j]);
                    //colName = ;
                    fno = (int)colMap[CellXname(cell.CellReference)];
                    var value = (cell.DataType == CellValues.SharedString)
                        ? ssTable.ChildElements[int.Parse(cell.CellValue.Text)].InnerText
                        : cell.CellValue.Text;
                    _Model.SetValue(fileRow, excelFids[fno], excelIsDates[fno]
                        ? DateTime.FromOADate(double.Parse(value)).ToString(uiDtFormat)
                        : value
                                    );
                }

                fileRows.Add(fileRow);
            }
            #endregion
            #endregion

            #region 2.validate fileRows loop
            idx = 0;
            //var error = "";
            foreach (var fileRow in fileRows)
            {
                //validate
                var context = new ValidationContext(fileRow, null, null);
                var results = new List <ValidationResult>();
                if (Validator.TryValidateObject(fileRow, context, results, true))
                {
                    //user validate rule
                    //if (importDto.FnCheckImportRow != null)
                    //    error = importDto.FnCheckImportRow(fileRow);
                    //if (_Str.IsEmpty(error))
                    _okRowNos.Add(idx);
                    //else
                    //    AddError(idx, error);
                }
                else
                {
                    AddErrorByResults(idx, results);
                }
                idx++;
            }
            #endregion

            #region 3.save database for ok rows(call FnSaveImportRows())
            if (_okRowNos.Count > 0)
            {
                //set okRows
                var okRows = new List <T>();
                foreach (var okRowNo in _okRowNos)
                {
                    okRows.Add(fileRows[okRowNo]);
                }

                //call FnSaveImportRows
                idx = 0;
                var saveResults = importDto.FnSaveImportRows(okRows);
                if (saveResults != null)
                {
                    foreach (var result in saveResults)
                    {
                        if (!_Str.IsEmpty(result))
                        {
                            AddError(_okRowNos[idx], result);
                        }
                        idx++;
                    }
                }
            }
            #endregion

            #region 4.save ok excel file
            if (_Str.IsEmpty(importDto.LogRowId))
            {
                importDto.LogRowId = _Str.NewId();
            }
            var fileStem = _Str.AddDirSep(dirUpload) + importDto.LogRowId;
            docx.SaveAs(fileStem + ".xlsx");
            #endregion

            #region 5.save fail excel file (tail _fail.xlsx)
            var failCount = _failRows.Count;
            if (failCount > 0)
            {
                //set excelFnos: excel column map model column
                var excelFnos = new List <int>();
                for (var i = 0; i < excelFidLen; i++)
                {
                    fno = modelFids.FindIndex(a => a == excelFids[i]);
                    excelFnos.Add(fno);    //<0 means no mapping
                }

                //get docx
                var failFilePath = fileStem + "_fail.xlsx";
                File.Copy(importDto.TplPath, failFilePath, true);

                var docx2   = SpreadsheetDocument.Open(failFilePath, true);
                var wbPart2 = docx2.WorkbookPart;
                var wsPart2 = (WorksheetPart)wbPart2.GetPartById(
                    wbPart2.Workbook.Descendants <Sheet>().ElementAt(0).Id);
                var sheetData2 = wsPart2.Worksheet.GetFirstChild <SheetData>();

                var startRow = importDto.FidRowNo;    //insert position
                for (var i = 0; i < failCount; i++)
                {
                    //add row, fill value & copy row style
                    var modelRow = fileRows[_failRows[i].Sn];
                    var newRow   = new Row();   //new excel row
                    for (var colNo = 0; colNo < excelFidLen; colNo++)
                    {
                        fno = excelFnos[colNo];
                        var value2 = _Model.GetValue(modelRow, excelFids[colNo]);
                        newRow.Append(new Cell()
                        {
                            CellValue = new CellValue(fno < 0 || value2 == null ? "" : value2.ToString()),
                            DataType  = CellValues.String,
                        });
                    }

                    //write cell for error msg
                    newRow.Append(new Cell()
                    {
                        CellValue = new CellValue(_failRows[i].Str),
                        DataType  = CellValues.String,
                    });

                    sheetData2.InsertAt(newRow, startRow + i);
                }
                docx2.Save();
                docx2.Dispose();
            }
            #endregion

            #region 6.insert ImportLog table
            var totalCount = fileRows.Count;
            var okCount    = totalCount - failCount;
            var sql        = $@"
insert into dbo.XpImportLog(Id, Type, FileName,
OkCount, FailCount, TotalCount,
CreatorName, Created)
values('{importDto.LogRowId}', '{importDto.ImportType}', '{fileName}',
{okCount}, {failCount}, {totalCount}, 
'{importDto.CreatorName}', '{_Date.NowDbStr()}')
";
            await _Db.ExecSqlAsync(sql);

            #endregion

            //7.return import result
            return(new ResultImportDto()
            {
                OkCount = okCount,
                FailCount = failCount,
                TotalCount = totalCount,
            });
        }
Пример #6
0
        /// <summary>
        /// import by excel docx
        /// excel row/cell 為base 0
        /// </summary>
        /// <param name="docx"></param>
        /// <param name="import">匯入參數</param>
        /// <returns>error msg if any</returns>
        public string ImportByDocx(string frontDtFormat, SpreadsheetDocument docx, ExcelImportDto <T> import)
        {
            //檢查傳入參數

            #region set docx, excelRows, ssTable
            var wbPart = docx.WorkbookPart;
            var wsPart = (WorksheetPart)wbPart.GetPartById(
                wbPart.Workbook.Descendants <Sheet>().ElementAt(import.SheetNo).Id);

            var excelRows = wsPart.Worksheet.Descendants <Row>();
            var ssTable   = wbPart.GetPartsOfType <SharedStringTablePart>().First().SharedStringTable;
            #endregion

            #region set import.ExcelFids, excelFidLen
            int idx;
            //string colName;
            var colMap = new JObject();     //欄位字元(ex:A) -> 欄位陣列元素idx
            var cells  = excelRows.ElementAt(0).Elements <Cell>();
            if (import.ExcelFids == null || import.ExcelFids.Count == 0)
            {
                //如果沒有傳入excel欄位名稱, 則使用第一行excel做為欄位名稱
                idx = 0;
                foreach (var cell in cells)
                {
                    import.ExcelFids.Add(GetCellValue(ssTable, cell));
                    colMap[CellNameRemoveNum(cell.CellReference)] = idx;
                    idx++;
                }
            }
            else
            {
                //有傳入excel欄位名稱
                //check
                var cellLen = cells.Count();
                if (cellLen != import.ExcelFids.Count)
                {
                    return("import.ExcelFids length should be " + cellLen);
                }

                //set colMap
                for (var i = 0; i < cellLen; i++)
                {
                    var colName = CellNameRemoveNum(cells.ElementAt(i).CellReference);
                    colMap[colName] = i;
                }
            }

            //initial excelIsDates & set excelFidLen
            var excelIsDates = new List <bool>();        //是否為是日期欄位
            var excelFidLen  = import.ExcelFids.Count;
            for (var i = 0; i < excelFidLen; i++)
            {
                excelIsDates.Add(false);    //initial
            }
            #endregion

            #region set excelIsDates, modelFids, modelDateFids/Fno/Len, modelNotDateFids/Fno/Len
            int fno;
            var modelFids = new List <string>();         //全部欄位
            var model     = new T();
            foreach (var prop in model.GetType().GetProperties())
            {
                //如果對應的excel欄位不存在, 則不記錄此欄位(skip)
                //var type = prop.GetValue(model, null).GetType();
                var fid = prop.Name;
                fno = import.ExcelFids.FindIndex(a => a == fid);
                if (fno < 0)
                {
                    continue;
                }

                modelFids.Add(fid);
                if (prop.PropertyType == typeof(DateTime?))
                {
                    excelIsDates[fno] = true;
                }
            }

            //var modelDateFidLen = modelDateFids.Count;
            //var modelNotDateFidLen = modelNotDateFids.Count;
            #endregion

            #region set modelRows
            //var rb = _Locale.RB;
            var modelRows = new List <T>();
            //Cell cell;
            //string value;
            var excelRowLen = excelRows.LongCount();
            for (var i = import.ExcelStartRow - 1; i < excelRowLen; i++)
            {
                //var cells = excelRows.ElementAt(i).Elements<Cell>();
                var excelRow = excelRows.ElementAt(i);
                //var cells = excelRows.ElementAt(i).Descendants<Cell>();
                //var cells = row.Elements<Cell>();

                var modelRow = new T();

                /*
                 * //寫入日期欄位
                 * //var rowHasCol = false;
                 * for(var j=0; j<modelDateFidLen; j++)
                 * {
                 *  //var cell = cells.ElementAt(modelDateFnos[j]);
                 *  var cell = excelRow.Descendants<Cell>().ElementAt(j);
                 *  if (cell.DataType != null)
                 *  {
                 *      //rowHasCol = true;
                 *      value = (cell.DataType == CellValues.SharedString) ? ssTable.ChildElements[int.Parse(cell.CellValue.Text)].InnerText :
                 *          cell.CellValue.Text;
                 *      _Model.SetValue(modelRow, modelDateFids[j], DateTime.FromOADate(double.Parse(value)).ToString(rb.FrontDtFormat));
                 *  }
                 * }
                 */

                //寫入非日期欄位
                //for (var j = 0; j < modelNotDateFidLen; j++)
                //var j = 0;
                foreach (Cell cell in excelRow)
                {
                    /*
                     * if (i == 2 && j == 1)
                     * {
                     *  var aa = "aa";
                     * }
                     */
                    //var cell = cells.ElementAt(modelNotDateFnos[j]);
                    //var cell = excelRow.Descendants<Cell>().ElementAt(modelNotDateFnos[j]);
                    //colName = ;
                    fno = (int)colMap[CellNameRemoveNum(cell.CellReference)];
                    var value = (cell.DataType == CellValues.SharedString)
                        ? ssTable.ChildElements[int.Parse(cell.CellValue.Text)].InnerText
                        : cell.CellValue.Text;
                    _Model.SetValue(modelRow, import.ExcelFids[fno], excelIsDates[fno]
                        ? DateTime.FromOADate(double.Parse(value)).ToString(frontDtFormat)
                        : value
                                    );
                }

                modelRows.Add(modelRow);
            }
            #endregion

            #region validate modelRows loop
            idx = 0;
            var error = "";
            foreach (var modelRow in modelRows)
            {
                //validate
                var context = new ValidationContext(modelRow, null, null);
                var results = new List <ValidationResult>();
                if (Validator.TryValidateObject(modelRow, context, results, true))
                {
                    //user validate rule
                    if (import.FnCheckImportRow != null)
                    {
                        error = import.FnCheckImportRow(modelRow);
                    }
                    if (string.IsNullOrEmpty(error))
                    {
                        _okRowNos.Add(idx);
                    }
                    else
                    {
                        AddError(idx, error);
                    }
                }
                else
                {
                    AddErrorByResults(idx, results);
                }
                idx++;
            }
            #endregion

            #region save database if need
            if (_okRowNos.Count > 0)
            {
                //set okRows
                var okRows = new List <T>();
                foreach (var okRowNo in _okRowNos)
                {
                    okRows.Add(modelRows[okRowNo]);
                }

                idx = 0;
                var saveResults = import.FnSaveImportRows(okRows);
                if (saveResults != null)
                {
                    foreach (var result in saveResults)
                    {
                        if (!string.IsNullOrEmpty(result))
                        {
                            AddError(_okRowNos[idx], result);
                        }
                        idx++;
                    }
                }
            }
            #endregion

            //save excel file
            var fileStem = _Str.AddAntiSlash(import.SaveDir) + import.LogRowId;
            docx.SaveAs(fileStem + "_source.xlsx");

            #region save error excel file
            var errorLen = _errorRows.Count;
            if (errorLen > 0)
            {
                //產生 excel 欄位與 model 欄位的對應 excelFnos
                var excelFnos = new List <int>();
                for (var i = 0; i < excelFidLen; i++)
                {
                    fno = modelFids.FindIndex(a => a == import.ExcelFids[i]);
                    excelFnos.Add(fno);    //小於0表示無對應欄位
                }

                //get docx
                var errorFilePath = fileStem + "_error.xlsx";
                File.Copy(import.TplFilePath, errorFilePath, true);

                var docx2   = SpreadsheetDocument.Open(errorFilePath, true);
                var wbPart2 = docx2.WorkbookPart;
                var wsPart2 = (WorksheetPart)wbPart2.GetPartById(
                    wbPart2.Workbook.Descendants <Sheet>().ElementAt(0).Id);
                var sheetData2 = wsPart2.Worksheet.GetFirstChild <SheetData>();

                var startRow = import.ExcelStartRow - 1;    //insert position
                for (var i = 0; i < errorLen; i++)
                {
                    //add row, fill value & copy row style
                    var modelRow = modelRows[_errorRows[i].Sn];
                    var newRow   = new Row();   //new excel row
                    for (var colNo = 0; colNo < excelFidLen; colNo++)
                    {
                        fno = excelFnos[colNo];
                        var value2 = _Model.GetValue(modelRow, import.ExcelFids[colNo]);
                        newRow.Append(new Cell()
                        {
                            CellValue = new CellValue(fno < 0 || value2 == null ? "" : value2.ToString()),
                            DataType  = CellValues.String,
                        });
                    }

                    //write cell for error msg
                    newRow.Append(new Cell()
                    {
                        CellValue = new CellValue(_errorRows[i].Str),
                        DataType  = CellValues.String,
                    });

                    sheetData2.InsertAt(newRow, startRow + i);
                }
                docx2.Save();
                docx2.Dispose();
            }
            #endregion

            #region add import log
            var sql = string.Format(@"
insert into dbo._ImportLog(Id, UploadFileName,
OkCount, FailCount, TotalCount,
CreatorName, Created)
values('{0}', '{1}',
{2}, {3}, {4},
'{5}', '{6}')
", import.LogRowId, import.UploadFileName,
                                    modelRows.Count - _errorRows.Count, _errorRows.Count, modelRows.Count,
                                    import.CreatorName, _Date.NowDbStr());

            _Db.Update(sql);
            #endregion

            return(string.Empty);
        }