In Excel tables, due to the complexity of various data, there may be problems such as the size of data fonts and the length of data content in cells, too wide or too narrow columns, or too large or too small rows. The common solution is to adjust the row height and column width. In Microsoft Excel, the adaptive row height or column width can be set manually in cell formatting, but through code, we can set the adaptive row height and column width of the specified data range or the whole worksheet by means of AutoFitColumns() or AutoFitRows(). Here we set up the adaptive sub-conditions as follows:

1. Fixed data, set line height and column width adaptive data

2. Fixed row height and column width, set data adaptive row height and column width (i.e. reduced cell data font size to fit cell)

Use tools: Free Spire. XLS for. NET (Free Edition)

Note: Note: After downloading and installing, add a reference to Spire.Xls.dll file, as follows:

 

Fixed data, set line height and column width adaptive data

Step 1: Load the workbook

//Create a Workbook Class objects and load test documents
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");

Step 2: Get the specified worksheet

Worksheet worksheet = workbook.Worksheets[0];

Step 3: Setting Adaptation

//Set column width and row height to be adaptive (for specified data ranges)
worksheet.AllocatedRange["A1:F15"].AutoFitColumns();
worksheet.AllocatedRange["A1:F15"].AutoFitRows();

////Setting column width and row height to be adaptive (applied to the entire worksheet)
//worksheet.AllocatedRange.AutoFitColumns();
//worksheet.AllocatedRange.AutoFitRows();

Step 4: Save Documents

workbook.SaveToFile("result.xlsx", FileFormat.Version2010);

Setting effect:

All code:

using Spire.Xls;

namespace AutoFit_XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook Class objects and load test documents
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("sample.xlsx");

            //Get the worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Set column width and row height to be adaptive (for specified data ranges)
            worksheet.AllocatedRange["A1:F15"].AutoFitColumns();
            worksheet.AllocatedRange["A1:F15"].AutoFitRows();

            ////Setting column width and row height to be adaptive (for the entire worksheet)
            //worksheet.AllocatedRange.AutoFitColumns();
            //worksheet.AllocatedRange.AutoFitRows();

            //Save document
            workbook.SaveToFile("result.xlsx", FileFormat.Version2010);
            System.Diagnostics.Process.Start("result.xlsx");
        }
    }
}

 

[Example 2] Fixed row height and column width, set data adaptive row height and column width

Step 1: Load the workbook

//instantiation Workbook Class objects and load test documents
Workbook workbook = new Workbook();
workbook.LoadFromFile("test.xlsx");

Step 2: Get the specified worksheet

Worksheet sheet = workbook.Worksheets[0];

Step 3: Get the specified data range

CellRange cell = sheet.Range["A1:C1"];

Step 4: Reduce cell fonts to fit column widths

CellStyle style = cell.Style;
style.ShrinkToFit = true;

Step 5: Save Documents

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);

 

Setting results:

All code:

using Spire.Xls;

namespace ShrinkTextToFitCell_XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            //instantiation Workbook Class objects and load test documents
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("test.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Gets the specified data range
            CellRange cell = sheet.Range["A1:C1"];

            //Set cell style (data shrinkage to fit cells)
            CellStyle style = cell.Style;
            style.ShrinkToFit = true;

            //Save document
            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);
            System.Diagnostics.Process.Start("result.xlsx");
        }
    }
}

 

(End of this article)