1 string fileName = AppDomain.CurrentDomain.BaseDirectory + "1.xls"; 2 Excel.Application xApp = new Excel.ApplicationClass(); 3 xApp.Visible = false; 4 xApp.AlertBeforeOverwriting = false;//覆盖不弹窗 5 xApp.DisplayAlerts = false; 6 Excel.Workbook xBook = xApp.Workbooks._Open(fileName, 7 Missing.Value, Missing.Value, Missing.Value, Missing.Value 8 , Missing.Value, Missing.Value, Missing.Value, Missing.Value 9 , Missing.Value, Missing.Value, Missing.Value, Missing.Value); 10 Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1]; 11 //关键代码 12 Range range = xSheet.get_Range(xSheet.Cells[4, 1], xSheet.Cells[7, 1]); 13 range.Rows.Select(); 14 range.Rows.Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing); 15 xSheet.SaveAs(AppDomain.CurrentDomain.BaseDirectory + "2.xls", //保存路径也可为上面打开的路径 16 Excel.XlFileFormat.xlExcel7, //指定保存格式 17 Missing.Value, 18 Missing.Value, 19 Missing.Value, 20 Missing.Value, 21 Excel.XlSaveAsAccessMode.xlShared, 22 Missing.Value, Missing. 23 Value, Missing.Value); 24 //关闭Excel 25 xSheet = null; 26 xBook = null; 27 xApp.Quit(); //这一句是非常重要的,否则Excel对象不能从内存中退出 28 xApp = null; 29 GC.Collect(); 30 System.GC.WaitForPendingFinalizers();
Aspose Cells是一款Excel文档处理控件,可以对Excel文件进行创建、处理、转换和各种操作,几乎可以实现Microsoft Excel一样的功能,在Excel里可以在数据区域范围外创建数据分组,当用户点击分组按钮时可以进行分组和显示。Aspose Cells提供一个类Workbook,里面包含了一系列函数和方法,GroupRows 和 GroupColumns 方法就是用于进行分组。
下面的事例简单介绍了分组行和列: //Creating a file stream containing the Excel file to be opened FileStream fstream = new FileStream("C:\\book1.xls", FileMode.Open); //Instantiating a Workbook object //Opening the Excel file through the file stream Workbook workbook = new Workbook(fstream); //Accessing the first worksheet in the Excel file Worksheet worksheet = workbook.Worksheets[0]; //Grouping first six rows (from 0 to 5) and making them hidden by passing true worksheet.Cells.GroupRows(0, 5, true); //Grouping first three columns (from 0 to 2) and making them hidden by passing true worksheet.Cells.GroupColumns(0, 2, true); //Saving the modified Excel file workbook.Save("C:\\output.xls"); //Closing the file stream to free all resources fstream.Close(); 同样地可以控制是否统计行是否显示 worksheet.Outline.SummaryRowBelow = false; worksheet.Outline.SummaryColumnRight = false;