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.