SpreadsheetGear

quququ

本帖最后由 quququ 于 2023-8-31 11:22 编辑

把Microsoft.Office.Interop.Excel;注释掉改成using SpreadsheetGear;就会报以下错误，请问怎么解决这个报错。
主要是range的报错问题，这个方法就是实现把第二个sheet的内容copy到第一个sheet的下面的功能。
谢谢了。

image.png85319518.png

private void copyDataToSheet1()
      {
         Microsoft.Office.Interop.Excel.Application excelApp = null;
         Workbook workBook = null;
         Worksheet sheet2 = null;

         try
         {
            excelApp = new Microsoft.Office.Interop.Excel.Application();
            workBook = excelApp.Workbooks.Open(this.saveFilePathExl, Type.Missing, Type.Missing, Type.Missing,
                                                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                      Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            excelApp.DisplayAlerts = false;

            int copyRowCount = mSheet2PageCount * SHEET2_MAX_ROW;
            sheet2 = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(2);
            Range cell1 = sheet2.Cells[1, 1];
            Range cell2 = sheet2.Cells[copyRowCount, SHEET2_MAX_COL];
            Range range2 = sheet2.get_Range(cell1, cell2);
            List<double> rowHeightList = new List<double>();

            for (int i = 1; i <= copyRowCount; i++)
            {
                  double height = ((Range)sheet2.Rows[i, Type.Missing]).RowHeight;
                  rowHeightList.Add(height);
            }

            Microsoft.Office.Interop.Excel.Worksheet sheet1 =
                  (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(1);
            Range range1 = sheet1.get_Range(COPY_START_COL + COPY_START_ROW.ToString(), Type.Missing);
            range2.Copy(range1);

            for (int i = 0; i < rowHeightList.Count; i++)
            {
                  ((Range)sheet1.Rows[i + COPY_START_ROW, Type.Missing]).RowHeight = rowHeightList;
            }

            sheet2.Delete();
            workBook.Save();
            workBook.Close();
         }
         catch (Exception ex)
         {
            throw ex;
         }
         finally
         {
            if (sheet2 != null)
            {
                  System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet2);
            }

            if (workBook != null)
            {
                  System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
            }

            if (excelApp != null)
            {
                  try
                  {
                     excelApp.Quit();
                  }
                  finally
                  {
                     System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                  }
            }

            GC.Collect();
         }
      }

quququ · quququ

解决了。用如下方法。
string sCopyArea = "A1:" + "T" + copyRowCount;
            SpreadsheetGear.IRange cells2 = sheet2.Cells;
            SpreadsheetGear.IRange rangeFrom = cells2[sCopyArea].EntireRow;

            string sPasteArea = "A" + COPY_START_ROW + ":T" + (COPY_START_ROW + copyRowCount).ToString();
            sheet = (IWorksheet)workBook.Sheets[0];
            SpreadsheetGear.IRange cells = sheet.Cells;
            SpreadsheetGear.IRange rangeTo = cells[sPasteArea].EntireRow;

            rangeFrom.Copy(rangeTo, SpreadsheetGear.PasteType.All,
                     SpreadsheetGear.PasteOperation.None, false, false);