how to generate auto-fit cells in excel using filestreamresult?
how to generate auto-fit cells in excel using c# 开发者_如何转开发filestreamresult
Brinal
AutoFitting the Contents
Aspose.Cells provides a class, Workbook that represents an Excel file. Workbook class contains a Worksheets collection that allows to access each worksheet in the Excel file.
A worksheet is represented by the Worksheet class. Worksheet class provides a wide range of properties and methods to manage a worksheet. But, in this topic, we will see that how can we use Worksheet class to AutoFit rows or columns.
AutoFit Row - Simple
The most simple approach of auto-sizing the width and height of a row is to call the AutoFitRow method of the Worksheet class. AutoFitRow method takes the row index (of the row whose auto-sizing is desired) as a parameter.
Example:
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream("C:\\book1.xls", FileMode.Open);
//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];
//Auto-fitting the 3rd row of the worksheet
worksheet.AutoFitRow(2);
//Saving the modified Excel file
workbook.Save("C:\\output.xls");
//Closing the file stream to free all resources
fstream.Close();
AutoFit Row in a Range of Cells
We know that one row is composed of many columns/cells. Aspose.Cells also allows developers to auto-fit a row based on the content in a range of cells within the row by calling an overloaded version of AutoFitRow method that takes three parameters as follows:
* Row Index, represents the index of the row whose contents need to auto-fit
* First Column Index, represents the index of the first column of the row
* Last Column Index, represents the index of the last column of the row
This method will check the contents of all columns in the row and then auto-fit the row.
Example:
//Auto-fitting the 3rd row of the worksheet based on the contents in a range of
//cells (from 1st to 9th column) within the row
worksheet.AutoFitRow(2,0,8);
AutoFit Column - Simple
The simplest approach of auto-sizing the width and height of a column is to call the AutoFitColumn method of the Worksheet class. AutoFitColumn method takes the column index (of the column whose auto-sizing is desired) as a parameter.
Example:
worksheet.AutoFitColumn(3);
AutoFit Column in a Range of Cells
One column is composed of many rows/cells. Developers can auto-fit a column based on the content in a range of cells within the column by calling an overloaded version of AutoFitColumn method that takes three parameters as follows:
* Column Index, represents the index of the column whose contents need to auto-fit
* First Row Index, represents the index of the first row of the column
* Last Row Index, represents the index of the last row of the column
This method will check the contents of all rows in the column and then auto-fit the column.
Example:
worksheet.AutoFitColumn(3,0,8);
AutoFit Rows for Merged Cells
MS Excel doesn't set auto-fit rows operation on merged cells natively. Sometimes the feature becomes vital for a user and he really needs to implement Auto-Fit Rows operation on the merged cells too.
Aspose.Cells provides this feasibility and it has AutoFitterOptions API. Using the API, one can auto-fit rows in the worksheet including the merged cells too.
Example:
Workbook wb = new Workbook();
Worksheet _worksheet = wb.Worksheets[0];
Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2);
range.Merge();
_worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end";
Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle();
style.IsTextWrapped = true;
_worksheet.Cells[0, 0].SetStyle(style);
AutoFitterOptions options = new AutoFitterOptions();
options.AutoFitMergedCells = true;
_worksheet.AutoFitRows(options);
wb.Save("e:\\test2\\autofitmergedcells.xlsx");
Important to Know
If a cell is merged then AutoFit methods will not be applied on that cell (by default), which is same as that of Microsoft Excel. Moreover, if a cell's text is wrapped, AutFitColumn method will not be applied on it also. Another important thing to know is that AutoFit methods are time consuming methods. So, you should call these methods as less as possible to ensure your application's efficiency.
精彩评论