execl插入数据

参考：https://www.cnblogs.com/kingangWang/archive/2011/08/31/2161319.html

1、在controller层操作execl，主要内容是如果超过数据行后，对行偏移ShiftRows，主要问题是NPOI中没有提示现有的插入行函数，如果CreateRow后样式有问题。

思路：

一：先确要从第几行插入----int InsertRowIndex;

二：指定要插入多少行----int InsertRowCount;

三：根据上一行formatRow，刷新新增行样式；

        [HttpPost]
        public async Task<IActionResult> DownloadTenantElectricPayData(GetTenantPayDataModel input)
        {
            var dataResult = await _client.GetGrain<IReportManagerService>(Guid.NewGuid()).GetTenantElectricPayData(input);
            var result = new ApiResult<string> { Code = dataResult.Code, Msg = dataResult.Msg };
            if (dataResult.Code== RequestBackStatuEnum.success.Value && dataResult.Data != null)
            {
                var tmpPath = System.IO.Path.Combine(Directory.GetCurrentDirectory(), "Template", "ZhongJiao");

                try
                {
                    string fileName = string.Empty;
                    HSSFWorkbook hssfworkbook = null;
                    if (input.PayType == 1)
                    {//预付费
                        fileName = "预付费租户报表.xls";
                        var fileNamePath = System.IO.Path.Combine(tmpPath, "预付费租户报表.xls");
                        FileStream fileStream = new FileStream(fileNamePath, FileMode.Open, FileAccess.Read);


                        hssfworkbook = new HSSFWorkbook(fileStream);



                        var sheet0 = hssfworkbook.GetSheetAt(0);

                        var row1 = sheet0.GetRow(1);
                        row1.GetCell(0).SetCellValue(dataResult.Data.Title);

                        sheet0.GetRow(2).GetCell(2).SetCellValue($"{input.StartTime.Year}年{input.StartTime.Month}月{input.StartTime.Day}日至{input.EndTime.Year}年{input.EndTime.Month}月{input.EndTime.Day}日");

                        #region MyRegion
                        if (dataResult.Data.Items.Count() > 10)
                        {
                            int insertRowIndex = 14;
                            int insertRowCount = dataResult.Data.Items.Count() - 10;
                            IRow formatRow = sheet0.GetRow(4);
                            sheet0.ShiftRows(insertRowIndex, sheet0.LastRowNum, insertRowCount, true, false);
                            for (int i = insertRowIndex; i < insertRowIndex + insertRowCount; i++)
                            {
                                IRow targetRow = null;
                                ICell sourceCell = null;
                                ICell targetCell = null;
                                targetRow = sheet0.CreateRow(i);
                                for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
                                {
                                    sourceCell = formatRow.GetCell(m);
                                    if (sourceCell == null)
                                    {
                                        continue;
                                    }
                                    targetCell = targetRow.CreateCell(m);
                                    targetCell.CellStyle = sourceCell.CellStyle;
                                    targetCell.SetCellType(sourceCell.CellType);

                                }
                            }
                            for (int i = insertRowIndex; i < insertRowIndex + insertRowCount; i++)
                            {
                                IRow firstTargetRow = sheet0.GetRow(i);
                                ICell firstSourceCell = null;
                                ICell firstTargetCell = null;

                                for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
                                {
                                    firstSourceCell = formatRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                                    if (firstSourceCell == null)
                                    {
                                        continue;
                                    }
                                    firstTargetCell = firstTargetRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);

                                    firstTargetCell.CellStyle = firstSourceCell.CellStyle;
                                    firstTargetCell.SetCellType(firstSourceCell.CellType);
                                }
                            }
                        }
                        #endregion

                        
                        int rowIndex = 0;
                        foreach (var itemRow in dataResult.Data.Items)
                        {
                            var rowCell = sheet0.GetRow(rowIndex + 4);
                            if (rowCell == null)
                            {
                                rowCell = sheet0.CopyRow(rowIndex + 3, rowIndex + 4);
                                
                                var formatRow = sheet0.GetRow(rowIndex + 3);
                                for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
                                {
                                    var sourceCell = formatRow.GetCell(m);
                                    var targetCell = rowCell.GetCell(m);
                                    if (sourceCell == null)
                                    {
                                        continue;
                                    }
                                    targetCell.CellStyle = sourceCell.CellStyle;
                                    targetCell.SetCellType(sourceCell.CellType);

                                }
                            }

                            int cellIndex = 0;
                            var cell0 = rowCell.GetCell(cellIndex);
                            if (cell0 == null)
                            {
                                cell0 = rowCell.CreateCell(cellIndex);
                            }
                            cell0.SetCellValue(rowIndex + 1);

                            var cell1 = rowCell.GetCell(cellIndex + 1);
                            if (cell1 == null)
                            {
                                cell1 = rowCell.CreateCell(cellIndex + 1);
                            }
                            cell1.SetCellValue(itemRow.ConstName);

                            var cell2 = rowCell.GetCell(cellIndex + 2);
                            if (cell2 == null)
                            {
                                cell2 = rowCell.CreateCell(cellIndex + 2);
                            }
                            cell2.SetCellValue(itemRow.EquipName);

                            var cell3 = rowCell.GetCell(cellIndex + 3);
                            if (cell3 == null)
                            {
                                cell3 = rowCell.CreateCell(cellIndex + 3);
                            }
                            cell3.SetCellValue(itemRow.AreaName);

                            var cell4 = rowCell.GetCell(cellIndex + 4);
                            if (cell4 == null)
                            {
                                cell4 = rowCell.CreateCell(cellIndex + 4);
                            }
                            cell4.SetCellValue(itemRow.LastEquipValue);

                            var cell5 = rowCell.GetCell(cellIndex + 5);
                            if (cell5 == null)
                            {
                                cell5 = rowCell.CreateCell(cellIndex + 5);
                            }
                            cell5.SetCellValue(itemRow.EquipValue);

                            var cell6 = rowCell.GetCell(cellIndex + 6);
                            if (cell6 == null)
                            {
                                cell6 = rowCell.CreateCell(cellIndex + 6);
                            }
                            cell6.SetCellValue(itemRow.EnergyConsum);

                            var cell7 = rowCell.GetCell(cellIndex + 7);
                            if (cell7 == null)
                            {
                                cell7 = rowCell.CreateCell(cellIndex + 7);
                            }
                            cell7.SetCellValue((double)itemRow.Price);

                            var cell8 = rowCell.GetCell(cellIndex + 8);
                            if (cell8 == null)
                            {
                                cell8 = rowCell.CreateCell(cellIndex + 8);
                            }
                            cell8.SetCellValue((double)itemRow.Money);

                            var cell9 = rowCell.GetCell(cellIndex + 9);
                            if (cell9 == null)
                            {
                                cell9 = rowCell.CreateCell(cellIndex + 9);
                            }
                            cell9.SetCellValue("");

                            var cell10 = rowCell.GetCell(cellIndex + 10);
                            if (cell10 == null)
                            {
                                cell10 = rowCell.CreateCell(cellIndex + 10);
                            }
                            cell10.SetCellValue(itemRow.Remark);
                            rowIndex++;
                        }
                    }
                    else if (input.PayType == 2)
                    {//后付费
                        fileName = "后付费租户报表.xls";
                        var fileNamePath = System.IO.Path.Combine(tmpPath, fileName);

                        FileStream fileStream = new FileStream(fileNamePath, FileMode.Open, FileAccess.Read);


                        hssfworkbook = new HSSFWorkbook(fileStream);



                        var sheet0 = hssfworkbook.GetSheetAt(0);
                        var row1 = sheet0.GetRow(1);
                        row1.GetCell(0).SetCellValue(dataResult.Data.Title);
                        sheet0.GetRow(2).GetCell(1).SetCellValue(dataResult.Data.Tenant);
                        sheet0.GetRow(3).GetCell(1).SetCellValue($"{input.StartTime.Year}年{input.StartTime.Month}月{input.StartTime.Day}日至{input.EndTime.Year}年{input.EndTime.Month}月{input.EndTime.Day}日");

                        int rowIndex = 5;
                        #region MyRegion
                        if (dataResult.Data.Items.Count() > 1)
                        {
                            int insertRowIndex = rowIndex + 1;
                            int insertRowCount = dataResult.Data.Items.Count() - 1;
                            IRow formatRow = sheet0.GetRow(rowIndex);
                            sheet0.ShiftRows(insertRowIndex, sheet0.LastRowNum, insertRowCount, true, false);
                            for (int i = insertRowIndex; i < insertRowIndex + insertRowCount; i++)
                            {
                                IRow targetRow = null;
                                ICell sourceCell = null;
                                ICell targetCell = null;
                                targetRow = sheet0.CreateRow(i);
                                for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
                                {
                                    sourceCell = formatRow.GetCell(m);
                                    if (sourceCell == null)
                                    {
                                        continue;
                                    }
                                    targetCell = targetRow.CreateCell(m);
                                    targetCell.CellStyle = sourceCell.CellStyle;
                                    targetCell.SetCellType(sourceCell.CellType);

                                }
                            }
                            for (int i = insertRowIndex; i < insertRowIndex + insertRowCount; i++)
                            {
                                IRow firstTargetRow = sheet0.GetRow(i);
                                ICell firstSourceCell = null;
                                ICell firstTargetCell = null;

                                for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
                                {
                                    firstSourceCell = formatRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                                    //firstSourceCell = formatRow.GetCell(m);
                                    if (firstSourceCell == null)
                                    {
                                        continue;
                                    }
                                    firstTargetCell = firstTargetRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                                    //firstTargetCell = firstTargetRow.GetCell(m);
                             
                                    firstTargetCell.CellStyle = firstSourceCell.CellStyle;
                                    firstTargetCell.SetCellType(firstSourceCell.CellType);
                                }
                            }
                        }
                        #endregion

                        var rowIndex1 = 5;
                        for (var i = 0; i < dataResult.Data.Items.Count(); i++)
                        {
                            var itemRow = dataResult.Data.Items[i];
                            IRow rowCell = sheet0.GetRow(i + rowIndex1);

                            int cellIndex = 0;
                            rowCell.GetCell(cellIndex).SetCellValue(i + 1);
                            rowCell.GetCell(cellIndex + 1).SetCellValue(itemRow.EquipName);
                            rowCell.GetCell(cellIndex + 2).SetCellValue(itemRow.AreaName);
                            rowCell.GetCell(cellIndex + 3).SetCellValue(itemRow.LastEquipValue);
                            rowCell.GetCell(cellIndex + 4).SetCellValue(itemRow.EquipValue);
                            rowCell.GetCell(cellIndex + 5).SetCellValue(itemRow.EnergyConsum);
                            rowCell.GetCell(cellIndex + 6).SetCellValue((double)itemRow.Price);
                            rowCell.GetCell(cellIndex + 7).SetCellValue((double)itemRow.Money);
                            rowCell.GetCell(cellIndex + 8).SetCellValue(itemRow.Remark);
                        }
                        var rowIndex2 = dataResult.Data.Items.Count() + 5;
                        var rowCellSum = sheet0.GetRow(rowIndex2);

                        //rowCellSum.GetCell(0).SetCellValue("合计");
                        //rowCellSum.GetCell(1).SetCellValue("");
                        //rowCellSum.GetCell(2).SetCellValue("");
                        //rowCellSum.GetCell(3).SetCellValue("");
                        //rowCellSum.GetCell(4).SetCellValue("");
                        rowCellSum.GetCell(5).SetCellValue(Math.Round(dataResult.Data.Items.Sum(s => s.EnergyConsum), 2));
                        //rowCellSum.GetCell(6).SetCellValue();
                        rowCellSum.GetCell(7).SetCellValue((double)Math.Round(dataResult.Data.Items.Sum(s => s.Money), 2));
                        //rowCellSum.GetCell(8).SetCellValue("");

                        var rowCellExp = sheet0.GetRow(rowIndex2 + 1);
                        //sheet0.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex2 + 1, rowIndex2 + 1, 1, rowCellExp.LastCellNum - 2) { });
                        rowCellExp.GetCell(1).SetCellValue(dataResult.Data.Explain);
                        //sheet0.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex2 + 2, rowIndex2 + 2, 1, rowCellExp.LastCellNum - 2) { });

                        var rowCellRemk = sheet0.GetRow(rowIndex2 + 2);
                        rowCellRemk.GetCell(1).SetCellValue(dataResult.Data.Remark);
                    }


                    MemoryStream memoryStream = new MemoryStream();
                    hssfworkbook.Write(memoryStream);
                    memoryStream.Flush();
                    memoryStream.Position = 0;

                    hssfworkbook.Close();
                    return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
                }
                catch (Exception ex)
                {
                    _logger.LogError(string.Format("{0}方法被调用，发生了错误:{1}", this.GetType().FullName, ex.Message.ToString()));
                    result.Code = RequestBackStatuEnum.fail.Value;
                    result.Msg = ex.Message;
                }
                finally
                {
                }
            }
            
            return StatusCode(500, result);
        }